Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the simple analysis of Procedure in Oracle storage procedures, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Here we discuss some advanced options for stored procedures: cursor cursors

A cursor is a memory workspace of SQL, defined by the system or user in the form of variables. The purpose of cursors is to temporarily store blocks extracted from the database. In some cases, the data needs to be transferred from the table stored on disk to the computer memory for processing, and finally the processing results are displayed or finally written back to the database. Only in this way can the speed of data processing be improved, otherwise the frequent io exchange of disk data will reduce the efficiency and affect the speed. This is also one of the reasons for introducing the concept of cursors.

Cursor cursors can be divided into three types: implicit cursor, explicit cursor, and dynamic (ref) cursor

1. Introduce the implicit cursor first.

For select... Into... Statement, only one piece of data can be obtained from the database at a time, and for this type of DML Sql statement, it is the implicit Cursor. For example: Select / Update / Insert/ Delete operation.

Function: the state and result of the operation can be understood through the properties of the implicit cursor, thus achieving the control of the process. The properties of Cursor include:

The SQL%ROWCOUNT integer represents the number of rows of data successfully executed by the DML statement

A SQL%FOUND Boolean value of TRUE indicates that the insert, delete, update or single-line query operation was successful

The SQL%NOTFOUND Boolean is the opposite of the return value of the SQL%FOUND property

SQL%ISOPEN Boolean DML is true during execution and false after execution

Implicit Cursor means that the system automatically opens and closes Cursor.

Here's an example:

Create or replace procedure Obj_up as

Begin

Update obj set object_name = 'MyMarry' where object_id =' 3'

If SQL%Found then

Dbms_output.put_line ('Object_name is updated fully fulfilled')

Commit

Else

Dbms_output.put_line ('Object_name is updated failed')

End if

End

Execute this stored procedure

Begin

-- Call the procedure

Obj_up

End

2. Then introduce explicit cursor

For extracting multiple rows of data from a database, you need to use explicit Cursor. The properties of an explicit Cursor include:

The type meaning of the property return value of the cursor

% ROWCOUNT integer gets the number of rows of data returned by the FETCH statement

% FOUND Boolean recent FETCH statement returns a row of data is true, otherwise it is false

% NOTFOUND Boolean is the opposite of the return value of% FOUND property

True when ISOPEN Boolean cursor has been opened, false otherwise

The use of explicit cursors is divided into four steps:

Define cursors-Cursor [Cursor Name] IS

Open the cursor-Open [Cursor Name]

Operation data-Fetch [Cursor name]

Close the cursor-Close [Cursor Name] this step should not be omitted.

Here's an example:

Create or replace procedure proc_salary is

-- define variables

V_empno emp.empno%TYPE

V_ename emp.ename%TYPE

V_sal emp.sal%TYPE

-- define cursors

CURSOR emp_cursor IS

SELECT empno, ename, sal from emp

BEGIN

-- the cycle begins

LOOP

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor

END IF

FETCH emp_cursor

INTO v_empno, v_ename, v_sal

-conditions for exiting the loop

EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL

Dbms_output.put_line ('empno' | | v_empno | | 'name is' | | v_ename | | 'salary is' | |

V_sal)

END LOOP

END

/

Execution

Begin

Proc_salary

End

3. Finally, introduce dynamic cursor.

Static cursors can determine the corresponding query statement before execution, and only pass some query parameters at most, so it is easier to deal with. Dynamic cursors are queried before execution that SQL is dynamically spliced and are not sure which tables and conditions are specifically queried.

Unlike implicit Cursor, explicit Cursor, Ref Cursor can get the data result set by passing parameters at run time. The other two Cursor, which are static, determine the data result set during compilation.

The use of Ref cursor

Type [Cursor type name] is ref cursor

Define dynamic Sql statement

Open cursor

Operation data-Fetch [Cursor name]

Close Cursor

Here's an example.

A statement executed by a dynamic SQL as a cursor that specifies only the type when defined and SQL. Loop processing is done using loop, so you need to end it manually.

Create or replace PROCEDURE PX_VARIFY_ZRP_EXT_MATCH (V_TABLE_NAME IN VARCHAR2

V_IMPORT_ID IN VARCHAR2) AS

V_SQL VARCHAR2 (1024)

VU_SQL VARCHAR2 (1024)

TYPE CV_PERSONS IS REF CURSOR

CV_PERSON CV_PERSONS

V_ID NAT_PERSON_INFO.ID%TYPE;-ID

V_ID_TYPE NAT_PERSON_INFO.ID_TYPE%TYPE;-document type

V_ID_CODE NAT_PERSON_INFO.ID_CODE%TYPE;-document number

V_PERSON_ID VARCHAR2 (36);-- returns the body ID

V_CNT NUMBER (5,0);-- number of eligible records

BEGIN

V_SQL: = 'SELECT ID,ID_TYPE,ID_CODE'

V_SQL: = V_SQL | | 'FROM' | | V_TABLE_NAME

V_SQL: = V_SQL | | 'WHERE IMPORT_ID =' | | V_IMPORT_ID

OPEN CV_PERSON FOR V_SQL

LOOP

FETCH CV_PERSON

INTO V_ID, V_ID_TYPE, V_ID_CODE

EXIT WHEN CV_PERSON%NOTFOUND

PX_VARIFY_ZRP_IDS_ATOM (V_ID_TYPE, V_ID_CODE, V_PERSON_ID, V_CNT)

IF V_CNT = 1 AND V_PERSON_ID IS NOT NULL THEN

VU_SQL: = 'UPDATE' | | V_TABLE_NAME | | 'SET PERSON_ID =''| |

V_PERSON_ID | |''WHERE ID =''| | V_ID | |'

END IF

IF V_CNT = 0 AND V_PERSON_ID IS NULL THEN

VU_SQL: = 'UPDATE' | | V_TABLE_NAME | |

'SET IMPORT_CHECK_FLAG =' 0cm','

VU_SQL: = VU_SQL | | 'CHECK_ERR_MSG = CHECK_ERR_MSG | |' | |

Verification rule code: 1001; error description: no corresponding natural person information was found.''

VU_SQL: = VU_SQL | | 'WHERE ID =' | | V_ID | |'

END IF

DBMS_OUTPUT.PUT_LINE (VU_SQL)

Execute immediate VU_SQL

END LOOP

Execution

Begin

PX_VARIFY_ZRP_EXT_MATCH

End

After reading the above, do you have any further understanding of the simple analysis of Procedure in Oracle stored procedures? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.