This article is written about how to create and drop methods in Oracle/PLSQL with syntax and examples.
Just as you can in different languages, you can create your very own processes 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 technique or function, you can also define parameters. There are three kinds of parameters that can be declared:
IN – The parameter can be referenced by means of the manner or function. The cost of the parameter can no longer be overwritten by way of the method or function. OUT – The parameter can not be referenced by means of the manner or function, however the fee of the parameter can be overwritten with the aid of the process or function. IN OUT – The parameter can be referenced by means of the technique or characteristic and the price of the parameter can be overwritten via the method or function.
Let’s look at an example of how to create a system 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 manner is known as UpdateCourse. It has one parameter referred to as name_in. The system will look up the course_number primarily based on route name. If it does not locate a match, it defaults the path wide variety to 99999. It then inserts a new report into the student_courses table.
Once you have created your procedure in Oracle, you may discover 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 The title of the process that you desire to drop.
Let’s look at an example of how to drop a method in Oracle.
DROP PROCEDURE UpdateCourse;
This instance would drop the manner referred to as UpdateCourse.