14.4. Basic Statements

This section begins the discussion of the programming statements that can be used in an SPL program.

14.4.1. NULL

The simplest statement is the NULL statement. This statement is an executable statement that does nothing.

NULL;

The following is the simplest, possible valid SPL program.

BEGIN
    NULL;
END;

The NULL statement can act as a placeholder where an executable statement is required such as in a branch of an IF-THEN-ELSE statement.

For example:

CREATE OR REPLACE PROCEDURE divide_it (
    p_numerator     IN  NUMBER,
    p_denominator   IN  NUMBER,
    p_result        OUT NUMBER
)
IS
BEGIN
    IF p_denominator = 0 THEN
        NULL;
    ELSE
        p_result := p_numerator / p_denominator;
    END IF;
END;

The assignment statement sets a variable or a formal parameter of mode OUT or IN OUT specified on the left side of the assignment, :=, to the evaluated expression specified on the right side of the assignment.

variable := expression;

variable is an identifier for a previously declared variable, OUT formal parameter, or IN OUT formal parameter. expression is an expression that produces a single value. The value produced by the expression must have a compatible data type with that of variable. See Chapter 9 for a discussion of data type compatibility and conversion.

While the dept_salary_rpt example in Variable Declaration showed assignment statements used in variable declarations, a variation of this example shows the typical use of assignment statements in the executable section of the procedure.

CREATE OR REPLACE PROCEDURE dept_salary_rpt (
    p_deptno        NUMBER
)
IS
    todays_date     DATE;
    rpt_title       VARCHAR2(40);
    base_sal        INTEGER;
    base_comm_rate  NUMBER;
    base_annual     NUMBER;
BEGIN
    todays_date := SYSDATE;
    rpt_title := 'Report For Department # ' || p_deptno || ' on ' || todays_date;
    base_sal := 35525;
    base_comm_rate := 1.33333;
    base_annual := ROUND(base_sal * base_comm_rate, 2);

    DBMS_OUTPUT.PUT_LINE(rpt_title);
    DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;

14.4.3. `SELECT INTO`

The SELECT INTO statement is an SPL variation of the SQL SELECT command, the differences being:

That SELECT INTO is designed to assign the results to variables or records where they can then be used in SPL program statements.
The accessible result set of SELECT INTO is at most one row.

Other than the above, all of the clauses of the SELECT command such as WHERE, ORDER BY, GROUP BY, HAVING, etc. are valid for SELECT INTO. The following are the two variations of SELECT INTO.

SELECT select_expressions INTO target FROM ...;

target is a comma-separated list of simple variables. select_expressions and the remainder of the statement are the same as for the SQL SELECT command. See Chapter 6 for a discussion of the SELECT command. The selected values must exactly match in data type, number, and order the structure of the target or a run-time error occurs.

SELECT * INTO record FROM table ...;

record is a record that has previously been declared using table%ROWTYPE where table is the table referenced in the FROM table clause.

If the query returns zero rows, null values are assigned to the target(s). If the query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. (Note that "the first row" is not well-defined unless you've used ORDER BY.)

Note: In both cases, where no row is returned or more than one row is returned, SPL throws an exception.

You can use the WHEN NO_DATA_FOUND clause in an EXCEPTION block to determine whether the assignment was successful (that is, at least one row was returned by the query).

This version of the emp_sal_query procedure uses the variation of SELECT INTO that returns the result set into a record. Also note the addition of the EXCEPTION block containing the WHEN NO_DATA_FOUND conditional expression.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    r_emp           emp%ROWTYPE;
    v_avgsal        emp.sal%TYPE;
BEGIN
    SELECT * INTO r_emp
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || r_emp.deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = r_emp.deptno;
    IF r_emp.sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
            || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
            || v_avgsal);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;

If the query is executed with a non-existent employee number the results appear as follows.

EXEC emp_sal_query(0);

Employee # 0 not found

Another conditional clause of use in the EXCEPTION section with SELECT INTO is the TOO_MANY_ROWS exception. If more than one row is selected by the SELECT INTO statement an exception is thrown by SPL.

When the following block is executed, the TOO_MANY_ROWS exception is thrown since there are many employees in the specified department.

DECLARE
    v_ename         emp.ename%TYPE;
BEGIN
    SELECT ename INTO v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee found');
        DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);
END;

More than one employee found
First employee returned is ADAMS

Note: See Section 14.5.5 for more information on exception handling.

14.4.4. `INSERT`

The INSERT command available in the SQL language can also be used in SPL programs.

