Here, we are going to explain the way for creating the stored procedure:

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;