SQL/PSM

From Wikipedia, the free encyclopedia

SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometimes called PSM-96 or even SQL-92/PSM[1]), SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2011. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM.[2] The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.

SQL/PSM standardizes syntax and semantics for control flow, exception handling (called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use of cursors. It also defines an information schema (metadata) for stored procedures. SQL/PSM is one language in which methods for the SQL:1999 structured types can be defined. (The other is Java, via SQL/JRT.)

In practice MySQL's procedural language and IBM's SQL PL (used in DB2) are closest to the SQL/PSM standard.[3]

SQL/PSM resembles and inspired by PL/SQL, as well as PL/pgSQL, so they are similar languages. With PostgreSQL v9.X some SQL/PSM features, like overloading of SQL-invoked functions and procedures[4] are now supported. A PostgreSQL addon implements SQL/PSM[5] (alongside its own procedural language), although it is not part of the core product.[6]

See also

Open source similar languages:

  • MySQL stored procedures (a procedural language that closely adheres to SQL/PSM[3])
  • PL/pgSQL (PostgreSQL language similar to SQL/PSM and PL/SQL)

Proprietary similar languages:

  • PL/SQL (Oracle proprietary language for stored procedures)
  • Transact-SQL (Microsoft and Sybase equivalent)

References

  1. ↑ Eisenberg, A. (1996). "New standard for stored procedures in SQL". ACM SIGMOD Record 25 (4): 81–88. doi:10.1145/245882.245907. 
  2. ↑ Jim Melton; Alan R. Simon (2002). SQL: 1999. Morgan Kaufmann. pp. 541–542. ISBN 978-1-55860-456-8. 
  3. ↑ 3.0 3.1 Guy Harrison; Steven Feuerstein (2008). MySQL Stored Procedure Programming. O'Reilly Media. p. 49. ISBN 978-0-596-10089-6. 
  4. ↑ feature T322
  5. ↑ See git's repository of plpsm0, 2011-05's announce, 2012-02's Proposal PL/pgPSM announce and 2008's Guide.
  6. ↑ PostgreSQL: Documentation: 9.2: SQL Conformance

Further reading

  • Jim Melton, Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM, Morgan Kaufmann Publishers, 1998, ISBN 1-55860-461-8
SQL
Versions
  • SQL-86
  • SQL-89
  • SQL-92
  • SQL:1999
  • SQL:2003
  • SQL:2006
  • SQL:2008
  • SQL:2011
Keywords
  • As
  • Case
  • Create
  • Delete
  • From
  • Having
  • Insert
  • Join
  • Merge
  • Null
  • Order by
  • Prepare
  • Select
  • Truncate
  • Union
  • Update
  • Where
  • With
Related
  • Edgar Codd
  • Relational database
ISO/IEC SQL parts
  • Framework
  • Foundation
  • Call-Level Interface
  • Persistent Stored Modules
  • Management of External Data
  • Object Language Bindings
  • Information and Definition Schemas
  • SQL Routines and Types for the Java Programming Language
  • XML-Related Specifications


This article is issued from Wikipedia. The text is available under the Creative Commons Attribution/Share Alike; additional terms may apply for the media files.