create OR REPLACE PACKAGE BODY zhixiangorder_matou ISFUNCTION Get_matouqs( matouname in varchar2,
laiyuan IN number,
LYZIDUAN IN varchar2,
zhixiancode_ in varchar2,
dop_id_ IN number,
ORDERNO IN varchar2
) RETURN varchar2
IS
temp varchar2(100);
tempa varchar2(100);
tempb varchar2(1000);
tempb:= 'Cursor get_matou_ is
select '+LYZIDUAN+' from xz_zhixiangorder_dataview
where dop_id='+dop_id_+' and zhixiancode='+zhixiancode_+' and ORDER_NO='+ORDERNO+';';
begin
temp:=matouname;
if laiyuan in (1,2,3,4) then
begin
tempb;
Open get_matou_;
Fetch get_matou_ Into tempa;
Close get_matou_;
temp:=temp||tempa;
end;
end if; Return temp;
End Get_matouqs;
--------------------------------------------------------------------
END zhixiangorder_matou;
这是本人写的一个包体,包体中有个select语句中的字段是传进来的参数。不能直接执行,本人的思路中像SQL SERVER中用字符串来实现(代码中打了红色标记的)。但本人对oracle中定义和执行字符串的规则不是很懂。请教各位如何在oracle中定义和执行字符串。如何实现以上的这段代码的功能。
laiyuan IN number,
LYZIDUAN IN varchar2,
zhixiancode_ in varchar2,
dop_id_ IN number,
ORDERNO IN varchar2
) RETURN varchar2
IS
temp varchar2(100);
tempa varchar2(100);
tempb varchar2(1000);
tempb:= 'Cursor get_matou_ is
select '+LYZIDUAN+' from xz_zhixiangorder_dataview
where dop_id='+dop_id_+' and zhixiancode='+zhixiancode_+' and ORDER_NO='+ORDERNO+';';
begin
temp:=matouname;
if laiyuan in (1,2,3,4) then
begin
tempb;
Open get_matou_;
Fetch get_matou_ Into tempa;
Close get_matou_;
temp:=temp||tempa;
end;
end if; Return temp;
End Get_matouqs;
--------------------------------------------------------------------
END zhixiangorder_matou;
这是本人写的一个包体,包体中有个select语句中的字段是传进来的参数。不能直接执行,本人的思路中像SQL SERVER中用字符串来实现(代码中打了红色标记的)。但本人对oracle中定义和执行字符串的规则不是很懂。请教各位如何在oracle中定义和执行字符串。如何实现以上的这段代码的功能。
DECLARE
sql_stat VARCHAR2(100);
emp_record emp%ROWTYPE;
BEGIN
sql_stat:='SELECT * FROM emp WHERE empno=:eno';
EXECUTE IMMEDIATE sql_stat INTO emp_record USING &1;
dbms_output.put_line('雇员'||emp_record.ename||'的工资为'||emp_record.sal);
END;
laiyuan IN number,
LYZIDUAN IN varchar2,
zhixiancode_ in varchar2,
dop_id_ IN number,
ORDERNO IN varchar2
) RETURN varchar2
IS
temp varchar2(100);
tempa varchar2(100);
tempb varchar2(1000); get_matou_ sys_refcursor;begin
tempb:= 'select '+LYZIDUAN+' from xz_zhixiangorder_dataview
where dop_id='+dop_id_+' and zhixiancode='+zhixiancode_+' and ORDER_NO='+ORDERNO+';'; temp:=matouname;
if laiyuan in (1,2,3,4) then
begin
Open get_matou_ FOR tempb;
Fetch get_matou_ Into tempa;
Close get_matou_;
temp:=temp||tempa;
end;
end if; Return temp;
End Get_matouqs;
ORA-06502:PL/SQL:numeric or value error:character to number conversion error
也就是这段代码出现了问题
tempb:= 'select '+LYZIDUAN+' from xz_zhixiangorder_dataview
where dop_id='+dop_id_+' and zhixiancode='''+zhixiancode_+''' and ORDER_NO='''+ORDERNO+''';';
请教各位是哪出错了。
where dop_id='+dop_id_+' and zhixiancode='+zhixiancode_+' and ORDER_NO='+ORDERNO+';'; + --->> || tempb:= 'select '||LYZIDUAN||' from xz_zhixiangorder_dataview
where dop_id='||dop_id_||' and zhixiancode='||zhixiancode_||' and ORDER_NO='||ORDERNO||';';
begin
Open get_matou_ for tempb;
Fetch get_matou_ Into tempa;
Close get_matou_;
temp:=temp||tempa;
end;
不知是我对游标不熟悉还是没理解你的构思。在红色哪步出错。提示是ORA-00933:SQL command not properly ended error
请教各位是什么原因出错了。
OPEN FOR V_SQL;
是因为多了一个;吧
tempb:= 'select '||LYZIDUAN||' from xz_zhixiangorder_dataview
where dop_id='||dop_id_||' and zhixiancode='||zhixiancode_||' and ORDER_NO='||ORDERNO
||';';乱讲啊,sys_refcursor在9i、10g上都有
看看是不是 少了或者多了 符号,这个是编译都没有通过的错误。