Oracle Cursor - Cursori

Un cursore è un puntamento ad una "private SQL area" dove risiede uno specific statement SQL. Si possono usare gli attribute del cursore per sapere il suo status attuale (quanti record ha elaborato, se è aperto o chiuso, se non ha trovato qualcosa..).

--Implicit/Explicit Cursor:
Il cursore della sessione costruita e gestita da PL/SQL è implicito.

  Il PL/SQL apre un cursore implicito ogni volta che esegue uno statement SQL.

  Per il cursore implicito non bisogna fare nessuna dichiarazione (come nel caso di uno statement SQL).

Il cursore della sessione che si "costruisce" e si "gestisce" è esplicito. Per aprire un cursore esplicito occorre una dichiarazione!

--> Gli attributi del Cursore Implicito sono:
 - SQL%ISOPEN          Attribute: Is the Cursor Open?
 - SQL%FOUND           Attribute: Were Any Rows Affected?
 - SQL%NOTFOUND        Attribute: Were No Rows Affected?
 - SQL%ROWCOUNT        Attribute: How Many Rows Were Affected?
 - SQL%BULK_ROWCOUNT   Getting Number of Rows Affected by FORALL Statement
 - SQL%BULK_EXCEPTIONS Handling FORALL Exceptions After FORALL Statement Completes

--> Gli attributi del Cursore Esplicito sono:
  - CURSOR_NAME%ISOPEN   Attribute: Is the Cursor Open?
  - CURSOR_NAME%FOUND    Attribute: Has a Row Been Fetched?
  - CURSOR_NAME%NOTFOUND Attribute: Has No Row Been Fetched?
  - CURSOR_NAME%ROWCOUNT Attribute: How Many Rows Were Fetched?

##########################################################  Cursore implicito
for cur_int in (   SELECT ename, sal, hiredate, deptno 
                     FROM emp
                )
loop
  ...
end loop;

##########################################################  Cursore Esplicito
DECLARE
  CURSOR c1 IS
  SELECT ename, sal, hiredate, deptno FROM emp;
  ...
BEGIN
  FOR emp_rec IN c1 LOOP
  ...
END LOOP;

##########################################################  Cursore Esplicito

create procedure CREA_SCRIPT_EXP_DWH   IS
cursor c_t is
select *
from bck.ELENCO_TABELLE_DWH
order by proprietario,tipo;
r c_t%rowtype;

BEGIN

--Apertura cursore
        open c_t; loop
        fetch c_t into r; exit when c_t%NOTFOUND;
--Chiusura cursore
        end loop; close c_t;
END;

/ 

##########################################################  ESEMPIO ATTRIBUTI CURSORE (con Dichiarazione RowType):

declare
   CURSOR c_esiti_adr IS
   SELECT *
     FROM d_ruoli_esiti_adr_stg
    WHERE tipo_flusso_esitato   = v_tipo_flusso;
   r_adr d_ruoli_esiti_adr_stg%ROWTYPE;
begin
         OPEN c_esiti_adr;
         LOOP
            FETCH c_esiti_adr INTO r_adr;
            EXIT WHEN c_esiti_adr%NOTFOUND; -- EXIT WHEN emp_cur%ROWCOUNT > 5; --IF emp_cur%FOUND THEN..else.endif.
                 dbms_output.put_line ('sax,ascjba,sdc');
         END LOOP;
         CLOSE c_esiti_adr;
end;
/

##########################################################ESEMPIO sql%rowcount (ATTRIBUTI del CURSORE-SQL):
DELETE d_ruoli_esiti_testa_coda_stg
 WHERE tipo_flusso_esitato   = v_tipo_flusso
;
dbms_output.put_line('--# Record Cancellati D_RUOLI_ESITI_TESTA_CODA_STG: '||sql%rowcount);

--Altri Attributi: SQL%Rowcount SQL%FOUND SQL%NOTFOUND

