引言:PLSQL数据类型

标量数据类型：数字类、字符类、日期类、布尔类(boolean)、

复合数据类型：记录(%rowtype)、表、数组

引用类型：REF CURSOR

LOB类型:BLOB、CLOB

1.系统定义的记录：%rowtype

使用%rowtype属性定义记录变量:可以基于表或视图定义记录变量

当使用%ROWTYPE属性定义记录变量时,记录成员个数,名称,类型与表或视图列的个数，

名称,类型完全相同.

1.1 在select语句中使用PL/SQL记录

declare

v_emp emp%rowtype;

begin

select * into v_emp from emp where empno=7788;

dbms_output.put_line(v_emp.ename);

end;

1.2 在insert语句中使用PL/SQL记录

declare

dept_record dept%rowtype;

begin

dept_record.deptno:=50;

dept_record.dname:='administrator';

dept_record.loc:='beijing';

insert into dept values dept_record;

end;

1.3 在insert语句VALUES子句中使用记录成员

declare

dept_record dept%rowtype;

begin

dept_record.deptno:=60;

dept_record.dname:='sales';

insert into dept (deptno,dname) values

(dept_record.deptno,dept_record.dname);

end;

1.4在UPDATE语句中使用PL/SQL记录

在SET子句中使用记录变量

declare

dept_record dept%rowtype;

begin

dept_record.deptno:=60;

dept_record.dname:='sales';

dept_record.loc:='shanghai';

update dept set row=dept_record where deptno=60;

end;

在SET子句中使用记录成员

declare

dept_record dept%rowtype;

begin

dept_record.loc:='guangzhou';

update dept set loc=dept_record.loc where deptno=10;

end;

1.5在DELETE语句中使用PL/SQL记录:只能在DELETE语句中的WHERE子句中使用记录成员

declare

dept_record dept%rowtype;

begin

dept_record.deptno:=50;

delete from dept where deptno=dept_record.deptno;

end;

2.用户自定义记录

首先定义类型，在声明该类型的变量。

2.1 定义记录类型

declare

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

dno emp.deptno%type);

begin

null;

end;

语法：

type 记录类型名 is record(

属性名 数据类型,

...

);

2.2 定义记录类型变量

emp_record emp_record_type;

语法：

变量名 记录类型名;

declare

--自定义类型emp_record_type

type emp_record_type is record(

ename emp.ename%type,

sal emp.sal%type);

--定义变量，变量的类型是emp_record_type

emp_record emp_record_type;

begin

--变量赋值

select ename,sal into emp_record from emp where empno=&no;

dbms_output.put_line(emp_record.ename||' '||emp_record.sal);

end;

2.3 在select into 语句中使用自定义记录

--set serveroutput on

declare

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

dno emp.deptno%type);

emp_record emp_record_type;

begin

select ename,sal,deptno into emp_record from emp where empno=&no;

dbms_output.put_line(emp_record.name);

end;

2.4 在select into 语句中使用记录成员

declare

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

dna emp.deptno%type);

emp_record emp_record_type;

begin

select ename,sal into emp_record.name,emp_record.salary

from emp where empno=&no;

dbms_output.put_line(emp_record.name);

end;

3.游标

当在PL/SQL块中执行查询语句SELECT和数据操纵语句DML时,

ORACLE会为其分配上下文区(CONTEXT AREA),游标是指向上下文区的指针。

对于数据操纵语句和单行SELECT INTO语句来说,ORACLE会为他们分配隐含游标。

使用显示游标处理多行数据。

3.1 显示游标操作

3.1.1 定义游标

语法：cursor cursor_name is 子查询;

例1：

declare

cursor cursor_emp is select * from emp;

begin

dbms_output.put_line('定义了一个游标');

end;

3.1.2 打开游标:

执行对应的SELECT语句并将SELECT语句的结果暂时存放到结果集中.

语法：open cursor_name;

例2：

declare

cursor cursor_emp is select * from emp;

begin

open cursor_emp;

dbms_output.put_line('打开了一个游标');

end;

3.1.3 提取数据

打开游标后,SELECT语句的结果被临时存放到游标结果集中,

使用FETCH语句只能提取一行数据

语法：fetch cursor_name into variable1,varibale2,...;

例3：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

v_counter number:=1;

begin

open cursor_emp;

loop

fetch cursor_emp into v_emp;

dbms_output.put_line('提取一行数据'||v_emp.ename);

v_counter:=v_counter+1;

if v_counter>20 then

exit;

end if;

end loop;

end;

3.1.4 关闭游标

语法：close cursor_name;

例4：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

v_counter number:=1;

begin

open cursor_emp;

loop

fetch cursor_emp into v_emp;

dbms_output.put_line('提取一行数据'||v_emp.ename);

v_counter:=v_counter+1;

if v_counter>20 then

exit;

end if;

end loop;

close cursor_emp;

end;

3.2 显示游标属性

