学步园

create or replace procedure P_CHANGE_FK_STATUS(IN_TABLE in varchar2,IN_TYPE IN VARCHAR2,IN_ACTION IN VARCHAR2) is
--失效或者生效指定表的外键
-- IN_table :表名
-- IN_TYPE :必须为 R 代表外键
-- IN_ACTION :DISABLE 或者 ENABLE
V_TABLE VARCHAR2(30);
V_ACTION VARCHAR2(10);
V_SQL VARCHAR2(200) ;
V_FK  VARCHAR2(60);  
begin 
      V_TABLE:=substr(IN_TABLE,2,length(IN_TABLE)-2);
      V_ACTION:=substr(IN_ACTION,2,length(IN_ACTION)-2);
      --查询外键名称
      V_SQL :='SELECT constraint_name FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE=  '|| IN_TYPE||  '    and  table_name='||IN_TABLE;
      DBMS_OUTPUT.PUT_LINE('V_SQL: ' || V_SQL);
      execute immediate V_SQL into V_FK;
      v_sql:='alter table '||  V_TABLE  ||'  '||V_ACTION ||'  constraint '||V_FK;  
      DBMS_OUTPUT.PUT_LINE('V_SQL: ' || V_SQL);
      execute immediate  v_sql;
end P_CHANGE_FK_STATUS;

 

 执行