imported>WikiCleanerBot: v2.05b - Bot T20 CW#61 - Fix errors for CW project (Reference before punctuation)

2023-06-03T20:38:00Z

v2.05b - Bot T20 CW#61 - Fix errors for CW project (Reference before punctuation)

New page

{{infobox programming language
| name = SQL/PSM
| logo =
| paradigm = [[multi-paradigm programming language|Multi-paradigm]]
| year = 1996
| designer =
| developer =
| latest_release_version = [[SQL:2023]]
| latest_release_date =
| latest_preview_version =
| latest_preview_date =
| turing-complete = Yes
| typing =
| implementations = [[PL/SQL]]<br/> [[MySQL]]/[[MariaDB]] <br />IBM's [[SQL PL]]<br />[[Mimer SQL]]
| influenced_by = [[PL/SQL]] <br /> [[Ada (programming language)|Ada]]<ref>{{Citation | url = http://ocelot.ca/blog/blog/2015/01/15/stored-procedures-critiques-and-defences/ | title = Stored Procedures: critiques and defences | year = 2015 | first1 = Peter | last1 = Gulutzan }}</ref>
| influenced =
| operating_system = [[Cross-platform|Cross-platform (multi-platform)]]
| license =
| website =
| file_ext =
| dialects =
| wikibooks =
}}

'''SQL/PSM''' ([[SQL]]/Persistent Stored Modules) is an [[ISO standard]] mainly defining an extension of SQL with a [[procedural language]] for use in [[stored procedure]]s. 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<ref>{{Cite journal | last1 = Eisenberg | first1 = A. | title = New standard for stored procedures in SQL | doi = 10.1145/245882.245907 | journal = ACM SIGMOD Record | volume = 25 | issue = 4 | pages = 81–88| year = 1996 | s2cid = 13023149 | doi-access = free }}</ref>), 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]].<ref>{{Citation | url = https://www.iso.org/standard/76585.html | contribution = SQL:2023 | title = Catalogue | publisher = ISO | format = webshop}}</ref> 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.<ref>{{cite book| first1 = Jim| last1 = Melton| first2 = Alan R| last2 = Simon| title = SQL: 1999| year = 2002| publisher = Morgan Kaufmann| isbn = 978-1-55860-456-8| pages = [https://archive.org/details/sql1999understan0000melt/page/541 541–42]| url-access = registration| url = https://archive.org/details/sql1999understan0000melt/page/541}}</ref> 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 [[Cursor (databases)|cursors]]. It also defines an information schema ([[metadata]]) for stored procedures. SQL/PSM is one language in which [[Method (computer programming)|methods]] for the SQL:1999 [[structured type]]s 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 [[United States Department of Defense|US Department of Defense]]'s [[Ada (programming language)|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<ref>{{Cite web|url=https://developer.mimer.com/article/stored-procedures-in-mimer-sql/|title = Stored Procedures in Mimer SQL}}</ref> 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.
<ref name = "HarrisonFeuerstein2008">{{cite book | first1 = Guy | last1 = Harrison| first2 = Steven | last2 = Feuerstein|title=MySQL Stored Procedure Programming|url= https://books.google.com/books?id=YpeP0ok0cO4C&pg=PT75 | year=2008|publisher=O'Reilly |isbn = 978-0-596-10089-6 |page= 49}}</ref>
<ref name="MariaDB Stored Procedure">{{Cite web|url=https://mariadb.com/kb/en/library/stored-procedures/|title=Stored Procedures|website=MariaDB KnowledgeBase}}</ref> However, a [[PostgreSQL]] addon implements SQL/PSM<ref>{{Citation | url = https://github.com/okbob/plpsm0 | format = git | type = repository | title = plpsm0| date = 7 July 2020 }}.</ref><ref>{{Citation | publisher = PostgreSQL | url = http://www.postgresql.org/message-id/1305291347.14548.13.camel@jara.office.nic.cz | date = May 2011 | title = Announce}}.</ref><ref>{{Cite web|url=https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com|title=PostgreSQL: Proposal: PL/pgPSM for 9.3|website=www.postgresql.org|date=22 February 2012}}</ref><ref>{{Citation | title = SQL/PSM | format = wigl | url = http://postgres.cz/wiki/SQL/PSM_Manual | publisher = PostgreSQL | type = manual | year = 2008}}.</ref> (alongside its other procedural languages like the PL/SQL-derived plpgsql), although it is not part of the core product.<ref>{{Citation | contribution-url = http://www.postgresql.org/docs/9.2/static/features.html | publisher = PostgreSQL | title = Documentation | edition = 9.2 | contribution = SQL Conformance}}.</ref>

[[Resource Description Framework|RDF]] functionality in [[Virtuoso Universal Server|OpenLink Virtuoso]] was developed entirely through SQL/PSM, combined with custom datatypes (e.g., <code>ANY</code> 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]]:
* [[HSQLDB]] stored procedures and functions<ref name="SQL/PSM routines">{{Cite web|url=http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_routines|title=Chapter 8. SQL-Invoked Routines|website=hsqldb.org}}</ref>
* [[MySQL]] stored procedures<ref name="HarrisonFeuerstein2008" />
* [[MariaDB]] stored procedures<ref name="MariaDB Stored Procedure" />
* [[Virtuoso Universal Server|OpenLink Virtuoso]] SQL Procedures (VSP)<ref name="SQL Procedure Language Guide">{{Cite web|url=http://docs.openlinksw.com/virtuoso/ch-sqlprocedures/|title=Chapter 11. SQL Procedure Language Guide|website=docs.openlinksw.com}}</ref>
* [[PostgreSQL]] [[PL/pgSQL]]

Proprietary:
* [[Oracle Database|Oracle]] [[PL/SQL]]
* [[Microsoft SQL Server|Microsoft]] and [[Sybase ASE|Sybase]] [[Transact-SQL]]
* Invantive Procedural SQL<ref>{{Cite web|url=https://documentation.invantive.com/2017R2/invantive-sql-grammar/invantive-sql-grammar-17.30.html#pSqlBlock|title=Invantive SQL v2.0 Grammar|website=Invantive}}</ref>
* [[Mimer SQL]] stored procedures <ref name="Mimer SQL PSM">{{Cite web|url=https://docs.mimer.com/MimerSqlManual/latest/Manuals/PSM/PSM.htm|title=Mimer SQL Stored Procedures|website=docs.mimer.com}}</ref>

==References==
{{reflist}}

==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}}

__NOTOC__

{{DEFAULTSORT:SQL PSM}}
[[Category:Data management]]
[[Category:SQL]]
[[Category:Data-centric programming languages]]
[[Category:Programming languages created in 1996]]

SQL/PSM - Revision history

imported>WikiCleanerBot: v2.05b - Bot T20 CW#61 - Fix errors for CW project (Reference before punctuation)