动态执行sql语句
我有个存储过程
create or replace procedure shop_sale_receive(
userAutoId IN number,
shopAutoId in number,
startDate in varchar2,
endDate in varchar2
)
as
sqls VARCHAR2(1000);
BEGIN
sqls:='select ORDER_T.CREATEDATE,
ORDER_T.ID,
ORDER_T.PAIDACCOUNT,
ORDER_T.CASH,
ORDER_T.CHEQUE,
ORDER_T.CREDIT,
ORDER_T.CHARGEUP,
ORDER_T.CURRENCYEXCHANGE,
ORDER_T.OTHERRECEIVE,
ORDER_T.STATUSID,
ORDER_T.REMARK,
ORDER_T.ORDERTYPE,
ORDER_T.CUSTOMERNAME
from ORDER_T where ORDER_T.ENABLE=1 and ORDER_T.shopAutoId='||shopAutoId||'
and ORDER_T.ORDERTYPE in ('||'''sale.receives'''||','||'''sale.receives.red'''||')
and ORDER_T.STATUS ='||'''ACCEPT''';
IF userAutoId!=0 THEN
sqls:=sqls||' and order_t.customerAutoId='||userAutoId;
END if;
dbms_output.put_line(startDate) ;
if startDate is not null then
sqls:=sqls||' and to_char(ORDER_T.createDate,'||'''yyyy-MM-dd'''||')>='||''''||startDate||'''';
end if;
if endDate is not null then
sqls:=sqls||' and to_char(ORDER_T.createDate,'||'''yyyy-MM-dd'''||')<='||'''' ||endDate||'''';
end if;
dbms_output.put_line(sqls);
EXECUTE IMMEDIATE sqls; //这句执行sql语句 但是执行了 怎么没有返回结果;
end;
[解决办法]
返回一个游标 试试
[解决办法]
CREATE OR REPLACE procedure selectAllEmployments
(r_cur OUT SYS_REFCURSOR)
IS
r_cur SYS_REFCURSOR;
BEGIN
OPEN r_cur FOR
SELECT EMPLOYEE, EMPLOYER,
STARTDATE, ENDDATE,
REGIONCODE, EID, VALUE, CURRENCY
FROM EMPLOYMENT;
END;
[解决办法]
数据集赋给游标，还要将游标里的数据读出来才行
[解决办法]
楼主可以通过建视图来查询结果
EXECUTE IMMEDIATE sqls; -->
execute immediate 'create or replace view view_tmp as '
[解决办法]
sqls;
执行完过程后查询select * from view_tmp
[解决办法]
如果存储过程没有错，调用的时候，这样：
sqlplus命令行：
variable a refcursor;
exec shop_sale_receive(1,1,'20080101','20091231',:a);
print a;
即可看到结果。
你可以把这段拿去试试看效果：
drop table emp;
create table emp(A varchar2(10),B varchar2(10),C varchar2(10));
insert into emp values('1','1','1');
insert into emp values('2','2','2');
insert into emp values('3','3','3');
commit;
set serveroutput on
--该存储过程根据 in 参数 i_A 查出表的 B列赋给out参数 o_B
create or replace procedure p_test_dym(
i_A in varchar2,
o_B out varchar2
)
as
begin
select B into o_B from emp where emp.A = i_A;
end;
/
variable b varchar2(10);
exec p_test_dym('1',:b);
print b;