Common oracle Stored Procedure skills (details) and oracle Stored Procedure skills
What we deal with most during pl/SQL programming is the stored procedure. The structure of the stored procedure is very simple. In addition to the basic structure of the stored procedure, we will also learn some practical knowledge about the stored procedure. Such as: processing of cursors, exception handling, set selection, etc.
1. Storage Process Structure
1.1 first Stored Procedure
Create or replace procedure proc1 (p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2) as v_name varchar2 (20); begin v_name: = 'zhang Sanfeng '; p_para3: = v_name; dbms_output.put_line ('P _ para3: '| p_para3); end;
The above is the simplest stored procedure. A stored procedure is divided into the following parts:
Create statement: 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, an error is returned. Create or replace procedure: if the system does not have this stored procedure, Create a new one. If this stored procedure exists in the system, delete the original one and recreate a stored procedure.
Stored Procedure name definition: includes the stored procedure name and parameter list. Parameter Name and parameter type. Parameter names cannot be repeated. parameter transmission methods: IN, OUT, IN OUT
IN indicates the input parameter, which is passed by value.
OUT indicates the output parameter, which can be understood as a reference transfer method. It can be used as the output result of the stored procedure for external callers.
In out can be used as input parameters or output parameters.
You only need to specify the type name for the Data Type of the parameter, and do not need to specify the width.
The parameter width is determined by the external caller.
The process can have parameters or no parameters.
Variable declaration block: followed by the as (is) keyword, which can be understood as the declare keyword of pl/SQL and used to declare variables.
The variable declaration block is used to declare the variables required for the stored procedure. Its scope is the stored procedure. In addition, the declared variable must specify the width. Complies with PL/SQL variable declaration specifications.
Process statement block: The statement block of the process starting with the begin keyword. The specific logic of the stored procedure is implemented here.
Exception Handling block: the keyword is exception, which is the exception generated by the processing statement. Optional
End Block: results by the end keyword.
1.2 parameter transfer method of Stored Procedure
There are three methods for passing stored procedure parameters: IN, OUT, in out.
IN is passed by value, and it cannot be re-assigned IN the stored procedure. If the stored parameter transfer type is not specified for the stored procedure parameter, the default value is IN.
Create or replace procedure proc1 (p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2) as v_name varchar2 (20); begin p_para1: = 'aaa'; p_para2: = 'bbb '; v_name: = 'zhang Sanfeng '; 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 -------- percentage 8/3 PLS-00363: expression 'P _ PARA1 'cannot be used as an assignment target 8/3 PL/SQL: Statement ignored
This is different from other advanced languages. It is equivalent to adding the final keyword before the parameter in java.
OUT parameter: as an output parameter, note that when a parameter is specified as the OUT type, the parameter is assigned a value before the stored procedure is called, in the stored procedure, the value of this parameter is still null.
Create or replace procedure proc1 (p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2) as v_name varchar2 (20); begin v_name: = 'zhang Sanfeng '; p_para3: = v_name; partition ('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 (10); SQL> var p2 varchar2 (10); SQL> var p3 varchar2 (10); SQL> exec: p1: = 'aaa '; SQL> exec: p2: = 'bbbbb'; SQL> exec: p3: = 'cccccc'; SQL> exec proc1 (: p1,: p2,: p3); p_para1: aaaa p_para2: p_para3: Zhang Sanfeng SQL> exec dbms_output.put_line (: p2); PL/SQL procedure successfully completed p2 ---------
INOUT is a real parameter passed by reference. It can be used as an input parameter or an output 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 v_name varchar2(2); begin v_name := p_para1; end; SQL> var p1 varchar2(10); SQL> var p2 varchar2(20); SQL> var p3 varchar2(30); SQL> exec :p1 :='aaaaaa'; SQL> exec proc1(:p1,:p2,:p3); ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "LIFEMAN.PROC1", line 8 ORA-06512: at line 1
First, we need to understand that we cannot specify the storage parameter width in the stored procedure definition, which leads to the failure to control the width of input variables in the stored procedure.
This width is completely determined by the external input.
Let's take a look at the width of the OUT type parameter.
create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as 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 :p2 :='a'; SQL> exec proc1(:p1,:p2,:p3);
During this process, p_para2 is assigned 20 characters.
In the external call process, the p2 parameter is only defined as varchar2 (1 ).
When p2 is called as a parameter, no error is reported. And its actual value is 20
SQL> select dump(:p2) from dual; DUMP(:P2) --------------------------------------------------------------------------- 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 --------- aaaaaaaaaaaaaaaaaaaa
Let's take a 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 v_name varchar2(2); begin p_para3 :='aaaaaaaaaaaaaaaaaaaa'; end; SQL> var p1 varchar2(1); SQL> var p2 varchar2(1); SQL> var p3 varchar2(1); SQL> exec proc1(:p1,:p2,:p3);
Execute this process correctly.
It can be seen that the width of the IN parameter is determined by the outside.
For the OUT and in out parameters, the width is determined by the stored procedure.
Therefore, it is necessary to describe the parameter width when writing a stored procedure. The most sensible method is to use % type for the parameter data type. In this way, both parties reach an agreement.
1.3 default value of the Parameter
You can set default values for stored procedure parameters.
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'); mark
You can use the default keyword to specify the default value for the stored procedure parameters. When you call a stored procedure, you can omit the default value.
Note that the default value only supports IN transmission parameters. The default values of OUT and in out cannot be specified.
If a parameter with a default value is not the last parameter.
create or replace procedure procdefault2(p1 varchar2 default 'remark', p2 varchar2 ) as begin dbms_output.put_line(p1); end;
The first parameter has a default value, and the second parameter does not. If you want to use the default value of the first parameter
exec procdefault2('aa');
In this case, an error is reported.
How can it be changed? You can specify the parameter value.
SQL> exec procdefault2(p2 =>'aa'); remark
In this case, it is okay to specify aa and pass it to the p2 parameter.
2. Internal block of the stored procedure
2.1 internal Blocks
We understand the structure of the stored procedure. The statement block starts from begin and ends with end. These blocks can be nested. You can nest Any of the following blocks in the statement block.
Declare … begin … exception … end; create or replace procedure innerBlock(p1 varchar2) as o1 varchar2(10) := 'out1'; begin dbms_output.put_line(o1); declare inner1 varchar2(20); begin inner1 :='inner1'; dbms_output.put_line(inner1); declare inner2 varchar2(20); begin inner2 := 'inner2'; dbms_output.put_line(inner2); end; exception when others then null; end; end;
Note the scope of the variable.
3. Common skills in Stored Procedures
3.1 which collection?
When using stored procedures, we often need to process Record Sets, that is, multiple data records. It can be divided into a single column, multiple rows, and multiple columns. These types can be called set types. We will compare these Collection types here to make the right choice during programming.
Index tables, also known as pl/SQL tables, cannot be stored in databases. There is no limit on the number of elements and the subscript can be negative.
type t_table is table of varchar2(20) index by binary_integer; v_student t_table;
Varchar2 (20)Indicates the Data Type of the stored element, and binary_integer indicates the Data Type of the underlying element.
Nested table. An index table is a nested table without an index by clause. It can be stored in data. The number of elements is infinite. The subscript starts from 1 and needs to be initialized.
type t_nestTable is table of varchar2(20); v_class t_nestTable ;
This statement is not applicable only. You must initialize the nested table. You can use its constructor to initialize the nested table.
v_class :=t_nestTable('a','b','c');
The above description is all about this article. I hope it will help you learn the oracle stored procedure.