PL/SQL编程

PL/SQL编程
1.1 PL/SQL基础
1.1.1 PLSQL概念
Procedural Language / SQL , 过程语言/SQL，是结合了Oracle过程语言和结构化查询语言的一种扩展语言。通过PL/SQL可以编写出具有很多高级功能的程序
在PLSQL块中，可以使用select,insert,update,delete等DML语句，事务控制语句及SQL函数，但不允许直接使用create,drop,alter等DDL语句，可以通过动态SQL执行。
1.1.2 PLSQL特点
 具有编程语言的特点
 能够给程序添加控制语句
 拥有异常处理机制
 可移植性好
 减少网络的交互，提高程序性能
1.1.3 PLSQL组成
一个PL/SQL块由3部分组成，分别是声明部分，执行部分，异常处理部分，其中执行部分不能省略。
[DECLARE]
–此处声明变量、游标等
BEGIN
–执行部分，过程或SQL语句，即程序主体
[EXCEPTION]
–异常捕捉部分
END
1.1.4 变量和常量
1.1.4.1 变量的定义
变量名 数据类型 [:=赋值];
例如： v_name VARCHAR2(20);
1.1.4.2 变量的赋值
v_name VARCHAR2(20) := ‘张三’;

1.1.4.3 常量
语法：变量名 CONSTANT 数据类型 [:=赋值];
v_name CONSTANT VARCHAR2(20) := ‘张三’;

1.1.4.4 注意
 变量名长度不能超过30个字符
 第一个字符必须是字母
 不区分大小写
 不能使用减号
 不能使用sql关键字

1.1.5 属性类型
 %type : 引用某张表中某列的类型
v_name emp.ename%type := ‘tom’;
 %rowtype ：引用某张表中一行的类型
v_row emp%rowtype;
select * into v_row from emp where empno=7788;
1.1.6 注释
单行注释：–
多行注释：/* */
1.1.7 控制语句
1.1.7.1 概述
控制语句共有3种类型，分别是条件控制、循环控制和顺序控制

1.1.7.2 条件控制
1.1.7.2.1 概述
条件控制用于根据条件执行一系列语句，包括if和case语句

1.1.7.2.2 If

1.1.7.2.3 if-else

1.1.7.2.4 if-elsif-else

注意：不能写成elseif

1.1.7.2.5 case

1.1.7.3 循环控制
1.1.7.3.1 概述
循环控制有3种类型，分别是loop、while、for循环
1.1.7.3.2 loop循环

1.1.7.3.3 While循环

1.1.7.3.4 for循环

1.1.7.4 顺序控制
顺序控制用于按顺序执行语句，包括null和goto语句，不推荐使用goto
null语句：null语句是一个可执行语句，相当于一个占位符或不执行任何操作的空语句
1.2 异常处理
1.2.1 分类
预定义异常：当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发 用户定义异常：用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
1.2.2 预定义异常

1.2.3 用户自定义异常
 使用RAISE关键字引发异常
 使用EXCEPTION关键字处理异常

1.2.4 案例
1.2.4.1 预定义异常

1.2.4.2 自定义异常

1.3 游标
1.3.1 基本原理
 游标是指向查询结果区域的一个指针
 游标为应用程序提供了一种对多行数据查询结果集中的每行数据进行单独处理的办法
1.3.2 游标分类
静态游标：隐式游标和显示游标
动态游标
1.3.3 显示游标
1.3.3.1 使用步骤

1. 声明游标
2. 打开游标
3. 提取游标
4. 关闭游标
   1.3.3.2 声明游标

1.3.3.3 打开游标

1.3.3.4 提取游标

1.3.3.5 关闭游标

1.3.3.6 显式游标属性
 %FOUND ：只有在DML语句影响一行或者多行时才返回true，表示有执行结果
 %NOTFOUND ：与上一个属性相反，如果没有影响任何行则返回true
 %ROWCOUNT：返回受影响行数，如果没有，则返回0
 %ISOPEN：判断游标是否打开
1.3.3.7 案例

1.4 存储过程
1.4.1 概念
存储过程相当于java中的方法，可以实现方法的重用
1.4.2 创建存储过程
1.4.2.1 语法
create [or replace] procedure procedure_name
[(parameter_list)]
{is | as}
[local_declarations]
begin
executable_statements
[exception]
[exception_handlers]
end [procedure_name];

1.4.2.2 案例
1.4.2.2.1 无参

1.4.2.2.2 带参无返回值

1.4.2.2.3 带参有返回值

1.4.3 调用存储过程
1.4.3.1 PLSQL直接调用

1.4.3.2 SQLPlus命令调用

1.4.4 存储过程的参数传递
 按位置传递
proc_calc(5, 6);
 按名称传递
proc_calc(num2=>1, num1=>2);
1.4.5 存储过程的参数模式
 in 输入参数，默认
 out 输出参数
 int out 输入输出参数
1.4.6 存储过程的访问权限
必须拥有创建存储过程的权限
grant execute on proc_calc to 用户名;
1.4.7 删除存储过程
Drop procedure 存储过程名称
1.5 存储过程的调试与跟踪
 创建存储过程
 在浏览器窗口Procedures节点找到要调试的存储过程
 选中要调试的存储过程右键 --> 添加调试信息
 选中要调试的存储过程右键 --> 测试，打开测试窗口
 在右侧调试窗口先填写存储过程的参数值
 点击开始调试器开始调试，然后点击单步进入或者单步跳过开始执行
 想要查看的值可以在变量窗口填写
1.6 存储过程规则
 输入参数的in关键字可有可无
 输入参数不能带长度
 is/as任选其一但不能省略
 局部变量声明不能写declare关键字
 每一个局部变量声明完成都要分号结束
 结束可以用end，也可以用end proc_name