Lesson 6

This module looked at creating functions and procedures, two of the most commonly used stored sub-programs. You created a function, a procedure, and then modified the procedure so that it called the function. In this module, you learned how to:

1. Distinguish between a function and a procedure
2. Create a procedure to calculate a percentage and an average for a given sales transaction
3. List capabilities and restrictions for calling other procedures
4. Add a function to a 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

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.

Rather than creating a large block of code within a trigger body, you can save the code as a stored procedure and call the procedure from within the trigger, by using the call command. For example, if you create an INSERT_BOOKSHELF_AUDIT_DUP procedure that inserts rows into BOOKSHELF_AUDIT_DUP, you can call it from a trigger on the BOOKSHELF_AUDIT table, as shown in the following listing:

create or replace trigger BOOKSHELF_AFT_INS_ROW
after insert on BOOKSHELF_AUDIT
for each row
begin
call INSERT_BOOKSHELF_AUDIT_DUP(:new.Title, :new.Publisher,
:new.CategoryName, :new.Old_Rating, :new.New_Rating,
:new.Audit_Date);
end;
/