使用Oracle的动态SQL支持
使用Oracle的动态SQL支持
Oracle动态SQL
- DBMS_SQL的内置包
- Native Dynamic SQL
Native Dynamic SQL的三种常用功能
- 执行不包含绑定变量的动态DDL或DML
- 执行包含绑定变量的动态DDL或DML
- DQL支持,Data Query Language
场景/简单流程
代码
- DECLARE
- TMPSTR VARCHAR2(4000) := NULL;
- TMPI NUMBER(10) := 0;
- BEGIN
- FOR C IN (SELECT B.TABLE_NAME BTN
- FROM USER_CONSTRAINTS A
- LEFT JOIN USER_CONSTRAINTS B
- ON A.OWNER = B.OWNER
- AND B.R_CONSTRAINT_NAME = A.CONSTRAINT_NAME
- WHERE A.OWNER = 'NSTCSA224'
- AND A.TABLE_NAME = 'CPM_BRANCH') LOOP
- IF (C.BTN IS NOT NULL) THEN
- TMPSTR := 'BEGIN SELECT COUNT(*) INTO :CC FROM ' || C.BTN || ' ; END;';
- EXECUTE IMMEDIATE TMPSTR USING OUT TMPI;
- DBMS_OUTPUT.PUT_LINE('SQL语句: ' || TMPSTR);
- DBMS_OUTPUT.PUT_LINE('表名称:' || C.BTN || ' 数据:' || TMPI);
- END IF;
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE(SQLERRM);
- END;
参考资料
1、Oracle Database 10g PL/SQL
评论暂时关闭