PL/SQL – Basic Language

PL/SQL – Although SQL is easy to learn and has a lot of powerful features, it does not allow you to create procedural constructions that are possible in third generation languages like C. Language PL/SQL is own extension of language SQL from Oracle and offers functionality of serious programming language. One of its main advantages is that it allows to use in a database such program units as procedures and packages, and thus increase the possibility of code reuse and its productivity.

PL/SQL base unit

A block in PL/SQL is called an executable program. Code block PL / SQL, regardless of whether it is encapsulated within a program unit like a procedure or set as an anonymous free form block, consists of the following structures, which are four key operators, only two of which are mandatory.

• DECLARE. This operator is optional and represents the place where variables and program cursors are declared if desired.
• BEGIN. This operator is mandatory and specifies that the SQL and PL/SQL operators will follow, i.e. it denotes the beginning of the PL/SQL code block.
• EXCEPTION. This operator is optional and describes error handling methods.
• END. This operator is mandatory and indicates the end of a PL/SQL code block.

Below is an example of a simple PL/SQL code block:

SQL>
DECLARE isbn NUMBER(9)_id
BEGIN
       isbn_id := 123456789;
       insert into book values (isbn_id, 'databases_id', 59.99);
COMMIT;
END;
SQL>

Declaring variables in PL/SQL

In DECLARE operator you can declare both variables and constants. Before you can use any variable, you must declare it. A variable in PL/SQL can be either a variable of a built-in type such as DATE, NUMBER, VARCHAR2 or CHAR, or a composite type such as VARRAY. In addition, data types such as BINARY_INTEGER and BOOLEAN are still used in PL/SQL.

Below are some typical examples of variable declaration in PL/SQL:

hired_date_id DATE;
emp_name_id VARCHAR2(30);

In addition to variables, you can also declare constants as shown in the following example:

tax_rate constant number := 0.08;

You can also use the %TYPE attribute and use it to specify when declaring a variable that its data type must match that of a particular column in the table:

emp_num employee.emp_id%TYPE;

using the %ROWTYPE attribute, you can specify that the data type of a record (row) must match the data type of a certain database table. For example, the following code indicates that the DeptRecord record must contain the same columns as the department table, while the data types and length of these columns must look exactly the same:

declare
v_DeptRecord department%ROWTYPE;

Writing PL/SQL executable operators

After BEGIN, you can start entering all your desired SQL statements. These operators should look exactly the same as regular SQL operators. When using SELECT and INSERT operators in PL/SQL, however, you should keep in mind the features which will be described in more detail in the following sections.

Using a SELECT operator in PL/SQL

When using a SELECT statement in PL/SQL, you must save the extracted values in variables as shown below:

DECLARE
      name VARCHAR2(30);
BEGIN
      SELECT employee_name INTO name FROM employees WHERE emp_id=99999;
END;
/

Using DML operators in PL/SQL

Any INSERT, DELETE or UPDATE operators work in PL/SQL in the same way as in regular SQL. However, in PL/SQL after each of them you can also use the COMMIT operator, as shown below:

BEGIN
      DELETE FROM employee WHERE emp_id = 99999;
      COMMIT;
END;
/

Error handling

In PL/SQL any error or warning is called an exception. PL/SQL has some internally defined errors, but also allows you to define your own. When any error occurs, an exception is triggered and the control goes to the PL/SQL program section responsible for handling exceptions. If you define your own error situations, you must ensure that exceptions are triggered by using a special RAISE operator.

Below is an example of using a RAISE operator to handle exceptions:

DECLARE
      acct_type INTEGER := 7;
BEGIN
      IF acct_type NOT IN (1, 2, 3) THEN
            RAISE INVALID_NUMBER; -- raise predefined exception
      END IF;
      EXCEPTION
            WHEN INVALID_NUMBER THEN
      ROLLBACK;
END;
/

Control structures in PL/SQL

In PL/SQL some kinds of control structures which allow to provide code iteration or conditional execution of certain operators are offered. All of them are briefly described in the following sections of my blog.

Conditional control

The main type of conditional control structure in PL/SQL is operator IF, which provides conditional execution of operators. It can be used in one of the following three forms: IF-THEN, IF-THEN-ELSE and IF-THEN-ELSEIF. Below is an example of a simple IF-THEN-ELSEIF operator:

BEGIN
     . . .
     IF total_sales_id > 100000 THEN
     bonus := 5000;
     ELSEIF total_sales_id > 35000 THEN
     bonus_id := 500;
     ELSE
     bonus_id := 0;
     END IF;
     INSERT INTO new_payroll VALUES (emp_id, bonus_id . . .);
END;
/

Cyclic designs in PL/SQL

