PL/SQL Basic Language

Preamble

PL/SQL – Despite being simple to learn and having many useful features, SQL does not support the creation of procedural constructions like those found in third-generation languages like C. Language PL/SQL is an extension of Oracle’s own SQL language and provides programming language functionality. One of its primary benefits is that it allows the use of program units such as procedures and packages in a database, increasing the possibility of code reuse and productivity.

PL/SQL base unit

A block is referred to as an executable program in PL/SQL. The following structures—four key operators, only two of which are required—make up a code block in PL/SQL, regardless of whether it is set as an anonymous free form block or enclosed within a program unit like a procedure.

• 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

Both variables and constants can be declared using the DECLARE operator. You must declare a variable before you can use it. In PL/SQL, a variable can be either a composite type like VARRAY or a built-in type like DATE, NUMBER, VARCHAR2, or CHAR. Additionally, PL/SQL continues to use data types like BOOLEAN and BINARY_INTEGER.

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

hired_date_id DATE;
emp_name_id VARCHAR2(30);

In addition to declaring variables, you can also declare constants, as in the example below:

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;

You can specify that the data type of a record (row) must match the data type of a specific database table by using the%ROWTYPE attribute. The DeptRecord record, for instance, must have exactly the same columns as the department table, with the same data types and lengths. This is demonstrated by the code below:

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. However, you should keep in mind the features that will be covered in more detail in the following sections when using the SELECT and INSERT operators in PL/SQL.

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

The same INSERT, DELETE, and UPDATE operators used in regular SQL also function in PL/SQL. However, you can also use the COMMIT operator in PL/SQL after each of them, as shown below:

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

Error handling

Any error or warning is referred to as an exception in PL/SQL. PL/SQL has some internally defined errors, but also allows you to define your own. Any error results in the triggering of an exception, which transfers control to the PL/SQL program section in charge of handling exceptions. If you make your own error situations, you must use a special RAISE operator to make sure that exceptions are thrown.

Here is an illustration of how to handle exceptions using the RAISE operator:

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

Some types of control structures are available in PL/SQL that allow for code iteration or conditional execution of specific operators. All of them are briefly described in the following sections of my blog.

Conditional control

Operator IF is the main type of conditional control structure in PL/SQL. It allows operators to be run based on certain conditions. 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

In PL/SQL, cyclic designs enable iterative code execution until a specific condition is either true or false, or once. The following subsections describe the main types of these constructs.

Simple loop

For the simple loop design, you put a set of SQL statements between the LOOP and END LOOP keywords. 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 used, the logic between the operators LOOP and END LOOP is used to figure out when a cycle should end.

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. Inside WHEN, a condition is set, and the cycle ends when this condition is met. 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 specifies that a particular operator must be used while a specific condition is true. Note that the condition is calculated outside of the loop. It is calculated whenever the operators listed between LOOP and END LOOP are executed. It ends the loop when the condition is no longer true. 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

When an operator needs to be done a certain number of times, the FOR cycle is used. It mimics the conventional do loop found in the majority of 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 might include fields, and each field might stand in for a different element. By using the attribute ROW%TYPE to declare a record as a column of a certain table, which means using the table as a cursor template, it is possible to make and own records. Here is a straightforward record example:

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

In Oracle, a cursor is a pointer to a region of memory that houses the set of SQL queries that are the result, allowing you to process each string in the set separately. Cursors created and used by application developers are known as explicit cursors, whereas Oracle uses implicit cursors when running DML-operators.

Implicit cursors

When the SELECT operator is used in PL/SQL code, Oracle automatically sets up implicit cursors. They can only be used in operators that return a single line. If an SQL statement returns more than one line, an error message will be made.

In the following PL/SQL code block, the operator SELECT, for example, provides the 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

The app developer makes explicit cursors, which make it easier to work with a set of lines that can be processed one after the other. 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 in the DECLARE section. This is different from the implicit cursor, which should never be used 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 illustration provided in Listing A.4, the cycle’s expected end point is indicated by a special cursor attribute of %NOTFOUND. When working with explicit cursors, cursor attributes are extremely helpful. The cursors that are most crucial 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

Typically, when using explicit cursors, you must open the cursor, extract the desired data, and then close the cursor. These opening, extraction, and closing processes can all be carried out automatically and with great ease using the FOR cursor loop. An illustration of a FOR Cursor Cycle design is provided in Listing A.5.

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

The current string in the resulting multi-line set is referenced by cursor variables. But a cursor variable is different from a normal cursor in that it can be given new values and passed to other procedures and functions. The following is how to create cursor variables in PL/SQL.

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

DECLARE
     TYPE EmpCurTyp_id IS REF CURSOR RETURN dept%ROWTYPE;

Then, cursor variables of type EmpCurType 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

PL/SQL procedures can be used to execute different DML operations. An example of a basic Oracle procedure is provided below:

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;
/

Functions in PL/SQL return a value as opposed to procedures, as demonstrated in the example below:

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;

Oracle packages are objects that typically include several interconnected procedures and functions. A package’s interrelated procedures and functions are typically called in order to carry out an application function. Large amounts of function code and the ability for multiple users to execute them at once make packages incredibly strong.

Each package usually consists of two parts: a specification and a body. The variables, cursors, and subroutines (procedures and functions) that make up a package are all listed in the specification, and the body of the package contains the actual source code for these cursors and subroutines.

Listing A.6 shows an example of a basic 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

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.