Oracle Stored Procedures – Tips
Table of Contents
- Usage of %TYPE and %ROWTYPE in declaration of variables.
- Usage of CASE statement and Expression.
- Usage of DECODE expression.
- Creating and Compiling a Package.
- Returning a cursor from a stored procedure.
- Error Handling in Stored Procedures.
- Difference between SQLCODE 1403 and SQLCODE 100.
- Usage of Global Temporary tables.
- Others
The following is a collection of some useful tips for writing oracle 8i Stored Procedures. This is directed towards new users of oracle for quick learning.
Pre-requisites
Users should have a fair knowledge of RDBMS concepts and should know how to write a simple stored procedure.
Preliminary knowledge of Oracle is desirable.
Usage of %TYPE and %ROWTYPE in declaration of variables
It is considered a good programming practice to use the %TYPE while declaring variables in PL/SQL programming.
Syntax: v_<variable_name> <table_name>.<column_name>%TYPE
Example v_assign_number R3000_nw_control.assign_number%TYPE.
This will take the data type of the mapped column and automatically cascades the changes to the variables if the parent column changes.
The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.
You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name.
In the example below, you use %ROWTYPE to declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.
CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
In the next example, you select a row from the emp table into a %ROWTYPE record:
SELECT * INTO emp_rec FROM emp WHERE empno = my_empno;
IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN
Usage of CASE statement and Expression
CASE statement is similar to the normal IF…THEN…ELSE loop, but is more efficient, easy to understand and maintain.
CASE statement takes many forms depending on the exact requirements.
The following are different forms of CASE expression and statement.
- Value Match CASE Expression
- Searched CASE Expression
- Value Match CASE Statement
- Searched CASE Statement
Value Match CASE Expression
The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found:
SELECT ename, empno,
(CASE deptno
WHEN 10 THEN ‘Accounting’
WHEN 20 THEN ‘Research’
WHEN 30 THEN ‘Sales’
WHEN 40 THEN ‘Operations’
ELSE ‘Unknown’
END) department
FROM emp
ORDER BY ename;
The value match CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
deptno NUMBER := 20;
dept_desc VARCHAR2(20);
BEGIN
dept_desc := CASE deptno
WHEN 10 THEN ‘Accounting’
WHEN 20 THEN ‘Research’
WHEN 30 THEN ‘Sales’
WHEN 40 THEN ‘Operations’
ELSE ‘Unknown’
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/
Searched CASE Expression
A more complex version is the searched CASE expression where a comparison expression is used to find a match. In this form the comparison is not limited to a single column:
SELECT ename, empno,
(CASE
WHEN sal < 1000 THEN ‘Low’
WHEN sal BETWEEN 1000 AND 3000 THEN ‘Medium’
WHEN sal > 3000 THEN ‘High’
ELSE ‘N/A’
END) salary
FROM emp
ORDER BY ename;
The searched CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
sal NUMBER := 2000;
sal_desc VARCHAR2(20);
BEGIN
sal_desc := CASE
WHEN sal < 1000 THEN ‘Low’
WHEN sal BETWEEN 1000 AND 3000 THEN ‘Medium’
WHEN sal > 3000 THEN ‘High’
ELSE ‘N/A’
END;
DBMS_OUTPUT.PUT_LINE(sal_desc);
END;
/
Value Match CASE Statement
The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements:
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, deptno FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ‘ : ‘ || cur_rec.empno || ‘ : ‘);
CASE cur_rec.deptno
WHEN 10 THEN
DBMS_OUTPUT.PUT_LINE(’Accounting’);
WHEN 20 THEN
DBMS_OUTPUT.PUT_LINE(’Research’);
WHEN 30 THEN
DBMS_OUTPUT.PUT_LINE(’Sales’);
WHEN 40 THEN
DBMS_OUTPUT.PUT_LINE(’Operations’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Unknown’);
END CASE;
END LOOP;
END;
/
Searched CASE Statement
As with its expression counterpart, the searched CASE statement allows multiple comparisons using multiple variables:
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ‘ : ‘ || cur_rec.empno || ‘ : ‘);
CASE
WHEN cur_rec.sal < 1000 THEN
DBMS_OUTPUT.PUT_LINE(’Low’);
WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN
DBMS_OUTPUT.PUT_LINE(’Medium’);
WHEN cur_rec.sal > 3000 THEN
DBMS_OUTPUT.PUT_LINE(’High’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Unknown’);
END CASE;
END LOOP;
END;
/
* NOTE: The CASE Expression is available from Oracle 9i onwards.
Usage of DECODE expression
DECODE is same as IF…THEN…ELSE statement, but is to code and maintain.
It is used to assign a value to variable basing on the value of some other variable.
Syntax: decode (expression, search, result [, search, result]… [, default])
Expression is the value to compare.
Search is the value that is compared against expression.
Result is the value returned, if expression is equal to search.
Default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Example: You could use the decode function in an SQL statement as follows:
|
SELECT supplier_name, |
||
|
decode(supplier_id, |
10000, |
‘IBM’, |
|
|
10001, |
‘Microsoft’, |
|
|
10002, |
‘Hewlett Packard’, |
|
|
|
‘Gateway’) result |
|
FROM suppliers; |
||
Nested DECODE can be used to evaluate some complex conditions.
(DECODE(NVL(C.PCT_100_DATE,0),0,DECODE(NVL(C.PCT_75_DATE,0),0,DECODE(NVL(C.PCT_50_DATE,0),0,DECODE(NVL(C.PCT_25_DATE,0),0,0,25),50),75),100)) AS TAST_COMPLETION_PCT
Creating and Compiling a Package
The following is the syntax used to create a Package.
CREATE PACKAGE <package_name> AS
<signature of the Procedures/Functions>
END <package_name>;
CREATE PACKAGE BODY <package_name> AS
<declaration of variables/types/cursors/procedures/functions/error handling>
To compile a package in PL/SQL F8 key is used.
Returning a cursor from a stored procedure
At times it is required to return multiple rows from a Stored Procedure. This could be achieved through VArrays, Nested Tables and Cursors.
Cursors are the simple to implement.
The following steps are to be followed in order to return a cursor from a SP.
- Declare the CURSOR type in the SP.
TYPE v_cursor IS REF CURSOR;
2. Declare a cursor variable of type v_cursor as IN OUT parameter in the SP.
PROCEDURE <procedure_name>
(<var 1> IN <xxx.xxx>%TYPE,
<cursor_variable> IN OUT v_cursor,
v_error_message OUT VARCHAR2,
v_error_code OUT NUMBER );
3. Define a cursor as per the requirement.
OPEN <cursor_name> FOR
SELECT <col1>,
…<col2>,
……
FROM <table 1>
<table 2>
WHERE <cond1>
AND <cond2>;
4. Assign the cursor to the cursor variable.
<cursor_variable> := <cursor_name>;
5. Since the cursor is not fetched, we are not sure whether it empty or contains records. This can be handled either in the front-end application or by using a singleton select on the primary key column of the tables in the WHERE condition of the cursor.
Error Handling in Stored Procedures
A good stored procedure is one which takes care of all the exceptions that occur.
At times we need to raise exceptions for wrong input values; SQLCODES and some times we don’t want to raise exceptions.
In order to raise an exception,
Define the exception
<exception1> EXCEPTION;
ERROR_IN_INPUT EXCEPTION;
This has to be raised at the time of error.
IF <input_vairable> IS NULL
THEN
RAISE ERROR_IN_INPUT;
END IF;
This transfers the control to the EXCEPTION block of the Procedure. Here the required processing should be done.
EXCEPTION
WHEN ERROR_IN_INPUT
THEN
v_error_code := <value>;
v_error_message:= <message>;
END;
If a stored procedure calls another stored procedure, then
Case 1: If OTHERS exception is not handled in the called SP, it has to handled in the calling SP to take the required action and this stops the further processing.
Case 2: If OTHERS exception is handled in the called SP, Depending on the requirements, the return values from the SP has to compared and required action should be taken.
It is strongly recommended to have each SQL statement with in BEGIN – EXCEPTION – END block to control the exceptions locally.
- Always handle all the specific exceptions locally.
Example:
BEGIN
…
…
…
…
BEGIN
…
…
…
EXCEPTION
WHEN EXP1a
….
WHEN EXP2a
….
END
…
…
BEGIN
…
…
…
EXCEPTION
WHEN EXP1b
….
WHEN EXP2b
….
END
…
…
EXCEPTION
WHEN EXP1g
….
WHEN EXP2g
….
WHEN OTHERS
…
END
- Never handle generic exceptions like ‘WHEN OTHERS’ locally because this will continue with further processing even when you need to stop and exit. There is no way to transfer the control back from the EXCPETION block.
BEGIN
…
…
…
…
BEGIN
…
…
…
EXCEPTION
WHEN EXP1a
….
WHEN EXP2a
….
WHEN OTHERS /* Not Suggestible */
…
END
EXCEPTION
WHEN EXP1g
….
WHEN EXP2g
….
WHEN OTHERS
…
END
BEGIN
…
…
…
…
BEGIN
…
…
…
EXCEPTION
WHEN EXP1a
….
WHEN EXP2a
….
END
EXCEPTION
WHEN EXP1g
….
WHEN EXP2g
….
WHEN OTHERS
…
END
The SQLCODE condition checks after a SQL query in a stored procedure will never get executed when an exception occurs
Consider the following piece of the code
BEGIN
SELECT fieldA
INTO fldA
FROM tableA
WHERE field1 = fld1;
IF SQLCODE <> 0 AND <> 100
THEN
<SOME STATEMENTS>
END-IF
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_code := 0;
END;
The above condition will not get executed if there is an exception for the SELECT query. It will jump to the outer exception WHEN OTHERS block.
To handle such cases, you can use the following logic
BEGIN
SELECT fieldA
INTO fldA
FROM tableA
WHERE field1 = fld1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_code := 0;
v_error_message:= ‘ ‘;
WHEN OTHERS
IF SQLCODE <> 0 AND SQLCODE <> 100
THEN
<SOME STATEMENTS>
END IF;
END;
Difference between SQLCODE 1403 and SQLCODE 100
|
ORA-01403: |
no data found |
|
Cause: |
In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query. |
|
Action: |
Terminate processing for the SELECT statement. |
|
ORA-00100: |
no data found |
|
Cause: |
An application made a reference to unknown or inaccessible data. |
|
Action: |
Handle this condition within the application or make appropriate modifications to the application code. |
Usage of Global Temporary tables
Temporary Tables
Oracle 8i introduces a new concept called global temporary tables. This version only provides global temporary tables and local tables are expected in the future. These are like normal tables but the segment is created only when the data is inserted and hence temporary in nature.
create global temporary table dbatest
(c1 number, c2 number);
The above statement creates a table whose definition is visible to all the sessions but the data is visible to only the session that has created it. Temporary tables can be used by developers to store session / transaction specific data that can be discarded as soon as the session/transaction ends. When a truncate is issued on this table, only data related to that particular session is truncated.
The above code creates a table named dbatest. When data is inserted into this table the data persists either at the session or transaction level based on how “On Commit” parameter is specified. On commit can be specified as “delete rows” or “preserve rows”. Delete Rows seems to be the default.
Create global temporary table dbatest
(
c1 number,
c2 number
) on commit delete rows;
In the above table, as soon as the user ends the transaction by issuing a commit statement, the data in the temporary table is deleted (truncated).
Create global temporary table dbatest
(
c1 number,
c2 number
) on commit preserve rows;
The above statement causes the creation of a table that will keep its rows even after the transaction is committed. If the user inserts rows into the above table and then commits the data, the data that was inserted will exist until the session is ended.
|
USER |
Temporary Tablespace
|
Default Tablespace |
Transaction Specific data(ON COMMIT DELETE ROWS) |
|
Session specific data(ON COMMIT PRESERVE ROWS) |
|
|
|
|
|
|
US-B |
|
US-C
USER SESSIONS/TRANSACTIONS |
|
|
TEMPORARY SEGMENT (S3) |
Advantages of Temporary Tables
- Used as buffer tables to increase performance while replacing complex joins.
- Used as scratch tables to handle voluminous data for sort and other operations.
- Supports Multi-session/Transaction.
- Only the definition is stored in the database.
- The data is deleted at the end of the Session/Transaction.
Others
- Update queries in ORACLE does not return an SQLCODE of 1403/100 when the where clause of the query does not return any rows. This can be handled in two ways
-
- Usage of SQL%ROWCOUNT: SQL%ROWCOUNT returns the number of rows updated by the query. If SQL%ROWCOUNT returns zero that means the where clause of the query does not return any rows.
-
- Usage of SELECT: A normal select or select for update or select count (*) with the same where clause can be used to check whether the records exist for the given condition before UPDATE query.
- Oracle 8i does not have utility functions such as IS NUMBER, IS CHARACTER, et al.
- When an aggregate function like SUM, AVG e.t.c is used in a select statement, the SQLCODE 100 is returned when no records are found. This can be handled by using NVL(SUM(<parameter>),0) to avoid SQLCODE 100