DB2在dbvisualizer 客户端执行begin end 语句块的案例,亲测可用
来源:互联网 发布:淘宝代销怎么终止合同 编辑:程序博客网 时间:2024/06/08 18:00
注意,begin end 代码块在dbvisualizer 执行前要加 --/ 后面要加 /
--/ BEGIN DECLARE v_i INTEGER ; declare sqlStr varchar(1000); set v_i=1; REPEAT SET sqlStr ='CREATE TABLE A_TEST'|| v_i||' ( ID BIGINT NOT NULL ) ' ; EXECUTE IMMEDIATE sqlStr; SET v_i = v_i + 1; UNTIL (v_i > 3) END REPEAT;END /
该语句块的作用是在循环创建表,表名是带有数字变量拼接而成的,该语句可以直接放到dbvisualizer 中执行
下面是我业务需要使用的begin end 语句块,动态创建表,和注释
注意事项 :
1、EXECUTE IMMEDIATE 后的sql 语句中不要含有分号;
2、字段注释sql 中的汉字,首尾用单引号包起来的,这个单引号需要转义,转义方式是单引号前加一个单引号(例: '')
--/ BEGIN DECLARE v_i INTEGER ; declare sqlStr varchar(2000); declare sqlcomments_1 varchar(500); declare sqlcomments_2 varchar(500); declare sqlcomments_3 varchar(500); declare sqlcomments_4 varchar(500); declare sqlcomments_5 varchar(500); declare sqlcomments_6 varchar(500); declare sqlcomments_7 varchar(500); declare sqlcomments_8 varchar(500); declare sqlcomments_9 varchar(500); declare sqlcomments_10 varchar(500); declare sqlcomments_11 varchar(500); declare sqlcomments_12 varchar(500); declare sqlcomments_13 varchar(500); declare sqlcomments_14 varchar(500); declare sqlcomments_15 varchar(500); declare sqlcomments_16 varchar(500); set v_i=2014; REPEAT SET sqlStr = 'CREATE TABLE FUND_DAILY_INCOME_'||v_i||' ( ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, ACCOUNT_NO VARCHAR(32), CREATED_TIME TIMESTAMP, FUND_CODE VARCHAR(8), SHARE_HOLDING VARCHAR(15), INCOME_DATE VARCHAR(10), INCOME_AMOUNT BIGINT, MERGE_FLAG VARCHAR(1), OFFSET_FLAG VARCHAR(1), UPDATE_TIME TIMESTAMP, COMPANY_CODE VARCHAR(16), ALL_INCOME DECIMAL(19,0) DEFAULT 0, HOLDING_INCOME DECIMAL(19,0) DEFAULT 0, SERVICE_TYPE VARCHAR(2), PLAT_ID BIGINT, SERVICE_CODE VARCHAR(32), PRIMARY KEY (ID) )' ; set sqlcomments_1 = 'COMMENT ON TABLE FUND_DAILY_INCOME_'||v_i||' IS ''用户每日收益表'' '; set sqlcomments_2 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.ACCOUNT_NO IS ''户头号'' '; set sqlcomments_3 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.FUND_CODE IS ''基金编号'' ' ; set sqlcomments_4 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.INCOME_DATE IS ''收益日期''' ; set sqlcomments_5 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.INCOME_AMOUNT IS ''收益(分)'''; set sqlcomments_6 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.MERGE_FLAG IS ''合计到累计收益标志'''; set sqlcomments_7 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.OFFSET_FLAG IS ''是否是调账标志'''; set sqlcomments_8 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.UPDATE_TIME IS ''更新时间'''; set sqlcomments_9 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.COMPANY_CODE IS ''基金公司商户号'''; set sqlcomments_10 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.ALL_INCOME IS ''累计收益'''; set sqlcomments_11 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.HOLDING_INCOME IS ''持有收益'''; set sqlcomments_12 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.SERVICE_TYPE IS ''业务类型 01-普通基金,02-智投组合,03-目标赢'''; set sqlcomments_13 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.PLAT_ID IS ''销售平台ID 10000-默认苏宁金融''' ; set sqlcomments_14 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.SERVICE_CODE IS ''业务编码 10000-默认基金超市''' ; set sqlcomments_15 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.SHARE_HOLDING IS ''持有份额'''; set sqlcomments_16 = 'COMMENT ON COLUMN FUND_DAILY_INCOME_'||v_i||'.CREATED_TIME IS ''创建时间'''; EXECUTE IMMEDIATE sqlStr; EXECUTE IMMEDIATE sqlcomments_1; EXECUTE IMMEDIATE sqlcomments_2; EXECUTE IMMEDIATE sqlcomments_3; EXECUTE IMMEDIATE sqlcomments_4; EXECUTE IMMEDIATE sqlcomments_5; EXECUTE IMMEDIATE sqlcomments_6; EXECUTE IMMEDIATE sqlcomments_7; EXECUTE IMMEDIATE sqlcomments_8; EXECUTE IMMEDIATE sqlcomments_9; EXECUTE IMMEDIATE sqlcomments_10; EXECUTE IMMEDIATE sqlcomments_11; EXECUTE IMMEDIATE sqlcomments_12; EXECUTE IMMEDIATE sqlcomments_13; EXECUTE IMMEDIATE sqlcomments_14; EXECUTE IMMEDIATE sqlcomments_15; EXECUTE IMMEDIATE sqlcomments_16; SET v_i = v_i + 1; UNTIL (v_i > 2016) END REPEAT;END /
阅读全文
0 0
- DB2在dbvisualizer 客户端执行begin end 语句块的案例,亲测可用
- 自己照抄的一个begin end语句块
- 8.5.1:begin end语句块
- perl中的BEGIN和END语句块
- begin end中阻塞语句与非阻塞语句执行顺序的问题
- mysql支持调用begin end来执行代码块吗?
- SQL begin end 块作用
- perl BEGIN 和END块
- Perl BEGIN块和END块
- 语句块的执行
- dbvisualizer不能执行pl/sql块
- 使用DbVisualizer划线执行SQL语句的时候提示Selection executes are onlyallowed in DbVisualizer Personel edition
- 迭代器的begin,end和指针的begin,end
- 为什么 微软 sql 语句 用 begin end
- mysql语句之begin end 多重嵌套
- awk的BEGIN和END
- awk的BEGIN和END
- Oracle 游标 + PL/SQL块(BEGIN END) 真的很好用...
- 独轮车,白书P308Uva10047(BFS求最短路)
- 使用QT和opencv3.3进行目标检测(YOLO)
- 集合覆盖问题、最大覆盖问题、呼叫中心排班问题闲谈
- JVM并不是那么重量级
- String.Format()方法
- DB2在dbvisualizer 客户端执行begin end 语句块的案例,亲测可用
- 51nod 1006 最长公共子序列Lcs
- 子类与继承:实验1
- 图像基础20 人脸辨识——人脸识别2
- 7-13 家庭房产(25 分)
- 电路实践
- 讲给Android程序员看的前端教程(32)——继承
- 模拟实现库函数
- Python模块一