Oracle stored procedure Detailed (Reference) + Supplemental (RPM)

I. Process (stored procedure)

A procedure is a subroutine that can perform a specific operation. Create or replace a subroutine that is saved in the database by using the creates or replace.
Example 1: declaring a stored procedure that returns the number of Dept table rows

Declareprocedure getdeptcountAsdeptcount INT; BEGINSELECT COUNT (*) into Deptcount from Dept;dbms_output. Put_Line (' Total number of records for the Dept table: ' | | Deptcount); END Getdeptcount; Begingetdeptcount[()]; END;    

Note: This stored procedure getdeptcount only works when the block is running.
Example 2: create a stored procedure with no parameters, which returns the number of Dept table rows

create or REPLACE PROCEDURE getdeptcountas | isdeptcount int; Beginselect COUNT (*) into Deptcount from Dept;dbms_output. Put_Line ( ' dept table in total ' | | deptcount| | ' Line Record ');
  end [Getdeptcount];       

 

When we create a stored procedure that has no parameters, you cannot have parentheses after the stored procedure name. Declarations are part of the declaration before the as or is to begin, and declarations in stored procedures do not use the DECLARE keyword. As with Anonymous PL/SQL blocks, the exception and declarations sections are optional.
When we create a process with errors, we can view it through select * from User_errors, or by using show ERRORS [PROCEDURE Proc_name].
Use the following code to execute a stored procedure:

Begingetdeptcount; Call    getdeptcount ();

Attention:

• Not all stored procedures can be called in this way
• Stored procedure name cannot be added () when no parameter stored procedure is defined
• You can omit () when you call a stored procedure in a block or through exec
• Calling a non-parametric stored procedure via call must be added ()

Example 3: creates a stored procedure with input parameters, which prints the payroll amount by employee number

create or REPLACE PROCEDURE getsalarybyempno (eNo number)--parameter data type cannot specify length assalary Emp.sal%type; Beginselect SAL into salary from EMP WHERE empno=eno;dbms_output. Put_Line (eno| |  employees ' wages are ' | | Salary); Exceptionwhen no_data_found thendbms_output. Put_Line ( "No employees found this number");         

 

The data type of a parameter cannot specify a length when a stored procedure is defined that contains a parameter. Parameters also have input and output points, which are not specified in this example, are input parameters by default, and can be displayed specifying that a parameter is an input parameter, such as (ENo in number). Unlike example 1, exception handling is added to this example. Similar to Example 1, you can call a stored procedure in two ways:
BEGIN
Getsalarybyempno (7788);
END;
or
EXEC getsalarybyempno (7788);  or
Call Getsalarybyempno (7788);
However, if the parameter passed to a stored procedure is a variable, you must use the begin  end block, as follows:

declareno emp.empno% TYPE; Beginno:=7788;getsalarybyempno (no);
  end;   

 

If a package contains constants, it can also be called as follows:
EXEC getsalarybyempno (constantpackage.no);
However, you can no longer use call calls in this way.
Example 4: creates a stored procedure that contains input and output parameters, which is searched for by employee number, and the payroll is returned as an output parameter

create or REPLACE PROCEDURE getsalarybyempno (eNo IN Number,salary out number) asbeginselect SAL to salary from EMP WHERE Empno=eno; Exceptionwhen no_data_found thendbms_output. Put_Line ( "No employees found this number");         

 

When an output parameter is included in the procedure, the call must pass the begin end block and cannot be called by exec or call. Such as:

Declaresalary number (7,2); Begingetsalarybyempno (7788,salary);D bms_output. Put_Line (Salary); END;

Example 5: Creating a parameter type is both an input parameter and an output parameter procedure

In out number)AsbeginSELECT SAL to nosalary from EMP WHERE empno=nosalary; EXCEPTIONThendbms_output. Put_Line (' employees who have not found this number ');  END;    

is called as follows:

declareno Number (7,2); beginno:=7788;getsalarybyempno (no);D bms_output. Put_Line (no);        

 

Example 6: Create a procedure with default values

 CREATE or REPLACE procedure addemp (empNo number,ename varchar2,job VARCHAR2: =< span class= "str" > ' clerk ', Mgr Number,hiredate date default sysdate,sal number Span class= "KWRD" >default 1000,comm number default 0,deptno number default 30) Span class= "KWRD" >asbegininsert into emp VALUES (EmpNo, ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO); end;               

 

