Oracle Cursors syntax Summary

Oracle Cursors syntax summary about Oracle Cursors syntax summary, Oracle Cursors is used to query the database, get a record set (result set) pointer, allows developers to access one result set at a time and perform operations on each result set. 1. Oracle's Cursors concept: cursor: Used to query databases and obtain a pointer to a record set (result set). This allows developers to access a row of result sets at a time and perform operations on each result set. Ii. Oracle Cursors classification: 1. Static cursor: divided into explicit cursor and implicit cursor. 2. REF cursor: A reference type, similar to a pointer. III. oracle Cursors details: 1. explicit CURSOR: CURSOR name (parameter) [Return Value Type] IS Select statement lifecycle:. OPEN cursor (OPEN) parsing, binding... Data is not retrieved from the database. B. The records (FETCH INTO) are obtained from the cursor for query and the result set is returned. Generally, the local variable is defined as the buffer for getting data from the cursor. C. CLOSE the cursor to complete the cursor processing. You cannot obtain rows from the cursor. You can also open it again. Option: set serveroutput on declare cursor emp_cur (p_deptid in number) is select * from employees where department_id = p_deptid; l_emp employees % rowtype; begin dbms_output.put_line ('getting employees from department 30'); open emp_cur (30); loop fetch emp_cur into l_emp; exit when emp_cur % notfound; dbms_output.put_line ('employee id' | l_emp.employee_id | 'is'); dbms_output.put_line (l_emp.f Irst_name | ''| l_emp.last_name); end loop; close emp_cur; values ('getting employees from department 90'); open emp_cur (90); loop fetch emp_cur into l_emp; exit when emp_cur % notfound; values ('employee id' | l_emp.employee_id | 'is'); values (l_emp.first_name | ''| l_emp.last_name); end loop; close emp_cur; end;/2. implicit cursor: you do not need to explicitly create a cursor variable. There are two types:. in PL/SQ L uses the DML language and the implicit cursor named SQL provided by ORACLE B. cursor for loop, used in for loop Statement. example: declare begin update configurations set department_name = department_name; -- where 1 = 2; dbms_output.put_line ('update' | SQL % rowcount | 'records'); end; B. example: declare begin for my_dept_rec in (select department_name, department_id from orders) loop dbms_output.put_line (rows | ':' | my_dept_rec.departmen T_name); end loop; end; c. example: select declare l_empno emp separately. EMPLOYEE_ID % type; -- l_ename emp. ename % type; begin select EMPLOYEE_ID into l_empno from emp; -- where rownum = 1; dbms_output.put_line (l_empno); end; use INTO to obtain the value, only one row can be returned. Cursor property: % FOUND: When the variable finally retrieves records from the cursor, the records are FOUND in the result set. % NOTFOUND: When the variable finally retrieves records from the cursor, no records are found in the result set. % ROWCOUNT: number of records that have been obtained from the cursor at the current time. % ISOPEN: whether or not to open. Declare Cursor emps is Select * from employees where rownum <6 order by 1; Emp employees % rowtype; Row number: = 1; Begin Open emps; Fetch emps into emp; loop If emps % found then Dbms_output.put_line ('looping over record '| row |' of '| emps % rowcount); Fetch emps into emp; Row: = row + 1; elsif emps % notfound then Exit; --- exit loop, not IF End if; End loop; If emps % isopen then Close emps; End if; E Nd; Difference between explicit and implicit cursors: use implicit cursors whenever possible to avoid additional cursor control code (Declaration, open, retrieve, and close ), you do not need to declare variables to save the data obtained from the cursor. 3. ref cursor: the query used by the CURSOR can be determined only when the CURSOR is running. Category: 1. Strong type (Restriction) ref cursor, specifying the return type 2. Weak type (unrestricted) ref cursor. No return type is specified and any result set can be obtained. TYPE ref_cursor_name is ref cursor [RETURN return_type] Declare Type refcur_t is ref cursor; Type emp_refcur_t is ref cursor return employee % rowtype; Begin Null; End; strong TYPE example: declare -- declare record type emp_job_rec is record (employee_id number, employee_name varchar2 (50), job_title varchar2 (30); -- declare ref cursor, the returned value is of this record type; -- defines the variable emp_refcur emp_job_refcur_type of the ref cursor CURSOR; emp_job emp_job_rec; begin open emp_refcur for select e. employee_id, e. first_name | ''| e. last_name "employee_name", j. job_title from employees e, jobs j where e. job_id = j. job_id and rownum <11 order by 1; fetch emp_refcur into emp_job; while emp_refcur % found loop aggregate (emp_job.employee_name | ''s job is '); Aggregate (emp_job.job_title ); fetch emp_refcur into emp_job; end loop; end;