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