OracleCursor details and examples

Oracle Cursor details and examples

Abstract: describes in detail the definition and use of cursors in oracle databases. You can use instance operations to gain an in-depth understanding of the usage and usage of cursor.

I. Related Concepts 1. concep

When Oracle Database executes aSQL statement, it stores the result set and processing information in anunnamed private SQL area. A pointer to this unnamed area, called a cursor, let you retrieve the rows of the result set one at a time. cursor attributesreturn information about the state of the cursor.

2. concept:

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. Cursor type: static Cursor-divided into explicit Cursor and implicit (implicit) Cursor, REF Cursor-dynamic Cursor, is a reference type, similar to a pointer.

II. Specific Types and use 1. implicit cursor

1) explain: Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. you can get information about this cursor from itsattributes, but you cannot control it. after the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.

2) implicit cursor (implicit cursor) the system automatically opens and closes a SQL dml instance. When we execute an SQL DML instance, the system automatically opens a cursor instance. After the execution is complete, the system closes the cursor instance. We cannot directly control the cursor instance, but we can you can use the attributes of implicit Cursor to understand the operation status and results and control the process. The attributes of Cursor include:

I. SQL % ROWCOUNT integer -- indicates the number of rows successfully executed by the DML statement

Ii. SQL % FOUND Boolean -- when the value is true, the table is inserted, deleted, updated, or queried successfully.

Iii. SQL % NOTFOUND Boolean -- opposite to above

V, SQL % ISOPEN Boolean -- the DML execution process is true, otherwise it is false

3) Example:

 

begin  update student set sname='chy' WHERE sno='1';  if sql%isopen then     dbms_output.put_line('cursor is opening !');  else     dbms_output.put_line('cursor is closed !');  end if;  if sql%found then     dbms_output.put_line('DML is successed !');  else     dbms_output.put_line('DML is failed !');  end if;  if sql%notfound then     dbms_output.put_line('DML is failed !');  else     dbms_output.put_line('DML is successed !');  end if;      dbms_output.put_line(sql%rowcount||' is the number of result !');  exception       when no_data_found then           dbms_output.put_line('Sorry No data');      when too_many_rows then           dbms_output.put_line('Too Many rows');end;

2. explicit cursor

1) explanation: PL/SQLalso lets you declare explicit cursors. an explicit cursor has a name and isassociated with a query (SQLSELECT statement)-usually one that returns multiplerows. after declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement ), and close the cursor (with the CLOSE statement ). after closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.

The usage and usage of the display cursor are straightforward.

2)The attributes of the explicit cursor include:

The type of the attribute return value of the cursor.

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

The latest FETCH statement of the % FOUND Boolean Type Returns a row of data, which is true. Otherwise, the return value is false.

The return value of % NOTFOUND is the opposite of that of % FOUND.

% ISOPEN: the value of the Boolean cursor that has been opened is true. Otherwise, the value is false.

3) the use of explicit Cursor involves four steps:

A defines the Cursor -- Cursor [Cursor Name [param_name, param_type] IS select xxx from xxxwhere xxx;

B Open the Cursor -- Open [Cursor Name [varialbe_value];

C operation Cursor -- Fetch [Cursor Name];

D Close the Cursor -- Close [Cursor Name];

4) use the display cursor to traverse the cyclic cursor:

A) Use a display cursor

I. Declared cursor: divides the storage area. Note that the Select statement is not executed at this time. CURSOR name (parameter list) [Return Value Type] IS Select statement;

Ii. Open the cursor: Execute the Select statement to obtain the result set and store it in the cursor. The cursor points to the result set header instead of the first record. Open cursor name (parameter list );

Iii. Retrieve records: move the cursor to get a record. The fetch cursor name is into a temporary record or attribute type variable;

V. Close the cursor: Put the cursor into the buffer pool and the resource is not completely released. You can open it again.

Close cursor name;

B) traverse the cyclic cursor

I. for Loop cursor

Loop cursors open the cursors implicitly, automatically scroll to get a record, and automatically create a temporary record type variable storage record. The cursor is automatically closed after processing.

......

For variable name In cursor name

Loop

Data processing statement;

End loop;

Ii. loop cursor

......

Loop

Fetch cursor name into temporary record or attribute type variable;

Exit when cursor name % notfound;

End loop;

Iii. while Loop

......

Open cursor name

Fetch cursor name into temporary record or attribute type variable;

While cursor name % foundloop

-- Do something

 

Fetch cursor name into temporary record or attribute type variable;

End loop;

......

Close cursor name

5) Common explicit Cursor usage:

I. Use a for loop to use cursor:

