PL/SQL is a programming language provided by Oracle and provide programming capabilities to Oracle's version of SQL.
It is having all major capabilities of powerful programming languages.
PL/SQL is integrated with Oracle’s SQL language, i.e. we can execute SQL statements directly from PL/SQL program without having to rely on any kind of intermediate API (Application Programming Interface) such as JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity). Also, we can call PL/SQL functions from within a SQL statement.
In this article, I will talk about features available in PL/SQL along-with their typical usage, without going much into details.
- Basic structure
- Anonymous Blocks
- Named blocks
- Exception handling
- Some Advance PL/SQL topics
- PL/SQL Performance
- Writing to a file using PL/SQL
PL/SQL code can exist in 2 types of blocks, anonymous block and named block. A block is smallest grouping of code lines.
Named blocks may be packages, procedures, functions, triggers, or object types.
Basic structure of PL/SQL block has 4 sections, only 1, the execution section is mandatory, rest all are optional.
HEADER OPTIONAL. used only for named blocks. These named blocks are then stored in database upon successful compilation. DECLARE OPTIONAL. declare the variables, cursors, and sub-blocks that are referenced in the execution and exception sections. BEGIN MANDATORY. actual code statements that are executed by PL/SQL engine. EXCEPTION OPTIONAL. for handling any error or warning conditions that may occur at runtime. These conditions may be user-defined too. END;
Anonymous Blocks TOP
Anonymous blocks are not stored in the database, we write them and execute them whenever we need, but they are not stored in database so cannot be called as such. These cannot be called by another block, but they can call named blocks.
See below example. Note that there is no header section as this is NOT a named block, we are not going to store it in database.
So we cannot call it from any anonymous or stored named block.
DECLARE l_book_count INTEGER; BEGIN SELECT COUNT(*) INTO l_book_count FROM books WHERE author LIKE 'MIKE%'; DBMS_OUTPUT.PUT_LINE ('Mike has written ' || l_book_count || ' books.'); EXCEPTION WHEN OTHERS THEN --do something-- END;
Named blocks (like procedures, functions, packages) are stored in database and can be called from other named blocks or anonymous blocks.
PROCEDURE check_sal (EMPNO IN NUMBER, SAL OUT NUMBER) <--- Header section IS v_sal NUMBER; <--- Declaration section BEGIN SELECT SAL INTO v_sal FROM EMPLOYEES WHERE EMPLOYEE_ID = EMPNO; <--- Execution section EXCEPTION WHEN NO_DATA_FOUND THEN <--- Exception section DBMS_OUTPUT.PUT_LINE ('Employee id not found.') ; END;
So basically when you want your code to persist in database and want to invoke/execute some functionality repetitively, store it in database as a named block.
But if you want to execute some code only once, execute it as an anonymous block.
In the above named block, we have created a STORED PROCEDURE named check_sal. If I logged into database as user SCOTT via SQL*Plus (or via any other tool) and the given named block, we finally will have a procedure called SCOTT.check_sal created and stored in database. But it can be executed or called from other blocks only when its successfully compiled.
Compilation ensures that the code does not contain any syntax error, the usage of keywords is correct, all referenced database objects, like table name, column name, stored object name, are existing in database and are in valid compiled state too. It also ensures that the user executing this has sufficient privileges to do so and no reserved keyword has been used illegally.
In the named block above, we basically created SCOTT.check_sal procedure in SCOTT schema. In header section, there are 2 formal parameters in the procedure having IN parameter mode. There are 3 parameter modes basically:
|IN||Read-only||The value of the actual parameter can be referenced inside the module, but the parameter cannot be changed. If you do not specify the parameter mode, then it is considered an IN parameter.|
|OUT||Write-only||The module can assign a value to the parameter, but the parameter’s value cannot be referenced.|
|IN OUT||Read/write||The module can both reference (read) and modify (write) the parameter.|
Also, there are two different kinds of parameters: actual and formal.
|Formal||Names declared in the parameter list of the header of a module (procedure/function)|
|Actual||Values or expressions placed in the parameter list of the actual call to the|
PROCEDURE check_sal (EMPNO IN NUMBER, SAL OUT NUMBER) --> EMPNO and SAL are formal parameters. When I call this procedure like this: check_sal (e_no,sal); --> e_no and sal are actual parameters. e_no is a constant (like a number), but sal is a variable (as it will contain an OUT type value)
A function is like procedure only, with the difference that it must return a value.
FUNCTION booktitle (isbn_in IN VARCHAR2) RETURN VARCHAR2 IS l_title books.title%TYPE; BEGIN SELECT title INTO l_title FROM books WHERE isbn = isbn_in; RETURN l_title; END; Now I can use this function and its return value in following way : DECLARE title VARCAHR2(30); BEGIN title := booktitle ('128978'); DBMS_OUTPUT.PUT_LINE ('Title of the ISBN is: '||title); END;
How to choose between using a function or a procedure ? Here are some tips:
- If you want to use the sub-program in a SQL query, go for function.
- If the sub-program will return a single value, go for function.
- If you want to do only processing, you do not need to get a returned value, go for procedure.
- Both function and procedure can be used for returning multiple values using OUT or IN OUT type of variables.
An Oracle package object is a group of elements of PL/SQL code into a named. It may have variables, constants, procedure, function, exception etc.
Packages improve performance by caching static data. They also make code maintenance easier and minimize the need to recompile code. a package usually consists of two pieces of code: the package specification and package body. External programs (not defined in the package) can only call programs listed in the specification. If you change and recompile the package body,those external programs are not invalidated. Minimizing the need to recompile code is a critical factor while maintaining code.
Here is an example of package specification: PACKAGE employee_pkg AS fullname_t IS VARCHAR2 (200); sal_t IS NUMBER (10,2); FUNCTION fullname ( last_in employees.last_name%TYPE, first_in employees.first_name%TYPE) RETURN VARCHAR2; FUNCTION get_sal ( employee_id_in IN employees.employee_id%TYPE) RETURN NUMBER; END employee_pkg; Here is the package body: PACKAGE BODY employee_pkg AS v_commission PLS_INTEGER := 0.4 ; FUNCTION fullname ( last_in employees.last_name%TYPE, first_in employees.first_name%TYPE) RETURN VARCHAR2 IS BEGIN RETURN last_in || ', ' || first_in; END; FUNCTION get_sal ( employee_id_in IN employees.employee_id%TYPE) RETURN NUMBER IS v_sal NUMBER; BEGIN SELECT SAL INTO v_sal FROM EMPLOYEES WHERE EMPLOYEE_ID = EMPNO; return v_sal * v_commission; END; END employee_pkg;
Remember, from outside a package, we can refer to only those elements which are there in package specification.
Here is how we can use the package elements: BEGIN employee_pkg.fullname_t := employee_pkg.fullname ('DOE','JOHN'); employee_pkg.sal_t := employee_pkg.get_sal (12300); DBMS_OUTPUT.PUT_LINE ('Full name is :'||employee_pkg.fullname_t); DBMS_OUTPUT.PUT_LINE ('Salary is:' || to_char(employee_pkg.sal_t)); END;
Elements in package specification are public, and those in package body (like variable v_commission) are private, i.e. they can be referenced ONLY from inside the package. Like variable v_commission is being used in package function get_sal.
Conditions and loops TOP
PL/SQL provides support for if-else-else if logic and CASE statement. It also provides loops like : for loop, while loop and do-while loop.
Exception handling TOP
Exception handling in PL/SQL is done in EXCEPTION section. Its good to capture exceptions in respective BEGIN-END blocks and take appropriate action.
There are 2 types of them : system exceptions like NO_DATA_FOUND, VALUE_ERROR etc. and user defined exceptions.
System defined and user defined exceptions TOP
See below example, it shows usage of both system defined and user defined exceptions.
PROCEDURE calc_annual_sales (company_id_in IN company.company_id%TYPE) IS --these are user-defined exceptions, which will be raised by RAISE command below invalid_company_id EXCEPTION; negative_balance EXCEPTION; duplicate_company BOOLEAN; BEGIN .... IF bal < 0 THEN RAISE negative_balance; END IF; IF company_id NOT IN ('CO','KP') THEN RAISE invalid_company_id; END IF; EXCEPTION WHEN NO_DATA_FOUND <-- This is a system exception THEN ==exception handling logic== WHEN invalid_company_id THEN ==exception handling logic== WHEN negative_balance THEN ==exception handling logic== END;
Using EXCEPTION_INIT TOP
Think about this, there are many Oracle error codes, but only few have exception names associated with them. Like error -1403 is named as NO_DATA_FOUND by Oracle, i.e. its a system defined exception. We can give a name to undefined error codes using EXCEPTION_INIT, which is a compile-time command or pragma used to associate a name with an internal error code.
For example, if we specify an invalid table name, Oracle raises error -903, which is just a number, there is no name associated with this exception. So using EXCEPTION_INIT, we can associate error number -903 with some named exception. Similarly, -1843 is for invalid month, we can associate a name with it too.
PROCEDURE my_procedure IS invalid_month EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_month, −1843); BEGIN --This update statement should fail as month is invalid. UPDATE EMP SET HIRE_DATE = TO_DATE('31-14-2000','DD-MM-YYYY') EXCEPTION WHEN invalid_month THEN <-- This will be raised. ==exception handling logic== WHEN OTHERS THEN ==exception handling logic== END;
Using RAISE_APPLICATION_ERROR TOP
Think about this, even though there are several Oracle defined error codes, you may need some for your application specific needs.
Oracle provides the RAISE_APPLICATION_ERROR procedure (defined in the default DBMS_STANDARD package) to raise application-specific errors in your application. The advantage to using RAISE_APPLICATION_ERROR instead of RAISE (which can also raise an application-specific, explicitly declared exception) is that you can associate an error message with the exception.
Here is syntax:
RAISE_APPLICATION_ERROR (num binary_integer, msg varchar2, keeperrorstack boolean default FALSE); where num is the error number and must be a value between –20,999 and –20,000 (just think: Oracle needs all the REST of those negative integers for its own exceptions!) msg is the error message and must be no more than 2K characters in length (any text beyond that limit will be ignored) keeperrorstack indicates whether you want to add the error to any already on the stack (TRUE) or replace the existing errors (the default, FALSE). Example: BEING if a = 0 then DBMS_OUTPUT.put_line ('STUDENT INVALID...'); RAISE_APPLICATION_ERROR(-20002,'student is invalid!!!'); end if; END;
Use SQLERRM and SQLCODE to get Oracle returned error message and error code corresponding to an exception.
DECLARE v_sqlerrm VARCHAR2(200); v_sqlcode NUMBER; BEGIN .... EXCEPTION WHEN OTHERS THEN v_sqlerrm := SUBSTR(SQLERRM,1,200); v_sqlcode := SQLCODE; DBMS_OUTPUT.PUT_LINE('SQLERRM: '||v_sqlerrm); DBMS_OUTPUT.PUT_LINE('SQLCODE: '||to_char(v_sqlcode) ); END;
See the usage of WHEN OTHERS exception section in all examples here. It just ensures that whatever exception has not been captured explicitly by you, it will be captured by WHEN OTHERS section.
Do NOT put it in beginning of EXCEPTION section, it will absorb all exceptions and you will not see your handled user-defined exceptions. Put it in the end, so that only un-handled exceptions are captured.
There are three ways that an exception may be raised in your application:
- The database might raise the exception when it detects an error.
- You might raise an exception with the RAISE statement.
- You might raise an exception with the RAISE_APPLICATION_ERROR built-in procedure.
Some Advanced PL/SQL topics
Records are composite data structures, which may be table based, cursor based or user-defined.
They make data manipulation very easy and also reduce lines of code. A record is like a row of database table. Every element of record is assigned a value explicitly via code. Then that record can be further used.
Remember, a record can contain only one row of data at a time. If you attempt putting multiple rows, it will fail. Use collection of records for that purpose.
Check below example. I have declared a user defined and table based record, fetched data into them and then displayed values.
DECLARE tab_based_rec employees%ROWTYPE; --> table based record TYPE user_def_rec_type is RECORD (emp_id employees.empno%TYPE, emp_name employees.first_name%TYPE); user_def_rec user_def_rec_type; --> user defined record BEGIN SELECT * INTO tab_based_rec FROM EMPLOYEES WHERE EMPNO = 200456; DBMS_OUTPUT.PUT_LINE ('Salary is :' || tab_based_rec.sal); DBMS_OUTPUT.PUT_LINE ('Commission is :' || tab_based_rec.comm); SELECT empno,first_name INTO user_def_rec FROM EMPLOYEES WHERE EMPNO = 200456; DBMS_OUTPUT.PUT_LINE ('Employee Name :' || user_def_rec.first_name); END;
Above example showed explicitly declared records. Below one shows example of an implicit record. Here, I did not declare any record type, its declared
implicitly by Oracle.
BEGIN FOR emp_rec IN (SELECT * FROM employees) LOOP DBMS_OUTPUT.PUT_LINE ('Calculation for Employee id :' || emp_rec.empno); calculate_total_sal (emp_rec); END LOOP; END;
Performing DML operations using Records TOP
Here is an example of an Insert statement using record. Note the shortness of Insert statement:
DECLARE emp_rec employees%ROWTYPE; BEGIN emp_rec.empno := 1000; emp_rec.first_name := 'Marc'; emp_rec.last_name := 'Josh'; emp_rec.sal := 5700; emp_rec.comm := 5; emp_rec.mgr := 4321; insert_employee (emp_rec); END; PROCEDURE insert_employee (emp_rec IN employees%ROWTYPE) IS INSERT INTO employees VALUES emp_rec; END;
Here is an example of an Update statement using record. Note the shortness and Syntax of Update statement:
DECLARE emp_rec employees%ROWTYPE; BEGIN emp_rec.empno := 1000; emp_rec.first_name := 'Marc'; emp_rec.last_name := 'Josh'; emp_rec.sal := 5700; emp_rec.comm := 5; emp_rec.mgr := 4321; UPDATE EMPLOYEES SET ROW = emp_rec --> Note the syntax WHERE EMPNO = emp_rec.empno; END;
Note:You must update an entire row with the ROW syntax. You cannot update a subset of columns (although this may be supported in future releases). Any fields whose values are left NULL will result in a NULL value assigned to the corresponding column.
Below example shows using records with the RETURNING clause:
DECLARE emp_rec employees%ROWTYPE; emp_return_rec employees%ROWTYPE; BEGIN emp_rec.empno := 1000; emp_rec.first_name := 'Marc'; emp_rec.last_name := 'Josh'; emp_rec.sal := 5700; emp_rec.comm := 5; emp_rec.mgr := 4321; UPDATE EMPLOYEES SET ROW = emp_rec WHERE EMPNO = emp_rec.empno RETURNING empno,first_name,last_name,sal,comm,mgr --> Note the syntax, * not supported till now. INTO emp_return_rec; END;
Note: You cannot use records in DML statements that are executed dynamically (EXECUTE IMMEDIATE). This requires Oracle to support the binding of a PL/SQL record type into a SQL statement, and only SQL types can be bound in this way.
Writing to a file using PL/SQLTOP
You can write to a file on server location where database is installed, using UTL_FILE built in package. Refer to this article for more details.