神通数据库 SQL 异常捕获

例如 select into 当 赋值 > 0 或者 = 0 抛出异常

DECLARE
_name TEXT;
_sql TEXT;
BEGIN
    LOOP
        BEGIN
            SELECT VIEW_NAME INTO _name 
            FROM (SELECT TOP 1 CONCAT(NS.NSPNAME, '.', CS.RELNAME) AS VIEW_NAME 
                FROM (SYS_CLASS CS JOIN SYS_NAMESPACE NS ON CS.RELNAMESPACE = NS.OID AND CS.RELKIND = 'v')
                WHERE HAS_ANY_TABLE_PRIVILEGE((CURRENT_USER_ID())::integer, CS.OID, FALSE, CS.RELOWNER, CS.RELACL)
                AND NS.NSPNAME NOT IN ('INFO_SCHEM', 'REPLICATION', 'DIRECTORIES', 'SYS_GLOBAL_TEMP', 'SYSFTSDBA', 'SYSAUDIT', 'SYSSECURE', 'STAGENT', 'PUBLIC')
                ORDER BY NS.NSPNAME, CS.RELNAME);
            _sql := 'DROP VIEW ' + _name + ' CASCADE';
            EXECUTE IMMEDIATE _sql;
            EXCEPTION WHEN OTHERS THEN EXIT;
        END;
    END LOOP;
END;

需要注意: 循环不能直接镶嵌 EXCEPTION , 需要用 begin ... end 包裹.

当遇到异常, 直接跳转到异常 then 语句

以下两个例子说明

例一 (不抛出异常)

例二 (抛出异常)

SQL

declare                                                     
    n int;                                                  
BEGIN                                                       
    n = 10/0;      
  dbms_output.put_line('good wleleaa'); 
    exception when others then                              
        dbms_output.put_line('good morning');                                    
END;