
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@orcl
DECLARE
   v_sal NUMBER(7,2);
   CURSOR emp_cur IS SELECT 12*sal FROM emp FOR UPDATE;
BEGIN
   OPEN emp_cur; 
   LOOP
     FETCH emp_cur INTO v_sal;
     EXIT WHEN emp_cur%NOTFOUND;
     IF v_sal<30000 THEN
         UPDATE emp SET sal=sal*1.2 WHERE CURRENT OF emp_cur;
     ELSE
         UPDATE emp SET sal=sal*1.15 WHERE CURRENT OF emp_cur;
     END IF;
   END LOOP;
   CLOSE emp_cur;
   COMMIT;
END;
/
SET PAGESIZE 200
SET LINESIZE 100
SELECT * FROM emp;

P205
9.17
CONN scott/tiger@orcl
DROP TABLE test_msg;
CREATE TABLE test_msg(
  empno NUMBER(4),
  message VARCHAR2(20));
SET serveroutput ON
DECLARE
  v_sal NUMBER;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno=888;
  DBMS_OUTPUT.PUT_LINE('888Ĺǣ'||to_char(v_sal));
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
    INSERT INTO test_msg(empno,message)
      VALUES(888,'not exists');
    COMMIT;
  WHEN OTHERS THEN
    ROLLBACK WORK;
END;
/

P207
9.18
CONN scott/tiger@orcl
SET serveroutput ON
DEFINE p_deptno = 30
DECLARE
  e_emps_remaining EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_emps_remaining,-2292);
BEGIN
  DELETE FROM dept
     WHERE deptno = &p_deptno;
  COMMIT;
EXCEPTION
  WHEN e_emps_remaining THEN  
    DBMS_OUTPUT.PUT_LINE ('ɾ:'
        ||TO_CHAR(&p_deptno) || 'йԱڸò');
END;
/
9.19
CONN scott/tiger@orcl
SET serveroutput ON
DECLARE
  v_sal NUMBER;
  e_user_1 EXCEPTION;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno=7788;
  DBMS_OUTPUT.PUT_LINE('7788ĹΪ:' || v_sal );
  IF v_sal>=3000 THEN
     RAISE e_user_1;
  END IF;
EXCEPTION
  WHEN e_user_1 THEN
    DBMS_OUTPUT.PUT_LINE('ûԶe_user_1');
    INSERT INTO test_msg(empno,message)
      VALUES(7788,'Ļ');
    COMMIT;
  WHEN OTHERS THEN
    ROLLBACK WORK;
END;
/

P208
9.20
CONN hr/hrpwd@orcl
SET serveroutput ON
SET verify OFF
DEFINE p_empno = 205
DECLARE
  e_emps_remaining EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_emps_remaining,-2292); 
BEGIN
  DELETE FROM employees
     WHERE employee_id = &p_empno;
  IF SQL%NOTFOUND THEN
     RAISE_APPLICATION_ERROR(-20001,
       'ָĹԱ'||TO_CHAR(&p_empno)||'',TRUE);
  END IF;
  COMMIT;
EXCEPTION
  WHEN e_emps_remaining THEN 
     RAISE_APPLICATION_ERROR(-20002,'ɾԱ:'
       ||TO_CHAR(&p_empno)||'м¼ոùԱ',TRUE);
END;
/

