Stored Procedures

• A 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 of 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.

Creating a Procedure

• A procedure is created with the CREATE OR REPLACE PROCEDURE statement.

• The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name 
  [(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

• procedure-name specifies the name of the procedure.

• [OR REPLACE] option allows modifying an existing procedure.

• The optional parameter list contains name, mode and types of the parameters.
  IN represents that value will be passed from outside and OUT represents that this parameter 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 1: Creating a Procedure

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
  dbms_output.put_line('Hello World!'); 
END; 
/

Output: Procedure created.

Executing a Standalone Procedure

• A standalone procedure can be called in two ways:
  – Using the EXECUTE keyword (sql* plus)
  – Calling the name of the procedure from a PL/SQL block

The above procedure named greetings can be called with the EXECUTE keyword as:

EXECUTE greetings;

Output:
Hello World
PL/SQL procedure successfully completed.

The procedure can also be called from another PL/SQL block:

BEGIN greetings; 
END; 
/

Output:
Hello World
PL/SQL procedure successfully completed.

Deleting a Standalone Procedure

• A standalone procedure is deleted with the DROP PROCEDURE statement.

DROP PROCEDURE procedure-name;

The greetings procedure can be dropped by using the following statement:

BEGIN 
  DROP PROCEDURE greetings; 
END; 
/

---

PL/SQL Function

• A PL/SQL function is same as a procedure except that it returns a value.

Creating a Function

• A standalone function is created using the CREATE FUNCTION statement.

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 modifying an existing function.

• The optional parameter list contains name, mode and types of the parameters.

• The function must contain a RETURN statement.

• RETURN clause specifies that 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 1

This function returns the total number of CUSTOMERS in the customers table.

CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS 
  total number(2) := 0; 
BEGIN 
  SELECT count(*) into total 
  FROM customers; 
  RETURN total; 
END; 
/

Output:
Function created.

Calling a Function

• Creating a function - what the function has to do

• To use a function - call that function to perform the defined task.

DECLARE 
  c number(2); 
BEGIN 
  c := totalCustomers(); 
  dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

Output:
Total no. of Customers: 6
PL/SQL procedure successfully completed.

Example 2: Calling a Function

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; 
/

Output:
Maximum of (23,45): 45
PL/SQL procedure successfully completed.

Specifying Constraints as Assertions

• via declarative assertions, using the CREATE ASSERTION statement of the DDL.

• To specify the constraint that the salary of an employee must not be greater than the salary of the manager of the department that the employee works for

Example 3

CREATE ASSERTION SALARY_CONSTRAINT 
CHECK (
  NOT EXISTS (
    SELECT * 
    FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D 
    WHERE E.Salary > M.Salary 
    AND E.Dno = D.Dnumber 
    AND D.Mgr_ssn = M.Ssn
  )
);

This SQL statement creates an assertion to demand that there’s no more than a single president among the employees:

CREATE ASSERTION AT_MOST_ONE_PRESIDENT AS 
CHECK (
  (SELECT COUNT(*) FROM EMP e WHERE e.JOB = 'PRESIDENT') <= 1
);

---

SQL Triggers

• Objective: to monitor a database and take action when a condition occurs

• A typical trigger has three components
  – Event
  – Condition
  – Action (to be taken when the condition is satisfied)

• The action is usually a sequence of SQL statements or even a transaction or external program

Trigger syntax

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;

Example 1: SQL Trigger

CREATE OR REPLACE TRIGGER age_violation 
BEFORE INSERT OR UPDATE OF age ON employee 
FOR EACH ROW 
WHEN (new.age > 60) 
BEGIN 
  inform_supervisor; 
END;

Example 2: Procedure Called by Trigger

CREATE OR REPLACE PROCEDURE INFORM_SUPERVISOR 
AS 
BEGIN 
  dbms_output.put_line('AGE VIOLATION!'); 
END;

Example 3: Maintaining Total Salary

• The Total_sal attribute is a derived attribute

• It should be the sum of salaries of all employees assigned to a department

CREATE TRIGGER Total_sal3 
AFTER UPDATE OF Dno ON EMPLOYEE 
FOR EACH ROW 
BEGIN 
  UPDATE DEPARTMENT 
  SET Total_sal = Total_sal + NEW.Salary 
  WHERE Dno = NEW.Dno;

  UPDATE DEPARTMENT 
  SET Total_sal = Total_sal - OLD.Salary 
  WHERE Dno = OLD.Dno;
END;

CREATE TRIGGER Total_sal4 
AFTER DELETE ON EMPLOYEE 
FOR EACH ROW 
WHEN (OLD.Dno IS NOT NULL) 
BEGIN 
  UPDATE DEPARTMENT 
  SET Total_sal = Total_sal - OLD.Salary 
  WHERE Dno = OLD.Dno; 
END;