Jump to content

SQL PL

Add links
From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by FredrikSande (talk | contribs) at 13:16, 15 March 2016. The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

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 (DB2 UDB Version 7) database system.[1] 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. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.[2]

IBM DB2

As of DB2 version 9, SQL PL stored procedures can run natively inside the DB2 process (inside the DBM1 address space, more precisely) instead of being fenced in an external process.[3] In DB2 version 9.7 IBM also added a PL/SQL front-end to this infrastructure (called "SQL Unified Runtime Engine"), meaning that procedural SQL using either the ISO standard or Oracle's syntax compile to bytecode running on the same engine in DB2.[4]

Raima RDM 14.0

The RDM SQL Programming Language (SQL PL) is based on the ANSI/ISO SQL Persistent Stored Modules (PSM) specification (ISO/IEC 90756-4:2008). It provides a high-level language in which stored procedures and functions can be easily written, compiled and called within the RDM SQL system. In earlier versions of RDM SQL, except for a rudimentary stored procedure capability, all such programming needed to be done through a standard programming language API such as either the native RDM SQL API or ODBC for C/C++ or JDBC for Java programs.[5]

Similar Languages

PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard.

The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascalprogram structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3]

PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.

PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7]

The StepSqlite product is a PL/SQL compiler for the popular small database SQLite.

References

  1. ^ IBM Info Center
  2. ^ "PL/SQL". Wikipedia, the free encyclopedia.
  3. ^ Craig S. Mullins (2012). DB2 Developer's Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS (6th ed.). IBM Press. p. 658. ISBN 978-0-13-283644-9.
  4. ^ Serge Rielau. "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows". Ibm.com. Retrieved 2012-07-26.
  5. ^ "Raima Homepage".

Software in SQL-PL

  • log4db2: A logging framework written in SQL-PL for IBM DB2 LUW
  • db2unit: a testing framework for the SQL-PL language


Stub icon

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

Retrieved from "https://en.wikipedia.org/w/index.php?title=SQL_PL&oldid=710184847"