PLSQL学习笔记.docx

下面我为这个系列文章做一个索引

一： 常量变量及数据类型初步
       1：常量变量
       2：数据类型（一）

二：变量作用域及数据类型深入
       1：变量作用域
       2：数据类型（二）

三：流程控制
       1：条件控制
       2：循环控制与顺序控制
四：异常处理
       1：预定义异常错误
       2：非预定义异常错误
       3：自定义异常并抛出

五：约束
       1：非空约束
       2：唯一约束
       3：主键约束
       4：外键约束
       5：检查约束

六：索引
       1：索引描述
       2：索引分类
       3：各类索引的应用场合

七：视图
       1：创建或者修改一个视图
       2：删除一个视图
       3：更新视图中的数据
       4：视图中的约束和主键

八：过程
       1：创建或者修改一个过程
       2：删除一个过程

九：函数
       1：创建或者修改一个函数
       2：删除一个函数

十：触发器
       1：语句级触发器
       2：行级触发器
       3：instead  of触发器(视图上的触发器)
       4：删除触发器

十一：程序包
       1：程序包的说明
       2：程序包体
       3：调用程序包
       4：删除程序包

十二：游标
       1：普通游标
       2：有参数的游标
       3：有返回值的游标
       4：运行过程中获取游标的内容
       5：隐式游标
       6：for循环简化游标的操作
       7：在游标中更新或删除数据
一：常量和变量
开始之前，还是照例做个经典的例子，如下：
declare
mydate varchar2(16) := 'hellow world';
begin
dbms_output.put_line(mydate);
end;

declare是声明部分
我在这个部分声明了varchar2类型的变量，名字叫mydata，内容是hellow world，长度是16个长度。
begin….end 部分是执行部分
我在这里调用了oracle已有的过程dbms_output.put_line，这个过程起到了打印的目的
执行完后就打印出了我刚才定义的变量。
注意PL/SQL与T-SQL一样是不区分大小写的
我是用pl/sql developer工具执行上述程序的（如果有必要以后介绍下oracle的安装，基本操作和相关工具）
下面贴两个图，看一下：

你可以通过使用constant关键字声明常量，如：
mydate constant varchar2(16) := 'hellow world';

二：数据类型（不全）

1：number(p,s)    声明数字
p表示精度，即：这个数字一共有几位，p取值1～38
s表示刻度，即：这个数字的小数点位于第几位（从右向左数），s取值-84～127
两个参数都是可选的，
刻度可以为负数，如：
mydate number(6,-2) := 123456;
此变量输出是为：123500。如你所见他被四舍五入了
另需注意：
mydate number(6,2) := 1234.123456;
如上，赋值的数字明显超过了精度，但是可以成功执行，得到的变量为
1234.12，精度为6。同样是四舍五入起的作用
不要试图这样做：
mydate number(6,2) := 123456.123456;
因为系统再怎么四舍五入都满足不了精度要求

2：char(n)    用于定义固定长度的字符串
n表示长度，n不应超过2000，是可选参数
如果你定义了n，但是存储的字符串长度比n小，那么系统会给你用空格补齐

3：varchar2(n)    用于定义可变长度的字符串
n是必须的，表示字符串的最大长度，取值范围1~32767
你定义了一个varchar2的数据，长度比n小，系统不会给你补空格！      

4：boolean       定义逻辑变量
取值范围是true，false，null

5：date      定义日期数据
只包含日期，公元前4712到公元9999年，一般不会超过限制
占7个字节

6：timestamp(s)     还是日期型数据
存储年、月、日、时、分、秒、 上午、下午
s表示秒的小数位数，在0~9之间取值

7：LOB     存储文件
BLOG：主要用于存储图像文件
CLOB：主要用于存储文本文件
BFILE：主要用于声音、视频文件
所有文件不要超过4G
一：变量作用域
DECLARE
current_block VARCHAR2(10) := 'Outer';
outer_block VARCHAR2(10) := 'Outer';
BEGIN
dbms_output.put_line('[current_block]['||current_block||']');
DECLARE
current_block VARCHAR2(10) := 'Inner';
BEGIN
dbms_output.put_line('[current_block]['||current_block||']');
dbms_output.put_line('[outer_block]['||outer_block||']');
END;
dbms_output.put_line('[current_block]['||current_block||']');
END;

