Oracle子程序就是存储在数据库中的PL/SQL块。
1. 存储过程
1.1 语法结构
create [or replace] precedure
<存储过程名> [(<参数列表>)]
is|as
<局部变量声明>
begin
<执行语句>
[exception
<异常处理>]
end;
1.2 案例
无参案例：
-- 写一个存储过程，往学生表中插入 100 条数据
create sequence seq_student;
create or replace procedure protest1
is
begin
for i in 1..100 loop
insert into student(id,name,sex) values(seq_student.nextval,'老登-' || i,'男');
end loop;
end;
-- 调用存储过程
begin
protest1();
end;
有参案例：
create or replace procedure protest2(
p_name varchar2,
p_sex varchar2,
p_age number
)
is
begin
dbms_output.put_line(p_name || '-' || p_sex || '-' || p_age);
end;
-- 调用存储过程
begin
protest2('小登', '女', 18);
end;
1.3 参数类型
存储过程的参数有三种类型：
- in 输入（不显示声明默认就是in）：接收传入的值，子程序内部不可对其进行更改
- out 输出：out声明的变量只能用于输出，会忽略传入的值，在子程序内部可以对其进行修改
- in out 输入输出
示例1：
create or replace procedure protest3(
p_name in varchar2,
p_sex in out varchar2,
p_age in out number
)
is
begin
dbms_output.put_line(p_name || '-' || p_sex || '-' || p_age);
p_sex := 'new' || p_sex;
end;
-- 调用存储过程
declare
-- 因为p_sex和p_age为in out类型，所以必须要通过变量传参
v_sex varchar2(5) := '女';
v_age number(3) := 18;
begin
protest3('小登', v_sex, v_age);
dbms_output.put_line(v_sex);
end;
示例2：
-- 根据性别或者名字查询记录，并将结果返回打印出来
create or replace procedure protest4(
p_name varchar2,
p_sex varchar2,
myresult out sys_refcursor
)
is
v_sql varchar2(200);
begin
v_sql := 'select * from student where 1 = 1';
if p_name is not null then
v_sql := v_sql || ' and name like ''%' || p_name || '%''';
end if;
if p_sex is not null then
v_sql := v_sql || ' and sex = ''' || p_sex || ''' ';
end if;
dbms_output.put_line('v_sql=' || v_sql);
open myresult for v_sql;
end;
-- 调用存储过程
declare
v_name varchar2(20) := '张三';
v_sex varchar2(4) := '男';
mycursor sys_refcursor;
v_row student%rowtype;
begin
-- a. 位置传参
-- protest4(v_name,v_sex,mycursor);
-- b. 名称传参
-- protest4(p_sex => v_sex, p_name => v_name, myresult => mycursor);
-- c. 混合使用
protest4(v_name, myresult => mycursor, p_sex => v_sex);
loop
fetch mycursor into v_row;
exit when mycursor%notfound;
dbms_output.put_line(v_row.name|| ',' || v_row.sex || ',' || v_row.age );
end loop;
close mycursor;
end;
2. 存储函数
类似Java中的方法，有返回值的PL/SQL子程序。
2.1 基本语法
create [or replace] function
<函数名> [(参数列表)]
return <数据类型>
is|as
[局部变量声明]
begin
执行语句
return 返回结果;
[exception
<异常处理>]
end;
2.2 案例
无参示例：
-- 写一个函数，获取学生名称
create or replace function func01
return varchar2
is
v_name varchar2(20);
begin
select name into v_name from student where id=201;
return v_name;
end;
调用方式：
- PL/SQL中直接调用
- select查询语句中调用
有参示例：
create or replace function func02(
p_name in varchar2,
p_sex out varchar2,
p_age in out number
)
return varchar2
is
begin
dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age);
p_sex := 'new' + p_sex;
return '成功';
end;
-- 调用存储函数
declare
v_name varchar2(10) := '存储函数测试 name';
v_sex varchar2(10) := '存储函数测试 sex';
v_age number(3) := 18;
v_result varchar2(30);
begin
v_result := func02(v_name, v_sex, v_age);
dbms_output.put_line('v_result=' || v_result || ', v_sex=' || v_sex);
end;
3. 程序包
程序包主要作用就是管理我们的存储过程和方法。
程序包由 规范 和 主体 两部分组成。
基本语法：
-- 创建规范
create [or replace] package 程序包名
is|as
[局部变量声明]
存储过程或者函数声明
end [程序包名];
-- 创建主体
create [or replace] package body 程序包名
is|as
[局部变量声明]
存储过程或者函数声明
begin
存储过程或者函数的具体实现
end [程序包名];
示例：
-- 创建规范
create or replace package pak01
is
procedure myprocedure(p_name varchar2);
function myfunction return number;
end pak01;
-- 创建规范对应主体实现
-- 主体中的方法如果在规范中声明了, 那么外包可以访问, 如果没有那么就只能被内部调用
create or replace package body pak01
is
-- myprocedure01
procedure myprocedure(p_name varchar2)
is
begin
dbms_output.put_line('执行了myprocedure方法...');
dbms_output.put_line('p_name=' || p_name);
end;
-- myfunction
function myfunction return number
is
begin
dbms_output.put_line('执行了myfunction 方法...');
return 666;
end;
-- 内部函数
function myfunction2 return number
is
begin
dbms_output.put_line('执行了myfunction 方法...');
return 666;
end;
end pak01;
-- 调用
begin
pak01.myprocedure('王五');
end;
-- 会报错找不到
-- select pak01.myfunction2 from dual;