Oracle Security

Stored Programs

Programs written in PL/SQL can be stored in compiled form in the database. These programs are referred to as either procedures or functions. The only difference is that, by definition, a function must return a value, while a procedure does not have to return a value. However, there is no problem in coding a procedure to return a value. Stored programs can be executed by table triggers, applications, or users. In this book, we use the phrase “stored program” or “program” to refer to both procedures and functions.

Stored programs can be created using either of two methods. You can create the program simply as a program, or you can create it in two parts: a package that specifies the programs to be implemented, and a package body containing the actual code.

Executing a Procedure or Function

Users or applications can execute stored programs provided that EXECUTE permission on the program has been granted. Unlike table triggers, procedures and functions are executed by an explicit call. For example, suppose a procedure called give_raise has been written and requires an employee number and percentage of raise as arguments. The call might then look like this:

SQL>  EXECUTE give_raise(8138, 23);

PL/SQL procedure successfully completed

Executing a function interactively is a bit more complex, as a function always returns a value and the calling statement structure must be able to receive the value. Stored functions can be included in SQL statements. The position in the ...