Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
//declaration
BEGIN
//execution
[EXCEPTION]
END ;
Example:
CREATE OR REPLACE PROCEDURE emp_salary_increase
(emp_id IN emptbl.empID%type, salary_inc IN OUT emptbl.salary%type)
IS
tmp_sal number;
BEGIN
SELECT salary
INTO tmp_sal
FROM emp_tbl
WHERE empID = emp_id;
IF tmp_sal between 10000 and 20000 THEN
salary_inout := tmp_sal * 1.2;
ELSEIF tmp_sal between 20000 and 30000 THEN
salary_inout := tmp_sal * 1.3;
ELSEIF tmp_sal > 30000 THEN
salary_inout := tmp_sal * 1.4;
END IF;
END;
Dropping a Procedure:
Syntax:
DROP FUNCTION procedure_name;
Example:
DROP FUNCTION UpdateAuthor;