- IN - The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.
- OUT - The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
- IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
Tuesday, March 19, 2013
What is Procedure in PL/SQL
In Oracle, you can create your own procedures.
The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
The following is a simple example of a procedure:
CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
cursor c1 is
where course_name = name_in;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
insert into student_courses
values ( name_in,
WHEN OTHERS THEN
raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.