Oracle 存储过程基础

1、创建存储过程

create or replace procedure test(var_name_1 in type,var_name_2 out type) as

--声明变量(变量名 变量类型)

begin

--存储过程的执行体

end test;

打印出输入的时间信息

E.g:

create or replace procedure test(workDate in Date) is

begin

dbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd'));

end test;

2、变量赋值

变量名 := 值;

E.g：

create or replace procedure test(workDate in Date) is

x number(4,2);

 begin

 x := 1;

end test;

3、判断语句:

if 比较式 then begin end; end if;

E.g

create or replace procedure test(x in number) is

begin

        if x >0 then

         begin

        x := 0 - x;

        end;

    end if;

    if x = 0 then

       begin

        x: = 1;

    end;

    end if;

end test;

4、For 循环

For ... in ... LOOP

--执行语句

end LOOP;

(1)循环遍历游标

create or replace procedure test() as

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

dbms_output.putline(name); 

end;

end LOOP;

end test;

(2)循环遍历数组

 create or replace procedure test(varArray in myPackage.TestArray) as

--(输入参数varArray 是自定义的数组类型，定义方式见标题6)

i number;

begin

i := 1;  --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

for i in 1..varArray.count LOOP     

dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));   

 end LOOP;

end test;

5、While 循环

while 条件语句 LOOP

begin

end;

end LOOP;

E.g

create or replace procedure test(i in number) as

begin

while i < 10 LOOP

begin    

 i:= i + 1;

end;

end LOOP;

 end test;

6、数组

首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)使用Oracle自带的数组类型

x array; --使用时需要需要进行初始化

e.g:

create or replace procedure test(y out array) is

 x array;  

 begin

x := new array();

y := x;

end test;

(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)

E.g (自定义使用参见标题4.2) create or replace package myPackage is

  -- Public type declarations type info is record(name varchar(20),     y number);

  type TestArray is table of info index by binary_integer;   --此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是

name，一个是y。需要注意的是此处使用了Index by binary_integer 编制该Table的索引项，也可以不写，直接写成：type TestArray is

table of info，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

7.游标的使用 Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor型游标(不能用于参数传递)

create or replace procedure test() is  

cusor_1 Cursor is select std_name from student where  ...;  --Cursor的使用方式1   cursor_2 Cursor;

begin

select class_name into cursor_2 from class where ...;  --Cursor的使用方式2

可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor的遍历

end test;

(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor SYS_REFCURSOR; name varhcar(20);

begin

OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通过OPEN方法来打开和赋值

LOOP

 fetch cursor into name   --SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND;              --SYS_REFCURSOR中可使用三个状态属性：                                         ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)                                         ---%ROWCOUNT(然后当前游标所指向的行位置)

 dbms_output.putline(name);

end LOOP;

rsCursor := cursor;

end test;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step                   一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

create or replace procedure autocomputer(step in number) is

rsCursor SYS_REFCURSOR;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

begin

i := 1;

get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息

OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;

LOOP

fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;

total := math + article + language + music + sport;

for i in 1..commentArray.count LOOP 

 record := commentArray(i);    

if stdId = record.stdId then  

 begin     

 if record.comment = &apos;A&apos; then     

  begin         

 total := total + 20;   

   go to next; --使用go to跳出for循环       

  end;    

end if;  

end;  

end if;

end LOOP;

<<continue>>  average := total / 5;

 update student t set t.total=total and t.average = average where t.stdId = stdId;

end LOOP;

end;

end autocomputer;

--取得学生评论信息的存储过程

create or replace procedure get_comment(commentArray out myPackage.myArray) is

rs SYS_REFCURSOR；

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

begin

open rs for select stdId,comment from out_school

i := 1;

LOOP

 fetch rs into stdId,comment; exit when rs%NOTFOUND;

record.stdId := stdId;

 record.comment := comment;

recommentArray(i) := record;

i:=i + 1;

end LOOP;

end get_comment;

--定义数组类型myArray

create or replace package myPackage is begin

type stdInfo is record(stdId varchar(30),comment varchar(1));

type myArray is table of stdInfo index by binary_integer;

end myPackage;

 

学习完以后，就想把自己前面写的一个select语句装在一个存储过程中，把查询的记录集用游标返回，试了多次都不成功，花了九牛二虎之力才弄明白，输出的游标需要引用类型，需要自己定义一个引用类型的游标。

下面是我创建了一个包，在包中完成了游标和存储过程的定义。

 

在包中定义过程
包头：
create or replace package aaatestp is
  type mycur is ref cursor;
  procedure aaatest(rq in PC_WELL_PRO_MONTHLY.Year_Mon%type, org in varchar2, outcur out mycur);