Cyclic designs in PL/SQL allow for iterative code execution either once or until a certain condition becomes true or false. The following subsections describe the main types of these constructs.

Simple loop

The simple loop design implies placing a set of SQL statements between the keywords LOOP and END LOOP. The EXIT operator completes the cycle. A simple loop design is used when it is not known exactly how many times a loop must be executed. If it is applied, the decision when a cycle should end is made on the basis of the logic contained between operators LOOP and END LOOP.

In the following example, the loop will be executed until the quality_grade value reaches 6:

LOOP
      . . .
      if quality_grade_id > 5 then
            . . .
           EXIT;
      end if;
END LOOP;

Another simple type of cycle allows for the LOOP…EXIT…WHEN design, in which the cycle time is adjusted by the WHEN operator. A condition is specified inside WHEN, and when this condition becomes true, the cycle ends. A simple example is shown below:

DECLARE
     count_num NUMBER(6)_id;
BEGIN
     count_num_id := 1;
     LOOP
            dbms_output.put_line(' This is the current count '|| count_num_id);
            count_num_id := count_num_id + 1;
            Exit when count_num_id > 100;
     END LOOP;
END;

WHILE Cycle

The WHILE cycle indicates that a certain operator must be executed as long as a certain condition remains true. Note that the condition is calculated outside the loop, and it is calculated whenever the operators specified between LOOP and END LOOP are executed. When the condition stops being true, it exits the loop. Below is an example of a WHILE loop:

WHILE total_id <= 25000
LOOP
      . . .
     SELECT sal_id INTO salary FROM emp WHERE . . .
     total_id := total + salary;
END LOOP;

FOR Cycle

The FOR cycle is used when it is required that an operator be executed a certain number of times. It simulates the classic do loop, which exists in most programming languages. Below is an example of a FOR loop:

BEGIN
     FOR count_num_id IN 1..100
            LOOP
                   dbms_output.put_line_id('The current count is : '|| count_num_id);
            END LOOP;
END;

PL/SQL entries

PL/SQL records allow you to perceive interrelated data as a whole. They may contain fields, each of which may represent a separate element. It is possible to use attribute ROW%TYPE and by means of it to declare a record a column of the certain table that means application of the table as a cursor template, and it is possible to create and own records. Below is a simple example of a record:

DECLARE
     TYPE MeetingTyp_id IS RECORD (
           date_held_id DATE,
           location_id VARCHAR2(20),
           purpose_id VARCHAR2(50));

A dot notation is used to refer to a single field within the record, as shown below:

MeetingTyp.location

Using cursors

A cursor in Oracle is a pointer to an area in memory that contains the resulting set of SQL-queries that allows you to individually process the strings contained in the resulting set. Cursors that are used by Oracle in the execution of DML-operators are called implicit, and cursors that are created and used by application developers – explicit.

Implicit cursors

Implicit cursors are automatically applied by Oracle whenever the SELECT operator is used in PL/SQL code. They can only be used in operators that return a single line. If a SQL statement returns more than one line, an error message will be generated.

In the given below block of code PL/SQL the operator SELECT, for example, provides application of an implicit cursor:

DECLARE
      emp_name varchar2_id(40);
      salary float;