以上为引用一本书的代码（Oracle Database 11g PL/SQL程序设计）
输出为
[current_block][Outer]
[current_block][Inner]
[outer_block][Outer]
[current_block][Outer]

这是两个嵌套的程序块
我们在子程序块中重定义了父程序块的变量
这个变量在子程序块执行完之后又恢复了父程序块中定义的值

二：%type    取一个已知变量的数据类型
declare mytitle labor.xland.title%type;
begin
select title into mytitle from labor.xland where state = 0;
dbms_output.put_line(mytitle);
end;

上面例子中取了表title列的数据类型（varchar2）
然后检索了一行一列数据，把这个数据赋值给我们定义的变量mytitle
当然你也可以定义多个变量
如：
select title,id into mytitle,myid from labor.xland where state = 0;

三：%rowtype   取一个已知行的数据类型，这应该是一个对象类型
declare
myrow labor.xland%rowtype;
begin
select * into myrow from labor.xland where state = 0;
dbms_output.put_line(myrow.title);
dbms_output.put_line(to_char(myrow.state));
end;

上例中我们取了某个表的rowtype，并定义了变量myrow
接着把这个表的一行数据赋值给了这个变量
输出的时候只要以点来获取这一行的某个列即可

四：record   这应该也是一个对象
declare
type myrecordtype is record
(
mytitle labor.xland.title%type,
mystate labor.xland.state%type
);
myrecord myrecordtype;
begin
select title, state into myrecord from labor.xland;
dbms_output.put_line(myrecord.mytitle);
dbms_output.put_line(to_char(myrecord.mystate));
end;
如上代码
我们先定义了一种数据类型（type）
这种类型包含两个属性，分别是mytitle和mystate
然后我们定义了一个变量myrecord
这个变量的类型就是我们刚才定义的数据类型
接着我们把两个数据赋值给类这个变量的两个属性
这里的赋值顺序是按属性定义的先后顺序给属性赋值的
然后输出

至此，数据类型仍未讲完
还有table（表）,array（数组）,exception（异常）,cursor（游标）等类型等没讲
异常和游标将在后面的文章提到
三：流程控制

一：if条件控制
先看一段程序：
declare
v_content varchar2(66);
begin
select content into v_content from xland where title='xland';
if length(v_content)>6 then
v_content := substr(v_content,0,6)||'...';
else
v_content := v_content||'（全部数据）';
end if;
dbms_output.put_line(v_content);
end;
这段程序用到了if…then  else  end if结构
1.其中length()函数可以获取一个字符串的字符长度
另外还有lengthb()函数，可以获取一个字符串的字节长度
2.substr()函数，可以按字符长度获取一个字符串的子字符串，
另外还有substrb()函数，可以按字节长度获取一个字符串的子字符串
3.select… 句是从一个表里获取一条数据，并把数据存储在我们定义的变量中
注意一定要是一条数据，因为我们的变量不是table或者record类型
至于这些类型会在后面的文章中提到

另外还有if…then   elseif…then   else   end if结构
这里就不举例子了
注意elseif是连在一起写的，这很像vb的语法，与C#语法有区别

二：case条件控制
先看例子：
declare
v_content number;
begin
select length(content) into v_content from xland where title='xland';
case
when v_content>6 then
dbms_output.put_line('长度为：'||to_char(v_content));
when v_content<6 then
dbms_output.put_line('长度为：'||to_char(v_content));
else
dbms_output.put_line('长度为：'||to_char(v_content));
end case;
end;
其中：to_char()是将一个日期或数字转换成字符串类型的函数
我这里只做演示
实际使用当中case分支控制结构当然不会这样写