end aaatestp;
包体：
create or replace package body aaatestp is
  procedure aaatest(rq in PC_WELL_PRO_MONTHLY.Year_Mon%type, org in varchar2, outcur out mycur) as
  begin
    open outcur for
      SELECT L.Project_Name       k1,
             D.WELL_COMMON_NAME   k2,
             H.Md_Top             k3,
             H.Md_Base            k4,
             H.THICKNESS          k5,
             e.Prod_Days          K6,
             B.OIL_PROD_METHOD    K7,
             A.STROKE_LENGTH      K8,
             A.STROKE_FREQUENCY   K9,
             A.PUMP_DIAMETER      K10,
             A.PUMP_DEPTH         K11,
             B.CASING_PRES        K14,
             F.bh_flow_pressure   K15,
             B.GAS_PROD_MON       K16,
             B.GAS_PROD_YEAR      K17,
             B.GAS_PROD_CUM       K18,
             B.WATER_PROD_VOL_MON K19,
             B.WATER_PROD_YEAR    K20,
             B.WATER_PROD_CUM     K21,
             B.GAS_RELEASE_MON    K22,
             B.GAS_RELEASE_YEAR   K23,
             B.GAS_RELEASE_CUM    K24,
             B.REMARKS            K25
        FROM PC_WELL_PRO_MECH_MONTHLY A,
             PC_WELL_PRO_MONTHLY B,
             PC_WELL_PRO_MONTHLY E,
             CD_WELL_SOURCE D,
             PC_LOG_DESC_PROCEDURE H,
             (select distinct b.project_id, b.project_name, d.site_id
                from cd_project_source B, CD_SITE_SOURCE c, cd_well_source D
               where d.org_id in
                     (SELECT ORG_ID
                        FROM PC_ORGANIZATION
                       START WITH ORG_ID IN org
                      CONNECT BY PRIOR ORG_ID = PARENT_ID)
                 and b.project_id = c.project_id
                 and c.site_id = d.site_id) L,
             (select cd.bh_flow_pressure, cs.well_id
                from cd_pressure_aof_flow cd,
                     cd_pressure_aof      af,
                     CD_PRESSURE_SURVEY   cs
               where cs.pressure_survey_id = af.pressure_survey_id
                 and cd.pressure_survey_id = af.pressure_survey_id
                 and cs.well_id in
                     (select well_id
                        from cd_well_source
                       where org_id in
                             (select org_id
                                from pc_organization
                               start with org_id in org
                              connect by prior org_id = parent_id))) F
       where B.Year_Mon = rq
         and a.year_mon = B.Year_Mon
         and e.year_mon = B.year_mon
         and a.well_id = D.Well_Id
         and B.Well_Id = D.Well_Id
         and E.Well_Id = D.well_id
         and H.Well_Id = D.well_id
         and D.Site_Id = l.site_id
         and a.well_id = f.well_id; 
  end aaatest;
end aaatestp;

 

编译通过，测试也看到了结果，游标参数里面是一张表。

这个过程能不能不放在包里面呢，应该可以吧，我就做了下面一个实验

 

Code
--输出记录集的游标必须是引用类型
create or replace procedure aaatest1(rq     in PC_WELL_PRO_MONTHLY.Year_Mon%type,
                                     org    in varchar2,
                                     outcur out aaatestp.mycur) as
  cursor tem_sursor is select t.org_name, t.org_id
      from pc_organization_t t
     where t.org_level = 10;                                   
  out_row tem_sursor%rowtype;
begin
--为输出的游标赋值
  open outcur for
    SELECT L.Project_Name       k1,
           D.WELL_COMMON_NAME   k2,
           H.Md_Top             k3,
           H.Md_Base            k4,
           H.THICKNESS          k5,
           e.Prod_Days          K6,
           B.OIL_PROD_METHOD    K7,
           A.STROKE_LENGTH      K8,
           A.STROKE_FREQUENCY   K9,
           A.PUMP_DIAMETER      K10,
           A.PUMP_DEPTH         K11,
           B.CASING_PRES        K14,
           B.GAS_PROD_MON       K16,
           B.GAS_PROD_YEAR      K17,
           B.GAS_PROD_CUM       K18,
           B.WATER_PROD_VOL_MON K19,
           B.WATER_PROD_YEAR    K20,
           B.WATER_PROD_CUM     K21,
           B.GAS_RELEASE_MON    K22,
           B.GAS_RELEASE_YEAR   K23,
           B.GAS_RELEASE_CUM    K24,
           B.REMARKS            K25
      FROM PC_WELL_PRO_MECH_MONTHLY A,
           PC_WELL_PRO_MONTHLY B,
           PC_WELL_PRO_MONTHLY E,
           CD_WELL_SOURCE D,
           PC_LOG_DESC_PROCEDURE H,
           (select distinct b.project_id, b.project_name, d.site_id
              from cd_project_source B, CD_SITE_SOURCE c, cd_well_source D
             where d.org_id in
                   (SELECT ORG_ID
                      FROM PC_ORGANIZATION
                     START WITH ORG_ID IN org --'HBwrtyQASD'--to_char(org)
                    CONNECT BY PRIOR ORG_ID = PARENT_ID)
               and b.project_id = c.project_id
               and c.site_id = d.site_id) L
     where B.Year_Mon = rq
       and a.year_mon = B.Year_Mon
       and e.year_mon = B.year_mon
       and a.well_id = D.Well_Id
       and B.Well_Id = D.Well_Id
       and E.Well_Id = D.well_id
       and H.Well_Id = D.well_id
       and D.Site_Id = l.site_id;
       --跳转标志，<<>>
       <<ReadSur>>
  --练习游标的isopen和found属性，isopen判断游标是否打开，found判断是否为空
  if tem_sursor%isopen then 
       fetch tem_sursor into out_row;
        if tem_sursor%found then
       dbms_output.put_line(out_row.org_name);
    else
      dbms_output.put_line('游标为空');
    end if;
  else
    open tem_sursor;
    goto ReadSur;
  end if;
close outcur;
end aaatest1;

 

过程从包中移了出来，但依然用了包中定义好的游标，其他基本没变，测试了一下，一样可以将整个记录集用游标返回，不错。

然后就顺手练习了一下条件语句，跳转语句，游标的定义赋值读值以及isopen、found属性，乱七八糟的全放在例子里面了，测试都看到了想要的效果，挺开心的就为学了这点皮毛。