Information is transmitted between a PL/SQL program and the database through variables. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation.
Every variable has a specific type associated with it. In this case, it is essential that the variable have the same type as the relation column.
*/ /* This is the only section of the block that is required.
*/ EXCEPTION /* Exception handling section: error handling statements go here. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). C style comments ( or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database.
PL/SQL is available in Oracle Database (since version 6 - stored pl/sql procedures/functions/packages/triggers since version 7), Times Ten in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).
All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine.
There is an overhead associated with each context switch between the two engines.
After a TCL operation is performed, the cursor pointer gets reset and the cursor will be no longer accessible, thus results in an error when fetched further as shown below.
For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, Postgre SQL has PL/pg SQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language, which conforms to the ISO SQL’s SQL/PSM standard.
The designers of PL/SQL modeled its syntax on that of Ada.
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.
PL/SQL works analogously to the embedded procedural languages associated with other relational databases.