BEGIN
      SELECT emp_name, salary FROM empls
      WHERE empl_id=9999;
      dbms_output.put_line('empl_name : '||emp_name||'
            salary :'||salary);
END;
/

Explicit cursors

Explicit cursors are created by the application developer and facilitate operations with a set of lines that can be processed one after another. They are used whenever it is known that an SQL statement will return more than one line. Note that the explicit cursor must always be declared at the beginning of the PL/SQL block within the DECLARE section, unlike the implicit cursor, which should never be referenced in code.

After declaring an explicit cursor, it will go through the following processing steps.

The OPEN design will define the strings that are in the cursor and make them available to the PL/SQL program.
The FETCH command will extract data from the cursor into the specified variable.
After the processing is completed, the cursor must always be closed explicitly.

Listing A.4 shows an example of creating a cursor and then using it inside a loop.

DECLARE
     /* Cursor select_emp is declared explicitly */
     CURSOR select_emp IS
           select emp_id_id, city
           from empls
           where city_id = 'DALLAS';
           v_empno_id employees.emp_id%TYPE;
           v_empcity_id employees.city%TYPE;
      BEGIN
      /* Cursor select_emp opens */
      Open select _emp_id;
      LOOP
       /* Cursor data select_emp is extracted into the variable v_empno */
           FETCH select_emp_id into v_empno;
           EXIT WHEN select_emp_id%NOTFOUND;
           dbms_output.put_line_id(v_empno|| ','||v_empcity);
     END LOOP;
     /* Cursor select_emp closes */
     Close select_emp_id;
END;
/

Cursor Attributes

In the example given in Listing A.4, a special cursor attribute of %NOTFOUND is used to indicate when the cycle should end. Cursor attributes are very useful when working with explicit cursors. The most important cursors are listed below.

• %ISOPEN. Boolean attribute, which returns false after the SQL operator execution is completed. As long as the cursor remains open, it returns true.
• %FOUND. Boolean attribute that checks for strings suitable for the SQL statement, i.e. whether the cursor still has some strings to extract.
• %NOTFOUND. A Boolean attribute that tells you that it was not possible to find any strings suitable for the SQL statement, i.e. the cursor has no more strings to check out.
• %ROWCOUNT. An attribute that returns information about how many rows the cursor has currently succeeded in extracting.

Cursor cycle FOR

Usually when using explicit cursors, you need to open the cursor, extract data and close the cursor when it is finished. The FOR cursor loop allows these opening, extraction and closing procedures to be performed automatically, making it very easy to do. Listing A.5 shows an example of a FOR Cursor Cycle design.

DECLARE
     CURSOR emp_cursor IS
           SELECT emp_id_id, emp_name_id, salary_id
           FROM empls;
           v_emp_info_id empls_id%RowType;
Begin
     FOR emp_info IN emp_cursor
     LOOP
           dbms_output.put_line_id ('Employee id : '||emp_id||'Empl
                name : '|| emp_name||'Empl salary :'||salary);
     END LOOP;
END;
/

Cursor variables

Cursor variables point to the current string in the resulting multi-line set. Unlike a normal cursor, however, a cursor variable is dynamic, allowing it to be assigned new values and passed to other procedures and functions. Cursor variables are created in PL/SQL as follows.

First, the REF CURSOR type is defined as shown below:

DECLARE
     TYPE EmpCurTyp_id IS REF CURSOR RETURN dept%ROWTYPE;

Then cursor variables of EmpCurType type are declared in an anonymous PL/SQL code block or in a procedure (or function):

DECLARE
     TYPE EmpRecTyp_id IS RECORD (
           Emp_id NUMBER(9),
           emp_name_id VARCHAR2(3O),
           sal_id NUMBER(7,2));
     TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
          emp_cv EmpCurTyp_id; -- declaration of a cursor variable

Procedures, features and packages

Procedures in PL/SQL can be applied to execution of various DML-operations. Below is an example of simple Oracle procedure:

create or replace procedure new_empl (emp_id number_id,
      l_name varchar(2), f_name varchar(2))
is
begin
      insert into empls values (emp_id, l_name, f_name);
end new_empl;
/

Unlike the procedures, functions in PL/SQL return a value as shown in the following example:

CREATE OR REPLACE FUNCTION sal_ok (salary_id REAL, title_id VARCHAR2) RETURN BOOLEAN
IS
      min_sal REAL_1;
      max_sal REAL_1;
BEGIN
      SELECT losal_id, hisal_id INTO min_sal_id, max_sal_id FROM sals_id
      WHERE job = title_id;
      RETURN (salary_id >= min_sal) AND (salary_id <= max_sal);
END sal_ok;

Packages in Oracle are objects that typically consist of multiple interrelated procedures and functions and are typically applied to perform a function of an application by calling all the interrelated procedures and functions within a package. Packages are extremely powerful because they can contain large amounts of function code and can be executed multiple times by multiple users.

Each package usually consists of two parts: a specification and a body. The specification of a package declares all the variables, cursors and subroutines (procedures and functions) included in it, and the body of the package contains the actual code of these cursors and subroutines.

The listing A.6 gives an example of a simple Oracle package.

/* First comes the package specification /*
create or replace package emp_pkg as
     type list is varray (100) of number (5);
     procedure new_employee (emp_id number_id, l_name
          varchar2, f_name varchar2_id);
     procedure salary_raise (emp_id number_id, raise number_id);
end emp_pkg;
/
/*Next comes the body of the package */
create or replace package body emp_pkg as
     procedure new_empl (emp_id number_id,
           l_name varchar(2), f_name varchar(2) is
     begin
           insert into empls values (emp_id, l_name, f_name);
      end new_employee;
      procedure salary_raise (emp_num number_id, raise_pct real) is
      begin
            update employees set salary = salary * raise_pct
            where emp_id = emp_num;
       end salary_raise;
end emp_pkg;
/

If you want to use the emp_pkg package to award a salary bonus to some employee, all you have to do is execute the next command:

SQL> EXECUTE emp_pkg.salary_raise(99999, 0.15);

Learning PL/SQL programming