PL/SQL is a procedural language that overcomes the shortcomings faced by Structured Query Language. So, why do we need PL/SQL when we have SQL? SQL is a query language. It is not a functional language. We can use insert, delete or alter keywords. But what if we want to write a function, a condition, or loops in SQL? It is an extension of SQL and we can even use SQL queries without any hassle in any PL/SQL application. While SQL is having DDL and DML, PL/SQL has program blocks, triggers, functions, procedures and cursors. In this PL/SQL tutorial, we will go through the basic concepts of PL/SQL in detail.
PL/SQL block
Syntax:
DECLARE BEGIN EXCEPTION END;
A simple program that prints a string to the console.
DECLARE message varchar2(20):= 'Hi, this me!';
BEGIN dbms_output.put_line(message);
END;
This prints numbers from 1 to 10, divided by 2 in the console.
DECLARE i number; //i is the variable and number is the datatype
BEGIN i:=1; /initialize the variable
dbms_ouput.put_line(i/2); //prints the number
i:= i + 1;
exit when i>10; //check the condition
end loop;
END;
We can embed SQL queries into PL/SQL functions. This will print the number of employees in the employee table. PL/SQL is just like another programming language. It has variables, constants, loops, and functions.
Example
DECLARE emp_count number;
BEGIN select count(*) into emp_count from EMPLOYEES;
dbms_output.put_line(emp_count);
END;
These PL/SQL blocks can be named and called from somewhere else. Such a thing is called a Subprogram. There are two types of subprograms.
Procedure
Function
Procedure
A subprogram is a program unit/module that performs a particular task. A subprogram can be created -
At the schema level
Inside a package
Inside a PL/SQL
At the schema level, the subprogram is standalone. It is created with CREATE PROCEDURE or the CREATE FUNCTION statement.
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [,....])]
{IS | AS}
BEGIN
< procedure_body>
END procedure_name;
Where,
procedure_name specifies the name of the procedure.
[OR REPLACE] option allows the modification of an existing procedure.
The optional parameter list contains the name, mode and types of the parameter. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
procedure_body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Example
The below procedure prints the subtraction of two numbers.
CREATE OR REPLACE PROCEDURE subtraction(x IN number, y IN number) // Get two input number
AS BEGIN
dbms_output.put_line(x-y);
END;
The following procedure takes two numbers and returns its sum. This procedure is declared in a PL/SQL block.
DECLARE //we declare the variable and their data types
a number;
b number;
c number;
PROCEDURE getSum(X IN number, Y IN number, Z OUT number)
//The procedure with the input and output variables
IS
BEGIN
Z := X + Y; // The execution and assigning the value to return
dbms_output.put_line('The sum is: '|| Z);
END;
BEGIN
getSum(12,70,c); //call the procedure with the correct number of arguments in it//
END;
Output: The sum is: 82
Parameter Modes in PL/SQL Subprograms
IN: An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It is the default mode of parameter passing. Parameters are passed by reference.
OUT: An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
IN OUT: An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter is passed by value.
Function
Functions are pretty similar to procedures but function always have a return value, unlike procedures.
Creating Function
A standalone function is created using the CREATE FUNCTION statement. The syntax is as follows
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [,...])]
RETURN return_datatype
{IS | AS}
BEGIN
<function_body>
END [function_name];
function_name specifies the name of the function.
[OR REPLACE] option allows the modification of an existing function.
The optional parameter list contains the name, mode and types of the parameter. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
The function must contain a return statement.
The RETURN clause specifies the data type you are going to return from the function.
function_body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone function.
Example
A function that returns the multiplication of two input numbers.
CREATE OR REPLACE FUNCTION multiplication(a number, b number) //It gets two number
RETURN number // Return a value in type number
IS
mul NUMBER := 0;
BEGIN mul:= a*b;
RETURN mul;
END;
DECLARE a number;
BEGIN
a:= multiplication(20,12);
dbms_output.put_line(a);
END;
Another Example
The following example demonstrates declaring, defining and invoking a simple PL/SQL function that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findmax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
z:=y;
END IF ;
RETURN z;
END;
BEGIN
a:=23;
b:=45;
c:= findmax(a,b);
dbms_output.put_line('Maximum of(23,45):' || c);
END;
Cursors
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the row(one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
There are two types of cursors:
Implicit cursor.
Explicit cursor.
There are some attributes for the cursors. %ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT are those attribute. How these attributes are working is as below.
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in them.
Whenever a DML statement(INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
Example
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The below program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected -
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%notfound THEN
total_rows := sql%rowcount;
dbms_output.put_line(total_rows || 'customers selected');
END IF;
END;
After the code is executed and we check the records in the customers table, you will find that the rows have been updated.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement that returns more than one row.
Syntax
CURSOR cursor_name IS select_statement;
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id,name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' C_addr);
END LOOP;
CLOSE c_customers;
END;
Trigger
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are written to be executed in response to any of the following events,
A database manipulation(DML) statement (DELETE, INSERT or UPDATE).
A database definition(DDL) statement (CREATE, ALTER or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP or SHUTDOWN).
Creating Triggers
Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTAED OF}
{INSTEAD [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH]
WHEN (condition)
DECLARE
declaration-statement
BEGIN
executablr-stataments
EXCEPTION
exception-handling-statements
END;
CREATE [OR REPLACE] TRIGGER trigger_name creates or replaces an existing trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} specifies when the trigger will be executed. The INSTEAD OF clause is used for creating a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} specifies the DML operation.
[OF col_name] specifies the column name that will be updated.
[ON table_name] specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n] allows you to refer new and old values for various DML statements, such as INSERT, UPDATE and DELETE.
[FOR EACH ROW] specifies a row-level trigger, the trigger will be executed for each row being affected. Otherwise, the trigger will execute just once when the SQL statement, which is called a table-level trigger.
WHEN(condition) provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.
Example
customers_table
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The following program creates a row-level trigger for the customer table that fires for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values,
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customer
FOR EACH ROW
WHEN (NEW.ID >0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old Salary: ' || :OLD.salary);
dbms_output.put_line('New Salary: ' || :NEW.salary);
dbms_output.put_line('Salary Difference: '|| sal_diff);
END;
Points to be considered
OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.
If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
Exception
Exception block in the program and an appropriate action is taken against the error condition. There are two types of exceptions:
System-defined exceptions
User-defined exceptions
Syntax
DECLARE
declaration_section
BEGIN
executable_command
EXCEPTION
exception_handling
WHEN exception1 THEN
exception1_handling_statements
WHEN exception2 THEN
exception2_handling_statements
WHEN exception3 THEN
exception3_handling_statements
.....
WHEN others THEN
exception_handling_statements
END;
Example
DECLARE
c_id customers.id%type :=8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name,address INTo c_name, c_addr
FROM customers
WHERE id = c_id;
dbms_output.put_line("Name: " || c_name);
dbms_output.put_line("Address: " || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line("No such customer!");
WHEN others THEN
dbms_output.put_line("Error");
END;
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE.
DECLARE
exception_name Exception;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
User-defined Exceptions
A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure
DBMS_STANDALONE.RAISE_APPLICATION_ERROR.
Syntax:
DECLARE
my-exception EXCEPTION;
Example
DECLARE
c_id cusomers.id%type :=&cc_id;
c_name customers.Name%type;
c_addr customers.address%type;
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <=0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address, INTO c_name, c_addr
FROM customers
WHERE id = c_id;
dbms_output.put_line('Name: '|| c_name);
dmbs_output.put_line('Address: '|| c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line("ID must be greater ");
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;