PL/SQL LOOP SAMPLE

 

 

-- Cursor FOR loop --
set serveroutput on
DECLARE
   CURSOR c1 IS SELECT lc,ac FROM account where rownum<10;
   i NUMBER:= 0;
BEGIN
  FOR e_rec IN c1 LOOP
  i:= i+1;
    dbms_output.put_line(i||chr(9)||e_rec.lc||chr(9)||e_rec.ac);
  END LOOP; 
END;
/

-- Cursor FOR loop --
set serveroutput on
DECLARE 
   i NUMBER:= 0;
BEGIN
  FOR e_rec IN (SELECT lc,ac FROM account where rownum<10) LOOP
  i:= i+1;
    dbms_output.put_line(i||chr(9)||e_rec.lc||chr(9)||e_rec.ac);
  END LOOP; 
END;
/


-- Cursor FOR loop --
set serveroutput on
DECLARE 
   l_sql varchar2(150);        -- variable that contains a query
   i NUMBER:= 0;
   l_c sys_refcursor;
   v_lc account.lc%TYPE;
   v_acct account.ac%TYPE;
BEGIN
  l_sql := 'SELECT lc,ac FROM account where rownum<10';
  OPEN l_c FOR l_sql;
  LOOP
    FETCH l_c INTO v_lc,v_acct;
    i:= i+1;
    EXIT WHEN l_c%NOTFOUND;
    dbms_output.put_line(i||chr(9)||v_lc||chr(9)||v_acct);
  END LOOP;
  CLOSE l_c;
END;
/

-- Cursor FOR loop --
set serveroutput on
DECLARE  
   i NUMBER:= 0;
   CURSOR acct_cursor IS SELECT lc,ac FROM account where rownum<10;
   v_lc account.lc%TYPE;
   v_acct account.ac%TYPE;
BEGIN 
  OPEN acct_cursor;
  LOOP
    FETCH acct_cursor INTO v_lc,v_acct;
    i:= i+1;
    EXIT WHEN acct_cursor%NOTFOUND;
    dbms_output.put_line(i||chr(9)||v_lc||chr(9)||v_acct);
  END LOOP;
  CLOSE acct_cursor;
END;
/



-- Cursor FOR loop --
set serveroutput on
DECLARE  
   i NUMBER:= 0;
   l_c sys_refcursor;
   v_lc account.lc%TYPE;
   v_acct account.ac%TYPE;
BEGIN 
  OPEN l_c FOR 'SELECT lc,ac FROM account where rownum<10';
  LOOP
    FETCH l_c INTO v_lc,v_acct;
    i:= i+1;
    EXIT WHEN l_c%NOTFOUND;
    dbms_output.put_line(i||chr(9)||v_lc||chr(9)||v_acct);
  END LOOP;
  CLOSE l_c;
END;
/

 

 

 

DECLARE
    TYPE output_type IS RECORD (
      USER_CLASS_ID       VARCHAR(20),
      TABLE_TYPE          VARCHAR(03)
    );
    output_record output_type;
    c_delimiter CONSTANT CHAR(1 CHAR)  := '￥';
BEGIN
            --
            DBMS_OUTPUT.PUT(TRIM(output_record.USER_CLASS_ID)||c_delimiter);
            DBMS_OUTPUT.PUT(output_record.TABLE_TYPE||c_delimiter); 
            DBMS_OUTPUT.NEW_LINE();
            --
END;
/ 

LOOP SAMPLE FOR -UPDATE

----循环修改运输记录的货主的TmsCode  方法---
declare
  balance_Id varchar2(100);
  balance_Name varchar2(100);
  conNum number;
