想使用拼接sql语句,然后执行返回游标的形式得到数据
错误代码
function test(P_TSE_ID IN Integer,
P_SALE_NO IN Varchar2,
P_FAC_NO IN Varchar2,
P_REEOR Out Varchar2)
Return Search_Result Is
CURINFO Search_Result; /*Type Search_Result Is Ref Cursor;*/
sSQL Varchar2(2000);
begin
Open CURINFO For
Select * From dual;
sSQL := 'Select * From Sell_Billinfo where tse_id = ' || P_TSE_ID;
sSQL := sSQL || ' and sale_no = ' || P_SALE_NO;
sSQL := sSQL || ' and fac_no = ' || P_FAC_NO; open CURINFO for sSQL;
If (CURINFO%Isopen) Then
Return CurInfo;
end if;
Exception
When Others Then
Begin
Rollback;
P_REEOR := '执行过程出现错误:' + Sqlerrm;
End;
end test;估计错误出在open CURINFO for sSQL;
但是不知道正确的怎么写?
错误代码
function test(P_TSE_ID IN Integer,
P_SALE_NO IN Varchar2,
P_FAC_NO IN Varchar2,
P_REEOR Out Varchar2)
Return Search_Result Is
CURINFO Search_Result; /*Type Search_Result Is Ref Cursor;*/
sSQL Varchar2(2000);
begin
Open CURINFO For
Select * From dual;
sSQL := 'Select * From Sell_Billinfo where tse_id = ' || P_TSE_ID;
sSQL := sSQL || ' and sale_no = ' || P_SALE_NO;
sSQL := sSQL || ' and fac_no = ' || P_FAC_NO; open CURINFO for sSQL;
If (CURINFO%Isopen) Then
Return CurInfo;
end if;
Exception
When Others Then
Begin
Rollback;
P_REEOR := '执行过程出现错误:' + Sqlerrm;
End;
end test;估计错误出在open CURINFO for sSQL;
但是不知道正确的怎么写?
解决方案 »
- 开启Oracle 归档日志问题,求救
- 过程与java的问题
- 安装多个oracle客户端
- oracle能实现临时角色吗,只在session中有效?
- sys_context('userenv','ip_address') 问题
- 关于查询建立索引的小问题
- date 类型怎么使用?我的这条语句为什么有这个错误 ?
- 请问:ORACLE 中授什么权限 ,可用select * from user_col_comments ?
- 有关oracle9i的TNSListener的三个端口的讨论
- 在oracle中查看刚执行的语句的一个问题
- 求Oracle中@的用法及意义
- 【急】sql中用case when then else end实现查询
SQL> create or replace function test(P_TSE_ID IN INTEGER)
2 Return sys_refcursor Is
3 CURINFO sys_refcursor; /*Type Search_Result Is Ref Cursor;*/
4 sSQL Varchar2(2000);
5 BEGIN
6
7 sSQL := 'Select rownum From dual connect by rownum<=' || P_TSE_ID;
8
9 open CURINFO for sSQL;
10 If (CURINFO%Isopen) Then
11 Return CurInfo;
12 end if;
13
14 Exception
15 When Others Then
16 Begin
17 ROLLBACK;
18 End;
19 end test;
20 /Function created.SQL> var o_cur refcursorSQL> begin
2 :o_cur:=test(3);
3 end;
4 /PL/SQL procedure successfully completed.SQL> print o_cur ROWNUM
----------
1
2
3
4SQL>
type search_result is ref cursor;
end;
/create or replace function f_test(P_TSE_ID IN Integer,
P_REEOR Out Varchar2)
Return my_types.Search_Result Is
CURINFO my_types.Search_Result; /*Type Search_Result Is Ref Cursor;*/
sSQL Varchar2(2000);
begin
Open CURINFO For
Select * From dual;
sSQL := 'Select first_name,salary From employees where employee_id = ' || P_TSE_ID; open CURINFO for sSQL;
If (CURINFO%Isopen) Then
Return CurInfo;
end if;
Exception
When Others Then
Begin
Rollback;
P_REEOR := '执行过程出现错误:' + Sqlerrm;
End;
end f_test;
/-- 下面是測試語句
variable cur refcursor;
variable errtext varchar2(200);
exec :cur := f_test(198, :errtext);
print cur;FIRST_NAME SALARY
------------------------------ ----------
Donald 2600
sSQL := 'Select * From Sell_Billinfo where tse_id = ''' || P_TSE_ID;
sSQL := sSQL ||''' and sale_no = ''' || P_SALE_NO;
sSQL := sSQL || ''' and fac_no = ''' || P_FAC_NO ||'''';
是SQL的拼接错了,谢谢了
也谢谢其他人