[PLSQL] process procedure parameters and real parameters, plsqlprocedure

[PLSQL] process procedure parameters and real parameters, plsqlprocedure
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************1.1 process procedure

Syntax:

Create or replace procedure proc_name

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

]

{Is |}

<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.1 call the Stored Procedure

Directly call in the SQL Worksheet:

Exec account_number;

1.1.2 stored procedure parameters

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'; -- the target cannot be copied.

V_c1: = P_c1;

P_c2: = p_c1 | 'D ';

P_c3 "= p_c3 | 'D ';

End;

Call:

Exec proc1 ('abc', 'abc', 'abc'); save

Call:

Declare

V_c2 varchar2 (10): = 'abc'

V_c33 varchar2 (20): = 'abc ';

Begin

Proc1 ('abc', v_c2, v_c3 );

Dbms_output.put_line (v_c2 );

Dbms_outpurt.put_line (v_c3 );

End;

1.1.3 form parameters and real parameters

Parameters in the 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 is a local variable in the process.

When calling, the parameters in brackets are real parameters.

Proc1 ('abc', v_c2, v_c3 );

Types of parameters:

In: default mode. The internal parameters in the process are read-only;

Out; during the process, the form parameters can be read and written; During the call process, any real parameters will be ignored, that is, the values will not be assigned to the form parameters; after the process is completed, the current value is assigned to the corresponding real parameter;

In out: During the call process, the value of the real parameter is assigned to the corresponding form parameter, which can be read or written internally. After the process is executed, the control is returned to the control environment, the parameter memory is assigned to the real parameter during the call;

Requirements for real parameters:

The real parameter corresponding to the In parameter. It can be a constant or a variable.

The actual parameter corresponding to an IN out or OUT parameter must be a variable. It is used to store the returned value;

Restrictions on parameters:

The length of the parameter cannot be declared, but it can be limited by % TYPE.

3.1.4 process call with Parameters

Location representation:

-- All parameters are added for the call. The real parameters and parameters correspond to each other in sequence.

Name representation:

-- The name of the parameter is given during the call, and the real parameter is given, for example

Procname (12, p_outparm +> v_var1, p_inout =) 10 );

The two methods can be used in combination:

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

When there are many parameters, it provides program readability and uses name notation;

3.1.5 use default parameters

The parameter can specify the default value.

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

The location indication method is that all default values are placed at the end;

If a default value exists during declaration, try to put the default value at the end of the parameter table;

3.1.6 DDL statements for Stored Procedures

To execute DDL operations during the process, the required permissions must be directly granted, rather than through the role;

During the call process, all roles are disable, that is, all permissions contained in the role cannot take effect;

When calling other users, the owner of the process must grant the execution permission.

Grant execut on procname to userame;

Create procedure proc1

Ls

Begin

Execute immediate 'create table test100 (c1 number )';

End;

**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************