动态执行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;

24 个解决方案

#1

那个仁兄看下 (急着要用 )

#2

1.OUPUT参数返回值

例： 向Order表插入一条记录,返回其标识


CREATE PROCEDURE [dbo].[nb_order_insert](

@o_buyerid int ,

@o_id bigint OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

BEGIN

INSERT INTO [Order](o_buyerid )

VALUES (@o_buyerid )

SET @o_id = @@IDENTITY

END

END

存储过程中获得方法：


DECLARE @o_buyerid int

DECLARE @o_id bigint

EXEC [nb_order_insert] @o_buyerid ,o_id bigint

#3

3.SELECT 数据集返回值


CREATE PROCEDURE [dbo].[nb_order_select](

@o_id int

)

AS

BEGIN

SET NOCOUNT ON;

SELECT o_id,o_buyerid FROM [Order]

WHERE o_id = @o_id

GO

存储过程中的获取方法

（1）、使用临时表的方法


CREATE TABLE [dbo].[Temp](

[o_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[o_buyerid] [int] NOT NULL

)

INSERT [Temp] EXEC [nb_order_select] @o_id

– 这时 Temp 就是EXEC执行SELECT 后的结果集

SELECT * FROM [Temp]

DROP [Temp] — 删除临时表

（2）、速度不怎么样.（不推荐）


SELECT * from openrowset(’provider_name','Trusted_Connection=yes’,'exec nb_order_select’)

#4

返回一个游标试试

#5

没有OUT参数，无法返回结果。
看你意思，应该是返回一个结果集，用ref cursor型传出参数返回之。

#6

返回一个游标该怎么写

#7

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;

#8

楼上的对游标不怎么熟悉
你看你给我说的用ref cursor 我在我的存储过程中在后应该怎么写

#9



set serveroutput on

#10

create or replace procedure shop_sale_receive(
  userAutoId    IN      number,
  shopAutoId    in number,
  startDate     in      varchar2,
  endDate       in      varchar2,
  r_cur         out     SYS_REFCURSOR
)
IS
   sqls     VARCHAR2(1000);
   r_cur    SYS_REFCURSOR;
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);
  open r_cur for sqls;
end;

#11

r_cur
这是个游标吧!
是不是还要声明个游标
我按楼上的加了一句.
但是报错说:必须声明 r_cur

#12

说这一句报错:create or replace procedure shop_sale_receive(

错误信息是:pls-00410:RECODRD,TABLE或参数列表中的字段不允许重复
PL/SQL:Compilaion unit analysis terminated
看看能帮我解决一下

#13

#14

我搞晕了
在PLSQL里
是这样写的吧

begin
a out varchar2(1000);
shop_sale_receive(7105,shopAutoId =>311,startDate => '2009-12-1',endDate => '2009-
12-20',r_cur => a);
end

有错

#15

suiziguo 能跟你聊两句吗? QQ或MSN

#16

我汗，你调试该过程吧？
有点昏厥，哎……
什么错，你总得告诉我吧。
你先告诉我，存储过程编译是否通过，有没有错误！！！

#17

存储过程没错了

就是调用存储过程不知道怎么执行了
传参数不知道传个什么类型进去

还有就是能不能就是我掉完成存储过程就显示数据
不要用 dbms_output.pint()这个函数去打印执行后的放回变量

#18

过程没错就说明对的
PL/SQL有专门的调试窗口，你直接在其下面输入你的值，就OK了。
然后点击返回的游标，新窗口里自然有返回的结果集。

#19

追后一个返回值该怎么给给什么类型的
我给了个a sys_refcursor 执行了也什么也不出来

#20

数据集赋给游标，还要将游标里的数据读出来才行

#21

楼主可以通过建视图来查询结果
EXECUTE IMMEDIATE sqls; -->
execute immediate 'create or replace view view_tmp as '||sqls;

执行完过程后查询select * from view_tmp

#22

引用 19 楼 helanhe 的回复:

追后一个返回值该怎么给给什么类型的
我给了个asys_refcursor 执行了也什么也不出来

如果存储过程没有错，调用的时候，这样：
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;

#23

还是不行啊! 都第2天了还是没搞出来啊执行的时候没有东西返回
open r_cur for sqls; 这一句是做什么用的打开游标后面是什么意思啊

我想打印下看下 sqls 这个变量的sql语句执行了没
打印下查出来的东西

#24

引用 10 楼的回复:

create or replace procedure shop_sale_receive(
  userAutoId IN number,
  shopAutoId in number,
  startDate in varchar2,
  endDate in varchar2,
r_cur out SYS_REFCURSOR
)
IS
  sqls VARCHAR……

在给动态SQL拼接SQL时，为什么有时候是三个单引号？我或许这么问比较清洗一点“拼接动态SQL时，怎么添加单引号？”。望多加指点，谢谢

#1