第 7章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例7.1 
DECLARE
  n number;         
  sal number(8,3);     
  sname varchar2(20);  
BEGIN
  sname:='zhangsan' ;    
  n:=10;
END;

例7.2 
conn scott/tiger@oradb
SET serveroutput ON
DECLARE
  v_dept emp.deptno%TYPE; 
  str varchar2(60);
  str1 str%TYPE;  
BEGIN
  str:='I am a Oracle starter, and you?';
  dbms_output.put_line(str);
END;
/

例7.3 
conn scott/tiger@oradb 
SET serveroutput ON
DECLARE
  TYPE my_rec IS RECORD (
    id number(3),
    name varchar2(20)
  );
  worker my_rec;
BEGIN
  worker.id:=10;
  worker.name:='Jack';
  dbms_output.put_line(worker.id||':'||worker.name);
END;

例7.4 
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));
END;
/

例7.5 
conn scott/tiger@oradb
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;
/

例7.6 
conn scott/tiger@oradb
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;
/

例7.7 
conn scott/tiger@oradb
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;
/

例7.8
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;
/

例7.9 
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;
/

例7.10 
conn hr/hr@oradb
DECLARE
  CURSOR Cur_Employees(p_dept number) IS 
    SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
    FROM employees
    WHERE employee_id > p_dept;
    emp_rec Cur_Employees%ROWTYPE;
BEGIN
  DELETE FROM sal_history;
  DELETE FROM mgr_history;
  COMMIT;
  OPEN Cur_Employees(100);
  LOOP
    FETCH Cur_Employees INTO emp_rec;
    EXIT WHEN Cur_Employees%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 Cur_Employees;
END;
/
SELECT * FROM sal_history;
SELECT * FROM mgr_history;

例7.11
DECLARE
  v_bonus NUMBER;
  CURSOR C1 IS SELECT ename,sal,comm FROM emp;
BEGIN
  DELETE FROM bonus;
  FOR  C1_REC  IN  C1  LOOP
    v_bonus:= (C1_REC.sal*0.05)+(C1_REC.comm*0.25);
    INSERT INTO BONUS(ename,sal,comm) VALUES(C1_REC.ename,C1_REC.sal,v_bonus);
  END LOOP;
  COMMIT;
END;
/

例7.12 
DECLARE
   my_sal NUMBER(7,2);
   num INTEGER(2) := 2;
   CURSOR emp_cur IS SELECT num*sal FROM emp FOR UPDATE;
BEGIN
   OPEN emp_cur; 
   LOOP
     FETCH emp_cur INTO my_sal;
     EXIT WHEN emp_cur%NOTFOUND;
     UPDATE emp SET sal=sal*1.2 WHERE CURRENT OF emp_cur;
   END LOOP;
   CLOSE emp_cur;
   COMMIT;
END;
/

例7.13
DROP TABLE test_msg;
CREATE TABLE test_msg(
  empno number(4),
  message varchar2(20));
SET serveroutput ON
DECLARE
  ret number;
BEGIN
  SELECT sal INTO ret FROM emp WHERE empno=888;
  commit;
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;
/
SELECT * FROM test_msg;

例7.14 
SET serveroutput ON
DECLARE
  ret number;
  except_1 EXCEPTION;
BEGIN
  SELECT sal INTO ret FROM emp WHERE empno=7788;
  dbms_output.put_line('7788的工资为' || ret );
  IF ret>2000 THEN
     RAISE except_1;
  END IF;
EXCEPTION
  WHEN except_1 THEN
    dbms_output.put_line('发生用户自定义例外except_1');
    INSERT INTO test_msg(empno,message) VALUES(7788,'挣的还可以');
    commit;
  WHEN OTHERS THEN
    ROLLBACK WORK;
END;
/
SELECT * FROM test_msg;

例7.15 
conn scott/tiger@oradb
DROP TABLE tab08;
CREATE TABLE tab08(
  name varchar2(20),
  age number(3));
DROP TABLE tab08_2;
CREATE TABLE tab08_2(
  name varchar2(20),
  score number(3));
CREATE OR REPLACE TRIGGER tab08ins08_2 
   AFTER INSERT ON tab08
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW WHEN (new.age>26)
BEGIN
  INSERT INTO tab08_2(name,score) VALUES(:new.name,90);
END;
/
INSERT INTO tab08(name,age) VALUES('Zhangsan',20);
INSERT INTO tab08(name,age) VALUES('Breaker',27);
commit;
SELECT * FROM tab08;

例7.16
CREATE or REPLACE PROCEDURE test_proc IS
 i number;
BEGIN
  i:=to_number(to_char(sysdate,'YYYYMMDDHH24MISS'));
  i:=mod(i,100);
  update tab08_2 SET score=i;
END;
/

例7.17
CREATE or REPLACE FUNCTION test_func return VARCHAR2 AS
  i number;
BEGIN
  return to_char(sysdate,'YYYY-MM-DD HH24')||':'||to_char(sysdate,'MI')||':'||to_char(sysdate,'SS');
END;
/
SELECT '当前时间：'||test_func "what time" FROM dual;

例7.18 
CREATE or REPLACE FUNCTION conv_numeric(n number) RETURN varchar2 IS
  ret varchar2(40):=to_char(round(n,0),'000000');
  str varchar2(40);
  result varchar2(40):='';
BEGIN
  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_numeric(123456.7) FROM dual;

例7.19 
CREATE or REPLACE PACKAGE my_package1 IS
  FUNCTION test_func return VARCHAR2;
  PROCEDURE test_proc;
END my_package1;
/
CREATE or REPLACE PACKAGE BODY my_package1 IS
  FUNCTION test_func return VARCHAR2 AS
    i number;
  BEGIN
    return to_char(sysdate,'YYYY-MM-DD HH24')||':'||to_char(sysdate,'MI')||':'||to_char(sysdate,'SS');
  END;
  PROCEDURE test_proc IS
    i number;
  BEGIN
    i:=to_number(to_char(sysdate,'YYYYMMDDHH24MISS'));
    i:=mod(i,100);
    update tab08_2 SET score=i;
  END;
END my_package1;
/

例7.20 
SELECT my_package1.test_func FROM dual;
execute my_package1.test_proc;
SELECT * FROM tab08_2;

例7.21 
connect scott/tiger@oradb
SET serveroutput ON
DECLARE
  CURSOR emp_rec IS SELECT ename,nvl(sal,0)+nvl(comm,0) money FROM emp;
  rec1 emp_rec%ROWTYPE;
BEGIN
  FOR rec1 IN emp_rec LOOP
    dbms_output.put_line(rpad(rec1.ename,10,' ')||''||rec1.money);
  END LOOP;
END;
/

例7.22
SET serveroutput ON
DECLARE
  f1 UTL_FILE.FILE_TYPE;
  str varchar2(60);
BEGIN
  f1:=UTL_FILE.fopen('d:\data','testfile.txt','r');
  UTL_FILE.get_line(f1,str,40);
  dbms_output.put_line(str);
  UTL_FILE.get_line(f1,str,40);
  dbms_output.put_line(str);
  UTL_FILE.fclose(f1);
END; 
/