PLSQL条件语句完成员工加薪功能（记录类型+游标case when+if+for update+where current of）
来源：互联网 发布：淘宝达人cps是什么意思 编辑：程序博客网 时间：2024/06/03 15:56
TEST1:请使用函数+plsql完成给员工加薪的功能。CLERK 加薪 20%; SALESMAN 加薪30%; MANAGER 加薪35%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 1014 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 thendbms_output.put_line(sqlerrm);end;执行结果： 27 /7369SMITH已经成功加薪7499ALLEN已经成功加薪7521WARD已经成功加薪7566JONES已经成功加薪7654MARTIN已经成功加薪7698BLAKE已经成功加薪7782CLARK已经成功加薪7788SCOTT已经成功加薪ORA-06592: 执行 CASE 语句时未找到 CASEPL/SQL procedure successfully completedSQL> 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 1014 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 1014 rows selectedTEST2：请使用PLSQL完成对SCOTT的emp表中的员工，按照不同的工作岗位类型，进行涨工资的操作CLERK 涨幅500块ANALYST 涨幅1000块MANAGER 涨幅8%其他岗位涨幅5%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 1014 rows selectedSQL> exec p_test;PL/SQL procedure successfully completedSQL> select * from t; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980/12/17 1300 20 7499 ALLEN SALESMAN 7698 1981/2/20 1696 300 30 7521 WARD SALESMAN 7698 1981/2/22 1325 500 30 7566 JONES MANAGER 7839 1981/4/2 3213 20 7654 MARTIN SALESMAN 7698 1981/9/28 1325 1400 30 7698 BLAKE MANAGER 7839 1981/5/1 3078 30 7782 CLARK MANAGER 7839 1981/6/9 2646 10 7788 SCOTT ANALYST 7566 1982/12/9 3100 20 7839 KING PRESIDENT 1981/11/17 5300 10 7844 TURNER SALESMAN 7698 1981/9/8 1590 0 30 7876 ADAMS CLERK 7788 1983/1/12 1600 20 7900 JAMES CLERK 7698 1981/12/3 1450 30 7902 FORD ANALYST 7566 1981/12/3 3100 20 7934 MILLER CLERK 7782 1982/1/23 1800 1014 rows selectedSQL>
阅读全文
0 0
- PLSQL条件语句完成员工加薪功能（记录类型+游标case when+if+for update+where current of）
- Oracle游标之select for update和where current of 语句
- 在游标中使用FOR UPDATE 和 WHERE CURRENT
- for update 与where current of的问题
- SQL/PLSQL:在where条件中写case when和decode
- plsql编程语言 if case when
- case when 写在where条件中
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle 游标的where current of子句
- UPDATE语句中的WHERE条件
- where 里面 写入 case when 语句
- sql case when 语句记录
- Erlang（5）：控制结构when，if，case，for
- SQL中的条件判断语句（case when zhen）用法
- Case when 与Update语句的使用
- topgp登陆不了提示DVM connection timed out
- PXE启动原理以及与普通Linux启动的对比
- Atcoder-Sports Festival(二分+暴力+思维+贪心)
- Docker网络管理机制实例解析+创建自己Docker网络
- ArrayList、LinkedList、Vector的区别
- PLSQL条件语句完成员工加薪功能（记录类型+游标case when+if+for update+where current of）
- 推荐系统方法概览
- 安卓常用的框架
- VideoView之视频播放
- 使用Delve调试Go语言报错的结果办法
- 平面中判断点是否在某一三角形内算法 .
- 英语12个月份的英文和缩写
- 1008. 数组元素循环右移问题
- js取得gridview中获取checkbox选中的值
