变量v_txdate NUMBER(8);由输入参数v_indate转化而来
v_txdate :=TO_CHAR(TO_DATE(v_indate,'YYYYMMDD'),'YYYYMMDD');存储过程中,有如下建表的动态sql
EXECUTE IMMEDIATE '
CREATE GLOBAL TEMPORARY TABLE TMP_TABLE
ON COMMIT PRESERVE ROWS AS (
SELECT
........
,vx_date AS BIZ_DATE //这里希望把输入日期写入字段,并插入到建的临时表中
........
FROM TableA T1
)'; 但显然这样是报错的,请问该如何用?
v_txdate :=TO_CHAR(TO_DATE(v_indate,'YYYYMMDD'),'YYYYMMDD');存储过程中,有如下建表的动态sql
EXECUTE IMMEDIATE '
CREATE GLOBAL TEMPORARY TABLE TMP_TABLE
ON COMMIT PRESERVE ROWS AS (
SELECT
........
,vx_date AS BIZ_DATE //这里希望把输入日期写入字段,并插入到建的临时表中
........
FROM TableA T1
)'; 但显然这样是报错的,请问该如何用?
EXECUTE IMMEDIATE '
CREATE GLOBAL TEMPORARY TABLE TMP_TABLE
ON COMMIT PRESERVE ROWS AS (
SELECT
........
,to_date('''||vx_date||''',''yyyy-mm-dd) AS BIZ_DATE
........
FROM TableA T1
)';
,to_date('''||vx_date||''',''yyyy-mm-dd'') AS BIZ_DATE
不转换的话,也要用3个单引号包住?
CREATE GLOBAL TEMPORARY TABLE TMP_TABLE
ON COMMIT PRESERVE ROWS AS (
SELECT
........
,'||vx_date||' AS BIZ_DATE
........
FROM TableA T1
)';
或者用using 关键字 ,
execute immediate '... :param1 ' using v_date;