结一下单行多列,单例多行和多行多列的写法:
1.单行多列:
- declare
- type xxx_type is record(
- my_sal emp.sal%type,
- my_num emp.empno%type
- );
-
- xxx xxx_type;
- begin
- select sal,empno into xxx from emp where emp.empno = 7369;
- dbms_output.put_line(xxx.my_sal);
- dbms_output.put_line(xxx.my_num);
-
- end;
- /
declare
type xxx_type is record(
my_sal emp.sal%type,--锚定数据类型
my_num emp.empno%type
);
--声明一个记录的数据类型(记录类型是单行多列)
xxx xxx_type;
begin
select sal,empno into xxx from emp where emp.empno = 7369;--执行sql语句是赋值用into
dbms_output.put_line(xxx.my_sal);--输出一行(需要先在sql下 set serveroutput on;)
dbms_output.put_line(xxx.my_num);
--这种方式大多用于自定义
end;
/
还有一种是用
表名+%rowtype来实现:
- declare
- xxx emp<SPAN style="COLOR: #ff0000">%rowtype</SPAN>;
- begin
- select * into xxx from emp where emp.empno = 7369;
- dbms_output.put_line(xxx.sal);
- dbms_output.put_line(xxx.empno);
- dbms_output.put_line(xxx.ENAME);
- dbms_output.put_line(xxx.job);
- end;
- /
declare
xxx emp%rowtype;
begin
select * into xxx from emp where emp.empno = 7369;
dbms_output.put_line(xxx.sal);
dbms_output.put_line(xxx.empno);
dbms_output.put_line(xxx.ENAME);
dbms_output.put_line(xxx.job);
end;--使用的时候成员个数,名称,类型必须和表货视图的列的个数,名称,类型完全相同/
2:单列多行
- declare
- type xxx_type is table of emp.sal%type
- index by binary_integer;
- xxx xxx_type;
- i number;
- begin
- select sal <SPAN style="COLOR: #ff0000">bulk collect</SPAN> into xxx from emp where emp.deptno = 10;
- select count(sal) into i from emp where emp.deptno = 10;
- for j in 1..i loop
- dbms_output.put_line(xxx(j));
- end loop;
- end;
- /
declare
type xxx_type is table of emp.sal%type
index by binary_integer;--声明一个单列多行的结构
xxx xxx_type;
i number;
begin
select salbulk collectinto xxx from emp where emp.deptno = 10;--给xxx批量赋值
select count(sal) into i from emp where emp.deptno = 10;
for j in 1..i loop
dbms_output.put_line(xxx(j));
end loop;
end;
/
3:表结构(多行多列):
- declare
- type xxx_type is table of emp%rowtype
- index by binary_integer;--声明一个多列多行的结构
- xxx xxx_type;
- i number;
- begin
- select * bulk collect into xxx from emp;--给xxx批量赋值
- select count(*) into i from emp ;
- for j in 1..i loop
- dbms_output.put_line(xxx(j).sal);
- dbms_output.put_line(xxx(j).ename);
- dbms_output.put_line(xxx(j).job);
- dbms_output.put_line(xxx(j).mgr);
- end loop;
- end;
- /
declare
type xxx_type is table of emp%rowtype
index by binary_integer;--声明一个多列多行的结构
xxx xxx_type;
i number;
begin
select * bulk collect into xxx from emp;--给xxx批量赋值
select count(*) into i from emp ;
for j in 1..i loop
dbms_output.put_line(xxx(j).sal);
dbms_output.put_line(xxx(j).ename);
dbms_output.put_line(xxx(j).job);
dbms_output.put_line(xxx(j).mgr);
end loop;
end;
/
异常的
学习:
题目如下 :请删除dept表中的deptno为10的这项并且emp表中的其他数据部受影响
- declare
- e exception;
- pragma exception_init(e,-02292);
- --自己定义非预处理异常并绑定异常
- begin
- delete dept where deptno = 10;
- --出现异常
- exception
- when e then
- --接收到异常
- --dbms_output.put_line('不能删除');
- update emp set deptno = null where deptno = 10;
- --将emp中的外键置为空
- delete dept where deptno = 10;
- --删除dept中的编号
- end;
- /