PL/SQL学习笔记(三)-数据库

子查询：

语法：
select col from table
where expr operator (select col from table);

子查询在主查询执行前执行一次。
子查询的结果被用于主查询。

使用规则：

在WHERE 和 HAVING 子句中都可以使用子查询。

*、子查询必须用括号扩起。
*、子查询应该在比较条件的右边。
*、在子查询中的ORDER BY 子句不需要，除非执行TOP-N分析。
TOP-N分析：（在一些表里求出最怎么怎么怎么样（最好、做多...）的几个人）。
*、对单行子查询使用单行比较操作符，多行子查询使用多行比较操作符。

可以在子查询中使用组函数。

子查询的分类：

单行单列子查询
单行操作符：>,<,=，>=,<=,<>
一定返回一行

多行单列子查询

单行多列子查询
返回零行或多行

多行操作符：
 in 等于列表中的任何值。（不能用NOT IN）
 ANY 与子查询返回的每个值进行比较。(小于是小于最大的，大于是大于最小的)
 
 select employee_id,last_name,job_id,salary from smployees
 where
 salary < any(select salary from employees where job_id='IT_PROG')
 and job_id <> 'IT_PROG';

 ALL（小于是小于最小的，大于是大于最大的）  

select e.employee_id,e.last_name,e.salary

from employees e,

(select department_id,min(salary) m from employees

group by department_id )d

where e.department_id=d.department_id

and

e.salary=d.m；
查询每个部门薪水最少的员工的资料。

多行多列子查询

替换变量：

临时存储值：

&  （生命周期）：单次引用中，不需要声明。如果替换字符或日期类型，最好用单引号扩起。

使用范围：
where
order by
列表达式
表名
整个SELECT 语句中。

&& （生命周期）：整个会话（session连接）,不需要声明。

define（生命周期）：整个会话，预先声明，使用时用&引用声明的变量。

define column_name(变量名) 查看变量命令。
undefine 变量名 清除变量
define variable=用户创建的CHAR类型的值：define 变量名=值；

accept（生命周期）：整个会话，预先声明，可以客户化提示信息，使用时用&引用声明的变量。
定义：accept   变量名   number/char/date   prompt   '提示信息内容'
ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format]
[DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]

例：accept a char prompt '请输入员工的雇佣时间(yyyy-mm-dd):'

hide
例：accept a char prompt 'input a:' hide
  

set verify(环境变量) off;关闭调试命令（关掉替换过程）
set verify(环境变量) on;打开调试命令(可以看到替换过程)

环境变量：

ECHO 显示回显

HEADING {OFF/ON}是否显示列标题；

ARRAYSIZE{20/n}每一次从查询得到的返回量的大小。

FEEDBACK{OFF/ON}回馈，反馈信息。

LONG{80/n}on/text} LONG类型

LINESIZE 行的宽度。
 
SET LINESIZE n(最好是在200之内)

PAGESIZE ：设置页的大小。SET PAGESIZE N
 
wrap{off/on} 折行 

SET 修改
SHOW 显示

格式化命令：

COLUMN[column option]可以设置字段或字段别名的格式。
COLUMN last_name HEADING employee|name  '|'代表换行。
col 字段名 查看命令

CLE[AR]：清除列的格式

HEA[DING] TEXT：设置列标题

FOR[MAT] FORMAT：格式化显示列的值，对字符和数字有效，对日期无效。
column salary justify left format $999,999.00       justify left:左对齐。
col manager_id format 999999999
限制字符串的长度有A+数字限制
限制数字的长度有9，有几为9就限制成几位。

NOPRINT/PRINT    NOPRINT：把一个字段从输出上屏蔽掉（返回但不显示）。
col 字段名 noprint/print.

NULL如果有NULL值，显示什么。
col name null 'on employee'

TTITLE[text/off/on]设置报表的表头
BTITLE[text/off/on]设置报表的表尾
做报表的时候要先想好PAGESIZE的大小。

BREAK ON [REPORT_ELEMENT]
压制重复值的显示。只能跟一个字段名才有效。
例：
select department_id,last_name
from employees
where rownum<30
order by 1,2;

break on department_id

otn.oracle.com/cn
www.oracle.com/cn

www.itpub.net入门与认证版 ora-600
www.oracle.com.cn
www.cnoug.org  ora-600

做脚本文件的过程：

变量定义accept
环境变量设置SET
格式控制命令
SPOOL
使用变量的SQL
SPOOL OFF
清除格式控制
重置环境变量
释放变量