用于返回显示游标的执行信息,包括%isopen,%found,%notfound,%rowcount

3.2.1 %isopen:确定游标是否打开

语法：

if cl%isopen then

...

else

open c1;

end if;

例5：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

v_counter number:=1;

begin

if cursor_emp%isopen then

null;

else

open cursor_emp;

end if;

loop

fetch cursor_emp into v_emp;

dbms_output.put_line('提取一行数据'||v_emp.ename);

v_counter:=v_counter+1;

if v_counter>20 then

exit;

end if;

end loop;

close cursor_emp;

end;

3.2.2 %found:检查是否从结果集中提取到了数据

语法：

loop

fetch c1 into var1,var2;

if c1%found then ... else exit;

end loop;

例6：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

begin

open cursor_emp;

loop

fetch cursor_emp into v_emp;

if cursor_emp%found then

dbms_output.put_line('提取一行数据'||v_emp.ename);

else

exit;

end if;

end loop;

close cursor_emp;

end;

3.2.3 %notfound 检查是否从结果集中提取不到数据

语法：

loop

fetch c1 into var1,var2;

exit when c1%notfound;

...

end loop;

例7：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

begin

open cursor_emp;

loop

fetch cursor_emp into v_emp;

exit when cursor_emp%notfound;

dbms_output.put_line('提取一行数据'||v_emp.ename);

end loop;

close cursor_emp;

end;

3.2.4 %rowcount:返回到当前行 为止已经提取到的实际行数

语法：

loop

v_1:=cursor_name%rowcount;

end loop;

例8：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

begin

if not cursor_emp%isopen then

open cursor_emp;

end if;

loop

fetch cursor_emp into v_emp;

exit when cursor_emp%notfound;

dbms_output.put_line('第'||cursor_emp%rowcount||'个雇员: '

||v_emp.ename);

end loop;

close cursor_emp;

end;

例9：

declare

cursor cursor_emp is select * from emp;

v_emp emp%rowtype;

begin

if not cursor_emp%isopen then

open cursor_emp;

end if;

loop

fetch cursor_emp into v_emp;

exit when cursor_emp%notfound;

dbms_output.put_line('提取一行数据'||v_emp.ename);

end loop;

dbms_output.put_line('游标总行数'||cursor_emp%rowcount);

close cursor_emp;

end;

3.3基于游标定义记录变量

例10：

declare

cursor emp_cursor is select ename,sal from emp;

emp_record emp_cursor%rowtype;

begin

open emp_cursor;

loop

fetch emp_cursor into emp_record;

exit when emp_cursor%notfound;

dbms_output.put_line('雇员名:'||emp_record.ename

||',雇员工资:'||emp_record.sal);

end loop;

close emp_cursor;

end;

3.4参数游标

定义参数游标时,游标参数只能指定数据类型,而不能指定长度.

语法：cursor cursor_name(parameter_name datatype) is select_statment;

例11：

declare

cursor emp_cursor(no number) is select ename from emp where deptno=no;

v_ename emp.ename%type;

begin

open emp_cursor(&no);

loop

fetch emp_cursor into v_ename;

exit when emp_cursor%notfound;

dbms_output.put_line(v_ename);

end loop;

close emp_cursor;

end;

例11-2：字符参数

declare

cursor emp_cursor(p_job varchar2) is select ename from emp where job=p_job;

v_ename emp.ename%type;

begin

open emp_cursor('&p_job');

loop

fetch emp_cursor into v_ename;

exit when emp_cursor%notfound;

dbms_output.put_line(v_ename);

end loop;

close emp_cursor;

end;

例11-3：两个参数

declare

cursor emp_cursor(p_no number,p_job varchar2) is

select ename from emp where deptno=p_no and job=p_job;

v_ename emp.ename%type;

begin

open emp_cursor(&p_no,'&p_job');

loop

fetch emp_cursor into v_ename;

exit when emp_cursor%notfound;

dbms_output.put_line(v_ename);

end loop;

close emp_cursor;

end;

--30 SALESMAN

begin

for rs in (select ename from emp where deptno=&no and job='&job') loop

dbms_output.put_line(rs.ename);

end loop;

end;

3.5 使用游标更新或删除数据

要通过游标更新或删除数据,在定义游标时必须要带有FOR UPDATE子句。

语法：

cursor cursor_name(parameter_name datetype) is

select_statement for update;

for update子句用于在游标结果集数据上加锁,防止其他用户在相应行执行DML操作。

使用游标更新或删除数据时，

可以在UPDATE后DELETE语句中引用WHERE CURRENT OF子句。

语法：

update table_name set 列名=值 where current of cursor_name;

delete table_name where current of cursor_name;

3.5.1 使用游标更新数据

例12：

declare

cursor emp_cursor is select ename,sal from emp for update;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_sal;

exit when emp_cursor%notfound;

if v_sal<2000 then

update emp set sal=sal+100 where current of emp_cursor;

dbms_output.put_line(v_ename||'工资增加100');

