存储过程和函数

概述：
ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。存储过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

存储过程

存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。

--创建存储过程的语法
create or replace procedure 存储过程名字 [(参数 in| out|in out 参数的数据类型)]
as | is
声明部分：
begin
plsql代码块
exception
异常处理
end;

-- 无参数存储过程
create or replace procedure p1
is
	cursor cur_emp is select * from emp;
begin
	for v_emp in cur_emp loop
	dbms_output.put_line(v_emp.ename);
	end loop;
end;

/*
如果使用/调用存储过程
*/
-- 1、在plsql块中调用
begin
 p1;-- p1();
end;
-- 2、call 命令调用
call p1();
-- 3、 execute 命令调用  （属于sqlplus命令） 需要通过cmd小黑窗执行 记得 set serverout on
execute p1();

/*
查询存储过程 user
select * from user_objects  --数据字典
*/

/*
删除存储过程
drop procedure 存储过程的名字；
*/
drop procedure  p1;
/*
参数的三种模式
IN  用于接受调用程序的值。默认的参数模式
OUT  用于向调用程序返回值 
IN OUT  用于接受调用程序的值，并向调用程序返回更新的值
*/
-- IN  用于接受调用程序的值。默认的参数模式
--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息
create or replace procedure p2(v_deptno in number)-- 这里的数据类型不需要加长度，这里in可以省略，默认为in
is
begin
 for v_emp in (select * from emp where deptno = v_deptno) loop
 	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
 end loop;
end;

-- 使用
begin
	p2(10);
end;

begin
	p2(v_deptno => 10);
end;

declare
	v number :=20;
begin
	p2(v); 
end;

create or replace procedure p2_2(v_deptno1 in number,v_deptno2 in number)
is
begin
 	dbms_output.put_line(v_deptno1||','||v_deptno1);
end;

begin
	p2_2(10,20);
end;

begin
	p2(v_deptno2 => 10,v_deptno1 => 20);-- 顺序可以打乱
end;

declare
	v1 number :=20;
	v2 number :=20;
begin
	p2_2(v1,v2); 
end;

-- OUT  用于向调用程序返回值 
--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
create or replace procedure p3(v_empno in emp.empno%type,v_emp out emp%rowtype)
is
begin
	select * into v_emp from emp where empno = v_empno;
end;

-- 使用
declare
	v_e emp%rowtype;
begin
	p3(7369,v_e);
	dbms_output.put_line(v_e.empno||','||v_e.ename);
end;

--IN OUT  用于接受调用程序的值，并向调用程序返回更新的值
--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
create or replace procedure p4(v_emp in out emp%rowtype)
is
begin
	select * into v_emp from emp where empno = v_emp.empno;
end;
-- 使用
declare
	v_e emp%rowtype;
begin
	v_e.empno := 7369;
	p4(v_e);
	dbms_output.put_line(v_e.empno||','||v_e.ename);
end;


-- 其他用户调用scott用户的存储过程
-- 创建用户
create user test6 identified by root;
grant connect,resource to test6;
-- 使用test6登录
begin
  scott.p1();
end;
-- 不能访问

-- 使用 scott用户授权才可以
grant execute on p1 to test1;

函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

--创建函数的语法
create [or replace] function 函数名称[(形式参数 参数类型,...)]
return 返回值类型
is
   声名变量;
begin
  plsql代码块;
  return 返回值;--return后面的内容不执行
end;

-- 无参数的函数
create or replace function  f1
return varchar2
is

begin
	return '我爱你';
end;	

-- 查看函数
select * from user_objects where object_ type = 'FUNCTION';

-- 使用函数，调用函数
1、用在sql语句中
select f1 from dual;
select f1 from emp;
select * from emp where f1()='我爱你';

2、在plsql块中使用
begin
	dbms_output.put_line(f1);
end;

-- 删除函数
-- drop function 函数名
drop function f1;


-- 有参数的函数
-- 编写函数，接受一个数，返回1-这个数的和。
create or replace function  f2(n number) 
return number
is
	v_sum number:=0;
begin
	for i in 1..n loop
		v_sum :=v_sum+i;
	end loop;
	
	return v_sum;
end;	

-- 使用
select f2(3) from dual;


-- 多个函数的写法
-- 编写函数，接受2个值，返回最大值
create or replace function  f3(n1 number,n2 number) 
return number
is
--	v number;	
begin
	if n1>n2 then
	--	v:=n1;
		return n1;
	else
	-- v:=n2;
		return n2;
	end if;
	--return v;
end;	

-- 使用
select f3(2,3) from dual;

更多推荐

存储过程和函数——PL/SQL教程（七）