再看一个例子：
declare
v_content number;
begin
select length(content) into v_content from xland where title='xland';
case v_content
when 16 then
dbms_output.put_line('长度为：'||to_char(v_content));
else
dbms_output.put_line('长度为：'||to_char(v_content));
end case;
end;
这个就不多解释了。
一：LOOP...END LOOP
先看代码
declare
v_flag number := 1;
begin
loop
exit when v_flag>16;
v_flag := v_flag+1;
dbms_output.put_line(to_char(v_flag));
end loop;
end;
其中exit when是跳出循环的条件，
注意代码中的分号，挺重要的。没有分号会报错

二：while……loop….end loop
几个和循环结构相关的代码，我比较喜欢这个，如下：
declare
v_flag number := 1;
begin
while v_flag<16 loop
v_flag := v_flag+1;
dbms_output.put_line(to_char(v_flag));
end loop;
end;

注意这个示例程序和上个示例程序的输出结果是不同的
具体为什么不同我就不多解释了

三：for…loop…end loop
还是类似的示例程序：
declare
v_flag number := 1;
begin
for v_flag in 2..16 loop
--v_flag := v_flag+1;
dbms_output.put_line(to_char(v_flag));
end loop;
end;

注意我用--注释掉了一句
因为在这个循环体内，是不允许为哨兵变量赋值的
哨兵变量不知道啥意思？hehe
--是注释单行的写法
/*  …..*/可以注释多行

四：顺序控制goto
看例子：
declare
v_flag number := 1;
begin
while v_flag<16 loop
v_flag := v_flag+1;
dbms_output.put_line(to_char(v_flag));
if mod(v_flag,6) = 0 then
goto lable_1;
end if;
end loop;
<<lable_1>>
dbms_output.put_line('跳出来了');
end;
mod(v,n)是取余数的意思，
这里写这个函数其实没意义
还不如直接判断变量是否等六
另外需要注意的是goto语句有很多限制
以下都是不被允许的：
1跳转到非执行语句前面
2跳转到子块中
3跳转到条件语句中
4跳转到循环语句中
5从条件语句的一部分跳转到另一部分
6从异常处理部分跳转到执行部分
一：预定义异常错误
先看代码：
declare
mytitle labor.xland.title%type;
begin
select title into mytitle from labor.xland where state = 2;
dbms_output.put_line(mytitle);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
end;

这段程序当检索到数据的时候就输出数据
检索不到数据即输出  没有找到数据
no_data_found 是一个预定义异常错误类型
更多预定义异常错误，请看：

二：非预定义异常错误
先看代码：
declare
v_sqlcode number;
v_sqlerrm varchar2(2048);
begin
insert into labor.xland values(null,'1111',1);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when others then
if sqlcode = -1400 then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
dbms_output.put_line(to_char(v_sqlcode));
dbms_output.put_line(v_sqlerrm);
end if;
end;
when 块不必跟end结束
if块需跟end if结束
sqlcode为错误码
sqlerrm为oracle反馈的错误信息
此程序输出：
-1400
ORA-01400: 无法将 NULL 插入 ("LABOR"."XLAND"."TITLE")
另外还有pragma exception_init(name,errcode)函数来处理非预定义异常
如：
declare
v_exception exception;
pragma exception_init(v_exception,-1400);
begin
insert into labor.xland values(null,'1111',1);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when v_exception then
dbms_output.put_line('没有找到数据1');
end;
这里提到了一个新的数据类型  exception
pragma exception_init(name,errcode)
把错误号为-1400的错误赋值给v_exception
在第二个when子句中不能使用sqlcode等系统变量

三：自定义异常并抛出
先看代码
declare
v_exception EXCEPTION;
begin
RAISE v_exception;
exception
when v_exception then
dbms_output.put_line('捕获异常');
end;
先定义一个异常
然后抛出这个异常
然后捕获这个异常
如此而已
约束就那么几种
1  NOT NULL  非空

2  UNIQUE  唯一

3  PRIMARY KEY  主键
主键既是非空约束也是唯一约束

4  FOREIGN KEY  外键

5  CHECK   检查
就这个比较灵活多变
以下是一个较长用的check约束

lie is null or (lie between 100 and 250)

关于约束，
有两点我觉得涉及到比较高级的话题，我这里不想多说了
一个是延迟检查
一个是约束激活和禁用约束的时候是否验证约束

