SQL> select * from t; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980/12/17 800 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30 7521 WARD SALESMAN 7698 1981/2/22 1250 500 30 7566 JONES MANAGER 7839 1981/4/2 2975 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30 7698 BLAKE MANAGER 7839 1981/5/1 2850 30 7782 CLARK MANAGER 7839 1981/6/9 2450 10 7788 SCOTT ANALYST 7566 1982/12/9 3000 20 7839 KING PRESIDENT 1981/11/17 5000 10 7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30 7876 ADAMS CLERK 7788 1983/1/12 1100 20 7900 JAMES CLERK 7698 1981/12/3 950 30 7902 FORD ANALYST 7566 1981/12/3 3000 20 7934 MILLER CLERK 7782 1982/1/23 1300 10 14 rows selected
总结： ①如果使用case when如果 case 发现when不存在判断条件时会触发异常。 if判断时不会出现这样的情况。以下进行了测试， 在进行if测试时，将不是条件中三种职业的sal值为空，原因是在调用函数时，l_result返回为空，1+null还是null,则sal就为空。所以最后调整程序 nvl处理了一下空值，经过测试，数据没有问题。 ② where current of cur_t，定义游标时使用for update. 测试： 创建函数： create or replace function call_func(l_job varchar2) return number as l_result number; begin case when l_job = 'CLERK' then l_result := 0.2; when l_job = 'SALESMAN' then l_result := 0.3; when l_job = 'MANAGER' then l_result := 0.35; end case; return l_result; end call_func;
declare l_empno number; l_ename varchar2(20); l_job varchar2(20); l_ratio number; cursor cur_t is select empno, ename, job from t for update; begin open cur_t; loop fetch cur_t into l_empno, l_ename, l_job; exit when cur_t%notfound; l_ratio := call_func(l_job); update t set sal =sal* (1 + nvl(l_ratio,0)) where current of cur_t; dbms_output.put_line(l_empno || l_ename || '已经成功加薪'); commit; end loop; close cur_t; exception when others then dbms_output.put_line(sqlerrm); end; 执行结果： 27 / 7369SMITH已经成功加薪 7499ALLEN已经成功加薪 7521WARD已经成功加薪 7566JONES已经成功加薪 7654MARTIN已经成功加薪 7698BLAKE已经成功加薪 7782CLARK已经成功加薪 7788SCOTT已经成功加薪 ORA-06592: 执行 CASE 语句时未找到 CASE PL/SQL procedure successfully completed
SQL> select * from t; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980/12/17 960 20 7499 ALLEN SALESMAN 7698 1981/2/20 2080 300 30 7521 WARD SALESMAN 7698 1981/2/22 1625 500 30 7566 JONES MANAGER 7839 1981/4/2 4016.25 20 7654 MARTIN SALESMAN 7698 1981/9/28 1625 1400 30 7698 BLAKE MANAGER 7839 1981/5/1 3847.5 30 7782 CLARK MANAGER 7839 1981/6/9 3307.5 10 7788 SCOTT ANALYST 7566 1982/12/9 3000 20 7839 KING PRESIDENT 1981/11/17 5000 10 7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30 7876 ADAMS CLERK 7788 1983/1/12 1100 20 7900 JAMES CLERK 7698 1981/12/3 950 30 7902 FORD ANALYST 7566 1981/12/3 3000 20 7934 MILLER CLERK 7782 1982/1/23 1300 10 14 rows selected
使用 if 语句判断 SQL> create or replace function call_func(l_job varchar2) return number as 2 l_result number; 3 begin 4 if l_job = 'CLERK' then 5 l_result := 0.2; 6 elsif l_job = 'SALESMAN' then 7 l_result := 0.3; 8 elsif l_job = 'MANAGER' then 9 l_result := 0.35; 10 end if; 11 return l_result; 12 end call_func; 13 / Function created 执行结果如下：通过以下执行结果，将不是条件中三种职业的sal值为空，原因是在调用函数时，l_result返回为空，1+null还是null,则sal就为空。所以最后调整程序 nvl处理了一下空值
SQL> select * from t; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980/12/17 960 20 7499 ALLEN SALESMAN 7698 1981/2/20 2080 300 30 7521 WARD SALESMAN 7698 1981/2/22 1625 500 30 7566 JONES MANAGER 7839 1981/4/2 4016.25 20 7654 MARTIN SALESMAN 7698 1981/9/28 1625 1400 30 7698 BLAKE MANAGER 7839 1981/5/1 3847.5 30 7782 CLARK MANAGER 7839 1981/6/9 3307.5 10 7788 SCOTT ANALYST 7566 1982/12/9 20 7839 KING PRESIDENT 1981/11/17 10 7844 TURNER SALESMAN 7698 1981/9/8 1950 0 30 7876 ADAMS CLERK 7788 1983/1/12 1320 20 7900 JAMES CLERK 7698 1981/12/3 1140 30 7902 FORD ANALYST 7566 1981/12/3 20 7934 MILLER CLERK 7782 1982/1/23 1560 10 14 rows selected
create or replace procedure p_test as --定义一个游标获取值 cursor cur_t is select empno, job, sal from t; --定义一个记录类型 type rec_emp_type is record( l_empno t.empno%type, l_job t.job%type, l_sal t.sal%type); rec_emp rec_emp_type; begin ---打开游标 open cur_t; --exit/continue语句必须出现在一个循环里 loop fetch cur_t into rec_emp; exit when cur_t%notfound; ---if判断 if rec_emp.l_job = 'CLERK' then update t set sal = sal + 500 where t.empno = rec_emp.l_empno; commit; elsif rec_emp.l_job = 'ANALYST' then update t set sal = sal + 100 where t.empno = rec_emp.l_empno; commit; elsif rec_emp.l_job = 'MANAGER' then update t set sal = sal * (1 + 0.08) where t.empno = rec_emp.l_empno; commit; else update t set sal = sal * (1 + 0.06) where t.empno = rec_emp.l_empno; commit; ---关闭游标 end if; end loop; close cur_t; end; 结果：
SQL> select * from t; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980/12/17 800 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30 7521 WARD SALESMAN 7698 1981/2/22 1250 500 30 7566 JONES MANAGER 7839 1981/4/2 2975 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30 7698 BLAKE MANAGER 7839 1981/5/1 2850 30 7782 CLARK MANAGER 7839 1981/6/9 2450 10 7788 SCOTT ANALYST 7566 1982/12/9 3000 20 7839 KING PRESIDENT 1981/11/17 5000 10 7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30 7876 ADAMS CLERK 7788 1983/1/12 1100 20 7900 JAMES CLERK 7698 1981/12/3 950 30 7902 FORD ANALYST 7566 1981/12/3 3000 20 7934 MILLER CLERK 7782 1982/1/23 1300 10 14 rows selected
