数据库存储过程实验报告五.docVIP

实验五 1、 --存储过程:创建显示学生总人数的存储过程STU_COUNT create or replace procedure STU_COUNT as stu_sum number; begin select count(*) into stu_sum from student; dbms_output.put_line(学生总人数：||stu_sum); end; / execute stu_count(); --存储过程:创建显示学生信息的存储过程STUDENT_LIST set serveroutput on; create or replace procedure student_list as cursor select_hand is select sno,sname,ssex,sage,sdept,sclass from student; begin for i in select_hand loop dbms_output.put_line(i.sno|| ||i.sname|| ||i.ssex|| ||i.sage|| ||i.sdept|| ||i.sclass); end loop; STU_COUNT(); end; / execute student_list(); --存储过程:创建显示某个学生平均成绩的存储过程PRO_AVG set serveroutput on; create or replace procedure PRO_AVG(stu_sno in student.sno%type) as avg1 number(3,1); begin select avg(score) into avg1 from score group by sno having sno = stu_sno; dbms_output.put_line(平均成绩:||avg1); end; / execute PRO_AVG(96002); --存储过程:创建显示所有学生平均成绩的存储过程ALL_AVG set SERVEROUTPUT ON create or replace procedure ALL_AVG as cursor calcu_avg is select sno,avg(score) as avgs from score group by sno; begin for i in calcu_avg loop dbms_output.put_line(学号:||i.sno||平均成绩:||i.avgs); end loop; end; / execute ALL_AVG(); --存储过程:创建对学生姓名进行模糊查找的存储过程PRO_NAME SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE PRO_NAME (S_NAME IN STUDENT.SNAME%TYPE) AS CURSOR XXX IS SELECT SNAME FROM STUDENT WHERE SNAME LIKE %||S_NAME||%; BEGIN FOR I IN XXX LOOP DBMS_OUTPUT.PUT_LINE(I.SNAME); END LOOP; END; / EXECUTE PRO_NAME(马); 2、 --创建图书表 CREATE TABLE Book( bookID number(10) primary key, bookName varchar2(50), states varchar2(20) ); --创建学生表 CREATE TABLE Student( stuID number(10) primary key, stuName varchar2(50) ); --创建人借阅表 CREATE TABLE BookLend( lendID number(10) primary key, stuID number(10) not null, bookID number(10) not null, lendDate date, returnDate date ); --向Book表中插入一条记录(111,JAVA程序设计,在馆) INSERT INTO BOOK VALUES(111,JAVA程序设计,在馆); --向Student表中插入一条记录(169074264,韩书) INSERT INTO student VALUES(169074264,韩书); --创建触发器LEND_TRIG 当BookLend表中插入一条记录时修改该图书的状态为借出 CREATE OR REPLACE