Oracle實驗代碼

1、游標(biāo)與函數(shù)的使用1、定義一個游標(biāo)完成顯示所有供應(yīng)商名。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、定義、調(diào)用一個簡單函數(shù)：查詢返回指定供應(yīng)商編號的供應(yīng)商名及其供應(yīng)零件總數(shù)量。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|'供應(yīng)商'|V_sno|'所供應(yīng)的零件總數(shù)為：'|V_sqty); END;3、定義一個函數(shù)：對于給定的供應(yīng)商號，判斷是否存在，若存在返回0，否則返回-1。寫一段程序調(diào)用此函數(shù)，若供應(yīng)商號存在則在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、定義、調(diào)用一個類似于SUM功能的函數(shù)

5、：計算指定供應(yīng)商編號的供應(yīng)零件總數(shù)量。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('供應(yīng)商號'|v_sno|'所供應(yīng)的零件總數(shù)為：'|v_sqty);END;5、將題2中函數(shù)改用包定義。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|'供應(yīng)商'|v_sno|'所供應(yīng)的零件總數(shù)為：'|v_sqty);END;存儲過程的使用1、定義、調(diào)用簡單存儲過程：計算所有供應(yīng)商供應(yīng)零件總數(shù)量并修改供應(yīng)商相關(guān)列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、定義、調(diào)用參數(shù)存儲過程：查詢返回指定供應(yīng)商的供應(yīng)零件總數(shù)量。比較與函數(shù)不同。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|'供應(yīng)商'|v_sno|'所供應(yīng)的零件總數(shù)為：'|

11、v_sqty);END;3、定義、調(diào)用存儲過程：插入一個供應(yīng)商信息（所有信息由參數(shù)提供）。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('插入的供應(yīng)

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、定義、調(diào)用存儲過程：刪除指定代碼

13、的零件信息，并給出刪除元組數(shù)。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('刪除元組的數(shù)目為：'|v_count);end;5、定義、調(diào)用存儲過程：修改指定代碼項目的其它信息（所有信息由參數(shù)提供）。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;觸發(fā)器的使用1、定義一個觸發(fā)器，完成及時計算所有供應(yīng)商供應(yīng)零件總數(shù)量。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、定義觸發(fā)器，實現(xiàn)實體完整性（以s表供應(yīng)商代碼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不能重復(fù)'); end if; end t2_spj;insert into S(SNO,SNAME,STATUS,C

19、ITY) values('S1','竟儀',20,'天津');3、定義觸發(fā)器，實現(xiàn)參照完整性（以spj表供應(yīng)商代碼sno參照s表供應(yīng)商代碼sno為例）。-當(dāng)在SPJ表插入數(shù)據(jù)時，如果S,P,J表不存在相應(yīng)的記錄時，則插入失敗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,'供應(yīng)商編號為'|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,'供應(yīng)商編號為'|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,'供應(yīng)商編號為'|to_char(:new.jno)|'不存在'); end if;end tr1_spj; insert into spj values('S10','P9','J4',500); -當(dāng)刪除或更新S表記錄時，當(dāng)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,'供應(yīng)商編號為'|to_char(:new.sno)|'在spj表中有引用'); end if;end tr2_sp

23、j; delete from s where s.sno='S1'-級聯(lián)刪除,刪除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'-級聯(lián)更新，更新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高級技術(shù)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、數(shù)據(jù)庫安全性（1）定義用戶TESTUSER，授予s表查詢權(quán)限，觀察授權(quán)前后不同情況。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，并授予存儲過程執(zhí)行權(quán)限，將用戶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數(shù)據(jù)類型定義及其數(shù)據(jù)導(dǎo)入（以p表零件圖片為例）drop table p_photo;create table p_pho