Oracle总结PLSQL学习

PLSQL介绍

PLSQL是Oracle对SQL99的一种扩展，基本每一种数据库都会对SQL进行扩展，Oracle对SQL的扩展就叫做PLSQL…

SQL99是什么

（1）是操作所有关系型数据库的规则

（2）是第四代语言

（3）是一种结构化查询语言

（4）只需发出合法合理的命令，就有对应的结果显示

SQL的特点

（1）交互性强，非过程化

（2）数据库操纵能力强，只需发送命令，无需关注如何实现

（3）多表操作时，自动导航简单，例如：

select emp.empno,emp.sal,dept.dname

from emp,dept

where emp.deptno = dept.deptno

（4）容易调试，错误提示，直接了当

（5）SQL强调结果

PLSQL是什么

是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL

过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。

PLSQL强调过程

这里写图片描述

为什么要用PLSQL

因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处，

SQL和PLSQL不是替代关系，是弥补关系

PLSQL语法

declare和exception都是可以省略的，是不能省略的。

在PLSQL程序中：；号表示每条语句的结束，/表示整个PLSQL程序结束

PLSQL与SQL执行有什么不同：

（1）SQL是单条执行的

（2）PLSQL是整体执行的，不能单条执行，整个PLSQL结束用/，其中每条语句结束用；号

PLSQL变量

既然PLSQL是注重过程的，那么写过程的程序就肯定有基本的语法，首先我们来介绍PLSQL的变量

PLSQL的变量有4种

number

varchar2

与列名类型相同

与整个表的列类型相同

这里写图片描述

何时使用%type，何时使用%rowtype？

当定义变量时，该变量的类型与表中某字段的类型相同时，可以使用%type

当定义变量时，该变量与整个表结构完全相同时，可以使用%rowtype，此时通过变量名.字段名，可以取值变量中对应的值

项目中，常用%type

判断体

语法：

这里写图片描述

值得注意的是：eslif并没有写错的，它是少了一个e的

循环

在PLSQL中，循环的语法有三种：

WHILE循环：

while后面跟的是循环条件，与java的差不多，LOOP和END LOOP是关键字**

LOOP循环：

exit后面的条件成立了才退出循环【有点绕】

FOR循环：

循环的递增只能是1，不能自定义步长

游标

Oracle中的游标其实就是类似JDBC中的resultSet，就是一个指针的概念。

既然是类似与resultSet，那么游标仅仅是在查询的时候有效的。

语法

例外、异常

我们在上面看PLSQL中的语法已经知道，有一个exception，这个在Oracle中称为例外，我们也可以简单看成就是Java中的异常。。。

语法：

这里写图片描述

存储过程和存储函数

在Oracle中，存储过程和存储函数的概念其实是差不多的，一般地，我们都可以混合使用。只不过有的时候有的情况使用过程好一些，有的情况时候函数的时候好一些。下面会讲解在什么时机使用过程还是函数的。

首先，我们在学习存储过程和存储函数之前，先要明白我们为什么要学他….

其实存储过程和函数就是类似与我们在Java中的函数的概念….

到目前为止，我们的PLSQL是有几个缺点的：

PLSQL不能将其封装起来，每次调用的时候都要将整片代码复制来调用

有的时候，我们想要将PLSQL的代码保存起来，只能自己手动保存在硬盘中，非常麻烦

我们学数据库就是为了让程序能够调用的，但是PLSQL不能让程序（java）调用

因此，存储过程和存储函数就能解决上面的问题了，能够将代码封装起来，保存在数据库之中，让编程语言进行调用….

这里写图片描述

存储过程和函数的语法

过程的语法：

函数的语法：

无论是过程还是函数，as关键字都代替了declare关键字。

创建第一个过程：

调用过程的三种方式：

exec过程名【SQLPLUS中使用】

PLSQL程序调用

Java调用

PLSQL调用

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

调用：

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

创建过程：在过程中的参数，默认值是IN，如果是输出的话，那么我们要指定为OUT。

调用：在调用的时候，使用到的psal，pname，pjob在调用的时候都没有定义的，因此我们需要先定义变量后使用！

创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

调用：在PLSQL中，赋值的语句不是直接“=”，而是:=

如果写的是=号，那么就会出现以下的错误：

创建有参存储函数findEmpNameAndJobAndSal(编号)，查询7788号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值

调用函数：

过程与函数的使用场景

我们发现过程与函数的区别其实是不大的，一般我们都可以用函数来实现的时候， 也可以使用过程来实现….

但是，总有些情况，使用函数比使用过程要好，使用过程比使用函数要好，那什么时候使用过程，什么时候使用函数呢？？？

不难发现的是，函数是必定要有一个返回值的，当我们在调用的时候，接受返回值就直接获取就行了。

也就是说

当返回值只有一个参数的时候，那么就使用存储函数！

当返回值没有参数或者多于一个参数的时候，那么就使用过程！

SQL与过程函数使用场景

【适合使用】过程函数：

》需要长期保存在数据库中

》需要被多个用户重复调用

》业务逻辑相同，只是参数不一样

》批操作大量数据，例如：批量插入很多数据

【适合使用】SQL：

》凡是上述反面，都可使用SQL

》对表，视图，序列，索引，等这些还是要用SQL

触发器

在PLSQL中也有个类似与我们Java Web中过滤器的概念，就是触发器…触发器的思想和Filter的思想几乎是一样的….

这里写图片描述

值得注意的是：对于触发器而言，是不针对查询操作的。也就是说：触发器只针对删除、修改、插入操作！

触发器语法

这里写图片描述

创建语句级触发器insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】，显示"hello world"

调用：

结果：

这里写图片描述

星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,

语法：raise_application_error('-20000','例外原因')

插入数据、响应触发器：

这里写图片描述

创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，语法：for each row/:new.sal/:old.sal

可以使用:new.sal/:old.sal来对比插入之前的值和插入之后的值

调用：

这里写图片描述