create or replace procedure sp_to_test()
returns varchar
language sql
as
$$
declare
var_dn VARCHAR2(6200);
tmp_str integer;
prc_nm varchar2(100);
begin
select count(1) into tmp_str from information_schema.tables where table_name ='TMP_TBL_TO_TEST';
IF (tmp_str = 1) then
var_dn:='Drop Table TMP_TBL_TO_TEST';
execute immediate var_dn;
END IF;
INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date)
VALUES (seq_val.nextval, 'sp_to_test', var_dn, SYSDATE());
commit;
var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date)
VALUES (seq_val.nextval, 'sp_to_test', var_dn, SYSDATE());
commit;
execute immediate var_dn;
end ;
$$
;
当我在Snowflake中运行此过程时,我得到了以下错误-第23行位置8处类型为"STATEMENT_error"的未捕获异常:SQL编译错误:第2行位置77处的错误标识符"var_dn"无效
不确定我做错了什么。请指导解决该问题。
前两个状态可以重写:
select count(1) into tmp_str
from information_schema.tables
where table_name ='TMP_TBL_TO_TEST';
IF (tmp_str = 1) then
var_dn:='Drop Table TMP_TBL_TO_TEST';
execute immediate var_dn;
END IF;
很简单:
DROP TABLE IF NOT EXISTS TMP_TBL_TO_TEST;
第二:
var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date)
VALUES (seq_val.nextval, 'sp_to_test', var_dn, SYSDATE());
应为(通过SQL语句访问的变量应以:
为前缀(:
var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date)
VALUES (seq_val.nextval, 'sp_to_test', :var_dn, SYSDATE());