P210
9.21
CONN scott/tiger@orcl
CREATE OR REPLACE PROCEDURE query_emp
  (p_no     IN  emp.empno%TYPE,
   p_name   OUT emp.ename%TYPE,
   p_sal OUT emp.sal%TYPE,
   p_comm   OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename,sal,comm
    INTO p_name,p_sal,p_comm
    FROM emp
    WHERE empno = p_no;
END query_emp;
/
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE query_emp(7369,:g_name,:g_sal,:g_comm)
PRINT g_name

P211
9.22
CONN scott/tiger@orcl
CREATE or REPLACE FUNCTION current_time RETURN VARCHAR2
AS
BEGIN
  return to_char(sysdate,'YYYY-MM-DD HH24')||':'
      ||to_char(sysdate,'MI')||':'||to_char(sysdate,'SS');
END;
/
SELECT  current_time  FROM DUAL;
9.23
CONN scott/tiger@orcl
CREATE or REPLACE FUNCTION conv_num(p_n NUMBER) RETURN VARCHAR2
IS
  ret VARCHAR2(24);
  str VARCHAR2(24);
  result VARCHAR2(24):='';
BEGIN
  IF p_n<0 THEN
    RAISE_APPLICATION_ERROR(-20098,'ֵ̫С',TRUE);
  END IF;
  IF p_n>=1000000 THEN
    RAISE_APPLICATION_ERROR(-20099,'ֵ̫',TRUE);
  END IF;
  --ԲȡʽתΪǰ06λַ
  ret:=to_char(round(p_n,0),'000000');
  --ʮ
  SELECT decode(substrb(ret,2,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||'ʰ' 
        INTO str FROM dual;
  result:=result||str;
  --
  SELECT decode(substrb(ret,3,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||''
        INTO str FROM dual;
  result:=result||str;
  --ǧ
  SELECT decode(substrb(ret,4,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||'Ǫ'
        INTO str FROM dual;
  result:=result||str;
  --
  SELECT decode(substrb(ret,5,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||''
        INTO str FROM dual;
  result:=result||str;
  --ʮ
  SELECT decode(substrb(ret,6,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||'ʰ'
        INTO str FROM dual;
  result:=result||str;
  --Ԫ
  SELECT decode(substrb(ret,7,1),'0','','1','Ҽ',
          '2','','3','','4','','5','','6','½',
          '7','','8','','9','')||'Ԫ'
        INTO str FROM dual;
  result:=result||str;
  return result;
END;
/
SELECT conv_num(123456.7) FROM dual;
SELECT conv_num(9123456.7) FROM dual;

P214
9.24
CONN scott/tiger@orcl
DROP TABLE log_table;
CREATE TABLE log_table(
  empno number(4),
  user_id VARCHAR2(20),
  log_date date);
CREATE OR REPLACE PROCEDURE dele_emp 
  (p_no IN  emp.empno%TYPE)
IS
  PROCEDURE write_log
  IS 
  BEGIN
    INSERT INTO log_table(empno,user_id,log_date)
      VALUES(p_no,USER,SYSDATE); 
  END;
BEGIN
  DELETE FROM emp
    WHERE empno = p_no;
  write_log;
END;
/
EXECUTE dele_emp(7798)
SELECT * FROM log_table;

P215
9.25
CONN scott/tiger@orcl
CREATE OR REPLACE PROCEDURE query_emp
  (p_no     IN  emp.empno%TYPE,
   p_name  OUT emp.ename%TYPE,
   p_sal    OUT emp.sal%TYPE,
   p_comm  OUT emp.comm%TYPE)
  AUTHID CURRENT_USER  
IS    
BEGIN
  SELECT ename,sal,comm
    INTO p_name,p_sal,p_comm
    FROM scott.emp
    WHERE empno = p_no;
END query_emp;
/
GRANT EXECUTE ON query_emp TO HR ;
CONN hr/hrpwd@orcl
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE scott.query_emp(7369,:g_name,:g_sal,:g_comm)
CONN scott/tiger@orcl
GRANT SELECT ON emp TO HR; 

P216
9.26
CONN scott/tiger@orcl
SET PAGESIZE 200
COL object_name FORMAT A60
SELECT object_name,object_type FROM user_objects;
COL NAME FORMAT A15
COL TEXT FORMAT A40 
COL LINE FORMAT 99 
SELECT name,type,line,text
  FROM user_source
  WHERE name='QUERY_EMP';

P217
9.27
CONN scott/tiger@orcl
CREATE OR REPLACE PACKAGE wage_package IS
  g_sal NUMBER := 0;
  g_comm NUMBER := 0;
  FUNCTION get_sal (p_eno IN NUMBER) RETURN NUMBER;
  PROCEDURE set_sal (p_eno IN NUMBER, p_sal IN NUMBER);
  FUNCTION get_comm (p_eno IN NUMBER) RETURN NUMBER;
  PROCEDURE set_comm (p_eno IN NUMBER, p_comm IN NUMBER);
  PROCEDURE reset_sal(p_sal IN NUMBER);
  PROCEDURE reset_comm(p_comm IN NUMBER);
END wage_package;
/

P218
9.28
CONN scott/tiger@orcl
CREATE OR REPLACE PACKAGE BODY wage_package
IS
  FUNCTION get_sal (p_eno IN NUMBER) RETURN NUMBER
  IS
    v_sal NUMBER;
  BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno=p_eno;
    return(v_sal);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20091,'ŵĹԱ');
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20092,'Աظ');
  END get_sal;
  PROCEDURE set_sal (p_eno IN NUMBER, p_sal IN NUMBER) IS
  BEGIN
    UPDATE emp SET sal=p_sal WHERE empno=p_eno;
    COMMIT;
  END set_sal;
  FUNCTION get_comm (p_eno IN NUMBER) RETURN NUMBER
  IS
    v_comm NUMBER;
  BEGIN
    SELECT comm INTO v_comm FROM emp WHERE empno=p_eno;
    return(v_comm);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20091,'ŵĹԱ');
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20092,'Աظ');
  END get_comm;
  PROCEDURE set_comm (p_eno IN NUMBER, p_comm IN NUMBER) IS
  BEGIN
    UPDATE emp SET comm=p_comm WHERE empno=p_eno;
    COMMIT;
  END set_comm;
  PROCEDURE reset_sal(p_sal IN NUMBER) IS 
  BEGIN
    g_sal := p_sal;
  END reset_sal;
  PROCEDURE reset_comm(p_comm IN NUMBER) IS
  BEGIN
    g_comm := p_comm;
  END reset_comm;
END wage_package;
/

P219
9.29
CONN scott/tiger@orcl
CREATE OR REPLACE PACKAGE global_1_package IS 
  PI constant NUMBER := 3.1415926;
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE;
END global_1_package;
/
CREATE OR REPLACE FUNCTION circumference
     (p_r IN NUMBER) RETURN NUMBER
IS 
BEGIN
  return ( 2 * global_1_package.PI * p_r);
END circumference;
/
SELECT circumference(10) FROM dual;

P220
CONN scott/tiger@orcl
CREATE OR REPLACE PACKAGE wage_package1
IS 
  PROCEDURE update_wages
     (p_eno IN emp.empno%TYPE,
      p_sal IN emp.sal%TYPE,
      p_comm IN emp.comm%TYPE);
  PROCEDURE update_wages
     (p_eno IN emp.empno%TYPE,
      p_sal IN emp.sal%TYPE);
END wage_package1;
/
CREATE OR REPLACE PACKAGE BODY wage_package1
IS 
  PROCEDURE update_wages
     (p_eno IN emp.empno%TYPE,
      p_sal IN emp.sal%TYPE,
      p_comm IN emp.comm%TYPE)
  IS
  BEGIN
    UPDATE emp SET sal=p_sal, comm=p_comm WHERE empno=p_eno;
  END update_wages;
  PROCEDURE update_wages
     (p_eno IN emp.empno%TYPE,
      p_sal IN emp.sal%TYPE)
  IS
  BEGIN
    UPDATE emp SET sal=p_sal WHERE empno=p_eno;
  END update_wages;
END wage_package1;
/
SELECT empno,ename,sal,comm FROM emp WHERE empno=7900;
EXECUTE wage_package1.update_wages(7900,2000)
SELECT empno,ename,sal,comm FROM emp WHERE empno=7900;
EXECUTE wage_package1.update_wages(7900,1000,200)
SELECT empno,ename,sal,comm FROM emp WHERE empno=7900;

P221
9.31
CREATE OR REPLACE PACKAGE BODY wage_package2
IS 
  --ǰ
  PROCEDURE calc_sale();
  PROCEDURE calc_tax();
  --ʵָ
  PROCEDURE cacl_reward ()
  IS
  BEGIN
    calc_sale();
    calc_tax();
    ;
  END;
  PROCEDURE calc_sale()
  IS
  BEGIN
    ;
  END;
  PROCEDURE calc_tax()
  IS
  BEGIN
    ;
  END;
END wage_package2;
/

P222
9.32
CONN scott/tiger@orcl
CREATE OR REPLACE PACKAGE wage_package3 IS
  g_sal NUMBER := 0;
  g_comm NUMBER := 0;
  FUNCTION get_sal (p_eno IN NUMBER) RETURN NUMBER;
  PROCEDURE set_sal (p_eno IN NUMBER, p_sal IN NUMBER);
  FUNCTION get_comm (p_eno IN NUMBER) RETURN NUMBER;
  PROCEDURE set_comm (p_eno IN NUMBER, p_comm IN NUMBER);
  PROCEDURE reset_sal(p_sal IN NUMBER);
  PROCEDURE reset_comm(p_comm IN NUMBER);
END wage_package3;
/

CREATE OR REPLACE PACKAGE BODY wage_package3
IS
  FUNCTION get_sal (p_eno IN NUMBER) RETURN NUMBER
  IS
    v_sal NUMBER;
  BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno=p_eno;
    return(v_sal);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20091,'ŵĹԱ');
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20092,'Աظ');
  END get_sal;
  PROCEDURE set_sal (p_eno IN NUMBER, p_sal IN NUMBER) IS
  BEGIN
    UPDATE emp SET sal=p_sal WHERE empno=p_eno;
    COMMIT;
  END set_sal;
  FUNCTION get_comm (p_eno IN NUMBER) RETURN NUMBER
  IS
    v_comm NUMBER;
  BEGIN
    SELECT comm INTO v_comm FROM emp WHERE empno=p_eno;
    return(v_comm);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20091,'ŵĹԱ');
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20092,'Աظ');
  END get_comm;
  PROCEDURE set_comm (p_eno IN NUMBER, p_comm IN NUMBER) IS
  BEGIN
    UPDATE emp SET comm=p_comm WHERE empno=p_eno;
    COMMIT;
  END set_comm;
  PROCEDURE reset_sal(p_sal IN NUMBER) IS 
  BEGIN
    g_sal := p_sal;
  END reset_sal;
  PROCEDURE reset_comm(p_comm IN NUMBER) IS
  BEGIN
    g_comm := p_comm;
  END reset_comm;
  BEGIN
    SELECT MAX(sal) INTO g_sal FROM emp;
    SELECT MAX(comm) INTO g_comm FROM emp;
END wage_package3;
/

P223
9.33
CONN sys/syspwd@orcl AS SYSDBA
GRANT CREATE TABLE TO scott;
GRANT DROP ANY TABLE TO scott;
CONN scott/tiger@orcl
DROP TABLE dept_wage;
CREATE TABLE dept_wage 
  AS SELECT deptno,SUM(sal) total_sal,SUM(comm) total_comm,
            SUM(sal+nvl(comm,0)) total_wage
     FROM emp GROUP BY deptno;
CREATE OR REPLACE PROCEDURE dept_wage_cacl
IS
  CURSOR dept_cursor IS 
    SELECT deptno,total_sal,total_comm,total_wage
      FROM dept_wage;
  v_dept dept_cursor%ROWTYPE;
  cursor_name INTEGER;
  v_rownum INTEGER;
BEGIN
  cursor_name := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_name, 'DROP TABLE dept_wage',
    DBMS_SQL.NATIVE);
  v_rownum := DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
  cursor_name := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_name, 'CREATE TABLE dept_wage 
    AS SELECT deptno,SUM(sal) total_sal,SUM(comm) total_comm,
              SUM(sal+nvl(comm,0)) total_wage
       FROM emp GROUP BY deptno', DBMS_SQL.NATIVE);
  v_rownum := DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
  FOR  dept_rec IN dept_cursor LOOP 
    DBMS_OUTPUT.PUT_LINE('DEPTNO:'||dept_rec.deptno
       ||',SAL:'||dept_rec.total_sal
       ||',COMM:'||dept_rec.total_comm
       ||',WAGE:'||dept_rec.total_wage);
  END LOOP;
END;
/
SET serveroutput ON
EXECUTE dept_wage_cacl

P224
9.34
CONN scott/tiger@orcl
CREATE PROCEDURE del_rows(p_tab_name IN VARCHAR2)
IS
BEGIN
  EXECUTE IMMEDIATE 'delete from ' || p_tab_name;
END;
/
CREATE OR REPLACE PROCEDURE dept_wage_cacl
IS
  CURSOR dept_cursor IS 
    SELECT deptno,total_sal,total_comm,total_wage
      FROM dept_wage;
  v_dept dept_cursor%ROWTYPE;
BEGIN
  del_rows('dept_wage');
  INSERT INTO dept_wage 
     SELECT deptno,SUM(sal) total_sal,SUM(comm) total_comm,
            SUM(sal+nvl(comm,0)) total_wage
     FROM emp GROUP BY deptno;
  FOR  dept_rec IN dept_cursor LOOP 
    DBMS_OUTPUT.PUT_LINE('DEPTNO:'||dept_rec.deptno
       ||',SAL:'||dept_rec.total_sal
       ||',COMM:'||dept_rec.total_comm
       ||',WAGE:'||dept_rec.total_wage);
  END LOOP;
END;
/

P225
9.35
SET SERVEROUTPUT ON
DECLARE
  str VARCHAR2(20);
  i INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('AAAAA');
  DBMS_OUTPUT.PUT_LINE('BBB');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.GET_LINE(str,i);
  DBMS_OUTPUT.PUT_LINE(i||':'||str);
  DBMS_OUTPUT.PUT_LINE('CCC');
END ;
/

P226
9.36
CONN scott/tiger@orcl
CREATE OR REPLACE PROCEDURE dept_wage_rpt
  (p_filedir IN VARCHAR2, p_filename IN VARCHAR2)
IS
  v_filehandle UTL_FILE.FILE_TYPE;
  CURSOR dept_cursor IS 
    SELECT deptno,total_sal,total_comm,total_wage
      FROM dept_wage;
  v_str VARCHAR2(200);
BEGIN
  v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w');
  UTL_FILE.NEW_LINE (v_filehandle);
  UTL_FILE.PUTF (v_filehandle,'DEPT WAGE REPORT: '
      ||'GENERATED ON %s\n',SYSDATE);
  UTL_FILE.NEW_LINE (v_filehandle);
  FOR  dept_rec IN dept_cursor LOOP 
    v_str:=RPAD('DEPTNO:'||dept_rec.deptno,10,' ')
       ||RPAD(',SAL:'||dept_rec.total_sal,10,' ')
       ||RPAD(',COMM:'||dept_rec.total_comm,10,' ')
       ||',WAGE:'||dept_rec.total_wage;
    UTL_FILE.PUT_LINE(v_filehandle, v_str);
  END LOOP;
  UTL_FILE.NEW_LINE (v_filehandle);
  UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR (-20081, 'Чļ');
  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR (-20082, 'дļ');
END dept_wage_rpt;
/
CONN sys/syspwd@orcl AS SYSDBA
ALTER SYSTEM SET utl_file_dir='d:\data' SCOPE=spfile;
SHUTDOWN
STARTUP
CONN scott/tiger@orcl
EXECUTE dept_wage_rpt('d:\data','dept_wage_rpt.txt')

P229
9.37
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER check_sal_emp
  BEFORE INSERT OR UPDATE ON emp
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  BEGIN
    IF :new.sal < 0 THEN
       RAISE_APPLICATION_ERROR (-20501,'ԱʲΪ');
    END IF;
    IF :new.sal > 2 * wage_package3.g_sal THEN
       RAISE_APPLICATION_ERROR(-20502,'Աʳ߹2');
    END IF;
END;
/
INSERT INTO emp (empno, ename, hiredate,job, sal, deptno)
  VALUES (300, 'Jordan', SYSDATE,'IT_PROG', -3, 10);
INSERT INTO emp (empno, ename, hiredate,job, sal, deptno)
  VALUES (300, 'TracyZhou', SYSDATE,'ACC.OFFI.', 16000, 10);

P230
9.38
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER check_sal_emp
  BEFORE INSERT OR UPDATE OR DELETE ON emp
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  BEGIN
    IF DELETING THEN
       IF (TO_CHAR (SYSDATE,'DY') IN ('','')) OR 
       (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '17') THEN
          RAISE_APPLICATION_ERROR (-20503,
             'ڷǹʱɾԱ¼');
       END IF;
    END IF;
    IF INSERTING OR UPDATING THEN
       IF :new.sal < 0 THEN
          RAISE_APPLICATION_ERROR (-20501,'ԱʲΪ');
       END IF;
       IF :new.sal > 2 * wage_package3.g_sal THEN
          RAISE_APPLICATION_ERROR (-20502,
             'Աʳ߹2');
       END IF;
    END IF;
END;
/
DELETE FROM emp WHERE empno=7369;

P231
9.39  
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER check_sal_emp
  BEFORE UPDATE OF sal ON emp
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  WHEN (new.job != 'SALESMAN' AND new.sal<old.sal)
  BEGIN
    RAISE_APPLICATION_ERROR 
      (-20504,'Ա⣬Աֻ!');
END;
/
UPDATE emp SET sal=100 WHERE EMPNO=7369;
9.40
CONN scott/tiger@orcl
ALTER TABLE dept ADD(tot_dept_sal NUMBER(8,2));
UPDATE dept SET tot_dept_sal=
   ( SELECT SUM(sal)
     FROM emp
     WHERE emp.deptno = dept.deptno );
SELECT * FROM dept;
DROP VIEW emp_info;
CREATE VIEW emp_info AS
  SELECT e.empno, e.ename, e.sal, e.deptno,
         e.job, d.dname, d.loc
  FROM emp e, dept d
  WHERE e.deptno = d.deptno;
CREATE OR REPLACE TRIGGER new_emp_dept
  INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_info
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
     INSERT INTO emp(empno, ename, sal,deptno, job, hiredate)
       VALUES (:NEW.empno, :NEW.ename, :NEW.sal,
          :NEW.deptno, :New.job, SYSDATE);
     UPDATE dept
       SET tot_dept_sal = tot_dept_sal + :NEW.sal
       WHERE deptno = :NEW.deptno;
  ELSIF DELETING THEN
     DELETE FROM emp
       WHERE empno = :OLD.empno;
     UPDATE dept
       SET tot_dept_sal = tot_dept_sal - :OLD.sal
       WHERE deptno = :OLD.deptno;
  ELSIF UPDATING ('sal') THEN
     UPDATE emp
       SET sal = :NEW.sal
       WHERE empno = :OLD.empno;
     UPDATE dept
       SET tot_dept_sal = tot_dept_sal + (:NEW.sal - :OLD.sal)
       WHERE deptno = :OLD.deptno;
  ELSIF UPDATING ('deptno') THEN
     UPDATE emp
       SET deptno = :NEW.deptno
       WHERE empno = :OLD.empno;
     UPDATE dept
       SET tot_dept_sal = tot_dept_sal - :OLD.sal
       WHERE deptno = :OLD.deptno;
     UPDATE dept
       SET tot_dept_sal = tot_dept_sal + :NEW.sal
       WHERE deptno = :NEW.deptno;
  END IF;
END;
/

P233
9.41
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE TRIGGER drop_alter_check
   BEFORE DROP OR ALTER
     ON DATABASE
BEGIN
  IF (TO_CHAR (SYSDATE,'DY') IN ('','')) OR 
       (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '17') THEN
            RAISE_APPLICATION_ERROR (-20602,
              'ڷǹʱɾ޸Ķ');
  END IF;
  IF USER!='SYS' THEN
     RAISE_APPLICATION_ERROR 
        (-20601,'ûɾ޸ĶȨ!');
  END IF;
END;
/
CONN scott/tiger@orcl
DROP TABLE emp;

P234
9.42
CONN scott/tiger@orcl
DROP TABLE log_trig_table;
CREATE TABLE log_trig_table(
  freq NUMBER(7),
  user_id VARCHAR2(30),
  log_date DATE,
  action VARCHAR2(60));
CREATE OR REPLACE TRIGGER logon_trig
  AFTER LOGON ON SCHEMA
DECLARE
  ret NUMBER;
BEGIN
  SELECT max(freq) INTO ret FROM  log_trig_table;
  IF NVL(ret,0)<1 THEN
     ret := 1;
  ELSE
     ret := ret+1; 
  END IF;
  INSERT INTO log_trig_table(freq,user_id, log_date, action)
    VALUES (ret,USER, SYSDATE, 'Logging on');
END;
/
CREATE OR REPLACE TRIGGER logoff_trig
  BEFORE LOGOFF ON SCHEMA
DECLARE
  ret NUMBER;
BEGIN
  SELECT max(freq) INTO ret FROM  log_trig_table;
  INSERT INTO log_trig_table(freq,user_id, log_date, action)
    VALUES (ret,USER, SYSDATE, 'Logging off');
END;
/
DISCONNECT
CONN scott/tiger@orcl
SELECT * FROM log_trig_table ORDER BY freq,action;

P235
9.43
CONN scott/tiger@orcl
CREATE OR REPLACE PROCEDURE logon_trig_proc
 IS
   ret NUMBER;
 BEGIN
   SELECT max(freq) INTO ret FROM log_trig_table;
   IF NVL(ret,0)<1 THEN
      ret := 1;
   ELSE
      ret := ret+1; 
   END IF;
   INSERT INTO log_trig_table(freq,user_id, log_date, action)
     VALUES (ret,USER, SYSDATE, 'Logging on');
 END;
 /
CREATE OR REPLACE PROCEDURE logoff_trig_proc
IS
  ret NUMBER;
BEGIN
  SELECT max(freq) INTO ret FROM  log_trig_table;
  INSERT INTO log_trig_table(freq,user_id, log_date, action)
    VALUES (ret,USER, SYSDATE, 'Logging off');
END;
/
CREATE OR REPLACE TRIGGER logon_trig
  AFTER LOGON ON SCHEMA
CALL logon_trig_proc 
/
CREATE OR REPLACE TRIGGER logoff_trig
  BEFORE LOGOFF ON SCHEMA
CALL logoff_trig_proc 
/
DISCONNECT
CONN scott/tiger@orcl
SELECT * FROM log_trig_table ORDER BY freq,action;
9.44
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER check_salary
  BEFORE INSERT OR UPDATE OF sal, job
  ON emp
  FOR EACH ROW
  WHEN (NEW.job <> 'SALSMAN')
DECLARE
  v_minsal emp.sal%TYPE;
  v_maxsal emp.sal%TYPE;
BEGIN
  SELECT MIN(sal), MAX(sal) INTO v_minsal, v_maxsal
    FROM emp
    WHERE job = :NEW.job;
  IF :NEW.sal < v_minsal OR :NEW.sal > v_maxsal THEN
     RAISE_APPLICATION_ERROR(-20089,'ʳΧ');
  END IF;
END;
/
UPDATE emp
  SET sal = 350
  WHERE ename = 'JAMES';
DROP TRIGGER check_salary;

P237
9.45
CONN scott/tiger@orcl
DROP TABLE cust_account ;
CREATE TABLE cust_account (
  cust_id varchar2(20),
  cust_name varchar2(80),
  balance NUMBER(12,2),
  cust_addr varchar2(80));
DROP TABLE audit_account_table;
CREATE TABLE audit_account_table(
  user_id varchar2(20),
  cust_id varchar2(20),
  old_name varchar2(80),
  cust_name varchar2(80),
  old_balance NUMBER(12,2),
  balance NUMBER(12,2),
  old_addr varchar2(80),
  cust_addr varchar2(80),
  oper_time timestamp,
  comments varchar2(10));
CREATE OR REPLACE PACKAGE account_reason_package IS
   g_reason VARCHAR2(60) :='OPEN';
END;
/
CREATE OR REPLACE TRIGGER audit_account_oper
  BEFORE DELETE OR INSERT OR UPDATE ON cust_account
  FOR EACH ROW
BEGIN
  IF (account_reason_package.g_reason IS NULL) THEN
     RAISE_APPLICATION_ERROR (-20101, 'òȨ');
  ELSE
     INSERT INTO audit_account_table (user_id,cust_id,
       old_name,cust_name,old_balance,balance,old_addr,
       cust_addr,oper_time,comments)
     VALUES (USER,:OLD.cust_id,:OLD.cust_name,:NEW.cust_name,
       :OLD.balance,:NEW.balance,:OLD.cust_addr,
       :NEW.cust_addr,SYSDATE,account_reason_package.g_reason);
  END IF;
END;
/
CREATE OR REPLACE TRIGGER cleanup_audit_account
  AFTER INSERT OR UPDATE OR DELETE ON cust_account
BEGIN
  account_reason_package.g_reason := NULL;
END;
/

P238
9.46
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER deptno_cascade_updates
  BEFORE UPDATE OF deptno ON dept
  FOR EACH ROW
BEGIN
  UPDATE emp
    SET emp.deptno = :NEW.deptno
    WHERE emp.deptno = :OLD.deptno;
END;
/
UPDATE dept SET deptno=90 WHERE deptno=10;
SELECT * FROM dept;
SELECT ename,deptno FROM emp ORDER BY deptno;
ROLLBACK;
9.47
CONN scott/tiger@orcl
ALTER TABLE dept ADD (flag char);
CONN scott/tiger@cemerp
ALTER TABLE dept ADD (flag char);
CONN scott/tiger@cemerp
CREATE OR REPLACE TRIGGER dept_replication
  BEFORE INSERT OR UPDATE ON dept
  FOR EACH ROW
BEGIN 
  IF INSERTING THEN
     IF :NEW.flag IS NULL THEN
        INSERT INTO
          dept@cemerp_orcl_dblink(deptno,dname,loc,flag)
            VALUES(:new.deptno,:new.dname,:new.loc,'B');
        :NEW.flag := 'A';
     END IF;
  ELSE    --UPDATE
     IF :NEW.flag = :OLD.flag THEN
        UPDATE dept@cemerp_orcl_dblink
          SET deptno=:new.deptno, dname=:new.dname,
              loc=:new.loc, flag=:new.flag
          WHERE deptno = :NEW.deptno;
     END IF;
     IF :OLD.flag = 'A' THEN :NEW.flag := 'B';
     ELSE :NEW.flag := 'A';
     END IF;
  END IF;
END;
/
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER dept_replication
  BEFORE INSERT OR UPDATE ON dept
  FOR EACH ROW
BEGIN 
  IF INSERTING THEN
     IF :NEW.flag IS NULL THEN
        INSERT INTO
          dept@orcl_cemerp_dblink(deptno,dname,loc,flag)
            VALUES(:new.deptno,:new.dname,:new.loc,'B');
        :NEW.flag := 'A';
     END IF;
  ELSE    --UPDATE
     IF :NEW.flag = :OLD.flag THEN
        UPDATE dept@orcl_cemerp_dblink
          SET deptno=:new.deptno, dname=:new.dname,
              loc=:new.loc, flag=:new.flag
          WHERE deptno = :NEW.deptno;
     END IF;
     IF :OLD.flag = 'A' THEN :NEW.flag := 'B';
     ELSE :NEW.flag := 'A';
     END IF;
  END IF;
END;
/
CONN scott/tiger@orcl
INSERT INTO dept(deptno,dname,loc) VALUES(91,'ͬ1','ص1');
CONN scott/tiger@cemerp
SELECT * FROM dept;
 
P240
9.48
CONN sys/syspwd@orcl AS SYSDBA
DROP TRIGGER drop_alter_check;
CONN scott/tiger@orcl
SELECT trigger_name,trigger_type,table_name
  FROM user_triggers
  WHERE table_name='DEPT';

P243
9.49
CONN scott/tiger@orcl
ALTER TABLE emp DROP COLUMN resume;
ALTER TABLE emp DROP COLUMN picture;
ALTER TABLE emp DROP COLUMN video;
ALTER TABLE emp ADD(resume CLOB,picture BLOB,video BFILE);
UPDATE emp SET resume = '2007.7񣺹ְڰͰ', 
               picture = EMPTY_BLOB(),video=NULL
  WHERE empno = 7369;
9.50
CONN scott/tiger@orcl
DECLARE
  lobloc CLOB; 
  text VARCHAR2(32767):=',';
  amount NUMBER ; 
  offset INTEGER; 
BEGIN
  SELECT resume INTO lobloc
    FROM emp
    WHERE empno = 7369 FOR UPDATE;
  offset := DBMS_LOB.GETLENGTH(lobloc) + 1;
  amount := length(text);
  DBMS_LOB.WRITE (lobloc, amount, offset, text );
  text:= ',зչǱ';
  SELECT resume INTO lobloc
    FROM emp
    WHERE empno = 7369 FOR UPDATE;
  amount := length(text);
  DBMS_LOB.WRITEAPPEND(lobloc, amount, text);
  COMMIT;
END;
/
UPDATE emp
  SET resume = '2007.7񣺹ְڰͰ,,зչǱ'
  WHERE empno = 7369;
SELECT resume FROM emp WHERE empno = 7369;

P244
9.51
CONN scott/tiger@orcl
SET serveroutput ON
DECLARE
  rlob clob;
  text VARCHAR2(4001);
  amt number := 4001;
  offset number := 1;
BEGIN
  SELECT SUBSTR (resume, 7, 10)
    INTO text
    FROM emp
    WHERE empno=7369 ;
  DBMS_OUTPUT.PUT_LINE('Substr1:'||text);
  SELECT DBMS_LOB.SUBSTR (resume, 7, 10)
    INTO text
    FROM emp
    WHERE empno=7369 ;
  DBMS_OUTPUT.PUT_LINE('Substr2:'||text);
  SELECT resume INTO rlob
    FROM emp
    WHERE empno = 7369;
  DBMS_LOB.READ(rlob, amt, offset, text);
  DBMS_OUTPUT.PUT_LINE(text);
END;
/
UPDATE emp
  SET resume = EMPTY_CLOB()
  WHERE empno = 7369;

P245
9.52
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE DIRECTORY "pict_dir" AS 'D:\data\emp\pict';
CREATE OR REPLACE DIRECTORY "video_dir" AS 'D:\data\emp\video';
GRANT READ ON DIRECTORY "pict_dir" TO PUBLIC;
GRANT READ ON DIRECTORY "video_dir" TO PUBLIC;
CONN scott/tiger@orcl
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE load_emp_bfile
  (p_file_loc IN VARCHAR2)
IS
  v_file BFILE;
  v_filename VARCHAR2(16);
BEGIN
   v_filename :=  '7369.avi';
   v_file := BFILENAME(p_file_loc, v_filename);
   DBMS_LOB.FILEOPEN(v_file);
   UPDATE emp SET video = v_file
        WHERE empno=7369 ;
   DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||v_filename
       || ' SIZE: '||DBMS_LOB.GETLENGTH(v_file));
   DBMS_LOB.FILECLOSE(v_file);
END load_emp_bfile;
/
EXECUTE load_emp_bfile('video_dir')
UPDATE emp
  SET video = BFILENAME('video_dir', '7369.avi')
  WHERE empno=7369 ;

P247
9.53
CONN scott/tiger@orcl
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE load_emp_pict
  (p_file_loc IN VARCHAR2,p_file_name IN VARCHAR2)
IS
  v_amount INTEGER;
  v_dest_loc BLOB;
  v_src_loc BFILE := BFILENAME(p_file_loc,p_file_name);
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_dest_loc,TRUE);
  DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.OPEN(v_dest_loc,DBMS_LOB.LOB_READWRITE);
  v_amount:=DBMS_LOB.GETLENGTH(v_src_loc);
  DBMS_LOB.LOADFROMFILE(v_dest_loc,v_src_loc,v_amount);
  UPDATE emp SET picture = v_dest_loc
        WHERE empno=7369 ;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('LOADED BLOB: '||v_amount||' Byte');
  DBMS_LOB.CLOSE(v_dest_loc);
  DBMS_LOB.FREETEMPORARY(v_dest_loc);
  DBMS_LOB.CLOSE(v_src_loc);
