Oracle PL/SQL, Strored procedures, trigers, functions , package and views

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL. Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

PL/SQL Block consists of three sections:

  • The Declaration section (optional).
  • The Execution section (mandatory).
  • The Exception Handling (or Error) section (optional).

Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon; PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

How a Sample PL/SQL Block Looks

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;

Advantages of PL/SQL

  • Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
  •  Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
  •  Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
  • Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

PL/SQL Placeholders: Placeholders are temporary storage area. PL/SQL Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block. Depending on the kind of data you want to store, you can define placeholders with a name and a data type. Few of the data types used to define placeholders are as given below.
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile

PL/SQL Variables: These are placeholders that store the values that can change through the PL/SQL Block.

General Syntax to declare a variable is: variable_name data_type [NOT NULL := value ];

  • variable_name is the name of the variable.
  • data type is a valid PL/SQL data type.
  • NOT NULL is an optional specification on the variable.
  • value or DEFAULT value is also an optional specification, where you can initialize a variable.
  • Each variable declaration is a separate statement and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.

DECLARE

salary number (6);

* “salary” is a variable of data type number and of length 6.

When a variable is specified as NOT NULL, you must initialize the variable when it is declared.

For example: The below example declares two variables, one of which is a not null.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.

1)We can directly assign values to variables.
The General Syntax is:

  variable_name:= value;

2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement. The General Syntax is:

SELECT column_name INTO variable_name FROM table_name [WHERE condition];

Example: The below program will get the salary of an employee with id ‘1116’ and display it on the screen.

DECLARE

 var_salary number(6);

 var_emp_id number(6) = 1116;

BEGIN

 SELECT salary

 INTO var_salary

 FROM employee

 WHERE emp_id = var_emp_id;

 dbms_output.put_line(var_salary);

 dbms_output.put_line(‘The employee ‘

    || var_emp_id || ‘ has salary ‘ || var_salary);

END;

/

NOTE: The backward slash ‘/’ in the above program indicates to execute the above PL/SQL Block.

Scope of PS/SQL Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

  • Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.
  • Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.

For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable ‘var_mult’ is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables ‘var_num1’ and ‘var_num2’ can be accessed anywhere in the block.

1> DECLARE2>   var_num1 number; 3>  var_num2 number; 4> BEGIN 5>  var_num1 := 100;

6>  var_num2 := 200;

7>  DECLARE

8>   var_mult number;

9>   BEGIN

10>    var_mult := var_num1 * var_num2;

11>   END;

12> END;

13> /

PL/SQL Constants

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.General Syntax to declare a constant is:

constant_name CONSTANT data _type := VALUE;

  • constant_name is the name of the constant i.e. similar to a variable name.
  • The word CONSTANT is a reserved word and ensures that the value does not change.
  • VALUE – It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE

salary_increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.

DECLARE  salary_increase CONSTANT number(3); BEGIN  salary_increase := 100; dbms_output.put_line (salary_increase);

END;

Stored Procedures

stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages.

A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.

A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

Procedures: Passing Parameters

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS      

Declaration section

BEGIN    

Execution section

EXCEPTION  

Exception section

END;

IS – marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.

The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code.

Procedures: Example

The below example creates a procedure ‘employer_details’ which gives the details of the employee.

1> CREATE OR REPLACE PROCEDURE employer_details

2> IS

3> CURSOR emp_cur IS

4> SELECT first_name, last_name, salary FROM emp_tbl;

5> emp_rec emp_cur%rowtype;

6> BEGIN 7> FOR emp_rec in sales_cur

8> LOOP 9> dbms_output.put_line(emp_cur.first_name || ‘ ‘ ||emp_cur.last_name10>   || ‘ ‘ ||emp_cur.salary);

11> END LOOP;

12>END;

13> /

How to execute a Stored Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name; 

Trigger

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax for Creating a Trigger

CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

    — sql statements

END;

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger – An event is triggered for each row updated, inserted or deleted.
2) Statement level trigger – An event is triggered for each sql statement executed.

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

Cursors

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

Implicit cursors: These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors: They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

DECLARE

CURSOR emp_cur IS

SELECT first_name, last_name, salary FROM emp_tbl;

