What is PL/SQL?
PL/SQL stands for Procedural Language/Structured Query Language. It is a combination of SQL with procedural features of programming language. It stored and compiled in the database, runs within the Oracle executable and inherits the security, robustness, and portability of the Oracle Database.
1.1 Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:
■ Tight Integration with SQL
■ Better Performance
■ Higher Productivity
■ Full Portability
■ Tight Security
■ Access to Pre-defined Packages
■ Support for Object-Oriented Programming
■ Support for Developing Web Applications and Pages
1.2 The Main Features of PL/SQL
PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements like IF and LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap runtime errors.
The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can be nested inside one another. A block group related declarations and statements. You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures.
-
PL/SQL Variables and Constants
PL/SQL lets you declare variables and constants, then use them in SQL and procedural statements anywhere an expression can be used. You must declare a constant or variable before referencing it in any other statements.
Example:-
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);
-
Assigning Values to a Variable
You can assign values to a variable in three ways:-
- The first way uses the assignment operator (:=), a colon followed by an equal sign.
- The second way to assign values to a variable is by selecting (or fetching) database values into it.
- The third way to assign a value to a variable is bypassing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.
When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically.
Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. No further assignments to the constant are allowed.
The following example declares a constant:
credit_limit CONSTANT NUMBER := 5000.00;
-
Processing Queries with PL/SQL
Example:- Processing Query Results in a LOOP
BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
', Last name = ' || someone.last_name);
END LOOP;
END;
/
Declaring PL/SQL Subprograms
Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has two types of subprograms: procedures and functions. The following is an example of a declaration of a PL/SQL procedure:
DECLARE
in_string VARCHAR2(100) := 'This is my test string.';
out_string VARCHAR2(200);
PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS
BEGIN
new_string := original || original;
END;
Writing Reusable PL/SQL Code
PL/SQL lets you break an application down into manageable, well-defined modules. PL/SQL meets this need with program units, which include blocks, subprograms, and packages. You can reuse program units by loading them into the database as triggers, stored procedures, and stored functions.
Inputting and Outputting Data with PL/SQL
Most PL/SQL input and output is through SQL statements, to store data in database tables or query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as
SQL*Plus, to read and display the data passed to DBMS_OUTPUT.
SQL*Plus does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON as follows:
SET SERVEROUTPUT ON
PL/SQL Data Abstraction
Data abstraction lets you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.
A cursor is a name for a specific private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors.
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a TYPE definition.
Records are composite data structures whose fields can have different data types. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use a TYPE definition.
An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. Object types reduce complexity by breaking down a large system into logical entities.
PL/SQL Error Handling
PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.
PL/SQL Architecture
The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed on an Oracle server or in an application development tool such as Oracle Forms.
