--创建学员信息表createtablestuInfo(stuIdvarchar2(15)notnull,--学员Id,主键stuNamevarchar2(10)notnull,--学员姓名stuNovarchar2(10)notnull,--学号,外键应用stuMarks的stuNostuAgeintnotnull,--年龄stuAddressvarchar2(100)default('中国')notnull,--家庭住址stuEmailvarchar2(100)notnull--电子邮箱);altertablestuInfoaddconstraintPK_stuIdprimarykey(stuId);altertablestuInfoaddconstraintCK_stuAgecheck(stuAgebetween18and40);altertablestuInfoaddconstraintCK_stuEmailcheck(stuEmaillike'%@%');--创建序列createsequenceSQ_IDincrementby1startwith10000;--为学员信息表创建触发器TG_STUIDcreateorreplacetriggerTG_STUIDbeforeinsertonstuInfoforeachrowbeginselect'SID'||SQ_ID.Nextvalinto:new.stuIdfromdual;end;--向学员信息表中添加数据insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('张飞','s1t0102',30,'三国','zhangfei@sina.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('关羽','s1t0830',35,'蜀国','guanyu@google.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('马超','s2t1326',25,'三国','machao@sohu.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('刘备','s3t0403',40,'蜀国','liubei@163.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('诸葛亮','s2t1521',21,'蜀国','zhugeliang@yahoo.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('刘翔','s3t0706',29,'上海','liuxiang@Gmail.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('曹操','s3t0915',34,'魏国','caocao@TOM.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('孙权','s1t1123',32,'东吴','sunquan@126.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('董卓','s2t0507',35,'三国','dongzhuo@cctv.com');insertintostuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail)values('朱军','s2t1127',39,'北京','zhujun@cctv.com');************************************--在包中使用游标(无参游标)1--定义包头createorreplacepackagePKG_STUiscursorgetStuInforeturnstuInfo;endPKG_STU;--创建包体createorreplacepackagebodyPKG_STUascursorgetStuInforeturnstuInfoisselect*fromstuInfo;endPKG_STU;--调用包beginforstu_RecordinPKG_STU.getStuInfoloopdbms_output.put_line('学员姓名:'||stu_Record.stuName||'学号:'||stu_Record.stuNo||'年龄:'||stu_Record.stuAge);endloop;end;运行结果如下:*************************************************--在包中使用有参数的游标--定义包头createorreplacepackagePKG_STUiscursorgetStuInfo(studentNovarchar2)returnstuInfo;endPKG_STU;--创建包体createorreplacepackagebodyPKG_STUiscursorgetStuInfo(studentNovarchar2)returnstuInfoisselect*fromstuInfowherestuNo=studentNo;end;--调用包测试beginforstuRecordinPKG_STU.getStuInfo('s2t1521')loopdbms_output.put_line('学员姓名:'||stuRecord.stuName||'学号:'||stuRecord.stuNo||'年龄:'||stuRecord.stuAge);endloop;end;运行结果如下:23
