【Oracle】数据库应用技术上机-week12

最新推荐文章于 2024-10-02 00:27:17 发布

it's-bellaaaa

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量175

点赞数 2

分类专栏：数据库文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/Bella_Seven/article/details/122470071

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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