Learn PL/SQL with meπŸ€

Learn PL/SQL with meπŸ€

Β·

10 min read

Play this article

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:

  1. Implicit cursor.

  2. 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;

References

Did you find this article valuable?

Support Sagar Medtiya by becoming a sponsor. Any amount is appreciated!

Β