Page 219 - Open Soource Technologies 304.indd

Open Source Technologies

Notes CALL procedure(...)
or

EXECUTE procedure(...)
Stored procedures may return result sets, i.e. the results of a SELECT statement.
Such result sets can be processed using cursors, by other stored procedures,
by associating a result set locator, or by applications. Stored procedures may
also contain declared variables for processing data and cursors that allow it to
loop through multiple rows in a table. Stored procedure languages typically
include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored
procedures can receive variables, return results or modify variables and return
them, depending on how and where the variable is declared.
13.2.1 Implementation

The exact and correct implementation of stored procedure varies from one database system to
another. Most major database vendors support them in some form. Depending on the database
ystem, stored procedures can be implemented in a variety of programming languages, for
example SQL, Java, C, or C++. Stored procedures written in non-SQL programming languages
may or may not execute SQL statements themselves.
The increasing adoption of stored procedures led to the introduction of procedural elements to
the SQL language in the SQL:1999 and SQL:2003 standards in the part SQL/PSM. That made
SQL an imperative programming language. Most database systems offer proprietary and
vendor-specific extensions, exceeding SQL/PSM.
Database system Implementation language

• Microsoft SQLServer Transact-SQL and various .NET Framework languages

• Oracle PL/SQL or Java
• DB2 SQL/PL or Java
• Informix SPL

• Postgre SQL PL/pg SQL, can also use own function languages such
as pl/perl or pl/php

• Fire bird PSQL (Fyracle also supports portions of Oracle’s PL/
SQL)

• My SQL Own stored procedures, closely adhering to SQL:
2003 standard.

13.2.2 Other Uses
In some systems stored procedures can be used to control transaction management; in others,
stored procedures run inside a transaction such that transactions are effectively transparent to
them. Stored procedures can also be invoked from a database trigger or a condition handler.
For example, a stored procedure may be triggered by an insert on a specific table, or update of
a specific field in a table, and the code inside the stored procedure would be executed. Writing

214 LOVELY PROFESSIONAL UNIVERSITY