Oracle PL/SQL 高级编程

首页 > 代码库 > Oracle PL/SQL 高级编程

2024-07-04 21:15:21 221人阅读

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类似与c语言中的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;

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Oracle PL/SQL 高级编程

Oracle PL/SQL 高级编程

看完仍有疑问？有类似问题直接问程序猿