oracle--Stored Procedures

A stored procedure is a program block stored in a data dictionary that can be shared among different users and applications, and enables optimization and reuse of programs.

First, the creation and execution of stored procedures

1. Create stored procedures using SQL commands

The syntax format is as follows:

SQL code create [or Replace] procedure [schema.] procedurename[(param1 mode1 dataType1,... N)] is |         As Var1 type1;         Var2 type2; ... begin statements; /* process body, to perform the operation * * END;

Where the mode1 represents the type of the parameter, like the parameter of the method, there are three types in, out, and in out; DataType1 represents the data type of the parameter.

Sample code:

SQL code Create or replace procedure Getmodulename (mid in number,mname out varchar) as begin select name into     Mname from T_module where Id=mid; End

2. Calling stored Procedures

The syntax format is as follows:

SQL code Exec[ute] procedurename[(param1,... N)]/* This is done at the command window.

Note:

You can also execute a defined stored procedure by entering the name of a stored procedure directly

Sample code:

SQL code DECLARE MID number: = 15;   Mname varchar (20);     Begin Getmodulename (mid,mname); Dbms_output.put_line (Mname | |   ' ********modulename '); End

Second, edit the stored procedure modification

While modifying a stored procedure and modifying a view, there is also an ALTER PROCEDURE statement, but it is used to recompile the stored procedure. If you want to modify a stored procedure that you have already defined, you still use the Create or replace procedure statement.

For example, modify the above getmodulename stored procedure as follows:

SQL code Create or replace procedure Getmodulename (mid number,mname out varchar) as BEGIN if mid > 0th       En select name into Mname from T_module where Id=mid;       else mname: = null;     End If; End

Third, delete stored procedures

When a stored procedure is no longer needed, it can be removed to free the memory resources it occupies.

The syntax format is as follows:

SQL code drop procedure [schema.] ProcedureName;

For example, delete the getmodulename stored procedure as follows:

SQL code drop procedure Getmodulename;