Oracle数据库部分命令练习(并不全面,对于Oracle体系结构、分区表等以及基础的分支语句、触发器、存储过程等补充请直接参考Oracle实验报告练习或直接下载资源文档:https://download.csdn.net/download/qq_42785226/14622190

Oracle Command

SQL题目练习

1、列出至少有一个员工的所有部门。

2、列出薪金比“SMITH”多的所有员工。

3、列出所有员工的姓名及其直接上级的姓名。

4、列出受雇日期早于其直接上级的所有员工。

5、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

6、列出所有“CLERK”(办事员)的姓名及其部门名称。

7、列出最低薪金大于1500的各种工作。

8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。

9、列出薪金高于公司平均薪金的所有员工。

10、列出与“SCOTT”从事相同工作的所有员工。

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

13、列出在每个部门工作的员工数量、平均工资和平均服务期限。

14、列出所有员工的姓名、部门名称和工资。

15、列出所有部门的详细信息和部门人数。

16、列出各种工作的最低工资。

17、列出各个部门的MANAGER(经理)的最低薪金。

答案:

1.select deptno, count(*) from scott.emp group by DEPTNO having count(*) >1;

2.select * from scott.emp where sa1 > (select sa1 from scott.emp where ename='SMITH');

3.select e0.ENAME, e1.ename from scott.emp e0,scott.emp e1 where e0.MGR=e1.empno;

 

分析:上述早于即小于(时间)

用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。如果加号写在右表,左表就是全部显示,所以是左连接。

这里以e0为员工,e1为其直接上级:

  1. select e0.*,e1.* from scott.emp e0,scott.emp e1 where e0.mgr=e1.empno(+) and e0.hiredate<e1.hiredate;

 

分析:同时列出那些没有员工的部门,即列出所有部门,我们采用左外连接:

  1. select d.dname,e.* from scott.dept d,scott.emp e where d.deptno=e.deptno(+);

 

6.select e.ename, d.dname from scott.emp e join scott.dept d on e.deptno = d.deptno where job = 'CLERK';

上述采用的是外连接,也可以采用多表查询等方式:

select ename,dname from emp, dept where emp.job='CLERK' and emp.deptno=dept.deptno;

 

7.select job from scott.emp group by job having min(sal) > 1500;

8.select e.ename from scott.emp e, scott.dept d where d.dname = 'SALES' and e.deptno(+) = d.deptno;

9.select * from scott.emp where sal > (select avg(sal) from scott.emp);

10.select * from scott.emp where job = (select job from scott.emp where ename = 'SCOTT');

11.select * from scott.emp where sal in (select sal from scott.emp where deptno = 30);

 

12.select * from scott.emp where sal > all (select sal from scott.emp where deptno = 30);

上述实现也可以采用如下方式(首先获取部门为30的最高工资(利用desc降序排列和rownum获取最高工资)):

select * from emp where sal >

 (select sal from

    (select sal from emp where deptno=30 order by sal desc)

      where rownum<=1);

(注意:ASC是升序排列,在rownum那里要进行嵌套查询,order by要写到where子句之后,否则会报ORA-00933: SQL 命令未正确结束错误group by也要写到where子句之后)

SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

SQL> select sal,deptno from emp

  2  order by sal desc

  3  where deptno=30

  4   ;

select sal,deptno from emp

order by sal desc

where deptno=30

 

ORA-00933: SQL 命令未正确结束

上述rownum是oracle中的top子句(取前number行数据):

top子句:

 

SQLServer用法:

SELECT TOP number|percent column_name(s)

FROM table_name

例(percent百分数):

SELECT TOP 2 * FROM Persons

从上面的 "Persons" 表中选取 50% 的记录:

SELECT TOP 50 PERCENT * FROM Persons

 

MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的:

MySQL 语法:

SELECT column_name(s)

FROM table_name

LIMIT number

Oracle 语法:

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

例(MySQL(limit)):

Oracle为rownum,这里不再多说。

需要注意的一点就是这几个top子句都是符合不同数据库的语法,不能混用:

例(在oracle中使用mysql中的limit top子句则行不通):

 

同时在oracle中和mysql中使用SqlServer中的top子句也不行。

 

13.select count(empno) 人数, avg(sal) 平均5261工资4102, avg(EXTRACT(year FROM sysdate) - EXTRACT(year FROM emp.hiredate)) 平均雇佣期限1653 from dual, scott.emp group by deptno;

14.select e.ename, d.dname, e.sal from scott.emp e, scott.dept d  where e.deptno = d.deptno(+);

15.select d.*, t.count from scott.dept d, (select deptno, count(empno) count from scott.emp group by deptno) t where d.deptno = t.deptno(+);

16.select job, min(sal) from scott.emp group by job;

17.select deptno, min(sal) from scott.emp where job = 'MANAGER' group by deptno;

 

PL/SQL基础语句练习

HelloWord以及赋值语句练习:

 

SET ServerOutput ON;

BEGIN

  dbms_output.put_line('HELLO WORLD!');

END;

/

 

SET ServerOutput ON;

DECLARE

Database VARCHAR2(50) := 'Oracle 11g';  --赋值语句练习

BEGIN

  dbms_output.put_line(Database);

END;

/

 

SQL%NOTFOUND练习(模拟merge练习):

SET ServerOutput ON;

DECLARE

    /*定义变量类型,注意:=*/

    v_xm varchar2(8):='Jame';

    v_zym varchar2(10):='计算机';

    v_zxf number(2):=45;

BEGIN

    UPDATE XS SET zxf=v_zxf WHERE xm=v_xm;

    IF SQL%NOTFOUND THEN --SQL%NOTFOUND用于判断update是否执行成功执行成功则sql found即SQL%NOTFOUND返回false,反之即执行失败返回true

      dbms_output.put_line('没有该人,需要插入该人');

      INSERT INTO XS(XH,XM,ZYM,ZXF)VALUES('007',v_xm,v_zym,v_zxf);

    END IF;

END;

/

 

Update扩展(带Return子句):

SET ServerOutput ON;

DECLARE

    row_id ROWID;

    info VARCHAR2(100);

BEGIN

      UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'

             RETURNING rowid,dname||':'||to_char(deptno)||':'||loc

                      INTO row_id,info;

      dbms_output.put_line('ROWID:'||row_id);

      dbms_output.put_line(info);

END;

/

结果:

ROWID:AAAMgxAAEAAAAAQAAB

RESEARCH:90:DALLAS

 

SET ServerOutput ON;

DECLARE

    --row_id ROWID;

    info VARCHAR2(100);

BEGIN

      UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'

             RETURNING dname||':'||to_char(deptno)||':'||loc

                      INTO info;

      --dbms_output.put_line('ROWID:'||row_id);

      dbms_output.put_line(info);

END;

/

结果:

RESEARCH:90:DALLAS

 

%rowtype练习:

SET ServerOutput ON;

DECLARE

   one_emp scott.emp%rowtype;

BEGIN

      select * into one_emp from scott.emp where empno=7900;

      dbms_output.put_line('该员工的职位为:'||one_emp.job);

      dbms_output.put_line('该员工的工资为:'||one_emp.sal);

END;

/

 

%type练习:

SET ServerOutput ON;

DECLARE

   emp_number constant number(4):=7900;

   emp_name scott.emp.ename%type;

   emp_job scott.emp.job%type;

   emp_sal scott.emp.sal%type;

BEGIN

      select ename,job,sal into emp_name,emp_job,emp_sal from scott.emp where empno=emp_number;

      dbms_output.put_line('查询的员工号为:'||emp_number);

      dbms_output.put_line('该员工的姓名为:'||emp_name);

      dbms_output.put_line('该员工的职位为:'||emp_job);

      dbms_output.put_line('该员工的工资为:'||emp_sal);

END;

/

 

异常处理:

SET ServerOutput ON;

DECLARE

  v_1 number;

BEGIN

  SELECT count(*) INTO v_1 FROM xs;

  dbms_output.put_line(v_1);

EXCEPTION

  when others then

    dbms_output.put_line('出现异常了');

END;

/

 

定义常量:

SET SERVEROUTPUT ON;

DECLARE

  conVersion constant VARCHAR2(20) := '1.0.0.1';  --直接赋值

BEGIN

  dbms_output.put_line(conVersion);

END;

/  

 

IF语句练习: IF...ELSIF...ELSE...END IF

SET SERVEROUTPUT ON;

DECLARE

  Num INTEGER :=- 11;

BEGIN

  IF Num <0 THEN

     dbms_output.put_line('负数');

  ELSIF Num > 0 THEN

       dbms_output.put_line('正数');

  ELSE

       dbms_output.put_line('0');

  END IF;

END;

/

 

简单Case语句练习:

SET SERVEROUTPUT ON;

DECLARE

  varDAY INTEGER := 3;

  Result VARCHAR2(20);

BEGIN

  Result := CASE varDAY  --赋值,看case是几

         WHEN 1 THEN '星期一'

         WHEN 2 THEN '星期二'

         WHEN 3 THEN '星期三'

         WHEN 4 THEN '星期四'

         WHEN 5 THEN '星期五'

         WHEN 6 THEN '星期六'

         WHEN 7 THEN '星期七'

         ELSE '数据越界'

  END;

  dbms_output.put_line(Result);

END;

/

 

循环语句练习:LOOP...IF..EXIT..END IF...END LOOP(采用if..exit中途退出的方式来跳出循环)

SET SERVEROUTPUT ON;

DECLARE

  v_Num INTEGER := 1;

  v_Sum INTEGER := 0;

BEGIN

  LOOP

    v_Sum := v_Sum + v_Num;

    dbms_output.put_line(v_Num);

    IF v_Num = 3 THEN

      EXIT;

    END IF;

    dbms_output.put_line('+');

    v_Num := v_Num+1;

  END LOOP;

  dbms_output.put_line('=');

  dbms_output.put_line(v_Sum);

END;

/

 

别名练习(也可加上as):

SQL> select xh xuehao,xm from xs;

 

XUEHAO XM

------ ------

 

DUAL练习:

Dual操作(DESC,查询内容,日期(格式),作计算器等):

SQL> SELECT * FROM DUAL;

 

DUMMY

-----

X

 

SQL> SELECT 1+1 FROM DUAL;

 

       1+1

----------

         2

 

SQL> SELECT SYSDATE FROM DUAL;

 

SYSDATE

-----------

2020/10/2 1

 

SQL> SELECT TO_CHAR(SYSDATE,'DY')FROM DUAL;

 

TO_CHAR(SYSDATE,'DY')

---------------------

星期五

 

SQL> DESC DUAL;

Name  Type        Nullable Default Comments

----- ----------- -------- ------- --------

DUMMY VARCHAR2(1) Y      

 

Dual查询user、日期(带特定格式)、生成随机数:

SQL> select user from dual;

 

USER

--------------------------------------------------------------------------------

SYSTEM

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')

---------------------------------------

2020-10-03 21:55:35

 

SQL> select to_char(sysdate,'mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'MM-DDHH24:MI:SS')

----------------------------------

10-03 21:56:06

 

 

SQL> select dbms_random.random from dual;

 

    RANDOM

----------

-496286255

 

SQL> select dbms_random.random from dual;

 

    RANDOM

----------

1745332824

 

 

临时变量&:

 

SQL> SELECT &a1+1 FROM DUAL;

 

       1+1

----------

         2

 

SQL> SELECT &a1+1 FROM DUAL;

 

       2+1

----------

         3

 

SQL> SELECT &&a1+1 FROM DUAL;

 

       5+1

----------

         6

 

SQL> SELECT &a1+1 FROM DUAL;

 

       5+1

----------

         6

 

  

导入表(黑屏下)

SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp;

 

黑屏下查询归档文件信息:

SQL> ArCHIVE Log list;

ORA-01031: 权限不足

SQL> conn /as sysdba;

已连接。

SQL> archive log list;

 

查询表信息:

SQL> select * from cat;

 

TABLE_NAME                     TABLE_TYPE

------------------------------ -----------

SYSCATALOG                     SYNONYM

CATALOG                        SYNONYM

...

184 rows selected

 

查询用户信息:

SQL> select * from dba_users;

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     PROFILE                        INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME

------------------------------ ---------- ------------------------------ -------------------------------- ----------- ----------- ------------------------------ ------------------------------ ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------

MGMT_VIEW                              53 181DA128BC4A91E7               OPEN                                                     SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        DEFAULT_CONSUMER_GROUP         

SYS                                     0 D3CE9AB10E42F19D               OPEN                                                     SYSTEM                         TEMP                           2007/4/17 0 DEFAULT                        SYS_GROUP                      

...

27 rows selected

 

查询数据文件信息dba_data_files:

SQL> select * from dba_data_files;

FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS

-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    5177344         632 ONLINE

...

 

select * from v$datafile;

SQL> select * from v$datafile;

 

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------

         1               11 2007/4/17 03:          0          1 SYSTEM  READ WRITE             730077 2020/10/9 15:56                     0                                                      521802         521803 2020/9/6 11  503316480      61440            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        0          8192 NONE                                                                                               0

...

 

查询归档文件信息:select name,log_mode from v$database;

SQL> select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- ------------

ORCL      NOARCHIVELOG

 

查询控制文件信息:select * from v$controlfile;

SQL> select * from v$controlfile;

 

STATUS  NAME                                                                             IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS

------- -------------------------------------------------------------------------------- --------------------- ---------- --------------

        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL                              NO                         16384            430

...

 

查询日志文件信息:

select * from v$log;

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------

         1          1          8   52428800          1 NO       INACTIVE                661898 2020/9/11 0

...

 

select * from v$logfile;

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

---------- ------- ------- -------------------------------------------------------------------------------- ---------------------

         3         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO

...

 

按组添加日志文件LOGFILE并作出相关查询:ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k;

SQL> ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k;

 

Database altered

 

重新查询logfile日志文件:

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

---------- ------- ------- -------------------------------------------------------------------------------- ---------------------

   4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.RDO                                 NO

         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.RDO                                 NO

...

 

按成员MEMBER添加日志文件LOGFILE到组GROUP并作出相关查询:ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4;

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4;

 

Database altered

 

重新查询logfile日志文件:

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE

---------- ------- ------- -------------------------------------------------------------------------------- ---------------------

 4 INVALID ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG3A.LOG                                 NO

...

 

按成员删除日志文件:ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log';

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log';

 

Database altered

 

再次查询日志文件发现没有log3a.log文件了。

SQL> select * from v$logfile;

...

 

按组删除日志文件:ALTER DATABASE DROP LOGFILE  Group 4;

SQL> ALTER DATABASE DROP LOGFILE  Group 4;

 

Database altered

 

再次查询日志文件发现没有4组的了。

SQL> select * from v$logfile;

...

 

查询数据块大小:SHOW PARAMETER db_block_size;

SQL> SHOW PARAMETER db_block_size;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     8192

 

查询用户分区信息:select * from user_extents;

内容太多,略。

 

归档模式与非归档模式:

非归档模式,不记归档日志,没有办法进行数据恢复。当然如果数据库的量比较大,变换的频率比较高,这个归档的模式也要合理规划的,否则归档日志的空间量是很可观的!

 

以下四步将数据库从非归档模式转化为归档模式(黑屏下):

shutdown immediate;

startup nomount;

alter database mount;

alter database archivelog;

 

将数据库从非归档模式转化为归档模式(黑屏下)可能出现的错误:

出现错误(ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务)   监听配置出错,修改listener.ora和tnsnames.ora配置文件后得以解决

例:

SQL> Shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> Startup nomount;

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

SQL> Alter database mount;

ERROR:

ORA-03114: 未连接到 ORALCE

 

 

 

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 10月 23 16:31:44 2020

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> SHOW USER;

USER 为 "SYSTEM"

SQL> conn /as sysdba;

已连接。

SQL> show user;

USER 为 "SYS"

 

//重新操作时oracle重新运行了,应该先关闭

 

SQL> startup nomount;

ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup nomount;

ORACLE 例程已经启动。

 

Total System Global Area  612368384 bytes

Fixed Size                  1292036 bytes

Variable Size             234883324 bytes

Database Buffers          369098752 bytes

Redo Buffers                7094272 bytes

SQL> alter database mount;

 

数据库已更改。

 

SQL> alter database archivelog;

 

数据库已更改。

 

SQL>

 

查询归档文件信息:select name,log_mode from v$database;

SQL> select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- ------------

ORCL      NOARCHIVELOG

 

ARCHIVE LOG LIST;黑屏模式下进行归档模式查询

SQL> ARCHIVE LOG LIST;

数据库日志模式             非存档模式

自动存档             禁用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     13

当前日志序列           15

 

分支结构1

SET SERVEROUTPUT ON;

DECLARE

v_xm varchar2(8):='jame';

v_zym varchar2(10):='计算机';

v_zxf number(2):=45; /*定义变量类型*/

BEGIN

  INSERT INTO XS(XH,XM,ZYM,ZXF)

  VALUES('007',v_xm,v_zym,v_zxf);

  IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE('操作成功');

  ELSE

    DBMS_OUTPUT.PUT_LINE('没有插入该人');

  END IF;

END;

/

 

分支结构2

DECLARE

v_sal scott.emp.sal%type;

v_tax scott.emp.sal%type;

BEGIN

  select sal into v_sal from scott.emp where empno=7788;  /*%type返回一条记录中某一列*/

  if v_sal>=3000 then

    v_tax:=v_sal*0.08;

  elsif v_sal>=1500 then

    v_tax:=v_sal*0.06;

  else

    v_tax:=v_sal*0.04;

  end if;

  DBMS_OUTPUT.PUT_LINE('应交税金:'||v_tax);  /*||连接字符串*/

END;

/

 

带临时变量的多分支结构

declare

   v_deptno  scott.emp.deptno%type;

   v_zl  scott.emp.sal%type;

begin

   select deptno into v_deptno  from  scott.emp  where empno=&&a;  --这里也可以为empno定义一个变量v_empno来记住外界输入的a的值

   if v_deptno=10 then

       v_zl:=100;

     elsif   v_deptno=20 then

        v_zl:=150;

          elsif   v_deptno=30 then

          v_zl:=200;

     else

     v_zl:=300;

     end if;

     --更新到原来的那个人(empno)

     UPDATE scott.emp SET sal=sal+v_zl  WHERE empno=&a;

     end;

/

/*上一步操作临时变量输入的是7934号员工,执行查询之后获得该7934号员工的deptno为100,增加100后更新到数据表中,结果可参见两次查询结果对比(1300->1400)*/

 

/*注意临时变量&&a和&a的使用

(&&是输入临时变量(并保存),&是获取上次输入过的临时变量,若没有输入过则需要进行输入(只&过的是不保存历史的临时变量,每次都要重新输入)*/

/*例:

输入a1:

SQL> SELECT &&a1+1 FROM DUAL;

 

       3+1

----------

         4

 

检测到历史a1,无需输入:

SQL> SELECT &a1+1 FROM DUAL;

 

       3+1

----------

         4

输入a2:

SQL> SELECT &a2+1 FROM DUAL;

 

       2+1

----------

         3

 

输入a2,上次的a2是&而不是&&,故需要重新输入:

SQL> SELECT &a2+1 FROM DUAL;

 

       5+1

----------

         6

 

*/

 

/*多分支case语句*/

1.简单型case

2.搜索性case

3.嵌入到select语句执行复杂任务的case

4.嵌入到PL/SQL程序语句(如赋值语句)的case

 

简单型case

declare

grade varchar2(4):='良好';

begin

  CASE grade

    when '优秀' then dbms_output.put_line('大于等于90');

    when '良好' then dbms_output.put_line('大于等于80,小于等于90');

    when '及格' then dbms_output.put_line('大于等于60,小于等于80');

    ELSE dbms_output.put_line('不及格');

    END CASE;

end;

/

 

等值比较的case语句

DECLARE

v_deptno emp.deptno%type;

v_increment number(4);

v_empno emp.empno%type;

BEGIN

  v_empno:=&x;  --这里也可以采用&&x然后使用&x来记录输入的x的值

  select deptno into v_deptno from emp where empno=v_empno;

  CASE v_deptno

  WHEN 10 THEN v_increment:=100;

  WHEN 20 THEN v_increment:=150;

  WHEN 30 THEN v_increment:=200;

  ELSE v_increment:=300;

  END CASE;

  update emp set sal=sal+v_increment where empno=v_empno;

END;

/

 

搜索case表达式:

SET SERVEROUTPUT ON;

declare

score int:=91;

begin

  case

    when score>=90 then dbms_output.put_line('优秀');

    when score>=80 then dbms_output.put_line('良好');

    when score>=60 then dbms_output.put_line('及格');

    else dbms_output.put_line('不及格');

  end case;

end;

/

 

嵌入到select语句执行复杂任务的case

/*与if语句不同,case语句可以用在select语句中,用于在检索数据的同时对数据进行判断并返回判断结果*/

select xh,xm,zxf,

(case

       when zxf>50 then '高'

         when zxf>=40 then '中'

           else '学分不够,需继续努力'

end) as 获得学分情况

from xs;

 

将上述例子的结果以表的形式保存起来:

create table t01 as

select xh,xm,zxf,

(case

       when zxf>50 then '高'

         when zxf>=40 then '中'

           else '学分不够,需继续努力'

end) as 获得学分情况

from xs;

 

/*case的select练习*/

/*对于学生借阅的图书信息,检验图书是否过期

1.过期

2.没过期

3.没有借阅图书*/

SQL> select empno,ename,job,hiredate,

  2  (case

  3         when trunc(sysdate-hiredate)>360 then '过期'  --trunc()取整函数

  4           when hiredate is null then '没借书'

  5             else '没过期'

  6  end)

  7  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 selected

 

查询图书是否过期及应缴金额(select--case查询)

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  then trunc((sysdate-HIREDATE)*0.1)

    when hiredate is null then 0

     else  0

    end)

   as 应缴金额

   from scott.emp;

 

结果:

EMPNO ENAME      JOB       HIREDATE    是否过期       应缴金额

----- ---------- --------- ----------- -------- ----------

 7369 SMITH      CLERK     1980/12/17  过期           1453

 7499 ALLEN      SALESMAN  1981/2/20   过期           1446

 7521 WARD       SALESMAN  1981/2/22   过期           1446

 7566 JONES      MANAGER   1981/4/2    过期           1442

 7654 MARTIN     SALESMAN  1981/9/28   过期           1424

 7698 BLAKE      MANAGER   1981/5/1    过期           1439

 7782 CLARK      MANAGER   1981/6/9    过期           1435

 7788 SCOTT      ANALYST   1987/4/19   过期           1221

 7839 KING       PRESIDENT 1981/11/17  过期           1419

 7844 TURNER     SALESMAN  1981/9/8    过期           1426

 7876 ADAMS      CLERK     1987/5/23   过期           1218

 7900 JAMES      CLERK     1981/12/3   过期           1418

 7902 FORD       ANALYST   1981/12/3   过期           1418

 7934 MILLER     CLERK     1982/1/23   过期           1413

 

14 rows selected

 

查询优良等级(select--case查询)

select xh,kch,cj,

    (case

          when cj>=90 then '优'

          when cj>=80 then '良'

           else ''

      end)

          as 级别

          from xs_kc;   

 

嵌入到pl/sql程序语句(如赋值语句)的case等。

 

对于日期的简单练习等:

-- https://www.cnblogs.com/jxtx92/p/7905339.html

-- https://blog.csdn.net/w405722907/article/details/75519156

-- https://blog.csdn.net/zoucui/article/details/79347286

 

SQL> select months_between(sysdate,to_date('20151001','yyyymmdd')) from dual;

 

MONTHS_BETWEEN(SYSDATE,TO_DATE

------------------------------

              61.4093977747909

 

SQL> select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;  

 

    MONTHS

----------

         1

 

SQL> select trunc(sysdate-to_date('20181001','yyyymmdd'))天数 from dual;

 

        天数

----------

       774

 

创建分区表:

create table part_book1

      (  bid number(4),

         bookname VARCHAR2(20),

         bookpress VARCHAR2(30),

          booktime date)

partition by list(bookpress)

  (partition part1 values('清华大学出版社') tablespace system,

   partition part2 values('教育出版社') tablespace users);

 

查询分区表:

SQL> select * from part_book1;

 BID BOOKNAME             BOOKPRESS                      BOOKTIME

----- -------------------- ------------------------------ -----------

    1 oralce               清华大学出版社                 2011/1/2

    2 音乐基础欣赏         教育出版社                     2012/1/2

 

SQL> select * from part_book1 partition(part1);

 

  BID BOOKNAME             BOOKPRESS                      BOOKTIME

----- -------------------- ------------------------------ -----------

    1 oralce               清华大学出版社                 2011/1/2

 

SQL> select * from part_book1 partition(part2);

 

  BID BOOKNAME             BOOKPRESS                      BOOKTIME

----- -------------------- ------------------------------ -----------

2 音乐基础欣赏         教育出版社                     2012/1/2

 

 

SQL>select * from dba_part_tables;

内容太多,略。

 

SQL> select * from dba_part_tables where table_name='PART_BOOK1';

 OWNER                          TABLE_NAME                     PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTITIONING_KEY_COUNT STATUS   DEF_TABLESPACE_NAME            DEF_PCT_FREE DEF_PCT_USED DEF_INI_TRANS DEF_MAX_TRANS DEF_INITIAL_EXTENT                       DEF_NEXT_EXTENT                          DEF_MIN_EXTENTS                          DEF_MAX_EXTENTS                          DEF_PCT_INCREASE                         DEF_FREELISTS DEF_FREELIST_GROUPS DEF_LOGGING DEF_COMPRESSION DEF_BUFFER_POOL

------------------------------ ------------------------------ ----------------- -------------------- --------------- ---------------------- ---------------------- ------------------------- -------- ------------------------------ ------------ ------------ ------------- ------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------- ------------------- ----------- --------------- ---------------

...

 

//注意:不同于select * from dba_part_tables

SQL> select * from dba_tab_partitions where table_name='PART_BOOK1';

 

TABLE_OWNER                    TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS

------------------------------ ------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------

...

 

修改分区表:

SQL> Alter table part_book1 add partition part4 values('北京邮电出版社') tablespace system;