首页 > 代码库 > 存储过程(学习笔记)
存储过程(学习笔记)
存储过程指的是在大型数据库系统中专门定义的一组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
过程授权
GRANT EXECUTE ON 过程名 TO 用户名 --将执行权授给用户，但用户不能再授权给其它用户
GRANT EXECUTE ON 过程名 TO 用户名 WITH GRANT OPTION; --将执行权授给用户，但用户可以再授权给其它用户
示例一、定义一个简单的过程
CREATE OR REPLACE PROCEDURE bdqn_procASBEGIN 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) ASBEGIN 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) ASBEGIN 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参数模式ASBEGIN 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参数模式ASBEGIN 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)ASBEGIN 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;
自治事务
使用下列语句声明
PRAGMA AUTONOMOUS_TRANSACTION;
示例一、
CREATE OR REPLACE PROCEDURE dept_insert_procAS PRAGMA AUTONOMOUS_TRANSACTION; --自治事务BEGIN INSERT INTO dept(deptno,dname,loc)VALUES(80,‘JAVA‘,‘北京‘); COMMIT; --提交自治事务END;调用DECLAREBEGIN INSERT INTO dept(deptno,dname,loc)VALUES(60,‘Oracl‘,‘深圳‘); dept_insert_proc(); ROLLBACK; --主事务回滚END;SELECT * FROM dept;
首先会向部门表中添加一条60部门的信息，此时调用过程，主程序会被挂起，到过程执行完
结果看出80部门已经添加成功，并没有受到ROLLBACK的影响
存储过程(学习笔记)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。