Jump to content

SQL/PSM

From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by 189.62.91.148 (talk) at 11:43, 11 November 2013. The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

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]

A PostgreSQL addon implements SQL/PSM[4] (alongside its own procedural language), although it is not part of the core product.[5]

See also

Open source similar languages:

  • PL/pgSQL (PostgreSQL language similar to SQL/PSM and PL/SQL)

Proprietary languages:

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

References

  1. ^ Attention: This template ({{cite doi}}) is deprecated. To cite the publication identified by doi:10.1145/245882.245907, please use {{cite journal}} (if it was published in a bona fide academic journal, otherwise {{cite report}} with |doi=10.1145/245882.245907 instead.
  2. ^ Jim Melton; Alan R. Simon (2002). SQL: 1999. Morgan Kaufmann. pp. 541–542. ISBN 978-1-55860-456-8.
  3. ^ Guy Harrison; Steven Feuerstein (2008). MySQL Stored Procedure Programming. O'Reilly Media. p. 49. ISBN 978-0-596-10089-6.
  4. ^ http://archives.postgresql.org/pgsql-announce/2011-05/msg00012.php; http://archives.postgresql.org/pgsql-hackers/2012-02/msg01042.php
  5. ^ http://www.postgresql.org/docs/9.2/static/features.html

Further reading

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


Stub icon

This programming-language-related article is a stub. You can help Wikipedia by expanding it.

Stub icon

This database-related article is a stub. You can help Wikipedia by expanding it.

Retrieved from "https://en.wikipedia.org/w/index.php?title=SQL/PSM&oldid=581170778"