Oracle Stored Procedure (4) cursor

Concept of a game subject:

A cursor is a memory Workspace of SQL, which is defined by the system or user as a variable. The cursor is used to temporarily store data blocks extracted from the database. In some cases, you need to transfer the data from the table stored in the disk to the computer memory for processing, and finally display the processing results or write them back to the database. In this way, the data processing speed will increase; otherwise, frequent disk data exchanges will reduce the efficiency. There are two types of cursors: explicit cursors and implicit cursors. Select... into... A query statement can only extract one row of data from the database at a time. For this form of query and DML operations, the system uses an implicit cursor. However, to extract multiple rows of data, the programmer must define an explicit cursor and process it through statements related to the cursor. An explicit cursor corresponds to a select statement that returns multiple rows and multiple columns. Once the cursor is opened, the data is transmitted from the database to the cursor variable, and then the application splits the required data from the cursor variable for processing.
Implicit cursor:
As mentioned above, DML operations and single-line select statements use implicit cursors, which are:
* Insert operation: insert.
* Update operation: update.
* Delete operation: Delete.
* Single row query operation: Select... ....
When the system uses an implicit cursor, you can use the attributes of the implicit cursor to understand the operation status and results, and then control the process of the program. The implicit cursor can be accessed using the name SQL, but note that the SQL cursor name can only access the cursor attribute of the previous DML operation or single-row select operation. Therefore, the SQL cursor name is used to access attributes immediately after the operation is completed. There are four types of cursor attributes, as shown below.

Although the cursor data can be obtained in the previous form, it is more flexible to use its attributes for structure control after the cursor definition. The attributes of an explicit cursor are as follows.

SQL code

1. Type of the attribute return value of the cursor
   
2. % Rowcount integerFetchNumber of rows returned by the statement
3. % Found Boolean nearestFetchIf the statement returns a row of data, it is true. Otherwise, it is false.
4. The return value of % notfound is the opposite of that of % found.
5. % Isopen: the value of the Boolean cursor that has been opened is true. Otherwise, the value is false.

Begin update user_info set Pwd = 'liyanbin' where id = 'u002'; If SQL % found then -- SQL % found: Check whether the SQL statement is successfully executed dbms_output.put_line ('check whether the modification is successful. '); Commit; else dbms_output.put_line (' modification failed. Please check your cursor. '); End if; end;

 

Explicit cursor
Cursor definition and operations
The procedure of using a cursor is as follows.
1. Declare a cursor
The declear part declares the cursor in the following format:
Cursor cursor name [(parameter 1 Data Type [, parameter 2 data type...])]
Is SELECT statement;
Parameters are optional. The defined parameters can appear in the WHERE clause of the SELECT statement. If a parameter is defined, the actual parameter must be passed when the cursor is opened.
A select statement is a query statement for a table or view, or even a joint query. The where condition, order by, group by clause can be included, but the into clause cannot be used. Variables defined before the cursor definition can be used in select statements.
2. Open the cursor
In the executable part, open the cursor in the following format:
Open cursor name [(actual parameter 1 [, actual parameter 2...])];
When the cursor is opened, the query results of the SELECT statement are transmitted to the cursor workspace.
3. extract data
In the executable section, the data in the cursor workspace is retrieved to the variable in the following format. The extraction operation must be performed after the cursor is opened.
Fetch cursor name into variable name 1 [, variable name 2...];
Or
Fetch cursor name into record variable;
After a cursor is opened, a pointer points to the data zone. The fetch statement returns the data of a row pointed to by the pointer at a time. To return multiple rows, repeated execution is required. You can use a loop statement to implement this. The control loop can be performed by judging the attributes of the cursor.
The two formats are described below:
The variable name in the first format is the variable used to receive data from the cursor, which needs to be defined in advance. The number and type of variables must be the same as the number and type of field variables in the SELECT statement.
In the second format, when a row of data is retrieved into the record variable at a time, you need to use % rowtype to define the record variable in advance. This method is more convenient to use and does not need to define or use multiple variables separately.
The method for defining record variables is as follows:
Variable name table name | cursor name % rowtype;
The table must exist and the cursor name must be defined first.
4. Close the cursor
Close cursor name;
After an explicit cursor is opened, it must be closed explicitly. Once the cursor is closed, the resources occupied by the cursor are released. The cursor becomes invalid and must be re-opened before it can be used.
Explicit cursor helloworld ---- data variable (extract the field value with the ID of u002 in the table)

  declare    name varchar(15);    pwd varchar(15);    CURSOR cursor is         select name,pwd from user_info where id='u002';    begin       open cursor;       fetch cursor into name,pwd;       dbms_output.put_line(name||','||pwd);       close cursor;    end;

