OraclePL/SQL高级编程
1、 复合数据类型——记录类型
? 语法格式
type 类型名 is record (
字段1 字段1类型 [not null]:=表达式1;
字段2 字段2类型 [not null] :=表达式2;
… )
? 说明:记录之间相互赋值要求两个记录类型完全相同。
? 案例
举例1
--创建表并插入记录
create table student(idnumber, name varchar2(20), birthday date);
insert into studentvalues(100, 'xiaoming', to_date('2000.12.17', 'yyyy.mm.dd'));
insert into studentvalues(200, 'xiaohua', to_date('2001.12.17', 'yyyy.mm.dd'));
insert into studentvalues(300, 'xiaoli', to_date('2002.12.17', 'yyyy.mm.dd'));
--定义记录类型
declare
type t_studentrecord isrecord(
id number,
name varchar2(20),
birthday date);
或者:
type t_studentrecord isrecord(
idstudent.id%type,
name student.name%type,
birthday student birthday%type);
v_students t_studentrecord;
begin
select * into v_students from students whereid=200;
dbms_output.put_line(v_students.id||' '||v_students.name||' '||to_char(v_students.birthday,'yyyy-mm-dd'));
end;
举例2
declare
type t_studentrecord is record(
id number(4),
name varchar2(20),
birthday date);
v_students t_studentrecord;
v_students_copy t_studentrecord;
begin
v_students.id:=400;
v_students.name:='赵伟';
v_students.birthday:= to_date('2003.12.17','yyyy.mm.dd');
v_students_copy:=v_students;
dbms_output.put_line(v_students_copy.id||''||v_students_copy.name||' '|| to_char(v_students_copy. birthday, 'yyyy-mm-dd'));
end;
举例3
declare
type t_studentrecord is record(
id number(4),
name varchar2(20),
birthday date);
type t_studentrecord2 is record(
id number(4),
namevarchar2(20),
birthday date);
v_students t_studentrecord;
v_students_copy t_studentrecord2;
begin
v_students.id:=400;
v_students.name:='赵伟';
v_students.birthday:= to_date('2003.12.17','yyyy.mm.dd');;
v_students_copy:=v_students;
dbms_output.put_line(v_students_copy.id||''||v_students_copy.name||' '|| to_char(v_students_copy. birthday, 'yyyy-mm-dd'));
end;
出错说明:如果两个记录类型类型名不同,但是内容完全相同,两个类型对应的两个变量不能互相赋值。
2、 集合数据类型——index-by表
? 介绍:类似于普通程序设计语言中的数组概念。
? 声明index-by表的方法:
type 类型名 is table of typeindex by binary_integer;
说明:其中type定义的是index-by表中各元素的类型,可以是内置类型、用户定义的对象类型或者使用%rowtype的表达式等。
? index-by表中单个元素
在声明了类型和变量后,可以通过:变量名(index)使用表中的单个元素,其中index是指表中的第几个元素。
? index by表的属性函数
属性名称 |
数据类型 |
说 明 |
count |
number |
返回表中的行数 |
delete |
无 |
用于从表中删除指定(由传入参数指定)的一行数据 |
exists |
boolean |
如果指定的行存在则返回true,否则返回false |
first |
binary_integer |
返回表中第一行的下标 |
last |
binary_integer |
返回表中最后一行的下标 |
next |
binary_integer |
返回指定行(由传入参数指定)的下一行的下标 |
prior |
binary_integer |
在指定行(由传入参数指定)的上一行的下标 |
? 案例
举例1
declare
type t_studentrecord isrecord(
idstudent.id%type,
name student.name%type,
birthdaystudent birthday%type);
type t_studenttable is table oft_studentrecord index by binary_integer;
v_students t_studenttable;
begin:
select * into v_students(100) from student whereid=100;
dbms_output.put_line(v_students(100).id||''||v_students(100).name||' '|| to_char(v_students(100). birthday, 'yyyy-mm-dd'));
end;
举例2
declare
type t_studenttable is table of student%rowtypeindex by binary_integer;
v_students t_studenttable;
begin
select * into v_students(1) from student whereid=200;
dbms_output.put_line(v_students(1).id ||''||v_students(1).name||'
'|| to_char(v_students(1). birthday,'yyyy-mm-dd'));
end;
举例3
declare
type t_s is table of scott.emp%rowtype indexby binary_integer;
v_s t_s;
v_index binary_integer;--索引号
v_loop binary_integer;--循环次数
begin
select * into v_s(10) from scott.emp whereempno='7788';
select * into v_s(22) fromscott.emp where empno='7902';
select * into v_s(-12) fromscott.emp where empno='7934';
v_index:= v_s.first;
v_loop:=v_s.count;
loop
dbms_output.put_line(v_s(v_index).empno);
v_index:=v_s.next(v_index);
v_loop:=v_loop-1;
exit when v_loop<=0;
end loop;
v_index:=v_s.last;
v_s.delete(v_index);
v_index:= v_s.first;
v_loop:=v_s.count;
loop
dbms_output.put_line(v_s(v_index).empno);
v_index:=v_s.next(v_index);
v_loop:=v_loop-1;
exit when v_loop<=0;
end loop;
end;
3、 集合数据类型——变长数组
? 介绍:变长数组:是一个存储有序元素的集合,每个元素都有一个索引,该索引相对应元素在数组中的位置。变长数组存在大小的限制,但是可以动态进行更改。
? 创建变长数组语句:
? 案例
--创建一个变长数组
create type varray_phone as varray(3) of varchar2(50);
--创建一个人员表,表中人员拥有一列电话(可能有1、2或3个电话号码)。
create table person3
(
id integer constraintperson3_pk primary key,
first_name varchar(20),
last_name varchar(20),
phone varray_phone
)
--填充变长数组
insert into person3values(1,'yuan','weixiang',varray_phone('12345','34567','56789'));
select * from person3;
insert intoperson3 values(2,'hao','lihai',varray_phone());
select * from person3;
--修改变长数组中的元素
update person3 setphone= varray_phone('12345','34567') where id = 2;
select * from person3;
--修改变长数组的元素的长度大小
alter type varray_phone modifyelement type varchar2(49) cascade --报错
alter type varray_phone modify element type varchar2(60)cascade
说明:cascade选项把更改传播到中的依赖对象 person3就是依赖对象
--修改变长数组元素的数目
alter type varray_phonemodify limit 2 cascade -- 报错,varray限制只能增大
alter type varray_phonemodify limit 5 cascade
insert into person3 values(3,'yuan','weixiang',varray_phone('12345','34567','56789','34567','56789'));
4、 游标
? 介绍:在pl/sql程序设计中,有时需要对查询返回结果集进行逐行处理,这就需要将该查询返回结果集缓存到一个内存区中,为了能对返回的结果集进行逐行操作,需返回该内存区的首地址,这个地址被称为游标。
? 定义游标语法
cursor 游标名 is select语句;
注意:在游标定义中的select语句不能包含into子句。
? 游标属性
游标属性 |
描 述 |
游标名%isopen |
布尔值,如果游标已打开,取值为true,否则为false。 |
游标名%notfound |
布尔值,如果最近一次fetch操作没有返回结果,则取值为true,否则为false。 |
游标名%found |
布尔值,如果最近一次fetch操作没有返回结果,则取值为false,否则为true。 |
游标名%rowcount |
数字型值,值为到当前为止返回的行数。 |
? 案例
举例1
declare
v_no scott.emp.empno%type;
v_name scott.emp.ename%type;
cursorc_e is select empno, ename from scott.emp;
begin
open c_e;
loop
fetch c_e into v_no, v_name;
exit when c_e%notfound;
dbms_output.put_lint(v_no||' '||v_name);
end loop;
close c_e;
end;
举例2
declare
cursor c_e is select empno, ename from scott.emp;
begin
for c1 in c_e loop
dbms_output.put_lint(c1.empno||' '||c1.ename);
end loop;
end;
5、 可更新的游标
? 介绍:从游标中抽取数据,可以对数据库中的数据进行update和delete操作。
? 语法:在定义游标时,必须加for update of子句;在update和delete语句中加上where current of子句。
? 案例
举例1:对scott方案emp表中某部门的各员工,如果其工资小于1600元,则将其工资设为1600元。
accept p_deptno prompt‘please enter the deptno’;
--accept类似与中的scanf,意为从屏幕接受输入到p_deptno变量中。
declare
v_deptno scott.emp.deptno%type:=&p_deptno;
cursor emp_cursor is select empno,job,sal from scott.emp wheredeptno=v_deptno for update of sal;
begin
for emp_record in emp_cursor loop
if emp_record.sal<1600 then
update emp set sal=1600 where currentof emp_cursor;
end if;
end loop;
end;
举例2:为职工涨工资,对员工按工资从低到高排序,从工资低的员工开始涨,每位员工涨10%。但要控制员工总工资在50万之内,一旦总额超过50万,就停止对剩余的员工涨工资。
6、 带参数的游标
? 介绍:定义显示游标时,可以加入参数的定义。在使用游标时,对于参数输入不同的数值,则游标缓存中的数据也随之变化。
? 定义游标语法:
cursor 游标名(参数1 数据类型, ….) is select 子句;
? 打开游标语法:
open游标名(&参数1, ….) ;
? 案例:从scott方案的emp表中查询并打印某个部门的雇员情况,其中部门号由用户交互式输入。
accept v_deptnoprompt‘please enter the deptno;
declare
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
cursor emp_cursor (v_deptno number) is selectename,sal from scott.emp where deptno=v_deptno;
begin
open emp_cursor(&p_deptno);
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename|| ' '||v_sal);
end loop;
close emp_cursor;
end;
上一篇: AI怎么设计旋转花瓣样式的图标?