Plsql stored Procedure (basic)-Go

I am not a dedicated developer, but the stored procedure is a very important knowledge, in order to be able to very good memory, the basic knowledge is now summed up. The stored procedure can realize the full sharing of the code and improve the system performance.

A review of basic knowledge

If you often use specific operations, which can be considered based on these operational processes, simplifying development and maintenance, you can fully realize code sharing, improve system performance.

Classification of the process:

(i) There are process parameters

(ii) no process parameters 1. Input parameters (default is input parameter) 2. Output parameters 3. Input and output parameters basic syntax rules: Create or Replace procedure procedure _name (argument1 [Mode1] datatype1, argument2 [Mode2] datatype2 ...) is [As]pl/sql Block; Note: When you define a parameter, you can specify only the data type, and you cannot specify a length. Example One: Parameterless process Create or replace procedure a_timeisbegindbms_session.set_nls (' Nls_date_format ', ' yyyy-mm-dd ');d Bms_ Output.put_line (sysdate); end;/ Execution Process:(Calling a parameterless procedure can refer to the procedure name directly) sql> exec a_time;2008-03-04 pl/sq L Process completed successfully Example Two: Process with input parameters:sql> Create or replace procedure B_insert (i emp.id%type,n emp.name%type) 2 is3 begin4 INSERT INTO EMP values (i,n); 5 commit;6 end; The sql> process has been created. perform this procedure:sql> exec B_insert (' + ', ' Peter_lin ');P the L/SQL process has been successfully completed. Sql> SELECT * from emp;id          name                                                                    --------------------                                                             14          peter_lin                                                             1           DICK_t                                                                    can see that the data is automatically inserted into the table. Example Three:Procedure with output parameters:sql> Create or replace procedure c_update (old varchar2,new Emp.id%type,nam out Emp.name%type) 2 Is3 begin4 sel ECT name to Nam from emp where id=old;5 update emp set id=new where id=old;6 commit;7 end; The sql> process has been created. perform this procedure:Procedures with output parameters, you need to use variables to receive this output value. sql> declare2 nn emp.name%type;3 begin4 c_update (&OLD,&NEW,NN); 5 dbms_output.put_line (' Employee name Modified ID: ' | | NN); 6 end;7/input old value: 14 Enter the value of new: 12     4:c_update (&OLD,&NEW,NN); new value      4:c_update (14,12,NN); The name of the employee whose ID was modified:peter_lin                                                 The     PL/SQL process has completed successfully. Sql> SELECT * from emp;id         name                                                                    ---------------- ----                                                             12         peter_lin                                                               1          DICK_t                                                                    2 rows have been selected. Example four:Procedure with input and output parameters sql>create or replace procedure In_out2 (N1 in out number,n2 in out number) IS3 v1 number;4 v2 number;5 begi N6 v1:=trunc (N1/N2); 7 v2:=mod (N1,N2); 8 n1:=v1;9 n2:=v2;10 end; The sql> process has been created. perform this procedure:Sql>declare2 A1 number:=&n1;3 A2 number:=&n2;4 Begin5 in_out (A1,A2); 6 dbms_output.put_line (' Quotient of division ' | | a1| | ', remainder of division: ' | | A2); 7 end; Sql> input N1 Value: 100 original value 2:A1 number:=&n1; new value 2:a1 number:=100; input n2 Value: 3 Original value 3:A2 number:=&n2; new value 3:a 2 number:=3;

Quotient of Division 33, remainder of division: 1

Plsql stored Procedure (basic)-Go