Plsql stored Procedure (basic)-Go

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.