END load_emp_pict;
/
EXECUTE load_emp_pict('pict_dir','7369.jpg')
SELECT DBMS_LOB.GETLENGTH(picture) FROM emp WHERE empno=7369 ;

P248
CREATE OR REPLACE PROCEDURE compile_obj
IS
  CURSOR obj_cur IS
    SELECT object_type, object_name
    FROM user_objects
    WHERE status = 'INVALID' AND object_type IN 
      ('PROCEDURE', 'FUNCTION','PACKAGE','PACKAGE BODY')
    ORDER BY object_type;
BEGIN
  FOR obj_rec IN obj_cur LOOP
    DBMS_DDL.ALTER_COMPILE
      (obj_rec.object_type, user,obj_rec.object_name);
  END LOOP;
END compile_obj;
/
EXECUTE compile_obj

P250
SELECT sys_context('userenv','AUTHENTICATION_TYPE') FROM dual;
SELECT sys_context('userenv','CLIENT_INFO') FROM dual;
SELECT sys_context('userenv','CURRENT_SCHEMA') FROM dual;
SELECT sys_context('userenv','DB_DOMAIN') FROM dual;
SELECT sys_context('userenv','DB_NAME') FROM dual;
SELECT sys_context('userenv','ENTRYID') FROM dual;
SELECT sys_context('userenv','HOST') FROM dual;
SELECT sys_context('userenv','ISDBA') FROM dual;
SELECT sys_context('userenv','LANG') FROM dual;
SELECT sys_context('userenv','LANGUAGE') FROM dual;
SELECT sys_context('userenv','NETWORK_PROTOCOL') FROM dual;
SELECT sys_context('userenv','NLS_DATE_FORMAT') FROM dual;
SELECT sys_context('userenv','OS_USER') FROM dual;
SELECT sys_context('userenv','SESSIONID') FROM dual;
SELECT sys_context('userenv','SESSION_USER') FROM dual;
SELECT sys_context('userenv','TERMINAL') FROM dual;

