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为其直接上级：

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

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

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

-----

SQL> SELECT 1+1 FROM DUAL;

1+1

----------

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

----------

SQL> SELECT &a1+1 FROM DUAL;

2+1

----------

SQL> SELECT &&a1+1 FROM DUAL;

5+1

----------

SQL> SELECT &a1+1 FROM DUAL;

5+1

----------

导入表（黑屏下）：

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

连接到:

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

----------

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

SQL> SELECT &a1+1 FROM DUAL;

3+1

----------

输入a2:

SQL> SELECT &a2+1 FROM DUAL;

2+1

----------

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

SQL> SELECT &a2+1 FROM DUAL;

5+1

----------

/*多分支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

----------

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;

Oracle数据库部分命令练习（Oracle Command）

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

Oracle Command

SQL题目练习

PL/SQL基础语句练习

导入表（黑屏下）：