Oracle学习笔记--day05

左手的ㄟ右手 2022-06-17 14:14 189阅读 0赞

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|);