Create Procedure   «Prev  Next»

Lesson 6

Oracle Functions and Procedures Conclusion

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

Oracle RDBMS: Procedure versus Stored Procedure

The short answer is: In the context of Oracle RDBMS, there's no functional distinction between a procedure and a stored procedure. They are essentially synonyms. Here's why:
  • Oracle's Terminology: Oracle's documentation consistently uses both terms interchangeably. You'll find references to both "procedures" and "stored procedures" to describe the same thing.
  • Definition: In Oracle, both procedures and stored procedures refer to named blocks of PL/SQL code that are stored within the database schema and can be executed to perform tasks.

Potential Origins of Confusion:
  • Other Databases: Some database systems, like Microsoft SQL Server, make a technical distinction between procedures and stored procedures. However, this distinction doesn't hold within the Oracle RDBMS environment.

Key Takeaways
  • When working with Oracle databases, you can use the terms "procedure" and "stored procedure" without worrying about any difference in their meaning or functionality.
  • Both serve the purpose of encapsulating logic within the database for reusability and to organize your database code.


CREATE PROCEDURE statement

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.

Calling Procedures within Triggers

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;
/

The next module shows you how to create another very popular form of stored sub-program: the trigger.

SEMrush Software