PL/SQL编程 变量 控制语句 游标
PL/SQL编程概念
-
PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。
- 有利于客户/服务器环境应用的运行
- 过程化
- 模块化
- 运行错误的可处理性
- 提供大量内置程序包
-
在PL/SQL中，只能使用SQL语句中的DML部分，不能使用DDL部分，如果要在PL/SQL中使用DDL（如 create table 等）的话，只能以动态的方式来使用。
-
PL/SQL程序由三个块组成，声明部分，执行部分，异常处理部分。
declare -- 声明部分：在此声明PL/SQL用到的变量，类型，及游标，以及局部存储过程的函数。 -- 标识符名不能超过30字符 -- 第一个字符必须是字母 -- 不分大小写 -- 不能用-号开头 -- 不能是sql保留字 -- 不要把变量声明写成跟表中字段名一样 begin -- 执行部分：过程及sql语句，即程序的主要部分。 exception -- 执行异常部分：错误处理 end
变量
声明变量位于declare声明部分
变量类型：
| 类型 | 子类 | 说明 |
|---|---|---|
| char | character和String | 定长字符串 |
| varchar2 | 可变长字符串 | |
| number | Dec，Double，Integer,Int,Numeric,Real,Small | |
| long | 变长字符串 | |
| Date | ||
| boolean | ||
| rowid |
给变量赋值
-
普通赋值
语法：变量名 变量类型 := 变量值；
示例：i int := 10;
-
数据库赋值（into）
select语句赋值，每次select语句执行一次，就赋值一次
declare name varchar2(20); begin select ename into name from emp where empid = 10; dbms_output.put_line(name); endinsert,update,delete数据时用return赋值
declare id rowid; name varchar2(20); begin insert into emp(eid,ename) values(123,'钟文涛') return eid,ename into id,name; dbms_output.put_line(id || '' || name); end
复合数据类型
-
记录类型
把逻辑相关的，分离的，基本数据类型的变量组成一个整体储存起来。需要先在声明部分先定义记录的组成，记录的变量，然后在执行部分引用该记录变量本身或其中成员。
declare type AA is record( id int, name varchar2(20) ) a AA; begin select eid,ename into a from emp where empid =10; dbms_output.put_line(a.id || '' || a.name); end%type
name em=.ename%type; -- 优点 -- 所引用数据库类型可以不知道，表的列类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。%rowtype
-- %rowtype 代表一个记录行 declare a emp%rowtype; begin select * into a from emp where empid = 10; dbms_output.put_line(a.ename); end -
表类型
与记录类型相似，但是它对记录类型的扩展。它可以处理多行记录，类似于高级数组中的二维数组，使得可以在PL/SQL中模仿数据库中的表。
控制语句
if 语句
语法：
if 布尔表达式 then
PL/SQL或者SQl语句
elsif 布尔表达式 then
其它语句
else
其它语句
end if;
示例：
declare
sal emp.esal%type,
id emp.eid%type
begin
id :=&请输入员工编号--&产生一个输入窗口，输入字符串需要带上单引号
select esal into sal from emp where empid = id;
if sal>20000 then
dbms_output.put_line('土豪');
elsif sal > 10000 then
dbms_output.put_line('小康');
else
dbms_output.put_line('雕塑');
end if;
end
case表达式（sql语句和PL/SQL语句中都能使用）
语法：
case 条件表达式 --可以省略，如果省略 when 可以写成条件表达式，如果不省略就要写成条件表达式
when 条件表达式结果1 then
语句1
when 条件表达式结果2 then
语句2
else
都不满足执行语句
end
示例：
declare
sf varchar(20);
begin
select case when sal>20000 then '土豪' when sal>10000 then '小康' else '屌丝' end into sf from emp where empid = 10;
dbms_output.put_line(sf);
end
循坏语句
-
简单循坏
语法：
loop 要执行的语句; exit when<条件语句> --满足条件时，就退出循坏 end loop;示例：
declare i int := 0; begin loop i := i+1; exit when i=100; end loop; end -
while循坏
语法：
while<布尔表达式> loop 要执行的语句; end loop;示例：
declare i int :=0; begin while i<10 loop i:=i+1; end loop; end -
数字式循坏
语法：
for 循坏计数器 in 下限..上限 loop 要执行的语句 end loop;示例：
declare i int :=0; begin for i in 0..100 loop dbms_output.put_line(i); end loop; end
游标
游标提供了一种对表中检索出的数据进行操纵的灵活手段，就本质而言，游标实际上是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。在PL/SQL块中执行DML语句时，Oracle会在内存中为其分配一个上下文区，即缓冲区。游标是指向该区的一个指针。
游标分为，显式游标，隐式游标。
-
显式游标
显式游标主要用于对查询语句的处理，尤其是在查询结果为多条记录的情况下。
显式游标处理需要四个步骤。
-
定义/声明游标
就是定义一个游标名，以及与其相对应的select语句。
CURSOR cursor_name[(parameter[, parameter]…)] [RETURN datatype] IS select_statement;
其中， parameter语法为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。
-
打开游标
就是执行游标所对应的select语句，将其查询的结果放入工作区，并且指向工作区的首部，标识游标结果集合。
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
PL/SQL 程序不能用OPEN 语句重复打开一个游标。
-
提取游标数据
检索结果集中的数据行，放入指定的输出变量中。
FETCH cursor_name INTO {variable_list | record_variable };
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行
执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行
%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
%NOTFOUND 布尔型属性，与%FOUND相反；
%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
-
关闭游标
当提取和处理完游标结果集数据后，应及时关闭游标，以释放该游标占用的系统资源。并使该游标的工作区变成无效，不能在使fetch语句取其中的数据，关闭后的游标可以使用open语句重新打开。
close cursor_name;
-
示例：
-- 无参 示例1
declare
cursor c is select * from emp;
type c2 is record(
id emp.eid%type,
name emp.ename%type,
loc emp.eloc%type
);
c3 c2;
begin
open c;
fetch c into c3;
while c%found loop
dbms_output.put_line(c3.id || '' || c3. name || '' || c3.loc);
fetch c into c3;
end loop;
close c;
end;
-- 有参 示例2
declare
cursor c(id emp.eid%type) is select * from emp where eid=id;
c2 c%rowtype;
begin
open c(10);
fetch c into c2;
while c%found loop
dbms_output.put_line(c2.eid);
fetch c into c2;
end loop;
close c;
end;
游标的for循坏
PL/SQL语言提供了游标for循坏语句，自动执行游标的open，fetch，close语句，和循坏语句功能。
语法：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游标数据处理代码
END LOOP;
示例：
-- 示例1 注：for后面的变量y不需要定义
declare
cursor c is select * from emp;
begin
for y in c loop
dbms_output.put_line(y.ename);
end loop;
end;
-- 示例2
declare
cursor c(id int) is select * from emp ;
begin
for y in c(20) loop
dbms_output.put_line(y.ename);
end loop;
end;
-- 示例3 子查询
declare
begin
for y in (select * from emp) loop
dbms_output.put_line(y.ename);
end loop;
end
-
隐式游标
对于非查询语句，如修改，删除等操纵，则由Oracle系统自动地为这些操纵设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标。
隐式游标的名字为sql
对于隐式游标的操纵，如定义，打开取值及关闭的操纵，都由Oracle系统自动地完成，无需用户处理。
用户只能通过隐式游标的相关属性，来完成相应的操纵。
在隐式游标的工作区当中，所存放的数据是与用户自定义的显示游标无关的，最新处理的一条sql语句所包含的数据。
属性 select insert update delete 值 sql%isopen false false false false sql%found 有结果 成功 成功 true sql%found 没结果 失败 失败 false sql%notfound 没结果 失败 失败 true sql%notfound 有结果 成功 成功 false sql%rowcount 放回行数，只为1 插入的行数 修改的行数 删除的行数 示例：
-- 示例1 -- 删除a表中某部门的所有员工，如果该部门中已没有员工，则在b表中删除该部门。 declare c a.id%tyep :&'请输入部门编号'; begin delete from a where id=c; if sql%notfound then delete from b where id=c; else end if; end; -- 示例2 -- 通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。 declare update emp set sal = 0; dbms_output.put_line(sql%rowtype); end
锁定游标结果集
? 游标修改和删除操纵是在指定游标下，修改或删除表中指定的数据行。这时，要求游标查询语句必须使用for update选项，以便在打开游标时锁定游标结果集在表中对应数据行的所以列和部分列。
SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
nowait表示被另一会议加锁，不等待，立即返回并给出resource busy and acquire with nowait specified信息
在update和delete中使用游标
如果使用forupdate声明游标，则可在delete中和update语句中使用where current of cursor_name子句，修改或删除游标结果集合当前对应的数据库表中的数据行。
示例：
-- 从a表中查询某部门的员工情况，将其工资少于1000的调整为1000
declare
cursor c is select * from a for update;
begin
for y in c loop
if y.sal < 1000 then
update a set sal = 1000 where current of c;
end if;
end loop;
end;