另外还有一些东西，我觉得没必要，也不讲了
一：索引描述
一个索引可以由一个或多个列组成
对列设置索引其实就是对列的内容按一定的方式进行排序
检索数据的时候，检索排过序的数据
检索到最后一个有效数据之后就跳出检索
这样就不必进行全表扫描了，
同时可以应用很多算法提高检索效率
数据库多用二分法检索数据

二：索引分类
1  唯一索引（Unique）
不允许列内有重复的值
2  非唯一索引（normal）
允许列内有重复的值（默认创建非唯一索引）
3  位图索引（bitmap）
这类索引和上两类索引的实现逻辑不通

三：各类索引的应用场合
当你的列中有很多重复的数据时适合建立位图索引
当查询时多用and 或or等逻辑运算符时适合建立位图索引
当查询条件多用大于小于等比较运算符时适合建立唯一或非唯一索引

下面看一个索引

另外
1  可以对函数创建索引，暂时先不谈这里的知识
2  索引可以由PL/SQL编程创建，但人们一般都是用工具完成这个工作的，索引我也不多介绍了
3  索引还有是否压缩和是否倒序的属性，用到的时候不多，也不介绍了
一：创建或者修改一个试图
create or replace view v_xland as
select title,content from labor.xland;

二：删除一个视图
drop view v_xland;

三：更新视图中的数据
如果检索一个视图里的数据没有什么限制的
如果想对视图进行更新、删除、新建数据那么该视图就要满足一下条件
1视图中不能有union   distinct  group by order by 的关键字或子句
2视图中不能有子查询
3视图中不能有分组函数
4需要更新的列不是由列表达式定义的
5表中所有NOT NULL列都在视图中

四：视图中的约束和主键
1：对视图中的某个列做了约束后不符合约束条件的数据不会出现在视图中
2：基础表中的主键出现在视图中将成为视图的主键

八：过程
一：创建和修改一个过程
create or replace procedure xland_proc
(v_title in varchar2,v_int out number)
is
v_char varchar2(111);
begin
select labor.xland.state,title into v_int,v_char from labor.xland where title = v_title;
end xland_proc;

存储过程有两个参数
一个是输入参数
一个是输出参数
is后面begin前面
是过程的声明部分
如果返回一个记录集就要用到游标了

执行这个存储过程
declare
x number;
begin
xland_proc('xland',x);
dbms_output.put_line(to_char(x));
end;

输出结果0
可以这样调用存储过程
你的过程名(过程形参=>你的变量,过程形参=>你的变量)

二：删除一个存储过程

九：函数

先看程序
create or replace function get_content
(v_title in xland.title%type,v_content out xland.content%type)
return number
is
v_state number;
begin
select state,content into v_state,v_content from xland where title = v_title;
return v_state;
end get_content;
参数可分为输入参数和输出参数
函数还有返回值
is和begin之间是定义部分
函数其实与过程类似

看调用过程
declare
xland varchar2(222):= 'xland';
content varchar2(2048);
out_v number;
begin
out_v := get_content(xland,content);
dbms_output.put_line(to_char(out_v));
dbms_output.put_line(content);
end;

定义了三个变量
前两个是函数的参数
后一个是返回值

看输出结果
0
xland is my name

删除一个过程
drop function yourfuncname

十：触发器
一：语句级触发器
语句级触发器是指当执行DML操作时，以语句为单位执行的触发器
（注意与下面提到的行级触发器比较）
先看代码
create or replace trigger xland_trigger
before insert
or update
or delete
on labor.xland
begin
if(to_char(sysdate,'DAY') in ('星期六','星期日'))
or (to_char(sysdate,'HH24') not between 8 and 18) then
raise_application_error(-20001,'不是上班时间');
end if;
end;

执行以下代码测试
insert into labor.xland (xland.title,xland.content,xland.state) values ('123','234',3);
ORACLE抛出异常

