PL SQL編程 Oracle數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告

1、PL/SQL 編程【實(shí)驗(yàn)?zāi)康摹縋L/SQL 的數(shù)據(jù)類型和書寫規(guī)則熟悉控制結(jié)構(gòu)和游標(biāo)的使用編寫和運(yùn)行函數(shù)、過程和觸發(fā)器【實(shí)驗(yàn)內(nèi)容】編寫腳本文件，調(diào)試運(yùn)行腳本文件，并記錄結(jié)果。SQL*Plus PL/SQL 塊，功能用于打印學(xué)生信息DECLARE部分完成：(1)中定義的記錄類型作為參數(shù)stu_recordBEGINEND 部分完成：stu_record變量的各個(gè)元素賦值如下： 姓名：李新對該變量的調(diào)用打印過程，輸出到屏幕bookinfoDML bookinfo表發(fā)生了任何bookinfo數(shù)據(jù)統(tǒng)計(jì)表中如果沒有則建立bookinfo表，選擇建立在scott 用戶下，表結(jié)構(gòu)為(booknovarchar

2、2(36) Primarykey,booknamevarchar2(40) not null, authornamevarchar2(10) not null, publishtimedate,bookpricefloat)建立數(shù)據(jù)統(tǒng)計(jì)表 bookinfo 表中插入、bookinfo 表進(jìn)行統(tǒng)計(jì)，結(jié)果存儲在major_stats表中bookinfomajor_stats表中數(shù)據(jù)的變化【實(shí)驗(yàn)結(jié)論】編寫腳本文件，調(diào)試運(yùn)行腳本文件，并記錄結(jié)果。SQL*Plus PL/SQL 塊，功能用于打印學(xué)生信息DECLARE部分完成：set serveroutput ondeclaretype stu_reco

3、rd_type isrecord (s_no varchar2 (8),s_name varchar2 (10),s_sex varchar2 (2),s_address varchar2 (20),s_studyscore varchar2 (10), s_activescore varchar2(10);，把(1)中定義的記錄類型作為參數(shù)procedure PrintStuRecord asbegindbms_output.put_line( 學(xué) 號 :| stu_record.s_no); dbms_output.put_line( 姓 名 :| stu_record.s_name);

4、dbms_output.put_line( 性 別 :| stu_record.s_sex); dbms_output.put_line( 籍 貫 :| stu_record.s_address); dbms_output.put_line(學(xué)習(xí)成績:| stu_record.s_studyscore); dbms_output.put_line(活動成績:| stu_record.s_activescore); end PrintStuRecord;stu_record stu_recordstu_record_type;BEGINEND 部分完成：stu_record變量的各個(gè)元素賦值如下

5、： 姓名：李新beginstu_record.s_no:=2001001; stu_record.s_name:= 李 新 ; stu_record.s_sex:=m; stu_record.s_address:=黑龍江省哈爾濱市; stu_record.s_studyscore:=Excellent; stu_record.s_activescore:=Good; PrintStuRecord;end;/對該變量的調(diào)用打印過程，輸出到屏幕bookinfoDML bookinfo表發(fā)生了任何bookinfo 據(jù)統(tǒng)計(jì)表中如果沒有則建立bookinfo 表，選擇建立在scott用戶下，表結(jié)構(gòu)為(b

6、ooknovarchar2(36) Primarykey,booknamevarchar2(40) not null, authornamevarchar2(10) not null, publishtimedate,bookpricefloat)create table bookinfo(bookno varchar2(36) Primary key,bookname varchar2(40) not null, authorname varchar2(10) not null, publishtime date,bookprice float);create table major_sta

7、ts (bookpage int , authornumber int);bookinfo 表中插入、bookinfo(2) major_stats表中create or replace trigger UpdateMajorStats after insert or delete or update on bookinfo begindelete from major_stats;insert into major_stats(bookpage,authornumber)Select count(bookno),count(distinct authorname)from bookinfo;

8、 end;/bookinfomajor_stats表中數(shù)據(jù)的變化插入insert into bookinfo (bookno,bookname,authorname,publishtime,bookprice) values(001,oracle,yt,22-oct-2000,30);insert into bookinfo (bookno,bookname,authorname,publishtime,bookprice) values(002,math,zx,26-may-1994,23);insert into bookinfo (bookno,bookname,authorname,publishtime,bookprice) values(003,english,ly,22-oct-1995,40);insert into bookinfo (bookno,bookname,authorname,publishtime,bookprice) values(004,c+,lt,11-oct-1998,30);insert into bookinfo (bookno,bookname,authorname,publishtime,bookprice) values(005,