1、1-创建学员信息表create table stuInfo(stuId varchar2(15) not null, -学员 Id,主键stuName varchar2(10) not null, -学员姓名stuNo varchar2(10) not null, -学号,外键应用 stuMarks 的 stuNostuAge int not null, -年龄stuAddress varchar2(100) default(中国) not null,-家庭住址stuEmail varchar2(100) not null -电子邮箱);alter table stuInfo add cons
2、traint 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
3、 insert on stuInfo for each rowbeginselect SID|SQ_ID.Nextval into :new.stuId from dual;end;-向学员信息表中添加数据insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(张飞,s1t0102,30,三国,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(关羽,s1t0830,35,蜀国,);insert into stuInfo(s
4、tuName,stuNo,stuAge,stuAddress,stuEmail) values(马超,s2t1326,25,三国,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(刘备,s3t0403,40,蜀国,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(诸葛亮,s2t1521,21,蜀国,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuE
5、mail) 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,东吴,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values(董
6、卓,s2t0507,35,三国,);insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values2(朱军,s2t1127,39,北京,);*-在包中使用游标(无参游标)-定义包头create or replace package PKG_STUiscursor getStuInfo return stuInfo;end PKG_STU;-创建包体create or replace package body PKG_STUascursor getStuInfo return stuInfo isselect * from
7、 stuInfo;end PKG_STU;-调用包beginfor stu_Record in PKG_STU.getStuInfo loopdbms_output.put_line(学员姓名:|stu_Record.stuName|学号:|stu_Record.stuNo|年龄:|stu_Record.stuAge);end loop;end;运行结果如下:*-在包中使用有参数的游标-定义包头create or replace package PKG_STUiscursor getStuInfo(studentNo varchar2) return stuInfo;end PKG_STU;-
8、创建包体create or replace package body PKG_STUiscursor getStuInfo(studentNo varchar2) return stuInfois select * from stuInfo where stuNo = studentNo;end;-调用包测试beginfor stuRecord in PKG_STU.getStuInfo(s2t1521) loopdbms_output.put_line(学员姓名:|stuRecord.stuName|学号:|stuRecord.stuNo|年龄:|stuRecord.stuAge);3end loop;end;运行结果如下:
