ORA-00942: 表或視圖不存在

-- 靜態(tài)SQL（編譯時(shí)檢查）
CREATE OR REPLACE PROCEDURE static_example IS
BEGIN
  SELECT * FROM non_existing_table;  -- 編譯時(shí)報(bào)錯(cuò)
END;

-- 動(dòng)態(tài)SQL（運(yùn)行時(shí)檢查）
CREATE OR REPLACE PROCEDURE dynamic_example IS
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM non_existing_table'; -- 運(yùn)行時(shí)才報(bào)錯(cuò)
END;

SELECT object_name, status 
FROM user_objects 
WHERE object_name = UPPER('static_example') AND object_type = 'PROCEDURE';

SELECT line, position, text 
FROM user_errors 
WHERE name = UPPER('static_example')
ORDER BY line;

-- 檢查權(quán)限
SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = '目標(biāo)表名';

-- 常見需要兩種權(quán)限
GRANT SELECT ON 表名 TO 用戶名;     -- 查詢權(quán)限
GRANT REFERENCES ON 表名 TO 用戶名; -- 引用權(quán)限

-- 錯(cuò)誤方式（假設(shè)表在HR schema中）
CREATE OR REPLACE PROCEDURE example IS
BEGIN
  SELECT * FROM employees; -- 報(bào)錯(cuò)
END;

-- 正確方式
CREATE OR REPLACE PROCEDURE example IS
BEGIN
  SELECT * FROM HR.employees; -- 指定schema
END;

CREATE OR REPLACE PROCEDURE get_sales(p_month VARCHAR2) IS
  v_count NUMBER;
BEGIN
  -- 靜態(tài)引用會(huì)導(dǎo)致編譯錯(cuò)誤
  SELECT COUNT(*) INTO v_count FROM SALES_||p_month;
END;

CREATE OR REPLACE PROCEDURE get_sales(p_month VARCHAR2) IS
  v_count NUMBER;
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM SALES_'||p_month;
  
  -- 先檢查表是否存在
  BEGIN
    EXECUTE IMMEDIATE 'SELECT 1 FROM SALES_'||p_month||' WHERE ROWNUM = 1';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001, '表 SALES_'||p_month||' 不存在');
  END;
  
  -- 執(zhí)行查詢
  EXECUTE IMMEDIATE v_sql INTO v_count;
  
  DBMS_OUTPUT.PUT_LINE('記錄數(shù): '||v_count);
END;

-- 用戶A執(zhí)行
CREATE TABLE important_data (id NUMBER);
INSERT INTO important_data VALUES (1);
GRANT SELECT ON important_data TO userB;

-- 用戶B執(zhí)行（會(huì)失?。?
CREATE OR REPLACE PROCEDURE process_data IS
  v_id NUMBER;
BEGIN
  SELECT id INTO v_id FROM important_data;
END;

-- 解決方案：用戶A需要額外授予REFERENCES權(quán)限
GRANT REFERENCES ON important_data TO userB;

SELECT * FROM ALL_TABLES 
WHERE OWNER = USER AND TABLE_NAME = '表名';

SELECT * FROM USER_TAB_PRIVS 
WHERE TABLE_NAME = '表名';

BEGIN
  EXECUTE IMMEDIATE 'SELECT 1 FROM 表名 WHERE ROWNUM = 1';
  DBMS_OUTPUT.PUT_LINE('表可訪問');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('錯(cuò)誤: '||SQLERRM);
END;

SELECT * FROM ALL_SYNONYMS 
WHERE TABLE_NAME = '表名';

EXECUTE IMMEDIATE 'SELECT...FROM '||動(dòng)態(tài)表名||'...';

CREATE VIEW all_sales AS
SELECT * FROM sales_202301 UNION ALL
SELECT * FROM sales_202302 UNION ALL
...

BEGIN
  -- 嘗試訪問表
EXCEPTION
  WHEN OTHERS THEN
    IF SQLERRM LIKE '%ORA-00942%' THEN
      -- 處理表不存在的情況
    END IF;
END;

CREATE OR REPLACE PROCEDURE example 
AUTHID CURRENT_USER IS
BEGIN
  -- 使用調(diào)用者權(quán)限
END;