is called as follows:

exec addemp (7776, ' Zhangsan ',  ' coder ', 7788, ' June-January-2000 ', 2000,0,10); --not using the default value exec addemp (7777, ' Lisi ',  ' coder ', 7788,  ' June-January -2000 ', 2000,null,10);--can use a NULL value exec addemp (7778,  ' Wangwu ', mgr=>7788); --Using the default value exec addemp (mgr=>7788,empno=>7779,ename=> ' Sunliu ');-- Change the order of parameters              

 

Example 7: Using Nocopy to compile hints
When a parameter is a large data structure, such as a collection, a record, and an object instance, all of their contents are copied to the form to reduce the execution speed and consume a lot of memory. To prevent this from happening, we can use the NOCOPY hint to let the compiler pass a reference to the arguments in the out mode.

of VARCHAR2 (Ten);d list  deptlist:=deptlist (' Coresun ',' Coresun ',' Coresun ',' Coresun ');  Out NOCOPY deptlist) as...     

Note: Nocopy is just a hint, not an instruction. Even though we sometimes use nocopy, the compiler may still make a copy of the value. Usually, nocopy can be successful.

Second, the maintenance process

1. Delete stored Procedures

PROCEDURE Proc_name;

2. View process Status

SELECT object_name,status  WHERE object_type=' PROCEDURE '; 

3. Recompile process

PROCEDURE Proc_name COMPILE;

4. Review the procedure Code

WHERE type=' PROCEDURE ';

third, the understanding of parameters

--There are two ways to solve the output parameter can not be modified
--1 changing parameters to input parameters
--2 is the parameter changed to the input and output parameters;
Three ways to call a procedure
1 is to use the call
You must add parentheses, parameters, and parameter values when calling functions only in call mode.

This way in the command window, the calling procedure will not present the input data.
2 is the use of the EXEC command , the command call procedure, the command line must be entered in the
procedure name, the command window can be added without (), if there are parameters, it must be added, and the parameter value, the type of the parameter value with the
The variable type is the same.
3 calls the procedure in the statement block, this way and the command pattern is similar, they are all can not (),
--in 2 and 3 without parentheses, the procedure has no parameters, if any, it must have ()
Output parameter characteristics
11 procedures, if there is an output parameter (out parameter), in the invocation of the use of the procedure, but also to pass in a parameter, this parameter can not be assigned at the place of the call
, directly into a declared variable, to accept the value of the output parameter in the stored procedure (out parameter)
2 input parameter value can not be changed in the procedure,
Note: In the stored procedure, his parameter type can not set its size;  
for example;

create or REPLACE PROCEDURE Hello (P_name in VARCHAR2 (a), p_age out number (10,2)) isbegin        

If there is an output parameter, there must be a parameter to receive it;

Out Emp.sal%type)isBEGINWHERE empno = 7788;d bms_output.put_line (p_age);  END;   

Calling Methods in---------block

Declarev_nanme varchar2 (n); v_age number (12,2); Beginhello (v_nanme,v_age);d bms_output.put_line (v_age); END; 

In this process, the incoming v_age is the value that accepts the stored procedure output parameter; Similar to the return value in Java

--Understanding in-out parameters

Out Emp.ename%type)is BEGIN--SELECT emp.ename to p_name from emp;p _name:=' A;sk, ' | | p_name;  END;--------------------------------------------------------------------------declarev_nanme VARCHAR2 (12); BEGIN v_nanme:=' 12312 '; Hello1 (v_nanme);        

Add: Execute stored procedure with output parameter as cursor in Sqlplus

Example 6:

Sqlplus create a stored procedure using the following:

Sql> as fromscott.emp t;  end;/  

--using Sqlplus to execute stored procedures created above with cursor output parameters

sql> var cur refcursorexec test1 (: cur);P L/procedure successfully completed.  Print cur; DEPTNO ename--------------------CLARK KING MILLER, ADAMS FORD JONES SCOTT SMITH, ALLEN BLAKE JAMES DEPTNO ENA ME--------rows selected.    

Oracle stored procedure Detailed (Reference) + Supplemental (RPM)