linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

Oracle-procedure/cursor解读

时间:2016-11-11  来源:未知  作者:linuxsir首页

procedure系列

Oracle-procedure解读

Oracle存储过程和自定义函数

procedure概述

存储过程( Stored Procedure )是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中。

用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

存储过程是由流控制和 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。

在 Oracle 中,若干个有联系的过程可以组合在一起构成程序包。


procedure优点

  • 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

  • 当对数据库进行复杂操作时(如对多个表进行 Update、Insert、Query、Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

  • 存储过程可以重复使用,可减少数据库开发人员的工作量。

  • 安全性高,可设定只有某用户才具有对指定存储过程的使用权。


和function的区别

这里写图片描述


procedure例子

CREATE OR REPLACE procedure proc_trade(
       v_tradeid     in number, --交易id
       v_third_ip    in varchar2, --第三方ip
       v_third_time  in date, --第三方完成时间
       v_thire_state in number, --第三方状态
       o_result      out number, --返回值
       o_detail      out varchar2 --详细描述
      ) as
  -- 定义变量
  v_error varchar2(500);
begin
  --对变量赋值
  o_result := 0;
  o_detail := '验证失败';
  --业务逻辑处理
  if v_tradeid > 100 then
    insert into table_name (.. .) values (.. .);
    commit;
  elsif v_tradeid < 100 and v_tradeid > 50 then
    insert into table_name (.. .) values (.. .);
    commit;
  else
    goto log;
  end if;
  --跳转标志符,名称自己指定
  <<log>>
  o_result := 1;
  --捕获异常
exception
  when no_data_found then
    result := 2;
  when dup_val_on_index then
    result := 3;
  when others then
    result := -1;
end proc_trade;

参数类型可以自己指定,这种写法可行,但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。 这样就不会出现参数类型的错误。


存储过程中的循环

for … in … loop 循环

循环遍历游标

示例1:

CREATE OR REPLACE PROCEDURE proc_test AS
  CURSOR c1 IS
    SELECT * FROM dat_trade;
BEGIN
  FOR x IN c1 LOOP
    DBMS_OUTPUT.put_line(x.id);
  END LOOP;
END proc_test;

示例 2:

CREATE OR REPLACE PROCEDURE proc_test AS
BEGIN
  FOR x IN (SELECT power_id FROM sys_power) LOOP
    DBMS_OUTPUT.put_line(x.power_id);
  END LOOP;
END proc_test;

根据数值进行循环

栗子一

CREATE OR REPLACE PROCEDURE proc_test() AS
BEGIN
  for x in 1 .. 100 loop
    dbms_output.put_line(x);
  end loop;
END proc_test;

栗子2:在过程里指定输入参数 v_num. 在调用过程时指定循环次数。

CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
  FOR x IN 1 .. v_num LOOP
    DBMS_OUTPUT.put_line(x);
  END LOOP;
END proc_test;

loop 循环

LOOP
DELETE FROM orders
WHERE senddate < TO_CHAR (ADD_MONTHS (SYSDATE, -3),
'yyyy-mm-dd')
AND ROWNUM < 1000;
EXIT WHEN SQL%ROWCOUNT < 1;
COMMIT;
END LOOP;

这 里 的 SQL%ROWCOUNT 是 隐 士 游 标 。 除 了 这 个 , 还 有 其 他 几
个: %found, %notfound, %isopen。


while 循环

CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) 
  AS
  i NUMBER := 1;
BEGIN
  WHILE i < v_num LOOP
    BEGIN
      i := i + 1;
      DBMS_OUTPUT.put_line(i);
    END;
  END LOOP;
END proc_test;

存储过程中的判断

if … elsif … else … 判断

CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
  IF v_num < 10 THEN
    DBMS_OUTPUT.put_line(v_num);
  ELSIF v_num > 10 AND v_num < 50 THEN
    DBMS_OUTPUT.put_line(v_num - 10);
  ELSE
    DBMS_OUTPUT.put_line(v_num - 50);
  END IF;
END proc_test;

case … when … end case 判断

CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
  case v_num
    when 1 then
      DBMS_OUTPUT.put_line(v_num);
    when 2 then
      DBMS_OUTPUT.put_line(v_num);
    when 3 then
      DBMS_OUTPUT.put_line(v_num);
    else
      null;
  end case;
END proc_test;

游标

之前整理的游标的知识

Cursor 型游标(不能用于参数传递)

CREATE OR REPLACE PROCEDURE proc_test AS
  CURSOR c1 IS
    SELECT * FROM dat_trade;
BEGIN
  FOR x IN c1 LOOP
    DBMS_OUTPUT.put_line(x.id);
  END LOOP;
END proc_test;

SYS_REFCURSOR 型游标

该游标是 Oracle 预先定义的游标,可作出参数进行传递。

SYS_REFCURSOR 只能通过 OPEN 方法来打开和赋值

