Oracle 存储过程语法
1. 创建表（测试数据准备）
-- 创建用户表
create table TT_USER
(USERID NUMBER(10),USERNAME VARCHAR2(255),PASSWORD VARCHAR2(255),SEX VARCHAR2(1)
);INSERT INTO TT_USER VALUES (101, 'zhang', '111', '1');
INSERT INTO TT_USER VALUES (102, 'han', '222', '2');
INSERT INTO TT_USER VALUES (103, 'hui', '333', '2');
INSERT INTO TT_USER VALUES (104, 'fang', '444', '1');
INSERT INTO TT_USER VALUES (105, 'li', '555', '2');
INSERT INTO TT_USER VALUES (106, 'cheng', '666', '1');
INSERT INTO TT_USER VALUES (107, 'zhao', '777', '0');select * from TT_USER;-- 创建学生表
CREATE TABLE student(sids number(11) NOT NULL,sname varchar(10),score float,sex char(1),addr varchar(10));INSERT INTO student VALUES (101, 'zhang', 98, '2', '苏州');
INSERT INTO student VALUES (102, 'han', 69, '1', '西安');
INSERT INTO student VALUES (103, 'hui', 72, '1', '苏州');
INSERT INTO student VALUES (104, 'fang', 100, '2', '苏州');
INSERT INTO student VALUES (105, 'li', 88, '1', '北京');
INSERT INTO student VALUES (106, 'cheng', NULL, '1', '北京');
INSERT INTO student VALUES (107, 'zhao', NULL, '3', '北京');select * from student;-- 创建学生表1
CREATE TABLE student1(sids number(11) NOT NULL,sname varchar(10),score float,sex char(1),sage number(10),addr varchar(10));INSERT INTO student1 VALUES (101, 'zhang', 98, '2', 15, '苏州');
INSERT INTO student1 VALUES (102, 'han', 69, '1', 30, '西安');
INSERT INTO student1 VALUES (103, 'hui', 72, '1', 25, '苏州');
INSERT INTO student1 VALUES (104, 'fang', 100, '2', 35, '苏州');
INSERT INTO student1 VALUES (105, 'li', 88, '1', 45, '北京');
INSERT INTO student1 VALUES (106, 'cheng', NULL, '1', 12, '北京');
INSERT INTO student1 VALUES (107, 'zhao', NULL, '3', 23, '北京');
select * from student1;-- 创建用户表
CREATE TABLE user_info(user_id number(11) NOT NULL,user_name varchar(10),user_password varchar(20),date_time date,create_time timestamp,update_time timestamp);
-- drop table user_info;insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(8,'李一','123456',to_date('2023-01-02','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(2,'王五','123456',to_date('2015-12-20','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(3,'田七','123456',to_date('2018-09-22','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(4,'赵八','123456',to_date('2021-10-21','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(5,'王二','123456',to_date('2020-05-20','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(6,'李四','123456',to_date('2022-05-16','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(7,'赵云','123456',to_date('2023-12-23','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(8,'黄忠','123456',to_date('2017-12-28','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);select * from user_info;2. 创建存储过程
2.1 MyDemo01 - 无参传递实例
-- 无参数传递，打印信息
create or replace procedure MyDemo01 is
begindbms_output.put_line('hello word, my name is stored procedure');
end;-- 调用存储过程操作(sql窗口)
call MyDemo01();2.2 myDemo02 - 有参传递实例1
-- 有参数传递 输入参数 in
create or replace procedure myDemo02(name in varchar,age in int) is
begindbms_output.put_line('name='||name||', age='||age);
end MyDemo02;-- 调用存储过程操作
call MyDemo02('zhu',20);2.3 myDemo03 - 有参传递实例2
-- 有参数传递 输出参数 out
create or replace procedure myDemo03(name out varchar,age in int) is
begindbms_output.put_line('age='||age);select 'zhu' into name from dual;
end;-- 调用存储过程操作
declarename varchar(10);age int;
beginmyDemo03(name=>name,age=>25);dbms_output.put_line('name='||name);
end;myDemo03 输出结果：
注：in 表示输入参数；out 表示输出参数
2.4 myDemo04 - 异常处理存储过程
-- 异常处理 exception
create or replace procedure myDemo04
as
age int;
beginage:=1/0;dbms_output.put_line(age);--异常exception when others thendbms_output.put_line('error');
end;-- 调用存储过程操作
call myDemo04();
-- 程序分析：程序出现异常，将打印 error2.5 myDemo05 - while 循环存储过程实例
-- while 循环
create or replace procedure myDemo05
asn_count number := 0;
beginwhile n_count < 5 loopdbms_output.put_line(n_count);n_count := n_count + 1;end loop;
end;-- 调用存储过程操作
call myDemo05();输出结果：
0
1
2
3
4
2.6 myDemo06 - for 循环存储过程实例
-- for 循环
create or replace procedure myDemo06
as
beginFOR USE in (select * from student1) loopif (USE.sids<108) thendbms_output.put_line(USE.sname);end if;end loop;
end;-- 调用存储过程操作
beginmyDemo06;
end;输出结果：
aaa
李太白
战三
张三
李四
王五
赵六
朱八
2.7 mydemo07 - 增删改查存储过程实例
-- 增删改
create or replace procedure mydemo07(sidss in int, sname in varchar,score in int, sex in char,sage in int,addr in varchar)
as
begin--insert into student1 VALUES(sids,sname,score,sex,sage,addr);UPDATE student1 t SET t.sname='狗蛋' WHERE t.sids=sidss ;--DELETE student1 t WHERE t.sids=sids ; commit; --提交
end MyDemo07;-- 调用存储过程操作
begin
mydemo07(101, '', 120, '1', 20, '');
end;2.8 myDemo08 - if 语句
-- 根据用户id判断用户是否存在
create or replace procedure myDemo08(pid in number,ret out number)
as
flag number;
beginselect count(1) into flag from student1 where sids = pid;if flag = 1 thendbms_output.put_line(pid||'用户存在');ret:=1; -- 如果用户存在就把ret设为1elsedbms_output.put_line(pid||'用户不存在');ret:=0; -- 如果用户不存在就把ret设为0end if;
end;-- 测试 myDemo08 存储过程
declareinput number(11):=101;ret number; -- 声明一个变量ret，类型是number，用来存储过程的输出值
beginMyDemo08(input,ret); -- 获取到过程的输出值存储在ret中dbms_output.put_line('存储过程返回值：' ||ret);
end;输出结果：
101用户存在
存储过程返回值：1
3. 存储过程常用语法实例
-- when
declare i int:= 0;
beginloopi:=i+1;dbms_output.put_line(i);EXIT WHEN i > 3;END LOOP;
end;
-- 输出结果：1234-- 使用Loop+游标的时候，取游标当中的值，必须重新赋值一遍，要不然会报错。
declarecursor user isselect * from user_info;user1 user_info%rowtype;
beginopen user;loopfetch user into user1;exit when user%notfound;dbms_output.put_line('用户名称：' || user1.USER_NAME);dbms_output.put_line('用户年龄：' || user1.user_id);end loop;close user; --关闭游标
end;
-- 输出结果：用户名称：李六用户年龄：1用户名称：李一用户年龄：8用户名称：王五用户年龄：2用户名称：田七用户年龄：3用户名称：赵八用户年龄：4用户名称：王二用户年龄：5用户名称：李四用户年龄：6用户名称：赵云用户年龄：7用户名称：黄忠用户年龄：8
-- WHILE语句:在执行之前，首先要判断条件表达式的值是否为true，true则执行循环体，否则退出WHILE循环，继续执行循环后面的代码。
declarei int := 0;
beginwhile i < 3 loopi := i + 1;dbms_output.put_line(i);end loop;
end;-- 实例测试
declarecursor user isselect * from user_info;user1 user_info%rowtype;
beginopen user;fetch user into user1;while(user%found)loopdbms_output.put_line('用户名称：' || user1.USER_NAME);fetch user into user1;end loop;
end;
-- 注意：%found %notfound 用法--FOR语句是一个可提前设置循环次数的循环控制语句，它有一个循环计数器，通常是一个整型变量，通过这个计数器来控制循环次数
for A IN (reverse) B...C LOOPD;END LOOP;--A: 表示一个变量，通常为证书类型，用来作为计数器，默认值是递增的，当循环当中使用reverse关键字时，就会循环递减。
--B: 计数器下线值，当计数器的值小于下限值的时候，终止循环。
--C: 计数器上线值，当计数器的值大于上限值的时候，终止循环。
--D: 循环体。declare i int := 0;
beginfor i IN reverse 1..5 LOOPdbms_output.put_line(i);END LOOP;
end;-- 配合游标实例测试
declarecursor user isselect * from user_info;
beginfor user1 in user loopdbms_output.put_line('用户名称：'|| user1.USER_NAME);dbms_output.put_line('用户ID：'|| user1.USER_ID);end loop;
end;游标实例测试 - 输出结果：
