Jump to content

SQL/PSM

From Wikipedia, the free encyclopedia
SQL/PSM
ParadigmMulti-paradigm
First appeared1996
Stable release
OSCross-platform (multi-platform)
Majorimplementations
PL/SQL
MySQL/MariaDB
IBM'sSQL PL
Mimer SQL
Influenced by
PL/SQL
Ada[1]

SQL/PSM(SQL/Persistent Stored Modules) is anISO standardmainly defining an extension of SQL with aprocedural languagefor use instored procedures.Initially published in 1996 as an extension ofSQL-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-partSQL:1999standard, and has been part 4 of that standard since then, most recently inSQL: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 forcontrol flow,exception handling(called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use ofcursors.It also defines an information schema (metadata) for stored procedures. SQL/PSM is one language in whichmethodsfor the SQL:1999structured typescan be defined. The other is Java, viaSQL/JRT.

SQL/PSM is derived, seemingly directly, from Oracle'sPL/SQL.Oracle developed PL/SQL and released it in 1991, basing the language on theUS Department of Defense'sAda programming language.However, Oracle has maintained a distance from the standard in its documentation. IBM'sSQL PL(used in DB2) andMimer SQL's PSM[5]were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps alsoMySQL/MariaDB's procedural language, are closest to the SQL/PSM standard. [6] [7]However, aPostgreSQLaddon 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]

RDFfunctionality inOpenLink Virtuosowas developed entirely through SQL/PSM, combined with custom datatypes (e.g.,ANYfor handling URI and Literal relation objects), sophisticated inde xing, and flexible physical storage choices (column-wise or row-wise).

See also[edit]

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

Open source:

Proprietary:

References[edit]

  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.S2CID13023149.
  3. ^"SQL:2023",Catalogue(webshop),ISO
  4. ^Melton, Jim; Simon, Alan R (2002).SQL: 1999.Morgan Kaufmann. pp.541–42.ISBN978-1-55860-456-8.
  5. ^"Stored Procedures in Mimer SQL".
  6. ^abHarrison, Guy; Feuerstein, Steven (2008).MySQL Stored Procedure Programming.O'Reilly. p. 49.ISBN978-0-596-10089-6.
  7. ^ab"Stored Procedures".MariaDB KnowledgeBase.
  8. ^plpsm0(git)(repository), 7 July 2020.
  9. ^Announce,PostgreSQL, May 2011.
  10. ^"PostgreSQL: Proposal: PL/pgPSM for 9.3".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.
  15. ^"Invantive SQL v2.0 Grammar".Invantive.
  16. ^"Mimer SQL Stored Procedures".docs.mimer.

Further reading[edit]

  • Jim Melton,Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM,Morgan Kaufmann Publishers, 1998,ISBN1-55860-461-8
Retrieved from "https://en.wikipedia.org/w/index.php?title=SQL/PSM&oldid=1158396221"