Learn how to create and drop procedures in Oracle/PLSQL with syntax and examples.
Just as you can in other languages, you can create your own procedures in Oracle.
The syntax to create a procedure in Oracle is:
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
- IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
- OUT - The parameter can not 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.
Let's look at an example of how to create a procedure in Oracle.
The following is a simple example of a procedure:
CREATE OR REPLACE Procedure UpdateCourse ( name_in IN varchar2 ) IS cnumber number; cursor c1 is SELECT course_number FROM courses_tbl WHERE course_name = name_in; BEGIN open c1; fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; INSERT INTO student_courses ( course_name, course_number ) VALUES ( name_in, cnumber ); commit; close c1; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;
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.
Once you have created your procedure in Oracle, you might find that you need to remove it from the database.
The syntax to a drop a procedure in Oracle is:
DROP PROCEDURE procedure_name;
procedure_name is the name of the procedure that you wish to drop.
Let's look at an example of how to drop a procedure in Oracle.
DROP PROCEDURE UpdateCourse;
This example would drop the procedure called UpdateCourse.