P251
CONN sys/syspwd@orcl AS SYSDBA
CREATE USER emp_mgr10 IDENTIFIED BY emp_mgr10pwd;
GRANT CONNECT,RESOURCE TO emp_mgr10;
GRANT EXECUTE ON dbms_rls TO hr;
GRANT EXECUTE ON dbms_rls TO scott;
GRANT EXECUTE ON dbms_rls TO emp_mgr10;
CONN scott/tiger@orcl
GRANT ALL ON emp TO hr;
GRANT ALL ON emp TO system;
GRANT ALL ON emp TO emp_mgr10;
9.54
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE CONTEXT empenv USING scott.emp_app_ctx;
CREATE OR REPLACE PACKAGE scott.emp_app_ctx AS
  PROCEDURE set_deptno;
END;
/
CREATE OR REPLACE PACKAGE BODY scott.emp_app_ctx AS
  PROCEDURE set_deptno IS
  BEGIN
    IF sys_context('userenv','session_user')='EMP_MGR10' THEN
      DBMS_SESSION.SET_CONTEXT('empenv','mgr10_attr1',10);
    ELSIF sys_context('userenv','session_user')='HR' THEN
      DBMS_SESSION.SET_CONTEXT('empenv','hr_attr1',10);
      DBMS_SESSION.SET_CONTEXT('empenv','hr_attr2',20);
      DBMS_SESSION.SET_CONTEXT('empenv','hr_attr3','%');
    ELSIF sys_context('userenv','session_user')='SYSTEM' THEN
      DBMS_SESSION.SET_CONTEXT('empenv','system_attr1','%');
    END IF;
  END;
