时间戳

首页 > 解决方案 > Oracle——创建基于游标删除表的动态函数

sql - Oracle——创建基于游标删除表的动态函数

问题描述

我正在尝试使用游标在 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则需要删除的表。我真的需要一个函数来传递参数 Process_name，我可以在其中输入“TEST1”，并使用游标通过将其绑定到来自 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;

标签： sqloracleddldml

解决方案

您可以使用异常处理直接处理这种情况，如下所示：

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;
/

推荐阅读