Orace PROCEDURE

Use the CREATE PROCEDURE statement to create a standalone stored procedure or a call specification.

A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

Prerequisites

Before creating a procedure, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create a procedure in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a procedure in another schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call spec, you may need additional privileges, for example, the EXECUTE object privilege on the C library for a C call spec.

To embed a CREATE PROCEDURE statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

OR REPLACE

Specify "OR REPLACE" to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.

PARAMETERS

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared

1. IN - Specify IN to indicate that you must supply a value for the argument when calling the procedure.
2. OUT - Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.
3. IN OUT - Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you do not specify IN, OUT, and IN OUT, then the argument defaults to IN.

Examples:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

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;