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:
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.