首页 > 代码库 > Oracle存储过程写法

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语句,中间可以进行字符串拼接(用"||")

Oracle存储过程写法