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;
注:仅适用于在一月之内的循环。
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;
分享到:
相关推荐
Oracle用三种循环方法输出乘法表,比如loop的用法: m := 1; loop n := 1; loop dbms_output.put(m||'*'||n||'='||m*n||' '); n := n + 1; exit when n > m; end loop; dbms_output.put_line(''); ...
本文为大家分享了Oracle中三种循环(For、While、Loop)案例,供大家参考,具体内容如下 1.ORACLE中的FOR循环用法(九九乘法表) declare i int:=0; j int:=0; begin for i in 1..9 loop for j in 1..i loop Dbms_...
Oracle数据库编程经典300例,这是完整版的pdf.可以参考
oracle plsql中的lop、for循环(求素数例子)
Welcome to “Beginning SQL for Oracle Database 18C”! Thanks for picking up this book. It’s safe to say you’re interested in learning about Oracle database, as that’s what this book is about. Why ...
Oracle 10g 第 2 版 ODAC 和 Oracle Developer Tools for Visual Studio .NET 下载文件 ODTwithODAC1020221.exe 235 MB(247,296,458 字节) ODAC 安装说明 Oracle Developer Tools For Visual ...
数据库oracle锁: for update of和for update的区别,解释得很清楚,有很多详细例子说明。
《Reference for Oracle GoldenGate for Windows and UNIX》参考文档,涉及到内容Oracle GoldenGate参数详解。
Oracle Developer Tools for Visual Studio 2019( ODT for VS 2019 19.3.0.0.0 (12 MB)) Download ODT for VS 2019, extract and run the VSIX file to install Oracle Developer Tools for Visual Studio 2019 ...
Important: Before you install this package please make sure there is no ODAC/Oracle Universal Installer (OUI) version of Oracle Developer Tools for Visual Studio installed on the machine. If there is ...
Pro Oracle Spatial for Oracle Database 11g shows how to take advantage of Oracle Database's built-in feature set for working with location-based data. A great deal of the information used in business ...
Oracle 12c For Dummies
oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO ...
资料,关于Oracle for windows安装的
初学者 练习 oracle loop while
navicat for oracle
SQL ServerOracle 迁移助手(SSMA)包含一个客户端应用程序,该应用程序用于执行从 Oracle 到SQL Server AZURE SQL...如果要升级到 SSMA for Oracle 的更高版本,必须先卸载客户端和服务器扩展包,然后安装较新的版本。
Statement_Tracer_for_Oracle是一款非常好用的oracle跟踪工具 目前网上基本都是英文原版的
toad for oracle 11.6 64bit 注册码 for oracle license key
在为SQL SERVER创建ORACLE的连接服务器时,没有ORACLE provider for OLE DB 的连接服务驱动。经网上搜索,把PDF安装说明和驱动程序一起收藏,请各位下载;