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_IDincrement by 1start with 10000;-为学员信息表创建触发器TG_STUIDcreate or replace trigger TG_STUIDbefore insert on stuInfo for each rowbegin 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,上海,liuxiangG);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(曹操,s3t0915,34,魏国,caocaoTOM.com);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_STUis cursor getStuInfo return stuInfo;end PKG_STU;-创建包体create or replace package body PKG_STUas 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_STUis cursor getStuInfo(studentNo varchar2) return stuInfo;end PKG_STU;-创建包体create or replace package body PKG_STUis 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