Explicit cursor helloworld ---- record variable

Declare cursor is select name, PWD from user_info where id = 'u002'; record cursor % rowtype; -- defines begin open cursor; fetch cursor into record; dbms_output.put_line (record. name | ',' | record. PWD); close cursor; end;

Explicit cursor helloworld ---- loop output (among Guo Jing and Qiao Feng, find the first three of them with the best effort)

    create table EMP    (      NAME   VARCHAR2(10),      SALARY VARCHAR2(10),      TITLE  VARCHAR2(10)    );

 

 declare  name varchar(15);  salary varchar(15);  CURSOR emp_cursor is     select name,salary from emp order by salary desc;  begin    open emp_cursor;    for i IN 1..3 loop        fetch emp_cursor into name,salary;       dbms_output.put_line(name||','||salary);    end loop;    close emp_cursor;  end;

Execution results: Feng Qingyang, 10000 Qiao Feng, 8000 Guo Jing, 6000

Explicit cursor helloworld ---- special for loop ---- omitting the cursor definition (use a special for loop to display the title and salary of all employees)

  declare  CURSOR emp_cursor is     select title,salary from emp order by salary desc;  begin     for emp_record in emp_cursor loop       dbms_output.put_line(emp_record.title||','||emp_record.salary);     end loop;  end;

This PL/SQL seems special, but it can be used directly without the declarative variable emp_record. The cursor is neither opened nor closed, and there is no data. How does it implement output, after reading the example below, we will summarize it.

 begin   for emp in(select title,salary from emp order by salary desc)loop     dbms_output.put_line(emp.title||','||emp.salary);   end loop; end;

Before the explicit cursor attribute, we use fetch to obtain the cursor data. However, in the displayed cursor, attributes can be used to flexibly control the structured organization. Below are common attributes.

1. Type of the attribute return value of the cursor
   
2. % Rowcount integerFetchNumber of rows returned by the statement
3. % Found Boolean nearestFetchIf the statement returns a row of data, it is true. Otherwise, it is false.
4. The return value of % notfound is the opposite of that of % found.
5. % Isopen: the value of the Boolean cursor that has been opened is true. Otherwise, the value is false.

Declare name varchar2 (10); salary number; Title varchar2 (10); cursor emp_cursor is select name, salary, title from EMP order by salary DESC; begin open emp_cursor; if emp_cursor % isopen then loop fetch emp_cursor into name, salary, title; exit when emp_cursor % notfound; dbms_output.put_line (to_char (emp_cursor % rowcount) | Name | '-' | salary | '-' | title); End loop; else dbms_output.put_line ('the cursor is not opened! '); End if; close emp_cursor; end;

Parameter cursor: helloworld

 

 declare   name varchar2(10);  salary number;  CURSOR emp_cursor(t_age number,t_educational varchar2) is     select name,salary from emp       where age = t_age and educational = t_educational;  begin    open emp_cursor(101,'postdoctor');    loop      fetch emp_cursor into name,salary;      exit when emp_cursor%notfound;      dbms_output.put_line(name||'----'||salary);    end loop;  end;

Output: elegant ---- 10000

Use of dynamic select statements and dynamic cursuse Oracle supports dynamic select statements and dynamic cursors. The dynamic method greatly extends the programming capability. For select statements whose query results are one row, you can use the dynamic generation of query statement strings to generate and execute them temporarily during the program execution phase,

Syntax: execute immediate query statement string into variable 1 [, variable 2...];

 declare   queryStr varchar2(100);  t_name varchar2(10);  begin    queryStr := 'select name from emp where salary>8000';    execute immediate queryStr into t_name;    dbms_output.put_line(t_name);  end;

The cursor defined in the variable declaration part is static and cannot be modified during the program running. Although different data can be obtained through parameter transmission, there are still many limitations. By using dynamic cursors, you can generate a query statement as the cursor at any time during the program running stage. To use a dynamic cursor, you must first define a cursor type and then declare a cursor variable. The query statement corresponding to the cursor can be dynamically described during program execution.

Statement defining the cursor type: Type cursor type name ref cursor;

The statement for declaring a cursor variable is as follows;

In the executable part, you can open a dynamic cursor: Open cursor variable name for query statement string;

Declare type cur_type is ref cursor; cur cur_type; REC Scott. EMP % rowtype; STR varchar2 (50); letter CHAR: = 'a'; begin loop STR: = 'select name from EMP where name like ''' % '| letter |' % '''; open cur for STR; dbms_output.put_line ('containing letters '| letter |' name: '); loop fetch cur into rec. name; exit when cur % notfound; dbms_output.put_line (Rec. name); End loop; exit when letter = 'Z'; letter: = CHR (ASCII (letter) + 1); End loop; end;

Summary: