2. Procedures & Functions

When writing Procedures and Functions use the following syntax:

CREATE OR REPLACE { PROCEDURE | FUNCTION }
NAME [ (params... ) ] [ RETURN datatype ] {AS | IS }
-- declare Variables here
[ EXCEPTION ]
- -exception handling here
END [ name ];
          

In the syntax above,anything in curly bracket '{ }' means its required, the pipe '|' means OR ,the square bracket '[ ]'means its optional.

The Function keyword only uses the the keyword RETURN, then its datatype which can be DATE,VARCHAR2(),NUMBER.
The AS | IS keyword can be used, they are required using either of them, Then we now declare our Variables after the AS or IS. The EXCEPTION follows,here we declare our exception handling and finally END with whatever name of Procedure or Function, which is  Required.

Procedures: Procedures are subprogram that performs specific actions,are stored in the database dictionary,they do not return a value when executed.
You write procedures using the SQL CREATE PROCEDURE statement. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions.

In this example below we would login as user hr password hr.
SQL>conn hr
> password hr
SQL>CREATE OR REPLACE PROCEDURE give_raises(r NUMBER)
1      >AS
2      >BEGIN
3      >FOR x IN 1..11 LOOP
4      >UPDATE employees SET salary = salary * r
5      >WHERE employee_id = ( Select manager_id from departments WHERE department_id= x *10);
6      >END LOOP;
7      >EXCEPTION
8      >When no_data_found THEN null;
9      >END;
10    >/

The image below shows the statement was a success.

Writing pl/sql statements

In Procedures,instead of having the DECLARE keyword,we have the AS keyword,anything that happens between AS-BEGIN is going to be a variable declaration,so i am not declaring any variable.

The image below shoes the tables that belongs to user scott.

The main chunk of code is from BEGIN line to EXCEPTION line, In the BEGIN block of code,we used a FOR LOOP,which ends using END LOOP.
Now lets us query this procedure by assigning give_raises 20 percent increment.
SQL>exec give_raises(1.20);

           exec give_raises(1.20);
           

OR you can run an anonymous block : they perform the same function.

SQL>BEGIN
2      >give_raises(1.20);
3      >END;
4      >/

BEGIN
give_raises(1.20);
END;
/
           

The salaries of employees whose employee_id is the same as their manager_id and who are in department id from 10-110 (department_id=x*10,for x in 1..11) are going to be increased by 20%.

In a nut shell, for x in 1,department_id would be10.then manager_id would be 200,so if manager=200,employee_id=200,then that salary would be updated,if not,exception which gives NULL. Same as when x=2,department_id=x*10 gives 20, then manager_id=201,so if employee_id=201 that persons salary is updated.

Function : Functions are subprograms stored in the Data dictionary that returns a value when executed using the RETURN keyword. You write FUNCTIONS using the SQL CREATE OR REPLACE FUNCTION statement.
You specify the name of the function, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions.

In this example we would login as user hr password hr.

SQL >CREATE OR REPLACE FUNCTION sum_sals (d employees.department_id%TYPE)
2       >RETURN number AS
3       >total NUMBER;
4       >BEGIN
5       >SELECT sum(salary)
6       >INTO total
7       >FROM employees
8       >WHERE department_id = d;
9       >RETURN total;
10     >EXCEPTION
11     >WHEN No_data_found THEN
12     >RETURN 0;
13     >END sum_sals;
14     >/
Function created

CREATE OR REPLACE FUNCTION sum_sals (d employees.department_id%TYPE) 
RETURN number AS
total NUMBER;
BEGIN
SELECT sum(salary)
INTO total
FROM employees
WHERE department_id = d;
RETURN total;
EXCEPTION
WHEN No_data_found THEN 
RETURN 0;
END sum_sals;
/

The image below shows the function query was successful.

Writing pl/sql statements

This function returns the SUM of salaries in number datatype of the employees who have a certain department id.
To call this function we write the following SELECT statement.

SQL >SELECT sum_sals(100)  "Salary Increment"
2       >FROM dual;

Salary Increment
------------------------
54009.6

Another Example of using the function sum_sals.
if we want to query job_id,sum of salary of employees at department 40,we run the following query to call on function sum_sals.

SQL >SELECT job_id, sum_sals(department_id)
2      >FROM employees
3     >where department_id=40;