END;
/

P252
9.55
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE PACKAGE scott.emp_security AS
  FUNCTION select_lmt(object_schema VARCHAR2,object_name VARCHAR2)
    RETURN VARCHAR2;
  FUNCTION dml_lmt(object_schema VARCHAR2,object_name VARCHAR2)
    RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY scott.emp_security AS
FUNCTION select_lmt(object_schema VARCHAR2,object_name VARCHAR2)
  RETURN VARCHAR2
IS
  rtn_predicate VARCHAR2(500);
BEGIN
  rtn_predicate:='1=1';
  IF user='EMP_MGR10' THEN
    rtn_predicate:='deptno=sys_context(''empenv'',''mgr10_attr1'')';
  ELSIF user='HR' THEN
    rtn_predicate:='deptno LIKE sys_context(''empenv'',''hr_attr3'')';
  ELSIF user='SYSTEM' THEN
    rtn_predicate:='deptno LIKE sys_context(''empenv'',''system_attr1'')';
  END IF;
  RETURN rtn_predicate;
END;
FUNCTION dml_lmt(object_schema VARCHAR2,object_name VARCHAR2)
  RETURN VARCHAR2 
IS
  rtn_predicate VARCHAR2(500);
BEGIN
  rtn_predicate:='1=2';
  IF user='EMP_MGR10' THEN
    rtn_predicate:='deptno=sys_context(''empenv'',''mgr10_attr1'')';
  ELSIF user='HR' THEN
    rtn_predicate:='deptno=sys_context(''empenv'',''hr_attr1'')'
    ||' OR '
    ||'deptno=sys_context(''empenv'',''hr_attr2'')';
  ELSIF user='SYSTEM' THEN
    rtn_predicate:='deptno LIKE sys_context(''empenv'',''system_attr1'')';
  END IF;
  RETURN rtn_predicate;
