Oracle 笔记(八)、PL/SQL 高级应用(游标、存储过程、函数、程序包)

简介:

一、游标
    游标是一种 PL/SQL 控制结构,可以对SQL语句的处理进行显式控制,便于对表的数据逐条进行处理。
ps.当表中数据量大的时候,不建议使用游标(效率不高,耗费资源),但是它能逐条取数据方法灵活。
    游标是记录的指针,利用游标对活动集的更新或删除会反馈到表的记录上。

游标属性:%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN


1、显式游标
    显式游标是由用户显式声明的游标。根据在游标中定义的查询,查询返回的行集可以包含零或多行,这些行称为活动集。游标将指向活动集中的当前行。

显式游标操纵过程:声明、打开、从游标中获取记录、关闭。

SET SERVEROUTPUT ON;
DECLARE
  CURSOR cur IS
    SELECT * FROM books;
  myrecord books%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO books;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(books.id || ' ' || books.name);
  END LOOP;
  CLOSE cur;
END;


1.1、带参数的显式游标:参数不需指定长度或者精度。
1.2、FOR 循环游标:采用遍历方式,自动打开、提取和关闭游标。(能否利用 %ROWCOUNT 获得游标提取的行数?)

DECLARE
  /* 定义带参数游标 */
  CURSOR cur_para(id varchar2) IS
  SELECT books_name FROM books WHERE books_id = id;
BGEIN
  /* 调用带参数游标,并以 FOR 循环方式处理 */
  FOR cur IN cur_para('0001') LOOP
    DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
  END LOOP;
END;

1.3、使用显示游标删除或更新记录
定义时:需使用 SELECT ... FOR UPDATE 语句表示事物的锁定;
执行时:需使用 WHERE CURRENT OF curXXX 子句指定游标的当前行。

  /* 定义部分 */
  CURSOR cur IS
  SELECT name FROM deptment FOR UPDATE;
....
  /* 执行部分 */
  UPDATE deptment SET name=name || '_tt' WHERE CURRENT OF cur;


2、隐式游标
不需声明,打开和关闭的游标。PL/SQL 为所有的 SQL 数据操纵语句隐式声明游标,它是不能直接命名和控制。

BEGIN
  FROM cur IN (SELECT name FROM deptment) LOOP
    DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
  END LOOP;
END;


ps.
匿名块:每次执行时都需要被编译,并且无法存储到数据库中,别的 PL/SQL 块也无法调用它。
命名块:存储在数据库中,属于数据库对象。

排错:SHOW ERRORS PROCEDURE/FUNCTION/PACKAGE obj_name;



二、存储过程

CREATE OR REPLACE PROCEDURE test (value IN varchar2, value2 OUT NUMBER)
  /* 参数,不需指定长度或精度 */
IS
  /* 局部变量,省略 DECLARE 关键字,需有长度 */
  identity NUMBER;
BEGIN
  SELECT ITEMRATE INTO identity
    FROM itemFile
    WHERE itemcode = value;
  IF identity < 200 THEN
    value2 := 200;
  ELSE
    value2 :=50;
  END IF;
END;


● 匿名块执行过程

DECLARE
  tvalue2 NUMBER;
BEGIN
  test('i202', tvalue2);
  DBMS_OUTPUT.PUT_LINE('value2的值为:' || TO_CHAR(value2));
END;

● 单独执行
    EXECUTE myproc('0001');



三、函数
    函数的主要特性是它必须返回一个值。创建函数时通过 RETURN 子句指定函数返回值的数据类型。
函数的一些限制:
  ● 函数只能带有 IN 参数,不能带有 IN OUT 或 OUT 参数。
  ● 形式参数必须只使用数据库类型,不能使用 PL/SQL 类型。
  ● 函数的返回类型必须是数据库类型。

CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)
   /* 参数、指定返回类型 */
  RETURN varchar2
AS
   /* 定义局部变量 */
  min_price NUMBER;
  max_price NUMBER;
