110---oracle的pl/sql例子

1.      数据类型

DECLARE

  v_empno employee.empno%TYPE :=7788;

  v_rec employee%ROWTYPE;

BEGIN

  SELECT * INTO v_rec FROM employee WHERE empno=v_empno;

  DBMS_OUTPUT.PUT_LINE

           ('姓名:'||v_rec.ename||'工资:'||v_rec.sal||'工作时间:'||v_rec.hiredate);

END;

 

 

2.      显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值。

DECLARE

   v_counter NUMBER := 5;

BEGIN

      DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:'||v_counter);

      IF v_counter >= 10 THEN

         NULL; --为了使语法变得有意义,去掉NULL会报语法错误

      ELSE

               v_counter := v_counter + 10;

                   DBMS_OUTPUT.PUT_LINE('v_counter的改变后值为:'||v_counter);

      END IF;

 END;

 

3.      预定义异常

DECLARE

  v_ename employee.ename%TYPE;

BEGIN

  SELECT ename INTO v_ename

  FROM employee

  WHERE empno=1234;

  dbms_output.put_line('雇员名:'||v_ename);

EXCEPTION

  WHEN NO_DATA_FOUND THEN

     dbms_output.put_line('雇员号不正确');  

  WHEN TOO_MANY_ROWS THEN

     dbms_output.put_line('查询只能返回单行');

  WHEN OTHERS THEN

     dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);

END;

 

4.      查询编号为7788的雇员的福利补助（comm列）。

DECLARE

       v_comm employee.comm%TYPE;

        e_comm_is_nullEXCEPTION; --定义异常类型变量

BEGIN

        SELECTcomm INTO v_comm FROM employee WHERE empno=7788;

        IFv_comm IS NULL THEN

           RAISEe_comm_is_null;

        ENDIF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);

   WHEN e_comm_is_null THEN

        dbms_output.put_line('该雇员无补助');

   WHEN others THEN

        dbms_output.put_line('出现其他异常');

END;

 

5.      使用显式游标输出每个员工的姓名和薪水。

DECLARE

 nameemployee.ename%type;

 salemployee.sal%type; --定义两个变量来存放ename和sal的内容

 CURSOR emp_cursor IS

   SELECT ename,sal

     FROM employee;

BEGIN

OPEN emp_cursor;

LOOP

  FETCH emp_cursor INTO name,sal;

  EXIT WHEN emp_cursor%NOTFOUND;

  DBMS_OUTPUT.PUT_LINE

     ('第'||emp_cursor%ROWCOUNT||'个雇员:'||name||sal);

END LOOP;

CLOSE emp_cursor;

END;

 

6.      循环游标的用法。

--显示雇员表中所有雇员的姓名和薪水

DECLARE

        CURSORemp_cursor IS

           SELECT ename,sal FROM employee;

BEGIN

        FORemp_record IN emp_cursor LOOP

           DBMS_OUTPUT.PUT_LINE

         ('第'||emp_cursor%ROWCOUNT||'个雇员：'

          ||emp_record.ename|| emp_record.sal);

        ENDLOOP;

END;

7.      多表查询更新时，更新表为锁定行所在表。

DECLARE

 CURSOR emp_cursor IS

   SELECT ename,sal

     FROM employee e INNER join dept d

               ON e.deptno=d.deptno

      FOR UPDATE OF sal;

 v_emp emp_cursor%ROWTYPE;

BEGIN

   IFNOT emp_cursor%ISOPEN THEN

     OPEN emp_cursor;     

  END IF;

LOOP

  FETCH emp_cursor INTO v_emp;

  EXIT WHEN emp_cursor%NOTFOUND;

  UPDATE employee

     SET sal=sal+200

                           WHERE CURRENT OF emp_cursor;

 

END LOOP;

CLOSE emp_cursor;

END;

 

8.      添加员工记录。

CREATE OR REPLACE PROCEDURE add_employee(

   eno NUMBER,                                                                   --输入参数,雇员编号

   name VARCHAR2,                                                               --输入参数,雇员名称

   salary NUMBER,                                                                 --输入参数,雇员薪水

   job VARCHAR2 DEFAULT 'CLERK',                             --输入参数,雇员工种默认'CLERK'

   dno NUMBER                                                            --输入参数,雇员部门编号

)

IS

BEGIN

  INSERT INTO employee

     (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);

