oracle 动态SQL 注意细节 ORA-00911: 无效字符

最新推荐文章于 2022-12-02 16:04:32 发布

最新推荐文章于 2022-12-02 16:04:32 发布 · 129 阅读

文章标签：

本文详细解析了SQL动态语句的结构与执行过程，包括变量引用、日期时间操作和参数化查询的关键细节，旨在帮助开发者理解并有效利用SQL动态语句进行复杂数据操作。

lv_sql:=' 
insert into ETL_SUCESS_AMOUNT 
select SEQ_OS_ETL_AMOUNTID.NEXTVAL AS AMOUNTID,1,AMOUNT_DATA,AMOUNT_HOUR, 
serviceid,portalid,mouduleid,actionid,RESERVE1_ID,RESERVE2_ID,RESERVE3_ID, 
RESERVE4_ID,AMOUNT,AMOUNT_TIME 
from 
( 
select 
trunc(INSERTTIME) AS AMOUNT_DATA, 
TO_CHAR(INSERTTIME,''HH24'') AS AMOUNT_HOUR, 
serviceid,portalid,mouduleid,actionid, 
NULL AS RESERVE1_ID, 
NULL AS RESERVE2_ID, 
NULL AS RESERVE3_ID, 
NULL AS RESERVE4_ID, 
count(*) AS AMOUNT, 
SYSDATE AS AMOUNT_TIME 
from '||lv_table_name||' 
where inserttime > to_date(:1,''yyyy-mm-dd hh24:mi:ss'') 
and inserttime < to_date(:2,''yyyy-mm-dd hh24:mi:ss'') 
group by trunc(INSERTTIME),TO_CHAR(INSERTTIME,''HH24''),serviceid,mouduleid,portalid,actionid 
)'; 
execute immediate lv_sql USING p_sdate,p_edate; 
-- 细节动态语句里面不需要分号

--细节(''HH24'') 动态语句里面单引号要双写

--细节 from '||lv_table_name||' 语句之间注意空格符号

--细节使用参数:1 USING p_sdate,p_edate;