BEGIN
  SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price
    FROM itemfile;
  IF price >= min_price AND price <= max_price THEN
    RETURN '输入的单价介于最低价与最高价之间';
  ELSE
    RETURN '超出范围';
  END IF;
END;


● 匿名块执行函数

DECLARE
  p NUMBER := 300;
  MSG varchar2(200);
BEGIN
  MSG := item_price_range(p);
  DBMS_OUTPUT.PUT_LINE(MSG);
END;

● SELECT查询调用(因为函数必须有返回值)
    SELECT myfunction FROM dual;



    过程与函数的异同
过程:
    作为 PL/SQL 语句执行;
    在规范中不包含 RETURN 子句;
    不返回任何值(只有输入/输出参数,结果集);
    可以包含 RETURN 语句,但是与函数不同,它不能用于返回值。
函数:
    作为表达式的一部分调用;
    必须在规范中包含 RETURN 子句;
    必须返回单个值;
    必须包含至少一条 RETURN 语句。



四、程序包
    程序包是一种数据库对象,它是对相关 PL/SQL 类型、子程序、游标、异常、变量和常量的封装。
程序包规范:声明类型、变量、常量、异常、游标和子程序。
程序包主体:用于实现在程序包规范中定义的游标、子程序。


4.1、程序包规范
包含应用程序所需的程序包资源,是与应用程序的接口。

CREATE OR REPLACE PACKAGE pack_me
IS
  PROCEDURE order_proc (orno varchar2);
  FUNCTION order_fun (ornos varchar2) RETURN varchar2;
END pack_me;

*创建 pack_me 包,并声明了子程序 order_proc 和 order_fun,并交由程序包主体实现。


4.2、程序包主体
当程序包规范中指定了子程序和游标时,必须有程序包主体。

CREATE OR REPLACE PACKAGE BODY pack_me
AS
  /* 实现定义的存储过程 */
  PROCEDURE order_proc (orno varchar2) 
  IS
    stst CHAR(1);
  BEGIN
    SELECT ostatus INTO stat FROM order_master
      WHERE orderno = orno;
    IF stat = 'p' THEN
      DBMS_OUTPUT.PUT_LINE('暂挂的订单');
    ELSE
      DBMS_OUTPUT.PUT_LINE('已完成的订单');
    END IF;
  END order_proc;
  /* 实现定义的函数 */
  FUNCTION order_fun(ornos varchar2) RETURN varchar2
  IS
    icode varchar2(5);
    ocode varchar2(5);
    qtyord NUMBER;
    qtydeld NUMBER;
  BEGIN
    SELECT qty_ord, qty_deld, itemcode, ordernc INTO qtyord, qtydeld, icode, ocode
      FROM order_detail
      WHERE orderno = ornos;
    IF qtyord < qtydeld THEN
      RETURN ocode;
    ELSE
      RETURN icode;
    END IF;
  END order_fun;
END pack_me;


● 要执行 pack_me包中的 order_proc过程,输入
    EXECUTE pack_me.order_proc('o002');

● 要执行包中预定义的函数

DECLARE
  msg varchar2(10);
BEGIN
  msg := pack_me.order_fun('o002');
  DBMS_OUTPUT.PUT_LINE('值是 ' || msg);
END;

  
4.3、程序包的优点
    程序包将相关的功能在逻辑上组织在一起,模块化,信息隐藏和更好的性能。
ps.数据字典视图 USER_SOURCE 包含存储过程的代码文本。


