Oracle Stored Procedures

1. Stored Procedure Structure
1.1 First stored procedure

Create or Replace procedure Proc1 (para1 varchar2,para2 out varchar2,para3 on out varchar2) asv_name varchar2; begin V_n Ame: = ' zhangsf '; PARA3: = V_name;dbms_output.put_line (' para3: ' | | PARA3);
End

This is one of the simplest stored procedures. A stored procedure is broadly divided into the following parts:
Creating statements: Create or Replace procedure stored procedure name
If there is no or replace statement, only a new stored procedure is created. If the stored procedure exists on the system, an error will be found. Create or replace procedure if there is no such stored procedure in the system, create a new one, and if there is a stored procedure in the system, delete the original and recreate a stored procedure.
Stored procedure name definition: includes the stored procedure name and the parameter list. The parameter name and the type of the argument. Parameter names cannot be duplicated, parameters are passed in
In represents an input parameter and is passed by value.
Out represents an output parameter, which can be understood as a pass-by-reference method. Can be used as the output of a stored procedure for external callers.
In-out can be used as input parameters or as output parameters.
The data type of the parameter only needs to indicate the type name and does not need to specify a width.
The width of the parameter is determined by the external caller.
The procedure can have parameters, or it can have no parameters
Variable declaration blocks: Immediately following the AS (IS) keyword, the DECLARE keyword, which can be understood as PL/SQL, is used to declare variables.
A variable declaration block declares a variable that the stored procedure needs to use, scoped to the stored procedure. In addition the variables declared here must specify the width. A variable declaration specification that follows PL/SQL.
PROCEDURE Statement BLOCK: A statement block that starts with the BEGIN 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 for the processing statement. The department is divided into optional
End BLOCK: Result of the end keyword.

1.2 How to pass parameters to stored procedures
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 re-assigned in the stored procedure. If the parameters of a stored procedure do not have a parameter delivery type specified, the default is in

Create or Replace procedure Proc1 (  para1 varchar2,  para2 out varchar2,  para3 in Out varchar2) as  v_name VA Rchar2 (a); begin  Para1: = ' aaa ';  Para2: = ' BBB ';  V_name: = ' zhangsf ';  PARA3: = V_name  dbms_output.put_line (' para3: ' | | PARA3);  Null;end;

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

First, we have to understand that we cannot specify the width of the stored parameters in the definition of the stored procedure, and we cannot control the width of the passed 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 proc2 (para1 varchar2,para2 out varchar2,para3 on out varchar2) asv_name varchar2 (2); beginpara2 : = ' aaaaaaaaaaaaaaaaaaaa '; end;--call Proc2var p1 varchar2 (1); var p2 varchar2 (1); var p3 varchar2 (1); exec:p 2: = ' a '; EXEC proc 1 (:p 1,:p 2,:p 3);

In this process, P_para2 is given 20 characters a.
In the external invocation process, the P2 parameter is only defined as VARCHAR2 (1).
Instead of calling the P2 as a parameter, the process does not give an error. And its real value is 20 a.

Perform this process and still execute correctly.

Visible, for the in parameter, its width is determined externally.
For out and in out parameters, the width is determined internally by the stored procedure.
Therefore, when writing a stored procedure, it is necessary to describe the width of the parameter, and the wisest way is to use%type for the data type of the parameter. So the two sides reached a consensus.

1.3 Default values for parameters
Parameters for stored procedures can be set to default values

Create or Replace procedure Procdefault (  p1 varchar2,  p2 varchar2 default ' mark ') Asbegin  dbms_output.put_ Line (p2); end;

Mark
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 is important to note that the default value only supports parameters in the 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) asbegin  Dbms_ Output.put_line (p1); end;

The first parameter has a default value, and the second parameter does not. If we want to use the default value of the first parameter
exec procdefault2 (' AA ');
This will be an error.
How does that change? You can specify the value of the parameter.

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

Remark
This will be OK, specify AA to pass to the parameter P2


2. Stored procedure internal block
2.1 Internal 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. Beign exception ... end;create or replace procedure innerblock (  p1 varchar2) as  O1 varchar2 (Ten): = ' out1 '; begi N  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 and then        null;  End;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, which is a number of data records. Divided into single-row and multi-column multiple rows, these types can be called collection types. Here we compare these collection types so that we can make the right choices when programming.
An index table, also known as a PL/SQL table, cannot be stored in a database, there is no limit on the number of elements, and the subscript can be negative.

3.2 What cursors are selected?
The display cursors are divided into: normal cursors, parameterized cursors and cursor variables three kinds.
The following is a procedure to illustrate

