《Oracle数据库实用教程(第二版)》课件.rar

P186 例9.1 CONN scott/tiger@orcl DECLARE v_bonus NUMBER(8,2); v_name VARCHAR2(30) := 'SCOTT'; v_hiredate DATE := '13-4月-09'; v_valid BOOLEAN := TRUE; BEGIN SELECT sal * 0.10 INTO v_bonus FROM emp WHERE ename = v_name; END; / P187 例9.2 CONN scott/tiger@orcl SET serveroutput ON DEFINE p_empno = 7934 VARIABLE g_salary NUMBER SET VERIFY ON BEGIN SELECT sal INTO :g_salary FROM emp WHERE empno='&p_empno'; DBMS_OUTPUT.PUT_LINE('工资值已取出至绑定变量g_salary'); END; / PRINT g_salary P189 例9.3 CONN scott/tiger@orcl SET serveroutput ON DECLARE v_eno emp.empno%TYPE; v_str VARCHAR2(60); v_str1 v_str%TYPE; BEGIN SELECT empno,job INTO v_eno,v_str FROM emp WHERE empno=7369; v_str1 := v_str; DBMS_OUTPUT.PUT_LINE(v_eno||' is a '||v_str1); END; / P190 例9.4 CONN scott/tiger@orcl DECLARE TYPE worker_record_type IS RECORD ( id NUMBER(3), name VARCHAR2(20) ); worker_record worker_record_type; BEGIN worker_record.id:=10; worker_record.name:='Jack'; DBMS_OUTPUT.PUT_LINE(worker_record.id ||':'||worker_record.name); END; / 例9.5 SET serveroutput ON DECLARE TYPE my_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; my_table my_table_type; BEGIN my_table(1):='百川到东海'; my_table(3):='何时复西归'; my_table(4):='少壮不努力'; my_table(-100):='老大徒伤悲'; DBMS_OUTPUT.PUT_LINE(my_table(1)); DBMS_OUTPUT.PUT_LINE(my_table(3)); DBMS_OUTPUT.PUT_LINE(my_table(4)); DBMS_OUTPUT.PUT_LINE(my_table(-100)); DBMS_OUTPUT.PUT_LINE(my_table.COUNT); END; / P191 例9.6 CONN scott/tiger@orcl SET serveroutput ON DECLARE CURSOR emp_cursor IS SELECT ename,sal,comm FROM emp; TYPE my_table_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER; my_table my_table_type; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO my_table(1); FETCH emp_cursor INTO my_table(2); CLOSE emp_cursor; DBMS_OUTPUT.PUT_LINE(my_table(1).ename); DBMS_OUTPUT.PUT_LINE(my_table(1).sal); DBMS_OUTPUT.PUT_LINE(my_table(2).ename); DBMS_OUTPUT.PUT_LINE(my_table(2).sal); END; / 例9.7 CONN scott/tiger@orcl SET serveroutput ON DECLARE TYPE table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; TYPE my_table_type IS TABLE OF table_type INDEX BY BINARY_INTEGER; my_table my_table_type; BEGIN my_table(1)(1):='百川到东海'; my_table(1)(3):='何时复西归'; my_table(1)(4):='少壮不努力'; my_table(2)(1):='老大徒伤悲'; DBMS_OUTPUT.PUT_LINE(my_table(1)(1)); DBMS_OUTPUT.PUT_LINE(my_table(1)(3)); DBMS_OUTPUT.PUT_LINE(my_table(1)(4)); DBMS_OUTPUT.PUT_LINE(my_table(2)(1)); END; / P192 例9.8 CONN scott/tiger@orcl SET serveroutput ON DECLARE TYPE my_varray_type IS VARRAY(2) OF NUMBER; v_array my_varray_type := my_varray_type(0,0); BEGIN FOR i IN v_array.FIRST .. v_array.LAST LOOP v_array(i) := i*100; DBMS_OUTPUT.PUT_LINE('Index '||to_char(i) ||' is: '||to_char(v_array(i))); END LOOP; v_array(3) := 300; END; / P194 例9.9 CONN scott/tiger@orcl SET serveroutput ON DECLARE v_dname dept.dname%TYPE; v_dept_rec dept%ROWTYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=10; SELECT * INTO v_dept_rec FROM dept WHERE deptno=10; DBMS_OUTPUT.PUT_LINE('10号部门的名称是：' || v_dname); DBMS_OUTPUT.PUT_LINE('10号部门的信息如下：'); DBMS_OUTPUT.PUT_LINE('编 号：' || v_dept_rec.deptno); DBMS_OUTPUT.PUT_LINE('名 称：' || v_dept_rec.dname); DBMS_OUTPUT.PUT_LINE('驻在地：' || v_dept_rec.loc); END; / 例9.10 CONN scott/tiger@orcl SET serveroutput ON DECLARE v_dname dept.dname%TYPE; v_dept_rec dept%ROWTYPE; BEGIN SELECT dname INTO v_dname FROM dept; DBMS_OUTPUT.PUT_LINE('取部门名称错误处理后返回。'); SELECT * INTO v_dept_rec FROM dept; DBMS_OUTPUT.PUT_LINE('10号部门的名称是：' || v_dname); DBMS_OUTPUT.PUT_LINE('10号部门的信息如下：'); DBMS_OUTPUT.PUT_LINE('编 号：' || v_dept_rec.deptno); DBMS_OUTPUT.PUT_LINE('名 称：' || v_dept_rec.dname); DBMS_OUTPUT.PUT_LINE('驻在地：' || v_dept_rec.loc); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('SELECT INTO语句写错了，返回了多条记录'); WHEN OTHERS THEN NULL; END; / P195 例9.11 CONN scott/tiger@orcl SET serveroutput ON DECLARE v_dname dept.dname%TYPE; v_dept_rec dept%ROWTYPE; BEGIN BEGIN SELECT dname INTO v_dname FROM dept; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('取部门名称错误处理放在这儿。'); WHEN OTHERS THEN NULL; END; BEGIN SELECT * INTO v_dept_rec FROM dept; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('取部门信息的错误处理放在这儿。'); WHEN OTHERS THEN NULL; END; DBMS_OUTPUT.PUT_LINE('取得结果部门的名称是：' || v_dname); DBMS_OUTPUT.PUT_LINE('取得结果部门的信息如下：'); DBMS_OUTPUT.PUT_LINE('编 号：' || v_dept_rec.deptno); DBMS_OUTPUT.PUT_LINE('名 称：' || v_dept_rec.dname); DBMS_OUTPUT.PUT_LINE('驻在地：' || v_dept_rec.loc); END; / P197 例9.12 CONN scott/tiger@orcl SET serveroutput ON DECLARE grade char:='B'; BEGIN IF grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade = 'D' THEN DBMS_OUTPUT. PUT_LINE('Fair'); ELSIF grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END IF; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; / P198 例9.13 CONN scott/tiger@orcl SET serveroutput ON DECLARE ret NUMBER := 1; again NUMBER := 1; BEGIN DBMS_OUTPUT.PUT_LINE('基本LOOP语句的输出：'); LOOP DBMS_OUTPUT.put(ret); ret:=ret+1; EXIT WHEN ret>5; END LOOP; <<label0>> DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('FOR循环语句的输出：'); FOR ret IN 1..5 LOOP DBMS_OUTPUT.put(ret); END LOOP; again:=again-1; IF again>=0 THEN DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('下面转到标号label0处继续执行'); GOTO label0; END IF; DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('程序运行结束。'); END; / P200 例9.14 CONN hr/hrpwd@orcl DECLARE CURSOR Employees_Cursor(p_empno NUMBER) IS SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > p_empno; emp_rec Employees_Cursor%ROWTYPE; BEGIN DELETE FROM sal_history; DELETE FROM mgr_history; COMMIT; OPEN Employees_Cursor(200); LOOP FETCH Employees_Cursor INTO emp_rec; EXIT WHEN Employees_Cursor%NOTFOUND; IF emp_rec.sal > 10000 THEN INSERT INTO sal_history VALUES(emp_rec.empid,emp_rec.hiredate,emp_rec.sal) ; END IF; IF emp_rec.mgr > 200 THEN INSERT INTO mgr_history VALUES(emp_rec.empid,emp_rec.mgr,emp_rec.sal) ; END IF; END LOOP; CLOSE Employees_Cursor; END; / SELECT * FROM sal_history; SELECT * FROM mgr_history; P202 例9.15 CONN scott/tiger@orcl DECLARE v_bonus NUMBER; CURSOR emp_cursor IS SELECT ename,sal,comm FROM emp; BEGIN DELETE FROM bonus; FOR emp_rec IN emp_cursor LOOP v_bonus:= (emp_rec.sal*0.05)+(nvl(emp_rec.comm,0)*0.25); INSERT INTO BONUS(ename,sal,comm) VALUES(emp_rec.ename,emp_rec.sal,v_bonus); END LOOP; COMMIT; END; / P203 例9.16 CONN scott/tiger@orc