Formal parameters and arguments for the "plsql" process procedure

************************************************************************* * * * 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

Example of creating a stored procedure:

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

For example:

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 parameters and arguments

Parameters in the Create stored procedure are formal:

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

Parameters in parentheses are arguments when calling

Proc1 (' abc ', V_C2,V_C3);

Types of formal parameters:

In : default mode, the internal parameter of the procedure is read-only;

Out ; Inside the procedure, the formal parameter can be read and written, and any real arguments is ignored during the invocation, that is, the value is not assigned to the parameter, and the current value is assigned to the corresponding argument after the completion of the process execution;

In out : during the call process, the value of the argument is assigned to the corresponding parameter, the inside of the procedure can be read, can be written, and after the execution of the process, the control is returned to the control environment, and the parameter memory is assigned to the argument at the call time;

Requirements for an actual parameter:

the argument corresponding to the parameter in which the pattern is in, which can be a constant and a variable

The actual parameter corresponding to the form parameter of a province for out or out must be a variable;

Limitations of formal parameters:

Parameters cannot be declared length, but can be restricted by using %TYPE

3.1.4Procedure call with Parameters

Position notation:

-- Add all arguments to the call, arguments and parameters correspond in order one by one

Name notation:

- -give the parameter name when called, and give the argument, for example

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

Two methods can be mixed:

when mixing, the first parameter must be specified according to the location class;

When the parameters are many, the readability of the program is provided by the name notation;

3.1.5Using default parameters

Formal parameters can indicate default values

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

The position indicator is that all default values are placed on the last side;

When declaring, if there is a default value, try to place the default value at the end of the parameter table;

3.1.6of the stored procedureDDLStatement

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

When the procedure is called, all roles are disable , that is, all the permissions contained in the role are not valid;

The process of invoking another user must have the owner of the procedure give execute 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's Column************************************************************************

Formal parameters and arguments for the "plsql" process procedure