1 Overview Of Pl Sql

Main Features of PL/SQL

Block Structure

The basic units that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks.
A PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

Variables and Constants

Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or any PL/SQL datatype, such as BOOLEAN or BINARY_INTEGER.

Assigning Values to a Variable

Three ways:

The first way uses the assignment operator (:=)
Examples:

tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;

The second way to assign values to a variable is by selecting (or fetching) database values into it.

Example: Oracle computes a 10% bonus when you select the salary of an employee. Now, you can use the variable bonus in another computation or insert its value into a database table.

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

The third way to assign values to a variable is by passing it as an OUT or IN OUT parameter to a subprogram.

DECLARE
   my_sal REAL(7,2);
   PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
   SELECT AVG(sal) INTO my_sal FROM emp;
   adjust_salary(7788, my_sal); -- assigns a new value to my_sal

Declaring Constants

Example:

credit_limit CONSTANT REAL := 5000.00;

Cursors

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually.

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. An explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.

Cursor FOR Loops

A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component selector.

Example: The cursor FOR loop implicitly declares emp_rec as a record.

DECLARE
   CURSOR c1 IS
      SELECT ename, sal, hiredate, deptno FROM emp;
   ...
BEGIN
   FOR emp_rec IN c1 LOOP
      ...
      salary_total :=  salary_total + emp_rec.sal;
   END LOOP;

Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.