思享工具箱

存储过程(学习笔记)

摘要：
执行EXECbdqn_proc；示例2：定义一个简单的过程CREATEORREPLACEPPROCEDUREFINDE_EMP（PNOEMP.EMPNO%TYPE）ASV_ENAMEEMP.ENAME%TYPE；--查询表中的记录总数IFV_COUNT=0THEN——判断是否有记录0表示没有RETURN；


 

存储过程指的是在大型数据库系统中专门定义的一组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

 存储过程(学习笔记)第1张

 

 

示例一、定义一个简单的过程

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; --将执行权授给用户，但用户可以再授权给其它用户

存储过程(学习笔记)第2张

存储过程(学习笔记)第3张

标签：#过程#'#_#参数 | 浏览：159 | 发布日期：

免责声明：内容来源于网络，仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Unity3D脚本语言UnityScript初探负载均衡介绍下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

webpack3向4升级中具体问题及解决办法

据说webpack4同比webpack旧版本构建速度提示至少一倍，于是开始webpack升级的尝试。 webpack4已经出来很长时间了，手头有一个基于webpack3的微信公众号项目，在项目的空档期自己在一个开发分支上做了webpack3向4的迁移。 　　首先将webpack升级到了4.28.2版本，因为线上项目基于webpack3的版本且为全局安装，不...

vuejs点滴

博客0.没事的时候可以看的一些博客：https://segmentfault.com/a/1190000005832164 http://www.tuicool.com/articles/vQBbiiQ 博客1.vuex应该去看的东西： http://blog.csdn.net/github_26672553/article/details/5317677...

Tornado web 框架

Tornado web 框架 其实很简单、深度应用一、简介   　　Tornado 是 FriendFeed 使用的可扩展的非阻塞式 web 服务器及其相关工具的开源版本。这个 Web 框架看起来有些像web.py 或者 Google 的 webapp，不过为了能有效利用非阻塞式服务器环境，这个 Web 框架还包含了一些相关有用工具及优化。   　　Tor...

JavaScript键盘鼠标事件处理

监听键盘鼠标事件 监听某个按键事件 当键盘上的某个键被按下时，会依次触发一次下面的事件： onkeydown: 键盘按下这个动作（按下键盘） onkeypress: 键盘被按住（一直按着键盘不动） onkeyup: 键盘被弹起（松开键盘） 通过监听keydown事件既可以知道键盘被按下： document.onkeydown = function(ev...

layui中，同一个页面动态加载table数据表格

效果图： 前端代码： <div class="layui-fluid" id="record-user" hidden="hidden"> <div class="layui-card"> <div class="layui-form" lay-filter="datafile">...

layer iframe 设置关闭按钮 和刷新和弹出框设置

layer弹出层的关闭问题  　　就是在执行添加或修改的时候，需要将数据提交到后台进行处理，这时候添加成功之后最理想的状态是关闭弹出层并且刷新列表的数据信息，之前一直想实现这样，可一直没有成功，今天决定好好弄一弄，在仔细看过layer的帮助手册以及查阅资料之后，有了以下的解决办法： 一、关闭弹出窗 　　这是layer官网给出的帮助手册，讲解的比较详细...

最新文章

随机推荐

思享工具箱导航