(一):循环内的异常处理
SET SERVEROUTPUT ON; DECLARE SELECT_SQL1 VARCHAR2(1000); SELECT_SQL2 VARCHAR2(1000); UPDATE_SQL VARCHAR2(1000); TABLE_NAME VARCHAR2(100); UNIT_ID VARCHAR2(100); BEGIN FOR C1 IN (SELECT DC.ID, DC.DOC_ID, DC.REF_ENTITY_NAME, DC.DOC_VERSION, DC.DOMAIN_ID FROM CNT_DOC_UNIT DC WHERE DC.UNIT_ID IS NULL) LOOP -- WHEN EXCEPTION OCCURED, CONTINUE THE LOOP BEGIN SELECT_SQL1 := 'SELECT PRODUCT_TABLE_NAME FROM CNT_ENTITY_DEFINITION WHERE ENTITY_NAME = ' || '''' || C1.REF_ENTITY_NAME || ''''; EXECUTE IMMEDIATE SELECT_SQL1 INTO TABLE_NAME; --DBMS_OUTPUT.PUT_LINE(C1.DOMAIN_ID); SELECT_SQL2 := 'SELECT ID FROM ' || TABLE_NAME || ' WHERE REF_NO = (SELECT REF_NO FROM ' || TABLE_NAME || ' WHERE ID = ' || '''' || C1.DOC_ID || '''' || ') AND VERSION = ' || C1.DOC_VERSION || ' AND DOMAIN_ID = ' || '''' || C1.DOMAIN_ID || '''' || ' AND IS_LATEST = 0 AND ROWNUM = 1 ORDER BY REVISION DESC'; --DBMS_OUTPUT.PUT_LINE(SELECT_SQL2); UNIT_ID := NULL; EXECUTE IMMEDIATE SELECT_SQL2 INTO UNIT_ID; UPDATE_SQL := 'UPDATE CNT_DOC_UNIT SET UNIT_ID = ' || '''' || UNIT_ID || '''' || ' WHERE ID = ' || '''' || C1.ID || ''''; --DBMS_OUTPUT.PUT_LINE(UPDATE_SQL); EXECUTE IMMEDIATE UPDATE_SQL; EXCEPTION WHEN OTHERS THEN NULL; END; -- WHEN EXCEPTION OCCURED, CONTINUE THE LOOP END LOOP; END; /
(二): 显式游标
set serveroutput on; DECLARE -- define cursor c_cursor CURSOR c_cursor IS SELECT DISTINCT ENTITY_NAME, PRODUCT_TABLE_NAME FROM cnt_entity_definition WHERE system_entity = 0; v_tablename cnt_entity_definition.product_table_name%TYPE; V_entityname cnt_entity_definition.entity_name%TYPE; v_dropcolumnsql varchar2(4000); BEGIN OPEN c_cursor; FETCH c_cursor INTO v_entityname,v_tablename; WHILE c_cursor%FOUND LOOP v_dropcolumnsql := 'ALTER TABLE ' || v_tablename || ' DROP COLUMN TEMP_NUM1'; -- execute immediate v_dropcolumnsql; DBMS_OUTPUT.PUT_LINE(v_dropcolumnsql); FETCH c_cursor INTO v_entityname,v_tablename; END LOOP; CLOSE c_cursor; END; /
(三):
DECLARE v_add_sql varchar2(1000); v_update_sql varchar2(1000); v_number NUMBER(20, 0) :=0; v_set_null_sql varchar2(1000); v_alter_sql varchar2(1000); v_set_value_sql varchar2(1000); v_drop_column_sql varchar2(1000); BEGIN FOR t in (SELECT DISTINCT ed.ENTITY_NAME, ed.PRODUCT_TABLE_NAME FROM CNT_ENTITY_DEFINITION ed JOIN USER_TABLES u ON ed.PRODUCT_TABLE_NAME=u.TABLE_NAME WHERE ed.SYSTEM_ENTITY = 0) LOOP -- ADD TEMP COLUMN v_add_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' ADD(TEMP_NUM NUMBER(20,0))'; EXECUTE IMMEDIATE v_add_sql; FOR v_number IN 1..6 LOOP v_update_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET TEMP_NUM=CUST_NUMBER' || v_number; -- update TEMP_NUM value to CUST_NUMBER's value EXECUTE IMMEDIATE v_update_sql; v_set_null_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET CUST_NUMBER' || v_number || '=NULL'; --set CUST_NUMER to null EXECUTE IMMEDIATE v_set_null_sql; v_alter_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' MODIFY(CUST_NUMBER' || v_number || ' NUMBER(20,0))'; --Alter CUST_NUMBER column TYPE to NUMBER(20, 0). EXECUTE IMMEDIATE v_alter_sql; v_set_value_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET CUST_NUMBER' || v_number || '=TEMP_NUM' ; --Set value of CUST_NUMBER = value in the TEMP_NUM. EXECUTE IMMEDIATE v_set_value_sql; END LOOP; --end for loop v_drop_column_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' DROP COLUMN TEMP_NUM'; EXECUTE IMMEDIATE v_drop_column_sql; END LOOP; END; /
(四):
set serveroutput on; DECLARE TYPE CUR_TYPE IS REF CURSOR; CUR CUR_TYPE; V_SELECT_RECORED_SQL VARCHAR2(1000); V_UPDATE_NEW_COLUMN_SQL VARCHAR2(1000); V_LOOKUP_KEY VARCHAR2(100); BEGIN FOR C1 IN (SELECT DISTINCT FD1.ENTITY_LOOKUP, FD1.ENTITY_LOOKUP_KEY, ED.PRODUCT_TABLE_NAME, FD2.COLUMN_NAME FROM CNT_FIELD_DEFINITION FD1 JOIN CNT_ENTITY_DEFINITION ED ON ED.ENTITY_NAME = ENTITY_LOOKUP JOIN USER_TAB_COLUMNS UTC ON ED.PRODUCT_TABLE_NAME = UTC.TABLE_NAME JOIN CNT_FIELD_DEFINITION FD2 ON FD2.PARENT_ID = ED.ID WHERE FD1.FIELD_TYPE = 'collection' AND ED.TYPE <> 'main' AND FD1.ENTITY_LOOKUP NOT IN ('VReportUserRole') AND utc.COLUMN_NAME = 'INTERNAL_SEQ_NO' AND FD2.FIELD_ID = FD1.ENTITY_LOOKUP_KEY) LOOP -- begin the loop c1 V_SELECT_RECORED_SQL := 'SELECT DISTINCT ' || C1.COLUMN_NAME || ' FROM ' || C1.PRODUCT_TABLE_NAME; OPEN CUR FOR V_SELECT_RECORED_SQL; LOOP FETCH CUR INTO V_LOOKUP_KEY; EXIT WHEN CUR%NOTFOUND; V_UPDATE_NEW_COLUMN_SQL := 'UPDATE ' || C1.PRODUCT_TABLE_NAME || ' SET INTERNAL_SEQ_NO = ROWNUM ' || ' WHERE ' || C1.COLUMN_NAME || ' = ''' || V_LOOKUP_KEY || ''''; --assign the value to new column EXECUTE IMMEDIATE V_UPDATE_NEW_COLUMN_SQL; --end the cursor loop CUR END LOOP; --end the loop c1. END LOOP; END; /
(五):
DECLARE TYPE CUR_TYPE IS REF CURSOR; CUR CUR_TYPE; V_ID VARCHAR2(100); V_PARENT_ID VARCHAR2(100); V_FIELD_ID VARCHAR2(100); V_CREATED_ON TIMESTAMP(6); V_UPDATE_SQL VARCHAR2(1000); V_SELECT_SQL VARCHAR2(1000); V_COUNT NUMBER; BEGIN FOR C1 IN (SELECT PARENT_ID,FIELD_ID FROM CNT_SELECTION GROUP BY PARENT_ID,FIELD_ID) LOOP -- begin the C1 loop V_SELECT_SQL := 'SELECT ID,CREATED_ON FROM CNT_SELECTION WHERE PARENT_ID = ''' || C1.PARENT_ID || ''' AND FIELD_ID = ''' || C1.FIELD_ID || ''' ORDER BY CREATED_ON'; V_COUNT := 1; OPEN CUR FOR V_SELECT_SQL; LOOP FETCH CUR INTO V_ID,V_CREATED_ON; EXIT WHEN CUR%NOTFOUND; V_UPDATE_SQL := 'UPDATE CNT_SELECTION SET INTERNAL_SEQ_NO = ' || V_COUNT || ' WHERE ID = ''' || V_ID || ''''; -- assign value to new column EXECUTE IMMEDIATE V_UPDATE_SQL; V_COUNT := V_COUNT + 1; END LOOP; -- end the loop C1 END LOOP; END; /
(六):
DECLARE TYPE DOMAINS_TYPE IS TABLE OF CNT_DOMAIN.DOMAIN_ID%TYPE INDEX BY PLS_INTEGER; V_DOMAIN_IDS DOMAINS_TYPE; V_UPDATE_SQL VARCHAR2(1000); V_INSERT_SQL VARCHAR2(1000); V_UPDATED_ON TIMESTAMP; V_DOMAIN_ID VARCHAR2(100); BEGIN UPDATE CNT_SEQ_DEF SET CYCLE_STARTED_ON = UPDATED_ON; -- update cycleStartedOn to updatedOn SELECT DOMAIN_ID BULK COLLECT INTO V_DOMAIN_IDS FROM CNT_DOMAIN WHERE DOMAIN_ID <> '/' AND DOMAIN_ID <> 'backend'; FOR C1 IN (SELECT ID FROM CNT_SEQ_DEF) LOOP -- begin the C1 looP FOR I IN V_DOMAIN_IDS.FIRST..V_DOMAIN_IDS.LAST LOOP V_DOMAIN_ID := V_DOMAIN_IDS(I); V_INSERT_SQL := 'INSERT INTO CNT_SEQ_DEF(REVISION, ENTITY_VERSION, DOMAIN_ID, ID, START_WITH, MAX_VALUE, INCREMENT_BY, CYCLE, CACHE_SIZE, UPDATED_ON, INTERNAL_SEQ_NO, SEQ_ID, NEXT_VAL, CYCLE_STARTED_ON) SELECT REVISION, ENTITY_VERSION, ''' || V_DOMAIN_ID || ''', SYS_GUID(), START_WITH, MAX_VALUE, INCREMENT_BY, CYCLE, CACHE_SIZE, UPDATED_ON, INTERNAL_SEQ_NO, SEQ_ID, NEXT_VAL, CYCLE_STARTED_ON FROM CNT_SEQ_DEF WHERE ID = ''' || C1.ID || ''''; -- duplicate a new record for domainId EXECUTE IMMEDIATE V_INSERT_SQL; END LOOP; -- end the loop C1 END LOOP; END; /
(七)
SET SERVEROUTPUT ON; DECLARE V_ID VARCHAR(200); S_ID VARCHAR2(200); ROW_DATA1 CNT_CODELIST_BOOK%ROWTYPE; ROW_DATA2 CTM_CODELIST_BOOK%ROWTYPE; ROW_DATA3 CNT_CODELIST%ROWTYPE; BEGIN FOR ROW_DATA1 IN (SELECT CLB.* FROM CNT_CODELIST_BOOK CLB, (SELECT ID, NAME FROM CNT_DATA_LIST_TYPE WHERE DOMAIN_ID = 'SGS' AND IS_LATEST = 1) DLT WHERE CLB.NAME = DLT.NAME(+) AND CLB.DOMAIN_ID ='SGS' AND CLB.IS_LATEST = 1 AND CLB.VERSION = (SELECT MAX(CCLB.VERSION) FROM CNT_CODELIST_BOOK CCLB WHERE CCLB.NAME = CLB.NAME AND CCLB.DOMAIN_ID = CLB.DOMAIN_ID AND CCLB.IS_LATEST = 1)) LOOP V_ID := ROW_DATA1.ID; ROW_DATA1.ID := SYS_GUID(); ROW_DATA1.DOMAIN_ID := 'ARONTEST1'; ROW_DATA1.HUB_DOMAIN_ID := 'ARONTEST1'; ROW_DATA1.REVISION := 1; ROW_DATA1.ENTITY_VERSION := 1; ROW_DATA1.VERSION := 1; ROW_DATA1.CREATE_USER := 'system'; ROW_DATA1.IS_FOR_REFERENCE := 0; INSERT INTO CNT_CODELIST_BOOK VALUES row_data1; SELECT * INTO ROW_DATA2 FROM CTM_CODELIST_BOOK WHERE ID = V_ID; ROW_DATA2.ID := ROW_DATA1.ID; ROW_DATA2.DOMAIN_ID := 'ARONTEST1'; INSERT INTO CTM_CODELIST_BOOK VALUES ROW_DATA2; FOR ROW_DATA3 IN (SELECT * FROM CNT_CODELIST WHERE PARENT_ID = V_ID) LOOP S_ID := ROW_DATA3.ID; ROW_DATA3.ID := SYS_GUID(); ROW_DATA3.PARENT_ID := ROW_DATA1.ID; ROW_DATA3.DOMAIN_ID := 'ARONTEST1'; ROW_DATA3.HUB_DOMAIN_ID := 'ARONTEST1'; ROW_DATA3.REVISION := 1; ROW_DATA3.ENTITY_VERSION := 1; ROW_DATA3.IS_FOR_REFERENCE := 0; INSERT INTO CNT_CODELIST VALUES ROW_DATA3; SELECT * INTO ROW_DATA2 FROM CTM_CODELIST_BOOK WHERE ID = S_ID; ROW_DATA2.ID := ROW_DATA3.ID; ROW_DATA2.DOMAIN_ID := 'ARONTEST1'; INSERT INTO CTM_CODELIST_BOOK VALUES ROW_DATA2; END LOOP; END LOOP; END; /
相关推荐
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。... 该安装包包含了解决PLSQL过期的文件,将PLSQL安装后将解决过期的文件放到安装包中,即可解决PLSQL过期的问题。即可永久使用。
plsql developer9 0破解版 PLSQL Developer 9 0 0 1601破解版本使用了8 0的汉化包 可以正常使用 无需注册 默认为英文 点击菜单tools preferences appearance language 选择简体中文即可切换到简体中文界面 如果...
PLSQL Developer 11.0.2.1766 中文绿色注册版(免Oracle客户端) PLSQL Developer 11.0.2.1766 中文绿色注册版(免Oracle客户端) 免安装Oracle客户端,绿色无...CN.GBK start PLSQLDev.exe 作用:设置临时变量,启动plsql
1、点击 "启动PLSQL.exe" 即可免装oracle使用PLSQL 或者 使用qidong.bat启动 (启动PLSQL.exe是qidong.bat编译的exe,隐藏调用qidong.bat启动PLSQL,比qidong.bat好处没有黑框界面,有图标); 2、Oracle64为oracle ...
下面简单介绍下PLSQL developer工具的SVN版本控制 1, 下载pl/sql-svn插件。 2, 关闭你打开的所有pl/sql窗口。 3,将插件里的SVN_Plugin.dll文件复制到PLSQL DeveloperPlugIns目录下。 4,重新打开pl/sql,...
PLSQL Developer9-GGS 0402版 (更新至Oracle11g2内核) 完美绿色中文破解版,真正地免安装Oralce,已集成最新的instantclient(Oracle11g2),兼容Oracle11g2以下所有版本。 如果需要运行原版,请直接进入bin文件夹...
plsql developer 64位是专门针对windows 64位操作系统开发的Oracle数据库管理工具,它拥有强大的数据库编辑器和智能化的语法特性,在使用sql语句创建和编辑数据库的基础上提供更加简捷实用的图形化界面进行数据库表...
plsql11安装包文件
PLSQL Developer 11.0.2.1766 中文绿色注册版(免Oracle客户端) PLSQL Developer 11.0.2.1766 中文绿色注册版(免Oracle客户端) 免安装Oracle客户端,绿色无公害。...作用:设置临时变量,启动plsql
PLSQL Developer7(oracle数据库) PLSQL Developer7(oracle数据库) PLSQL Developer7(oracle数据库) PLSQL Developer7(oracle数据库) PLSQL Developer7(oracle数据库) PLSQL Developer7(oracle数据库) ...
plsql developer plsql developer plsql developer plsql developer plsql developer
64位版本的 PLSQL 正式版,只能运行在64位系统中,需要你安装 64 位的 Oracle11g 或 Oracle12c 客户端。 安装中文包时请注意安装路径是否为PLSQL程序的路径。 Enhancements in PL/SQL Developer 11.0.6 ===========...
plsql安装程序plsql.rar......................................
plsql精解plsql精解plsql精解plsql精解
plsql删除重复记录
plsql11汉化包 一键汉化plsql 告别英文plsql 亲测可用
32位版本的 PLSQL 正式版。 安装请查看说明。 APRIL 8, 2022 - VERSION 15.0 RELEASED Dark Mode styles and Light Mode styles Read-Only Connections Editor enhancements A macro can now be repeated by ...
使plsql 中的日常操作更加便捷,可以自定义快捷键
64位版本的 PLSQL 正式版,只能运行在64位系统中,需要你安装 64 位的 Oracle 客户端。 安装请查看说明。 MAY 27, 2022 - VERSION 15.0.1 RELEASED Enhancements Stability improvements Difference Viewer ...
PLSQL Developer14用户指南英文版