An expression in the SPL language can be used wherever an expression is allowed in the SQL INSERT command. Thus, SPL variables and parameters can be used to supply values to the insert operation.

The following is an example of a procedure that performs an insert of a new employee using data passed from a calling program.

CREATE OR REPLACE PROCEDURE emp_insert (
    p_empno         IN emp.empno%TYPE,
    p_ename         IN emp.ename%TYPE,
    p_job           IN emp.job%TYPE,
    p_mgr           IN emp.mgr%TYPE,
    p_hiredate      IN emp.hiredate%TYPE,
    p_sal           IN emp.sal%TYPE,
    p_comm          IN emp.comm%TYPE,
    p_deptno        IN emp.deptno%TYPE
)
IS
BEGIN
    INSERT INTO emp VALUES (
        p_empno,
        p_ename,
        p_job,
        p_mgr,
        p_hiredate,
        p_sal,
        p_comm,
        p_deptno);

    DBMS_OUTPUT.PUT_LINE('Added employee...');
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || p_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || p_job);
    DBMS_OUTPUT.PUT_LINE('Manager    : ' || p_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || p_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || p_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || p_deptno);
    DBMS_OUTPUT.PUT_LINE('----------------------');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('OTHERS exception on INSERT of employee # ' || p_empno);
        DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
END;

If an exception occurs all database changes made in the procedure are automatically rolled back. In this example the EXCEPTION section with the WHEN OTHERS clause catches all exceptions. Two variables are displayed. SQLCODE is a number that identifies the specific exception that occurred. SQLERRM is a text message explaining the error. See Section 14.5.5 for more information on exception handling.

The following shows the output when this procedure is executed.

EXEC emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);

Added employee...
Employee # : 9503
Name       : PETERSON
Job        : ANALYST
Manager    : 7902
Hire Date  : 31-MAR-05
Salary     : 5000
Commission :
Dept #     : 40
----------------------

SELECT * FROM emp WHERE empno = 9503;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9503 PETERSON   ANALYST         7902 31-MAR-05       5000                    40

An optional clause can be appended to the INSERT command that returns a list of expressions that may contain the columns of the affected table. This provides a convenient means of obtaining the newly inserted values without the necessity of issuing a subsequent SELECT command to retrieve the new row.

