Techniques commonly used in Oracle stored procedures (detail) _oracle

The most we deal with in Pl/sql programming is the stored procedure. The structure of the stored procedure is very simple, and we are here to learn some useful knowledge about how to write stored procedures in addition to the basic structure of the stored procedures. such as: cursor processing, exception handling, selection of sets, etc.

1. Stored Procedure Structure

1.1 First stored procedure

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C7/>v_name VARCHAR2 (a);  
Begin  
 V_name: = ' Zhang San Feng ';  
 P_PARA3: = V_name;  
 Dbms_output.put_line (' p_para3: ' | | P_PARA3);  

The above is the simplest stored procedure. A stored procedure is generally divided into several parts:

Creation statement: Create or Replace procedure stored procedure name

If there is no or replace statement, just create a new stored procedure. If the stored procedure exists on the system, an error is found. Create or replace procedure if the stored procedure is not in the system, create a new one, if the system has this stored procedure to delete the original, recreate a stored procedure.

Stored procedure name definition: Includes stored procedure name and parameter list. Parameter name and type of parameter. Parameter names cannot be repeated, parameters are passed in:

In represents the input parameter, which is passed by value.

Out represents an output parameter that can be understood to be passed by reference. Can be used as an output result of a stored procedure for external callers.

In out can be used as input parameters, but also as output parameters.

The data type of the parameter requires only the type name to be specified, and no width is required.

The width of the parameter is determined by the external caller.
Procedures can have parameters, or they can have no parameters

Variable declaration BLOCK: the immediately following as (IS) keyword, which can be understood as the Pl/sql declare keyword, used to declare variables.

A variable declaration block is used to declare a variable that the stored procedure needs to use, and its scope is the stored procedure. In addition the variables declared here must specify the width. Follow the PL/SQL variable declaration specification.

PROCEDURE Statement BLOCK: A statement block that begins with the START keyword as a procedure. The specific logic of the stored procedure is implemented here.

Exception handling BLOCK: The keyword is exception, the exception that is generated to handle the statement. The department is divided into optional

End BLOCK: The result of the ending keyword.

1.2 parameter passing mode of stored procedure

There are three ways to pass the parameters of a stored procedure: In,out,in out.

In is passed by value, and it is not allowed to be assigned a value in a stored procedure. If the parameter of the stored procedure does not specify a save parameter delivery type, the default is in

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C5/>v_name VARCHAR2 (a);  
Begin  
 P_para1: = ' aaa ';  
 P_para2: = ' BBB ';  
 V_name: = ' Zhang San Feng ';  
 P_PARA3: = V_name;  
 Dbms_output.put_line (' p_para3: ' | | P_PARA3);  
 null;  
End;  
    
Warning:procedure created with compilation errors  
 
sql> show error;  
Errors for PROCEDURE Lifeman. PROC1:  
 
line/col ERROR  
------------------------------------------------------------------------------  
8/3   Pls-00363:expression ' p_para1 ' cannot be used as a assignment target  
8/3 Pl/sql:statement ignored    

This is different from other advanced languages. It is equivalent to Java in front of the parameter plus final keyword.

Out parameter: As an output parameter, note that when a parameter is specified as an out type, the value of the parameter is still null in the stored procedure, even if the parameter is assigned before the stored procedure is called.

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C5/>v_name VARCHAR2 (a);  
Begin  
 V_name: = ' Zhang San Feng ';  
 P_PARA3: = V_name;  
 Dbms_output.put_line (' p_para1: ' | | P_PARA1);  
 Dbms_output.put_line (' p_para2: ' | | P_PARA2);  
 Dbms_output.put_line (' p_para3: ' | | P_PARA3);  
End;  
 
sql> var p1 varchar2 (a);  
sql> var p2 varchar2 (a);  
sql> var p3 varchar2 (a);  
sql> EXEC:p 1: = ' aaaa ';  
Sql> EXEC:p 2: = ' bbbb ';  
Sql> EXEC:p 3: = ' CCCC ';  
sql> exec Proc1 (:p 1,:p 2,:p 3);  
P_PARA1:AAAA  
p_para2:  
p_para3: Zhang  
sql> exec dbms_output.put_line (:p 2);  
 
 
Pl/sql procedure successfully completed  
P2  

INOUT is really a passing argument by reference. Can be passed as an incoming parameter or as an outgoing parameter.

1.3 Stored Procedure parameter width

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C5/>v_name VARCHAR2 (2);  
Begin  
 V_name: = p_para1;  
End;  
sql> var p1 varchar2 (a);  
sql> var p2 varchar2 ();  
sql> var p3 varchar2 ();  
sql> EXEC:p 1: = ' aaaaaa ';  
sql> exec Proc1 (:p 1,:p 2,:p 3);  
Ora-06502:pl/sql:numeric or value Error:character string buffer too small  
ora-06512:at "Lifeman. PROC1 ", line 8 

