Oracle子程序

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;

调用方式：

1. PL/SQL中直接调用
2. 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;