Oracle存储过程

1.储存过程简单写法

1.1 有参数有返回值的存储过程

create or replace function get_table_item(bpoId in varchar2) return varchar2 is
  column_val varchar2(2200);
begin
   select p.item_name into column_val  from CG_CONTRACT_INFO_02 p where p.bpo_id=bpoId and rownum=1;
  return(column_val);
end get_table_item;

1.2 案例实战

1.有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
这条sql，写出来如下：

update student set s_grade=s_grade+1

分析：

如果我们直接运行运行这条sql，因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student;
  WHILE i<=total LOOP
    UPDATE student SET grade=grade+1 WHERE s_no=i;
    i := i + 1;
    IF i >= 100000 THEN
      COMMIT;
    END IF;
  END LOOP;
  dbms_output.put_line('finished!');
END;

数据来源-@coward

2.输出10的整倍数(此过程可能运用在向某张表插入数据，但是某一个字段按照某种规律变动的)

create or replace procedure test_table is
  i number;
begin
  i:=10;
  loop 
    dbms_output.put_line(i);
    i:=i+10;
    if i>100 then
      exit;
    end if;
  end loop;    
end test_table;

结果：

10
20
30
40
50
60
70
80
90
100

3.输出某张表里面某个字段(此过程可能运用在，查询某张表，满足某个条件的数据)

create or replace procedure test_table is
begin
  for idx in(select TYPE_ID from SADP_MD_COMMON_TYPES tys where PARENT_SID=1428097 and TYPE_ID in(10,20)) loop
    dbms_output.put_line('id：'||idx.TYPE_ID);
  end loop; 
  commit; 
end test_table;

显示结果：

id：20
id：10