END;

 

9.      sql*plus下调用存储过程

--EXEC add_employee(1111,'MARY',2000,'MANAGER',10);

--EXECadd_employee(dno=>10,name=>'MARY',salary=>2000,eno=>1112,job=>'MANAGER');

--EXECadd_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

--EXECadd_employee(1114,dno=>10,name=>'MARY',salary=>2000);

 

 

10. PL/SQL下调用存储过程

BEGIN

   --按位置传递参数

   add_employee(2111,'MARY',2000,'MANAGER',10);

   --按名字传递参数

  add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112,job=>'MANAGER');

   --混合方法传递参数

  add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

   --默认值法

  add_employee(4111,dno=>10,name=>'MARY',salary=>2000);

END;

 

 

11. 将示例8按照推荐规则修改。

 

CREATE OR REPLACE PROCEDURE add_employee(

   eno employee.empno%type,                 --输入参数,雇员编号

   name employee.ename%type,               --输入参数,雇员名称

   salary employee.sal%type,               --输入参数,雇员薪水

   job employee.job%type DEFAULT 'CLERK',        --输入参数,雇员工种默认'CLERK'

   dno employee.deptno%type,                --输入参数,雇员部门编号

   on_Flag OUT number,       --执行状态

   os_Msg OUT VARCHAR2       --提示信息

)

IS

BEGIN

  INSERT INTO employee (empno,ename,sal,job,deptno)VALUES(eno,name,salary,job, dno);

  on_Flag:=1;

  os_Msg:='添加成功';

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN

     on_Flag:=-1;

     os_Msg:='该雇员已存在。';

  WHEN OTHERS THEN

     on_Flag:=SQLCODE;

      os_Msg:=SQLERRM;    

END;

 

DECLARE

  on_Flag NUMBER;

  os_Msg VARCHAR2(100);    

BEGIN

   --按位置传递参数

  add_employee(2111,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);

  dbms_output.put_line(on_Flag||os_Msg);

END;

12. 示例1：给变量和常量声明赋值

   

 

/*

===========================================================

|       给变量和常量声明赋值

============================================================

*/

DECLARE

    v_ename VARCHAR2(20);

    v_rate NUMBER(7,2);

    c_rate_incr CONSTANT NUMBER(7,2):=1.10;

BEGIN

     --方法一：通过SELECT INTO给变量赋值

    SELECT ename, sal* c_rate_incr INTO   v_ename, v_rate

     FROM   employee

    WHERE empno='7788';

   --方法二：通过赋值操作符“:=”给变量赋值

   v_ename:='SCOTT';

END;

13. 练习1：PL／SQL基础知识综合练习

   

 

DECLARE

  v_tax_rate NUMBER(2);

  v_sal_d NUMBER(5);

  v_tax NUMBER(10,2);

  v_sal NUMBER(10,2);

  c_sal_start CONSTANT NUMBER(10):=3500;

BEGIN

  SELECT sal

    INTO v_sal

    FROM employee

  WHERE ename='KING';

  v_tax:=0;

   IFv_sal>=3500 THEN

    v_sal:=v_sal-c_sal_start;

    CASE

    WHEN v_sal<=1500 THEN

         v_tax_rate:=3;

         v_sal_d:=0;

    WHEN v_sal<=4500 THEN

         v_tax_rate:=10;

         v_sal_d:=105;  

    WHEN v_sal<=9000 THEN

         v_tax_rate:=20;

         v_sal_d:=555;

    ELSE

         v_tax_rate:=25;

         v_sal_d:=1005;

    END CASE;

    v_tax:=v_sal*v_tax_rate/100-v_sal_d;

  END IF;

  dbms_output.put_line(v_tax);

END;

 

 

DECLARE

  v_bonus NUMBER(10,2);

  v_hiredate employee.hiredate%TYPE;

BEGIN

  SELECT hiredate

    INTO v_hiredate

    FROM employee

  WHERE ename='SCOTT';

   IFsysdate-v_hiredate>=365*6 THEN

      v_bonus:=2000;

  ELSE

      v_bonus:=1500;

  END IF;

  UPDATE employee SET comm=v_bonus

   WHERE ename='SCOTT';

END;

 

 

DECLARE

  v_grade  NUMBER(2);

  v_dname dept.dname%TYPE;

  v_sal employee.sal%TYPE;

