存储过程
存储过程是一种命名的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 IS
BEGIN
INSERT INTO EMP
VALUES
('2333', 'new name', 'new job', 7777, SYSDATE, 100, 200, 20);
COMMIT;
DBMS_OUTPUT.PUT_LINE('插入新数据成功!');
END;
--在PL/SQL块中调用存储过程
DECLARE
BEGIN
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) IS
BEGIN
INSERT INTO EMP
(EMPNO, ENAME, DEPTNO)
VALUES
(V_EMPNO, V_ENAME, V_DEPTNO);
COMMIT;
DBMS_OUTPUT.PUT_LINE('插入新数据成功!');
END;
--调用(按位置传参)
DECLARE
BEGIN
PRO_INSERT_EMP02(6666, 'new name2', 20);
END;
--调用(指定名称传参)
DECLARE
BEGIN
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) IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.PUT_LINE('雇员姓名:' || V_ENAME);
END;
--不指定雇员编号调用
DECLARE
BEGIN
PRO_SELECT02();
END;
OUT参数
这是一种输出类型的参数,表示这个参数在存储过程中已经被赋值,并且这个参数值可以传递到当前存储过程以外的环境中,关键字out位于参数名称之后。
示例
--使用out参数,查询emp表中指定雇员编号数据
CREATE OR REPLACE PROCEDURE PRO_EMP_SELECT01(V_EMPNO IN EMP.EMPNO%TYPE,
V_EMP OUT EMP%ROWTYPE) IS
BEGIN
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) IS
BEGIN
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;