createtable manager(
userid number notnullprimarykey,
username varchar2(4000)notnull,
age number notnull,
phone number notnull);
for循环入表测试数据
--1、声明ylm_managercreateorreplaceprocedure ylm_manager(startIndex in number,
endIndex in number)asbegin
DBMS_OUTPUT.ENABLE(buffer_size =>null);for p_userid in startIndex .. endIndex loopinsertinto manager
(userid, username, age, phone)values(p_userid,'四年级学生',18,110);
DBMS_OUTPUT.PUT_LINE('userid is '|| p_userid);endloop;end ylm_manager;--2、调用call ylm_manager(10003,10004);
output会输出
userid is 10001
userid is 10002
if else:userid奇数更新username,偶数更新phone
--1、声明ylm_manager_updatecreateorreplaceprocedure ylm_manager_update(startIndex in number,
endIndex in number)asbegin
DBMS_OUTPUT.ENABLE(buffer_size =>null);for p_userid in startIndex .. endIndex loopifmod(p_userid,2)=1thenupdate manager
set username ='四年级学生'|| p_userid
where userid = p_userid;endif;ifmod(p_userid,2)=1thenupdate manager set phone = p_userid where userid = p_userid;endif;
DBMS_OUTPUT.PUT_LINE('userid is '|| p_userid);endloop;end ylm_manager_update;--2、调用call ylm_manager_update(10,20);
procedure编译报错可在存储过程名上右键选择View,窗口底部会出现报错信息
ORA-20000:ORU-10027:buffer overflow,limit of 10000 bytes