我们可以使用这种类似的游标来返回一个结果集:

CREATE OR REPLACE procedure proc_test(
    checknum   in number, --每次返回的数据量
    ref_cursor out sys_refcursor --返回的结果集,游标
    ) as
begin
  open ref_cursor for
    select *
      from (select * from dat_trade where state = 41 order by id)
     where rownum < checknum;
end proc_test;

SYS_REFCURSOR 中可使用四个状态属性:

  • ( 1) . %NOTFOUND(未找到记录信息)
  • ( 2) . %FOUND(找到记录信息)
  • ( 3) . %ROWCOUNT(然后当前游标所指向的行位置)
  • (4). %ISOPEN(是否打开)
CREATE OR REPLACE PROCEDURE proc_test(
    checknum   IN NUMBER, --每次返回的数据量
    ref_cursor OUT sys_refcursor --返回的结果集,游标
    ) AS
  t_tmp table_name%ROWTYPE;
BEGIN
  OPEN ref_cursor FOR
    SELECT *
      FROM (SELECT * FROM table_name WHERE state = 41 ORDER BY id)
     WHERE ROWNUM < checknum;
  --循环游标
  LOOP
    FETCH ref_cursor
      INTO t_tmp;
    EXIT WHEN ref_cursor%NOTFOUND;
    -- DBMS_OUTPUT.put_line (t_tmp.id);
    UPDATE table_name SET state = 53 WHERE id = t_tmp.id;
    COMMIT;
  END LOOP;
  CLOSE ref_cursor;
END proc_test;

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

友情链接
  • Mozilla发布Firefox 67.0.4,修复沙箱逃逸漏洞
  • 蚂蚁金服正式成为CNCF云原生计算基金会黄金会员
  • Firefox 68将采用Microsoft BITS安装更新
  • OpenSSH增加对存储在RAM中的私钥的保护
  • 谷歌想实现自己的curl,为什么?
  • Raspberry Pi 4发布:更快的CPU、更大的内存
  • Firefox的UA将移除CPU架构信息
  • Ubuntu放弃支持32位应用程序实属乌龙,Steam会否重回Ubuntu怀抱
  • Qt 5.13稳定版发布:引入glTF 2.0、改进Wayland以及支持Lottie动
  • 红帽企业Linux 7现已内置Redis 5最新版
  • Slack进入微软内部禁用服务清单,GitHub也在其列?
  • 安全的全新编程语言V发布首个可用版本
  • Windows Terminal已上架,快尝鲜
  • 阿里巴巴微服务开源生态报告No.1
  • 面世两年,Google地球将支持所有基于Chromium的浏览器
  • 推进企业容器化持续创新,Rancher ECIC千人盛典完美收官
  • CentOS 8.0最新构建状态公布,或于数周后发布
  • Debian移植RISC
  • 微软拆分操作系统的计划初现雏形
  • Oracle发布基于VS Code的开发者工具,轻松使用Oracle数据库
  • Ubuntu 19.10停止支持32位的x86架构
  • 微软为Windows Terminal推出全新logo
  • 联想ThinkPad P系列笔记本预装Ubuntu系统
  • 微软发布适用于Win7/8的Microsoft Edge预览版
  • 启智平台发布联邦学习开源数据协作项目OpenI纵横
  • 经过六个多月的延迟,微软终于推出Hyper
  • ZFS On Linux 0.8.1 发布,Python可移植性工作
  • DragonFly BSD 5.6.0 发布,HAMMER2状态良好
  • Linux Kernel 5.2
  • CentOS 8.0 看起来还需要几周的时间
  • 百度网盘Linux版正式发布
  • PCIe 6.0宣布:带宽翻倍 狂飙至256GB/s
  • PHP 7.4 Alpha 发布,FFI扩展,预加载Opcache以获得更好的性能
  • Canonical将在未来的Ubuntu版本中放弃对32位架构的支持
  • Scala 2.13 发布,改进的编译器性能
  • 微软的GitHub收购了Pull Panda,并且使所有订阅完全免费
  • Windows Subsystem for Linux 2 (WSL 2)现在适用于Windows 10用
  • Debian 10 “Buster”的RISC
  • MariaDB宣布发布MariaDB Enterprise Server 10.4
  • DXVK 1.2.2 发布,带来微小的CPU开销优化
  • DragonFlyBSD 5.6 RC1 发布,VM优化,默认为HAMMER2
  • PrimeNG 8.0.0 发布,支持Angular 8,FocusTrap等
  • GIMP 2.10.12 发布,一些有用的改进
  • 清华大学Anaconda 镜像服务即将恢复
  • Debian GNU/Linux 10 “Buster” 操作系统将于2019年7月6日发布
  • 时时彩论坛
  • 五星体育斯诺克
  • 北单比分直播
  • 河北11选5走势图
  • 福建体彩36选7开奖结果
  • 九龙图库下载