Oracle数据库试题

constraint pk_spj primary key (sno,pno,jno),

constraint fk_spj_sno foreign key (sno) references s(sno), constraint fk_spj_pno foreign key (pno) references p(pno), constraint fk_spj_jno foreign key (jno) references j(jno) 实验二 游标和函数

1、定义一个游标完成显示所有供应商名。 declare

v_sname s.sname%type;

cursor cursor_sname is select sname from s; begin

for curso in cursor_sname loop

dbms_output.put_line(curso.sname); end loop; end;

2、定义、调用一个简单函数：查询返回指定供应商编号的供应商名及其供应零件总数量。 create or replace function fun(f_sno in s.sno%type,f_sname out s.sname%type) return number as f_qty number; begin

SELECT s.sname,sum(qty) into f_sname,f_qty from s,spj WHERE s.sno=spj.sno GROUP BY s.sname,spj.sno having spj.sno=f_sno; return f_qty; end; declare

v_sno s.sno%type:='&sno'; v_sname s.sname%type; v_qty spj.qty%type; begin

v_qty:=fun(v_sno,v_sname);

dbms_output.put_line(v_sname||v_qty); end;

3、定义一个函数：对于给定的供应商号，判断是否存在，若存在返回0，否则返回-1。写一段程序调用此函数，若供应商号存在则在spj插入一元组。

create or replace function fun1(f1_sno in s.sno%type) return number as a number; begin

select count(sno) into a from s where sno=f1_sno; if a=0 then return -1; else return 0; end if; end; declare

v_sno s.sno%type:='&sno'; begin

if fun1(v_sno)=0 then

insert into spj values(v_sno,'P4','J5',120); end if; end;

select *from spj where sno='S1';

4、定义、调用一个类似于SUM功能的函数：计算指定供应商编号的供应零件总数量。 create or replace function fsum(fs_sno in s.sno%type) return number as summ number;

cursor cursor_sno is select qty from s,spj where s.sno=fs_sno and spj.sno=s.sno ; begin summ:=0;

for curso in cursor_sno loop summ:=summ+curso.qty; end loop;

return summ; end; declare

v_sno s.sno%type:='&sno'; c number; begin

c:=fsum(v_sno); dbms_output.put_line(c);

end;

5、将题2中函数改用包定义。 create or replace package pack

Is function fun(f_sno in s.sno%type,f_sname out s.sname%type) return number; end;

create or replace package body pack Is

function fun(f_sno in s.sno%type,f_sname out s.sname%type)

return number as f_qty number; begin

SELECT s.sname,sum(qty) into f_sname,f_qty from s,spj WHERE s.sno=spj.sno GROUP BY s.sname,spj.sno having spj.sno=f_sno; return f_qty; end fun; end; declare

v_sno s.sno%type:='&sno'; v_sname s.sname%type; v_qty spj.qty%type; begin

v_qty:=pack.fun(v_sno,v_sname); dbms_output.put_line(v_sname||v_qty); end;

实验三 存储过程

1、定义、调用简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。 create or replace procedure pro1 As p_qty number;

cursor cur1 is select sno,sum(qty) as p_qty from spj group by spj.sno; begin

for c in cur1 loop

update s set s.sqty=c.p_qty where sno=c.sno; end loop; end; begin

pro1; end;

2、定义、调用参数存储过程：查询返回指定供应商的供应零件总数量。比较与函数不同。 create or replace procedure pro2(p_sno in s.sno%type,p_qty out spj.qty%type) As begin

select sum(qty) into p_qty from spj WHERE spj.sno=p_sno GROUP BY spj.sno ; dbms_output.put_line('供应商'||p_sno||'的总数量为：'||p_qty); end; declare

v_sno s.sno%type:='&sno'; v_qty spj.qty%type; Begin

pro2(v_sno,v_qty); end;

3、定义、调用存储过程：插入一个供应商信息（所有信息由参数提供）。

create or replace procedure pro3(p_sno s.sno%type,p_sname s.sname%type,p_status s.status%type,p_city s.city%type) As cout number; begin

select count(*) into cout from s where s.sno=p_sno; if cout>0 then

dbms_output.put_line('编号为'||p_sno||'的供应商已存在！'); elsif cout=0 then

insert into s(sno,sname,status,city) values(p_sno,p_sname,p_status,p_city); dbms_output.put_line('插入成功！');

else dbms_output.put_line('出现其它错误！'); end if; end; declare

v_sno s.sno%type:='&sno'; v_sname s.sname%type:='&sname; v_status s.status%type:='&status'; v_city s.city%type:='&city'; begin

pro3(v_sno,v_sname,v_status,v_city); end; select *from s;

4、定义、调用存储过程：删除指定代码的零件信息，并给出删除元组数。 create or replace procedure pro4(p_pno in p.pno%type,p_cut out number) As begin

delete from spj where spj.pno=p_pno; delete from p where pno=p_pno; p_cut:=SQL%ROWCOUNT;

dbms_output.put_line('已经删除'||p_cut||'行'); end; declare

v_pno p.pno%type:='&pno'; v_cut number; begin

pro4(v_pno,v_cut); end;

5、定义、调用存储过程：修改指定代码项目的其它信息（所有信息由参数提供）。 create or replace procedure pro5(p_jno in j.jno%type,p_jname j.jname%type,p_city j.city%type) As p_cout number; begin

select count(*) into p_cout from j where jno=p_jno; if p_cout=0 then

dbms_output.put_line('编号'||p_jno||'的项目不存在');

else update j set jname=p_jname,city=p_city where jno=p_jno; dbms_output.put_line('修改成功！'); end if; end; declare

v_jno j.jno%type:='&jno'; v_jname j.jname%type:='&jname';

5、将数据增加到1万以上，在spj定义包括sno、pno、jno索引，对同一查询观察前后的时间变化。

create index idx_spj1 on spj1(sno,pno,jno); begin

for i in 1..10000 loop

insert into spj1 values(i,i,i,i); end loop; end; select *from spj1;

select *from spj1 where sno=10000;

本文来源：https://www.bwwdw.com/article/yed6.html