我們今天是要和大家一起討論的是Oracle for in loop 兩例,我前兩天在相關網站看見Oracle for in loop 兩例的相關資料,覺得在實際操作中會對計算機一族有所幫助,就拿出來供大家分享。
Oracle for in loop 壹:
1、搭建環境
- create table ETL_DATE(DATE_CHAR VARCHAR2(8),DATE_DATE DATE);
- select * from etl_date;
2、代碼
- DECLARE
- v_date date;
- BEGIN
- EXECUTE IMMEDIATE 'truncate table etl_date';
- for v_date in 20091001 .. 20091021 LOOP
- INSERT INTO etl_date
- (date_char, date_date)
- SELECT v_date, to_date(v_date, 'YYYY-MM-DD') FROM dual;
- END LOOP;
- COMMIT;
- END;
- select * from etl_date;
3、刪除環境
- drop table etl_date;
注:僅適用於在一月之內的循環。
Oracle for in loop 貳:
1、搭建環境
- create table SQLTEXT(TEXT VARCHAR2(100));
- create table HZ(HZ_NAME VARCHAR2(3));
- INSERT INTO hz(HZ_NAME)values(' ');
- INSERT INTO hz(HZ_NAME)values('PRE');
- INSERT INTO hz(HZ_NAME)values('CUR');
- INSERT INTO hz(HZ_NAME)values('INS');
- INSERT INTO hz(HZ_NAME)values('UPD');
- select * from HZ;
2、代碼
- declare
- P_TABLE_NAME varchar2(100) := 'CFA';
- begin
- for HZ in (select HZ_NAME from HZ) LOOP
- insert into sqltext
- select 'CREATE TABLE ' || REPLACE(P_TABLE_NAME, 'EDW', 'TMP') ||
- HZ.HZ_NAME || ' AS select * from ' || P_TABLE_NAME ||
- ' where ROWNUM<1'
- from dual;
- END LOOP;
- end;
- select * from SQLTEXT;
3、清空環境
- drop table SQLTEXT;
- drop table HZ;
- powershell