请教.那里错了.谢了 CREATE OR REPLACE PROCEDURE test_data(post_type VARCHAR2, contract_no VARCHAR2, proj_code VARCHAR2, STARTDATE VARCHAR2, ENDDATE VARCHAR2, Re_CURSOR OUT T_CURSOR) IS TYPE RESULTSET IS REF CURSOR; SQLSTR VARCHAR2(1000); CR RESULTSET; billtype varchar2(50) := 'bill'; datetype varchar2(50) := 'yyyy-mm-dd'; bt varchar2(100) := 'materialOnsite'; strkeyman int := 1; BEGIN SQLSTR := 'select p.* from gl_ccm_middle_pay p where p.keyman=strkeyman and p.posttype=post_type p.ipm_date>=to_date(STARTDATE,datetype) and p.ipm_date<=to_date(ENDDATE,datetype) and p.projcode=proj_code p.contract_no = contract_no and p.b_t=bt and p.bill_type=billtype)'; OPEN CR FOR SQLSTR USING post_type, STARTDATE,ENDDATE, proj_code, contract_no; Re_CURSOR := CR; CLOSE CR; END test_data;
SQLSTR := 'select p.* from gl_ccm_middle_pay p where p.keyman=strkeyman and p.posttype=post_type p.ipm_date>=to_date(STARTDATE,datetype) and p.ipm_date<=to_date(ENDDATE,datetype) and p.projcode=proj_code p.contract_no = contract_no and p.b_t=bt and p.bill_type=billtype)'; 过程会认为这是一个字符串,无法识别其中的参数,用“||”将参数划分出来,拼成SQL语句应该就可以了!
SQLSTR := 'select p.* from gl_ccm_middle_pay p where p.keyman=strkeyman and p.posttype=post_type p.ipm_date>=to_date(STARTDATE,datetype) and p.ipm_date<=to_date(ENDDATE,datetype) and p.projcode=proj_code p.contract_no = contract_no and p.b_t=bt and p.bill_type='||billtype||')';
在SQL中用到了billtype而总是说被定义了但没有使用???
CREATE OR REPLACE PROCEDURE test_data(post_type VARCHAR2,
contract_no VARCHAR2,
proj_code VARCHAR2,
STARTDATE VARCHAR2,
ENDDATE VARCHAR2,
Re_CURSOR OUT T_CURSOR) IS
TYPE RESULTSET IS REF CURSOR;
SQLSTR VARCHAR2(1000);
CR RESULTSET;
billtype varchar2(50) := 'bill';
datetype varchar2(50) := 'yyyy-mm-dd';
bt varchar2(100) := 'materialOnsite';
strkeyman int := 1;
BEGIN
SQLSTR := 'select p.*
from gl_ccm_middle_pay p
where p.keyman=strkeyman and
p.posttype=post_type
p.ipm_date>=to_date(STARTDATE,datetype) and
p.ipm_date<=to_date(ENDDATE,datetype) and
p.projcode=proj_code
p.contract_no = contract_no and
p.b_t=bt and
p.bill_type=billtype)';
OPEN CR FOR SQLSTR USING post_type, STARTDATE,ENDDATE, proj_code, contract_no;
Re_CURSOR := CR;
CLOSE CR;
END test_data;
from gl_ccm_middle_pay p
where p.keyman=strkeyman and
p.posttype=post_type
p.ipm_date>=to_date(STARTDATE,datetype) and
p.ipm_date<=to_date(ENDDATE,datetype) and
p.projcode=proj_code
p.contract_no = contract_no and
p.b_t=bt and
p.bill_type=billtype)';
过程会认为这是一个字符串,无法识别其中的参数,用“||”将参数划分出来,拼成SQL语句应该就可以了!
from gl_ccm_middle_pay p
where p.keyman=strkeyman and
p.posttype=post_type
p.ipm_date>=to_date(STARTDATE,datetype) and
p.ipm_date<=to_date(ENDDATE,datetype) and
p.projcode=proj_code
p.contract_no = contract_no and
p.b_t=bt and
p.bill_type='||billtype||')';