课程十三 使用组合数据类型* 游标操纵数据
本课重点:
1、创建用户自定义的PLSQL记录
2、利用%ROWTYPE属性来创建记录
3、创建PLSQL表
4、描述记录、表、记录的表之间的区别
注意:以下实例中标点均为英文半角
一、合成数据类型
1、类型分为PLSQL记录和PLSQL表
2、包含内部组件
3、可重用
二、PLSQL记录
与3GL中的记录结构相似
与数据库表是两回事
是一个方便的途径FETCH一些行FROM一个表来进行相关处理。
标准语法格式我们暂不介绍,因为每本书上均有。
看例子:
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
ttt vtotal%type;
type emp_record_type is record
(empno number not null:=100,
ename emp.ename%type,
job emp.job%type);
emp_record emp_record_type;
begin
--select sysdate into vtotal from dual;--体会有无此句与结果的影响
dbms_output.put_line (vtotal);
end;
/
主要看TYPE RECORD出现的位置。每一个例子都是可以成功执行的。
我们也可以利用原有的表结构:
DECLARE
EMP_RECORD EMP%ROWTYPE;
游标操纵数据
PLSQL游标提供了一种从数据库提取多行数据,然后对每行数据进行单独处理的方法。
一、两种游标:
显式游标
隐式游标
二、显式游标:操纵步骤如下:声明游标、打开游标、从游标中取回数据、关闭游标
三、声明游标:
DECLARE CURSOR_NAME
IS
SELECT STATMENT
能够控制游标的,唯一参数是INIT.ORA中的OPEN_CURSORS,我原来以为是客户端最多可以打开多少个游标,但有
本书上讲这是用于管理游标的内存的数量。
DECLARE
CURSOR C_NAME
IS
SELECT ENAME FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
WHERE CITY_ID=‘BJ’)
--- 说明游标可以用子查询
四、打开游标
OPEN CURSOR_NAME;
这时游标将它的指针指向活动集的开始,指针指向第一条记录的前面是因为它还没有执行FETCH命令。如果试图打
开一个已经打开的游标,将出错:
ORA-06511:PL/SQL:CURSOR ALREADY OPEN
我们可以这样:
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME;
END IF;
五、从游标中取回数据
FETCH CURSOR_NAME INTO RECOR-LIST;
关闭游标:CLOSE CURSOR_NAME
六、实例:
DECLARE
myname varchar2(22);
CURSOR C_NAME
IS
SELECT ENAME FROM EMP;
begin
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname;
dbms_output.put_line (myname);
exit when c_name%notfound;
end loop;
close c_name;
end;
/--我们将对以上程序进行变形,形成复杂的光标利用。
DECLARE
myname varchar2(22);
thisdeptno scott.emp.deptno%type;
CURSOR C_NAME
IS
SELECT ENAME,deptno FROM EMP order by deptno desc;
begin
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname,thisdeptno;
dbms_output.put_line (myname||','||thisdeptno || ',' || to_char(c_name%rowcount));
exit when c_name%notfound;
end loop;
dbms_output.put_line ('the Total record is fetched is ' || to_char(c_name%rowcount));
close c_name;
end;
/
我们增加变量,进行用了排序,使用了光标属性,大家看结果发生的变化,想想为什么。
实例精华!!!: DECLARE
myname varchar2(22);
ii number;
thisdeptno scott.emp.deptno%type;
CURSOR C_NAME
IS
SELECT * FROM EMP order by deptno desc;
emp_record c_name%rowtype;
begin
ii:=1;
for emp_record in c_name loop
dbms_output.put_line(ii);
ii:=ii+1;
end loop;
end;
/
--这里使用了游标FOR循环,在FOR循环的开始,进行、和END LOOP,分别隐式进行了游标的打开、FETCH和CLOSE
。
我们甚至可以不声明游标:FOR emp_record in (SELECT * FROM DEPT) loop
这种技术被称为显式游标的自动化。
在上面,我们可以将一个表的所有字段输出,如我们将PUT_LINE的II改为emp_record.ename,就可以输出一个字段
内容。
这种方式非常简单而且效率较高。
----------------
为了测试光标属性的重要性,我们做一个以下的过程:
create or replace PROCEDURE change_salary
(v_emp_id IN NUMBER, -- formal parameters
v_new_salary IN NUMBER)
IS
BEGIN -- begin PL/SQL block
UPDATE emp
SET sal = v_new_salary
WHERE empno = v_emp_id;
COMMIT;
END change_salary;
/
这样,我们在匿名块中,
UPDATE DEPT
SET DNAME='MY DEPT' WHERE ....;
IF SQL%FOUND THEN
COMMIT;
ELSE
change_salary(7369,9000);
END IF;
我们看到我们通过流程控制了不同的执行结果,对于过程,我们可以用以下几种方法调用:
在SQLPLUS中:CALL change_salary(7369,9000);
EXECUTE change_salary(7369,9000);
在一个块中,如:
begin
change_salary(7369,9000);
end;
/