Oracle实验代码

1、游标与函数的使用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 N

2、UMBERIS 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_o

3、utput.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=

4、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功能的函数

5、：计算指定供应商编号的供应零件总数量。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

6、' 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 B

7、ODY 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 := 

8、9;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)

9、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 snam

10、e,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|'所供应的零件总数为：'|

11、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_count>0)then dbms_output.put_line('插入的供应

12、商存在'); 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、定义、调用存储过程：删除指定代码

13、的零件信息，并给出删除元组数。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_

14、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;

15、 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

16、;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.sn

17、o=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

18、 count(*) into v_count from s where sno is null; if v_count>0 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,C

19、ITY) 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 co

20、unt(*) into v_count1 from s where sno=:new.sno; if v_count1<1 then raise_application_error(-20001,'供应商编号为'|to_char(:new.sno)|'不存在'); end if; select count(*) into v_count2 from p where pno=:new.pno; if v_count2<1 then raise_application_error(-20002,'供应商编号为'|to_char(:new.

21、pno)|'不存在'); end if; select count(*) into v_count3 from j where jno=:new.jno; if v_count3<1 then raise_application_error(-20003,'供应商编号为'|to_char(:new.jno)|'不存在'); end if;end tr1_spj; insert into spj values('S10','P9','J4',500); -当删除或更新S表记录时，当SPJ表上有引

22、用时抛出异常create or replace trigger tr2_spjbefore delete or update of snoon sfor each row declare v_count number;begin select count(*) into v_count from spj where sno=:old.sno; if v_count>1 then raise_application_error (-20005,'供应商编号为'|to_char(:new.sno)|'在spj表中有引用'); end if;end tr2_sp

23、j; 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_sp

24、jafter 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='S9'where sno='S1'Oracle高级技术1、定义序列并完成基本表spj重新定义。create sequence s_testincrement by 1 start with 1;create table test(sno number,sname varchar2(10);insert i

25、nto 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 tabl

26、espace 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_pho