문제 설명
Oracle ‑ 커서를 기반으로 테이블 삭제를 위한 동적 함수 생성 (Oracle‑ creating dynamic function for deleting tables based on cursor)
삭제하고 다시 생성해야 하는 모든 테이블에 대해 커서를 사용하여 Oracle에서 동적 함수를 빌드하려고 합니다. 예를 들어, 다음 예제 테이블 구조가 있습니다.
CREATE TABLE All_tmp_DATA AS
(SELECT 'T_tmp_test1' As Table_NM, 'TEST1' As Process_name FROM DUAL UNION ALL
SELECT 'T_tmp_test2' As Table_NM, 'TEST1' As Process_name FROM DUAL UNION ALL
SELECT 'T_tmp_test3' As Table_NM, 'TEST1' As Process_name FROM DUAL)
위의 "T_tmp"로 시작하는 테이블은 TEST1 프로세스를 시작할 때 카운트가 >1인 경우 삭제해야 하는 데이터베이스의 모든 테이블을 나타냅니다. . "TEST1"을 입력할 수 있는 Process_name 매개변수를 전달하고 All_tmp_DATA에서 Table_NM에 바인딩하고 다음 코드에서 table_name에 삽입하여 커서를 사용하여 루프를 빌드하는 함수가 정말 필요합니다.
BEGIN
SELECT count(*)
INTO l_cnt
FROM user_tables
WHERE table_name = 'MY_TABLE';
IF l_cnt = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE my_table';
END IF;
END;
참조 솔루션
방법 1:
In the beginning, I'd suggest you not to use mixed case when naming Oracle objects.
Test case:
SQL> select * From all_tmp_data;
TABLE_NM PROCE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑
T_tmp_test1 TEST1
T_tmp_test2 TEST1
T_tmp_test3 TEST1
SQL> create table "T_tmp_test1" as select * From dept;
Table created.
SQL> ‑‑ I don't have "T_tmp_test2"
SQL> create table "T_tmp_test3" as select * From emp;
Table created.
SQL>
SQL> select table_name From user_Tables where upper(table_name) like 'T_TMP%';
TABLE_NAME
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
T_tmp_test3
T_tmp_test1
Procedure which drops tables contained in ALL_TMP_DATA:
- as opposed to your code, I concatenated table name with
DROP - as you use table names with mixed case, you have to enclose their names into double quotes, always (did I say not do use that?)
As the final select shows, those tables don't exist any more.
SQL> declare
2 l_cnt number;
3 begin
4 for cur_r in (select table_nm from all_tmp_data) loop
5 select count(*) into l_cnt
6 from user_tables
7 where table_name = cur_r.table_nm;
8
9 if l_cnt > 0 then
10 execute immediate ('drop table "' || cur_r.table_nm || '"');
11 end if;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select table_name From user_Tables where upper(table_name) like 'T_TMP%';
no rows selected
SQL>
As of the process column: I have no idea what is it used for so I did exactly that ‑ didn't use it.
방법 2:
You can use the exception handling to handle such scenario directly as follows:
DECLARE
TABLE_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT ( TABLE_DOES_NOT_EXIST, ‑00942 );
BEGIN
FOR CUR_R IN (
SELECT TABLE_NM
FROM ALL_TMP_DATA
) LOOP
BEGIN
EXECUTE IMMEDIATE 'drop table "' || cur_r.table_nm || '"';
DBMS_OUTPUT.PUT_LINE('"' || cur_r.table_nm || '" table dropped.');
EXCEPTION
WHEN TABLE_DOES_NOT_EXIST THEN
DBMS_OUTPUT.PUT_LINE('"' || cur_r.table_nm || '" table does not exists');
END;
END LOOP;
END;
/
(by Yen Ching、Littlefoot、Popeye)