2023-11-10 oracle第十周实验

野性酷女 2024-02-19 11:40 48阅读 0赞

第十周实验
    $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp
    
    
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
    Connected as system@ORCL
     
    SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp
    SQL> select * from xs;
     
    XH     XM     ZYM    XB CSSJ        ZXF BZ
    ------ ------ ------ -- ----------- --- --------------------
    061101 王林   计算机 男 1986/2/10    50 
    101112 李明   计算机 男 1986/1/30    36 
    001    张琼   计算机                 45 三好学生
    121112 王小二 计算机 男 1986/1/30    36 
     
    SQL>  select * from kc;
     
    KCH KCM              KKXQ  XS XF
    --- ---------------- ---- --- --
    001 001                 1  11  1
    001 001                 1  11  1
     
    SQL>  select * from xs_kc;
     
    XH     KCH                                         CJ  XF
    ------ ------ --------------------------------------- ---
    061101 101                                         80 
    061101 102                                         78 
    061101 206                                         76 
    061103 101                                         82 
    061103 102                                         82 
    061103 206                                         83 
    061104 101                                         90 
    061107 101                                         98 
    061107 102                                         80 
     
    9 rows selected
     
    SQL> select xh,xm,zxf from xs;
     
    XH     XM     ZXF
    ------ ------ ---
    061101 王林    50
    101112 李明    36
    001    张琼    45
    121112 王小二  36
     
    SQL> select xh,xm,zxf as 总学分 from xs;
     
    XH     XM        总学分
    ------ ------ ------
    061101 王林       50
    101112 李明       36
    001    张琼       45
    121112 王小二     36
     
    SQL> select xh,xm,zxf 总学分 from xs;
     
    XH     XM        总学分
    ------ ------ ------
    061101 王林       50
    101112 李明       36
    001    张琼       45
    121112 王小二     36
     
    SQL> 
    SQL>   select xh,
      2           xm,
      3           zxf,
      4           (case
      5             when zxf >= 50 then
      6              'gao'
      7             when zxf >= 40 then
      8              'zhong'
      9             else
     10              '学分不够，需继续'
     11           end) as 获得学分
     12      from xs;
     
    XH     XM     ZXF 获得学分
    ------ ------ --- ----------------
    061101 王林    50 gao
    101112 李明    36 学分不够，需继续
    001    张琼    45 zhong
    121112 王小二  36 学分不够，需继续
     
    SQL> 
    SQL> create table t01 as
      2  select xh,xm,zxf,(
      3         case
      4           when zxf>=50 then 'gao'
      5             when zxf>=40 then 'zhong'
      6               else '学分不够，需继续'
      7                 end
      8  )as 获得的学分 from xs;
     
    Table created
     
    SQL>  select * from t01;
     
    XH     XM     ZXF 获得的学分
    ------ ------ --- ----------------
    061101 王林    50 gao
    101112 李明    36 学分不够，需继续
    001    张琼    45 zhong
    121112 王小二  36 学分不够，需继续
     
    SQL> select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;
     
    MONTHS_BETWEEN(SYSDATE,TO_DATE
    ------------------------------
                  97.3126355286738
     
    SQL>  select trunc(sysdate-to_date('20181001','yyyymmdd')) 天数 from dual;
     
            天数
    ----------
          1866
     
    SQL> 
    SQL> select empno,
      2         ename,
      3         job,
      4         hiredate,
      5         (case
      6           when trunc(sysdate - hiredate) > 360 then
      7            '过期'
      8           when hiredate is null then
      9            '没借书'
     10           else
     11            '没过期'
     12         end) as 是否过期
     13    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 selected
     
    SQL> 
    SQL> select empno,
      2         ename,
      3         job,
      4         hiredate,
      5         (case
      6            when trunc(sysdate - hiredate) > 360 then
      7             '过期'
      8            when hiredate is null then
      9             '没借书'
     10            else
     11             '没过期'
     12          end) as 是否过期(case
     13           when trunc(sysdate - hiredate) - 360 > 0 then
     14            0.1 * trunc(sysdate - hiredate)
     15           else
     16            0
     17         end) 代缴金额
     18    from scott.emp;
     
    select empno,
           ename,
           job,
           hiredate,
           (case
              when trunc(sysdate - hiredate) > 360 then
               '过期'
              when hiredate is null then
               '没借书'
              else
               '没过期'
            end) as 是否过期(case
             when trunc(sysdate - hiredate) - 360 > 0 then
              0.1 * trunc(sysdate - hiredate)
             else
              0
           end) 代缴金额
      from scott.emp
     
    ORA-00923: 未找到要求的 FROM 关键字
     
    SQL> 
    SQL>  select empno,
      2          ename,
      3          job,
      4          hiredate,
      5          (case
      6            when trunc(sysdate - hiredate) > 360 then
      7             '过期'
      8            when hiredate is null then
      9             '没借书'
     10            else
     11             '没过期'
     12          end) as 是否过期,
     13          (case
     14            when trunc(sysdate - hiredate) - 360 > 0 then
     15             1 * trunc(sysdate - hiredate) - 360
     16            else
     17             0
     18          end) 代缴金额
     19     from scott.emp;
     
    EMPNO ENAME      JOB       HIREDATE    是否过期       代缴金额
    ----- ---------- --------- ----------- -------- ----------
     7369 SMITH      CLERK     1980/12/17  过期          15308
     7499 ALLEN      SALESMAN  1981/2/20   过期          15243
     7521 WARD       SALESMAN  1981/2/22   过期          15241
     7566 JONES      MANAGER   1981/4/2    过期          15202
     7654 MARTIN     SALESMAN  1981/9/28   过期          15023
     7698 BLAKE      MANAGER   1981/5/1    过期          15173
     7782 CLARK      MANAGER   1981/6/9    过期          15134
     7788 SCOTT      ANALYST   1987/4/19   过期          12994
     7839 KING       PRESIDENT 1981/11/17  过期          14973
     7844 TURNER     SALESMAN  1981/9/8    过期          15043
     7876 ADAMS      CLERK     1987/5/23   过期          12960
     7900 JAMES      CLERK     1981/12/3   过期          14957
     7902 FORD       ANALYST   1981/12/3   过期          14957
     7934 MILLER     CLERK     1982/1/23   过期          14906
     
    14 rows selected
     
    SQL> 
    SQL> select empno,
      2         ename,
      3         job,
      4         hiredate,
      5         (case
      6           when trunc(sysdate - hiredate) > 360 then
      7            '过期'
      8           when hiredate is null then
      9            '没借书'
     10           else
     11            '没过期'
     12         end) as 是否过期,
     13         (case
     14           when trunc(sysdate - hiredate) - 360 > 0 then
     15            0.1 * trunc(sysdate - hiredate)
     16           else
     17            0
     18         end) 代缴金额
     19    from scott.emp;
     
    EMPNO ENAME      JOB       HIREDATE    是否过期       代缴金额
    ----- ---------- --------- ----------- -------- ----------
     7369 SMITH      CLERK     1980/12/17  过期         1566.8
     7499 ALLEN      SALESMAN  1981/2/20   过期         1560.3
     7521 WARD       SALESMAN  1981/2/22   过期         1560.1
     7566 JONES      MANAGER   1981/4/2    过期         1556.2
     7654 MARTIN     SALESMAN  1981/9/28   过期         1538.3
     7698 BLAKE      MANAGER   1981/5/1    过期         1553.3
     7782 CLARK      MANAGER   1981/6/9    过期         1549.4
     7788 SCOTT      ANALYST   1987/4/19   过期         1335.4
     7839 KING       PRESIDENT 1981/11/17  过期         1533.3
     7844 TURNER     SALESMAN  1981/9/8    过期         1540.3
     7876 ADAMS      CLERK     1987/5/23   过期           1332
     7900 JAMES      CLERK     1981/12/3   过期         1531.7
     7902 FORD       ANALYST   1981/12/3   过期         1531.7
     7934 MILLER     CLERK     1982/1/23   过期         1526.6
     
    14 rows selected
     
    SQL> 
    SQL> declare
      2    cursor c_1 is
      3      select xh, xm from xs; -- 游标声明
      4    v_xm xs.xh%type;
      5    v_xm xs.xm%type;
      6  begin
      7    open c_1; -- 打开游标
      8    fetch c_1
      9      into v_xh, v_xm;
     10    dbms_output.put_line(v_xh || '' || v_xm);
     11    close c_1; --关闭游标
     12  end;
     13  、
     14  、
     15  /
     
    declare
      cursor c_1 is
        select xh, xm from xs; -- 游标声明
      v_xm xs.xh%type;
      v_xm xs.xm%type;
    begin
      open c_1; -- 打开游标
      fetch c_1
        into v_xh, v_xm;
      dbms_output.put_line(v_xh || '' || v_xm);
      close c_1; --关闭游标
    end;
    、 
    、
     
    ORA-06550: 第 14 行, 第 2 列: 
    PLS-00103: 出现符号 "、"
     
    SQL> 
    SQL> declare
      2    cursor c_1 is
      3      select xh, xm from xs; -- 游标声明
      4    v_xm xs.xh%type;
      5    v_xm xs.xm%type;
      6  begin
      7    open c_1; -- 打开游标
      8    fetch c_1
      9      into v_xh, v_xm;
     10    dbms_output.put_line(v_xh || '' || v_xm);
     11    close c_1; --关闭游标
     12  end;
     13  /
     
    declare
      cursor c_1 is
        select xh, xm from xs; -- 游标声明
      v_xm xs.xh%type;
      v_xm xs.xm%type;
    begin
      open c_1; -- 打开游标
      fetch c_1
        into v_xh, v_xm;
      dbms_output.put_line(v_xh || '' || v_xm);
      close c_1; --关闭游标
    end;
     
    ORA-06550: 第 10 行, 第 10 列: 
    PLS-00201: 必须声明标识符 'V_XH'
    ORA-06550: 第 9 行, 第 3 列: 
    PL/SQL: SQL Statement ignored
    ORA-06550: 第 11 行, 第 24 列: 
    PLS-00201: 必须声明标识符 'V_XH'
    ORA-06550: 第 11 行, 第 3 列: 
    PL/SQL: Statement ignored
     
    SQL> 
    SQL> declare
      2    cursor c_1 is
      3      select xh, xm from xs; -- 游标声明
      4    v_xh xs.xh%type;
      5    v_xm xs.xm%type;
      6  begin
      7    open c_1; -- 打开游标
      8    fetch c_1
      9      into v_xh, v_xm;
     10    dbms_output.put_line(v_xh || '' || v_xm);
     11    close c_1; --关闭游标
     12  end;
     13  /
     
    PL/SQL procedure successfully completed
     
    SQL>
    
    
    
    
    
    
    
    SQL> /*简单型case*/
      2  declare
      3    grade varchar2(4) := '良好';
      4  begin
      5    case grade
      6      when '优秀' then dbms_output.put_line('大于等于90');
      7      when '良好' then dbms_output.put_line('大于等于80，小于90');
      8      when '及格' then dbms_output.put_line('大于等于60，小于80');
      9      else dbms_output.put_line('不及格');
     10      end case;
     11  end;
     12  /
     
    大于等于80，小于90
     
    PL/SQL procedure successfully completed
     
    SQL> select * from emp where empno='7788';
     
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
    ----- ---------- --------- ----- ----------- --------- --------- ------
     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
     
    
    SQL> select * from emp where empno='7788';
     
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
    ----- ---------- --------- ----- ----------- --------- --------- ------
     7788 SCOTT      ANALYST    7566 1987/4/19     3150.00               20
     
    SQL> 
    SQL> declare
      2      v_deptno emp.deptno%type;
      3      v_increment number(4);
      4      v_empno emp.empno%type;
      5      v_record emp%rowtype;
      6  begin
      7    v_empno := '7788';
      8    select deptno into v_deptno from emp where empno=v_empno;
      9    case v_deptno
     10      when 10 then v_increment := 100;
     11      when 20 then v_increment := 150;
     12      when 30 then v_increment := 200;
     13      else v_increment := 300;
     14    end case;
     15    update emp set sal=sal+v_increment where empno=v_empno;
     16    if SQL%found then
     17      dbms_output.put_line('更改成功');
     18      select * into v_record from emp where empno=v_empno;
     19      dbms_output.put_line(v_record.empno||' '||v_record.ename||' '||v_record.job||' '||v_record.mgr||' '||v_record.hiredate||' '||v_record.sal||' '||v_record.comm||' '||v_record.deptno);
     20    end if;
     21  end;
     22  /
     
    更改成功
    7788 SCOTT ANALYST 7566 19-4月 -87 3300  20
     
    PL/SQL procedure successfully completed
     
    SQL> /*搜索case*/
      2  declare
      3    score int := 91;
      4  begin
      5    case
      6      when score>=90 then dbms_output.put_line('优秀');
      7      when score>=80 then dbms_output.put_line('良好');
      8      when score>=60 then dbms_output.put_line('及格');
      9      else dbms_output.put_line('不及格');
     10    end case;
     11  end;
     12  /
     
    优秀
     
    PL/SQL procedure successfully completed
    
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
    Connected as system@ORCL
     
    SQL> 
    SQL>  declare
      2     cursor c_dept_stat is
      3       select department_id, avg(salary) avgsal
      4         from hr.employees
      5        group by department_id; --游标(cursor)
      6     v_dept c_dept_stat%rowtype;
      7   begin
      8     open c_dept_stat;
      9     loop
     10       fetch c_dept_stat
     11         into v_dept;
     12       exit when c_dept_stat%notfound;
     13       dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
     14     end loop;
     15     close c_dept_stat;
     16   end;
     17  /
     
    PL/SQL procedure successfully completed
     
    SQL> select * from xs;
     
    XH     XM     ZYM    XB CSSJ        ZXF BZ
    ------ ------ ------ -- ----------- --- --------------------
    061101 王林   计算机 男 1986/2/10    50 
    101112 李明   计算机 男 1986/1/30    36 
    001    张琼   计算机                 45 三好学生
    121112 王小二 计算机 男 1986/1/30    36 
     
    SQL> select * from kc;
     
    KCH KCM              KKXQ  XS XF
    --- ---------------- ---- --- --
    001 001                 1  11  1
    001 001                 1  11  1
     
    SQL>  select * from kc_xs;
     
    select * from kc_xs
     
    ORA-00942: 表或视图不存在
     
    SQL> select * from xs_kc;
     
    XH     KCH                                         CJ  XF
    ------ ------ --------------------------------------- ---
    061101 101                                         80 
    061101 102                                         78 
    061101 206                                         76 
    061103 101                                         82 
    061103 102                                         82 
    061103 206                                         83 
    061104 101                                         90 
    061107 101                                         98 
    061107 102                                         80 
     
    9 rows selected
     
    SQL> 
    SQL> declare
      2    cursor c_dept_stat is
      3      select department_id, avg(salary) avgsal
      4        from hr.employees
      5       group by department_id;
      6    v_dept c_dept_stat%type;
      7  begin
      8    open c_dept_stat;
      9    fetch c_dept - stat
     10      into v_dept;
     11    while c_dept_stat%found loop
     12      dbms_output.put_line('退出循环的v_dept:');
     13      dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
     14      close c_dept_stat;
     15    end;
     16  /
     
    declare
      cursor c_dept_stat is
        select department_id, avg(salary) avgsal
          from hr.employees
         group by department_id;
      v_dept c_dept_stat%type;
    begin
      open c_dept_stat;
      fetch c_dept - stat
        into v_dept;
      while c_dept_stat%found loop
        dbms_output.put_line('退出循环的v_dept:');
        dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
        close c_dept_stat;
      end;
     
    ORA-06550: 第 10 行, 第 16 列: 
    PLS-00103: 出现符号 "-"在需要下列之一时：
     . into bulk
    ORA-06550: 第 12 行, 第 3 列: 
    PLS-00103: 出现符号 "WHILE"
     
    SQL> 
    SQL> declare
      2    cursor c_dept_stat is
      3      select department_id, avg(salary) avgsal
      4        from hr.employees
      5       group by department_id;
      6    v_dept c_dept_stat%type;
      7  begin
      8    open c_dept_stat;
      9    fetch c_dept_stat
     10      into v_dept;
     11    while c_dept_stat%found loop
     12      dbms_output.put_line('退出循环的v_dept:');
     13      dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
     14      close c_dept_stat;
     15    end;
     16  /
     
    declare
      cursor c_dept_stat is
        select department_id, avg(salary) avgsal
          from hr.employees
         group by department_id;
      v_dept c_dept_stat%type;
    begin
      open c_dept_stat;
      fetch c_dept_stat
        into v_dept;
      while c_dept_stat%found loop
        dbms_output.put_line('退出循环的v_dept:');
        dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
        close c_dept_stat;
      end;
     
    ORA-06550: 第 16 行, 第 6 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     loop
     
    SQL> 
    SQL> declare
      2    cursor c_dept_stat is
      3      select department_id, avg(salary) avgsal
      4        from hr.employees
      5       group by department_id;
      6    v_dept c_dept_stat%type;
      7  begin
      8    open c_dept_stat;
      9    fetch c_dept_stat
     10      into v_dept;
     11    while c_dept_stat%found loop
     12      dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
     13      etch c_dept_stat
     14        into v_dept;
     15    end loop;
     16    dbms_output.put_line('退出循环的v_dept:');
     17    dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
     18    close c_dept_stat;
     19  end;
     20  
     21  /
     
    declare
      cursor c_dept_stat is
        select department_id, avg(salary) avgsal
          from hr.employees
         group by department_id;
      v_dept c_dept_stat%type;
    begin
      open c_dept_stat;
      fetch c_dept_stat
        into v_dept;
      while c_dept_stat%found loop
        dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
        etch c_dept_stat
          into v_dept;
      end loop;
      dbms_output.put_line('退出循环的v_dept:');
      dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
      close c_dept_stat;
    end;
     
    ORA-06550: 第 14 行, 第 10 列: 
    PLS-00103: 出现符号 "C_DEPT_STAT"在需要下列之一时：
     := . ( @ % ;
     
    SQL> 
    SQL> declare
      2    cursor c_dept_stat is
      3      select department_id, avg(salary) avgsal
      4        from hr.employees
      5       group by department_id;
      6    v_dept c_dept_stat%type;
      7  begin
      8    open c_dept_stat;
      9    fetch c_dept_stat
     10      into v_dept;
     11    while c_dept_stat%found loop
     12      dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
     13      fetch c_dept_stat
     14        into v_dept;
     15    end loop;
     16    dbms_output.put_line('退出循环的v_dept:');
     17    dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
     18    close c_dept_stat;
     19  end;
     20  /
     
    declare
      cursor c_dept_stat is
        select department_id, avg(salary) avgsal
          from hr.employees
         group by department_id;
      v_dept c_dept_stat%type;
    begin
      open c_dept_stat;
      fetch c_dept_stat
        into v_dept;
      while c_dept_stat%found loop
        dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
        fetch c_dept_stat
          into v_dept;
      end loop;
      dbms_output.put_line('退出循环的v_dept:');
      dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
      close c_dept_stat;
    end;
     
    ORA-06550: 第 7 行, 第 10 列: 
    PLS-00206: %TYPE 必须用于变量, 列, 字段或属性, 而不是 'C_DEPT_STAT'
    ORA-06550: 第 7 行, 第 10 列: 
    PL/SQL: Item ignored
    ORA-06550: 第 11 行, 第 10 列: 
    PLS-00320: 此表达式的类型声明不完整或格式不正确
    ORA-06550: 第 10 行, 第 3 列: 
    PL/SQL: SQL Statement ignored
    ORA-06550: 第 13 行, 第 26 列: 
    PLS-00320: 此表达式的类型声明不完整或格式不正确
    ORA-06550: 第 13 行, 第 5 列: 
    PL/SQL: Statement ignored
    ORA-06550: 第 15 行, 第 12 列: 
    PLS-00320: 此表达式的类型声明不完整或格式不正确
    ORA-06550: 第 14 行, 第 5 列: 
    PL/SQL: SQL Statement ignored
    ORA-06550: 第 18 行, 第 24 列: 
    PLS-00320: 此表达式的类型声明不完整或格式不正确
    ORA-06550: 第 18 行, 第 3 列: 
    PL/SQL: Statement ignored
     
    SQL> 
    SQL> declare
      2    cursor c_dept_stat is
      3      select department_id, avg(salary) avgsal
      4        from hr.employees
      5       group by department_id;
      6    v_dept c_dept_stat%rowtype;
      7  begin
      8    open c_dept_stat;
      9    fetch c_dept_stat
     10      into v_dept;
     11    while c_dept_stat%found loop
     12      dbms_output.put_line(v_dept.department_id || ' ' || v_dept.avgsal);
     13      fetch c_dept_stat
     14        into v_dept;
     15    end loop;
     16    dbms_output.put_line('退出循环的v_dept:');
     17    dbms_output.put_line(v_dept.department_id || '' || v_dept.avgsal);
     18    close c_dept_stat;
     19  end;
     20  /
     
    PL/SQL procedure successfully completed
     
    SQL>