4.4、内置程序包
STANDARD 和 DBMS_STANDARD:定义和扩展 PL/SQL 语言环境
DBMS_LOB:提供对 Oracle LOB 数据类型进行操作的功能
DBMS_LOCK:用户定义的锁
DBMS_OUTPUT:处理 PL/SQL 块和子程序输出调试信息
DBMS_SESSION:提供 ALTER SESSION 命令的 PL/SQL 等效功能
DBMS_ROWID:获得 ROWID 的详细信息
DBMS_RANDOM:提供随机数生成器
DBMS_SQL:允许用户使用动态 SQL,构造和执行任意 DML 或 DDL 语句
DBMS_JOB:提交和管理在数据库中执行的定时任务
DBMS_XMLDOM:用 DOM 模型读写 XML 类型的数据
DBMS_XMLPARSER:XML 解析,处理 XML 文档内容和结构
DBMS_XMLGEN:将 SQL 查询结果转换为规范的 XML 格式
DBMS_XMLQUERY:提供将数据转换为 XML 类型的功能
DBMS_XSLPROCESSOR:提供 XSLT 功能,转换 XML 文档
UTL_FILE:用 PL/SQL 程序来读写操作系统文本文件

本文转自 qvodnet 51CTO博客,原文链接:http://blog.51cto.com/bks2015/1982991


相关文章
|
27天前
|
存储 Oracle 关系型数据库
Oracle存储过程插入临时表优化与慢查询解决方法
优化是一个循序渐进的过程,就像雕刻一座雕像,需要不断地打磨和细化。所以,耐心一点,一步步试验这些方法,最终你将看到那个让你的临时表插入操作如同行云流水、快如闪电的美丽时刻。
62 14
|
2月前
|
存储 SQL 数据库连接
C#程序调用Sql Server存储过程异常处理:调用存储过程后不返回、不抛异常的解决方案
本文分析了C#程序操作Sql Server数据库时偶发的不返回、不抛异常问题,并提出了解决思路。首先解析了一个执行存储过程的函数`ExecuteProcedure`,其功能是调用存储过程并返回影响行数。针对代码执行被阻塞但无异常的情况,文章总结了可能原因,如死锁、无限循环或网络问题等。随后提供了多种解决方案:1) 增加日志定位问题;2) 使用异步操作提升响应性;3) 设置超时机制避免阻塞;4) 利用线程池分离主线程;5) 通过信号量同步线程;6) 监控数据库连接状态确保可用性。这些方法可有效应对数据库操作中的潜在问题,保障程序稳定性。
159 11
|
9月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第18天】SQL Server 存储过程具有提高性能、增强安全性、代码复用和易于维护等优点。它可以减少编译时间和网络传输开销,通过权限控制和参数验证提升安全性,支持代码共享和复用,并且便于维护和版本管理。然而,存储过程也存在可移植性差、开发和调试复杂、版本管理问题、性能调优困难和依赖数据库服务器等缺点。使用时需根据具体需求权衡利弊。
203 1
|
8月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
105 2
|
9月前
|
存储 SQL 缓存
SQL Server存储过程的优缺点
【10月更文挑战第22天】存储过程具有代码复用性高、性能优化、增强数据安全性、提高可维护性和减少网络流量等优点,但也存在调试困难、移植性差、增加数据库服务器负载和版本控制复杂等缺点。
385 1
|
9月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第17天】SQL Server 存储过程是预编译的 SQL 语句集,存于数据库中,可重复调用。它能提高性能、增强安全性和可维护性,但也有可移植性差、开发调试复杂及可能影响数据库性能等缺点。使用时需权衡利弊。
160 3
|
9月前
|
存储 SQL 数据库
Sql Server 存储过程怎么找 存储过程内容
Sql Server 存储过程怎么找 存储过程内容
509 1
|
8月前
|
存储 Oracle 关系型数据库
oracle服务器存储过程中调用http
通过配置权限、创建和调用存储过程,您可以在Oracle数据库中使用UTL_HTTP包发起HTTP请求。这使得Oracle存储过程可以与外部HTTP服务进行交互,从而实现更复杂的数据处理和集成。在实际应用中,根据具体需求调整请求类型和错误处理逻辑,以确保系统的稳定性和可靠性。
365 0
|
6月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
SQL 关系型数据库 Oracle
ORACLE SQL优化之ORA-03150&ORA-02055&ORA-02063
                                                                                                             >   
4958 0

推荐镜像

更多