说:有12张表,table01到table12。按月查表格,规则如下:例3月份查table02,4月份查table03类推。
每个表都有字段5个:字段1,字段2,字段3,字段4,字段5,用存储过程怎么写呢?
CREATE OR REPLACE PROCEDURE "test1"
(
vo_resultcode OUT NUMBER ,
vo_resultinfo OUT VARCHAR2
)
IS
字段1 VARCHAR2(30);
字段2 VARCHAR2(30);
字段3 VARCHAR2(30);
字段4 VARCHAR2(30);
字段5 VARCHAR2(30);
v_sql VARCHAR2(1500);
BEGIN
v_sql := ‘SELECT * FROM TABLE’||TO_CHAR(sysdate,‘MM’);
EXECUTE IMMEDIATE v_sql INTO 字段1,字段2,字段3,字段4,字段5;请高手帮我看看有没有错呢?谢谢啦,新手求指导。
每个表都有字段5个:字段1,字段2,字段3,字段4,字段5,用存储过程怎么写呢?
CREATE OR REPLACE PROCEDURE "test1"
(
vo_resultcode OUT NUMBER ,
vo_resultinfo OUT VARCHAR2
)
IS
字段1 VARCHAR2(30);
字段2 VARCHAR2(30);
字段3 VARCHAR2(30);
字段4 VARCHAR2(30);
字段5 VARCHAR2(30);
v_sql VARCHAR2(1500);
BEGIN
v_sql := ‘SELECT * FROM TABLE’||TO_CHAR(sysdate,‘MM’);
EXECUTE IMMEDIATE v_sql INTO 字段1,字段2,字段3,字段4,字段5;请高手帮我看看有没有错呢?谢谢啦,新手求指导。
如果不是的话就有问题了,就必须要用游标来接这些数据。
'INTO 字段1, 字段2, 字段3, 字段4, 字段5';如果返回结果有多条记录需要使用光标,其实一个带参光标就搞定了。
呵呵,调成这样了。比较复杂,能帮修改下嘛,谢啦:CREATE OR REPLACE PROCEDURE "zengzhi_xingweimingxi_yi"
(
vo_resultcode OUT NUMBER,
vo_resultinfo OUT VARCHAR2
)
IS
START_DATE VARCHAR2(8);
MSISDN VARCHAR2(15);
OTHER_PARTY VARCHAR2(30);
CALL_TYPE VARCHAR2(2);
START_TIME VARCHAR2(20);
END_TIME VARCHAR2(20);
CALL_DURATION INTEGER;
v_sql VARCHAR2(1500);
BEGIN
v_sql:='SELECT START_DATE,MSISDN,OTHER_PARTY,CALL_TYPE,to_char(TO_DATE(START_DATE||'' ''||START_TIME,''yyyymmdd hh24miss''),''yyyymmdd hh24miss'')
,to_char(TO_DATE(START_DATE||'' ''||START_TIME,''yyyymmdd hh24miss'') + CALL_DURATION / 24 / 60 / 60,''yyyymmddhh24miss''),CALL_DURATION FROM TG_CDR'
||to_char(add_months(sysdate,-1),'mm');
EXECUTE IMMEDIATE v_sql INTO START_DATE,MSISDN,OTHER_PARTY,CALL_TYPE,START_TIME,END_TIME,CALL_DURATION;END;我是按别人修改的,用toad运行时候报“OUT is not a valid INTEGER"错误,请问那个
vo_resultcode OUT NUMBER,
vo_resultinfo OUT VARCHAR2这两句是在说系统的结果返回吗??不明白是做什么的啊?
怎么添加呢?代码如下:
CREATE OR REPLACE PROCEDURE "zengzhi_xingweimingxi_yi"
(
vo_resultcode OUT NUMBER,
vo_resultinfo OUT VARCHAR2
)
IS
START_DATE VARCHAR2(8);
MSISDN VARCHAR2(15);
OTHER_PARTY VARCHAR2(30);
CALL_TYPE VARCHAR2(2);
START_TIME VARCHAR2(20);
END_TIME VARCHAR2(20);
CALL_DURATION INTEGER;
v_sql VARCHAR2(1500);
BEGIN
v_sql:='SELECT START_DATE,MSISDN,OTHER_PARTY,CALL_TYPE,to_char(TO_DATE(START_DATE||'' ''||START_TIME,''yyyymmdd hh24miss''),''yyyymmdd hh24miss'')
,to_char(TO_DATE(START_DATE||'' ''||START_TIME,''yyyymmdd hh24miss'') + CALL_DURATION / 24 / 60 / 60,''yyyymmddhh24miss''),CALL_DURATION FROM TG_CDR'
||to_char(add_months(sysdate,-1),'mm');
EXECUTE IMMEDIATE v_sql INTO START_DATE,MSISDN,OTHER_PARTY,CALL_TYPE,START_TIME,END_TIME,CALL_DURATION;END;我是按别人修改的,用toad运行时候报“OUT is not a valid INTEGER"错误,请问那个
vo_resultcode OUT NUMBER,
vo_resultinfo OUT VARCHAR2这两句是在说系统的结果返回吗??不明白是做什么的啊?