PL/SQL-存储过程、函数

存储过程

存储过程是一种命名的PL/SQL程序块,它既可以没有参数,也可以有若干个输入、输出参数,甚至可以有多个既作输入又作输出的参数,但它通常没有返回值。存储过程被保存在数据库中,它不可以被SQL语句直接执行或调用,只能通过EXECUT命令执行或在PL/SQL程序块中被调用，由于存储过程是已经编译好的代码,所以其被调用或引用时,执行效率非常高。

语法

CREATE [OR REPLACE] PROCEDURE pro_name[(parameter1)[,parameter2]…] IS|AS
–内部声明
BEGIN
–PL/SQL语句
[EXCEPTION]
–异常处理语句
END;

– pro_name 存储过程的名称,如果已存在,可以使用 OR REPLACE 来覆盖
– parameter1 存储过程的参数,若是输入参数,则需要在其后指定”in”关键字,默认
– 若是输出参数,则需要在其后指定”out”关键字
– 在in或out关键字的后面是参数的数据类型,但不能指定该类型的长度

---

创建与调用

--创建存储过程,向emp表中插入一条记录CREATE OR REPLACE PROCEDURE PRO_INSERT_EMP01 ISBEGIN  INSERT INTO EMP  VALUES    ('2333', 'new name', 'new job', 7777, SYSDATE, 100, 200, 20);  COMMIT;  DBMS_OUTPUT.PUT_LINE('插入新数据成功！');END;--在PL/SQL块中调用存储过程DECLAREBEGIN  PRO_INSERT_EMP01;END;--也可以在SQL*Plus使用execute命令执行,execute可以简写为exec

---

IN参数

这是一种输入类型的参数,参数值由调用方传入,并且只能被存储过程读取。这种参数模式是最常用的,也是默认的参数模式,关键词in位于参数名称之后。

示例

--使用IN参数,向emp表中插入一条记录CREATE OR REPLACE PROCEDURE PRO_INSERT_EMP02(V_EMPNO IN  EMP.EMPNO%TYPE,                                             V_ENAME IN  EMP.ENAME%TYPE,                                             V_DEPTNO IN EMP.DEPTNO%TYPE) ISBEGIN  INSERT INTO EMP    (EMPNO, ENAME, DEPTNO)  VALUES    (V_EMPNO, V_ENAME, V_DEPTNO);  COMMIT;  DBMS_OUTPUT.PUT_LINE('插入新数据成功！');END;--调用(按位置传参)DECLAREBEGIN  PRO_INSERT_EMP02(6666, 'new name2', 20);END;--调用(指定名称传参)DECLAREBEGIN  PRO_INSERT_EMP02(V_DEPTNO => 20, V_NAME => 'new name3', V_EMPNO => 7777);END;

in参数可以设置默认值,这样在存储过程被调用时,如果没有向in参数传入值,就使用默认值进行操作。

示例

--给IN参数指定默认值,这样可以不传入此参数值(使用默认)--查询emp表中指定雇员编号的姓名,如果不指定默认查询编号7788的雇员CREATE OR REPLACE PROCEDURE PRO_SELECT02(V_EMPNO IN EMP.EMPNO%TYPE DEFAULT 7788) ISV_ENAME EMP.ENAME%TYPE;BEGINSELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = V_EMPNO;DBMS_OUTPUT.PUT_LINE('雇员姓名:' || V_ENAME);END;--不指定雇员编号调用DECLAREBEGINPRO_SELECT02();END;

---

OUT参数

这是一种输出类型的参数,表示这个参数在存储过程中已经被赋值,并且这个参数值可以传递到当前存储过程以外的环境中,关键字out位于参数名称之后。

示例

--使用out参数,查询emp表中指定雇员编号数据CREATE OR REPLACE PROCEDURE PRO_EMP_SELECT01(V_EMPNO IN EMP.EMPNO%TYPE,                                             V_EMP   OUT EMP%ROWTYPE) ISBEGIN  SELECT * INTO V_EMP FROM EMP WHERE EMPNO = V_EMPNO;EXCEPTION  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE('雇员编号不存在');END;--在PL/SQL块中调用需要定义一个相同类型的接受变量DECLARE  V_EMP   EMP%ROWTYPE;  V_EMPNO EMP.EMPNO%TYPE := 7788;BEGIN  PRO_EMP_SELECT01(V_EMPNO, V_EMP);  DBMS_OUTPUT.PUT_LINE('雇员姓名:' || V_EMP.ENAME || ' 雇员薪水:' || V_EMP.SAL);END;

---

IN OUT 参数

in out类型参数兼顾in类型和out类型特点,既可以作为输入参数也可以作为输出参数。

示例

--使用IN OUT参数,输入此类型参数计算一个数的平方根CREATE OR REPLACE PROCEDURE PRO_SQUARE(V_NUM IN OUT NUMBER) ISBEGIN  V_NUM := SQRT(V_NUM);END;--计算1024的平方根DECLARE  V_NUM NUMBER := 1024;BEGIN  PRO_SQUARE(V_NUM);  DBMS_OUTPUT.PUT_LINE(V_NUM);END;

---

函数

函数的创建语法与存储过程比较类似,它也是一种存储在数据库中的命名程序块,函数可以接受零或多个输入参数,并且函数必须有返回值,这一点在存储过程中不是必须的。

语法

CREATE [OR REPLACE] FUNCTION fun_name[(parameter1[,parameter2]…)] RETURN data_type IS
–函数的内部变量,可选项
BEGIN
–PL/SQL语句
[EXCEPTION]
–异常处理
END;

–fun_name 函数名
–parameter1 函数的参数,可选项
–data_type 函数的返回值类型,这是个必选项。在返回值类型的前面要使用return关键字来标明

示例

--定义一个函数,计算emp表中指定某个部门的平均工资CREATE OR REPLACE FUNCTION FUN_EMP_AVGSAL(V_DEPTNO EMP.DEPTNO%TYPE)  RETURN NUMBER IS  V_AVG_NUM NUMBER;BEGIN  SELECT AVG(SAL) INTO V_AVG_NUM FROM EMP WHERE DEPTNO = V_DEPTNO;  RETURN ROUND(V_AVG_NUM, 2);EXCEPTION  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE('部门编号不存在');    RETURN(0);END;--调用函数--因为函数有返回值,所以调用时需要使用一个变量来接收函数返回值DECLARE  V_NUM NUMBER;BEGIN  V_NUM := FUN_EMP_AVGSAL(20);  DBMS_OUTPUT.PUT_LINE('平均工资:' || V_NUM);END;