sql_select_updatetime := select min(updatetime) into V_updatetime FROM SHEB_STATICP201106DC
WHERE fltno='ZH4330' and buydate='12-5月 -11'
and godate='10-6月 -11' and orgcity='SZX'
and descity='WUX' and router ='SZX-CTU' execute immediate sql_select_updatetime;
报 缺失关键字错误
WHERE fltno='ZH4330' and buydate='12-5月 -11'
and godate='10-6月 -11' and orgcity='SZX'
and descity='WUX' and router ='SZX-CTU' execute immediate sql_select_updatetime;
报 缺失关键字错误
WHERE fltno=''ZH4330'' and buydate=''12-5月 -11''
and godate=''10-6月 -11'' and orgcity=''SZX''
and descity=''WUX'' and router =''SZX-CTU''' execute immediate sql_select_updatetime into V_updatetime;--另外你日期格式只能使用中文才能有效,最好使用to_date转换
--日期最好是使用ro_date函数转换
sql_select_updatetime :=
'select min(updatetime) into V_updatetime FROM SHEB_STATICP201106DC
WHERE fltno=''ZH4330''
and buydate=to_date(''2011-5-12'',''yyyy-mm-dd''
and godate=to_date(''2011-6-10'',''yyyy-mm-dd'')
and orgcity=''SZX''
and descity=''WUX'' and router =''SZX-CTU''';
execute immediate sql_select_updatetime;
oracle 单双引号的问题
(
P_flightno VARCHAR2,
P_orgcity VARCHAR2,
P_descity VARCHAR2,
P_buydate VARCHAR2,
P_godate VARCHAR2,
P_availfltclass VARCHAR2,
P_availkfltclass VARCHAR2,
P_flightflag VARCHAR2,
P_router VARCHAR2,
P_Cursor OUT T_CURSOR
)
is
(
V_FlightnoNumstr VARCHAR2(6);
V_tablename VARCHAR2(60);
V_recordnumber int;
V_updatetime date;
V_price number;
V_spcode VARCHAR2(30);
V_fltclass VARCHAR2(30);
V_t VARCHAR2(300);
V_ei VARCHAR2(300);
V_ruletype VARCHAR2(6);
V_farebasis VARCHAR2(50);
V_zknum number;
V_favzknum number;
V_flightflag VARCHAR2(30);
V_sid number;
V_cursor T_CURSOR;
sql_select_updatetime VARCHAR2(1000);
sql_insert VARCHAR2(1000);
sql_select VARCHAR2(1000);
sql_update VARCHAR2(1000);
V_buydate date;
V_godate date;
BEGINV_FlightnoNumstr := substr(P_flightno,3);
V_buydate := to_date(P_buydate,'YYYY-MM-DD');
V_godate := to_date(P_godate,'YYYY-MM-DD');V_tablename := 'SHEB_STATICP2011'||to_char(sysdate,'MM')||'DC';
sql_select_updatetime := 'SELECT min(updatetime) into V_updatetime FROM '||V_tablename|| ' WHERE fltno = '''||P_flightno||'''
and buydate = '''||V_buydate||'''
and godate = '''||V_godate||'''
and orgcity = '''||P_orgcity||'''
and descity = '''||P_descity||'''
and router = '''||P_router||'''';
execute immediate sql_select_updatetime; 原程序是这样的,测试时,刚才的SQL就是由拼接的SQL语句得到的。
报 缺失关键字错误
BEGIN/*V_FlightnoNumstr := substr(P_flightno,3);
V_buydate := to_date(P_buydate,'YYYY-MM-DD');
V_godate := to_date(P_godate,'YYYY-MM-DD');*/
--这三个赋值的问题:
使用select colname into v_name from table_name 形式赋值
select to_date(P_buydate,'YYYY-MM-DD') into V_buydate from table_name;
...V_tablename := 'SHEB_STATICP2011'||to_char(sysdate,'MM')||'DC';
(
P_flightno VARCHAR2,
P_orgcity VARCHAR2,
P_descity VARCHAR2,
P_buydate VARCHAR2,
P_godate VARCHAR2,
P_availfltclass VARCHAR2,
P_availkfltclass VARCHAR2,
P_flightflag VARCHAR2,
P_router VARCHAR2,
P_Cursor OUT T_CURSOR)
IS
V_FlightnoNumstr VARCHAR2(6);
V_ASID VARCHAR2(500);
V_SSID VARCHAR2(500);
V_ESID VARCHAR2(500);
V_JSID VARCHAR2(500);
V_USID VARCHAR2(500);
V_tablename VARCHAR2(60);
V_recordnumber int;
V_updatetime date;
V_price number;
V_spcode VARCHAR2(30);
V_fltclass VARCHAR2(30);
V_t VARCHAR2(300);
V_ei VARCHAR2(300);
V_ruletype VARCHAR2(6);
V_farebasis VARCHAR2(50);
V_zknum number;
V_favzknum number;
V_flightflag VARCHAR2(30);
V_sid number;
V_cursor T_CURSOR;
sql_select_updatetime VARCHAR2(1000);
sql_insert VARCHAR2(1000);
sql_select VARCHAR2(1000);
sql_update VARCHAR2(1000);
V_buydate date;
V_godate date;
BEGINV_FlightnoNumstr := substr(P_flightno,3);
/*V_buydate := to_date(P_buydate,'YYYY-MM-DD');
V_godate := to_date(P_godate,'YYYY-MM-DD');
*/
select to_date(P_buydate,'YYYY-MM-DD') into V_buydate from dual ;
select to_date(P_godate,'YYYY-MM-DD') into V_godate from dual ; if(P_flightflag = 'DC') then
V_tablename := 'SHEB_STATICP2011'||to_char(sysdate,'MM')||'DC';
sql_select_updatetime := 'SELECT min(updatetime) into V_updatetime FROM '||V_tablename||
' WHERE fltno = '''||P_flightno||'''
and buydate = '''||V_buydate||'''
and godate = '''||V_godate||'''
and orgcity = '''||P_orgcity||'''
and descity = '''||P_descity||'''
and router = '''||P_router||''''; 修改赋值方式后,依然报同样的错;
execute immediate sql_select_updatetime; --测试出错:缺失关键字
test出来,放到sql窗口运行,你不就明白了吗?