Oracle Stored Procedure learning notes

Oracle Stored Procedure learning notes
Stored Procedure

A stored procedure is a type of block named pl/SQL. It can be assigned parameters and stored in a database and can be called by users.The stored procedure is compiled code, so you do not have to compile the code again when calling it, thus improving the program running efficiency.. In addition, the storage process can implement modular design of the program.

1. Create a stored procedure

Syntax:

 Create [or replace] procedure procedure_name   [ (parameter[{in|in out}]) data_type，   (parameter[{in|in out}]) data_type，   ……   ]  { is|as}   Decoration section  Begin     Executable section;  Exception     Exception handlers;  End;  

Name of the Procedure_name Stored Procedure
Parameter Parameters
In transmits parameters to the stored procedure
Out: parameters returned from the Stored Procedure
In out: Pass and return parameters
Data_type: parameter typeCannot specify length
As | the main process body of the variable declared after is, And the declare statement cannot be added..

// Create a Stored procedure SQL> create procedure insert_emp as begin insert into emp (empno, ename, job, mgr, sal, comm, deptno) values ('20140901', 'redarmy', 'teacher', '20160901', 7777, 7369, 20); commit; end insert_emp ;/

2. Call the Stored Procedure

SQL> set serveroutput on; SQL> begin insert_emp; end; /

3. Modify the Stored Procedure

SQL> create or replace procedure insert_emp as // you only need to add or replace to modify the stored procedure. You can modify begin insert into emp (empno, ename, job, mgr, sal, comm, deptno) values ('20170101', 'redarmy', 'teacher', '20170101', 9000,1000, 20); commit; end insert_emp ;/

4. Parameters

Oracle has three parameter models: in, out, and in out.

(1). in Parameters

This type of parameter value can only be read by stored procedures and is also the default format.

Case:

SQL> create or replace procedure insert_emp( cempno in number, cename in varchar2, cjob in varchar2, cmgr in number, chiredate in date, csal in number, ccomm in number, cdeptno in number ) as begin insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(cempno,cename,cjob,cmgr,chiredate,csal,ccomm,cdeptno); end insert_emp; / Procedure created 

The stored procedure created above requires input and output parameters. There are three ways to input parameters in oralce:

Name Representation

Syntax:

Parameter Name => parameter value. Multiple parameters are separated by commas.

SQL> set serveroutput on; SQL> begininsert_emp(cempno=>7377,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20); end; / 

Location Representation

When there are many parameters, the name representation may be relatively long. To overcome the disadvantages of the name representation, you can use the location representation. Note that the parameters must correspond.

SQL> set serveroutput on; SQL> begin insert_emp(3333,'mjjj','stu',7777,to_date('2011-01-01','YYYY-MM-dd'),5000,1000,20); end; / 

Hybrid notation

SQL> set serveroutput on; SQL> begin insert_emp(9999,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20); end; /

Note: When you use a hybrid notation, the line must be consistent before the demarcation line, and the line must be consistent after the demarcation line, and cannot be interspersed.

(2). out parameters

This type of parameter value is a parameter with the Stored Procedure write. out type. It is applicable when the stored procedure returns multiple pieces of information to the caller.

// Create an SQL> create or replace procedure emp_select (cempno in number, cename out emp. ename % type, csal out emp. sal % type) is begin select ename, sal into cename, csal from emp where empno = cempno; exception when NO_DATA_FOUND then cename: = 'null'; csal: = 0; end emp_select; /Procedure created

Call the stored procedure:

The output parameter is the return value, which means that a variable that can accept the returned value must be provided when the stored procedure is called.
Here we need to use the variable command to bind Parameters

SQL> variable ename varchar2 (20); // declaration of binding parameters SQL> variable sal number; SQL> begin emp_select ('2013',: ename,: sal ); // execute the Stored procedure end;/PL/SQL procedure successfully completed ename --------- redarmy sal --------- 9000

SQL> print ename; // print the corresponding parameter ename --------- redarmy

SQL> print sal; // print the corresponding parameter sal --------- 9000

(3). in out parameters

The in parameter can receive a value, but it cannot be modified in the stored procedure. For the out parameter, It is null during the calling process and will specify a value for this parameter during execution, and return after execution.
The in out parameter also features the in parameter and out parameter, and can read and write this type of parameter during the process.

// Job implementation case exchange two numbers create or replace procedure test_pro (num1 in out number, num2 in out number) asnum3 number; begindbms_output.put_line (before 'change: '| num1 | ''| num2); num3: = num1; num1: = num2; num2: = num3; dbms_output.put_line (' after replacement: '| num1 | ''| num2); end test_pro;

// Execution result SQL> set serveroutput on; SQL> declare num1 number; num2 number; begin num1: = 1; num2: = 2; test_pro (num1, num2); end; /before replacement: 1 2 after replacement: 2 1PL/SQL procedure successfully completed