emp_rec emp_cur%rowtype;

BEGIN

FOR emp_rec in sales_cur

LOOP

dbms_output.put_line(emp_cur.first_name || ‘ ‘ ||emp_cur.last_name || ‘ ‘ ||emp_cur.salary);

END LOOP;

END;

/

PL/SQL Functions

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

General Syntax to create a function is

CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype;

  IS

Declaration_section  

BEGIN

Execution_section

Return return_variable;  

EXCEPTION

exception section

Return return_variable;  
END;

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle data type like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a function called ”employer_details_func’ similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func

2>   RETURN VARCHAR(20);

3> IS

5>   emp_name VARCHAR(20);

6> BEGIN

7>    SELECT first_name INTO emp_name

8>    FROM emp_tbl WHERE empID = ‘100’;

9>    RETURN emp_name;

10> END;

11> /

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the ’emp_name’ which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;

If ‘employee_name’ is of data type varchar we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);

This line displays the value returned by the function.

Package

Package is oracle database object that binds many oracle objects like procedures, cursors, functions etc. It is comprised of two parts:

  • Package Specification
  • Package Body

CREATE OR REPLACE PACKAGE c_package AS

— Adds a customer

PROCEDURE addCustomer(c_id   customers.id%type,

c_name customers.name%type,

c_age customers.age%type,

c_addr customers.address%type,

c_sal customers.salary%type);

PROCEDURE delCustomer(c_id customers.id%TYPE);

PROCEDURE listCustomer;

END c_package;

/

Package created.

CREATING THE PACKAGE BODY:

CREATE OR REPLACE PACKAGE BODY c_package AS

PROCEDURE addCustomer(c_id customers.id%type,

c_name customers.name%type,

c_age customers.age%type,

c_addr customers.address%type,

c_sal   customers.salary%type)

IS

BEGIN

INSERT INTO customers (id,name,age,address,salary)

VALUES(c_id, c_name, c_age, c_addr, c_sal);

END addCustomer;

PROCEDURE delCustomer(c_id   customers.id%type) IS

BEGIN

DELETE FROM customers

WHERE id = c_id;

END delCustomer;

PROCEDURE listCustomer IS

CURSOR c_customers is

SELECT name FROM customers;

TYPE c_list is TABLE OF customers.name%type;

name_list c_list := c_list();

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter +1;

name_list.extend;

name_list(counter) := n.name;

dbms_output.put_line(‘Customer(‘ ||counter|| ‘)’||name_list(counter));

END LOOP;

END listCustomer;

END c_package;

/

Package body created.

USING THE PACKAGE:

The following program uses the methods declared and defined in the package c_package.

DECLARE

code customers.id%type:= 8;

BEGIN

c_package.addcustomer(7, ‘Rajnish’, 25, ‘Chennai’, 3500);

c_package.addcustomer(8, ‘Subham’, 32, ‘Delhi’, 7500);

c_package.listcustomer;

c_package.delcustomer(code);

c_package.listcustomer;

END;

/

View

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

The syntax for the Oracle CREATE VIEW Statement is:

CREATE VIEW view_name AS SELECT columns FROM tables WHERE conditions;

view_name is the name of the Oracle VIEW that you wish to create.

Example

Here is an example of how to use the Oracle CREATE VIEW:

CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = ‘Microsoft’;

This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the Oracle VIEW as follows:

SELECT *FROM sup_orders;

Update VIEW

You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the Oracle CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE conditions;

Example

Here is an example of how you would use the Oracle CREATE OR REPLACE VIEW Statement:

CREATE or REPLACE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = ‘Apple’;

This Oracle CREATE OR REPLACE VIEW example would update the definition of the Oracle VIEW called sup_orders without dropping it. If the Oracle VIEW did not yet exist, the VIEW would merely be created for the first time.

Drop VIEW

Once an Oracle VIEW has been created, you can drop it with the Oracle DROP VIEW Statement.

Syntax

The syntax for the Oracle DROP VIEW Statement is:

DROP VIEW view_name;

view_name is the name of the view that you wish to drop.

Example

Here is an example of how to use the Oracle DROP VIEW Statement:

DROP VIEW sup_orders;

 

You may also like...