存储过程与函数

存储过程(注重行为)

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集（pl/sql子程序），它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

创建存储过程

一、语法格式

create [or replace] procedure 存储过程名[(形参列表 [in|out|in out] 形参数据类型)]
{is|as}
	声明部分;
begin
	pl/sql代码块;
exception
	异常处理语句;
end;

• or replace表示如果有重名存储过程则覆盖，如果没有则创建
• 注意形参数据类型不能给具体精度即不能给varchar2(21)后的（21）部分

二、无参数存储过程

create or replace procedure p1 is
begin
	dbms_output.put_line('hello world');
end;

在vs code中运行后结果如下：

三、带参数存储过程

• in——从外部进入存储函数，且不能被存储过程改变(在存储过程中类似于一个常量被处理)
  （1）错误示范-1（在存储过程中给in类型参数赋值）

create or replace procedure p1(a IN NUMBER) is
begin
	--该语句不被允许
	a:=1;
	--打印a
	dbms_output.put_line(a);
end;

（2）正确示范

--接收一个雇员号，输出雇员姓名与雇员职位
create OR REPLACE PROCEDURE p1(v_empno IN EMP.EMPNO%TYPE)
IS
	--声明一个变量
	A EMP%ROWTYPE;
BEGIN
	--赋值
	SELECT * INTO A FROM EMP WHERE EMPNO=v_empno;
	--输出
	dbms_output.put_line('雇员姓名：'||a.ename||', 雇员职位：'||a.job);
END;

• out——从存储过程到外部pl/sql程序块，可被存储过程中相关内容改变(可以看作一个从存储过程中抛出的变量)，out变量在外部必须有相应变量接应

--抛出存储过程变量值
create OR replace PROCEDURE p1(n OUT VARCHAR2) IS
	a VARCHAR2(200):='hello world';
BEGIN
	n:=a;
END;

• in out——可进可出，外部程序块与内部存储过程均可改变

----根据用户输入的部门号，输出部门相关信息
create OR replace PROCEDURE p1(n IN OUT DEPT%ROWTYPE) IS
BEGIN
	SELECT * INTO n FROM DEPT WHERE DEPTNO=n.DEPTNO;
END;
/
--调用
DECLARE
	n DEPT%ROWTYPE;
BEGIN
	n.DEPTNO:=&请输入部门号;
	--注意此处参数为n而不是n.deptno
	p1(n);
	dbms_output.put_line(n.DEPTNO||', '||n.dname||', '||n.loc);
END;

调用存储过程

一、pl/sql中调用

begin
--带参数存储过程调用
存储过程名(实参1,实参2,...,实参n);
--不带参数存储过程调用
存储过程名;
存储过程名();
end;

二、call调用
关键部分代码如下：

--call有个特别点：即使存储过程无参数传递但存储过程名后()仍不可省略
call 存储过程名();

三、execute/exec调用（cmd窗口中）
(1)win+R打开命令提示符登录sqlplus

(2)打开serveroutput服务，代码如下：

set serveroutput on;

(3)输入exec/execute 存储过程名（）；

删除存储过程

drop procedure 存储过程名;

其他用户使用他人存储过程

（1）登录存储过程拥有者账号，授予使用该过程用户权限，语句如下

grant execute on 存储过程名 to 用户名;

• 用户在调用该函数时应用存储过程拥有者账号.存储过程名；例如scott.p1
• 收回权限使用revoke…from…语句即可

函数（注重结果）

函数可以理解为特殊的必须有return语句的存储过程

创建函数

一、语法格式

create [or replace] function 函数名(形参 [in|out|in out] 形参数据类型) return 返回值数据类型
is
	声明部分;
begin
	pl/sql代码块;
exception
	异常处理;
end;

二、无参数函数

create or replace function demo return varchar2
is
begin
	return 'Hello World';
end;

三、带参数函数(与存储过程大同小异，不再赘述，给出相关示例如下)

• in

--输出用户输入内容
create or replace function demo(a IN VARCHAR2) return varchar2
is
begin
	return a;
end;

• out

--抛出函数中的值(return也可看作一个out参数)
create or replace function demo(a OUT NUMBER) return NUMBER
is
b NUMBER:=3;
begin
	a:=b;
	return a;
end;

• in out

--输入雇员号返回雇员名
create or replace function demo(a IN OUT EMP%ROWTYPE) return EMP%ROWTYPE
is
begin
	SELECT * INTO a FROM EMP WHERE EMPNO=a.EMPNO;
	return a;
end;
/
--调用
DECLARE
	--承载形参变量
	q EMP%ROWTYPE;
	--承载函数变量
	e EMP%ROWTYPE;
BEGIN
	q.EMPNO:=&请输入员工编号;
	e:=DEMO(A  => q /*OUT NUMBER*/);
	dbms_output.put_line(q.ename);
END;

调用函数

• sql语句中调用

select 函数名(实参列表) from dual;

• pl/sql中调用

--因为函数必须有return则我们可以将函数看作一个值看待
declare
	承载函数变量 return返回值数据类型;
begin
	承载函数变量:=函数名(实参列表);
	--此时打印的是return返回值内容
	dbms_output.put_line(承载函数变量);
end;

删除函数

drop function 函数名;

查看当前用户下所有存储过程与函数

SELECT * FROM USER_PROCEDURES;