"Plsql" procedure procedure parameter and parameters

************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************1.1Processprocedure

Syntax:

Create or replace procedure Proc_name

[(Arg_name{in | out | On out} type, ...)

]

{is | as}

<local variable declareation>

Begin

<executable statements>

exception

<exception handlers>

End

To create a stored procedure sample:

Create or replace procedure Account_number

Ls

V_cnt Binary_interger;

Begin

Select COUNT (id) into v_cnt from account;

Dbms_output.put_line (' account number is ' | | v_cnt);

End

1.1.1call a stored procedure

Call directly in the SQL worksheet:

exec account_number;

1.1.2parameters of the stored procedure

Like what:

Create or replace procedure Proc1

(P_c1 varchar2,p_c2 out varchar2,p_c3 in out varchar2)

Ls

V_C1 varchar (10);

Begin

--p_c1: =p_c1 | | ' d '; -- cannot be the target of replication

V_C1:=P_C1;

P_C2: =P_C1 | | ' d ';

P_C3 "=p_c3| |" d ';

End

Called as:

exec proc1 (' abc ', ' abc ', ' abc '); Save

Call:

Declare

V_C2 Varchar2 (Ten): = ' abc '

V_c33 varchar2: = ' abc ';

Begin

Proc1 (' abc ', V_C2,V_C3);

Dbms_output.put_line (V_C2);

Dbms_outpurt.put_line (V_C3);

End

1.1.3formal participation and actual participation

The parameters in the Create stored procedure are as follows:

Create or replace procedure Proc1

(P_c1 varchar2,p_c2 out varchar2,p_c3 in out varchar2)

Ls

V_C1 varchar (10);

V_c1 as a local variable in the process

The argument in parentheses is called when the parameter is

Proc1 (' abc ', V_C2,V_C3);

Type of participation:

In : default mode. The internal form of the process is read-only;

Out ; Inside the process, the shape participates in the ability to read and write; No matter what the actual participation will be ignored, that is, the value will not be assigned to the shape, the completion of the process, the current value assigned to the corresponding actual participation;

In out : called during the process. The value of the actual participation is assigned to the corresponding shape, inside the process, able to read, can write. After the process has finished running. Control is returned to the control environment. The memory assigned to the call when the actual participation.

Requirements for the actual participation:

The pattern is the corresponding actual participation in the shape of the in. Can be constants and variables

The actual argument of a province for the form of out or out must be a variable; used to store the returned value;

Restrictions on form participation:

The shape cannot declare the length. But the ability to use %TYPE to limit

3.1.4Procedure call with a number of parameters

Position notation:

-- Add all the arguments to the call, the actual participation and the formal participation according to the order one by one corresponding

Name notation:

- -Give the name of the form when calling. and give the actual participation. Like what

procname (12,p_outparm +> v_var1,p_inout =) ；

Two methods can be used to mix:

when mixing, the first parameter must be specified in accordance with the location class.

When the number of references is very high. Provide the readability of the program, using the name notation;

3.1.5Use default number of parameters

The shape can indicate the default value

Parm_name [mode] type {: =|default} int_value;

Position indication is that all default values are placed on the last side;

When declaring, assume that there are default values and try to place the default values at the end of the tables;

3.1.6of the stored procedureDDLStatement

In the process of running DDL operations, the required permissions must be granted through direct grant, not by role;

Call the procedure at the time. All the characters are disable . That is, all the permissions that the role includes are not valid.

The process of invoking another user must have the owner of the process give the Run permission

Grant Execut on procname to Userame;

CREATE PROCEDURE Proc1

Ls

Begin

Execute immediate ' CREATE TABLE test100 (c1 number) ';

End

************************************************************************* * * Original:blog.csdn.net/clark_xu Xu Changliang column************************************************************************

"Plsql" procedure procedure parameter and parameters