存储过程传参问题

Question

本帖最后由 rain1816 于 2010-08-27 14:01:16 编辑

Answer 1

你的每一个str_sql 里只有一个参数。

Answer 2

  能否具体点？怎样在str_sql中定义多个参数？

Answer 3

-- 你这样是不行的：当你某个参数输入为空的时候，比如：zl
-- 那么其SQL语句为：'select * from KF_FYRKMX where 1=1 and FYRKMX_KF = :kf1'
-- 此时，你只需要一个绑定变量，
-- 而你后面：open table1 for str_sql using zl, kf;
-- using 了两个绑定变量，所以肯定会出错滴！

Answer 4

----- 这样试试：
CREATE OR REPLACE PROCEDURE FYRK_SUM(zl IN VARCHAR2, kf IN VARCHAR2, table1 OUT SYS_REFCURSOR)
IS
  str_sql VARCHAR2(4000);
BEGIN
  str_sql :='SELECT * FROM KF_FYRKMX WHERE (FYRKMX_YLMC = :zl OR :zl IS NULL) AND (FYRKMX_KF = :kf OR :kf IS NULL)';
  OPEN table1 FOR str_sql USING zl, zl, kf, kf;
END FYRK_SUM;
/

Answer 5

str_sql := str_sql || ' and FYRKMX_KF = :kf1'
and 前少空格

Answer 6

-- 那就不能用绑定变量啦，那样反而不好（只能用SQL拼接)，别去考虑....

Answer 7

-- 还有：最好别用 select *

Answer 8

SQL> create or replace procedure FYRK_SUM(table1 out sys_refcursor, zl in varchar2, kf in varchar2) 
is
  2    str_sql varchar2(5000);   
  3  begin
  4    str_sql := 'select * from TJ_TEST1 where 1=1 ';  
  5    IF(zl IS NOT NULL)
  6    THEN
  7    str_sql := str_sql || 'and name = :zl';
  8    END IF;   
  9    IF(kf IS NOT NULL)
 10    THEN
 11    str_sql := str_sql || ' and address = :kf1';
 12    END IF;    
 13    dbms_output.put_line(str_sql);  
 14  open table1 for str_sql using zl,kf;
 15  end FYRK_SUM;
 16  /过程已创建。SQL> exec FYRK_SUM(:tcur,'joe','sh');
select * from TJ_TEST1 where 1=1 and name = :zl and address = :kf1PL/SQL 过程已成功完成。SQL> print :tcur;        ID NAME                        AGE ADDRESS
---------- -------------------- ---------- --------------------
         3 joe                          27 sh

Answer 9

SQL 拼接？那具体是怎么实现的？不会啊

Answer 10

----- 好的方法：
CREATE OR REPLACE PROCEDURE FYRK_SUM(zl IN VARCHAR2, kf IN VARCHAR2, table1 OUT SYS_REFCURSOR)
IS
  str_sql VARCHAR2(4000);
BEGIN
  str_sql :='SELECT * FROM KF_FYRKMX WHERE (FYRKMX_YLMC = :zl OR :zl IS NULL) AND (FYRKMX_KF = :kf OR :kf IS NULL)';
  OPEN table1 FOR str_sql USING zl, zl, kf, kf;
END FYRK_SUM;
/
----- 差的方法：
CREATE OR REPLACE PROCEDURE FYRK_SUM(zl IN VARCHAR2, kf IN VARCHAR2, table1 OUT SYS_REFCURSOR)
IS
  str_sql VARCHAR2(4000);
BEGIN
  str_sql :='SELECT * FROM KF_FYRKMX WHERE 1=1 ';
  
  IF(zl IS NOT NULL)
  THEN
  str_sql := str_sql || ' AND FYRKMX_YLMC = '''||zl||'''';
  END IF;  IF(kf IS NOT NULL)
  THEN
  str_sql := str_sql || ' AND FYRKMX_KF = '''||kf1||'''';
  END IF;   OPEN table1 FOR str_sql;
END FYRK_SUM;
/

Answer 11

谢谢，请问如果其中一个参数为空时，USING了两个参数，这时会出错，是吗？

Answer 12

哦， :zl IS NULL 时，则不查询此条件，对吧，那我就用上面这个方法吧；
ORACLE里当传值为""空字符串时，使用:zl IS NULL，也实现了不查询此条件吧

Answer 13

--空格的问题
str_sql := str_sql || ' and FYRKMX_YLMC = :zl';str_sql := str_sql || ' and FYRKMX_KF = :kf1';

Answer 14

create or replace procedure FYRK_SUM(table1 out sys_refcursor, zl in varchar2, kf in varchar2) is  str_sql varchar2(5000);
   
begin
  str_sql := 'select * from KF_FYRKMX where 1=1 ';
   
  IF(zl IS NOT NULL)
  THEN
  str_sql := str_sql || ' and FYRKMX_YLMC = :zl';
  END IF;
   
  IF(kf IS NOT NULL)
  THEN
  str_sql := str_sql || ' and FYRKMX_KF = :kf1';
  END IF; 
   
  dbms_output.put_line(str_sql);
   
open table1 for str_sql using zl, kf;end FYRK_SUM;------- 首先请问：这段代码是你写的吗？你能看懂这段代码吗？
  IF(zl IS NOT NULL)
  THEN
  str_sql := str_sql || 'and FYRKMX_YLMC = :zl';
  END IF;-- *1)   IF(zl IS NOT NULL)  -- 如果传入的“zl”参数为空(只有：条件满足的时候才执行下面的 THEN ... END IF语句块)
--       明白“只有”的意思吗？
-- 这个语句块的作用是： str_sql := str_sql || 'and FYRKMX_YLMC = :zl'; 
-- 就是在str_sql 原有值串的末尾追加  'and FYRKMX_YLMC = :zl'     -- 当然：and 前面最好加个空格，将其变成：' and FYRKMX_YLMC = :zl'-- 其下同理！-- 再不明白的话，我也不再解释　.... 

Answer 15

--我把罗哥的意思给你整明白
SQL> edi
已写入 file afiedt.buf  1  create or replace procedure p1(tb_rec out sys_refcursor,zl varchar2,kf varchar2)
  2  as
  3  str_sql varchar2(3000);
  4  begin
  5  str_sql:='select * from KF_FYRKMX where 1=1';
  6  if zl is not null and kf is null  then
  7  str_sql:=str_sql||' and  FYRKMX_YLMC=:zl';
  8  open tb_rec for str_sql using zl;
  9  elsif kf is not null and zl is null then
 10  str_sql:=str_sql||' and FYRKMX_KF=:kf1';
 11  open tb_rec for str_sql using kf;
 12  elsif zl is not null and kf is not null then
 13  str_sql:=str_sql||' FYRKMX_YLMC=:zl and FYRKMX_KF=:kf1';
 14  open tb_rec for str_sql using zl,kf;
 15  elsif zl is null and kf is null then
 16  open tb_rec for str_sql;
 17  end if;
 18  dbms_output.put_line(str_sql);
 19* end;
 20  /过程已创建。SQL> var t_rec refcursor
SQL> exec p1(:t_rec,'','')
select * from KF_FYRKMX where 1=1PL/SQL 过程已成功完成。