Create or Replace procedure Proccursor (  p varchar2) as  v_rownum number (Ten): = 1;  Cursor C_postype is a select Pos_type from pos_type_tb1 where rownum = 1;  Cursor C_postype1 is a select Pos_type from pos_type_tb1 where rownum = V_rownum;  Cursor C_postype2 (p_rownum number) is a select Pos_type from pos_type_tb1 where rownum = P_rownum;  Type t_postype is REF CURSOR;  C_postype3 T_postype;  V_postype VARCHAR2 (a); Begin  Open c_postype;  Fetch c_postype into V_postype;    Dbms_output.put_line (v_postype);  Close C_postype;  Open c_postype1;  Fetch c_postype1 into V_postype;    Dbms_output.put_line (v_postype);  Close C_postype1;  Open C_postype2 (1);  Fetch c_postype2 into V_postype;    Dbms_output.put_line (v_postype);  Close c_postype2;  Open C_postype3 for select Post_type from pos_type_tb1 where rownum=1;  Fetch c_postype3 into V_postype;    Dbms_output.put_line (v_postype);  Close c_postype3end;

Cursor C_postype is select Pos_type from pos_type_tbl where RowNum =1
This sentence defines one of the most common cursors, the entire query has been written to death, the call can not make any changes.
Cursor C_postype1 is a select Pos_type from pos_type_tbl where rownum = V_rownum;
This sentence is not written to death, the query parameters are determined by the variable v_rownum. It is important to note that V_rownum must be declared before this cursor definition.
Cursor C_postype2 (p_rownum number) is a select Pos_type from pos_type_tbl where rownum = P_rownum;
This statement, similar to the second, is a dynamic query that can be implemented for cursors. But it further narrows the scope of the parameters. But readability is reduced a lot.
Type t_postype is REF CURSOR;
C_postype3 T_postype;
A reference cursor type is defined before a cursor variable is declared.
Open C_postype3 for select Pos_type from pos_type_tbl where rownum = 1;
Then open for a query. It is important to note that it can be used multiple times to open different queries.
Cursor variables are best used in terms of dynamics, but reading is also the worst.
Note that the cursor definition can only be used to make the keyword is, which is not common with AS.

3.3 Cursor Loop best strategy
When we are doing PL/SQL programming, we often need to iterate through the data of the result set. For progressive processing, this process requires that the cursor be looped. There are several ways to loop a cursor, which we analyze here.

Create or Replace procedure proccycle (P varchar2) as cursor C_postype is select Pos_type,description from Pos_type_tb1 w Here RowNum < 6;v_postype varchar2, v_description varchar2, Beginopen c_postype;if C_postype%found then Dbms_ Output.put_line (' found true '); ElseIf C_postype%found = False Then Dbms_output.put_line (' found false '); else dbms_output  . Put_Line (' found null '); end If;loop fetch c_postype into v_postype,v_description;  Exit when C_postype%notfound; Dbms_output.put_line (' Postype: ' | | v_postype| | ', Description: ' | |  v_description); end loop;close C_postype;dbms_output.put_line ('---loop end---'); open c_postype;  Fetch c_postype into v_postype,v_description; While C_postype%found loop Dbms_output.put_line (' Postype: ' | | v_postype| | ', Description: ' | |  V_description);  Fetch c_postype into v_postype,v_description; End loop;close C_postype;dbms_output.put_line ('---while end---'), for V_pos in C_postype loop v_postype: = V_pos.pos_type  ;  V_description: = v_pos.description; Dbms_output.put_line (' Postype: ' | | v_postype| | ', Description: ' | | v_description); end Loop;dbms_output.put_line ('---for end---'); end;

A cursor is required before the cursor is used, and the open cursor closes the cursor close after the loop is finished.
This is the rule that the cursor should be carefully remembered in mind.
The above procedure demonstrates three ways to loop a cursor.
Before we discuss the loop method, let's take a look at the properties of these cursors,%found and%notfound.

Open c_postype;if C_postype%found then  dbms_output.put_line (' found true '), ElseIf C_postype%found = False  Then Dbms_output.put_line (' found false '); else  dbms_output.put_line (' found null '); End if;

After you open a cursor, check its%found or%notfound property immediately, and the result is not true or false. Instead, it is null. After a FETCH statement must be executed, these properties are value.

The first use of loop loops

Loop  fetch c_postype into v_postype,v_description;  Exit when C_postype%notfound;  ... end loop;

It is important to note that the exit when statement must immediately follow the fetch. Unnecessary data processing will be avoided.
The processing logic needs to follow the exit when. This requires a bit of caution.
Remember to close the cursor after the loop is over

The second uses the while loop.

Fetch c_postype into v_postype,v_description;while c_postype%found loop   ... Fetch c_postype into v_postype,v_description;end loo;

We know that after a cursor is opened, the FETCH statement must be executed once, and the cursor's properties will only work. So when using the while loop, you need a fetch action before the loop.
and the data processing must be placed before the Fetch method in the loop body. The Fetch method in the loop body should be put at the end. Otherwise, it will be processed more than once. Be very careful with this.
In summary, using while to loop through cursors is the most complex method.

Third type for loop

For V_pos in C_postype loop  v_postype: = V_pos.pos_type;  V_description: = v_pos.description;  ... end loop;

