• 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;
    /
  • 相关阅读:
    输入输出、基本运算符、流程控制
    Node学习6-fs模块
    Node学习5-events模块
    Node学习4-Buffer模块
    Node学习3-Path模块
    Node学习2-基础知识/HelloWorld/模块调用/global/process
    Node学习1-基本概念
    gulp/bower/less知识
    AngularJS学习1-基础知识
    JavaScript学习-类/原型链/class
  • 原文地址:https://www.cnblogs.com/kakaisgood/p/12744538.html
Copyright © 2020-2023  润新知