Oracle数据库游标在包中的使用.doc

--创建学员信息表
create table stuInfo
(
   stuId varchar2(15) not null,      --学员Id,主键
   stuName varchar2(10) not null,    --学员姓名
   stuNo varchar2(10) not null,      --学号,外键应用stuMarks的stuNo
   stuAge int not null,              --年龄
   stuAddress varchar2(100) default('中国') not null,--家庭住址
   stuEmail varchar2(100) not null   --电子邮箱
);
alter table stuInfo add constraint PK_stuId primary key(stuId);
alter table stuInfo add constraint CK_stuAge check(stuAge between 18 and 40);
alter table stuInfo add constraint CK_stuEmail check(stuEmail like '%@%');
--创建序列
create sequence SQ_ID
increment by 1
start with 10000;
--为学员信息表创建触发器TG_STUID
create or replace trigger TG_STUID
before insert on stuInfo for each row
begin
    select 'SID'||SQ_ID.Nextval into :new.stuId from dual;
end;
--向学员信息表中添加数据
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('张飞','s1t0102',30,'三国','zhangfei@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('关羽','s1t0830',35,'蜀国','guanyu@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('马超','s2t1326',25,'三国','machao@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('刘备','s3t0403',40,'蜀国','liubei@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('诸葛亮','s2t1521',21,'蜀国','zhugeliang@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('刘翔','s3t0706',29,'上海','liuxiang@G');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('曹操','s3t0915',34,'魏国','[e-mail]');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('孙权','s1t1123',32,'东吴','sunquan@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('董卓','s2t0507',35,'三国','dongzhuo@');
insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values
('朱军','s2t1127',39,'北京','zhujun@');
************************************
--在包中使用游标(无参游标)
--定义包头
create or replace package PKG_STU
is
      cursor getStuInfo return stuInfo;
end PKG_STU;
--创建包体
create or replace package body PKG_STU
as
    cursor getStuInfo return stuInfo is
    select * from stuInfo;
end PKG_STU;
--调用包
begin
     for stu_Record in PKG_STU.getStuInfo loop
     dbms_output.put_line('学员姓名:'||stu_Record.stuName||'学号:'||
            stu_Record.stuNo||'年龄:'||stu_Record.stuAge);
     end loop;
end;
运行结果如下:

*************************************************

--在包中使用有参数的游标
--定义包头
create or replace package PKG_STU
is
      cursor getStuInfo(studentNo varchar2) return stuInfo;
end PKG_STU;
--创建包体
create or replace package body PKG_STU
is
     cursor getStuInfo(studentNo varchar2) return stuInfo
     is select * from stuInfo where stuNo = studentNo;
end;
--调用包测试
begin
    for stuRecord in PKG_STU.getStuInfo('s2t1521') loop
         dbms_output.put_line('学员姓名:'||stuRecord.stuName||
         '学号:'||stuRecord.stuNo||'年龄:'||stuRecord.stuAge);
    end loop;
end;
运行结果如下:

3