Oracle存储过程写法

create or replace procedure QIANFEIGL_JIAOKUANDY(

cebenh varchar2, kehuh varchar2, hetongh varchar2, v_cur  out query_pkg.Query_cur

)

is

SQLSTR VARCHAR2(3000);

begin

  SQLSTR:='   select a.s_ch,a.s_cid,c.s_hetongh,a.d_caozuosj from zw_yingyez a left join kg_biaokaxx b on a.s_cid=b.s_cid   left join kg_zhanghuxx c on c.s_zhanghubh=b.s_zhanghubh   where i_xiaozhang=0' ;

  if cebenh is not null and length(cebenh)>0 then

    SQLSTR:=SQLSTR||' and a.s_ch='''||cebenh||'''';

   end if;

   if hetongh is not null and length(hetongh)>0 then

       SQLSTR:=SQLSTR||' and c.s_hetongh='''||hetongh||'''';

  end if;

  if kehuh is not null and length(kehuh)>0 then

     SQLSTR:=SQLSTR||' and a.s_cid='''||kehuh||'''';

  end if;

   SQLSTR:=SQLSTR||' order by a.d_caozuosj desc';

OPEN V_CUR FOR SQLSTR;

end QIANFEIGL_JIAOKUANDY;

1、在Oracle中写有返回数据集的存储过程要用游标(CURSOR),在查询语句前加 OPEN V_CUR FOR

2、没有if...else...,可以用if...end if ; 形式进行判断

3、可以在Oracle中声明一个varchar2类型的变量,用它来存放SQL语句,中间可以进行字符串拼接(用"||")

上一篇:frame.origin.x 的意思和作用?


下一篇:第六篇:在SOUI中用九宫格拉伸方式显示一个图片资源