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 NUMBER IS 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 IS jg_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; DECLARE C_sno s.sno%TYPE :=sno; hf NUMBER ; BEGIN hf := 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 NUMBER IS 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_spj IS FUNCTION 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_spj IS FUNCTION fun_sno(s_sno IN s.sno%TYPE,s_sname OUT s.sname