Oracle删除表主键语句

删除无名主键语句：

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = '表名';
ALTER TABLE 表名 DROP CONSTRAINT 主键名称;

上面语句需要手动，如何自动执行？看下面：

CREATE OR REPLACE FUNCTION DEL_TABLE_CONSTRAINT (tb_name       VARCHAR2,
                                                 field_name    VARCHAR2)
   RETURN NUMBER
IS
   con_count   NUMBER;
   con_name    VARCHAR2 (32);
BEGIN
   con_name := '';
   con_count := 0;

   SELECT COUNT (DISTINCT constraint_name)
     INTO con_count
     FROM user_cons_columns
    WHERE     table_name = UPPER (tb_name)
          AND column_name = UPPER (field_name)
          AND ROWNUM = 1;

   IF con_count > 0
   THEN
      SELECT DISTINCT constraint_name
        INTO con_name
        FROM user_cons_columns
       WHERE     table_name = UPPER (tb_name)
             AND column_name = UPPER (field_name)
             AND ROWNUM = 1;

      EXECUTE IMMEDIATE
         'alter table ' || tb_name || ' drop constraint ' || con_name;
   END IF;

   RETURN 0;
END;

这样执行

DECLARE
   a   NUMBER;
BEGIN
   a := DEL_TABLE_CONSTRAINT ('表名', '字段名');
END;
/

 

转载于:https://www.cnblogs.com/mygod/p/5790172.html