BEGIN

  SELECT sal,dname

    INTO v_sal,v_dname

    FROM employee e INNER join dept d

      ON  e.deptno=d.deptno

   WHERE ename='SCOTT';

  CASE

  WHEN v_sal<=3200 AND v_sal>=700 THEN

     v_grade:=1;

  WHEN v_sal<=4400 THEN

     v_grade:=2;

  WHEN v_sal<=5000 THEN

     v_grade:=3;

  WHEN v_sal<=7000 THEN

     v_grade:=4;

  WHEN v_sal<=9999 THEN

     v_grade:=5;

  ELSE

     v_grade:=0;

  END CASE;

   IFv_grade=0 THEN

     dbms_output.put_line('SCOTT所在部门：'||v_dname||' 薪水：'||v_sal||'工资无级别');

  ELSE

     dbms_output.put_line('SCOTT所在部门：'||v_dname||' 薪水：'||v_sal||'工资级别:'||v_grade);     

  END IF;

END;

 

--为员工SCOTT增加工资，每次增加100元，直到10000元停止。

 

DECLARE

  v_hight employee.sal%TYPE:=10000;

  v_sal employee.sal%TYPE;

BEGIN

  SELECT sal

    INTO v_sal

    FROM employee

   WHERE ename='SCOTT';

  LOOP

  EXIT WHEN v_sal+100>v_hight;  

     UPDATE employee

        SET sal=sal+100

      WHERE ename='SCOTT' ;

      v_sal:=v_sal+100;

  END LOOP;

END;

 

SELECT * FROM employee;

 

 

 

14. 练习2：使用预定义异常完善员工查询信息功能

   

 

DECLARE

  v_empno VARCHAR2(10):=7788;

  v_ename VARCHAR2(4);

BEGIN

 SELECT ename INTO v_ename FROM employee WHERE empno=v_empno;

 dbms_output.put_line(v_ename);

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);

 WHEN VALUE_ERROR THEN

    dbms_output.put_line('名称长度超过变量长度');

 WHEN others THEN

    dbms_output.put_line('出现其他异常');

END;

 

15. 练习3：使用用户自定义异常完善员工调整工资业务需求

   

 

 

 

DECLARE

       v_sal employee.sal%TYPE;

       v_deptno employee.deptno%TYPE;

        e_comm_is_nullEXCEPTION; --定义异常类型变量

BEGIN

        SELECTsal,deptno INTO v_sal,v_deptno

   FROM employee

  WHERE empno=7788;

  IF  v_deptno=20 THEN

     IF v_sal<10000 THEN

        UPDATE employee

        SET sal=10000

        WHERE empno=7788;

     ELSE

               RAISE e_comm_is_null;

     END IF;

         END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

      dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);

         WHEN e_comm_is_null THEN

           dbms_output.put_line('工资不低于10000元');

         WHEN others THEN

           dbms_output.put_line('出现其他异常');

END;

 

 

 

16. 练习4：使用显式游标技术完善公司业务需求

   

DECLARE

  v_tax_rate NUMBER(2);

  v_sal_d NUMBER(5);

  v_tax NUMBER(10,2);

  v_sal NUMBER(10,2);

  v_sum NUMBER(10,2):=0;

  c_sal_start CONSTANT NUMBER(10):=3500;

  CURSOR emp_cursor IS

     SELECT sal

       FROM employee;

BEGIN

 OPEN emp_cursor;

 LOOP

    FETCH emp_cursor INTO v_sal;

    EXIT WHEN emp_cursor%NOTFOUND;  

    v_tax:=0;

    IF v_sal>=3500 THEN

       v_sal:=v_sal-c_sal_start;

       CASE

       WHEN v_sal<=1500 THEN

         v_tax_rate:=3;

         v_sal_d:=0;

       WHEN v_sal<=4500 THEN

         v_tax_rate:=10;

         v_sal_d:=105;  

       WHEN v_sal<=9000 THEN

         v_tax_rate:=20;

         v_sal_d:=555;

       ELSE

         v_tax_rate:=25;

         v_sal_d:=1005;

       END CASE;

       v_tax:=v_sal*v_tax_rate/100-v_sal_d;

     END IF;

      v_sum:=v_sal+v_tax;

  END LOOP;

  CLOSE emp_cursor;

  dbms_output.put_line(v_sum);

