Oracle学习笔记--day05

PL/SQL编程
简介
SQL，一种通用的数据库语言，只能实现简单的功能(访问数据库操作)
Oracle公司对SQL进行了扩展，
扩展后得到的语言叫PL/SQL，过程化SQL语言（Procedural Language/SQL），
是Oracle数据库中的编程语言，功能更加强大，用于开发基于数据库的应用程序

PL/SQL 
Procedural Language/SQL [prə'si:dʒərəl]
过程化SQL语言
Oracle数据库中的编程语言,用于开发基于数据库的应用程序
编程过程中需要结合使用SQL

PL/SQL程序的基本组成单位是 块(block)
块
按照一定的语法，编写的一段程序
分类
匿名块
没有名字
不能被其他程序调用 是因为调用时都是通过名字调用
可以直接运行，临时使用
匿名块的代码不会存储在数据库中

命名块
有名字
可以重复调用
会被编译并存储在数据库中

存储过程 procedure [prəˈsi:dʒə(r)]
函数 function
触发器 trigger
包 package
包体 package body

子程序
就是命名块
有时候特指存储过程和函数
PL/SQL基础语法
匿名块语法：
declare
/*
*定义部分
*/
begin
/*
*执行部分
*/
exception
/*
*异常处理部分
*/
end; --块块结束标记，必须

declare
用于指定定义部分
在此处可以定义常量、变量、游标、异常、数据类型
可选
begin
用于指定执行部分
在此处编写要执行的PL/SQL语句和SQL语句
必须
exception
用于指定异常处理部分
处理执行部分在运行时可能会出现的错误
可选
end;
块结束标记 标识一个块结束了
必须
分号也是必须的



例：
begin
dbms_output.put_line('Hello World!');
end;
/

dbms_output是Oracle提供的一个系统包，put_line是该包下的一个存储过程，用于输出一个字符串
字符串是用单引号引起的字符序列
存储过程，不是说在这段程序执行的时候一定就会往数据库中存储数据，只是一个习惯的叫法，叫过程也可以
dbms_output.put_line(str);
使用dbms_output输出时，必须将sql*plus客户端的系统参数serveroutput设置为on或在sqlplus/admin/glogin.sql中添加set serveroutput on，sqlplus打开的时候会自动加载这个文件
set serveroutput on;(sqldeveloper也可以使用)
SQL*Plus中，SQL语句以分号结尾，表示命令结束并执行，且将该语句保存在缓存中；
不以分号结尾，只会将该命令保存到缓存中，并不执行
/表示执行缓存中的语句
缓冲区只存储最近的一条语句
SQL 缓冲区中无可运行的程序
在客户端工具中，在块中，;表示一条语句结束，执行要用/

块中可以使用Oracle提供的函数

定义变量或常量
变量的值可以改变
常量的值不可以改变

变量/常量名  [constant]   数据类型  [not null]  [:=/default 值]
:=
赋值运算符
如果没有赋值，值为null
default
指定默认(初始)值
constant
指定定义的是常量
定义时必须指定初始值，且其值一旦指定后不能被改变
如果未指定常量的初始值，则报错，出现符号 "CONSTANT"时需要下列之一： := 、default
值可以为null
not null
指定值不能为null
定义时必须指定初始值
如果没指定初始值，定义后则值就是null
值不能为null
数据类型
简单类型
定义的变量，只能存放一个值

varchar2(n)
长度可变的字符串
n指定的是字符串的最大字节数，最大值是32767
单位是字节
必须指定n

char(n)
固定长度的字符串
n指定的是字符串的字节数，最大值为32767
单位是字节
可以不指定n，默认值为1
boolean
布尔类型
值true|false
表的列不能采用该类型

空值
表示未知，不存在

其他的简单类型同Oracle表列的类型，请参考表列的类型：
number(p,s)
date
Oracle中日期的默认显示格式：
日-月-年
可以直接用默认日期格式的字符串给date类型的变量赋值

timestamp
clob
blob
复合类型
定义的变量，可以存放多个值

1.记录类型
一种自定义类型
基于其他已存在的类型，由其他的类型复合而成

一个记录可以包含多个成员

定义记录类型：
type 记录类型名 is record(
--成员定义列表
成员名1 类型1,
成员名2,类型2,
...
);
type emp_type is redcord(
ename varchar2(30),
job varchar2(30),
sal number
);

定义记录类型的变量
变量名 记录类型;

访问记录类型的变量的成员
记录变量名.成员名 [:=]

定义记录类型，定义的结果是定义了一个新的数据类型
例：
declare 
 type user_record_type is record(
id number,
username varchar2(60),
password varchar2(60),
birthday date,
married boolean,
--hobbies hobbies_varray_type
 );
 user_record user_record_type;
 v_temp varchar2(2) := '否';
begin
 user_record.id := 1;
 user_record.username := 'zhangsan';
 user_record.password := '123';
 user_record.birthday := '1-3月-17';
 user_record.married := false;
 --user_record.hobbies := hobbies_varray_type('吃饭','睡觉','打豆豆');
 
 if user_record.married then
v_temp := '是';
 end if;
 dbms_output.put_line('ID：' || user_record.id || '，用户名：' || user_record.username || '，密码：' 
|| user_record.password || '，出生日期：' 
|| to_char(user_record.birthday,'yyyy-mon-dd') || '，是否已婚：' || v_temp);
 --dbms_output.put_line('兴趣爱好：' || user_record.hobbies(1) || ',' || user_record.hobbies(2));
end;
2.数组类型
定义数组类型
type 数组类型名 is varray(数组最大大小) of 数组元素类型;
varray 可变数组，数组元素个数可变，但不能超过最大个数

初始化数组
数组变量名 := 数组类型名(元素1,元素2,...)
ORA-06531: 引用未初始化的收集(数组)

数组的属性
first 第一个元素的下标
last  最后一个元素的下标
数组变量名.属性

访问数组的元素
数组变量名(index) [:=]
index从1开始
可能会有 下标超出限制问题

例：
declare 
 type hobbies_varray_type is varray(4) of varchar2(4);
 hobbies_varray hobbies_varray_type;
begin
 hobbies_varray := hobbies_varray_type('吃饭');
 dbms_output.put_line(hobbies_varray.first);
 dbms_output.put_line(hobbies_varray.last);
--  hobbies_varray(5) := 'dfdsfds'; 错误
end;

PL/SQL中的特殊符号
运算符
算术
+ - * / **(幂运算符)

逻辑运算符
and or not

比较(关系)
> >= < <=
不等于：!= <> ^=
等于：=
is null  is not null
like
not like 
between x and y  <==>  >= x and <= y
in(值列表)
not in(值列表)

赋值运算符
变量名 := 值|expr 

字符串连接符
str1 || str2

表达式或列表分隔符
()

语句终止符
;
一条语句的结束标志
每条语句都必须以;结尾
注释符
-- 单行注释
/*
*多行注释
*/
注释会被编译器忽略
人能看得懂，机器看不懂，需要编译
范围操作符
..
1..10
表示[1,10]的数字范围
*游标属性提示符
%
PL/SQL语句
语句必须以;结束
表达式不是语句，可以作为语句一部分
赋值语句
变量名 := 值|表达式
null语句
null;
是一条语句，表示空语句，什么都不做 
PL/SQL中空语句不是直接一个;
begin
null;
end;
select语句
在块中可以执行select语句
在块中执行select语句时，必须使用 into子句 或 bulk collect into子句指定用变量把查询结果的所有内容都收集起来
bulk [bʌlk]大批的 collect 收集

否则，运行时提示在此 SELECT 语句中缺少 INTO 子句
into子句
把select语句的一行查询结果保存到变量中
这时，查询必须且只能返回1行，
否则，运行时提示返回的行数过多或未找到数据
因为into子句只能用于保存一行
这时，必须确保查询返回的一行中的所有列的值被接收保存了
否则，没有足够的值(变量指定少了)、值过多(变量指定多了)
几个列就定义几个变量
记录类型的变量

例：
declare
 v_dname varchar2(60);
 
begin
 select dname into v_dname from dept where 1 = 10;
end;
bulk collect into子句
把select语句的一批(0行、一行、多行)查询结果保存到变量中
考虑用数组，数组元素类型为记录类型，需要确保：记录类型的元素能保存一行的所有列的值，数组长度能保存下所有的行
这时数组不需要初始化，底层初始化
declare 
type dept_record_type is record(
 deptno number,
 dname varchar2(60),
 loc varchar2(60)
);
 type dept_varray_type is varray(2) of dept_record_type;
 --type dept_varray_type is varray(4) of dept%rowtype;
 dept_varray dept_varray_type;
 dept_record dept_record_type;
begin
 select * bulk collect into dept_varray from dept;
--  dbms_output.put_line(dept_varray(0).loc);
end;
%type
引用表列或已经定义过的变量的类型
用法：
变量名 表名.列名%type
变量名 已经定义过的变量名%type;
%rowtype
引用表的行类型作为数据类型，即表结构
是记录类型，它的成员与表结构是一致的
用法：
变量名 表名%rowtype
DML语句
在块中可以直接执行DML语句、commit、rollback
还可以使用returning子句指定返回受影响的行的指定列的值，这时，必须使用into子句 或 bulk collect into 子句进行收集
最后需不需要commit或rollback，要看客户端工具有没有开启自动提交，即有没有开事务

例：
declare 
 --v_dname dept.dname%type;
 --v_deptno number;
 type dname_varray_type is varray(20) of varchar2(60);
 dname_varray dname_varray_type;
begin
 --set autocommit off;
  update dept set dname = 'bbb' returning dname bulk collect into dname_varray;
 dbms_output.put_line(dname_varray(1));
end;
/

流程控制语句
条件分支语句
if后的条件表达式也可以用圆括号括起来
if与then可以不书写在同一行
if-then
if 条件 then
语句块
end if;
表示if语句的执行体的结束
条件的结果必须是boolean类型
if-then-else
if 条件 then
语句块1
else
语句块2
end if;
if-then-elsif
if 条件 then
…
elsif 条件 then
…
else
…
end if;
case
case [selector] --selector可选
when表达式then语句;
…
[else语句;]
end case;
存在selector时，表示匹配，选择第一个匹配的执行
不存在selector时，
表示判断是否为真，
如果when表达式不是boolean类型，则提示表达式类型错误
一旦找到匹配的when或判断条件为true，则执行对应的then，不再继续往下匹配或判断
begin
if 2 < 1 then
dbms_output.put_line('2 > 1');
elsif 3 > 1 then
dbms_output.put_line('3 > 1');
elsif 4 > 1 then
dbms_output.put_line('4 > 1');
else 
dbms_output.put_line('else');
end if;
end;
循环
基本循环
loop
/*循环体*/
end loop;
死循环，要在某一条件成立时，结束循环
例：
declare
v_count number := 0;
begin
loop
/*循环体*/

dbms_output.put_line(countx);
count := countx + 1;
if countx > 10 then
exit;
end if;
end loop;
end;
遍历数组：
declare
 type hobbies_varray_type is varray(100) of varchar2(60);
 hobbies_varray hobbies_varray_type := hobbies_varray_type('a','b','c','d');
 v_length number := hobbies_varray.last;
 v_index number := hobbies_varray.first;
begin
 loop
dbms_output.put_line(hobbies_varray(v_index));
v_index := v_index + 1;
if v_index > v_length then
 exit;
end if;
 end loop;
end;
for循环
for 循环变量 in [reverse] 开始值..结束值 
loop
/*循环体*/
end loop;

循环变量
是整数
Oracle会自动定义，不需要显示定义它
即使与声明部分定义的变量同名，此处也不是定义的变量，是自己在内部定义的
在循环中，使用的是循环变量
相当于局部变量覆盖了同名的全局变量，想使用同名的全局变量，改名字
循环变量的值在每次循环后会自动修改
没有指定reverse
表示升序循环
循环变量的初始值是指定范围的结束值
每次循环后，循环变量+1
指定了reverse
表示降序循环
循环变量的初始值是指定范围的开始值
每次循环后，循环变量-1

开始值<=结束值，否则不会执行循环体

循环变量一直修改到>最大值或<最小值时结束循环

例：
declare 
 counter number(3,1) := 2.3;
begin
 for counter in reverse  2..1
 loop
dbms_output.put_line(counter);
 end loop;
 dbms_output.put_line(counter);
end;

遍历数组
declare
 type hobbies_varray_type is varray(100) of varchar2(60);
 hobbies_varray hobbies_varray_type := hobbies_varray_type('a','b','c','d');
 v_length number := hobbies_varray.last;
 v_first number := hobbies_varray.first;
begin


 for v_index in v_first .. v_length
 loop
dbms_output.put_line(v_index);
dbms_output.put_line(hobbies_varray(v_index));
 end loop;
end;

while循环
while 条件
loop
/*循环体*/
end loop;
例：
declare
counter number := 0;
begin
while counter < 10 
loop
counter := counter + 1;
dbms_output.put_line(counter);
end loop;
end;

结束整个循环
exit;
exit when 条件;
当满足指定条件时，结束整个循环
结束本次循环，继续下一次循环
不会执行continue后的代码
continue;
continue;是11g新增内容
可使用goto lableName 语句模拟实现
跳转到指定位置处执行
<<lableName>>
continue when 条件;
当满足指定条件时，结束本次循环，继续下一次循环
游标


存放的是指针，指针就是地址
指向了系统为用户分配的一块内存，这块内存中存放select语句的查询结果
用于对查询语句返回的多行(0 、1、多行)进行处理
利用游标可以从结果集中每次提取一条记录

游标变量
隐式游标
显示游标

定义游标：
cursor 游标名 is select语句
定义时，select语句不执行
游标的操作：
打开游标：
open 游标名;
分配一块内存，执行select语句，把查询结果放到分配的内存中，同时把这块空间的地址保存在指定的游标中

从游标中取数据：
fetch 游标名 into var1,....|record
从游标中的地址对应的内存空间中取数据
最初，指向第一行
取的数据是指针当前正在指向的那行
一次取一行
每取一次之后，游标指针会自动移动到下一行
必须确保所有的列的值都会被接收保存了
关闭游标：
close 游标名;
游标用完后，要关闭，未关闭的游标会占用内存，占用资源，关闭后占用的内存会被释放
关了之后，就不可以再使用，否则无效的游标
例：
declare
 cursor cur_dept is select * from dept;
 dept_record dept%rowtype;
begin
 open cur_dept;
 fetch cur_dept into dept_record;
 dbms_output.put_line(dept_record.loc);
 close cur_dept;
end;

declare
 cursor cur_dept is select * from dept;
 dept_record dept%rowtype;
begin
 open cur_dept;
 loop
fetch cur_dept into dept_record;
--if cur_dept%notfound then
exit when cur_dept%notfound;
--end if;
dbms_output.put_line(dept_record.loc);
dbms_output.put_line(cur_dept%rowcount);
 end loop;
 close cur_dept;
end;

declare
 cursor cur_dept is select * from dept;
 dept_record dept%rowtype;
begin
 open cur_dept;
 fetch cur_dept into dept_record;
 while cur_dept%found
 loop 
--if cur_dept%notfound then
--exit when cur_dept%notfound;
--end if;
dbms_output.put_line(dept_record.loc);
dbms_output.put_line(cur_dept%rowcount);
fetch cur_dept into dept_record;
 end loop;
 close cur_dept;
end;
游标的属性：
%found 最后一次取数据(fetch)，是否取到了 true false
%notfound 最后一次取数据(fetch)，是否没取到 true false
%isopen 游标是否已打开 true false
%rowcount 返回当前从游标中已经取的记录个数
游标的使用：
打开游标
开始循环
从游标中取数据
处理从游标取得数据
退出循环
关闭游标
游标for循环
for 记录变量名 in 游标名
loop
/*循环体*/
end loop;

已经把打开游标、从游标中取数据、关闭游标这些操作做了

记录变量
不需要显示定义，Oracle会自动定义
记录类型，成员与查询结果中的每一行的结构相同
每次循环时，都存放了从游标中按顺序取出的一行数据，取完结束循环
例：
declare
 cursor cur_dept is select * from dept;
 dept_record dept%rowtype;
begin
for dept_record in cur_dept
loop
 dbms_output.put_line(cur_dept%rowcount);
 dbms_output.put_line(dept_record.loc);
end loop;
end;
异常
就是错误
异常的组成部分：
ORA-01476: 除数为 0

-01476   错误编号
除数为 0， 错误信息
ZERO_DIVIDE 错误名
预定义异常：
异常名 异常编号 异常信息
zero_divide -01476 除数为0
value_error   -06502 数据长度超出定义的最大长度或字符串转换为数字错误
no_data_found   -01403   未找到数据
too_many_rows   -01422   返回的结果多余一行
cursor_already_open   -06511 游标已经打开
invalid_cursor -01001 在不合法的游标上进行了操作

例：
declare
--  dept_record dept%rowtype;
--  v_result number(1) := 1111;
--  cursor cur_test is select * from dept;  
begin
--  v_result := 1 / 0;
--  open cur_test;
--  open cur_test;
--  close cur_test;
--  fetch cur_test into dept_record;
--  select * into dept_record from dept where 1 = 1;
dbms_output.put_line(1 + 'abc');
end;

异常处理部分
exception
when 异常名1 then 
/*异常处理1*/
when 异常名2 then 
/*异常处理2*/
.
.
.
when others then 其他异常处理
函数
sqlcode() 返回错误代码
sqlerrm() 返回错误消息
()可省略
例：
declare
 v_result number(3) := 'a' ;   
begin
 v_result := 'a';
exception  --处理执行部分在运行时可能会出现的错误
 when value_error then
dbms_output.put_line('异常编号：' || sqlcode || '        异常信息：'|| sqlerrm);
end;
自定义异常
异常名字 exception;
pragma exception_init(异常名字,oracle_error_number)
oracle_error_number:
必须为负数
有些已被Oracle使用了，有些可能会使用
留给用户用的：-20000~-20999
程序员手动抛出异常；
raise 异常名;
raise_application_error(code,msg)
抛出一个自定义异常
不需要预先定义
例：
declare
 v_oper1 number := 2;
 v_oper2 number := 0;
 v_result number;
 my_divide_error exception;
 pragma exception_init(my_divide_error,-1);
begin
 if v_oper2 = 0 then
raise my_divide_error;
 end if;
 v_result := v_oper1 / v_oper2;
 dbms_output.put_line('结果是：' || v_result);
exception
 when my_divide_error then
dbms_output.put_line('被除数不能为0');
end;


命名规范
简单类型变量 v_
常量 cons_
游标 cursor_
记录类型 _record_type
记录类型变量 _record
数组类型 _varray_type
数组类型变量 _varray
包 _package
存储过程 _procedure
函数 _function
触发器 _trigger
能自动转成字符串的类型
number、date、timestamp、null
dbms_output.put_line(1|date|);