The following example illustrates the use of the RETURNING INTO clause of the INSERT command:

    CREATE OR REPLACE PROCEDURE emp_insert_returns (
    p_empno         emp.empno%TYPE,
    p_ename         emp.ename%TYPE,
    p_job           emp.job%TYPE,
    p_mgr           emp.mgr%TYPE,
    p_sal           emp.sal%TYPE
    )
   IS
    v_empno         emp.empno%TYPE;
    v_name_title    VARCHAR2(21);
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_total         NUMBER(8,2);
    v_deptno        emp.deptno%TYPE;
    BEGIN
    INSERT INTO emp VALUES (
        p_empno,
        p_ename,
        p_job,
        p_mgr,
        SYSDATE,
        p_sal,
        p_sal * .5,
        40)
    RETURNING
        empno,
        ename || ', ' || job,
        mgr,
        hiredate,
        sal,
        comm,
        (sal + comm) * 24,
        deptno
    INTO
        v_empno,
        v_name_title,
        v_mgr,
        v_hiredate,
        v_sal,
        v_comm,
        v_total,
        v_deptno;

    DBMS_OUTPUT.PUT_LINE('Added employee...');
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_name_title);
    DBMS_OUTPUT.PUT_LINE('Manager    : ' || v_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
    DBMS_OUTPUT.PUT_LINE('Total Yrly : ' || v_total);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('----------------------');
    
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('OTHERS exception on INSERT of employee # ' ||
            p_empno);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
    END;

The following shows the results of executing the procedure:

EXEC emp_insert_returns(9504,'JOHNSON','ANALYST',7902,8000)

Added employee...
Employee # : 9504
Name       : JOHNSON, ANALYST
Manager    : 7902
Hire Date  : 05-JUL-06
Salary     : 8000
Commission : 4000
Total Yrly : 288000
Dept #     : 40
----------------------

SPL allows RETURNING INTO clause of INSERT command to return values into record datatype. The following example will insert data of a new employee in the emp table and will then return these values in an emp%ROWTYPE record.

    DECLARE
    emp_rec          emp%ROWTYPE;

    BEGIN
    INSERT INTO emp VALUES(
        8001, 
        'David',
        'ANALYST', 
        7698, 
        '12-DEC-90', 
        2000, 
        200, 
        10) 
    RETURNING 
        ename, 
        job,
        sal
    INTO 
        emp_rec.ename,
        emp_rec.job,
        emp_rec.sal; 
    
    DBMS_OUTPUT.PUT_LINE('New Employee Record: ' || emp_rec.ename ||' ' || emp_rec.sal ||' ' || emp_rec.job);
    END;

When executed, the above block statement will insert the employee data into table emp and will then return employee name, job and salary into emp_rec.ename, emp_rec.job and emp_rec.sal respectively. The emp_rec values will then be used to display the following output:

    INFO:  New Employee Record: David 2000.00 ANALYST

See Section 5.1 and INSERT for more information on the SQL INSERT command.

14.4.5. `UPDATE`

The UPDATE command available in the SQL language can also be used in SPL programs.

An expression in the SPL language can be used wherever an expression is allowed in the SQL UPDATE command. Thus, SPL variables and parameters can be used to supply values to the update operation.

CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno         IN emp.empno%TYPE,
    p_sal           IN emp.sal%TYPE,
    p_comm          IN emp.comm%TYPE
)
IS
BEGIN
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
        DBMS_OUTPUT.PUT_LINE('New Salary         : ' || p_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission     : ' || p_comm);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;

The SQL%FOUND conditional expression returns "true" if a row is updated, "false" otherwise. See Section 14.4.7 for a discussion of SQL%FOUND and other similar expressions.

The following shows the update on the employee using this procedure.

EXEC emp_comp_update(9503, 6540, 1200);

Updated Employee # : 9503
New Salary         : 6540
New Commission     : 1200

SELECT * FROM emp WHERE empno = 9503;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9503 PETERSON   ANALYST         7902 31-MAR-05       6540       1200         40

An optional clause can be appended to the UPDATE command that returns a list of expressions that may contain the columns of the affected table. This provides a convenient means of obtaining the newly updated values without the necessity of issuing a subsequent SELECT command to retrieve the modified row.

The following example illustrates the use of the RETURNING INTO clause of the UPDATE command.

    CREATE OR REPLACE PROCEDURE emp_comp_update_returns (
     p_empno          IN emp.empno%TYPE,
     p_sal            IN emp.sal%TYPE,
     p_comm           IN emp.comm%TYPE
     )
    IS
    v_empno         emp.empno%TYPE;
    v_name_title    VARCHAR2(21);
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_total         NUMBER(8,2);
    v_deptno        emp.deptno%TYPE;
    
    BEGIN
    
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno
    RETURNING
        empno,
        ename || ', ' || job,
        mgr,
        hiredate,
        sal,
        comm,
        (sal + comm) * 24,
        deptno
    INTO
        v_empno,
        v_name_title,
        v_mgr,
        v_hiredate,
        v_sal,
        v_comm,
        v_total,
        v_deptno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated employee...');
        DBMS_OUTPUT.PUT_LINE('Employee #     : ' || v_empno);
        DBMS_OUTPUT.PUT_LINE('Name           : ' || v_name_title);
        DBMS_OUTPUT.PUT_LINE('Manager        : ' || v_mgr);
        DBMS_OUTPUT.PUT_LINE('Hire Date      : ' || v_hiredate);
        DBMS_OUTPUT.PUT_LINE('New Salary     : ' || v_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
        DBMS_OUTPUT.PUT_LINE('Total Yrly     : ' || v_total);
        DBMS_OUTPUT.PUT_LINE('Dept #         : ' || v_deptno);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
    END;

The following shows the results of executing the procedure.

EXEC emp_comp_update_returns(9504,9500,5000)

Updated employee...
Employee #     : 9504
Name           : JOHNSON, ANALYST
Manager        : 7902
Hire Date      : 05-JUL-06
New Salary     : 9500
New Commission : 5000
Total Yrly     : 348000
Dept #         : 40

SPL allows RETURNING INTO clause of UPDATE command to return values into record datatype. The following example will update values in the emp table of employee "DAVID" and will then return all these values in an emp%ROWTYPE record.

    DECLARE
    emp_rec          emp%ROWTYPE;

    BEGIN
    UPDATE emp SET sal = sal + 2000 WHERE ename = 'David' 
    RETURNING 
        * 
    INTO  
        emp_rec;
    
    DBMS_OUTPUT.PUT_LINE('Updated Salary: ' || emp_rec.ename ||' '|| emp_rec.sal);
    END;

When executed, the above block statement will update salary of employee "David". It will then return the employee's data into emp_rec which is a record data type. Notice that we can return all values of a row by using "*". Following will be the output of this SQL script:

    INFO:  Updated Salary: David 4000.00

See Section 5.2 and UPDATE for information on the SQL UPDATE command.

14.4.6. `DELETE`

The DELETE command available in the SQL language can also be used in SPL programs.

An expression in the SPL language can be used wherever an expression is allowed in the SQL DELETE command. Thus, SPL variables and parameters can be used to supply values to the delete operation.

CREATE OR REPLACE PROCEDURE emp_delete (
    p_empno         IN emp.empno%TYPE
)
IS
BEGIN
    DELETE FROM emp WHERE empno = p_empno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || p_empno);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;

The SQL%FOUND conditional expression returns "true" if a row is deleted, "false" otherwise. See Section 14.4.7 for a discussion of SQL%FOUND and other similar expressions.

The following shows the deletion of an employee using this procedure.

EXEC emp_delete(9503);

Deleted Employee # : 9503

SELECT * FROM emp WHERE empno = 9503;

no rows selected

An optional clause can be appended to the DELETE command that returns a list of expressions that may contain the columns of the affected table. This provides a convenient means of obtaining the newly deleted values without the necessity of issuing a subsequent SELECT command to retrieve the deleted row.

The following example illustrates the use of the RETURNING INTO clause of the DELETE command.

   CREATE OR REPLACE PROCEDURE emp_comp_delete_returns (
     p_empno          IN emp.empno%TYPE,
     p_sal            IN emp.sal%TYPE,
     p_comm           IN emp.comm%TYPE
     )
    IS
    v_empno         emp.empno%TYPE;
    v_name_title    VARCHAR2(21);
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_total         NUMBER(8,2);
    v_deptno        emp.deptno%TYPE;
    BEGIN
    
    DELETE from emp where empno = p_empno 
    RETURNING
        empno,
        ename || ', ' || job,
        mgr,
        hiredate,
        sal,
        comm,
        (sal + comm) * 24,
        deptno
    INTO
        v_empno,
        v_name_title,
        v_mgr,
        v_hiredate,
        v_sal,
        v_comm,
        v_total,
        v_deptno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee Not Deleted...');
        DBMS_OUTPUT.PUT_LINE('Employee #     : ' || v_empno);
        DBMS_OUTPUT.PUT_LINE('Name           : ' || v_name_title);
        DBMS_OUTPUT.PUT_LINE('Manager        : ' || v_mgr);
        DBMS_OUTPUT.PUT_LINE('Hire Date      : ' || v_hiredate);
        DBMS_OUTPUT.PUT_LINE('New Salary     : ' || v_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
        DBMS_OUTPUT.PUT_LINE('Total Yrly     : ' || v_total);
        DBMS_OUTPUT.PUT_LINE('Dept #         : ' || v_deptno);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' Employee has been deleted and hence record not found');
    END IF;
    END;

    EXEC emp_comp_delete_returns(9504,9500,5000);

SPL allows RETURNING INTO clause of DELETE command to return values into record datatype. The following example will delete data in the emp table of employee "David". The SQL script will then return these values in an emp%ROWTYPE record.

    DECLARE
    emp_rec          emp%ROWTYPE;

    BEGIN
    DELETE FROM emp WHERE ename = 'David' 
    RETURNING 
        ename,
        sal 
    INTO 
        emp_rec.ename,
        emp_rec.sal;

    DBMS_OUTPUT.PUT_LINE('Deleted Employee Name and Salary: '|| emp_rec.ename ||' ' ||  emp_rec.sal);
    END;

The above block statement will delete record for the employee "David". It will return employee name and salary using the RETURNING INTO clause. Following is the output of this query.

   INFO:  Deleted Employee Name and Salary: David 4000.00

See Section 5.3 and DELETE for information on the SQL DELETE command.

14.4.7. Obtaining the Result Status

There are several attributes that can be used to determine the effect of a command. SQL%FOUND is a boolean that returns true if at least one row was affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved one or more rows.

BEGIN
    UPDATE emp SET mgr = '7900' WHERE job = 'CLERK';
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('At least one row has been updated');
    END IF;
END;

SQL%NOTFOUND is the opposite of SQL%FOUND. SQL%NOTFOUND returns true if no rows were affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved no rows.

BEGIN
    UPDATE emp SET job = 'CLERK' WHERE empno = 9000;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows were updated');
    END IF;
END;

SQL%ROWCOUNT provides the number of rows affected by an INSERT, UPDATE or DELETE command.

BEGIN
    UPDATE emp SET mgr = 7900 WHERE deptno = 30;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows were updated');
END;