declare   cursor cur is select * from t_user where age = 22;  userinfo t_user%rowtype;begin  for userinfo in cur loop    exit when cur%notfound;    dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);  end loop;  exception     when others then      dbms_output.put_line(sqlerrm);end;      

Ii. Use fetch to use cursor: exp2

declare   cursor cur is select * from t_user where age = 22;  userinfo t_user%rowtype;begin  open cur;  loop     exit when cur%notfound;     fetch cur into userinfo;     dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);  end loop;  exception     when others then          dbms_output.put_line(sqlerrm); close cur;end; 

Iii. Use fetch and while Use cursor: exp3

declare  cursor cur is select * from t_user where age = 23;  userinfo t_user%rowtype;begin  open cur;  fetch cur into userinfo;  if cur%isopen then    while cur%found loop          dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);          fetch cur into userinfo;    end loop;    dbms_output.put_line('totle result : ' || cur%rowcount);  else    dbms_output.put_line('cursor is closed!');  end if;    close cur;  exception     when others then          dbms_output.put_line(sqlerrm); close cur;end;   

V. Use cursor to modify data (cursor with parameters). The following three functions are the same, but the internal implementation is slightly different.

-- Raise a salary for an employee who works as CLERK -- onedeclare cursor cur (c_job varchar2) is select * from emp1 where emp1.job = c_job for update of sal; ef emp1 % rowtype; c_sal emp1.sal % type; begin for ef in cur ('cler') loop exit when cur % NOTFOUND; if ef. SAL <1000 THEN C_SAL: = EF. SAL * 1.2; elsif ef. SAL <2000 THEN C_SAL: = EF. SAL * 1.5; elsif ef. SAL <3000 THEN C_SAL: = EF. SAL * 2; ELSE C_SAL: = EF. SAL * 2.2; end if; UPDATE EMP1 SET EMP1. SAL = C_SAL where current of cur; end loop; exception when others then dbms_output.put_line (sqlerrm); END; -- twodeclare cursor cur (c_job varchar2) is select * from emp1 where emp1.job = c_job for update of sal; EF emp1 % rowtype; C_SAL emp1.sal % type; begin open cur ('cler'); fetch cur into EF; while cur % found loop exit when cur % NOTFOUND; if ef. SAL <1000 THEN C_SAL: = EF. SAL * 1.2; elsif ef. SAL & lt; 2000 THEN C_SAL: = EF. SAL * 1.5; elsif ef. SAL <3000 THEN C_SAL: = EF. SAL * 2; ELSE C_SAL: = EF. SAL * 2.2; end if; update emp1 set emp1.sal = C_SAL where current of cur; fetch cur into EF; end loop; close cur; end; -- threedeclare -- define the cursor Note: the select SQL is not excuted! Cursor cur (c_job varchar2) is select * from emp1 where emp1.job = c_job for update of sal; ef emp1 % rowtype; c_sal emp1.sal % type; begin open cur ('cler '); fetch cur into ef; while cur % found loop exit when cur % notfound; case when ef. sal <1000 then c_sal: = ef. sal * 1.2; when ef. sal <2000 then c_sal: = ef. sal * 1.4; when ef. sal <3000 then c_sal: = ef. sal * 1.6; end case; update emp1 set emp1.sal = c_sal where current of cur; fetch cur into ef; end loop; close cur; end;

Vi. Use cursor to delete data:

--use cursor to delect datecreate table emp3 as select * from emp;--delete the date of emp3 where the job is 'CLERK';declare  cursor cur(c_job varchar2) is select * from emp3 where emp3.job=c_job for update;  ef emp3%rowtype;begin  for ef in cur('CLERK') loop    exit when cur%notfound;    delete from emp3 where current of cur;  end loop;end;

Supplement: ref cursor will appear in the next note. Here, we will add the criterion if used in the loop. Pay attention to the following two sections of code:

IF EF.SAL < 1000 THEN      C_SAL := EF.SAL*1.2;    ELSIF EF.SAL < 2000 THEN      C_SAL := EF.SAL*1.5;    ELSIF EF.SAL < 3000 THEN      C_SAL := EF.SAL*2;    ELSE      C_SAL := EF.SAL*2.2;    END IF;

IF EF.SAL < 1000 THEN      C_SAL := EF.SAL*1.2;    ELSE IF EF.SAL < 2000 THEN      C_SAL := EF.SAL*1.5;    ELSE IF EF.SAL < 3000 THEN      C_SAL := EF.SAL*2;    ELSE      C_SAL := EF.SAL*2.2;    END IF;

When multiple if conditions are used, an error occurs. The preceding elsif must be used to combine the if conditions!