Oracle PL/SQL Tutorial/Table/Drop Table
Содержание
Drop only if table exists.
<source lang="sql">
SQL> BEGIN
2 3 FOR i IN (SELECT table_name FROM user_tables WHERE table_name = "SHARED_ALL") LOOP 4 EXECUTE IMMEDIATE "DROP TABLE shared_all"; 5 END LOOP; 6 7 END; 8 /
PL/SQL procedure successfully completed. SQL></source>
Dropping a Table
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 /
ID VALUE
----------
1 9
2 2.11
3 3.44
4 -4.21
5 10
6 3
7 -5.88
8 123.45
9 98.23
9 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
DROP TABLE with CASCADE CONSTRAINTS
<source lang="sql">
SQL> SQL> CREATE TABLE ONSITE_SCHEDULE (
2 SALESMAN_ID NUMBER, 3 VISIT_DATE DATE, 4 COMPANY_NAME VARCHAR2(50), 5 COMPANY_URL VARCHAR2(200) ) ;
Table created. SQL> SQL> DROP TABLE ONSITE_SCHEDULE CASCADE CONSTRAINTS ; Table dropped. SQL></source>
ORA-04091: table JAVA2S.DETAIL_TABLE is mutating, trigger/function may not see it
<source lang="sql">
SQL> SQL> CREATE TABLE master_table
2 (master_id NUMBER NOT NULL PRIMARY KEY);
Table created. SQL> SQL> CREATE TABLE detail_table
2 (detail_id NUMBER NOT NULL, 3 master_id NUMBER NOT NULL, 4 CONSTRAINT detail_to_emp 5 FOREIGN KEY (master_id) 6 REFERENCES master_table (master_id) 7 ON DELETE CASCADE);
Table created. SQL> SQL> CREATE OR REPLACE TRIGGER after_delete_master
2 AFTER DELETE ON master_table 3 FOR EACH ROW 4 DECLARE 5 CURSOR curs_count_detail IS 6 SELECT COUNT(*) 7 FROM detail_table; 8 v_detail_count NUMBER; 9 BEGIN 10 OPEN curs_count_detail; 11 FETCH curs_count_detail INTO v_detail_count; 12 CLOSE curs_count_detail; 13 END; 14 /
Trigger created. SQL> SQL> BEGIN
2 FOR master_counter IN 1..10 LOOP 3 INSERT INTO master_table 4 VALUES(master_counter); 5 FOR detail_counter IN 1..2 LOOP 6 INSERT INTO detail_table 7 VALUES(detail_counter, 8 master_counter); 9 END LOOP; 10 END LOOP; 11 END; 12 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT * FROM master_table;
MASTER_ID
1
2
3
4
5
6
7
8
9
10
10 rows selected. SQL> SQL> SELECT * FROM detail_table;
DETAIL_ID MASTER_ID
----------
1 1
2 1
1 2
2 2
1 3
2 3
1 4
2 4
1 5
2 5
1 6
DETAIL_ID MASTER_ID
----------
2 6
1 7
2 7
1 8
2 8
1 9
2 9
1 10
2 10
20 rows selected. SQL> SQL> DELETE master_table; DELETE master_table
*
ERROR at line 1: ORA-04091: table sqle.DETAIL_TABLE is mutating, trigger/function may not see it ORA-06512: at "sqle.AFTER_DELETE_MASTER", line 3 ORA-06512: at "sqle.AFTER_DELETE_MASTER", line 7 ORA-04088: error during execution of trigger "sqle.AFTER_DELETE_MASTER"
SQL> SQL> SELECT * FROM master_table;
MASTER_ID
1
2
3
4
5
6
7
8
9
10
10 rows selected. SQL> SQL> SELECT * FROM detail_table;
DETAIL_ID MASTER_ID
----------
1 1
2 1
1 2
2 2
1 3
2 3
1 4
2 4
1 5
2 5
1 6
DETAIL_ID MASTER_ID
----------
2 6
1 7
2 7
1 8
2 8
1 9
2 9
1 10
2 10
20 rows selected. SQL> SQL> DROP TABLE detail_table; Table dropped. SQL> DROP TABLE master_table; Table dropped. SQL> SQL> SQL></source>
