SQL/PSM

Last updated
SQL/PSM
Paradigm Multi-paradigm
First appeared1996
Stable release
OS Cross-platform (multi-platform)
Major implementations
PL/SQL
MySQL/MariaDB
IBM's SQL PL
Mimer SQL
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.

Contents

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:

Related Research Articles

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source relational database management system

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.

Structured Query Language (SQL), is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

<span class="mw-page-title-main">PL/pgSQL</span>

PL/pgSQL is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Version 9 also implements some ISO SQL/PSM features, like overloading of SQL-invoked functions and procedures.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

<span class="mw-page-title-main">Null (SQL)</span> Marker used in SQL databases to indicate a value does not exist

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, NULL is a reserved word used to identify this marker.

SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. Aside from a few minor incompatibilities, the SQL-89 standard is forward-compatible with SQL-92.

A spatial database is a general-purpose database that has been enhanced to include spatial data that represents objects defined in a geometric space, along with tools for querying and analyzing such data. Most spatial databases allow the representation of simple geometric objects such as points, lines and polygons. Some spatial databases handle more complex structures such as 3D objects, topological coverages, linear networks, and triangulated irregular networks (TINs). While typical databases have developed to manage various numeric and character types of data, such databases require additional functionality to process spatial data types efficiently, and developers have often added geometry or feature data types. The Open Geospatial Consortium (OGC) developed the Simple Features specification and sets standards for adding spatial functionality to database systems. The SQL/MM Spatial ISO/IEC standard is a part of the SQL/MM multimedia standard and extends the Simple Features standard with data types that support circular interpolations. Almost all current relational and object-relational database management systems now have spatial extensions, and some GIS software vendors have developed their own spatial extensions to database management systems.

SQLJ is a working title for efforts to combine Java and SQL. It was a common effort started around 1997 by engineers from IBM, Oracle, Compaq, Informix, Sybase, Cloudscape and Sun Microsystems.

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code, of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as C/C++, COBOL and Fortran. This differs from SQL-derived programming languages that don't go through discrete preprocessors, such as PL/SQL and T-SQL.

In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information that some databases make available through non-standard commands, such as:

 => SELECT count(table_name) FROM information_schema.tables;  count   -------  99    => SELECT column_name, data_type, column_default, is_nullable  FROM information_schema.columns WHERE table_name='alpha';  column_name | data_type | column_default | is_nullable   -------------+-----------+----------------+-------------  foo | integer | | YES  bar | character | | YES    => SELECT * FROM information_schema.information_schema_catalog_name;  catalog_name   --------------  johnd

SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM Db2 database system. It provides procedural programmability in addition to the querying commands of SQL. It is a subset of the SQL Persistent Stored Modules (SQL/PSM) language standard.

SQL/OLB, or Object Language Bindings, is a standard for embedding SQL in Java, commonly known by its prior name as SQLJ. Besides describing the syntax and semantics of SQLJ, which are typically given relative to JDBC, the standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes.

SQL/JRT, or SQL Routines and Types for the Java Programming Language, is an extension to the SQL standard first published as ISO/IEC 9075-13:2002. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications, commonly referred to as "Java stored procedures". SQL/JRT also calls for the ability to use Java classes as SQL structured user-defined types. The two parts of the extension originate from the earlier ANSI SQLJ part 1 and 2 standards

SQL/XML or XML-Related Specifications is part 14 of the Structured Query Language (SQL) specification. In addition to the traditional predefined SQL data types like NUMERIC, CHAR, TIMESTAMP, ... it introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.

SQL:1999 was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile SQL:1999 is deprecated.

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

Object-PL/SQL is a methodology of using the Oracle Corporation's procedural extension language for SQL and the Oracle relational database. The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the object-oriented database paradigm.

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. 1 2 Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN   978-0-596-10089-6.
  7. 1 2 "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

This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.