DBheart: About PL/SQL

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.

Oracle PL/SQL

Basic structure
Anonymous Blocks
Named blocks

Procedure
Function
Package

Exception handling

System defined and user defined exceptions
Using EXCEPTION_INIT
Using RAISE_APPLICATION_ERROR

Some Advance PL/SQL topics

Records

Performing DML operations using Records

Collections
Triggers
PL/SQL Performance
Writing to a file using PL/SQL

Basic structure

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. 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 blocksTOP

Named blocks (like procedures, functions, packages) are stored in database and can be called from other named blocks or anonymous blocks.
For example

Procedure

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:

Mode	Description	Parameter usage
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

For instance, in above given example:

		
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)

Function TOP

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.

Package TOP

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.
Example:

		
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

RecordsTOP

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.

CollectionsTOP

TriggersTOP

PL/SQL PerformanceTOP

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.

Thanks...

TOP

About Oracle PL/SQL