SQL检查表是否存在并截断 [英] SQL check if table exist and truncate

问题描述

我正在尝试编写一条语句，以检查表是否存在以及是否确实将其截断/删除.如果不存在，则打印消息此表不存在！"

I'm trying to code a statement that checks if a table exist and if it does to truncate/delete it. If it doesn't exist, to print the message 'This table does not exist!'

这是我到目前为止提出的，但是似乎没有用.

This is what I've come up so far but doesn't seem to work.

BEGIN
         TRUNCATE TABLE PPA_P6_2018;
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -942 THEN
                DBMS_OUTPUT.put_line('This table does not exist!');
            ELSE
                RAISE;
                DBMS_OUTPUT.put_line('This table has been delted!');
            END IF;
    END;

推荐答案

DECLARE
    v_count        NUMBER;
    v_table_name   VARCHAR2 (100) := 'YOURTABLE';
    v_sqlstr       VARCHAR2 (1000);
BEGIN
    SELECT   COUNT ( * )
      INTO   v_count
      FROM   user_tables
     WHERE   table_name = v_table_name;

    IF v_count > 0
    THEN
        v_sqlstr := 'DELETE FROM ' || v_table_name;

        EXECUTE IMMEDIATE v_sqlstr;

        DBMS_OUTPUT.put_line (
            'TABLE ' || v_table_name || ' HAS BEEN DELETED!');
    ELSE
        DBMS_OUTPUT.put_line ('TABLE ' || v_table_name || ' DOES NOT EXIST!');
    END IF;
END;

这篇关于SQL检查表是否存在并截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！