begin      
     --循环运输记录中的结算单位（ID,NAME）（查询结果集循环）--
     for item in (select A.BALANCE_ID,A.BALANCE_NAME from VIEW_GZ_YIMIAO A group by A.BALANCE_ID,A.BALANCE_NAME) loop
         -- 将查询到的数据赋值给变量 --
         balance_Id := item.balance_id;
         balance_Name := item.balance_name;      
         -- 获取结算单位是否存在(查询结果集赋值)---
         select COUNT(*) into conNum from Dxc_Consignor where NAME=balance_Name;
         --判断是否存在，存在则将结算单位ID赋给货主的TmsCode --
         if conNum=1 then
              dbms_output.put_line('存在');
                --执行修改--
              update Dxc_Consignor set TMSCODE=balance_Id where NAME=balance_Name;
         else
              dbms_output.put_line('不存在');
              --执行新增--
         end if;
                
      end loop;    
end;

SIMPLE LOOP AND BULK COLLECT INTO

BEGIN
  FOR employees IN (SELECT emp_id FROM emp)
  LOOP
    dbms_output.put_line( employees.emp_id );
  END LOOP;
END;


DECLARE
  TYPE emp_id_tbl IS TABLE OF emp.emp_id%type;
  l_emp_ids emp_id_tbl ;
BEGIN
  SELECT emp_id
    BULK COLLECT INTO l_emp_ids 
    FROM emp;

  FOR i IN l_emp_ids .FIRST .. l_empnos.LAST
  LOOP
    dbms_output.put_line( l_emp_ids (i) );
  END LOOP;
END;

 

LOOP TO DELETE

declare 
   l_sql varchar2(500);        -- variable that contains a query
   l_c   sys_refcursor;        -- cursor variable(weak cursor). 
   l_res1 VARCHAR2(60 BYTE);   -- variable containing fetching data 
   l_res2 VARCHAR2(60 BYTE);   -- variable containing fetching data 
   l_res3 date;   -- variable containing fetching data 
begin
         l_sql := 'select fk_col.table_name, fk_col.column_name from user_constraints pk, user_constraints fk, user_cons_columns fk_col where pk.table_name = ''STAGING_STATE'' and pk.constraint_type = ''P'' and fk.r_constraint_name = pk.constraint_name and fk_col.constraint_name = fk.constraint_name';
         open l_c for l_sql;
         loop
         fetch l_c into l_res1,l_res2;
         exit when l_c%notfound;   -- Exit the loop if there is nothing to fetch.  
        EXECUTE IMMEDIATE 'delete from ' || l_res1 || ' where ' || l_res2 || ' NOT IN (SELECT GUID FROM STAGING_STATE WHERE LOCATION_CODE IN (SELECT LOC_TABLE_ENTRY_KY FROM STAGING_LC))'; 
        -- process fetched data 
        end loop;
        close l_c; -- close the cursor 
end; 
/
commit;

 

COMMON:

declare
  l_sql varchar2(123);        -- variable that contains a query
  l_c   sys_refcursor;        -- cursor variable(weak cursor). 
  l_res your_table%rowtype;   -- variable containing fetching data  
begin
  l_sql := 'select * from your_table';
  -- Open the cursor and fetching data explicitly 
  -- in the LOOP.
  open l_c for l_sql;

  loop
    fetch l_c into l_res;
    exit when l_c%notfound;   -- Exit the loop if there is nothing to fetch.
     -- process fetched data 
  end loop;

  close l_c; -- close the cursor
end;

 

NUMERIC LOOP

-- Numeric FOR loop --
set serveroutput on -->> do not use in TOAD --
DECLARE
  k NUMBER:= 0;
BEGIN
  FOR i IN 1..10 LOOP
    k:= k+1;
    dbms_output.put_line(i||' '||k);
 END LOOP;
END;
/

 

-- Cursor FOR loop --
set serveroutput on
DECLARE
   CURSOR c1 IS SELECT * FROM scott.emp;
   i NUMBER:= 0;
BEGIN
  FOR e_rec IN c1 LOOP
  i:= i+1;
    dbms_output.put_line(i||chr(9)||e_rec.empno||chr(9)||e_rec.ename);
  END LOOP;
END;
/