二：行级触发器
行级触发器是指执行DML操作时，以数据行为单位执行的触发器，每一行都执行一次触发器
先看代码：
create or replace trigger xland_trigger
before insert
on labor.xland
for each row
begin
if :new.title = 'xland' then
insert into labor.xland (xland.title,xland.content,xland.state) values ('123','cha2',3);
end if;
end;
执行以下代码测试
insert into labor.xland (xland.title,xland.content,xland.state) values ('xland','cha1',3);

结果：

在行级触发器中可以对列的值进行访问（很重要！）
列名前加   :old.    表示变化前的值
列名前加   :new.    表示变化后的值
在when子句中不用冒号。

三：instead of 触发器（视图上的触发器）
先看代码
create or replace trigger t_xland
instead of insert
on v_xland
for each row
begin
insert into xland (title,content,state) values ('1','1',1);
end;

其实就是取代了insert语句和其他触发器没什么大区别

四：删除触发器
drop trigger t_xland;

十一：程序包

一：程序包的说明
create or replace package p_xland
is
pragma serially_reusable;
v_val varchar2(32);
function set_val(ref_val varchar2) return varchar2;
end p_xland;
程序包可以包含多个变量，函数，过程。
函数或过程应该放在变量之后声明
pragma serially_reusable;决定创建的包是否可以连续使用。
有此句，调用包的时候结果将不受以前调用所影响

二：程序包体
create or replace package body p_xland
is
pragma serially_reusable;
function set_val(ref_val varchar2) return varchar2
is
begin
v_val := ref_val;
return 'myreturnval';
end set_val;
end p_xland;
程序包体和程序包的名字必须相同
程序包体内的过程或者函数与普通的过程函数相同
总之程序包就像一个对象

三：调用程序包
declare
v_myval varchar2(32):='xland';
begin
p_xland.v_val := v_myval;
dbms_output.put_line(p_xland.v_val);
v_myval := p_xland.set_val('xland2');
dbms_output.put_line(p_xland.v_val);
dbms_output.put_line(v_myval);
end;

下面是执行结果：
xland
xland2
myreturnval

四：删除程序包
drop package body your_pb_name
drop package your_p_name

十二：游标

一：普通游标
declare
--定义record类型变量
type v_record is record
(title labor.xland.title%type,state labor.xland.title%type);
--定义字符串类型变量
v_title labor.xland.title%type;
--定义一个游标
cursor c1 is
select title from labor.xland;
begin
open c1;--打开游标
loop
fetch c1 into v_title;--把游标中的数据存入变量，可以有多个变量
if c1%found then--如果找到数据,找不到数据为notfound
dbms_output.put_line(v_title);
else
dbms_output.put_line('已经处理完结果集');
exit;--退出循环
end if;
end loop;
close c1;--关闭游标
end;

输出结果
new xland
123
xland
123
xland
已经处理完结果集

二：有参游标
declare
--定义record类型变量
type v_record is record
(title labor.xland.title%type,state labor.xland.title%type);
--定义字符串类型变量
v_title labor.xland.title%type;
--定义有参数的游标
cursor c1(v_no number) is
select title from labor.xland where state > v_no;
begin
open c1(0);--打开游标，传递参数
loop
fetch c1 into v_title;--把游标中的数据存入变量，可以有多个变量
if c1%found then--如果找到数据,找不到数据为notfound
dbms_output.put_line(v_title);
else
dbms_output.put_line('已经处理完结果集');
exit;--退出循环
end if;
end loop;
close c1;--关闭游标
end;

输出结果
123
xland
123
xland
已经处理完结果集

三：有返回值的游标
declare
--定义一个类型
type t_record is record
(title labor.xland.title%type,state labor.xland.state%type);
--定义v_record类型的变量
v_record t_record;
--定义字符串类型变量
v_title labor.xland.title%type;
--定义有返回值的游标
cursor c1(v_no number) return t_record is
select title,state from labor.xland where state > v_no;
begin
open c1(v_no => 0);--打开游标，传递参数(参数的另一种传递方式)
loop
fetch c1 into v_record;--把游标中的数据存入变量，可以有多个变量
exit when c1%notfound;
dbms_output.put_line(v_record.title||' '||to_char(v_record.state));
end loop;
close c1;--关闭游标
end;

输出结果
123 3
xland 3
123 3
xland 3