END;

 

 

DECLARE

  v_bonus NUMBER(10,2);

  CURSOR emp_cursor IS

     SELECT hiredate

       FROM employee

       FOR UPDATE;

BEGIN

  FOR cur1 IN emp_cursor LOOP

     IF sysdate-cur1.hiredate>=365*6 THEN

        v_bonus:=2000;

     ELSE

        v_bonus:=1500;

     END IF;

     UPDATE employee SET comm=v_bonus

      WHERE CURRENT OF emp_cursor;

  END LOOP;

END;

 

 

DECLARE

  v_grade  NUMBER(2);

  CURSOR emp_cursor IS

     SELECT ename,sal,dname

       FROM employee e INNER join dept d

         ON  e.deptno=d.deptno

      WHERE dname='SALES';

BEGIN

  FOR cur1 IN emp_cursor LOOP   

     CASE

     WHEN cur1.sal<=3200 AND cur1.sal>=700 THEN

        v_grade:=1;

     WHEN cur1.sal<=4400 THEN

        v_grade:=2;

     WHEN cur1.sal<=5000 THEN

        v_grade:=3;

     WHEN cur1.sal<=7000 THEN

        v_grade:=4;

     WHEN cur1.sal<=9999 THEN

        v_grade:=5;

     ELSE

        v_grade:=0;

     END CASE;

     IF v_grade=0 THEN

        dbms_output.put_line(cur1.ename||'所在部门：'||cur1.dname||'薪水：'||cur1.sal||' 工资无级别');

     ELSE

        dbms_output.put_line(cur1.ename||'所在部门：'||cur1.dname||'薪水：'||cur1.sal||' 工资级别:'||v_grade);      

     END IF;

  END LOOP;

END;

 

 

 

 

 

17. 练习5：使用存储过程实现业务需求

   

 

CREATE OR REPLACE PROCEDURE fire_employee(

   eno employee.empno%type,                 --输入参数,雇员编号

   on_Flag OUT number,       --执行状态

   os_Msg OUT VARCHAR2       --提示信息

)

IS

   e1EXCEPTION; --定义异常类型变量

BEGIN

  DELETE

    FROM employee

   WHERE empno=eno;

   IFSQL%NOTFOUND THEN

    RAISE e1;

  ELSE

     on_Flag:=1;

     os_Msg:='成功';

  END IF;

EXCEPTION

  WHEN e1 THEN

     on_Flag:=-1;

     os_Msg:='该雇员不存在。';

  WHEN OTHERS THEN

     on_Flag:=SQLCODE;

     os_Msg:=SQLERRM;

END;

 

DECLARE

  v_no employee.empno%TYPE;

  on_Flag number(1);     --执行状态

  os_Msg VARCHAR2(200);       --提示信息 

BEGIN

  v_no:=7788;

  fire_employee(v_no,on_flag,os_Msg);

  dbms_output.put_line(on_flag);

  dbms_output.put_line(os_Msg);

END;

 

 

--调用get_sals存储过程,显示员工薪水

 

 

CREATE OR REPLACE PROCEDURE get_sals(

  cur_salary OUT SYS_REFCURSOR,

  on_Flag OUT number,       --执行状态

  os_Msg OUT VARCHAR2       --提示信息

)

AS

BEGIN

   OPEN cur_salary FOR

      SELECT empno,sal FROM employee;

  on_Flag:=1;

  os_Msg:='成功';      

EXCEPTION

  WHEN OTHERS THEN

     on_Flag:=-1;

     os_Msg:='其他错误，与管理员联系。';

END;

 

DECLARE

  v_empno  employee.empno%type;

  v_sal employee.sal%type;

  emp_salary SYS_REFCURSOR;

  on_Flag number(1);     --执行状态

  os_Msg VARCHAR2(200);       --提示信息   

BEGIN

  get_sals(emp_salary,on_Flag,os_Msg);

   IFon_flag=1 THEN

     LOOP

        FETCH emp_salary INTO v_empno, v_sal;

        EXIT WHEN emp_salary%notfound;

        DBMS_OUTPUT.PUT_LINE(v_empno||'的薪水是' ||v_sal);

     END LOOP;

  ELSE

     dbms_output.put_line(os_Msg);

  END IF;

   IFemp_salary%ISOPEN THEN

     CLOSE emp_salary;

  END IF;

END;