PLSQL开发笔记和小结
时间：2021-07-01 10:21:17
帮助过：8人阅读
*****************************************
PLSQL基本结构
*****************************************
基本数据类型变量
1. 基本数据类型
Number 数字型
Int 整数型
Pls_integer 整数型，产生溢出时出现错误
Binary_integer 整数型，表示带符号的整数
Char 定长字符型，最大255个字符
Varchar2 变长字符型，最大2000个字符
Long 变长字符型，最长2GB
Date 日期型
Boolean 布尔型(TRUE、FALSE、NULL三者取一)
在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。
2. 基本数据类型变量的定义方法
变量名 类型标识符[not null]:
=值;
declare
age number(
3):
=26;
--长度为3，初始值为26
begin
commit;
end;
其中，定义常量的语法格式：
常量名 constant 类型标识符 [not null]:
=值;
declare
pi constant
number(
9):
=3.1415926;
--为pi的数字型常量，长度为9，初始值为3.1415926
begin
commit;
end;
表达式
变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。
1. 数值表达式
PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，
可以使用的算术运算符包括+(加法)、
-(减法)、
*(乘法)、
/(除法)和
**(乘方)等。
命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10
+3*4-20+5**2的值，理论结果应该是27。
―――――――――――――――――――――――――――――――――――――
set serveroutput
on
Declare
result integer;
begin
result:=10+3*4-20+5**2;
dbms_output.put_line(‘运算结果是：‘||to_char(result));
end;
―――――――――――――――――――――――――――――――――――――
dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。
2. 字符表达式
字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“||”。
3. 关系表达式
关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符包括以下9种。
< 小于
> 大于
= 等于(不是赋值运算符:
=)
like 类似于
in 在……之中
<= 小于等于
>= 大于等于
!= 不等于 或
<>
between 在……之间
关系型表达式运算符两边的表达式的数据类型必须一致。
4. 逻辑表达式
逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种。
NOT：逻辑非
OR：逻辑或
AND：逻辑与
运算的优先次序为NOT、AND和OR。
PLSQL函数
PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。
To_char：将其他类型数据转换为字符型。
To_date：将其他类型数据转换为日期型。
To_number：将其他类型数据转换为数值型。
继续追加中..
系统输出打印
利用pl/sql在数据库服务器端打印一句话：
set serveroutput
on--设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello PL/SQL‘);
END;
pl/sql程序中对大小写不敏感(打印声明的变量)
―――――――――――――――――――――――――――――――――――――
set serveroutput
on
DECLARE
v_char varchar2(
20):
=‘a‘;
v_char1 varchar2(
20):
=‘b‘;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_char);
DBMS_OUTPUT.PUT_LINE(v_char1);
END;
pl语句块是pl/sql里最小的编程块，其中可以再嵌套begin
end
begin
dbms_output.put_line(‘Hello World‘);
dbms_output.put_line(‘2*3=‘||(
2*3));
dbms_output.put_line(‘what‘‘s‘);
end;
―――――――――――――――――――――――――――――――――――――
PL/SQL中的变量声明
所有变量必须在declare中声明，程序中不允许声明。
没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：PL/SQL中变量一般以v_开头
(等同于存储过程中as和begin区域的变量定义习惯)。
注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存。
long是字符类型，boolean类型不能打印。
标准变量类型：数字，字符，时间，布尔。
―――――――――――――――――――――――――――――――――――――
declare
v_number1 number;
v_number2 number(
3,
2) ;
v_number3 binary_integer :=1;
v_name varchar2(
20) :
=‘kettas‘;
v_date date :=sysdate;
v_long long :
=‘ni hao‘;
v_b boolean := true;
begin
if (v_number1
is null)
then
dbms_output.put_line( ‘hello‘);
end if;
dbms_output.put_line(v_number1);
dbms_output.put_line(v_number2);
dbms_output.put_line(v_number3);
dbms_output.put_line(v_name);
dbms_output.put_line(v_date);
dbms_output.put_line(v_long);
--dbms_output.put_line(v_b); --执行该句ORACLE提示“调用‘PUT_LINE‘ 时参数个数或类型错误”
end;
―――――――――――――――――――――――――――――――――――――
备注：
关于声明number(4,
3)中括号中的两个数字的意义，前面的数字叫精度，后面的叫刻度。
刻度：
当刻度为正数的时候，表示四舍五入到小数点后面的位数
当刻度为负数的时候，表示四舍五入到小数点前面的位数
精度：
从数字的最前面不为零开始到刻度精确到的位置
v_Number number(
4,
3):
=123.12312
1、按刻度进行四舍五入得到123.
123
2、确定刻度精确到的位置123123处，精度为6位(.符号不算)
2、根据精度进行判断6位(
>4)精度上限值
--报错不能存储
number(
3,
-3):
=44445
1、根据刻度－3进行四舍五入得到44000
2、小数点向前移动3位44.此位置为刻度精确到的位置
3、根据精度进行判断2位(
<3)精度上限值
--不报错可存储结果为44000
DECLARE
v_Number number(
4,
3):
=123.12312;
--实际精度6位大于上限精度值4位，提示“ORA-06502:PL/SQL: 数字或值错误 : 数值精度太高”
BEGIN
DBMS_OUTPUT.PUT_LINE(v_Number);
END;
DECLARE
v_Number number(
7,
3):
=4555;
--实际精度7位等于上限精度值，可以存储
BEGIN
DBMS_OUTPUT.PUT_LINE(v_Number);
END;
*****************************************
变量赋值方式
*****************************************
oracle中变量赋值方式是值拷贝而非引用
declare
v_number1 number:
=100;
v_number2 number;
begin
v_number2:=v_number1;
v_number1:=200;
dbms_output.put_line(v_number1); --200
dbms_output.put_line(v_number2);
--100
end;
*****************************************
PLSQL复合类型
*****************************************
记录类型record
record类型最常用，声明的时候可以加not null，但必须给初始值，如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值。引用记录型变量的方法是“记录变量名.基本类型变量名”。
―――――――――――――――――――――――――――――――――――――
declare
type t_first is record(
idnumber(3),
namevarchar2(20)
);
v_first t_first;
begin
v_first.id:=1;
v_first.name:=‘cheng‘;
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
end;
record类型变量间赋值
declare
type t_first is record(
id number,
name varchar2(
20)
);
v_first t_first;
v_second t_first;
begin
v_first.id:=1;
v_first.name:=‘susu‘;
v_second:=v_first;
--相互赋值
v_first.id:=2;
v_first.name:=‘kettas‘;
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
dbms_output.put_line(v_second.id);
dbms_output.put_line(v_second.name);
end;
―――――――――――――――――――――――――――――――――――――
表类型变量table
语法如下：
type 表类型 is table of 类型
index by binary_integer;
表变量名 表类型;
类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于Java中的Map容器，就是一个可变长的数组，
key(符号整数索引)必须是整数，可以是负数，value(类型)可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。
1. 定义一维表类型变量
―――――――――――――――――――――――――――――――――――――
declare
type t_tb is table of varchar2(
20)
index by binary_integer;
v_tb t_tb;
begin
v_tb(100):
=‘hello‘;
v_tb(98):
=‘world‘;
dbms_output.put_line(v_tb(100));
dbms_output.put_line(v_tb(98));
end;
类型为record的表类型变量
declare
type t_rd is record(id
number,namevarchar2(
20));
type t_tb is table of t_rd
index bybinary_integer;
v_tb2 t_tb;
begin
v_tb2(100).id:
=1;
v_tb2(100).name:
=‘hello‘;
--dbms_output.put_line(v_tb2(100).id);
--dbms_output.put_line(v_tb2(100).name);
dbms_output.put_line(v_tb2(
100).id
||‘ ‘||v_tb2(
100).name);
end;
―――――――――――――――――――――――――――――――――――――
2. 定义多维表类型变量
该程序定义了名为tabletype1的多维表类型，相当于多维数组，table1是多维表类型变量，将数据表tempuser.testtable中recordnumber为60的记录提取出来
存放在table1中并显示。
―――――――――――――――――――――――――――――――――――――
declare
type tabletype1 is table of testtable
%rowtypeindex
by binary_integer;
table1 tabletype1;
begin
select * into table1(
60) fromtempuser.testtable
where recordnumber
=60;
dbms_output.put_line(table1(60).recordnumber
||table1(
60).currentdate);
end;
备注：在定义好的表类型变量里，可以使用count、delete、first、last、
next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。
set serveroutput
on
declare
type tabletype1 is table ofvarchar2(
9)
index by binary_integer;
table1 tabletype1;
begin
table1(1):
=‘成都市‘;
table1(2):
=‘北京市‘;
table1(3):
=‘青岛市‘;
dbms_output.put_line(‘总记录数：‘||to_char(table1.
count));
dbms_output.put_line(‘第一条记录：‘||table1.first);
dbms_output.put_line(‘最后条记录：‘||table1.last);
dbms_output.put_line(‘第二条的前一条记录：‘||table1.prior(
2));
dbms_output.put_line(‘第二条的后一条记录：‘||table1.
next(
2));
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
%type和
%rowtype
*****************************************
使用%type定义变量，为了让PL
/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了
%type定义方法。这样当数据表的字段类型修改后，PL
/SQL程序中相应变量的类型也自动修改。
―――――――――――――――――――――――――――――――――――――
create table student(
id number,
name varchar2(
20),
age number(
3,
0)
);
insert into student(id,name,age)
values(
1,
‘susu‘,
23);
--查找一个字段的变量
declare
v_name varchar2(
20);
v_name2 student.name%type;
begin
select name
into v_name2
from studentwhere rownum
=1;
dbms_output.put_line(v_name2);
end;
--查找多个字段的变量
declare
v_id student.id%type;
v_name student.name%type;
v_age student.age%type;
begin
select id,name,age
into v_id,v_name,v_age fromstudent
where rownum
=1;
dbms_output.put_line(v_id||‘ ‘||v_name
||‘ ‘||v_age);
end;
--查找一个类型的变量，推荐用*
declare
v_student student%rowtype;
begin
select * into v_student
from student whererownum
=1;
dbms_output.put_line(v_student.id||‘ ‘||v_student.name
||‘ ‘||v_student.age);
end;
--也可以按字段查找，但是字段顺序必须一样，不推荐这样做
declare
v_student student%rowtype;
begin
select id,name,age
into v_student
from student whererownum
=1;
dbms_output.put_line(v_student.id||‘ ‘||v_student.name
||‘ ‘||v_student.age);
end;
declare
v_student student%rowtype;
begin
select id,name,age intov_student.id,v_student.name,v_student.age
from student
where id
=1;
--select * intov_student.id,v_student.name,v_student.age from student where id=1;
dbms_output.put_line();
end;
―――――――――――――――――――――――――――――――――――――
备注：insert，
update，
delete，select都可以，createtable，droptable不行。DPL，DML，和流程控制语句可以在pl
/sql里用，但DDL语句不行。
declare
v_name student.name%type:
=‘wang‘;
begin
insert into student(id,name,age)
values(
2,v_name,
26);
end;
begin
insert into student(id,name,age)
values(
5,
‘hehe‘,
25);
end;
declare
v_name student.name%type:
=‘hexian‘;
begin
update student
set name
=v_name
where id
=1;
end;
begin
update student
set name
=‘qinaide‘ whereid
=2;
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
PLSQL变量的可见空间
*****************************************
变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字<<outername
>>,通过outername变量名来访问外部过程的变量(待测试..)。
―――――――――――――――――――――――――――――――――――――
declare
v_i1binary_integer:=1;
begin
declare
v_i2binary_integer:=2;
begin
dbms_output.put_line(v_i1);
dbms_output.put_line(v_i2);
end;
dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2); 解开后执行Oracle会提示“必须说明标识符‘V_I2‘”
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
PLSQL流程控制
*****************************************
if判断
declare
v_b boolean:=true;
begin if v_b
then
dbms_output.put_line(‘ok‘);
end if;
end;
if else判断
declare
v_b boolean:=true;
begin
if v_b
then
dbms_output.put_line(‘ok‘);
else
dbms_output.put_line(‘false‘);
end if;
end;
if elsif else判断
declare
v_name varchar2(
20):
=‘cheng‘;
begin
if v_name
=‘0701‘ then
dbms_output.put_line(‘0701‘);
elsif v_name=‘cheng‘ then
dbms_output.put_line(‘cheng‘);
else
dbms_output.put_line(‘false‘);
end if;
end;
loop循环，注意推出exit是推出循环，而不是推出整个代码块
declare
v_i binary_integer:=0;
begin
loop
if v_i
>10 then
exit;
end if;
v_i:=v_i
+1;
dbms_output.put_line(‘hehe‘);
end loop;
dbms_output.put_line(‘over‘);
end;
loop简化写法
declare
v_i binary_integer :=0;
begin
loop
exit when v_i
>10;
v_i :=v_i
+1;
dbms_output.put_line(‘hehe‘);
end loop;
dbms_output.put_line(‘over‘);
end;
while循环
declare
v_i binary_integer:=0;
begin
while v_i
<10 loop
dbms_output.put_line(‘hello‘||v_i );
v_i:=v_i
+1;
end loop;
dbms_output.put_line(‘over‘);
end;
for循环，注意不需要声明变量
begin
for v_i
in 0..
10 loop
dbms_output.put_line(‘hello‘||v_i);
end loop;
dbms_output.put_line(‘over‘);
end;
*****************************************
PLSQL异常处理
*****************************************
1、声明异常
异常名 EXCEPTION；
2、抛出异常
RAISE 异常名
3、处理异常
抛出异常后的逻辑代码不会被继续执行
异常的定义使用
―――――――――――――――――――――――――――――――――――――
begin
dbms_output.put_line(1/0);
exception
when othersthen
dbms_output.put_line(‘error‘);
end;
declare
e_myException exception;
begin
dbms_output.put_line(‘hello‘);
raisee_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。
dbms_output.put_line(
‘world‘);
dbms_output.put_line(1/0);
exception
whene_myException then
dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码
dbms_output.put_line(sqlerrm);
--当前错误信息
dbms_output.put_line(
‘my error‘);
when othersthen
dbms_output.put_line(‘error‘);
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
PLSQL游标和goto语句
*****************************************
备注：下面提到的游标为静态cursor，包括显示和隐式。
游标，从declare、open、
fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl
/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。
cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
declare
cursor c_studentis
select * from book;
begin
open c_student;
close c_student;
end;
第二种游标的定义方式，用变量控制结果集的数量。
declare
v_id binary_integer;
cursor c_student isselect
* from book
where id
>v_id;
begin
v_id:=10;
open c_student;
close c_student;
end;
第三种游标的定义方式，带参数的游标，用的最多。
declare
cursor c_student(v_idbinary_integer)
is select * from book
where id
>v_id;
begin
open c_student(
10);
close c_student;
end;
游标的使用，一定别忘了关游标。
declare
v_student book%rowtype;
cursor c_student(v_idbinary_integer)
is select * from book
where id
>v_id;
begin
open c_student(
10);
fetch c_student intov_student;
close c_student;
dbms_output.put_line(v_student.name);
end;
如何遍历游标fetch
游标的属性 %found，
%notfound,
%isopen,
%rowcount。
%found:若前面的fetch语句返回一行数据，则
%found返回true，如果对未打开的游标使用则报ORA
-1001异常。
%notfound，与
%found行为相反。
%isopen，判断游标是否打开。
%rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA
-1001。
注：
no_data_found和%notfound的用法是有区别的，小结如下
1)
SELECT. . .
INTO 语句触发 no_data_found；
2)当一个显式光标(静态和动态)的
where 子句未找到时触发
%notfound；
3)当UPDATE或DELETE语句的where 子句未找到时触发 sql
%notfound；
4)在光标的提取(
Fetch)循环中要用
%notfound 或
%found 来确定循环的退出条件，不要用no_data_found。
下面是几个实例：
create table BOOK
(
ID VARCHAR2(
10)
not null,
BOOKNAME VARCHAR2(
10)
not null,
PRICE VARCHAR2(
10)
not null,
CID VARCHAR2(
