2019-05-11
PL(Procedural Language)/SQL概述
在实际工作中，有些复杂的数据处理操作需要在客户端编程实现，但是客户端所处理的数据必须由数据库服务器通过网络传输而来，如果数据处理量非常大，采用客户端编程的方法就降低了数据处理速度。为了减少客户端与数据库服务器端之间的网络数据传输量，提高数据处理速度，可以直接在数据库服务器端编程，实现对数据的处理，处理完成后直接将处理结果返回客户端。为此Oracle提供了在数据库服务器端编程的语言，即PL/SQL。
SQL只是一种声明式语言，是非过程性的，语句之间相互独立。在实际工作中，许多事务处理应用都是过程性的，前后语句之间存在一定关联。PL/SQL是过程化SQL，可以弥补SQL语句的不足。可以通过IF和LOOP语句控制程序的执行流程；可以定义变量以便在语句之间传递数据信息。PL/SQL是对标准SQL语言的扩展， SQL语句完全可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。
pl/sql的分类：
-
匿名的pl/sql:只是一段pl/sql语句块，只是在运行时执行一次，不会在数据库系统中永久存储。
-
命名的pl/sql：pl/sql存储在数据库系统中，可以按照名称重复调用。包括存储过程、函数、触发器等
[ declare 定义语句段 ; ] --声明部分，可选项，用于声明变量
begin
执行语句段 ; --执行部分，可包含流程控制和sql语句
[ exception 异常处理语句段 ; ] --异常处理部分，可选项
end;
示例：根据用户输入的员工编号获得其工资和奖金
declare
var_empno number; --员工编号
var_sal number; --工资
var_comm number; --奖金
begin
--接收用户输入的员工编号
var_empno:=&var_empno;
--获得工资
select sal,nvl(comm,0) into var_sal,var_comm from scott.emp where empno=var_empno;
dbms_output.put_line('员工编号是'||var_empno||'的工资是'||var_sal||'奖金是'||var_comm);
end;
示例：根据用户输入的员工编号进行涨工资10%，但是如果增长后的工资超过3200则不予增长
declare
--员工编号
var_empno number;
--工资
var_sal number;
begin
--获得员工编号
var_empno:=&var_empno;
--根据编号获得其工资
select sal into var_sal from scott.emp where empno=var_empno;
--判断工资增长后是否超过3200
if var_sal*1.1<=3200 then
--更新工资
update scott.emp set sal=sal*1.1 where empno=var_empno;
commit;
end if;
end;
变量与数据类型
变量名 数据类型 := 初值 ;
变量名 数据类型 default 初值 ;
- 长度不能超过30个字符，不能有空格。
- 由字母、数字、下划线、美元符号$和#号组成，必须以字母开头。
- 不能使用PL/SQL或SQL的关键字。可以使用help reserved words命令获得关键字列表。
- 变量默认值为null。
变量命名规范
| 标 识 符 | 命名规则 | 例 子 |
|---|---|---|
| 程序变量 | v_name | v_student_name |
| 程序常量 | c_name | c_company_name |
| 游标变量 | cursor_name | cursor_emp |
| 异常标志 | e_name | e_too_many |
| 表类型 | name_table_type | emp_record_type |
| 表 | name_table | emp_table |
| 记录类型 | name_record | emp_record |
| 绑定变量 | g_name | g_year_sal |
变量的标准数据类型：
number：数字类型，可以代表整数和浮点数。
int：整数型。
pls_integer：整数型，计算速度快（直接由cpu执行），占用较少的存储空间。但产生溢出时将触发异常。
binary_integer：带符号的整数型，不会出现溢出，但计算速度较慢（由oracle模拟执行）。
char：定长字符，最长255个字符。
varchar2：变长字符，最长2000个字符。
long：变长字符，最长2gb。
date：日期型。
boolean：布尔型(true或false)。
流程控制语句
条件语句
if <表达式> then
语句块;
end if;
if <表达式> then
语句块1;
else
语句块2;
end if;
if <表达式1> then
语句块1;
elsif <表达式2> then
语句块2;
else
语句块3;
end if;
case语句
case <表达式>
when <表达式1> then 语句块1;
when <表达式2> then 语句块2;
……
when <表达式n> then 语句块n;
[ else 语句块 n+1 ]
end ;
循环语句
loop
循环体;
exit when 循环退出条件
end loop;
while 循环条件
loop
循环体;
end loop;
for 循环控制变量 in [reverse] 循环下限 .. 循环上限
loop
循环体;
end loop
for循环示例
declare
sum_num number := 0;
begin
for i in 1..100 loop
sum_num := sum_num + i;
end loop;
dbms_output.put_line(sum_num);
end;
%TYPE变量
%TYPE用来定义与数据表中指定字段数据类型相同的变量。如果表中字段的数据类型或长度发生变化，%TYPE变量会自动随之变化。这样用户不必查看表中各个字段的数据类型，就可以确保所定义的变量能够正确存储字段数据。
变量名 方案名.表名.字段名%type
var_empno scott.emp.empno%type; --员工编号
自定义记录类型变量
自定义记录数据类型可以表示由多个字段值组成的一行数据。使用时首先要定义记录类型的结构，然后声明该类型的变量存储数据。
type 类型名称 is record (
变量名称 数据类型 := 默认值 ,
……
变量名称 数据类型 := 默认值
) ;
示例1：输入职工编号，输出其姓名与工资
declare
type employee_type is record ( --声明自定义记录类型
ename scott.emp.ename%type,
sal scott.emp.sal%type
);
var_emp employee_type ; --声明自定义记录类型变量
var_empno scott.emp.empno%type;
begin
var_empno:=&var_empno;
select ename, sal into var_emp from scott.emp where empno=var_empno ;
dbms_output.put (var_emp.ename) ;
dbms_output.put_line(' '||var_emp.sal) ;
end;
示例2：允许同一记录类型的两个变量整体赋值。
declare
type employee_type is record (
ename scott.emp.ename%type,
sal scott.emp.sal%type
);
var_emp1 employee_type ;
var_emp2 employee_type ;
var_empno scott.emp.empno%type;
begin
var_empno:=&var_empno;
select ename, sal into var_emp1 from scott.emp where empno=var_empno ;
var_emp2:=var_emp1;
dbms_output.put (var_emp2.ename) ;
dbms_output.put_line(' '||var_emp2.sal) ;
end;
%ROWTYPE变量
%ROWTYPE类型可以根据数据表的行结构定义数据类型，用于存储从数据表中检索到的一行完整的数据。如果数据库表的结构发生变化，记录变量中的结构也将随之改变。
变量名 方案名.表名%rowtype
示例：输入职工编号，输出其详细信息
declare
var_emp scott.emp%rowtype ; --数据行变量
var_empno scott.emp.empno%type;
begin
var_empno:=&var_empno;
select * into var_emp from scott.emp where empno=var_empno ;
dbms_output.put (var_emp.ename) ;
dbms_output.put_line(' '||var_emp.job) ;
dbms_output.put_line(' '||var_emp.sal) ;
end;
异常处理
PL/SQL程序运行时出现的错误称为异常。可以分为两类：
预定义异常： 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户自定义异常：用户可以声明自定义异常，自定义的异常通过 raise 语句手动引发
系统预定义异常
begin
过程及sql语句;
exception
when 异常名称then
过程及sql语句;
when others then
过程及sql语句;
end;
| 命名的系统异常 | 产生原因 |
|---|---|
| ACCESS_INTO_NULL | 未定义对象 ，视图给一个没有初始化的对象赋值 |
| CASE_NOT_FOUND | CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 |
| COLLECTION_IS_NULL | 集合元素未初始化 |
| CURSER_ALREADY_OPEN | 游标已经打开 |
| DUP_VAL_ON_INDEX | 唯一索引对应的列上有重复的值 |
| INVALID_CURSOR | 在不合法的游标上进行操作 |
| INVALID_NUMBER | 内嵌的 SQL 语句不能将字符转换为数字 ，即将一个非有效的字符串转换成数字 |
| NO_DATA_FOUND | 使用 select into 未返回行，或应用索引表未初始化的元素时 ；查询语句无返回数据，或者引用了一个被删除的元素，或者引用了一个没有被初始化的元素 |
| TOO_MANY_ROWS | 执行 select into 时，结果集超过一行 |
| ZERO_DIVIDE | 除数为 0 |
| SUBSCRIPT_BEYOND_COUNT | 元素下标超过嵌套表或 VARRAY 的最大值 |
| SUBSCRIPT_OUTSIDE_LIMIT | 使用嵌套表或 VARRAY 时，将下标指定为负数 |
| VALUE_ERROR | 赋值时，变量长度不足以容纳实际数据 |
| LOGIN_DENIED | PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 |
| NOT_LOGGED_ON | PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 |
| PROGRAM_ERROR | PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 |
| ROWTYPE_MISMATCH | 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 |
| SELF_IS_NULL | 使用对象类型时，在 null 对象上调用对象方法 |
| STORAGE_ERROR | 运行 PL/SQL 时，超出内存空间 |
| SYS_INVALID_ID | 无效的 ROWID 字符串 |
| TIMEOUT_ON_RESOURCE | Oracle 在等待资源时超时 |
可以使用others子句捕获各种异常，同时结合两个函数向用户报告信息：
sqlcode函数：返回出错码
sqlerrm函数：返回出错信息
begin
insert into scott.emp(empno , ename , job , sal , deptno)
values('7999','ATG','CLERK',1500,'ABC');
exception
when dup_val_on_index then
dbms_output.put_line('捕获dup_val_on_index异常');
dbms_output.put_line('该主键值已经存在');
when others then -- others子句必须放在各种异常处理的最后
dbms_output.put_line( '错误号：' || sqlcode );
dbms_output.put_line( '错误描述：' ||sqlerrm );
end;
用户自定义异常
程序开发人员可以根据具体的业务逻辑规则，自定义特定异常。当用户操作违反了业务逻辑规则后，可以手动引发该异常，从而中断程序的正常执行，转到自定义的异常处理代码。
自定义异常的处理步骤：
定义异常处理：异常名 exception ;
触发异常处理：raise 异常名
网友评论