存储过程指的是在大型数据库系统中专门定义的一组SQL语句集,它可以定义用户操作参数,并且存在于数据库中,当使用时直接调用即可
存储过程=过程的声明+PL/SQL块
定义过程语法:
CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称[参数模式] NOCOPY 数据类型 [参数名称 [参数模式] NOCOPY 数据类型,...]])
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
声明部分;
BEGIN
程序部分;
EXCEPTION
导常处理;
END;
- 参数中定义参数模式表示过程的数据的接收操作,一般分为
IN
,OUT
,IN OUT
3类 CREATE [OR REPLACE]
:表示创建或者替换过程,如果过程存在则替换,如果不存在就创建一个新的AUTHID
子句定义了一个过程的所有者权限,DEFINER
(默认)表示定义者权限执行,或者用CURRENT_USER
覆盖程序的默认行为,变为使用者权限PRAGMA AUTONOMOUS_TRANSACTION
:表示过程启动一个自治事务,自治事务可以让主事挂起,在过程中执行完SQL后,由用户处理提交或者回滚自治事务, 然后恢复主事务- EXECUTE 过程名 来调用过程 或者
EXEC
过程名
在sqlplus中设置过程显示
SET serveroutput ON
示例一、定义一个简单的过程
CREATE OR REPLACE PROCEDURE bdqn_proc
AS
BEGIN
dbms_output.put_line('学习使用存储过程!');
END;
执行
EXEC bdqn_proc;
示例二、定义一个简单的过程
CREATE OR REPLACE PROCEDURE FIND_EMP(PNO EMP.EMPNO%TYPE) AS
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_COUNT NUMBER;
BEGIN
SELECT COUNT(EMPNO) INTO V_COUNT FROM EMP; --查询表中的记录总数
IF V_COUNT = 0 THEN
--判断是否有记录0表示没有
RETURN; --结束
END IF;
SELECT ENAME, JOB INTO V_ENAME, V_JOB FROM EMP WHERE EMPNO = PNO; --查寻姓名和职位,并将值传给变量
DBMS_OUTPUT.PUT_LINE('员工编号: ' || PNO || ' 员工姓名: ' || V_ENAME ||
' 员工职位: ' || V_JOB);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据');
END FIND_EMP;
执行
EXECUTE FIND_EMP(&pnd)
示例三、使用过程增加部门
CREATE OR REPLACE PROCEDURE dpetadd_proc(
v_deptno dept.deptno%TYPE,
v_dname dept.dname%TYPE,
v_loc dept.loc%TYPE)
AS
v_count NUMBER;
BEGIN
SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno; --统计
IF v_count>0 THEN
raise_application_error(-20888,'增加失败,这个部门已经存在');
ELSE
INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
dbms_output.put_line('新部门增加成功');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM='||SQLERRM);
ROLLBACK;
END;
执行
EXEC dpetadd_proc(10,'北大青鸟','北京')
EXEC dpetadd_proc(90,'北大青鸟','北京')
SELECT * FROM dept;
示例四、使用过程增加部门
CREATE OR REPLACE PROCEDURE dpetadd2_proc(
v_deptno dept.deptno%TYPE:=&deptno,
v_dname dept.dname%TYPE:='&dname',
v_loc dept.loc%TYPE:='&loc')
AS
v_count NUMBER;
BEGIN
SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno; --统计
IF v_count>0 THEN
raise_application_error(-20888,'增加失败,这个部门已经存在');
ELSE
INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
dbms_output.put_line('新部门增加成功');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM='||SQLERRM);
ROLLBACK;
END;
执行
EXEC dpetadd2_proc(10,'北大青鸟','北京')
EXEC dpetadd2_proc(16,'北大青鸟','北京')
示例五、使用过程查询1981年入职的员工的工资和公司平均工资比较小于输出低工资,等于工资还行,高于输出高工资
CREATE OR REPLACE PROCEDURE SEARCH_PRO AS
V_EMPNO EMP.EMPNO%TYPE;
V_NAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_HIREDATE EMP.HIREDATE%TYPE;
V_GRADE SALGRADE.GRADE%TYPE;
V_AVG NUMBER;
V_DNAME DEPT.DNAME%TYPE;
V_SAL EMP.SAL%TYPE;
CUR_S SYS_REFCURSOR;
BEGIN
SELECT AVG(SAL) INTO V_AVG FROM EMP;
OPEN CUR_S FOR
SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE, E.SAL, S.GRADE, D.DNAME
FROM EMP E, SALGRADE S, DEPT D
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.DEPTNO = D.DEPTNO(+);
LOOP
FETCH CUR_S
INTO V_EMPNO, V_NAME, V_JOB, V_HIREDATE, V_SAL, V_GRADE, V_DNAME;
EXIT WHEN CUR_S%NOTFOUND;
IF V_SAL < V_AVG THEN
DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' || V_EMPNO || ' 姓名:' ||
V_NAME || ' 职位:' || V_JOB || ' 入职日期:' ||
V_HIREDATE || ' 工资:||v_sal' || ' 工资等级' ||
V_GRADE || ' 部门名称:' || V_DNAME);
DBMS_OUTPUT.PUT_LINE('工资太低了');
ELSIF V_SAL = V_AVG THEN
DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' || V_EMPNO || ' 姓名:' ||
V_NAME || ' 职位:' || V_JOB || ' 入职日期:' ||
V_HIREDATE || ' 工资:||v_sal' || ' 工资等级' ||
V_GRADE || ' 部门名称:' || V_DNAME);
DBMS_OUTPUT.PUT_LINE('工资还行');
ELSE
DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' ||
V_EMPNO || ' 姓名:' || V_NAME ||
' 职位:' || V_JOB || ' 入职日期:' ||
V_HIREDATE || ' 工资:||v_sal' ||
' 工资等级' || V_GRADE ||
' 部门名称:' || V_DNAME);
DBMS_OUTPUT.PUT_LINE('工资高了');
END IF;
END LOOP;
CLOSE CUR_S;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
执行
EXEC SEARCH_PRO
参数模式
IN模式
示例一、定义过程使用IN默认可以不写
CREATE OR REPLACE PROCEDURE in_proc(
p_a IN VARCHAR2, --明确定义IN参数模式
p_b IN VARCHAR2 --默认的参数模式为in
)
AS
BEGIN
dbms_output.put_line('执行in_proc()过程: p_a='||p_a);
dbms_output.put_line('执行in_proc()过程: p_b='||p_b);
END;
执行
DECLARE
v_a VARCHAR2(50):='Java开发实战经典';
v_b VARCHAR2(50):='Oracle开发实战经典';
BEGIN
in_proc(v_a,v_b);
END;
结果:
执行in_proc()过程: p_a=Java开发实战经典
执行in_proc()过程: p_b=Oracle开发实战经典
示例二、定义过程使用default定义参数默认值
CREATE OR REPLACE PROCEDURE in_proc(
p_a IN VARCHAR2 DEFAULT '好好学习JAVA', --明确定义IN参数模式
p_b IN VARCHAR2 DEFAULT '努力看Oracle' --默认的参数模式为in
)
AS
BEGIN
dbms_output.put_line('执行in_proc()过程: p_a='||p_a);
dbms_output.put_line('执行in_proc()过程: p_b='||p_b);
END;
DECLARE
v_a VARCHAR2(50):='Java开发实战经典';
BEGIN
in_proc(v_a);
END;
结果:
执行in_proc()过程: p_a=Java开发实战经典
执行in_proc()过程: p_b=努力看Oracle
使用了第二个参数没有写,使用了默认值,如果有传递参数则使用传递的参数
OUT模式
示例一、定义过程使用OUT
CREATE OR REPLACE PROCEDURE out_proc(
p_a OUT VARCHAR2, --明确定义out参数模式
p_b OUT VARCHAR2) --明确定义out参数模式
AS
BEGIN
dbms_output.put_line('执行out_proc()过程: p_a='||p_a);
dbms_output.put_line('执行out_proc()过程: p_b='||p_b);
p_a :='Java开发实战经典'; --将此值返回给实参
p_b :='Oracle开发实战经典';
END;
执行
DECLARE
v_a VARCHAR2(50):='好好学习';
v_b VARCHAR2(50):='天天向上';
BEGIN
out_proc(v_a,v_b);
dbms_output.put_line('调用out_proc()过程: v_a='||v_a);
dbms_output.put_line('调用out_proc()过程: v_b='||v_b);
END;
结果:
执行out_proc()过程: p_a=
执行out_proc()过程: p_b=
调用out_proc()过程: v_a=Java开发实战经典
调用out_proc()过程: v_b=Oracle开发实战经典
OUT模式时,传入的参数数是无用的,传入的内容不会传递 到过程中去
inout模式
示例一、定义过程使用INOUT
CREATE OR REPLACE PROCEDURE inout_proc(
p_a IN OUT VARCHAR2, --明确定义out参数模式
p_b IN OUT VARCHAR2) --明确定义out参数模式
AS
BEGIN
dbms_output.put_line('执行inout_proc()过程: p_a='||p_a);
dbms_output.put_line('执行inout_proc()过程: p_b='||p_b);
p_a :='Java开发实战经典'; --将此值返回给实参
p_b :='Oracle开发实战经典';
END;
执行
DECLARE
v_a VARCHAR2(50):='好好学习';
v_b VARCHAR2(50):='天天向上';
BEGIN
inout_proc(v_a,v_b);
dbms_output.put_line('调用inout_proc()过程: v_a='||v_a);
dbms_output.put_line('调用inout_proc()过程: v_b='||v_b);
END;
结果:
执行inout_proc()过程: p_a=好好学习
执行inout_proc()过程: p_b=天天向上
调用inout_proc()过程: v_a=Java开发实战经典
调用inout_proc()过程: v_b=Oracle开发实战经典
调用inout_proc过程时,将2个变量v_a,v_b传入到了过程中,由于是INOUT模式,所过程可以接收到传递的变量内容,同时过程对变量做了修改也可以运回给实参
示例二、利用过程增加部门
CREATE OR REPLACE PROCEDURE DEPTINSER_PROC(
p_DNO DEPT.DEPTNO%TYPE,
p_DNAME DEPT.DNAME%TYPE,
p_LOC DEPT.LOC%TYPE,
P_RESULT OUT NUMBER --此为标记变量
) AS
V_COUNT NUMBER; --保存count函数的结果
BEGIN
SELECT COUNT(DEPTNO) INTO V_COUNT FROM DEPT WHERE DEPTNO = p_DNO;
IF V_COUNT > 0 THEN
P_RESULT := -1;
ELSE
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (p_DNO, p_DNAME, p_LOC);
P_RESULT := 0;
COMMIT;
END IF;
END;
调用
DECLARE
V_RESULT NUMBER; --定义变量接收结果
BEGIN
DEPTINSER_PROC(66, 'test', 'China', V_RESULT); --调用过程
IF V_RESULT = 0 THEN
DBMS_OUTPUT.PUT_LINE('新部门增加成功');
ELSE
DBMS_OUTPUT.PUT_LINE('新部门增加失败');
END IF;
END;
示例三、利用OUT传递游标使用过程是查询员工ID,姓名,职位,工资
CREATE OR REPLACE PROCEDURE search_pro(
p_emp OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN p_emp FOR SELECT e.empno,e.ename,e.job,e.sal
FROM emp e;
END;
调用
DECLARE
V_ID EMP.EMPNO%TYPE;
V_NAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_SAL EMP.SAL%TYPE;
CUR_EMP SYS_REFCURSOR; --定义弱类型游标
BEGIN
SEARCH_PRO(CUR_EMP);
LOOP
FETCH CUR_EMP
INTO V_ID, V_NAME, V_JOB, V_SAL;
EXIT WHEN CUR_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || ' 员工编号:' || V_ID || ' 姓名:' ||
V_NAME || ' 职位:' || V_JOB || ' 工资:' || V_SAL);
END LOOP;
CLOSE CUR_EMP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
示例四 、定义过程,根据员工编号,查询员工姓名和工资,并输出参数输出
CREATE OR REPLACE PROCEDURE pro_emp_query(
v_eno IN emp.empno%TYPE,
v_name OUT emp.ename%TYPE,
v_sal OUT emp.sal%TYPE
)AS
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_eno;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('没有这个员工!');
END;
DECLARE
v_eno emp.empno%TYPE:=&empno;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
pro_emp_query(v_eno,v_ename,v_sal);
dbms_output.put_line('编号:'||v_eno||' 姓名:'||v_ename||' 工资:'||v_sal);
END;
示例五 、交换值
CREATE OR REPLACE PROCEDURE swap(
p1 IN OUT NUMBER,
p2 IN OUT NUMBER
)AS
v_temp NUMBER;
BEGIN
v_temp:=p1;
p1:=p2;
p2:=v_temp;
END;
--调用
DECLARE
num1 NUMBER:=10;
num2 NUMBER:=20;
BEGIN
dbms_output.put_line(num1||' '||num2);
swap(num1,num2);
dbms_output.put_line(num1||' '||num2);
END;
自治事务
使用下列语句声明
PRAGMA AUTONOMOUS_TRANSACTION;
示例一、
CREATE OR REPLACE PROCEDURE dept_insert_proc
AS
PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
BEGIN
INSERT INTO dept(deptno,dname,loc)VALUES(80,'JAVA','北京');
COMMIT; --提交自治事务
END;
调用
DECLARE
BEGIN
INSERT INTO dept(deptno,dname,loc)VALUES(60,'Oracl','深圳');
dept_insert_proc();
ROLLBACK; --主事务回滚
END;
SELECT * FROM dept;
首先会向部门表中添加一条60部门的信息,此时调用过程,主程序会被挂起,到过程执行完
结果看出80部门已经添加成功,并没有受到ROLLBACK的影响
NOCOPY选项
PL/SQL中对于IN模式的传递的参数采用孝是引用的传递方式,所以其性能较高。
而对于OUT或者IN OUT模式传递参数采用的是数值传递,在传递时需要将实参数据复制一份给形参,但当传递的数据较大时(如集合,记录),那么这一复制过程就会变长
也会消耗大量的内在空间,
在定义参数时可以使用NOCOPY选项,将OUT或者IN OUT的值传递变为引用传递
语法:
参数名称 [参数模式out | in out] NOCOPY数据类型
--定义过程
create or replace procedure change_proc(
p_a in out number,p_nocopy in out nocopy NUMBER
)
AS
begin
p_a:=100;
p_nocopy:=100;
raise_application_error(-20008,'测试NOCOPY');
end change_proc;
调 用
--调用过程
DECLARE
v_a NUMBER:=10;
v_b NUMBER:=20;
BEGIN
dbms_output.put_line('调用过程之前 v_a='||v_a||' v_b='||v_b);
change_proc(v_a,v_b);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('调用过程之后v_a='||v_a||' v_b='||v_b);
END;
结果:
调用过程之前 v_a=10 v_b=20
ORA-20008: 测试NOCOPY
调用过程之后v_a=10 v_b=100
因为过程抛出了导常所以使用NOCOPy定义的参数正确将结果返回了,而没有使用NOCOPY定义的参数不能正常返回,
子程序权限:
过程授权
GRANT EXECUTE ON 过程名 TO 用户名 --将执行权授给用户,但用户不能再授权给其它用户
GRANT EXECUTE ON 过程名 TO 用户名 WITH GRANT OPTION; --将执行权授给用户,但用户可以再授权给其它用户
评论前必须登录!
注册