Oracle Database Stored Procedures Tutorial

To create a procedure object (procedural object), you must have the Create PROCEDURE system permission. If this process object needs to be used by other user schemas, then you must have the CREATE any PROCEDURE permission. You may need to excute permissions when performing procedure. or excute any PROCEDURE permission

If you assign permissions individually, the following example shows:

Grant execute on My_procedure to Jelly

create a stored procedure

The code is as follows

Copy Code

Create or replace procedure Proc_data_check is --Defining variables V_LOCK_ID INTEGER; V_lockhandle VARCHAR (128); V_RELEASE_ID INTEGER; --Define error exception information encoding V_error_code VARCHAR2 (10): = '; --Define Error exception description information V_error_message VARCHAR2 (1000): = '; Cursor Check_orgs is Select Org_code, org_name from check_org; Begin Dbms_lock. Allocate_unique (' Proc_data_check ', V_lockhandle, 86400); v_lock_id: = Dbms_lock. REQUEST (V_lockhandle, Dbms_lock. X_mode, 0, FALSE); if (v_lock_id = 0) Then For check_org in Check_orgs loop Null End Loop; v_release_id: = Dbms_lock.release (V_lockhandle); COMMIT; End If; Commit EXCEPTION When others THEN v_release_id: = Dbms_lock.release (V_lockhandle); --Get the exception code V_error_code: = SQLCODE; --Get Exception description information V_error_message: = SQLERRM; --maintaining exception information in the exception table End Proc_data_check;

Modifying a stored procedure

Let's write an output string "Hello world!" Stored procedure, open your Skeleton.sql file with Notepad,. With Dbms_output. Put_Line The procedure call to replace the NULL statement as follows:

The code is as follows	Copy Code
CREATE OR REPLACE PROCEDURE skeleton Is BEGIN Dbms_output. Put_Line (' Hello world! '); End;

Save to file Skeleton.sql.
From the Sql*plus command line, open the file skeleton.sql.

The code is as follows	Copy Code
Sql> @skeleton Sql> CREATE OR REPLACE PROCEDURE skeleton Is BEGIN Dbms_output. Put_Line (' Hello world! '); * END; Sql>/ Sql*plus notifies you that the stored procedure successfully creates and outputs a message: Procedure created. Sql>

Run your stored procedure with the Execute command:

The code is as follows	Copy Code
sql> EXECUTE skeleton;

Sql*plus shows that the stored procedure is running successfully: Pl/sql procedure successfully completed.
We want the output string "Hello world!" Did not come out, in the display of a dbms_output. Put_Line you need to run a set command before the result, at the Sql*plus command line prompt, type:

The code is as follows	Copy Code
Sql> SET Serveroutput on Execute your stored procedure again: sql> EXECUTE skeleton; Now the result is output: Hello world!

Pl/sql procedure successfully completed.

the difference between stored procedures (PROCEDURE) and functions (function).

The function has a return value, and you can reference the function in query directly or use the return value of the function.

There is no difference in nature, both are pl/sql programs, can have a return value. The fundamental difference is that a stored procedure is a command, and a function is part of an expression. Like what:

Select Max (NAME) from

But you cannot exec max (NAME) If Max is a function at this point.

Package is a combination of function,procedure,variables and SQL statements. Package allows multiple procedure to use the same variable and cursor.