The cursor of pl/sql refers to storing the data queried from the database in the form of a temporary table in the memory. The cursor can operate on the data stored in the memory, returning a line or a group of records, or none return.
Although the records and table types in pl/sql can store data, it is more troublesome to operate on the data in the memory.The cursor is to solve the memory query to the data for processing changes.
The basic operation of the cursor: (pl/sql includes implicit cursor and display cursor) 1. Declare cursor
cursor cursor_name is select...
2. Open cursor
3. Fetch cursor
fetch cursor_name into variable1, variable2...
4. Close cursor
cursor cursor_name is select...
cursor: cursor keyword
cursor_name: cursor name
select: the query language used to create the cursor
The cursor is a temporary table, and the status of the cursor can be obtained through the properties of the cursor. 1. The %isopen attribute is mainly used to determine whether the cursor is open. When using the cursor, if you are not sure whether it has been opened, you can judge and use it (unopened cursors cannot be extracted)
2.%found attribute is mainly used to judge whether the cursor finds a record If the record is found, use the fetch statement to fetch the cursor data
3.%notfound If the data is fetched, it returns false, otherwise it returns true and %found is the opposite
4.%rowcount This attribute is used to return to the actual number of rows currently fetched
declare cursor c_emp is select * from emp; v_emp emp%rowtype; begin open c_emp; fetch c_emp into v_emp; while c_emp%found loop dbms_output.put_line(c_emp%rowcount||' '||v_emp.ename|| ' '||v_emp.sal); fetch c_emp into v_emp; end loop; if c_emp%isopen then close c_emp; end if; end;
After defining the cursor, pass the parameter
cursor cursor_name(paramter) is select...
-- Created on 2019-09-04 by LINNE declare cursor emp_cursor(dno number) is select ename from emp where deptno = dno; v_ename emp.ename%type; begin -- Test statements here open emp_cursor(20); loop fetch emp_cursor into v_ename; dbms_output.put_line(v_ename); exit when emp_cursor%notfound; end loop; close emp_cursor; dbms_output.put_line('---------------------------------'); open emp_cursor(10); loop fetch emp_cursor into v_ename; dbms_output.put_line(v_ename); exit when emp_cursor%notfound; end loop; close emp_cursor; end;
Cursor for loop
The cursor for loop is the simplest way to use the cursor in the pl/sql block, which simplifies the processing of the cursor.Oracle will implicitly open the cursor, extract the cursor, and close the cursor.
for record in cursor_name loop ..... end loop; declare cursor c_dept(dno number) is select * from dept where deptno = dno; begin for v_dept in c_dept(20) loop dbms_output.put_line('第'||c_dept%rowcount||'个员工'||v_dept.dname); end loop; end;
When executing a SQL statement, the Oracle server will automatically create an implicit cursor, the fixed name SQL of the implicit cursor, the implicit cursor does not need to be declared and opened, and does not need to be closed after use. The
implicit cursor can only process one row of data , So into can only assign values to a set of variables!
Various exceptions often occur during the operation of the pl/sql program.Once the exception occurs, the program will be terminated if it is not processed.The Oracle system divides the exceptions into predefined exceptions and custom exceptions.
EXCEPTION when exception1 then ..... when exception2 then ..... when others then .....
If the exceptions do not match, the code in orthers will be executed
Predefined exception timeout_on_resource: Waiting for the resource to time out
invalid_cursor: An invalid cursor is used
no_logged_on: Oracle is not connected
login_denied: Invalid username and password
no_data_found: No data found (into)
zero_divide: View is divided by zero
value_error: Conversion or truncation error