Oracle basic (fourteen) cursor

cursor

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
open cursor_name
3. Fetch cursor
fetch cursor_name into variable1, variable2...
4. Close cursor
close cursor_name

cursor cursor_name is select...
cursor: cursor keyword
cursor_name: cursor name
select: the query language used to create the cursor

Cursor properties

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;

Parameterized cursor

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;

Implicit cursor

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!

abnormal

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