END;
END;
/

P253
9.56
CONN sys/syspwd@orcl AS SYSDBA
EXECUTE DBMS_RLS.ADD_POLICY(-
object_schema=>'scott',-
object_name=>'emp',-
policy_name=>'select_policy',-
function_schema=>'scott',-
policy_function=>'emp_security.select_lmt',-
statement_types=>'select');
EXECUTE DBMS_RLS.ADD_POLICY(-
object_schema=>'scott',-
object_name=>'emp',-
policy_name=>'dml_policy',-
function_schema=>'scott',-
policy_function=>'emp_security.dml_lmt',-
statement_types=>'insert,update,delete');
CONN sys/syspwd@orcl AS SYSDBA
EXECUTE DBMS_RLS.ADD_POLICY(object_schema=>'scott',object_name=>'emp',policy_name=>'select_policy',function_schema=>'scott',policy_function=>'emp_security.select_lmt',statement_types=>'select');
EXECUTE DBMS_RLS.ADD_POLICY(object_schema=>'scott',object_name=>'emp',policy_name=>'update_policy',function_schema=>'scott',policy_function=>'emp_security.dml_lmt',statement_types=>'insert,update,delete');

P254
9.57
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE TRIGGER logon_trigger
  AFTER LOGON ON DATABASE CALL scott.emp_app_ctx.set_deptno
