Use of Oracle stored procedures and REFCURSOR

It mainly records the knowledge about oracle storedprocedure, from definition to use. Start with the simplest example and go deeper. The refcursor is used later.

It mainly records the knowledge about oracle stored procedure, from definition to use. Start with the simplest example and go deeper. At the same time, the use of ref cursor is combined later.

Abstract: It mainly records the knowledge about Oracle stored procedure, from definition to use. Start with the simplest example and go deeper. At the same time, the use of ref cursor is combined later. By the way, I added how to use it in java.

I. Introduction 1. Stored Procedure Definition:

Stored Procedure is a set of SQL statements for specific functions. It is 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. Stored procedures are an important object in databases. Any well-designed database application should use stored procedures.

A stored procedure is a process of flow control and SQL statement writing. The procedure is compiled and optimized and stored on the database server. You only need to call it when using the application. In ORACLE, several associated processes can be combined to form a package.

2. Advantages:

A) The stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

B) When performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction management provided by the database.

C) stored procedures can be reused to reduce the workload of database developers.

D) High security. You can set that only a user has the right to use the specified stored procedure.

Simply put, you have written a stored procedure on your machine. This stored procedure is stored in a distant database server like the data in those tables, but it is executable code, other users that can connect to the database server can call the stored procedure you write.
Its function is to hide details. That is to say, the stored procedure code you write may be very complicated, but others call it very easily. You don't need to know how it works, multiple commands can be completed at a time.

3. Category:

Oracle has two types of stored procedures: system stored procedures and custom stored procedures.

The system stored procedure is a set of stored procedures that are provided in advance by oracle to complete specific functions. After installing oracle, you will have it.

A custom stored procedure is a procedure process that consists of a set of plsql statements in the oracle database ). It can be called by other oracle custom stored procedures, udfs, and jobs, or by client programs.

Ii. Basic usage and Examples

1. Basic Structure:

CREATE OR REPLACE PROCEDURE
(Parameter 1 in number, parameter 2 in number)
AS
Variable 1 INTEGER: = 0;
Variable 2 DATE;
BEGIN
END stored procedure name

2. procedure without parameters:

-- No procedure Parameter
Create or replace procedure pro_no_param
Is
Begin
Dbms_output.put_line ('the procedure without params ');
End pro_no_param;

-- Call
-- One: the name of the procedure without a parameter must be followed ();
Call pro_no_param ();

-- Two: The procedure name can be followed by NO ();
Begin
Pro_no_param ();
End;

3. procedure with the parameter type IN:

-- The procedure parameter has only the IN type.
Create or replace procedure pro_in_param (
V_1 in number,
V_2 in varchar2,
V_3 in date
)
Is
Begin
Dbms_output.put_line ('v1: '| v_1 | 'v2:' | v_2 | 'v2: '| (to_char (v_3, 'yyyy-mm-dd ')));
End pro_in_param;

Begin
Pro_in_param (1, 'chy', sysdate );
End;

4. procedure with the parameter type of OUT:

-- The procedure parameter has only the OUT type.
Create or replace procedure pro_out_param (
V1 out number,
V2 out char
)
Is
Begin
V1: = 2;
V2: = 'andychen ';
End pro_out_param;

-- Remember to declare the variable used to store the out value of procedure
-- When the statement ends, remember the end --;
Declare
V_1 number;
V_2 varchar2 (200 );
Begin
Pro_out_param (v_1, v_2 );
Dbms_output.put_line ('v1: '| v_1 | 'v2:' | v_2 );
End;

5. procedure with both IN and OUT parameters:

-- Procedure of the inout Parameter
-- Use the same variable to receive the passed value and then assign the variable as the output value to the variable declared during execution
Create or replace procedure pro_in_out_param (
In_out_param in out varchar2
)
Is
Begin
In_out_param: = 'in _ out_param and '| in_out_param;
End pro_in_out_param;

Declare
In_out_param varchar2 (222): = 'detail param ';
Begin
Pro_in_out_param (in_out_param );
Dbms_output.put_line (in_out_param );
End;