oracle作业(答案).doc

方法一1、从表1获取数据，格式化输出为表2。 drop user ben cascade;create user ben identified by 12345;grant dba to ben;conn ben/12345;show user ;create table tableone( studname varchar2(16), coursename varchar2(16), cscore number(2) check(cscore =0 and cscore =0 and cscore =100), uscore number check(uscore in (1,3,5);insert into tableone values(李白,摔跤,80,1);insert into tableone values(李白,军事理论,60,5);insert into tableone values(李白,数学,70,3);insert into tableone values(杜甫,摔跤,95,1);insert into tableone values(杜甫,军事理论,85,5);insert into tableone values(杜甫,数学,75,3);insert into tableone values(李清照,摔跤,45,1);insert into tableone values(李清照,军事理论,55,5);insert into tableone values(李清照,数学,50,3);insert into tableone values(蔡琰,摔跤,25,1);insert into tableone values(蔡琰,军事理论,15,5);insert into tableone values(蔡琰,数学,10,3);create or replace view shituas select studname,coursename,cscore,uscorefrom tableone;set pagesize 15;column studname format a18;column coursename format a18;select * from tableone;Set serveroutput on;declare cursor tabletwo is select * from shitu; i number(2) :=1; zf number(2) ; type STUDDATE IS TABLE OF shitu%ROWTYPE; st STUDDATE; begin dbms_output.put_line(姓名 摔跤 军事理论 数学 总学分); st := STUDDATE(); open tabletwo; FETCH tabletwo BULK COLLECT INTO st; while i 11 loop zf:=st(i).uscore+st(i+1).uscore+st(i+2).uscore; dbms_output.put_line(st(i).studName | | st(i).cscore | |st(i+1).cscore | |st(i+2).cscore | | zf); i:=i+3; end loop; close tabletwo; end;/2、从表2读取数据，格式化输出为表1。drop table tableone;create table tabletwo( 姓名 varchar2(16), 摔跤 number(2), 军事理论 number(2), 数学 number(2), 总学分 number);insert into tabletwo values(李白,80,60,70,9);insert into tabletwo values(杜甫,95,85,75,9);insert into tabletwo values(李清照,45,55,50,9);insert into tabletwo values(蔡琰,25,15,10,9);create or replace view shitu1as select 姓名,摔跤,军事理论,数学from tabletwo;column studname format a18;column coursename format a18;select * from tabletwo;Set serveroutput on;declare cursor tableone is select * from shitu1; i number(2) :=1; type st1UDDATE IS TABLE OF shitu1%ROWTYPE; st1 st1UDDATE; begin dbms_output.put_line(st1udname coursename cscore uscore); st1 := st1UDDATE(); open tableone; FETCH tableone BULK COLLECT INTO st1; while i 5 loop dbms_output.put_line(st1(i).姓名 | |摔跤| |st1(i).摔跤| |1); dbms_output.put_line(st1(i).姓名 | |军