First, we have to understand that we cannot specify the width of the stored parameter in the definition of the stored procedure, and we cannot control the width of the incoming variable in the stored procedure.

This width is determined entirely by the external incoming time.

Let's look at the width of the parameters of the out type.

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C5/>v_name VARCHAR2 (2);  
Begin  
 P_para2: = ' aaaaaaaaaaaaaaaaaaaa ';  
End;  
sql> var p1 varchar2 (1);  
sql> var p2 varchar2 (1);  
sql> var p3 varchar2 (1);  
Sql> EXEC:p 2: = ' a ';  

In this process, the P_PARA2 is given a 20 character a.

In the external invocation process, the P2 parameter is only defined as VARCHAR2 (1).

And the P2 as a parameter call this process, but did not error. And its real value is 20 aces.

Sql> Select Dump (:p 2) from dual;  
DUMP (:P 2)  
---------------------------------------------------------------------------  
typ=1 len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97 
p2  
---------  

And look at the width of the in out parameter

Create or Replace procedure Proc1 (  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in Out varchar2  
) as< C5/>v_name VARCHAR2 (2);  
Begin  
 P_para3: = ' aaaaaaaaaaaaaaaaaaaa ';  
End;  
sql> var p1 varchar2 (1);  
sql> var p2 varchar2 (1);  
sql> var p3 varchar2 (1);  

Execute this process, still execute correctly.

Visible, for in parameters, the width is determined externally.

For out and in out parameters, the width is determined by the inside of the stored procedure.

Therefore, when writing stored procedures, it is very necessary to explain the width of the parameters, the most sensible method is the data type of the parameter using%type. So the two sides reached a consensus.

1.3 Default values for parameters

Parameters of stored procedures can set default values

Create or Replace procedure Procdefault (P1 varchar2,  
                    p2 varchar2 default ' mark ')  
as  
begin  
 Dbms_ Output.put_line (p2);  
End;  
Sql> set serveroutput on;  
sql> exec procdefault (' a '); 

You can specify a default value for the parameters of a stored procedure by using the default keyword. When you call a stored procedure, you can omit the default value.
It should be noted that the default value only supports parameters in the in transport type. Out and in out cannot specify default values

Parameters that have default values are not in the final case.

Create or Replace procedure Procdefault2 (P1 varchar2 Default ' remark ',  
                    p2 varchar2)  
as  
begin  
 Dbms_ Output.put_line (p1);  

The first parameter has a default value and the second argument does not. If we want to use the default value of the first parameter

This will be an error.

How does that change? You can specify the value of the parameter.

sql> exec procdefault2 (p2 => ' AA '); 

So that's OK, specify AA pass parameter P2

2. Stored procedure internal block

2.1 Inner Block

We know the structure of the stored procedure, and the statement block begins with begin and ends with end. These blocks can be nested. You can nest any of the following blocks in a statement block.

Declare ... begin ... exception ... end;  
Create or Replace procedure Innerblock (P1 varchar2)  
as  
 O1 varchar2 (a): = ' out1 ';  
Begin  
 Dbms_output.put_line (O1);  
 DECLARE  
  inner1 varchar2 ();  
 Begin  
  Inner1: = ' inner1 ';  
  Dbms_output.put_line (inner1);  
 
  DECLARE  
   inner2 varchar2 ();  
  Begin  
   Inner2: = ' inner2 ';  
   Dbms_output.put_line (inner2);  
  End;  
 Exception when  
  others then  
   null;  
 End  

You need to be aware of the scope of the variable.

3. Common techniques for Stored procedures

3.1 What kind of collection?

When we use stored procedures, we often need to process the recordset, that is, more than one data record. A single row of multiple rows and rows of multiple columns, all of which can be called collection types. We're here to compare these collection types so that we can make the right choices when we're programming.

The index table, also known as the Pl/sql table, cannot be stored in the database, the number of elements is unlimited, and the subscript can be negative.

Type t_table is Table of VARCHAR2 (a) index by Binary_integer;  
 

VARCHAR2 represents the data type that holds the element, Binary_integer the data type that represents the subscript of the element.

Nested tables, where the index table has no index BY clause is nested table, it can be stored in the data, the number of elements is infinite, subscript starting from 1, and need to initialize

Type t_nesttable is Table of VARCHAR2 (20);  

Just so the declaration is not available, you must initialize the nested table and initialize the nested table to use its constructor

V_class: =t_nesttable (' A ', ' B ', ' C ');

The above description is the entire contents of this article, I hope to learn from the Oracle stored procedures help.