What is oracle stored procedure

In Oracle, a stored procedure is a set of SQL statements to complete a specific function. It is compiled and stored in the database. The user executes it by specifying the stored procedure name and giving parameters; the syntax format is "create or replace procedure stored procedure name".

The operating environment of this tutorial: Windows 7 system, Oracle version 11.2.0.1.0, DELL G3 computer.

Oracle stored procedures include three parts: process declaration, execution process part, and stored procedure exceptions (can be written or not. To enhance the fault tolerance of the script and the convenience of debugging, write exception handling)

The use of stored procedures is mainly to complete a complex function. If you use sql statements directly, you need to compile them every time. However, stored procedures only need to be compiled once and can be called directly later. Its syntax is

create or replace procedure procedure_name
as
begin
   extention;
end;
/

Line 1:

CREATE OR REPLACE PROCEDURE is a SQL statement that notifies the Oracle database to create a stored procedure called skeleton, and overwrite it if it exists;

Line 2:

The IS keyword indicates that a PL/SQL body will follow.

Line 3:

The BEGIN keyword indicates the beginning of the PL/SQL body.

Line 4:

NULL PL/SQL statement indicates that nothing should be done. This sentence cannot be deleted because there needs to be at least one sentence in the PL/SQL body;

Line 5:

The END keyword indicates the end of the PL/SQL body

Stored procedure creation syntax:

 create or replace procedure 存储过程名（param1 in type，param2 out type） 
as 
变量1 类型（值范围）; --vs_msg   VARCHAR2(4000); 
变量2 类型（值范围）;
Begin
Select count(*) into 变量1 from 表A where列名=param1；
 
    If (判断条件) then
       Select 列名 into 变量2 from 表A where列名=param1；
       Dbms_output。Put_line(‘打印信息’);
    Elsif (判断条件) then
       Dbms_output。Put_line(‘打印信息’);
    Else
       Raise 异常名（NO_DATA_FOUND）;
    End if;
Exception
    When others then
       Rollback;
End;

Notes:

1 , The stored procedure parameters do not have a value range, in means incoming, and out means output. The

type can use any legal type in Oracle.

2, The variable has a value range, followed by a semicolon

3. Before judging the statement, it is best to use the count(*) function to determine whether the operation record exists

4, Use select. . . into. . . Assign a value to the variable

5. Use raise to throw an exception in the code. The exception name is

. Recommended (free): oracle

The above is the detailed content of What is oracle stored procedure. For more information, please follow other related articles on the PHP Chinese website!