不同模式下删除Oracle数据表的三个实例

DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];
复制

CREATE TABLE persons (
    person_id NUMBER,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL, 
    PRIMARY KEY(person_id)
);
复制

DROP TABLE persons;
复制

--表1：汽车品牌
CREATE TABLE brands(
    brand_id NUMBER PRIMARY KEY,
    brand_name varchar2(50)
);
--表2：汽车
CREATE TABLE cars(
    car_id NUMBER PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year NUMBER NOT NULL,
    plate_number VARCHAR(25),
    brand_id NUMBER NOT NULL,


    CONSTRAINT fk_brand 
    FOREIGN KEY (brand_id) 
    REFERENCES brands(brand_id) ON DELETE CASCADE
);
复制

DROP TABLE brands;
复制

SELECT
    a.table_name,
    a.column_name,
    a.constraint_name,
    c.owner,
    c.r_owner,
    c_pk.table_name r_table_name,
    c_pk.constraint_name r_pk
FROM
    all_cons_columns a
JOIN all_constraints c ON
    a.owner = c.owner
    AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON
    c.r_owner = c_pk.owner
    AND c.r_constraint_name = c_pk.constraint_name
WHERE
    c.constraint_type = 'R'
    AND a.table_name = 'CARS';
复制

DROP TABLE brands CASCADE CONSTRAINTS;
复制

DROP TABLE cars purge;
复制

--新建测试表
CREATE TABLE test_1(c1 VARCHAR2(50));
CREATE TABLE test_2(c1 VARCHAR2(50));
CREATE TABLE test_3(c1 VARCHAR2(50));
复制

BEGIN
  FOR rec IN
    (
      SELECT
        table_name
      FROM
        all_tables
      WHERE
        table_name LIKE 'TEST_%'
    )
  LOOP
    EXECUTE immediate 'DROP TABLE '||rec.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/    -- 该符号表示执行这段PL/SQL代码
复制