`
linuxstuding
  • 浏览: 1223020 次
文章分类
社区版块
存档分类
最新评论

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 程序来读写操作系统文本文件

分享到:
评论

相关推荐

    oracle存储过程、游标、函数、PL/SQL块

    包含oracle存储过程的增、删、查、改 %type、%rowtype的使用 自定义函数 PL/SQL块

    PL/SQL 程序设计

    PL/SQL 程序设计 本章主要重点:  PL/SQL概述  PL/SQL块结构  PL/SQL流程  运算符和表达式  游标  异常处理  数据库存储过程和函数  包  触发器

    PL/SQL程序设计

    PL /SQL是一种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理。除此之外,可以在ORACLE数据库的某些...

    Oracle PL/SQL语言初级教程

    讲解oracle中的pl/sql的使用,存储过程,游标,函数,程序包,触发器。等等。

    Oracle_PLSQL语言基础

    ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL/SQL程序。  PL/SQL的优点  从版本6...

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(二)

    通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具  第1章 在windows ...

    PL/SQL Developer8.04官网程序_keygen_汉化

    PL/SQL Developer侧重于易用性、代码品质和生产力,充分发挥Oracle应用程序开发过程中的主要优势。 主要特性  登录界面PL/SQL编辑器,具有语法加强、SQL和PL/SQL帮助、对象描述、代码助手、编译器提示、PL/SQL...

    Oracle 11g SQL和PL SQL从入门到精通〖送源代码〗

    通过使用这些PL/SQL系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合SQL和PL/SQL初学者,也适合于有经验的Oracle应用开发人员。 前言 第一部分SQL和PL/SQL相关工具  第1章在Windows平台...

    PL/SQL语言的简介

    PL/SQL语言的简介 程序结构 流程控制 异常处理 过程与函数 游标 程序包 触发器

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(一)

    通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具  第1章 在windows ...

    PLSQL基础word

    PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL... PL/SQL程序块可背独立编译并存储在数据库中,任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。ORACLE提供了四种类型的可存储的程序

    PL/SQL 基础.doc

    PL/SQL 基础,一个不错的 PL/SQL 参考手册。内容预览: ---- 第一章 PL/SQL 简介 ---- 1. Oracle应用编辑方法概览 1) Pro*C/C++/... : C语言和数据库打交道的方法,比OCI更常用; 2) ODBC 3) OCI: C语言和...

    Oracle 11g SQL和PL SQL从入门到精通.part1

    通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具  第1章 在windows ...

    Oracle帮助文档

    Oracle帮助文档 ... 子程序/过程(procedure) 子程序/函数 事务处理 触发器 包 范式 数据备份(exp)与恢复(imp) 归档方式 过程练习题 考试题_查询部分 考试题_查询部分(2) 考试题_pl/sql 习题_触发器

    Oracle 11g SQL和PL SQL从入门到精通part2 pdf格式电子书 下载(二)

    通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具  第1章 在windows ...

    PLSQLDeveloper下载

    PL/SQL Developer侧重于易用性、代码品质和生产力,充分发挥Oracle应用程序开发过程中的主要优势。 目录 简介 背景 主要特性 编程基础知识程序结构 控制结构 变量声明与赋值 SQL基本命令 过程与函数 游标显示游标 ...

    Oracle包应用详解.doc

    (1)包是一种数据库对象,将逻辑上相关的PL/SQL类型、对象和子程序组合成一个更大的单位,是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合,它具有面向对象程序设计语言的特点,是对这些PL/SQL ...

Global site tag (gtag.js) - Google Analytics