Oracle Stored Procedures

Briefly

A PL/SQL program block named in the database that can pass several parameters and usually have no return value . cannot be directly SQL Call Execution , but by Execute command or PL/SQL call execution in the program block .

Syntax format

Create [or Replace] procedure stored procedure name [( parameter )] Is|as

Begin

Body Code

[exception

Exception handling Statements ]

End [ name ];

Description : the or in 1.or replace cannot be omitted

2.Create or replace is not created , There is a overwrite of the original

3. after creation , the principal statement is simply compiled and not executed

Call execution

1.execute Command

Execute Stored procedure name ;

2. Using the PL/SQL program block

Begin

...

The name of the stored procedure ;

End;

Parameter mode

1.in mode ( input type parameter , parameter is passed in by caller and can only be read by stored procedure )

Parameter name in data type [ default parameter value ]

Use the default description :

A. Use default to set defaults for parameters .

B. When using the default value , want to give some of the default value of the pass value , and others do not pass the value of the time , it is convenient to use by name to pass the value of the way

Three ways to pass in parameters

A. Specifying a name pass (parameter name = = value passed in Parameters ... , parameter name = = value passed in )

B. Passing by position ( parameter value ..., parameter value )

C. Mixed mode transfer combines the above two ways , but first by location , followed by name

( parameter value ... , parameter value , parameter name = = passed in argument value ... , parameter name = = parameter value passed in )

2.Out mode ( Output type parameter , parameter value can be passed to an environment other than the current stored procedure )

Variable name out data type

Call The Out- mode stored procedure :

A. Calling in a PL/SQL block

Declare the variable in the declare section , and pass the argument directly to it.

B. Invoking with the execute command

defining variables with variable (variable variable name data type )

Execute Stored procedure name ( Parameters ) (execute stored procedure name (): variable name ) ;

Description : The value of the in , the direct value can be , followed by out The value of the type to be received using the defined variable , preceded by :

Then use the print or select statement to output the value

The Print variable name variable name ;

Select: variable name ,: variable name from dual;

3.In out mode ( combines in mode and out mode )

The variable name in the Out data type ;

Oracle Stored Procedures