oracle存储过程

有入参无返回的存储过程

create or replace procedure pro1
(title1 in t_forum.title%type,author1 in t_forum.author%type )
as
begin
  insert into t_forum(title,author)values(title1,author1);
exception
  when others then
    dbms_output.put_line('出错');
 end;
 
begin
  pro1('二手','哈哈作者');
  end;
===============================================在java中用call关键字调用存储过程

有入参有一个返回值的存储过程

create or replace procedure pro2
(id1 out t_user.id%type,name1 in t_user.name%type,pwd1 in t_user.pwd%type)
as
begin 
 insert into t_user(name,pwd)values(name1,pwd1);
exception
  when others then
    dbms_output.put_line('出错');
 end;

declare 
 id1 t_user.id%type;
begin
 pro2(id1,'aaa','123')
 dbms_output.put_line('成功'，||id1)
end

包含动态语句的存储过程

procedure QueryMainDoctorWorkload(paraKSRQ00    in VARCHAR2, --开始日期
                                         paraJSRQ00    in VARCHAR2, -- 终止日期
                                         cur_ReportData out ref_cur)is
 VSQLSTR varchar2(2000);
 sCondetion varchar2(2000);

  begin
   if paraKSRQ00 is not null then
     sCondetion:=sCondetion||' and A.CJRQ00 between '''||paraKSRQ00||''' and '''||paraJSRQ00||''' ' ;
   end if;


VSQLSTR:='select (select   zwxm00 from bm_ygbm00 where ygbh00=ksys00) ZWXM00,';
     declare cursor cur_bmmc00 is
 select  REGEXP_SUBSTR(value0, '[^,]+', 1, l)bmmc00 from xt_xtcs00, (SELECT LEVEL l FROM dual CONNECT BY LEVEL <=20) b
    where name00='TJZXZJYSGZLTJBMMC'and  l <=  LENGTH(value0) -LENGTH(REPLACE(value0,',','')) + 1 ;--要统计的部门

    begin
     for vrow in cur_bmmc00 loop
            VSQLSTR:=VSQLSTR||' sum(case when BM =  '''||vrow.bmmc00||''' then 1 else 0 end) "'||vrow.bmmc00||'",' ;
     end loop;
    end;

VSQLSTR:=VSQLSTR||' sum(case when ksys00=shysid then 1 else 0 end)"报告解读" ,
            (select  count(1) from sy_pe_ywsj_tjz000 where zjysid =ksys00) "报告主检"
            from
            (
            select
             (select  bmmc00 from bm_bmbm00 where a.bmbh00=bmbh00) BM,
             a.*
            from sy_pe_ywsj_tjfkjc  a where 1=1  '||sCondetion||')

            group by ksys00';

    open cur_ReportData for VSQLSTR;


end;

 VSQLSTR := 'insert into SY_PE_YWSJ_TJKSWJ (LSID00, LB0000, TJH000, BMBH00, WJID00, WJXM00, CZY000)
            select SQ_PE_YWSJ_TJKSWJ_LSID00.nextval, 1, ' ||
                 paraTJH000 || ', ' || paraBMBH00 ||
                 ', WJID00, WJXM00, nvl(''' || paraCZY000 ||
                 ''', 0) from BM_BMWJ00 A where BMBH00 = ' || paraBMBH00 || '
            and YXBZ00 = ''1'' and not exists (select 1 from SY_PE_YWSJ_TJKSWJ where TJH000 = ' ||
                 paraTJH000 || ' and BMBH00 = ' || paraBMBH00 ||
                 ' and WJID00 = A.WJID00) ' || VSQL000;
      execute immediate VSQLSTR;