SQL/PSM

SQL/PSM
ParadigmMulti-paradigm
First appeared1996
Stable release
OSCross-platform (multi-platform)
Major implementations
PL/SQL
MySQL/MariaDB
IBM's SQL PL
Mimer SQL
PL/pgSQL
Influenced by
PL/SQL
Ada[1]

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[2]), 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:2023.[3] 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.[4] 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.

SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language. However, Oracle has maintained a distance from the standard in its documentation. IBM's SQL PL (used in DB2) and Mimer SQL's PSM[5] were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps also MySQL/MariaDB's procedural language, are closest to the SQL/PSM standard. [6] [7] However, a PostgreSQL addon implements SQL/PSM[8][9][10][11] (alongside its other procedural languages like the PL/SQL-derived plpgsql), although it is not part of the core product.[12]

RDF functionality in OpenLink Virtuoso was developed entirely through SQL/PSM, combined with custom datatypes (e.g., ANY for handling URI and Literal relation objects), sophisticated indexing, and flexible physical storage choices (column-wise or row-wise).

See also

The following implementations adopt the standard, but they are not 100% compatible to SQL/PSM:

Open source:

Proprietary:

References

  1. ^ Gulutzan, Peter (2015), Stored Procedures: critiques and defences
  2. ^ Eisenberg, A. (1996). "New standard for stored procedures in SQL". ACM SIGMOD Record. 25 (4): 81–88. doi:10.1145/245882.245907. S2CID 13023149.
  3. ^ "SQL:2023", Catalogue (webshop), ISO
  4. ^ Melton, Jim; Simon, Alan R (2002). SQL: 1999. Morgan Kaufmann. pp. 541–42. ISBN 978-1-55860-456-8.
  5. ^ "Stored Procedures in Mimer SQL".
  6. ^ a b Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN 978-0-596-10089-6.
  7. ^ a b "Stored Procedures". MariaDB KnowledgeBase.
  8. ^ plpsm0 (git) (repository), 7 July 2020.
  9. ^ Announce, PostgreSQL, May 2011.
  10. ^ "PostgreSQL: Proposal: PL/pgPSM for 9.3". www.postgresql.org. 22 February 2012.
  11. ^ SQL/PSM (wigl) (manual), PostgreSQL, 2008.
  12. ^ "SQL Conformance", Documentation (9.2 ed.), PostgreSQL.
  13. ^ "Chapter 8. SQL-Invoked Routines". hsqldb.org.
  14. ^ "Chapter 11. SQL Procedure Language Guide". docs.openlinksw.com.
  15. ^ "Invantive SQL v2.0 Grammar". Invantive.
  16. ^ "Mimer SQL Stored Procedures". docs.mimer.com.

Further reading

  • Jim Melton, Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM, Morgan Kaufmann Publishers, 1998, ISBN 1-55860-461-8

Content Disclaimer

Informasi ini disarikan dari Wikipedia dan disajikan kembali untuk tujuan edukasi. Konten tersedia di bawah lisensi CC BY-SA 3.0. Kami tidak bertanggung jawab atas ketidakakuratan data yang bersumber dari kontribusi publik tersebut.

  1. The information displayed on this website is sourced in part or in whole from Wikipedia and has been adapted for the purpose of restating it. We strive to provide accurate and relevant information, however:
  2. There is no guarantee of absolute accuracy. Wikipedia is an open, collaborative project that can be edited by anyone, so information is subject to change.
  3. It is not intended to constitute professional advice. The content displayed is for informational and educational purposes only. For important decisions (e.g., medical, legal, or financial), please consult a professional.
  4. Content copyright. Wikipedia is licensed under the Creative Commons Attribution-ShareAlike License (CC BY-SA). This means that content may be reused with appropriate attribution and shared under a similar license.
  5. Responsible use. Any risk arising from the use of information from this website is entirely the responsibility of the user.