Visible for loop is a relatively simple and practical method.
First, it will automatically open and close cursors. Resolves the annoyance that you forgot to turn the cursor on or off.
Other, automatically defines a record type and declares the type of the variable, and automatically fetch the data into this variable.
We need to note that v_pos this variable does not need to be declared outside of the loop, it is not necessary to specify a data type for it.
It should be a record type, and the specific structure is determined by the cursor.
The scope of this variable is only in the loop body.
Think of V_pos as a record variable, if you want to get a value just like a record call.
such as V_pos.pos_type
This shows that the For loop is the best way to loop a cursor. efficient, concise and safe.
Unfortunately, it is the first method that is often seen. So I have to change this habit from now on.

3.4 Select INTO non-visual issues
We know that in PL/SQL, to assign a value to a variable from a data table, you need to use the SELECT INTO clause.
But it will drive some problems, and if the query is not logged, it throws a No_data_found exception.
If there are more than one record, a Too_many_rows exception is thrown.
This is a rather bad one. Once an exception is thrown, the process is interrupted. In particular, no_data_found this anomaly, not serious enough to let the program to the point of interruption, can be completely handed out by the program processing.

Create or Replace procedure Procexception (P varchar2) as  v_postype varchar2 (a); Begin  Select Pos_type into v_ Postype from POS_TYPE_TB1 where 1=0;--cliff is empty, report no_data_found error  Dbms_output.put_line (v_postype); end;

There are three ways of dealing with this.
1. Add exception handling directly.

Create or Replace procedure Procexception (P varchar2) as  v_postype varchar2 (a); Begin  Select Pos_type into v_ Postype from POS_TYPE_TB1 where 1=0;--cliffs are empty  dbms_output.put_line (v_postype);-Workaround 1: Increase exception Handling module exception when  No_data_found then    dbms_output.put_line ("Data not Found"); end;

In this case, the program is still interrupted. Maybe it's not what we want.
2. Select INTO as a separate block for exception handling in this block

Create or Replace procedure Procexception (P varchar2) as  v_postype varchar2 (a); Begin  Select Pos_type into v_ Postype from POS_TYPE_TB1 where 1=0;--cliffs are empty  dbms_output.put_line (v_postype);--Actually, I think it's the same principle as above, no Zuo nod Ieexception when  No_data_found then    v_postype: = ";  End;  Dbms_output.put_line (v_postype); end;

This is a better way to deal with it. does not cause the program to break because of this exception.
3. Using Cursors

Create or Replace procedure Proceexception (P varchar2) as  v_postype varchar2 (a);  Cursor C_postype is a select Pos_type from pos_type_tb1 where 1=0;beign  open c_postype;  Fetch c_postype into V_postype;  Close C_postype;  Dbms_output.put_line (v_postype); end;

--Personally think it would be better to create or replace procedure Proceexception (P varchar2) as V_postype varchar2 in order to make non-null judgments first  ;  Cursor C_postype is select Pos_type from pos_type_tb1 where 1=0;beign  if C_postype%found then    open c_postype;
   fetch c_postype into V_postype;    Close C_postype;    Dbms_output.put_line (v_postype);  End If;end;

This completely avoids the no_data_found exception. Completely controlled by the programmer.

The second case is the problem of too_many_rows anomalies.
Too_many_rows This problem is more complicated than no_data_found.
When assigning a value to a variable, the query results have multiple records.
There are two ways of dealing with this problem:
1. More than one piece of data is acceptable, meaning that you can take a value from the result set. This should be extreme, if this is the case, it also shows that the rigor of the program has problems.
2. Many of the data is not acceptable, in this case is certainly the logic of the program is a problem, it is said that the original would not have thought it would produce more than one record.
In the first case, the cursor must be used for processing, and for the second case it must be handled with an internal block, and the exception will be re-thrown.
Multiple data can be accepted, take a random one, this is the same as No_data_found, using cursors.
I'm only saying the second case, not a lot of data, but do not forget to deal with No_data_found oh. You cannot use cursors, you must use an inner block.

Create or Replace Proceduer Procexception2 (P varchar2) asv_postype varchar2; begin  begin    Select Pos_type Into V_postype from POS_TYPE_TB1 where RowNum < 5;  Exception when No_data_found and then      v_postype: = null;    When Too_many_rows then      raise_application_error (-20000, ' Assign to V_postype, find more than one data ');  End;  Dbms_output.put_line (v_postype); end;

Note that the no_data_found must be added to the processing, the occurrence of multiple records will continue to throw exceptions, let the previous layer to deal with.
In short, you need to be aware of both cases for the SELECT INTO statement. It needs to be handled properly.

3.5 Returning a result set in a stored procedure
We use stored procedures to return a single value, sometimes we need to return a collection from the procedure. That is, more than one piece of data. There are several solutions. It is relatively simple to write a temporary table, but this is not a flexible approach. and maintenance trouble. We can do this using nested tables. None of the collection types can match the JDBC type of java. This is the impedance of the object to the relational database. Database objects are not fully converted to programming language objects, and must also be handled using relational databases

Oracle Stored Procedures