##########################################################ESEMPIO CURSORE Senza dichiarazione RowType:
declare
    CURSOR c1 IS
        SELECT *
          FROM d_riscosso_testa_coda_stg
         WHERE versione_flusso       = v_versione
           AND tipo_record           = v_tipo_record_file_testa
           AND flag_scarto           = '0';
begin
        FOR rec1 IN c1
        LOOP
                 dbms_output.put_line ('sax,ascjba,sdc');
        END LOOP;
end;
/

########################################################## REF CURSOR:
--> REF CURSOR

è un cursore esplicito che può essere costruito dinamicamente
To pass a cursor variable parameter between subprograms in different PL/SQL units, define the REF CURSOR type of the parameter in a package.

--> SYS REF CURSOR

è un evoluzione del REF_CURSOR che non necessita più della definizione.

Anonymus Procedure

DECLARE
-------------------------------
--Creazione tabella dinamica:--
-------------------------------
   creatabella            VARCHAR (3000)
      :=    'CREATE TABLE tmp_recesso_acq AS('
         || 'SELECT a.row_id FROM s_doc_quote a, s_quote_soln b, s_org_int c '
         || 'WHERE a.row_id = b.quote_id AND b.x_bu_id = c.row_id '
         || 'AND c.NAME = ''Telecom Italia France'' AND a.x_var_30_26 = ''Y'')';
-----------------------------------
--Cancellazione tabella dinamica:--
-----------------------------------
   cancellatabella        VARCHAR (300):= 'DROP TABLE tmp_recesso_acq';
---------------------------------
--Query sulla tabella dinamica:--
---------------------------------
   querycursore           VARCHAR (1000):= 'SELECT row_id FROM tmp_recesso_acq';

-----------------------------------------
--Dichiarazione di un cursore generico:--
-----------------------------------------
   TYPE doc_quote_cur_typ IS REF CURSOR;
   cur_all_quote          doc_quote_cur_typ;

---------------------------------------------------------------------
--Dichiarazione variabile per inserire i dati del cursore generico:--
---------------------------------------------------------------------
   rec_all_quote_row_id   VARCHAR2 (20);
BEGIN
-------------------------------
--Creazione tabella dinamica:--
-------------------------------
   EXECUTE IMMEDIATE (creatabella);

---------------------------------------------------------------------------------
--Apertura del un cursore generico sulla Query eseguita sulla tabella dinamica:--
---------------------------------------------------------------------------------
   OPEN cur_all_quote FOR querycursore;
   LOOP
      FETCH cur_all_quote INTO rec_all_quote_row_id;
      EXIT WHEN cur_all_quote%NOTFOUND;

......

   END LOOP;

-------------------------------------
--Chiusura del un cursore generico:--
-------------------------------------
   CLOSE cur_all_quote;

-----------------------------------
--Cancellazioen tabella dinamica:--
-----------------------------------
   EXECUTE IMMEDIATE (cancellatabella);

end;
/

########################################################## SYS REF CURSOR:

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN   emp.deptno%TYPE,
                      p_recordset OUT  SYS_REFCURSOR)

AS
BEGIN
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/ 

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);           
  LOOP
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

Others..

##################################################  Ciclo for
--> Numerico

for i in 1..1000 loop
rlist(i) := rid_tab(i+SELF.howmany);
end loop;

--> Character string

SET SERVEROUTPUT ON SIZE 4000

DECLARE

   TYPE lista IS TABLE OF VARCHAR2 (50);

   my_list   lista := lista ('BMCMCU02', 'BMCMCU01', 'BMCMCA01');

BEGIN

   FOR i IN my_list.FIRST .. my_list.LAST

   LOOP

      DBMS_OUTPUT.put_line (my_list (i));

   END LOOP;

END;

##################################################  Ciclo While
WHILE condition
LOOP
     {.statements.}
END LOOP;
WHILE monthly_value <= 4000
LOOP
     monthly_value := daily_value * 31;
END LOOP;