Oracle Stored Procedure

Stored Procedures (StoredProcedure) are a set of SQL statements for specific functions in large database systems. They are compiled and stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. If a specific operation is often required in an application

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and Stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. If a specific operation is often required in an application

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and Stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. If you often need to perform specific operations in an application, you can establish a specific process based on these operations. By using the process, you can not only simplify the development and maintenance of client applications, but also improve the running performance of applications.

The syntax for creating a stored procedure is as follows:

Create or replace procedure procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2,...) IS [AS] PL/SQL block;

Procedure_name: name of the stored procedure; argument1 and argument2 are used to specify the parameters of the procedure; mode1 and mode2 are used to specify the parameter modes, such as IN, OUT, and in out, IS or as is used to start a PL/SQL block. the length of a parameter cannot be specified when the parameter type is specified. During the creation process, you can specify input parameters (IN), output parameters (OUT), and input and output parameters (in out ). If the parameter mode is not specified, the default value is IN.

The following example shows how to establish a process and use various parameter modes:

1. the creation process without any parameters.

create or replace procedure out_timeisbegin dbms_output.put_line(systimestamp);end;

You can call the procedure out_time. Generally, execute (exec) is used to call the procedure:

SQL> set serveroutput onSQL> exec out_time 27-6-13 04.47.342.1686000000 PM + 08:00 PL/SQL procedure successfully completed SQL>/

2. creation process with the IN parameter.

The following describes how to create an employee data insertion process (add_employee) with an input parameter:

Create or replace procedure add_employee (eno NUMBER, name VARCHAR2, sal NUMBER, job VARCHAR2 default 'cler', dno NUMBER) IS e_integrity EXCEPTION; PRAGMA prediction_init (e_integrity,-2291 ); begin insert into emp (empno, ename, sal, job, deptno) VALUES (eno, name, sal, job, dno); exception when DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR (-2000, 'employee ID cannot be repeated '); WHEN e_integrity then RAISE_APPLICATION_ERROR (-2000, 'department ID does not exist'); END;

The parameter mode is not specified IN the above program, so all parameters are input (IN) by default. When calling this process, in addition to having the default value parameter, other parameters must provide the parameter value. Call example:

SQL> exec add_employee(1111,'MARY',2000,'MANAGER',10); PL/SQL procedure successfully completed

2. creation process with the OUT parameter.

The process can not only perform specific operations, but also output data. The OUT or in out parameters are used to output data. When defining output parameters, the OUT keyword must be provided. The following process is used to create output employee names and their salaries:

Create or replace procedure query_employee (eno NUMBER, name OUT VARCHAR2, salary OUT VARCHAR2) is begin select ename, sal INTO name, salary from emp WHERE empno = eno; exception when NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-2000, 'this employee does not exist'); END;

The following is an example of calling this process in SQL * Plus:

SQL> var name varchar2(10);SQL> var salary numberSQL> exec query_employee(7788,:name,:salary); PL/SQL procedure successfully completedname---------SCOTTsalary---------1000

Specifically: name,: salary, why is there a colon Before: I understand it, because they are not PL/SQL variables, and PL/SQL variables are the variables defined in PL/SQL blocks, haha .....

3. creation process with in out parameters

When this parameter is used, you need to pass data to this parameter through the variable before calling the process. After the call is complete, Oracle will pass the process result to the application through this variable.

The following uses compute as an example to describe how to use the in out parameter. Example:

CREATE OR REPLACE PROCEDURE  compute (num1 IN OUT NUMBER, num2 IN OUT NUMBER)IS  v1 NUMBER; v2 NUMBER;BEGIN  v1:=num1/num2;  v2:=MOD(num1,num2);  num1:=v1;  num2:=v2;END;

': =' Is the value assignment operator in PL/SQL programs. Next, you can call this process in SQL * Plus:

SQL> var n1 number;SQL> var n2 number;SQL> exec :n1:=100; PL/SQL procedure successfully completedn1---------100 SQL> exec :n2:=30; PL/SQL procedure successfully completedn2---------30 SQL> exec compute(:n1,:n2); PL/SQL procedure successfully completedn1---------3.33333333333333n2---------10

For more information about the colon (:) In the statement, see the previous explanation. Because I was also very troubled at the time, haha, if you know, let me speak.

4. Next we will introduce how to pass variables and data for parameters when calling a stored procedure with parameters. There are three main methods: Location transfer, name transfer, and combination transfer.

Before introducing the transfer method, create a process for adding data to the DEPT table.

Create or replace procedure add_dept (dno NUMBER, dname VARCHAR2 default null, loc VARCHAR2 default null) is begin insert into dept VALUES (dno, dname, loc ); exception when DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR (-2000, 'department No. can be repeated '); END;

4.1 location Transfer

Location transfer means that when a subroutine is called, the corresponding variable or value is specified for the parameter in sequence according to the parameter definition sequence. The example is as follows:

 exec add_dept(50,'SALES','NEWYORK') exec add_dept(60) exec add_dept(70,'ADMIN');  

When no data parameters are provided for the second and second times, they all have the default value NULL.

4.2 name transfer

Name transfer means to specify the parameter name when calling the subroutine and use the correlated symbol "=>" to provide corresponding values or variables for it. When using this method, you do not need to consider the positional relationship. Example:

 exec add_dept(loc=>'NEW YORK'，dno=>50); exec add_dept(loc=>'NEW YORK',dname=>'SALES',dno=>60)

4.3 Combined Transfer

Combined Transfer refers to the use of location transfer and name transfer when calling a subroutine. The example is as follows:

 exec add_dept(50,loc=>'NEW YORK'); exec add_dept(60,loc=>'NEW YORK',dname=>'SALES') 

5. query the source code of the stored procedure

After the process is established, Oracle stores the process name, source code, and Execution Code in the data dictionary. When calling a process, the application executes the Code directly according to the Execution Code without re-parsing the Process Code. Therefore, the performance of using subprograms is better than that of directly executing SQL statements. You can query the data dictionary USER_SOURCE to display all the subprograms of the current user and their source code. Example:

SQL> SELECT text FROM user_source where name = 'add _ DEPT '; TEXT --------------------------------------- ---------------------------------------- -PROCEDURE add_dept (dno NUMBER, dname VARCHAR2 default null, loc VARCHAR2 default null) is begin insert into dept VALUES (dno, dname, loc); exception when DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR (-2000, 'department No. Repeated '); END; 13 rows selected

Of course, you can also view the source code using the PL/SQL tool.

6. Delete the Stored Procedure

You can use the PL/SQL tool to delete data in a graphical manner, or DROP PROCEDURE add_dept to delete data.

I have introduced so much about the stored procedure. I will introduce the debugging of the stored procedure later.

Appendix:

1. Check the stored procedures of database users.

Select * from dba_source where OWNER = 'username' and type = 'Procedure'