Oracle PL/SQL:Stored Procedures
A procedure is a subprogram that performs a specific action . A stored procedure is a PL/SQL block that is stored in the database with a name. It is invoked using the name. Each procedure is meant for a specific purpose.
A stored procedure is stored in the database as an object. It is also called as database procedure as it is stored in the database.
A procedure may take one or more parameters. If a procedure takes parameters then these parameters are to be supplied at the time of calling the procedure
You write procedures using the syntax :-
PROCEDURE name [(parameter[, parameter, ...])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
where :-
>>parameter stands for the following syntax:
parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr]
>>You cannot impose the NOT NULL constraint on a parameter.
>>you cannot specify a constraint on the datatype. For example, the following declaration of emp_id is illegal:
PROCEDURE … (emp_id NUMBER(4)) IS — illegal; should be NUMBER
BEGIN … END;
>> A procedure has two parts:
>> the specification.
>>the body.
>>The procedure specification :-
>>begins with the keyword PROCEDURE .
>>ends with the procedure name or a parameter list.
>>Parameter declarations are optional. Procedures that take no parameters are written without parentheses.
>>The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name.
>> The procedure body has three parts:
>> a declarative part.
>> an executable part.
>>and an optional exception-handling part.
>>The declarative part contains:
local declarations, which are placed between the keywords IS and BEGIN.
The keyword DECLARE, which introduces declarations in an anonymous PL/SQL block, is not used.
>> The executable part contains:
statements, which are placed between the keywords BEGIN and EXCEPTION (or END).
At least one statement must appear in the executable part of a procedure.
The NULL statement meets this requirement.
>> The exception-handling part contains:
exception handlers, which are placed between the keywords EXCEPTION and END.
Example 1
Consider the procedure raise_salary, which increases the salary of an employee:
PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + increase
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, ‘No such number’);
WHEN salary_missing THEN
INSERT INTO emp_audit VALUES (emp_id, ‘Salary is null’);
END raise_salary;
When called:
-this procedure accepts an employee number and a salary increase amount.
- It uses the employee number to select the current salary from the emp database table.
- If the employee number is not found or if the current salary is null, an exception is raised. Otherwise, the salary is updated.
A procedure is called as a PL/SQL statement. For example, you might call the procedure raise_salary as follows:
CALLING: raise_salary(emp_num, amount);
Example 2
Consider the procedure compute_stud_score, which computes the total mark and average of an student and storing into stud_score table:
PROCEDURE compute_stud_score (stud_id INTEGER) IS
mark_1 number(3);
mark_2 number(3);
mark_3 number(3);
total_mark number(4);
mark_avg number(3);
BEGIN
SELECT mark1,mark2,mark3 INTO mark_1,mark_2,mark_3 FROM student WHERE student_id = stud_id;
total_mark:=mark_1+mark_2+mark_3;
mark_avg =total_mark /3;
– inserting mark and average into stud_score table
INSERT INTO stud_score
VALUES( stud_id,total_mark,mark_avg);
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO stud_audit
VALUES (stud_id, ‘No such Idr’);
END compute_stud_score;