EXPLICIT CURSORTypes of cursor
Created and Managed internally by the oracle server to process SQL statements. Attributes Description ---------------- ---------------------------------------------------------------------------- sql%notfound Boolean attributes that evaluates to TRUE. If the most recent SQL statement did not return even one row. sql%found Boolean attribute that evaluates to TRUE if the most recent SQL statement returned at least on row. sql%rowcount An integer value that represents the number of row affected by the most recent SQL statement.Implicit cursor : declare v_rows number(5); begin update emp set sal=sal+100 where comm is null; if sql%notfound then dbms_output.put_line('NOT UPDATE'); elsif sql%found then v_rows := sql%rowcount; dbms_output.put_line('salary for '||v_rows||' people increased'); end if; end; / salary for 10 people increased PL/SQL procedure successfully completed.Explicit cursor : Explicit declared the cursor name by the programmer. Attributes Description ---------------- --------------------------------------------------------------------- CUR%ISOPEN Evaluates to TRUE if the cursor is open. CUR%NOTFOUND Evaluates to TRUE if the most recent fetch does not return a row. CUR%FOUND Evaluates to TRUE if the most recent fetch returns a row: complement of %NOTFOUND CUR%ROWCOUNT Evaluates to the total number of rows returned so far.Explicit cursor : Explicit declared the cursor name by the programmer. Syntax : CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ] [ RETURN return_specification ] IS sql_select_statements [FOR UPDATE [OF [column_list]]; Begin Open cursor_name: statement; Close cursor_name; End; ![]() Action Description ---------- ------------------------------------------------------------- Declare cursor is creating a memory area. Open Identify the active set from a SQL query Fetch Load the current information into variables Empty Its check whether row exists, if exists fetch the record. Close Release the active set.Explicit cursor - static : SQL> declare tesdb_dept dept%rowtype; cursor tesdb_cur is select * from dept; begin open tesdb_cur; fetch tesdb_cur into tesdb_dept; dbms_output.put_line(tesdb_dept.deptno||' '|| tesdb_dept.dname||' '||tesdb_dept.loc); close tesdb_cur; end; / 10 ACCOUNTING NEW YORK PL/SQL procedure successfully completed.Explicit cursor - Dynamic : SQL> declare tesdb_dept dept%rowtype; cursor tesdb_cur is select * from dept; begin open tesdb_cur; loop fetch tesdb_cur into tesdb_dept; exit when tesdb_cur%notfound; dbms_output.put_line (tesdb_dept.deptno||' '|| tesdb_dept.dname||' '||tesdb_dept.loc); end loop; close tesdb_cur; end; / 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALESTEAM CHICAGO 40 OPERATIONS BOSTON 90 SALES MUMBAI PL/SQL procedure successfully completed. Explicit cursor -%isopen SQL >declare tesdb_dept dept%rowtype; cursor tesdb_cur is select * from dept; begin if not tesdb_cur%isopen then open tesdb_cur; end if; loop fetch tesdb_cur into tesdb_dept; exit when tesdb_cur%notfound; dbms_output.put_line (tesdb_dept.deptno ||' '||tesdb_dept.dname||' '||tesdb_dept.loc); end loop; close tesdb_cur; end; / 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALESTEAM CHICAGO 40 OPERATIONS BOSTON 90 SALES MUMBAI PL/SQL procedure successfully completed.For loop with cursor : SQL> declare cursor tesdb_cur is select * from salgrade; tesdb_salgrade salgrade%rowtype; begin for tesdb_salgrade in tesdb_cur loop dbms_output.put_line(tesdb_salgrade.grade||' '|| tesdb_salgrade.losal||' '||tesdb_salgrade.hisal); end loop; end; / 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 PL/SQL procedure successfully completed.While loop with cursor : SQL> declare cursor tesdb_cur is select * from dept; tesdb_dept tesdb_cur%rowtype; begin open tesdb_cur; fetch tesdb_cur into tesdb_dept; while tesdb_cur%found loop dbms_output.put_line(tesdb_dept.deptno||' '|| tesdb_dept.dname||' '||tesdb_dept.loc); fetch tesdb_cur into tesdb_dept; end loop; end; / 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALESTEAM CHICAGO 40 OPERATIONS BOSTON 90 SALES MUMBAI PL/SQL procedure successfully completed.Reference cursor : An acronym of reference to a cursor.A Ref cursor is plsql data type is the memory address of work area. Ref cursor is a pointer to handle result set on the database.A Ref cursor refers to a memory address on the database. declare type tesdb_ty is ref cursor; cur_tesdb tesdb_ty; db_free number; db_used number; db_total number; be --free space open cur_tesdb for select sum(bytes/1024/1024) from dba_free_space; loop fetch cur_tesdb into db_free; exit when cur_tesdb%notfound; dbms_output.put_line ('database free space is '||db_free); end loop; close cur_tesdb; --used space open cur_tesdb for select sum(bytes/1024/1024) from dba_segments; loop fetch cur_tesdb into db_used; exit when cur_tesdb%notfound; dbms_output.put_line ('database used space is '||db_used); end loop; close cur_tesdb; --total space open cur_tesdb for select sum(bytes/1024/1024) from dba_data_files; loop fetch cur_tesdb into db_total; exit when cur_tesdb%notfound; dbms_output.put_line ('database total available space is '||db_total); end loop; close cur_tesdb; end; / database free space is 1443.125 database used space is 674.875 database total available space is 2130 PL/SQL procedure successfully completed.Parameterized cursor : Parameterized cursors are static cursors that can accept passed-in parameter values when they are opened. declare a number; cursor c1(b number) is select empno from emp where deptno=10; begin open c1(10); loop fetch c1 into a; dbms_output.put_line(a); exit when c1%notfound; end loop; end; 7782 7839 7934 7934Non-declared cursor : declare a number begin for i in (select * from emp) loop dbms_output.put_line(i.empno); end loop; end; / PL/SQL procedure successfully completed. 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 PL/SQL procedure successfully completed. « Previous Next Topic » (PL/SQL - Exceptions) |