Common oracle Stored Procedure skills

Common oracle Stored Procedure skills
1. Stored Procedure structure 1.1 The 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, it is just a new stored procedure. 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. The parameter names cannot be repeated. The parameter transmission method is IN, OUT, in out in, which indicates the input parameter and value-based transmission. 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 Parameter Variable declaration block: followed by the as (is) keyword, can be understood as the declare keyword of pl/SQL, 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. This part is divided into optional end blocks: results are obtained by the end keyword. */