Oracle存储过程

建表语句

create table manager(
userid number not null primary key,
username varchar2(4000) not null,
age number not null,
phone number not null  
);

for循环入表测试数据

--1、声明ylm_manager
create or replace procedure ylm_manager(startIndex in number,
                                        endIndex   in number) as
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  for p_userid in startIndex .. endIndex loop
    insert into manager
      (userid, username, age, phone)
    values
      (p_userid, '四年级学生', 18, 110);
    DBMS_OUTPUT.PUT_LINE('userid is ' || p_userid);
  end loop;
end ylm_manager;
--2、调用
call  ylm_manager(10003,10004);

• output会输出

userid is 10001
userid is 10002

if else：userid奇数更新username，偶数更新phone

--1、声明ylm_manager_update
create or replace procedure ylm_manager_update(startIndex in number,
                                               endIndex   in number) as
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  for p_userid in startIndex .. endIndex loop
    if mod(p_userid, 2) = 1 then
      update manager
         set username = '四年级学生' || p_userid
       where userid = p_userid;
    end if;
    if mod(p_userid, 2) = 1 then
      update manager set phone = p_userid where userid = p_userid;
    end if;
    DBMS_OUTPUT.PUT_LINE('userid is ' || p_userid);
  end loop;
end ylm_manager_update;
--2、调用
call ylm_manager_update(10,20);

procedure编译报错可在存储过程名上右键选择View，窗口底部会出现报错信息

ORA-20000:ORU-10027:buffer overflow,limit of 10000 bytes

• 解决方案

1. 重新设置打印字符
   SQL>set serveroutput on size 1000000
2. 在begin后面加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，表示输出buffer不受限制