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';
百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle数据库试题在线全文阅读。
相关推荐：