pl/sql过程错误:无法使用绑定变量
pl/sql过程错误:无法使用绑定变量
提问于 2012-02-04 04:49:43
我已经搜索了各种论坛和站点，我认为我在代码中所犯的错误是没有使用绑定变量。不确定这是否是唯一的出路，但经过大量研究，我认为可能就是这样。
下面是我使用的代码：
create or replace
PROCEDURE CUBE_VIEW (Var_dt varchar2 default '')
AUTHID CURRENT_USER
IS
Start_Date number;
End_Date number;
Var Date;
BEGIN
If Var_dt is null then
Var := SYSDATE;
Else
Var := to_date(Var_dt);
end if;
select TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((select ADD_MONTHS((select TO_DATE(Var,'DD-
MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(Var,'DD-MON-YY')+1,'D'))) from dual),-34) from dual),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) into Start_Date from dual;
select TO_NUMBER(TO_CHAR(TRUNC(TO_DATE((select ADD_MONTHS((select TO_DATE(Var,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(Var,'DD-MON-YY')+1,'D'))) from dual),-1) from dual),'DD-MON-YY'),'MM')-1,'YYMMDD')) into End_Date from dual;
DBMS_OUTPUT.PUT_LINE('Start Date is ' || Start_Date);
DBMS_OUTPUT.PUT_LINE('End Date is ' || End_Date);
execute immediate 'CREATE OR REPLACE VIEW F_SURVEY_YESTERDAY AS SELECT FLIGHTDATE FROM F_SURVEY_MULTICOL WHERE FLIGHTDATE BETWEEN Start_Date AND End_Date';
execute immediate 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN Start_Date AND End_Date';
end CUBE_VIEW;代码成功地符合，但是当我执行代码时，这里是我得到的错误。
错误：
*Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 17
ORA-06512: at line 6
Start Date is 90301
End Date is 111231
Process exited.
Disconnecting from the database LocalEnvironment.*当我硬编码这些值时，它工作得很好，但是当我从过程中生成值时，它就中断了。有解决办法吗？
create or replace
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
VAR:= SYSDATE;
ELSE
VAR:= TO_DATE(VAR_DT);
END IF;
SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-34) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO START_DATE FROM DUAL;
SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-1) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO END_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE('START DATE IS ' || START_DATE);
DBMS_OUTPUT.PUT_LINE('END DATE IS ' || END_DATE);
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN' ||START_DATE|| 'AND' ||END_DATE;
END CUBE_VIEW;
/这就是我怎么累了我的查询后，我张贴在这里，但仍然没有工作！
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-02-04 07:17:58
最后，您必须将Start_Date和End_Date从动态SQL中提取出来：
execute immediate 'CREATE OR REPLACE VIEW F_SURVEY_YESTERDAY AS SELECT FLIGHTDATE FROM F_SURVEY_MULTICOL WHERE FLIGHTDATE BETWEEN '||Start_Date||' AND '||End_Date;
execute immediate 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN '||Start_Date||' AND '||End_Date;但是，请注意，这些都是number变量，而不是date，因此，除非您更改了这些变量，否则可能会有进一步的问题。
编辑:根据要求，完整的代码，为我工作。但是，它依赖于类型为FLIGHTDATE的NUMBER列。代码可以工作，但是您可能想了解为什么要传递VARCHAR2而不是DATE，以及为什么START_DATE和END_DATE是NUMBER而不是DATE。这肯定会节省大量的铸造，并使这些选择更容易维护。
create or replace procedure CUBE_VIEW(VAR_DT varchar2 default '')
authid current_user is
START_DATE number;
END_DATE number;
VAR date;
begin
if VAR_DT is null then
VAR := sysdate;
else
VAR := to_date(VAR_DT);
end if;
select to_number(
to_char(
trunc(
trunc(
to_date(
(select add_months(
(select to_date(VAR, 'DD- MON-YY')
+ (6
- to_number(
to_char(
to_date(VAR, 'DD-MON-YY') + 1
,'D')))
from dual)
,-34)
from dual)
,'DD-MON-YY')
,'MM')
- 1
,'MM')
,'YYMMDD'))
into START_DATE
from dual;
select to_number(
to_char(
trunc(
to_date(
(select add_months(
(select to_date(VAR, 'DD-MON-YY')
+ (6
- to_number(
to_char(
to_date(VAR, 'DD-MON-YY') + 1
,'D')))
from dual)
,-1)
from dual)
,'DD-MON-YY')
,'MM')
- 1
,'YYMMDD'))
into END_DATE
from dual;
dbms_output.PUT_LINE('Start Date is ' || START_DATE);
dbms_output.PUT_LINE('End Date is ' || END_DATE);
execute immediate 'CREATE OR REPLACE VIEW F_SURVEY_YESTERDAY AS SELECT FLIGHTDATE FROM F_SURVEY_MULTICOL WHERE FLIGHTDATE BETWEEN '
|| START_DATE
|| ' AND '
|| END_DATE;
execute immediate 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN '
|| START_DATE
|| ' AND '
|| END_DATE;
end CUBE_VIEW;
/页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9138497
复制相关文章
相似问题
腾讯云开发者
