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、定义一个游标完成显示所有供应商名。

declarev_sname s.sname%type;cursor cursor_sname is select sname from s;beginfor curso in cursor_snameloopdbms_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;beginSELECT 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;declarev_sno s.sno%type:='&sno';v_sname s.sname%type;v_qty spj.qty%type;beginv_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;beginselect count(sno) into a from s where sno=f1_sno;if a=0 thenreturn -1;else return 0;end if; end;declarev_sno s.sno%type:='&sno';beginif fun1(v_sno)=0 theninsert 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 ;beginsumm:=0;for curso in cursor_sno loopsumm:=summ+curso.qty;end loop;return summ; end;declarev_sno s.sno%type:='&sno';c number;beginc:=fsum(v_sno);dbms_output.put_line(c);end;5、将题2中函数改用包定义。

create or replace package packIs function fun(f_sno in s.sno%type,f_sname out s.sname%type)return number; end;create or replace package body packIs function fun(f_sno in s.sno%type,f_sname out s.sname%type)return number as f_qty number;beginSELECT 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;declarev_sno s.sno%type:='&sno';v_sname s.sname%type;v_qty spj.qty%type;beginv_qty:=pack.fun(v_sno,v_sname);dbms_output.put_line(v_sname||v_qty);end;实验三存储过程1、定义、调用简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。

create or replace procedure pro1As p_qty number;cursor cur1 is select sno,sum(qty) as p_qty from spj group by spj.sno;beginfor c in cur1 loopupdate s set s.sqty=c.p_qty where sno=c.sno;end loop; end;beginpro1; end;2、定义、调用参数存储过程：查询返回指定供应商的供应零件总数量。

比较与函数不同。

create or replace procedure pro2(p_sno in s.sno%type,p_qty out spj.qty%type)As beginselect 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;declarev_sno s.sno%type:='&sno';v_qty spj.qty%type;Beginpro2(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;beginselect count(*) into cout from s where s.sno=p_sno;if cout>0 thendbms_output.put_line('编号为'||p_sno||'的供应商已存在！');elsif cout=0 theninsert 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;declarev_sno s.sno%type:='&sno';v_sname s.sname%type:='&sname;v_status s.status%type:='&status';v_city s.city%type:='&city';beginpro3(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 begindelete 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;declarev_pno p.pno%type:='&pno';v_cut number;beginpro4(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;beginselect count(*) into p_cout from j where jno=p_jno;if p_cout=0 thendbms_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;declarev_jno j.jno%type:='&jno';v_jname j.jname%type:='&jname';v_city j.city%type:='&city';beginpro5(v_jno,v_jname,v_city);end;select * from j;实验四触发器1、定义一个触发器，完成及时计算所有供应商供应零件总数量。