开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Oracle中的plsql编程 -> 正文阅读

[大数据]Oracle中的plsql编程

1. 概述

pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
过程、函数、触发器是pl/sql编写。
过程、函数、触发器是在Oracle中。
pl/sql是非常强大的数据库过程语言。
过程、函数可以在Java程序中调用。

2. pl/sql的优缺点

优点：

提高应用程序的运行性能
模块化的设计思想【分页的过程，订单的过程，转账的过程。。】
减少网络传输量
提高安全性（sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会）

缺点：

移植性不好（换数据库就用不了）。

3. pl/sql开发工具

<1>sqlplus开发工具

sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的：

举一个简单的案例：

编写一个存储过程，该过程可以向某表中添加记录。

创建一个简单的表

create table mytest(name varchar2(30),passwd varchar2(30));

创建过程

create or replace procedure sp_pro1 is 
2. begin--执行部分  
3. insert into mytest values('韩顺平','m1234')；  
4. end;      
5. /

replace：表示如果有sp_pro1，就替换

查看错误信息：show error;

调用该过程：

exec 过程名（参数值1，参数值2…）；

call 过程名（参数值1，参数值2…）；

<2>pl/sql developer开发工具

pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。

举一个简单案例：

编写一个存储过程，该过程可以删除某表记录。

create or replace procedure sp_pro2 is 
2. begin
3. --执行部分  
4. delete from mytest where name='韩顺平'；      
5. end; 
6. /

调用过程：

exec sp_pro2;

4. pl/sql基础

<1>概述

开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。

<2>pl/sql简单分类

<3>编写规范

单行注释 –
多行注释 /* … */来划分
标志符号的命名规范
- 当定义变量时，建议用v_作为前缀，例如：v_sal 。
- 当定义常量时，建议用c_作为前缀，例如：c_rate。
- 当定义游标时，建议用_cursor作为后缀，例如：emp_cursor 。
- 当定义例外时，建议用e_作为前缀，例如：e_error 。

<4>pl/sql块

概述：

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

块结构示意图：

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。

注意：

定义部分是从declare开始的，该部分是可选的；

执行部分是从begin开始的，该部分是必须的；

例外处理部分是从exception开始的，该部分是可选的。

pl/sql块的实例1，只包括执行部分的pl/sql块：

dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。

pl/sql块的实例2，包含定义部分和执行部分的pl/sql块：

如果要把薪水也显示出来，那么执行部分就应该这么写：

select ename,sal into v_ename,v_sal from emp where empno=&aa;

& 表示要接收从控制台输入的变量。

pl/sql块的实例3，包含定义部分，执行部分和例外处理部分：

为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。

比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。
有时出现异常，希望用另外的逻辑处理

实例：

5. 过程

过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。

实例：

请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资。

6. 函数

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数。

实例：

输入雇员的姓名，返回该雇员的年薪。

7. 包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

我们可以使用create package命令来创建包。

实例：

–创建一个包sp_package

–声明该包有一个过程update_sal

–声明该包有一个函数annual_income

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
建立包体可以使用create package body命令

–给包sp_package实现包体
如何调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。

8. 触发器

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。

9. 定义并使用变量

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：

标量类型(scalar)
复合类型(composite)
参照类型(reference)
lob(large object)

<1>标量

在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下：

identifier [constant] datatype [not null] [:=| default expr]

identifier : 名称

constant ：指定常量。需要指定它的初始值，且其值是不能改变的

datatype ：数据类型

not null ：指定变量值不能为null

:= 给变量或是常量指定初始值

default 用于指定初始值

expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。

标量定义的案例：

定义一个变长字符串

v_ename varchar2(10);
定义一个小数,范围 -9999.99~9999.99

v_sal number(6,2);
定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号

v_sal2 number(6,2):=5.4;
定义一个日期类型的数据

v_hiredate date;
定义一个布尔变量，不能为空，初始值为false

v_valid boolean not null default false;

在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)

下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。

使用%type类型：

对于上面的pl/sql块有一个问题：

就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

我们看看这个怎么使用：

标识符名表名.列名%type;

比如上例的v_ename，这样定义： v_ename emp.ename%type;

<2>复合变量

用于存放多个值的变量。主要包括这几种：

pl/sql记录
pl/sql表
嵌套表
varray

复合类型——pl/sql记录：

类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

1.	declare  
2.	--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理   
3.	type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);   
4.	--定义了一个sp_record变量，这个变量的类型是emp_record_type   
5.	sp_record emp_record_type;   
6.	begin  
7.	select ename, sal, job into sp_record from emp where empno = 7788;   
8.	dbms_output.put_line ('员工名:' || sp_record.name);   
9.	end;  
10. /

复合类型-pl/sql表：

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。

实例：

sp_table_type 是pl/sql表类型

emp.ename%type 指定了表的元素的类型和长度

sp_table 为pl/sql表变量

sp_table(0) 则表示下标为0的元素

如果把select ename into sp_table(-1) from emp where empno = 7788;变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：

ORA-01422:实际返回的行数超出请求的行数

解决方法是：使用参照变量

<3>参照变量

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。

游标变量

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。

实例：

10. pl/sql进阶

<1>控制结构

在任何计算机语言(c，java，pascal)都有各种控制语句（条件语句，循环结构，顺序控制结构…）在pl/sql中也存在这样的控制结构。

简单的条件判断 if – then

编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
二重条件分支 if – then – else

编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
多重条件分支 if – then – elsif – then

编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200。
循环语句 –loop

是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。

案例：
循环语句 –while循环

基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while…loop开始，以end loop结束。

案例：
循环语句 –for循环

基本for循环的基本结构如下：
顺序控制语句 –goto

goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。

基本语法：

其中lable是已经定义好的标号名
顺序控制语句 –null

null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。

<2>编写分页过程

分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

无返回值的存储过程：

案例：

有返回值的存储过程（非列表）：

案例：

编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

案例扩张：

编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

有返回值的存储过程（列表[结果集]）：

案例：

编写一个过程，输入部门号，返回该部门所有雇员信息。

对该题分析如下：

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：

建立一个包，在该包中，定义类型test_cursor,是个游标。
建立存储过程。
用Java调用

编写分页过程：

要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。

后续还需要使用Java测试代码。

<3>例外

oracle将例外分为预定义例外，非预定义例外和自定义例外三种。

预定义例外：用于处理常见的oracle错误
非预定义例外：用于处理预定义例外不能处理的例外
自定义例外：用于处理与oracle错误无关的其它情况

处理预定义例外：

预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。

预定义例外 case_not_found

在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外：
预定义例外 cursor_already_open

当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open：
预定义例外 dup_val_on_index

在唯一索引所对应的列上插入重复的值时，会隐含的触发例外：
预定义例外 invalid_cursor

当试图在不合法的游标上执行操作时，会触发该例外：

例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
预定义例外 invalid_number

当输入的数据有误时，会触发该例外：

比如：数字100写成了loo就会触发该例外
预定义例外 no_data_found

下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外：
预定义例外 too_many_rows

当执行select into语句时，如果返回超过了一行，则会触发该例外。
预定义例外 zero_divide

当执行2/0语句时，则会触发该例外。
预定义例外 value_error

当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外。
其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)

1.login_denied

当用户非法登录时，会触发该例外

2.not_logged_on

如果用户没有登录就执行dml操作，就会触发该例外

3.storage_error

如果超过了内存空间或是内存被损坏，就触发该例外

4.timeout_on_resource

如果oracle在等待资源时，出现了超时就触发该例外

非预定义例外：

非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。

处理自定义例外：

预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外。

案例：

请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。