【Oracle】数据库应用技术上机-week12
2022/1/13 19:06:01
本文主要是介绍【Oracle】数据库应用技术上机-week12，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.%rowtype
SQL> SQL> declare 2 v_emp scott.emp%rowtype; 3 begin 4 select * into v_emp from scott.emp where empno=7900; 5 DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal); 6 end; 7 / 7900 950 PL/SQL procedure successfully completed
2.elsif分支结构
查询emp表中empno=7902的员工，部门号为10的员工工资加100，给部门号为10的员工工资加100，给部门号为10的员工工资加100，剩下的员工工资加400。
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14 rows selected
SQL> SQL> declare 2 v_deptno scott.emp.deptno%type; 3 v_zl scott.emp.sal%type; 4 begin 5 select deptno into v_deptno from scott.emp where empno='7902'; 6 if v_deptno=10 then v_zl:=100; 7 elsif v_deptno=20 then v_zl:=200; 8 elsif v_deptno=30 then v_zl:=300; 9 else v_zl:=400; 10 end if; 11 update scott.emp set sal=sal+v_zl where empno='7902'; 12 end; 13 / PL/SQL procedure successfully completed
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3200.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14 rows selected
SQL> select empno,sal from scott.emp where empno='7902'; EMPNO SAL ----- --------- 7902 3200.00
3.加入临时变量
SQL> SQL> declare 2 v_deptno scott.emp.deptno%type; 3 v_zl scott.emp.sal%type; 4 begin 5 select deptno into v_deptno from scott.emp where empno=&&a; 6 if v_deptno=10 then v_zl:=100; 7 elsif v_deptno=20 then v_zl:=200; 8 elsif v_deptno=30 then v_zl:=300; 9 else v_zl:=400; 10 end if; 11 update scott.emp set sal=sal+v_zl where empno=&a; 12 end; 13 / PL/SQL procedure successfully completed
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3400.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14 rows selected SQL> select empno,sal from scott.emp where empno='7902'; EMPNO SAL ----- --------- 7902 3400.00
4.将第2题修改为case语句
SQL> SQL> select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM DEPTNO, 2 (case 3 when deptno=10 then sal+100 4 when deptno=20 then sal+200 5 when deptno=30 then sal+300 6 else sal+400 7 end)as 8 加薪后工资 from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO 加薪后工资 ----- ---------- --------- ----- ----------- --------- --------- ---------- 7369 SMITH CLERK 7902 1980/12/17 800.00 1000 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 1900 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 1550 7566 JONES MANAGER 7839 1981/4/2 2975.00 3175 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 1550 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 3150 7782 CLARK MANAGER 7839 1981/6/9 2450.00 2550 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 3200 7839 KING PRESIDENT 1981/11/17 5000.00 5100 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 1800 7876 ADAMS CLERK 7788 1987/5/23 1100.00 1300 7900 JAMES CLERK 7698 1981/12/3 950.00 1250 7902 FORD ANALYST 7566 1981/12/3 3400.00 3600 7934 MILLER CLERK 7782 1982/1/23 1300.00 1400 14 rows selected
5.case语句
学生表根据成绩划分高中低等级
SQL> SQL> select xh,xm,zxf,( 2 case 3 when zxf>50 then '高' 4 when zxf>=40 then '中' 5 else '学分不够，还需继续！' 6 end)as 学生等级 from xs; XH XM ZXF 学生等级 ------ ------ --- -------------------- 061101 王林 50 中 101112 李明 36 学分不够，还需继续！ 001 张琼 45 中 121112 王小二 36 学分不够，还需继续！
6.查询图书借阅是否到期
SQL> SQL> select EMPNO,ENAME,JOB,MGR,HIREDATE, 2 (case 3 when sysdate-HIREDATE>60 then '已到期' 4 else '未到期' 5 end) as 6 借阅是否到期 from scott.emp; EMPNO ENAME JOB MGR HIREDATE 借阅是否到期 ----- ---------- --------- ----- ----------- ------------ 7369 SMITH CLERK 7902 1980/12/17 已到期 7499 ALLEN SALESMAN 7698 1981/2/20 已到期 7521 WARD SALESMAN 7698 1981/2/22 已到期 7566 JONES MANAGER 7839 1981/4/2 已到期 7654 MARTIN SALESMAN 7698 1981/9/28 已到期 7698 BLAKE MANAGER 7839 1981/5/1 已到期 7782 CLARK MANAGER 7839 1981/6/9 已到期 7788 SCOTT ANALYST 7566 1987/4/19 已到期 7839 KING PRESIDENT 1981/11/17 已到期 7844 TURNER SALESMAN 7698 1981/9/8 已到期 7876 ADAMS CLERK 7788 1987/5/23 已到期 7900 JAMES CLERK 7698 1981/12/3 已到期 7902 FORD ANALYST 7566 1981/12/3 已到期 7934 MILLER CLERK 7782 1982/1/23 已到期 14 rows selected SQL> SQL> select empno,ename,job,hiredate, 2 ( 3 case 4 when trunc(sysdate-HIREDATE)>60 then '已过期' 5 when hiredate is null then '没借书' 6 else '未过期' 7 end 8 ) 9 as 借阅是否到期 from scott.emp; EMPNO ENAME JOB HIREDATE 借阅是否到期 ----- ---------- --------- ----------- ------------ 7369 SMITH CLERK 1980/12/17 已过期 7499 ALLEN SALESMAN 1981/2/20 已过期 7521 WARD SALESMAN 1981/2/22 已过期 7566 JONES MANAGER 1981/4/2 已过期 7654 MARTIN SALESMAN 1981/9/28 已过期 7698 BLAKE MANAGER 1981/5/1 已过期 7782 CLARK MANAGER 1981/6/9 已过期 7788 SCOTT ANALYST 1987/4/19 已过期 7839 KING PRESIDENT 1981/11/17 已过期 7844 TURNER SALESMAN 1981/9/8 已过期 7876 ADAMS CLERK 1987/5/23 已过期 7900 JAMES CLERK 1981/12/3 已过期 7902 FORD ANALYST 1981/12/3 已过期 7934 MILLER CLERK 1982/1/23 已过期 14 rows
这篇关于【Oracle】数据库应用技术上机-week12的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-23增量更新怎么做？-icode9专业技术文章分享
- 2024-11-23压缩包加密方案有哪些？-icode9专业技术文章分享
- 2024-11-23用shell怎么写一个开机时自动同步远程仓库的代码？-icode9专业技术文章分享
- 2024-11-23webman可以同步自己的仓库吗？-icode9专业技术文章分享
- 2024-11-23在 Webman 中怎么判断是否有某命令进程正在运行？-icode9专业技术文章分享
- 2024-11-23如何重置new Swiper?-icode9专业技术文章分享
- 2024-11-23oss直传有什么好处？-icode9专业技术文章分享
- 2024-11-23如何将oss直传封装成一个组件在其他页面调用时都可以使用？-icode9专业技术文章分享
- 2024-11-23怎么使用laravel 11在代码里获取路由列表？-icode9专业技术文章分享
- 2024-11-22怎么实现ansible playbook 备份代码中命名包含时间戳功能？-icode9专业技术文章分享
