Oracle实验代码.doc

游标与函数的使用1、定义一个游标完成显示所有供应商名。DECLARE v_sname s.sname%TYPE;CURSOR cu_sname IS SELECT sname FROM s;BEGIN FOR cur IN cu_sname LOOP dbms_output.put_line(cur.sname); END LOOP; END;2、定义、调用一个简单函数：查询返回指定供应商编号的供应商名及其供应零件总数量。CREATE OR REPLACE FUNCTION cx(cx_sno IN s.sno%TYPE ,cx_sname OUT s.sname%TYPE )RETURN NUMBERIS V_sqty NUMBER;BEGIN SELECT sname ,SUM(qty) INTO cx_sname,V_sqty FROM s,spj WHERE s.sno=spj.sno GROUP BY spj.sno,s.sname HAVING spj.sno=cx_sno; RETURN V_sqty; END cx; DECLARE V_sno s.sno%TYPE:=&sno; V_sqty s.sqty%TYPE; V_sname s.sname%TYPE; BEGIN V_sqty:=cx(V_sno,V_sname); dbms_output.put_line(V_sname|供应商|V_sno|所供应的零件总数为：|V_sqty); END;3、定义一个函数：对于给定的供应商号，判断是否存在，若存在返回0，否则返回-1。写一段程序调用此函数，若供应商号存在则在spj插入一元组。CREATE OR REPLACE FUNCTION CZ(cx_sno IN s.sno%TYPE)RETURN NUMBER ISjg_sno NUMBER ;BEGIN SELECT COUNT(*) INTO jg_sno FROM s WHERE s.sno=cx_sno ; IF jg_sno=0 THEN RETURN (-1); ELSE RETURN 0; end IF;END;DECLAREC_sno s.sno%TYPE :=&sno;hf NUMBER ;BEGINhf := CZ(C_sno);IF hf=0 THEN dbms_output.put_line(C_sno|存在); -INSERT INTO spj VALUES (C_sno,); ELSE dbms_output.put_line(C_sno|不存在);END IF;END; 4、定义、调用一个类似于SUM功能的函数：计算指定供应商编号的供应零件总数量。CREATE OR REPLACE FUNCTION fun_sum(v_sno IN s.sno%TYPE) RETURN NUMBERIS CURSOR cur_qty IS SELECT qty FROM spj WHERE sno=v_sno; v_sqty NUMBER:=0; temp NUMBER;BEGIN FOR temp IN cur_qty LOOP v_sqty:=v_sqty+temp.qty; END LOOP; return v_sqty;END; DECLARE v_sno s.sno%TYPE:=&sno; v_sqty s.sqty%TYPE; BEGIN v_sqty:=fun_sum(v_sno); dbms_output.put_line(供应商号|v_sno|所供应的零件总数为：|v_sqty);END;5、将题2中函数改用包定义。CREATE OR REPLACE PACKAGE pkage_spjISFUNCTION fun_sno(s_sno IN s.sno%TYPE,s_sname OUT s.sname%TYPE) RETURN NUMBER;end pkage_spj;CREATE OR REPLACE PACKAGE BODY pkage_spjISFUNCTION fun_sno(s_sno IN s.sno%TYPE,s_sname OUT s.sname%TYPE) RETURN NUMBER AS v_sqty NUMBER; BEGIN SELECT sname,SUM(qty)INTO s_sname,v_sqty FROM s,spj WHERE s.sno=spj.sno GROUP BY spj.sno,s.sname HAVING spj.sno=s_sno; RETURN v_sqty; END;end pkage_spj; DECLARE v_sno s.sno%TYPE := S1; v_sname s.sname%TYPE; v_sqty spj.qty%TYPE;BEGIN v_sqty:=pkage_spj.fun_sno(v_sno,v_sname); dbms_output.put_line(v_sname|供应商|v_sno|所供应的零件总数为：|v_sqty);END;存储过程的使用1、定义、调用简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。CREATE OR REPLACE PROCEDURE p1AScursor cur_1 is select sno ,sum(qty) as sumqty from spj group by sno; BEGIN for cur in cur_1 loop update s set sqty=cur.sumqty where s.sno=cur.sno; end loop; END;begin p1;end;2、定义、调用参数存储过程：查询返回指定供应商的供应零件总数量。比较与函数不同。CREATE OR REPLACE procedure p2(sno_sno IN s.sno%type,s_sname OUT s.sname%type,v_sqty out s.sqty%type) IS BEGIN SELECT sname,SUM(qty)INTO s_sname,v_sqty FROM s,spj WHERE s.sno=spj.sno GROUP BY spj.sno,s.sname HAVING spj.sno=sno_sno; end; DECLARE v_sno s.sno%TYPE:=S1; v_sqty s.sqty%TYPE; v_sname s.sname%TYPE; BEGIN pro2(v_sno,v_sname,v_sqty); dbms_output.put_line(v_sname|供应商|v_sno|所供应的零件总数为：|v_sqty);END;3、定义、调用存储过程：插入一个供应商信息（所有信息由参数提供）。create or replace procedure p3(s_sno in s.sno%type,s_sname in s.sname%type,s_status in s.status%type,s_city in s.city%type)isv_count number;begin select count(sno) into v_count from s where s.sno=s_sno; if(v_count0)then dbms_output.put_line(插入的供应商存在); else insert into s(sno,sname,status,city) values(s_sno,s_sname,s_status,s_city); end if; end; declare v_sno s.sno%type:=S6;v_sname s.sname%type:=科院;v_status s.status%type:=50;v_city s.city%type:=十堰;begin pro3(v_sno,v_sname,v_status,v_city);end;4、定义、调用存储过程：删除指定代码的零件信息，并给出删除元组数。CREATE OR REPLACE PROCEDURE proc4(p_pno p.pno%type ,p_count out number)isv_count number;begin delete from spj where spj.pno=p_pno ; delete from p where pno=p_pno; v_count:=sql%rowcount; p_count:=v_count;end;declarev_pno p.pno%type:=&pno;v_count number;begin proc4(v_pno,v_count); dbms_output.put_line(删除元组的数目为：|v_count);end;5、定义、调用存储过程：修改指定代码项目的其它信息（所有信息由参数提供）。CREATE OR REPLACE PROCEDURE proc5(p_pno p.pno%type,p_pname p.pname%type,p_color p.color%type,p_weight p.weight%type)is v_count number:=0;begin select count(p_pno) into v_count from p where p.pno=p_pno; if(v_count=0) then dbms_output.put_line(你所要修改的零件号不存在.); else update p set pname=p_pname,color=p_color,weight=p_weight where p.pno=p_pno;end if; end;declare v_pno p.pno%type:=&pno; v_pname p.pname%type:=螺丝刀; v_color p.color%type:=红; v_weight p.weight%type:=15;begin proc5(v_pno,v_pname,v_color,v_weight); end;触发器的使用1、定义一个触发器，完成及时计算所有供应商供应零件总数量。create or replace trigger t1_spjafter insert or delete or update on spjdeclarecursor cur_1 is select sno ,sum(qty) as sumqty from spj group by sno; begin for cur in cur_1 loop update s set sqty=cur.sumqty where s.sno=cur.sno; end loop; end t1_spj;insert into spj values(S5,P6,J4,700);2、定义触发器，实现实体完整性（以s表供应商代码sno为例）。create or replace trigger t2_spjafter insert on sdeclarev_sno s.sno%type;v_count number;cursor cur_sno is select sno from s group by sno having count(*)1;begin select count(*) into v_count from s where sno is null; if v_count0 then raise_application_error (-20008,主码sno不能取空值); end if; open cur_sno; fetch cur_sno into v_sno; if cur_sno%found then raise_application_error (-20012,主码sno不能重复); end if; end t2_spj;insert into S(SNO,SNAME,STATUS,CITY) values(S1,竟仪,20,天津);3、定义触发器，实现参照完整性（以spj表供应商代码sno参照s表供应商代码sno为例）。-当在SPJ表插入数据时，如果S,P,J表不存在相应的记录时，则插入失败create or replace trigger tr1_spjbefore insert or update of sno,pno,jnoon spjfor each row declare v_count1 number; v_count2 number; v_count3 number; begin select count(*) into v_count1 from s where sno=:new.sno; if v_count11 then raise_application_error(-20001,供应商编号为|to_char(:new.sno)|不存在); end if; select count(*) into v_count2 from p where pno=:new.pno; if v_count21 then raise_application_error(-20002,供应商编号为|to_char(:new.pno)|不存在); end if; select count(*) into v_count3 from j where jno=:new.jno; if v_count31 then raise_application_error (-20005,供应商编号为|to_char(:new.sno)|在spj表中有引用); end if;end tr2_spj; delete from s where s.sno=S1;-级联删除,删除S表中的记录时，同时删除SPJ表中的记录create or replace trigger tr3_spjafter delete on sfor each row declare begin delete from spj where spj.sno=:old.sno; end tr3_spj;delete from s where s.sno=S1;-级联更新，更新S表中的SNO时，同时更新SPJ表中的SNO记录create or replace trigger tr4_spjafter update of sno on sfor each row declare begin update spj set sno=:new.sno where sno=:old.sno; end tr3_spj; update s set sno=S9where sno=S1;Oracle高级技术1、定义序列并完成基本表spj重新定义。create sequence s_testincrement by 1 start with 1;create table test(sno number,sname varchar2(10);insert into test values (s_test.nextval,aa);insert into test values (s_test.nextval,bb);insert into test values (s_test.nextval,cc);select * from testselect s_test.nextval from dual;3、数据库安全性（1）定义用户TESTUSER，授予s表查询权限，观察授权前后不同情况。create user testuseridentified by oracledefault tablespace userstemporary tablespace temp;grant create session to testuser;revoke create session from testuser;grant select on hr.s to testuser;revoke select on hr.s from testuser;（2）定义角色TESTROLE，并授予存储过程执行权限，将用户TESTUSER加入；观察加入前后不同情况。create role testrole;grant execute any procedure to testrole;grant testrole to testuser;revoke testrole from testuser;create or replace procedure proc_snameascursor cur_sname is select sname from s;begin for rec in cur_sname loop dbms_output.put_line(rec.sname); end loop; end; begin c_sname; end;4、BLOB数据类型定义及其数据导入（以p表零件图片为例）drop table p_photo;create table p_photo( pno char(2) primary key, zp blob );cre