Shulou(Shulou.com)06/01 Report--

REF cursor:

REF cursors, also known as dynamic cursors, associate different statements with them at run time, and dynamically associate the temporary objects of the result set, that is, dynamically decide to execute the query at run time. REF cursors can use cursor variables.

Cursor variable:

A cursor variable is a variable that refers to a REF cursor type and only wants the result set to be dynamically associated.

Type of cursor variable:

1. Cursor variables with constraints, and cursor variables with return types are also called strong cursors.

two。 Unconstrained cursor variables and cursor variables with no return type are also called weak cursors.

The purpose of the REF cursor:

Realize the function of transferring result sets between programs, and bulk sql can also be realized by using REF cursor to improve the performance of sql.

The difference between static cursors and REF cursors:

1. Static cursors are statically defined, while REF cursors are dynamically associated

two。 REF cursor variables are required to use REF cursors

3.REF cursors can be passed as parameters, while static cursors cannot be passed as parameters.

Syntax for REF cursors:

1. Strongly typed REF cursors: specifies that return datatype,REF cursor variables must be of the same type as return datatype.

Syntax: TYPE TYPE_NAME IS REF CURSOR RETURN DATATYPE

two。 Weakly typed REF cursors: do not specify that return datatype can match any type of cursor variable to get any result set.

Syntax: TYPE TYPE_NAME IS REF CURSOR

SYS_REFCURSOR:

It is mainly used to return the result set in the process. If you only want to return the value, you do not need to define the cursor type in the packet header, you can easily return the result by directly using sys_refcursor.

Tip:

Using a static cursor-- through a static SQL (but not a ref cursor)-- is more efficient than using the ref cursor, while the use of the ref cursor is limited to the following situations:

Return the result set to the client

Share the cursor between multiple subroutines (actually very similar to the one mentioned above)

When there is no other effective way to achieve your goal, use the ref cursor, just as you must use dynamic SQL

In short, consider using static SQL first, using ref cursors only when it is absolutely necessary to use ref cursors, and some people suggest using implicit cursors as much as possible to avoid writing additional cursor control code (declare, open, get, close), and do not need to declare variables to hold the data obtained from cursors.

PACKAGE

The package and package body involved in the example are the following instructions for the use of package:

Package structure:

A package consists of two separate parts: the package specification and the package body.

1. Package definition (package): the package definition part is the interface for the application, declaring elements such as data types, variables, constants, cursors, subroutines, and exception error handling in the package, which are public elements of the package.

Syntax:

CREATE [OR REPLACE] PACKAGE PACKAGE_NAME

{IS | AS}

[public data type definition]

[public cursor declaration]

[public variables, constant declarations]

[declaration of public subroutine]

END [PACKAGE_NAME]

two。 Package body (package body): the package body is the concrete implementation of the package definition part, which defines the cursors and subroutines declared by the package definition part, and can also declare the private elements of the package in the package body. If the cursor or subroutine in the package body is not defined in the package header, then the cursor or subroutine is private.

Syntax:

CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME

{IS | AS}

[private data type definition]

[private variables, constants]

[private subroutine declaration and definition]

[definition of public subroutine]

BEGIN

Execution part (initialization section)

END [PACKAGE_NAME]

Like classes, the program elements in a package are divided into public elements and private elements. The difference between these two elements is that they allow access to different program scope, that is, their scope is different. Public elements can not only be called by functions and procedures in the package, but also can be accessed by PL/SQL programs outside the package, while private elements can only be accessed by functions and procedures in the package. The package definition and the package body are compiled separately and stored in the database dictionary as two separate objects. The package definition must be compiled in front of the package body, which can be absent, but there must be a package definition, and the name of the package must be consistent with the name of the package body.

Example 1: strongly typed REF cursors

DECLARETYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;- cursors can only open DJ_DJB table data REF_C REF_CURSOR;---- cursor variable V_DJB DJ_DJB%ROWTYPE;SELECTION VARCHAR2 (1): = ('& Please enter:'); BEGIN IF SELECTION='1' THEN OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM

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.