/
CONN scott/tiger@orcl
SELECT count(*) FROM emp;
DELETE FROM emp;
ROLLBACK;
CONN emp_mgr10/emp_mgr10pwd@orcl
SELECT count(*) FROM scott.emp;
DELETE FROM scott.emp;
ROLLBACK;
CONN hr/hrpwd@orcl
SELECT count(*) FROM scott.emp;
DELETE FROM scott.emp;
ROLLBACK;
CONN system/systempwd@orcl
SELECT count(*) FROM scott.emp;
DELETE FROM scott.emp;
ROLLBACK;
9.58
CONN sys/syspwd@orcl AS SYSDBA
BEGIN
  DBMS_RLS.DROP_POLICY(
    object_schema=>'scott',
    object_name=>'emp',
    policy_name=>'dml_policy');
END;
/
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema=>'scott',
    object_name=>'emp',
    policy_name=>'dml_policy',
    function_schema=>'scott',
    policy_function=>'emp_security.dml_lmt',
    statement_types=>'insert,update,delete',
    sec_relevant_cols=>'sal');
END;
/
SELECT empno,ename,sal,comm,deptno
  FROM scott.emp
  WHERE empno IN(7934,7369,7900)
  ORDER BY deptno;
CONN emp_mgr10/emp_mgr10pwd@orcl
SELECT empno,ename,sal,comm,deptno
  FROM scott.emp
   WHERE empno IN(7369,7900);
UPDATE scott.emp SET sal=10 WHERE empno=7369;
UPDATE scott.emp SET comm=20 WHERE empno=7369;
UPDATE scott.emp SET sal=30 WHERE empno=7934;
UPDATE scott.emp SET comm=40 WHERE empno=7934;
ROLLBACK;

P256
CONN scott/tiger@orcl
COL OBJECT_NAME FORMAT A11
COL POLICY_NAME FORMAT A13
COL PACKAGE FORMAT A12
COL FUNCTION FORMAT A12
COL SEL FORMAT A3
COL INS FORMAT A3
COL UPD FORMAT A3
COL DEL FORMAT A3
COL ENABLE FORMAT A6
COL POLICY_TYPE FORMAT A11
SELECT object_name,policy_name,package,function,
       sel,ins,upd,del,enable,policy_type
  FROM user_policies;

