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

There are three kinds of session cursor in oracle: explicit cursor (explicit cursor), implicit cursor (implicit cursor) and reference cursor (ref cursor).

1. Implicit cursor (implicit cursor)

Everywhere, the most common cursors in oracle, as long as a SQL or pl/sql,Oracle is executed, an implicit cursor is automatically created, and its open,bind,parse,execute,fetch,close is automatically controlled by the SQL engine or pl/sql engine, which also means that we have lost control of the implicit cursor.

However, you can learn the sql information related to the implicit cursor through the following properties

SQL%FOUND

SQL%NOTFOUND

SQL%ISOPEN

SQL%ROWCOUNT

1. SQL%FOUND indicates whether the number of records changed by a statement is greater than or equal to 1 after a statement is executed, so it is usually applicable to DML statements, or select into. This value is null before SQL execution, and becomes true after successfully changing the number of records, otherwise it is false

Declare

Empno_no number (4): = 7934

Begin

Delete from emp where empno=empno_no

If sql%found then

Insert into emp (empno,ename,mgr) values (8000jack Magne7902)

End if

Commit

End

/

This example is using SQL%FOUND, when a record is deleted, a record is inserted.

Pay special attention to the case of select into. Oracle will not report an error only if there is only one record in the returned result. If the result is 0, it will report an error no data found. If the result is greater than 1, it will report an error too many rows.

Declare

Emp1 varchar2 (14)

Vc_message varchar2 (4000)

Begin

Select empno into emp1 from emp where empno = 7900

Exception

When no_data_found then

Dbms_output.put_line ('no data customers')

Return

When too_many_rows then

Dbms_output.put_line ('too many rowsgiving')

Return

When others then

Vc_message: ='E' | |'_'| | sqlcode | |'_'| | sqlerrm

Dbms_output.put_line (vc_message)

Return;end

2 、 SQL%NOTFOUND

Contrary to SQL%FOUND, whether the number of records changed by it is 0, so it is usually applicable to DML statements, or select into. Before the execution of SQL, the value is null, and the number of records is not changed to true. If changed, it is false.

3 、 SQL%ISOPEN

In implicit cursors, this value is always false

4 、 SQL%ROWCOUNT

SQL%FOUND indicates the number of records affected by a statement after it is executed. Like SQL%FOUND SQL%NOTFOUND, this value is applicable to DML operations such as update,delete,insert and select into. Note that in select into, if the return value is more than 1, Oracle will report an error. This is the number of records returned by this value, rather than the number of records in select. The current SQL%ROWCOUNT only represents the last sql executed. If there is a new SQL execution, this value will be overwritten, so if you need to use the value generated by a SQL execution, you can put the value in a variable after execution.

Explicit Vernier (explicit cursor)

Used in pl/sql code (such as package, functions, stored procedures), the open,fetch,close in its life cycle can be displayed and controlled by us in the code, four common properties of explicit cursors CURSORNAME%FOUND,CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT

1 、 CURSORNAME%FOUND

Whether the specified explicit cursor (that is, the cursor name of cursorname) has a record fetch, when a cursor is open, there is no fetch, the value is null,fetch, the value is true,fetch and the value is true after all records, then fetch again, the Oracle will not report an error, but the value will become false. If the cursor attempts to use this value before it has been used by open, it will report to invalid_cursor

Declare

Cursor c1 is select ename,sal from emp where 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.