end if;

end loop;

close emp_cursor;

end;

例12-2：

declare

cursor emp_cursor is select ename,sal from emp;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_sal;

exit when emp_cursor%notfound;

if v_sal<2000 then

update emp set sal=sal+100 where ename=v_ename;

dbms_output.put_line(v_ename||'工资增加100');

end if;

end loop;

close emp_cursor;

end;

3.5.2 使用游标删除数据

例13：

declare

cursor emp_cursor is select ename,sal,deptno from emp for update;

v_ename emp.ename%type;

v_sal emp.sal%type;

v_deptno emp.deptno%type;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_sal,v_deptno;

exit when emp_cursor%notfound;

if v_deptno=30 then

delete from emp where current of emp_cursor;

dbms_output.put_line(v_ename||'被删除了');

end if;

end loop;

close emp_cursor;

end;

例13-2：

declare

cursor emp_cursor is select ename,sal,deptno from emp;

v_ename emp.ename%type;

v_sal emp.sal%type;

v_deptno emp.deptno%type;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_sal,v_deptno;

exit when emp_cursor%notfound;

if v_deptno=30 then

delete from emp where ename=v_ename;

dbms_output.put_line(v_ename||'被删除了');

end if;

end loop;

close emp_cursor;

end;

3.6 游标FOR循环

使用FOR循环时,ORACLE会隐含的打开游标,提取游标数据并关闭游标

语法：

for record_name in cursor_name loop

statement1;

statement2;

...

end loop;

每循环一次提取一次数据，在提取了所有数据后,自动退出循环并隐含的关闭游标

3.6.1 使用游标FOR循环

例14：

declare

cursor emp_cursor is select ename,sal from emp;

begin

for emp_record in emp_cursor loop

dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员: '

||emp_record.ename);

end loop;

end;

3.6.2 在游标FOR循环中直接使用子查询

例15：

declare

i number:=1;

begin

for rs in (select ename,sal from emp) loop

dbms_output.put_line('第'||i||'个雇员: '||rs.ename);

i:=i+1;

end loop;

end;

3.7 使用游标变量

PL/SQL的游标变量中存放着指向内存地址的指针.

3.7.1 游标变量使用步骤

包括定义游标变量,打开游标,提取游标数据,关闭游标等四个阶段

1)定义ref cursor类型和游标变量

type ref_type_name is ref cursor [return return_type];

cursor_varibale ref_type_name;

当指定RETURN子句时,其数据类型必须是记录类型,不能在包内定义游标变量

2)打开游标

open cursor_variable for select_statement;

3)提取游标数据

fetch cursor_varibale into variable1,variable2,...;

4)关闭游标变量

close cursor_varibale;

3.7.2 游标变量使用示例

1)在定义REF CURSOR类型时不指定RETURN子句

在打开游标时可以指定任何的SELECT语句

例16：

declare

type emp_cursor_type is ref cursor;

emp_cursor emp_cursor_type;

emp_record emp%rowtype;

begin

open emp_cursor for select * from emp where deptno=10;

loop

fetch emp_cursor into emp_record;

exit when emp_cursor%notfound;

dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员: '||emp_record.ename);

end loop;

close emp_cursor;

end;

2)在定义REF CURSOR类型时指定RETURN子句

在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型相匹配.

例17：

declare

type emp_record_type is record(ename varchar2(10),salary number(6,2));

type emp_cursor_type is ref cursor return emp_record_type;

emp_cursor emp_cursor_type;

emp_record emp_record_type;

begin

open emp_cursor for select ename,sal from emp where deptno=20;

loop

fetch emp_cursor into emp_record;

exit when emp_cursor%notfound;

dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员: '

||emp_record.ename);

end loop;

close emp_cursor;

end;

3.8 使用CURSOR表达式

CURSOR表达式用于返回嵌套游标

结果集不仅可以包含普通数据,而且允许包含嵌套游标的数据

语法：cursor(subquery)

例18：

declare

type refcursor is ref cursor;

cursor dept_cursor(no number) is

select a.dname,

cursor(select ename,sal from emp where deptno=a.deptno)

from dept a where a.deptno=no;

empcur refcursor;

v_dname dept.dname%type;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

open dept_cursor(&no);

loop

fetch dept_cursor into v_dname,empcur;

exit when dept_cursor%notfound;

dbms_output.put_line('部门名:'||v_dname);

loop

fetch empcur into v_ename,v_sal;

exit when empcur%notfound;

dbms_output.put_line('雇员名:'||v_ename||',工资:'||v_sal);

end loop;

end loop;

close dept_cursor;

end;

例19：

begin

for rs in (select dname,deptno from dept where deptno=&no) loop

dbms_output.put_line('部门名:'||rs.dname);

for rs2 in (select ename,sal from emp where deptno=rs.deptno) loop

dbms_output.put_line('雇员名:'||rs2.ename||',工资:'||rs2.sal);

end loop;

end loop;

end;