[分享]如何捕捉表的约束信息并转化成sql脚本_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4391 | 回复: 0   主题： [分享]如何捕捉表的约束信息并转化成sql脚本        下一篇 
guoqiang
注册用户
等级：上士
经验：252
发帖：26
精华：0
注册：2012-4-12
状态：离线
发送短消息息给guoqiang 加好友    发送短消息息给guoqiang 发消息
发表于： IP：您无权察看 2012-4-12 9:56:44 | [全部帖] [楼主帖] 楼主

如何捕捉表的约束信息并转化成sql脚本

下面的信息就是教我们如何捕捉定义在一个表上的所有约束的语句信息，分布信息，重建信息等

列出的两个脚本可能会被用做捕捉现有表的约束信息在sql的可执行文件上

第一个脚本，创建一个输出文件，包含了一个给出对象中已存在的约束信息

第二个脚本，创建一个输出文件，包含了对一个单独的表增加约束的信息

获取脚本的约束信息最大的用处就是可以日后对约束的参数进行修改

例如：如果一个表被用 create as select 创建，那么这个新表的结构与之前的表一样，但是新表没有约束。那么g_tab_cons.sql可以用来获取这些约束条件并直接作用在新表上

G_schema_cons.sql脚本可以被用作是一个备份的工具，去创建最近被误删除的约束

输出的结果可以被编辑从而改变主键或者唯一性索性的storage参数

读下面的免责声明和解析

创建g_schema_cons.sql脚本的目的就是创建一个文件，文件中包含用户创建所有约束的信息。这个脚本可以被用来创建那些被误删的约束或者修改约束参数

解析：

这个脚本必须运行在SQL*PLUS下，当脚本执行后，她会提示你输入用户名，和异常表名。脚本会提示用户的异常表名，如果用户下没有异常表，那么直接按下下一步或者.异常表可能会被utlexcpt.sql脚本创建。G_schema_cons.sql脚本可能会在本地创建一个文件，文件中会包含一个或则多个ALTER TABLE ADD CONSTRAINT 语句。脚本用PL/SQL特性，例如游标循环和DBMS_OUTPUT.PUT_LINE函数包存储过程。这个存储过程被用来捕捉约束信息别且写到文件汇中。输出的文件将会以schema_cons_schemaname.lst形式命名在unix系统中。

例如：

执行@$HOME/g_schema_cons脚本，输入对象名 xxx，输入异常exceptions

在输出文件的行中将会有一些限制，是由于PL/SQL的DBMS_OUTPUT.PUT_LINE存储过程造成的。我在脚本中将缓存的最大值设置成1000000。如果有很多表的话和约束的话， 那么这个值可能会太小。如果你遇到一个buffer error 的错误，那就再次运行一下g_tab_cons.sql脚本。这次将会限制单一表的输出。在做这个之前，一定要写一个脚本删除你表上的约束信息。如果一个列被改成 alter table mofify，或者一个列被定义成 NOT NULL 在表被创建的时候，这个脚本将不会修改这些约束。

如果这些约束被加进来，那么NOT NULL 将不会出现在描述属性中，但是哪些NOT NULL 的列的约束将会生效。为了能工作在这种情况下，创建NOT NULL 列的时候用重命名的约束，这些约束将会被脚本重建。

下面这些可以改进脚本的格式

SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please wait...'
SPOOL schema_cons_&puser
DECLARE
CURSOR cons_cur (v_userid VARCHAR2) IS
SELECT * FROM dba_constraints
WHERE owner = v_userid
AND constraint_type in ('P','U','C','R')
ORDER BY constraint_type;
CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
SELECT * FROM dba_cons_columns
WHERE owner = con_owner
AND constraint_name = con_name
ORDER BY position;
CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
SELECT a.*
FROM dba_indexes a, dba_ind_columns b, dba_cons_columns c
WHERE c.constraint_name = con_name
AND a.owner = ind_own
AND b.index_owner = ind_own
AND c.owner = b.index_owner
AND c.position = 1
AND c.table_name = b.table_name
AND c.column_name = b.column_name
AND b.index_name = a.index_name;
col_str VARCHAR2(200);
v_user VARCHAR2(30) := UPPER('&puser');
v_output VARCHAR2(480); -- max of 16 cols at 30 chars each
v_excp NUMBER(1) := 0;
v_excptab VARCHAR2(60) := NULL;
v_delrule VARCHAR2(4);
v_status VARCHAR2(4);
srch_cond VARCHAR2(1000);
v_errcode NUMBER := 0;
v_errmsg varchar2(50) := ' ';
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Prevents buffer exceeded error
BEGIN
v_excptab := UPPER('&pexcp');
IF v_excptab IS NOT NULL THEN
SELECT 1
INTO v_excp
FROM dba_objects
WHERE owner = UPPER('&puser')
AND object_name = UPPER('&pexcp');
v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in the schema: ');
RAISE NO_DATA_FOUND;
GOTO err;
END;
FOR c1 IN cons_cur(v_user) LOOP
begin
srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
-- Dont remove table constraint NOT NULL
IF (instr(srch_cond,'NOT NULL') (instr(srch_cond,'NOT NULL') IS NULL) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(' ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);
IF c1.constraint_type = 'P' THEN v_output := ' PRIMARY KEY (';
ELSIF c1.constraint_type = 'R' THEN v_output := ' FOREIGN KEY (';
ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE (';
ELSIF c1.constraint_type = 'C' THEN
v_output := ' CHECK ('||c1.search_condition||') '||v_excptab;
END IF;
FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
IF c2.position = 1 THEN
v_output := v_output||c2.column_name;
ELSIF c2.position > 1 THEN
v_output := v_output||', '||c2.column_name;
END IF;
END LOOP;
v_output := v_output||')';
DBMS_OUTPUT.PUT_LINE(v_output);
IF c1.constraint_type = 'R' THEN
v_output := NULL;
FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
IF c3.position = 1 THEN
v_output := ' REFERENCES '||c3.owner||'.'||c3.table_name||'(';
v_output := v_output||c3.column_name;
ELSIF c3.position > 1 THEN
v_output := v_output||', '||c3.column_name;
END IF;
END LOOP;
v_output := v_output||')';
DBMS_OUTPUT.PUT_LINE(v_output);
v_delrule := substr(c1.delete_rule,1,2);
IF v_delrule IS NULL THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE
'||v_excptab
|| ')';
END IF;
DBMS_OUTPUT.PUT_LINE(v_output);
END IF;
FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
IF c1.constraint_type in ('P','U') THEN
DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
DBMS_OUTPUT.PUT_LINE(' pctfree '||c4.pct_free);
DBMS_OUTPUT.PUT_LINE(' initrans '||c4.ini_trans);
DBMS_OUTPUT.PUT_LINE(' maxtrans '||c4.max_trans);
DBMS_OUTPUT.PUT_LINE(' tablespace '||c4.tablespace_name);
DBMS_OUTPUT.PUT_LINE(' Storage (');
DBMS_OUTPUT.PUT_LINE(' initial '||c4.initial_extent);
DBMS_OUTPUT.PUT_LINE(' next '||c4.next_extent);
DBMS_OUTPUT.PUT_LINE(' minextents '||c4.min_extents);
DBMS_OUTPUT.PUT_LINE(' maxextents '||c4.max_extents);
DBMS_OUTPUT.PUT_LINE(' pctincrease '||c4.pct_increase||') '||
v_excptab ||')');
END IF;
END LOOP;
v_output := NULL;
v_status := substr(c1.status,1,1);
IF v_status = 'E' THEN
v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
ELSIF v_status = 'D' THEN
v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
END IF;
DBMS_OUTPUT.PUT_LINE('/ ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
DBMS_OUTPUT.PUT_LINE(' ');
END;
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN others THEN
v_errcode := sqlcode;
v_errmsg := SUBSTR(sqlerrm, 1, 50);
DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
DBMS_OUTPUT.PUT_LINE(c1.search_condition);
END;
END LOOP;
NULL;
END;
/
SPOOL off


打印脚本，并将脚本执行的结果保存在本地的文件中

创建g_tab_cons.sql脚本

创建一个完全的表约束信息在一个输入文件中。这个脚本的目的就是创建一个文件，该文件中包含用户在指定的表上创建的所有约束信息。这儿写输入脚本可能会被用来创建那些被误删的约束或者修改那些约束的参数信息

解析：

这个脚本必须运行在SQL*PLUS下，当脚本执行后，她会提示你输入用户名，和异常表名。脚本会提示用户的异常表名，如果用户下没有异常表，那么直接按下下一步或者.异常表可能会被utlexcpt.sql脚本创建。G_schema_cons.sql脚本可能会在本地创建一个文件，文件中会包含一个或则多个ALTER TABLE ADD CONSTRAINT 语句。脚本用PL/SQL特性，例如游标循环和DBMS_OUTPUT.PUT_LINE函数包存储过程。这个存储过程被用来捕捉约束信息别且写到文件汇中。输出的文件将会以schema_cons_schemaname.lst形式命名在unix系统中。

例如：

执行@$HOME/g_tab_cons脚本，输入对象名 xxx，输入表名 xxx，输入异常exceptions

在输出文件的行中将会有一些限制，是由于PL/SQL的DBMS_OUTPUT.PUT_LINE存储过程造成的。我在脚本中将缓存的最大值设置成1000000。如果有很多表的话和约束的话， 那么这个值可能会太小。如果你遇到一个buffer error 的错误，那就再次运行一下g_tab_cons.sql脚本。这次将会限制单一表的输出。在做这个之前，一定要写一个脚本删除你表上的约束信息。如果一个列被改成 alter table mofify，或者一个列被定义成 NOT NULL 在表被创建的时候，这个脚本将不会修改这些约束。

如果这些约束被加进来，那么NOT NULL 将不会出现在描述属性中，但是哪些NOT NULL 的列的约束将会生效。为了能工作在这种情况下，创建NOT NULL 列的时候用重命名的约束，这些约束将会被脚本重建。

执行下面的代码改进输出格式

SET ARRAYSIZE 1
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
SET FEEDBACK off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT ptab PROMPT 'Enter the table name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please be patient...'
SPOOL tab_cons_&ptab
DECLARE
CURSOR cons_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS
SELECT * FROM sys.dba_constraints
WHERE owner = v_userid
AND constraint_type in ('P','U','C','R')
AND table_name = v_tabname
ORDER BY constraint_type;
CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
SELECT * FROM sys.dba_cons_columns
WHERE owner = con_owner
AND constraint_name = con_name
ORDER BY position;
CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
SELECT a.*
FROM sys.dba_indexes a, sys.dba_ind_columns b, sys.dba_cons_columns c
WHERE c.constraint_name = con_name
AND a.owner = ind_own
AND b.index_owner = ind_own
AND c.owner = b.index_owner
AND c.position = 1
AND c.table_name = b.table_name
AND c.column_name = b.column_name
AND b.index_name = a.index_name;
col_str VARCHAR2(200);
v_user VARCHAR2(30) := UPPER('&puser');
v_tabname VARCHAR2(30) := UPPER('&ptab');
v_output VARCHAR2(480); -- max of 16 cols at 30 chars each
v_dummy NUMBER := 0;
v_delrule VARCHAR2(4);
v_status VARCHAR2(4);
v_excp NUMBER(1) := 0;
v_excptab VARCHAR2(60) := NULL;
srch_cond VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Prevents buffer exceeded error
SELECT 1 -- Check to see if the table exists
INTO v_dummy
FROM dba_tables
WHERE table_name = v_tabname
AND owner = v_user;
BEGIN
v_excptab := UPPER('&pexcp');
IF v_excptab IS NOT NULL THEN
SELECT 1
INTO v_excp
FROM sys.dba_objects
WHERE owner = UPPER('&puser')
AND object_name = UPPER('&pexcp');
v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in your schema: ');
RAISE NO_DATA_FOUND;
GOTO err;
END;
FOR c1 IN cons_cur(v_user, v_tabname) LOOP
begin
srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
-- Dont remove table constraint NOT NULL
IF (instr(srch_cond,'NOT NULL') (instr(srch_cond,'NOT NULL') IS NULL) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(' ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);
IF c1.constraint_type = 'P' THEN v_output := ' PRIMARY KEY (';
ELSIF c1.constraint_type = 'R' THEN v_output := ' FOREIGN KEY (';
ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE (';
ELSIF c1.constraint_type = 'C' THEN
v_output := ' CHECK ('||c1.search_condition||') '||v_excptab;
END IF;
FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
IF c2.position = 1 THEN
v_output := v_output||c2.column_name;
ELSIF c2.position > 1 THEN
v_output := v_output||', '||c2.column_name;
END IF;
END LOOP;
v_output := v_output ||')';
DBMS_OUTPUT.PUT_LINE(v_output);
IF c1.constraint_type = 'R' THEN
v_output := NULL;
FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
IF c3.position = 1 THEN
v_output := ' REFERENCES '||c3.owner||'.'||c3.table_name||'(';
v_output := v_output||c3.column_name;
ELSIF c3.position > 1 THEN
v_output := v_output||', '||c3.column_name;
END IF;
END LOOP;
v_output := v_output||') ';
DBMS_OUTPUT.PUT_LINE(v_output);
v_delrule := substr(c1.delete_rule,1,2);
IF v_delrule IS NULL THEN v_output := v_excptab ||' )';
ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )';
ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE
'||v_excptab || ')';
END IF;
DBMS_OUTPUT.PUT_LINE(v_output);
END IF;
FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
IF c1.constraint_type in ('P','U') THEN
DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
DBMS_OUTPUT.PUT_LINE(' pctfree '||c4.pct_free);
DBMS_OUTPUT.PUT_LINE(' initrans '||c4.ini_trans);
DBMS_OUTPUT.PUT_LINE(' maxtrans '||c4.max_trans);
DBMS_OUTPUT.PUT_LINE(' tablespace '||c4.tablespace_name);
DBMS_OUTPUT.PUT_LINE(' Storage (');
DBMS_OUTPUT.PUT_LINE(' initial '||c4.initial_extent);
DBMS_OUTPUT.PUT_LINE(' next '||c4.next_extent);
DBMS_OUTPUT.PUT_LINE(' minextents '||c4.min_extents);
DBMS_OUTPUT.PUT_LINE(' maxextents '||c4.max_extents);
DBMS_OUTPUT.PUT_LINE(' pctincrease '||c4.pct_increase||') '||
v_excptab ||')');
END IF;
END LOOP;
v_output := NULL;
v_status := substr(c1.status,1,1);
IF v_status = 'E' THEN
v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
ELSIF v_status = 'D' THEN
v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
END IF;
DBMS_OUTPUT.PUT_LINE('/ ');
DBMS_OUTPUT.PUT_LINE(v_output);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
DBMS_OUTPUT.PUT_LINE(' ');
END;
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60));
DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
DBMS_OUTPUT.PUT_LINE(c1.search_condition);
END;
END LOOP;
NULL;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('This table: '||v_tabname||', Does not exist or has no
constraints!');
END;
/
SPOOL off


打印脚本，并将脚本执行的结果保存在本地的文件中

下面就是一些g_schema_cons.sql和g_tab_cons.sql脚本输出结果的例子

这是个关于schema_cons_ernie.lst的输出内容

ALTER TABLE ERNIE.NEWEMP
ADD (CONSTRAINT NEWEMP_SALARY_CK
CHECK (salary >= 0) EXCEPTIONS INTO exceptions)
/
REM This constraint NEWEMP_SALARY_CK was ENABLED
--------------------------------------------
ALTER TABLE ERNIE.DEPT
ADD (CONSTRAINT DEPT_DEPTNO_PK
PRIMARY KEY (DEPTNO)
USING INDEX
pctfree 10
initrans 2
maxtrans 255
tablespace SYSTEM
Storage (
initial 10240
next 10240
minextents 1
maxextents 121
pctincrease 50) EXCEPTIONS INTO exceptions)
/
REM This constraint DEPT_DEPTNO_PK was ENABLED
--------------------------------------------
ALTER TABLE ERNIE.NEWEMP
ADD (CONSTRAINT NEWEMP_ID_PK
PRIMARY KEY (ID)
USING INDEX
pctfree 5
initrans 2
maxtrans 255
tablespace TOOLS
Storage (
initial 51200
next 51200
minextents 1
maxextents 121
pctincrease 0) EXCEPTIONS INTO exceptions)
/
REM This constraint NEWEMP_ID_PK was ENABLED
--------------------------------------------
ALTER TABLE ERNIE.NEWEMP
ADD (CONSTRAINT NEWEMP_DEPTNO_FK
FOREIGN KEY (DEPTNO)
REFERENCES ERNIE.DEPT(DEPTNO)
ON DELETE CASCADE EXCEPTIONS INTO exceptions)
/
REM This constraint NEWEMP_DEPTNO_FK was ENABLED
--------------------------------------------
ALTER TABLE ERNIE.NEWEMP
ADD (CONSTRAINT NEWEMP_MANAGER_ID_FK
FOREIGN KEY (MANAGER_ID)
REFERENCES ERNIE.NEWEMP(ID)
EXCEPTIONS INTO exceptions )
/
REM This constraint NEWEMP_MANAGER_ID_FK was ENABLED
--------------------------------------------
ALTER TABLE ERNIE.NEWEMP
ADD (CONSTRAINT NEWEMP_USERID_UK
UNIQUE (USERID)
USING INDEX
pctfree 5
initrans 2
maxtrans 255
tablespace TOOLS
Storage (
initial 51200
next 51200
minextents 1
maxextents 121
pctincrease 0) EXCEPTIONS INTO exceptions)
/
REM This constraint NEWEMP_USERID_UK was ENABLED


这是个关于tab_cons_chktab.lst输出的内容

ALTER TABLE CBLAKEY.CHKTAB
ADD (CONSTRAINT CHK_COL1_VAL
CHECK ( col1 = TRANSLATE(
col1,'*`~!@#$%^&()-_=+[{]};:"\|/?.>,','!********************************
*') ) EXCEPTIONS INTO exceptions)
/
REM This constraint CHK_COL1_VAL was ENABLED
--------------------------------------------
ALTER TABLE CBLAKEY.CHKTAB
ADD (CONSTRAINT CHKTAB_COL_PK
PRIMARY KEY (COL1, COL2)
USING INDEX
pctfree 10
initrans 2
maxtrans 255
tablespace USERS
Storage (
initial 10240
next 10240
minextents 1
maxextents 121
pctincrease 50) EXCEPTIONS INTO exceptions)
/


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com