5 Oracle PL/SQL 存储过程--函数--包

一、存储过程

之前写的代码 declare begin end；称为匿名块，没有名称，没有存储在数据库中；
存储过程：Stored Procedure,简称sp、存过，它是一种数据库对象，它遵从PL/SQL基本语法；
语法：
CREATE [OR REPLACE] PROCEDURE 存储过程名[参数1 [IN/OUT]参数类型，参数2 [IN/OUT] 参数类型，...]
IS | AS
声明变量
BEGIN
执行部分
EXCEPTION
异常部门
END [存储过程名];
--写一个PL/SQL，打印员工编号为7788的员工 编号、姓名，工资

CREATE OR REPLACE PROCEDURE pr_print_emp
AS
v_empno NUMBER;
v_ename VARCHAR2(20);
v_sal NUMBER(8,2);
BEGIN
SELECT empno,ename,sal INTO v_empno,v_ename,v_sal FROM emp WHERE empno=7369;
dbms_output.put_line(v_empno||v_ename||v_sal);
END;

SELECT * FROM emp;
---需要注意：存储过程需要先编译，后调用；
---存储过程调用方式：
--1、CALL 存储过程名();--不管存储过程是否有参数都要加（）
CALL pr_print_emp();
--2、exec 存储过程名（）；--这是一个数据库命令，需要在命令行窗口执行，不能在SQL窗口执行；
EXEC pr_print_emp();--如果有打印命令，需要先设置set serveroutput on
--3、在PL/SQL中调用
BEGIN
pr_print_emp;()
END;
--4、browser窗口找到procedures下的pr_print_emp右键test;

---存储过程的删除：
DROP PROCEDURE pr_print_emp;

--例题1(有输入参数的)：调用存储过程，实现功能：输入员工编号，打印对应员工编号的信息(编号、姓名、工资)
CREATE OR REPLACE PROCEDURE pr_print_emp2(in_num IN NUMBER)--这里注意:1、参数类型不要给长度2、IN 可以省略默认为IN
IS
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec FROM emp WHERE empno=in_num;
dbms_output.put_line(v_emp_rec.empno||v_emp_rec.ename);
END;
---注意：我们上节写存储过程，使用的是SQL窗口编写。
---这个SQL窗口有一个问题，如果存储过程写错了，编译存储过程时，不会提示错误在哪。
--这时候还有另外一种方法，在程序窗口中编写；
--我们建议可以先使用SQL窗口编写，编译后如果在brower中看到有红叉号，右键edit在程序窗口中编译找到错误所在并修改；

CALL pr_print_emp2(7788);

例2（有输出参数）：调用存储过程实现功能：输入员工编号，打印员工信息（编号姓名工资），同时还要有一个参数接收领导的编号；

CREATE OR REPLACE PROCEDURE pr_print_emp3(in_num IN NUMBER,o_num OUT NUMBER)
IS
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec FROM emp WHERE empno=in_num;
o_num:=v_emp_rec.mgr;
END;

DECLARE
v_num NUMBER(8);
BEGIN
pr_print_emp3(7788,v_num);
dbms_output.put_line('7788号员工的领导编号是：'||v_num);
END;

例3（有输入输出参数）：上述例2，要用一个参数实现
CREATE OR REPLACE PROCEDURE pr_print_emp4(v_num IN OUT NUMBER)
IS
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec FROM emp WHERE empno=v_num;
dbms_output.put_line(v_emp_rec.empno||v_emp_rec.ename);
v_num:=v_emp_rec.mgr;
END;
----调用：
DECLARE
v_num NUMBER(8):=7788;
BEGIN
pr_print_emp4(v_num);
dbms_output.put_line(v_num);
END;

一般在（数据分析、数仓开发）项目中，使用最多的是输入参数，偶有输出参数，即使有输出参数，也是有关存储过程执行状态的输出（0/1之类的），仅此而已。

而输入参数也通常是一个日期，输入一个年份月份等。

二、函数 Funciton

是一种数据库对象，遵从PL/SQL基本语法。

语法：

CREATE OR REPLACE FUNCITON 函数名[(参数1 参数类型，参数2 参数类型...)]

RETURN  数据类型

IS | AS

BEGIN

RETURN 结果;
EXCEPTION

END;

调用：1、放在SQL语句中调用
SELECT fun_helo() FROM dual;
2、放在PL/SQL中调用
DECLARE
v_str VARCHAR2(20);
BEGIN
v_str :=fun_helo();
dbms_output.put_line(v_str);
END;
--例题1（无参数）：调用函数返回'hello,world!'
CREATE OR REPLACE FUNCTION fun_helo
RETURN VARCHAR2
IS
BEGIN
RETURN 'hello,world!';
END;

--例题2（有参数）：写一个翻倍函数，实现功能，输入一数字，返回双倍结果。
CREATE OR REPLACE FUNCTION fun_double(v_num NUMBER)
RETURN NUMBER
IS
v_res NUMBER;
BEGIN
v_res:=v_num*2;
RETURN v_res;
END;

SELECT fun_double(3) FROM dual;

三、包 Package和包体 package body

包是一个数据库对象，是一组用于实现功能的函数、存储过程、变量、常量等PL/SQL设计元素的组合
作用类似于收纳盒。

语法：

包:包头声明包里有哪些元素，类似于目录，大概说明包里有什么
CREATE OR REPLACE PACKAGE 包名 IS
PROCEDURE 存储过程名（..）;
FUNCTION 函数名（..）；
声明变量等..
...
END;

包体：
CREATE OR REPLACE PACKAGE BODY 包名 IS
PROCEDURE 存储过程名（..） IS ...begin ..end;
FUNCTION 函数名（..） RETURN ..IS.. begin..end;
..
..
END;
注意:包头里面有的，包体里面必须有，包头和包体要保持一致。

包的作用：
1、方便管理维护：把实现相同功能的过程和函数放在一起；
2、增加安全性；
3、可以创建私有函数和存储过程，只能被指定的同包内容引用；