в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:57Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Table/Drop Table - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия