oracle存储过程、声明变量、for循环--后期添加游标 if 、decode、 case when

oracle存储过程、声明变量、for循环

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 = 'A' 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;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/squirrelrao/archive/2008/07/11/2639571.aspx https://www.cnblogs.com/yw0219/p/5939558.html

应用：

oracle游标，存储过程，for循环，loop合一

create or replace procedure libsys.add_money(money out number) ascursor cur is

select amount from libsys.money_record;

begin

money:=0;

for amounts in cur

loop

money:=money+amounts;

end loop;

end add_money;

自己使用过的案列

declare
        --类型定义
        cursor c_sursorA
       is
        select node_id,flow_id from itouch_workflow.FLOW_NODES where node_name='不予许可通知';
        --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_sursorA%rowtype;
 begin
        for c_row in c_sursorA loop
                
                INSERT into itouch_workflow.FLOW_NODE_RESULTS(node_id,result_item_id,result_item_alias,id) 
                values(c_row.node_id,'3','不予许可',RAWTOHEX(sys_guid()));


        dbms_output.put_line(c_row.node_id);
                DBMS_OUTPUT.ENABLE(buffer_size => null) ;
       end loop;

 end;

DECLARE CURSOR c_job9 IS
select DISTINCT(ent_code) from (
select distinct t.* from ent_license t inner join ent_license f on t.ent_code = f.ent_code where t.lic_code <> f.lic_code and t.ent_code is not null order by t.ent_code
) where ROWNUM<810;
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
  c_row c_job9 % rowtype ;
BEGIN
  FOR c_row IN c_job9 loop
  --dbms_output.put_line (c_row.ENT_CODE) ;
  declare
  i int ;
  j int :=1;
  typez nvarchar2(100);
  IDZ nvarchar2(100);
  typenum int :=1;
  typeentcode nvarchar2(50);
begin
dbms_output.put_line (c_row.ENT_CODE) ;
typeentcode :=sys_guid();
SELECT count(*) into i FROM ITOUCH_FDA_ENT.ENT_TYPE_RELATION T WHERE T.ENT_CODE = c_row.ENT_CODE;
SELECT count(*) into j FROM ITOUCH_FDA_ENT.ENT_LICENSE T WHERE T.ENT_CODE = c_row.ENT_CODE;
 if(j=2) then
   begin
     if(i=2) then
    begin

   dbms_output.put_line(i||'*****'||j);
dbms_output.put_line (c_row.ENT_CODE) ;
  --处理代码块
SELECT SUBSTR (ENT_TYPE_ID,1,4) into typez FROM ITOUCH_FDA_ENT.ENT_LICENSE T WHERE T.ENT_CODE = c_row.ENT_CODE and ROWNUM<2;
dbms_output.put_line(typez);

--查询count =1 才进行获取ID再使用各表id进行修改ent_code
--SELECT COUNT(*) into typenum FROM ITOUCH_FDA_ENT.ENT_TYPE_RELATION T WHERE T.ENT_CODE = c_row.ENT_CODE and
-- ENT_TYPE_ID like ''''||typez||'%''';
--SUBSTR (acc_unit_name,1,"LENGTH" (acc_unit_name) - 1) 
-- '''%'||typez||'%''';
--根据各类型进行分离扩展表。
dbms_output.put_line(typez||'if(typenum=1)开始');
  --if(typenum=1)
  --THEN
BEGIN
     dbms_output.put_line(i||'*****'||j);
dbms_output.put_line('jingrule开始'||typez);
--找出不同类型企业
  if (typez='1204') then 
    BEGIN
      SELECT COUNT(*) into typenum FROM ITOUCH_FDA_ENT.ENT_TYPE_RELATION T WHERE T.ENT_CODE = c_row.ENT_CODE and ENT_TYPE_ID like '1204%';

      if typenum=1 THEN
        BEGIN
          --食品经营分离操作
          dbms_output.put_line('进入了1204');
          dbms_output.put_line("RAWTOHEX"(sys_guid()));
          INSERT INTO ENT_BASIC (SELECT RAWTOHEX(sys_guid()),typeentcode,
ENT_NAME,PARENT_ENT_CODE,ENT_DESC,ORG_CODE,BIZ_LIC_NO,TAX_REG_NO,ENT_PROPERTY_ID,ENT_PROPERTY_NAME,ACCOUNT_TYPE_ID,
ACCOUNT_TYPE_NAME,REG_ADDR,REG_ADDR_POSTAL_CODE,ENT_LEGAL_REP_ID,ENT_LEGAL_REP,ENT_PRINCIPAL_ID,ENT_PRINCIPAL,
ENT_LINKMAN_ID,ENT_LINKMAN,ENT_TEL,ENT_FAX,ENT_EMAIL,EST_DATE,REG_CAP,UNIT_ID,UNIT_NAME,AREA_CODE,AREA_NAME,
AREA_TYPE_ID,AREA_TYPE_NAME,LONGITUDE,LATITUDE,CA_ID,CA_DN,ENT_STATE,ENT_PASSWORD,ATTACHMENT_CODE,IS_RISK,
REMARK,PARENT_ENT_NAME,LEGAL_REP_REG_NO,UNITE_CREDIT_NO,CORP_REG_NO,ENT_TAG,AUTH_CODE_TYPE_ID,ENT_DC_TIMES 
FROM ENT_BASIC WHERE ENT_CODE =c_row.ENT_CODE );
          --测试修改一条测试表数据
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE
and  ENT_TYPE_ID like '1204%';
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1204%';
          UPDATE ENT_LICENSE set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1204%';
          UPDATE ENT_PERSON_JOB set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1204%';
          UPDATE ENT_EQUIP set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1204%';
          UPDATE ENT_FOOD_BIZ_EXT set  ent_code =typeentcode where ent_code=c_row.ENT_CODE ;
          
        end;
      end if;
    
    end;
  elsif (typez='1102') then 
    BEGIN
      SELECT COUNT(*) into typenum FROM ITOUCH_FDA_ENT.ENT_TYPE_RELATION T WHERE T.ENT_CODE = c_row.ENT_CODE and ENT_TYPE_ID like '1102%';

      if typenum=1 THEN
        BEGIN
          --药品零售分离操作
          dbms_output.put_line('进入了1102');
          INSERT INTO ENT_BASIC (SELECT RAWTOHEX(sys_guid()),typeentcode,
ENT_NAME,PARENT_ENT_CODE,ENT_DESC,ORG_CODE,BIZ_LIC_NO,TAX_REG_NO,ENT_PROPERTY_ID,ENT_PROPERTY_NAME,ACCOUNT_TYPE_ID,
ACCOUNT_TYPE_NAME,REG_ADDR,REG_ADDR_POSTAL_CODE,ENT_LEGAL_REP_ID,ENT_LEGAL_REP,ENT_PRINCIPAL_ID,ENT_PRINCIPAL,
ENT_LINKMAN_ID,ENT_LINKMAN,ENT_TEL,ENT_FAX,ENT_EMAIL,EST_DATE,REG_CAP,UNIT_ID,UNIT_NAME,AREA_CODE,AREA_NAME,
AREA_TYPE_ID,AREA_TYPE_NAME,LONGITUDE,LATITUDE,CA_ID,CA_DN,ENT_STATE,ENT_PASSWORD,ATTACHMENT_CODE,IS_RISK,
REMARK,PARENT_ENT_NAME,LEGAL_REP_REG_NO,UNITE_CREDIT_NO,CORP_REG_NO,ENT_TAG,AUTH_CODE_TYPE_ID,ENT_DC_TIMES 
FROM ENT_BASIC WHERE ENT_CODE =c_row.ENT_CODE );
          --测试修改一条测试表数据
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE
and  ENT_TYPE_ID like '1102%';
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1102%';
          UPDATE ENT_LICENSE set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1102%';
          UPDATE ENT_PERSON_JOB set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1102%';
          UPDATE ENT_EQUIP set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1102%';
          UPDATE ent_drug_biz_ext set  ent_code =typeentcode where ent_code=c_row.ENT_CODE ;
          
        end;
      end if;
      
    end;
elsif (typez='1201') then 
    BEGIN
      SELECT COUNT(*) into typenum FROM ITOUCH_FDA_ENT.ENT_TYPE_RELATION T WHERE T.ENT_CODE = c_row.ENT_CODE and ENT_TYPE_ID like '1201%';

      if typenum=1 THEN
        BEGIN
          --食品生产分离操作
          dbms_output.put_line('进入了1201');
          
          dbms_output.put_line("RAWTOHEX"(sys_guid()));
          INSERT INTO ENT_BASIC (SELECT RAWTOHEX(sys_guid()),typeentcode,
ENT_NAME,PARENT_ENT_CODE,ENT_DESC,ORG_CODE,BIZ_LIC_NO,TAX_REG_NO,ENT_PROPERTY_ID,ENT_PROPERTY_NAME,ACCOUNT_TYPE_ID,
ACCOUNT_TYPE_NAME,REG_ADDR,REG_ADDR_POSTAL_CODE,ENT_LEGAL_REP_ID,ENT_LEGAL_REP,ENT_PRINCIPAL_ID,ENT_PRINCIPAL,
ENT_LINKMAN_ID,ENT_LINKMAN,ENT_TEL,ENT_FAX,ENT_EMAIL,EST_DATE,REG_CAP,UNIT_ID,UNIT_NAME,AREA_CODE,AREA_NAME,
AREA_TYPE_ID,AREA_TYPE_NAME,LONGITUDE,LATITUDE,CA_ID,CA_DN,ENT_STATE,ENT_PASSWORD,ATTACHMENT_CODE,IS_RISK,
REMARK,PARENT_ENT_NAME,LEGAL_REP_REG_NO,UNITE_CREDIT_NO,CORP_REG_NO,ENT_TAG,AUTH_CODE_TYPE_ID,ENT_DC_TIMES 
FROM ENT_BASIC WHERE ENT_CODE =c_row.ENT_CODE );
          --测试修改一条测试表数据
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE
and  ENT_TYPE_ID like '1201%';
          UPDATE ENT_TYPE_RELATION set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1201%';
          UPDATE ENT_LICENSE set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1201%';
          UPDATE ENT_PERSON_JOB set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1201%';
          UPDATE ENT_EQUIP set  ent_code =typeentcode where ent_code=c_row.ENT_CODE and  ENT_TYPE_ID like '1201%';
          UPDATE ent_food_prod_ext set  ent_code =typeentcode where ent_code=c_row.ENT_CODE ;
          



          --UPDATE temp_ent_code set nums=(SELECT max("TO_NUMBER"(nums))+1 from temp_ent_code);
        end;
      end if;
      DBMS_OUTPUT.ENABLE(buffer_size => null) ;
    end;

  --else i:=20;
 
  end if;

DBMS_OUTPUT.ENABLE(buffer_size => null) ;
END;
  --end if;
--找不同企业if结束


   end;
  end if;
  end;
  end if;

end;
END loop ;

END ;

View Code

3游标if

--select * from FLOW_NODES where FLOW_ID in (select FLOW_ID from FLOW_NAME where FLOW_STATE=0) and 
 --(node_name ='不准予许可' or node_name='不予许可通知');

declare
        --类型定义
        cursor c_sursorA
       is
        select * from FLOW_NODES where FLOW_ID in (select FLOW_ID from FLOW_NAME where FLOW_STATE=0) and 
                (node_name ='不准予许可' or node_name='不予许可通知') ;
        --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_sursorA%rowtype;

 begin
      for c_row in c_sursorA loop
      DECLARE i int ;j int ;
      BEGIN
        
        SELECT count(*) INTO i from FLOW_NODE_RESULTS where NODE_ID=c_row.node_id and RESULT_ITEM_ID='3';
        
        
               if(i=0) then
                    dbms_output.put_line('+999999999+');
                    --插入结果选项
                    INSERT into FLOW_NODE_RESULTS(node_id,result_item_id,result_item_alias,id) 
                    values(c_row.node_id,'3','不予许可',RAWTOHEX(sys_guid()));
                    end if;

        SELECT count(*) INTO j from FLOW_NODE_ACTIONS where NODE_ID=c_row.node_id and ACTION_ID='402881f651480ea701514843e3200000';
                 if(j=0) then
                    dbms_output.put_line('+888+');
                    --插入结论
                    INSERT into  FLOW_NODE_ACTIONS(node_id,ACTION_ID,"ID") 
                    values(c_row.node_id,'402881f651480ea701514843e3200000',RAWTOHEX(sys_guid()));
                    end if;

        dbms_output.put_line(c_row.node_id||'---结果'||i||'+节点动作结论+'||j);
        DBMS_OUTPUT.ENABLE(buffer_size => null) ;
      END;

       end loop;

 end;

View Code

4。3中if句

一、单个IF
  1、if
 
 if a=...  then
 .........
 end if;
 
 2、if else
 
 if a=... then
 ......
 else
 ....
 end if;
 
3、多个IF
 
 if a=..  then
 ......
 elsif a=..  then
 ....
 end if;     
 这里中间是ELSIF 而不是ELSE IF  这里需要特别注意

4.decode函数

DECODE的语法：

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

View Code

5.case when

case when a='1'then 'xxxx'
     when a='2' then 'ssss'
else
　　'zzzzz'
end as



注意

1、以CASE开头，以END结尾 
2、分支中WHEN 后跟条件，THEN为显示结果 
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加 
4、END 后跟别名

View Code