PL/SQL学习笔记

基本概念：
                                                       
数据库对象：                                         
                                                          
表                                                   
                                                      
约束条件：保证数据完整性。                            
                                                   
视图：虚表，命名的查询语句。                        
                                                     
索引：加速查询（加快查询的速度）。                 
                                                    
序列：一串连续递增或递减的数字，步长相同，（代理键）。
                                                                                                   
同义词：一个对象的另外一个叫法（对象的别名）。                                                                                                
                                                                                             
存储过程：用于操作

函数：用作复杂运算的。用于计算。                                                                                         
                                                                                               
触发器：由事件触发的存储过程。                                                                                         

包：

数据库安全：

1、用户

2、方案或模式(Schema)：是用户所对应的对象的集合。用户名等于方案名

3、权限

4、角色：权限组，一组权限。
                                                      
5、配额(quota)：允许被使用的空间。用户可以在表空间上可以使用的空间。                                            
                                               
端口：2030

环境变量

-ORACLE_BASE基本目录

-ORACLE_NAME 当前的主目录

-ORACLE_NLS33
使用US7ASCLL字符集时不用设

-PATH 路径

基本的SQL SELECT 语句

口令中的第一个字符不能为数字。

语句：

（一）查询：SELECT
数据操作语句：DML（数据的插入INSERT、删除DELETE、修改UPDATE、合并MERGE）

（二）合并：把一个表中的数据合并到另一个表中去，如果数据在原表中存在做UPDATE，否则INSERT（9I独有）。

（三）事务控制语句：COMMIT 提交、ROLLBACK 回滚、

SAVEPOINT 存储点（与 ROLLBACK 搭配使用）在回滚的时候可以回滚到某个存储点上。否则回滚到最初起点上。

（四）数据定义语句：对对象操作。TRUNCATE 清除表中所有数据 /CREATE 创建 /DROP 删除 /ALTER 修改 

（五）权限控制语句（DCL）：GRANT 授予权限 /REVOKE 移除权限

 

SELECT：

SELECT 查询列表 FROM 数据源；

*&* SQL命令必须加分号。

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK；
修改用户   解锁
给HR解锁

CONNECT(conn) HR/HR（密码） 用HR用户连接数据库。 
*&*（不是SQL命令 是 SQLPLUS 命令）不用加分号

 

DESCRIBE(desc) DEPARTMENTS
关键字  表名

描述表命令（SQLPLUS 命令）

SQL语句说明：

（1）语句文本的书写不区分大小写。（但字符串在作为值的时候要注意大小写）
（2）语句可以写单行也可以写多行。
（3）关键字不能缩写或跨行。
（4）语句通常被分多行书写。
（5）缩进被用于提高语句的可读性。

 

数字和日期都可以使用数学运算符建立表达式。
+，-，*，/ <>不等于

日期可以加减数字，数字默认为天。

日期不能加日期，但日期可以减日期。

字符不能加减。

 

定义空（NULL）值

空值出现在表达式中会导致整个表达式的值为空。

NVL（字段名，将要赋予的值）函数
作用：将空值转换成其他有ASCLL码的值。

annual_salary年薪

别名

可以加中文的字段别名。

如果想强制地改变列名的大小写，可以在别名的定义时加上双引号,列名有空格时也要在列名上加双引号。
例：
select lastname as "employees name" from employees;

spool +路径；
保存命令（将显示保存）

 

 

连接操作符：
 
 ||

select lastname || 'work in' || department_id from tablename;

select last_name || '''s salary is ' || salary 员工月薪 from employees;
     ~~~ 
     在单引号中还要使用单引号的话，就必须使用两个单引号来实现一个单引号的功能。 

文本字符串：

*可以代表字符、数字或是日期。
*当代表字符或日期的时候用单引号括起来，数字不需要。

 

 

在查询时默认显示所有的行，包括有重复值的行。

DISTINCT 消除重复行关键字，放在整查询列表的最前面。

作用范围：整个查询列表的组合。

消除重复行后会按字段的特性，做升序排列。（执行过程：先排序，再消除重复）

select distinct department_id,job_id from employees;

 

SQLPLUS 与 SQL 的关系

SQL     *是一种语言
 *ANSI标准
 *关键字不能缩写
 *用于操作数据库中的数据和表的定义
  
SQLPLUS命令的功能：

*描述表的结构
*编辑SQL语句

查询 SQLPLUS 命令

help + 命令

SQLPLUSW 在 WINDOWS 下运行的分析器。
 

登陆ISQLPLUS

（1）先到服务中启动OracleOraHome92HTTPServer

（2）在浏览器中输入：http://wnj:7778/isqlplus
       ~~~~~~~~~~~~~~~ 
       URL(网页中的地址)
      
 

SQLPLUS 命令：

与文件相关的命令：
  spool + 路径
 .
 .
 .
 .
   spool off

save
把当前内存中的语句保存为文本文件。

run 或  /
运行当前内存中的语句

clear buffer（cl buff）
清空当前内存中的语句

start @
读取并执行

get
读取不执行

 

编辑命令：

list
列出一条语句
*表示当前行

change
修改命令
原来c/jj/kk
c/jjj/xxx

input
在当前行之后插入一行新的数据

append
在当前行中插入新的东西

n
写数字显示对应行

delete
del + 回车 删除当前行
del 1 3 删除第一到第三行

edit

l,c,i,a,n,d,e

 

查看当前用户

SHOW USER

默认的日期格式
DD-MON-RR
日-月-年

日期可以进行比较；

字符可以进行比较（以字母的ASCLL码比较）；

IN（set）或NOT IN 匹配任何列表中的值；

LIKE 模糊匹配字符串值；

IS NULL 是否空值；
IS NOT NULL 是否不为空；

BETWEEN 可以做数字、日期和字符的比较。

 

通配符

%
S_mith

WHERE first_name like 's/_%'escape'/';
    解释这个符号后的下划线为正常的字符。
    如果不加，将被视为通配符作用的下划线。

'_'只能通配一个字符
主要用于通配固定位数的字符。例如查询月收入五位数以上的员工。五位就可以用'_'来查询。

 

逻辑操作符（用在WHERE子句中）

AND

OR

NOT

先执行 NOT ，再执行 AND 最后执行 OR。

 

ORDER BY

ORDER BY子句在 SELECT 语句的最后。 

ASC：升序
DESC：降序

空值作为无穷大来处理。

rownum 显示行数量约束的关键字（在结果中可以做代理键使用）；

可以按照查询列表中序号进行排序。

系统在用户写出查询列表的同时就赋予每个列名一个序号，升序赋予。
例：
SELECT name,phone,adress from.........;
          1      2          3

 

单行函数：对单行数据进行计算并返回一个值的函数。

*修改数据项
*接受参数返回一个值。
*对每行进行操作。
*每行返回一个结果。
*可以修改数据类型
*可以嵌套

character字符类型函数：

LOWER()强制小写

UPPER()强制大写

INITCAP()每个单词首字母大写

可以用在WHERE子句中。

CONCAT（‘’，‘’）连接函数
    {
 SUBSTR(string,a[,b])返回string的一部分，a和b以字符为单位。

 SUBSTRB(string,a[,b])返回string的一部分，a和b是以字节为单位。

 SUBSTRC(string,a[,b])返回string的一部分，a和b是以UNICODE完全字符为单位。

 SUBSTR2(string,a[,b])返回string的一部分，a和b是以UCS2代码点为单位。

 SUBSTR4(string,a[,b])返回string的一部分，a和b是以UCS4代码点为单位。

    以上函数都是返回string的一部分，从字符位置A开始，长为B个字符。如果A是0，那它就被认为是1（字符串的开始位置）。如果A是正数，那么字符从左边开始数。如果是负数，则从STRING的末尾开始，从右边数。如果B不存在，那么缺省是整个字符串。如果B小于1，将返回NULL。如果A或B使用了浮点数，那么该数值首先被节取成一个整数，返回类型与STRING相同。
    }

 

{
 LENGTH(string)
 LENGTHB(string)
 LENGTHC(string)
 LENGTH2(string)
 LENGTH4(string)
以上函数返回string的长度。因为CHAR类型的值是填充空格的，所以如果string是CHAR数据类型，那么末尾的空格算在长度之内。如果string是NULL，函数返回NULL。
}

 

{
 INSTR(string1,string2[,a][,b])   返回string1中包含string2的位置。a和b以字符为单位。
 
 INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以字节为单位。 

 INSTRC(string1,string2[,a][,b])返回string1中包含string2的位置。a和b是以UNICODE完全字符为单位。

 INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以UCS2代码点为单位

 INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置a和b是以UCS4代码点为单位。
以上函数返回string1中包含string2的位置。从左边开始扫描string1,起始位置是A。如果A为负数那么从右边开始扫描。第B次出现的位置将被返回。A和B缺省都为1，即返回在string1中第一次出现string2的位置。如果string2在A和B的规定下没有找到那么就返回0。位置的计算是相对于string1的开始位置的，而不关A和B的取值。
}

 

LPAD（列名,数字,‘要补上的字符’）左补位

RPAD（列名，数字，‘要补上的字符’）右补位

TRIM（‘child_str’ FROM ‘parents_str’）将连续子串（只能有一个字符）从主串的两边截取出来，区分大小写。
默认为截取空格。

LTRIM（）左截取

RTRIM（）右截取

ascii(x)函数，返回'X'字符的十进制数，即X的ASCII码值。

chr(x)函数，返回ASCII码为X的字符。

length(x)函数，求串X的长度，与之相似的是lengthb(x)函数，用在多字节字符中。

replace(x,y[,z])函数，返回值为将串X中的Y串用Z串替换后的结果字符串。若省略Z参数，则将串X中为Y串的地方删除。

soundex(x)函数，返回串X的语音描述，这个描述由4个字符组成，说明串X的声音表示形式发音，有时在只知道一个名字的发音而不知道拼写情况下或许能用到。
例：select soundex('smith') from dual; 返回值为：S530.
 
translate(x,y,z)函数，返回将X串中每个字符按它在Y串中出现的位置翻译成Z串中相应位置的字符后的结果，相当与替换。
例：select translate('this is an example','my is','@#$%^&') from dual;

NLS函数
除了NCHR，这些函数都是以字符类型为参数返回字符类型值。

CONVERT(string,dest_charset[,source_charset])

将输入string转换为指定字符集dest_charset。source_charset是输入值的字符集——如果它没有被指定，则缺省为数据库字符集。输入值可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB和NCLOB类型。返回值为
VARCHAR2类型。如果dest_charset中没有输入字符串中的一个字符，将会使用一个代替字符（由dest_charset定义）

NCHR(X)

返回数据库国家字符集中值为X的字符。NCHR(X) 等价于CHR(x USING NCHAR_CS).

NLS_CHARSET_DECL_LEN(byte_width,charset)

返回一个NCHAR值的声明宽度(以字符为单位).byte_width是该值以字节为单位的长度charset是该值的字符集ID。

NLS_CHARSET_ID(charset_name)

返回指定字符集charset_name的数字ID。为charset_name指定“CHAR_CS”将返回数据库字符集的ID，为charset_name指定“NCHAR_CS”将返回数据库国家字符集的ID。如果charset_name是一个无效字符集名，将返回
NULL。NLS_CHARSET_ID和NLS_CHARSET_NAME是互为反函数。

NLS_CHARSET_NAME([charset_id])

返回指定字符集ID charset_id 的名字。如果 charset_id 是一个无效字符集ID，将返回NULL;

NLS_INITCAP(string[,nlsparams])

以字符串中每个单词第一个字符大写而单词中其余字母小写的形式返回string.nlsparams指定了一个与该会话缺省的不同的排序次序。如果没有指定该参数，NLS_INITCAP与INITCAP相同。nlsparams应该采取下面的形式：
'NLS_SORT=sort',其中sort是一个语言排序序列。

NLS_UPPER(string[,nlsparams])

以大写形式返回string,不是字母的字符不受影响。如果没有指定nlsparams,NLS_UPPER与UPPER相同。

NLS_LOWER

以小写形式返回string,不是字母的字符不受影响，如果没有指定nlsparams，NLS_LOWER与LOWER相同。

NLSSORT(string[,nlsparams])

返回用于排序string的字符串字节。所有值都被转换为字节字符串，这样在不同数据库之间就保持了一致性。
如果没有指定nlsparams,那么就会使用会话中缺省排序序列。

TRANSLATE(string USING {CHAR_CSINCHAR_CS})

TRANSLATE...USING 将输入string参数转换为数据库字符集（指定CHAR_CS）或数据库国家字符集（指定NCHAR_CS）.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2类型。如果指定CHAR_CS，返回类型为VARCHAR2，如果指定NCHAR_CS,返回类型为NVARCHAR2。TRANSLATE...USING是CONVERT功能的子集。
如果输入值包含UCS2字符或反斜线符号要使用UNSTR函数。

例：
SQL> select translate('asd' using NCHAR_CS) from dual;--数据库国家字符集

TRANSL
------
asd

SQL> select translate('asd' using CHAR_CS) from dual;--数据库字符集

TRA
---
asd

UNSTR(s)

返回转换为数据库UNICODE字符集的字符串。s可包含escaped UCS2 代码点字符。它由一个反斜线符号加上十六进制代码点数字组成。因此，要在字符串中包含一个反斜线符号就必须使用双反斜线符号(//).UNISTR与
TRANSLATE...USING相似，差别是它仅能转换为UNICODE，而且可以包含escaped 字符。

general
//number
//conversion
//date

多行函数：对多行数据（一组数据）进行计算并返回一个值的函数。
number数字类型函数

ABS(x) 函数，此函数用来返回一个数的绝对值。

ACOS(x)函数，返回X的反余弦值。X范围从1到-1，输入值从0到派，以弧度为单位。

ASIN(x)函数，返回X的反正弦值。X范围从1到-1，输入值从-PI/2到PI/2，以弧度为单位。

ATAN(x)函数，返回X的反正切值。输入值从-PI/2到PI/2，以弧度为单位。

BITAND(x,y)函数,返回X和Y的与结果。X和Y必须为非负整数。注意没有BITOR函数，但是在UTL_RAW包中有用      于RAW值的位操作符。

CEIL(x)函数，用来返回大于或等于X的最小整数。

COS(x)函数，返回x的余弦值。x是以弧度表示的角度。

COSH(x)函数，返回X的双曲余弦。

EXP(x)函数，与power(x,y)函数类似，不过不用指明基数，返回E的X次幂。E=2.71828183...

FLOOR(x)函数，用来返回小于或等于X的最大整数。

LN(x)函数，返回x的自然对数。x必须大于0。

LOG(x,y)函数，返回以X为底Y的对数。底必须是不为0和1的正数，Y是任意正数。

MOD(被除数，除数)求余函数，如果除数为0，则返回被除数。

POWER(x,y)函数，返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数。

ROUND(x[,y])函数，返回舍入到小数点右边Y位的X值。Y缺省为0，这将X舍入为最接近的整数。如果Y是负数，       那么舍入到小数点左边相应的位上，Y必须为整数。

SIGN(x)函数，此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1，
            若为0则仍返回0，有点像把模拟量数字化的意思。  

SIN(x)函数，返回X的正弦。x是以弧度表示的角度。

SINH(x)函数，返回x的双曲正弦。

SQRT(x)函数，返回x的平方根，x不能是负数。

TAN(x)函数，返回x的正切。x是以弧度表示的角度。

TANH(x)函数，返回x的双曲正切。

TRUNC(x[,y])截取值函数，Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置

WIDTH_BUCKET(x,min,max,num_buckets) 只能在SQL语句中使用。

使用WIDTH_BUCKET可以根据输入参数创建等长的段。范围MIN到MAX被分为num_buckets节，每节有相同的大小。返回X所在的那一节。如果X小于MIN，将返回0，如果X大于或等于MAX，将返回num_buckets+1.MIN和MAX
都不能为NULL，num_buckets必须是一个正整数。如果X是NULL，则返回NULL。

 

时间类型函数：（date）

内部存储格式：
世纪、年、月、日、小时、分钟、秒

默认格式是：DD-MON-RR。

SYSDATE 返回当前的系统时间。

SELECT SYSDATE FROM DUAL；

对日期的数学运算

SELECT （SYSDATE-HIRE_DATE）/7 FROM TABLENAME WHERE ROWNUM;
  数字列

ADD_MONTHS(date,x)函数，返回加上X月后的日期DATE的值。X可以是任意整数。如果结果的月份中所包含的   日分量少于DATE的月份的日分量，则返回结果月份的最后一天。如果不小于，则    结果与DATE的日分量相同。时间分量也相同。

CURRENT_DATE 以DATE类型返回会话时区当前的日期。这个函数同SYSDATE相似，除了SYSDATE不管当               会话时区。

CURRENT_TIMESTAMP[(precision)] 以TIMESTAMP WITH TIMEZONE 类型返回会话时区当前的日期。如果    指定precision，它指返回秒数的精度，缺省为6。

DBTIMEZONE 返回数据库的时区。 
 
LAST_DAY（日期） 指定日期所在月份的最后一天的日期，这个函数可用来确定本月还有多少天。

LOCALTIMESTAMP[(precision)] 以TIMESTAMP类型返回会话时区的当前日期。如果指定precision，它指    返回秒数的精度，缺省为6 。

MONTHS_BETWEEN(离当前比较近的日期date1,以前的日期)  两个日期之间相差的月数（以日作为最小单位来计算的）。返回是相差的月数。如果date1和date2的日分量相同，或者这两个日期都分别是所在月的最后一天，那么返回结果是个整数。否则，返回结果包含一个分数，以一个月31天计算。

NEW_TIME(d,zone1,zone2)函数，当时区zone1中的日期和时间是D的时候，返回时区zone2中的日期和时间。
              返回类型为DATE。zone1和zone2是字符字符串，另外的时区可在ORACLE9I中
              通过查询V$TIMEZONE_NAMES得到。  

NEXT_DAY （日期，星期几） 指定日期后将要遇到的后七天的某一天的日期。

ROUND（日期，‘MONTH/YEAR’） 四舍五入得到新的日期。 保留位置是月和年

SESSIONTIMEZONE 返回当前会话的时区。返回类型是一个时区偏移或时区片名的字符字符串。如果指   定格式，则与 ALTER SESSION 语句中的格式相同。

SYS_EXTRACT_UTC(datetime) 从提供的DATETIME中以UTC(Coordinated Universal Time)返回时间。
    DATETIME必须包含一个时区。

SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE 返回当前的日期和时间。当在分布式SQL语句中使用的时  候，返回本地数据库的日期和时间。

TRUNC（日期，‘MONTH/YEAR’） 截取

TZ_OFFSET(timezone) 以字符字符串返回提供的timezone和UTC之间的偏移量。timezone可以被指定为时   区名或'+/-HH:HI'格式表示的偏移量。也可使用SESSIONTIMEZONE和   DBTIMEZONE函数，返回格式为'+/-HH:HI'。

字符字符串   时区
AST    大西洋标准时
ADT    大西洋夏令时
BST    白令标准时
BDT    白令夏令时
CST    中央标准时
CDT    中央夏令时
EST    东部标准时
EDT    东部夏令时
GMT    格林威治平均时
HST    阿拉斯加夏威夷标准时
HDT    阿拉斯加夏威夷夏令时
MST    Mountain标准时
MDT    Mountain夏令时
NST    纽芬兰标准时
PST    太平洋标准时
PDT    太平洋夏令时
YST    YuKon标准时
YDT    YuKon夏令时 
    

日期和日期时间算术

运算  返回类型
结果 

d1-d2  NUMBER
返回D1和D2之间相差的天数。该值是一个数值，其小数部分代表一天的几分之几。

dt1-dt2  INTERVAL
返回DT1和DT2之间的时间间隔。

i1-i2  INTERVAL
返回i1和i2之间的差距。

d1+d2  N/A
非法——仅能进行两个日期之间的相减。

dt1+dt2  N/A
非法——仅能进行两个日期之间的相减。

i1+i2  INTERVAL
返回i1和i2的和。

d1+n  DATE
在D1上加上N天作为DATE类型返回。N可以是实数，它包含一天的几分之几。

d1-n  DATE
从D1上减去N天作为DATE类型返回。N可以是实数，它包含一天的几分之几。

dt1+i1  DATETIME
返回DT1和I1的和。

dt1-i1  DATETIME
返回DT1和I1之间的差距。

i1*n  INTERVAL
返回I1的N次方。

i1/n  INTERVAL
返回I1除以N的值。

表中注：
D1和D2指日期值；
DT1和DT2指日期时间值；
I1和I2指时间间隔值；
N指数字值。

 

 

显示转换：（conversion）

TO_NUMBER(char[,'format_model'])  字符转换到数字类型
       

TO_DATE(char[,'format_model'])  字符转换到日期类型
  格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。

 

TO_CHAR(date[,'format_model'[,nlsparams]])
  第二个参数可以省略，不指定格式，按系统默认格式输出。
  区分大小写。
  使用FM（在格式控制符前添加）符号可以去掉空格或是首位的零。
  如果指定了NLSPARAMS，则它控制返回字符串的月和日分量所使用的语言。格式为：
  'NLS_DATA_LANGUAGE=language',language指需要的语言。
  例：
  select to_char(sysdate,'FMyyyy-mm-dd') from dual;
  格式控制符的类型：
    YYYY 四位的年
    YEAR 年的拼写      
    MM  2位数字的月
    MONTH 月的全名
    MON 月名的前三个字符
    DY 星期名的前三个字符
    DAY 星期名的全称
    DD 2位的天 
  时间格式控制符：
    HH24:MI:SS AM
    HH12:MI:SS PM
    
  通过“”来实现加入特殊字符的格式控制符。
  SELECT TO_CHAR(SYSDATE,'FMyyyy"年"mm"月"dd"日"') from dual;

  DDSPTH
  ~~ 
  DD是格式控制符。 
  TH是序数词，将日期转换成英文的序数词拼写。
  SP是基数词，将日期转换成英文的基数词拼写。

TO_CHAR(NUM[,'format_model'[,nlsparams]])转换数字

将NUMBER类型参数NUM转换成VARCHAR2类型。如果指定FORMAT，它会控制整个转换。
如果没有指定FORMAT，那么结果字符串中将包含和NUM中有效位的个数相同的字符。NLSPARAMS用来指定小数点和千分符及货币符号。它的格式可为：'NLS_NUMERIC_CHARS=' ' dg ' 'NLS_CURRENCY= ' 'string' ' '
d和g分别代表小数点和千分符。STRING代表货币符号。  

数字格式控制符：
  9 代表一位数字（替换符。有，数字显示；没有。不什么都显示。） 
  0 代表一位数字（有数字，显示；没有，强制显示0。） 
  $ 美圆符号
  L 本地货币
  . 小数点
  , 千分符
  B 当整数部分为0时，将整数部分填充为空格。 例：B999
  MI  返回带有后继符号而不是前导负号的负数值，正数值将带有后继的空格。999MI 
  S 返回一个前导符号或后继符号，正数为+，负数为-。 S9999 或 9999S
  PR  使用尖括号返回负数。正数将有前导或后继空格。999PR
  D 在指定位置返回一个小数点。两侧的9的个数指定了最大的位数。99D9
  G 在指定位置返回千分符，G可以在FORMAT_model中出现多次。9G999G9
  C 在指定位置返回ISO货币符号。C可以在FORMAT_model中出现多次。C99
  L 在指定位置上返回本地货币符号。 L99
  V 返回一个被乘以10的N次方的数值，这里N是V后边9的个数。99V99
  EEEE 使用科学记数法返回该数值。9.99EEEE
  RM 使用大写的罗马数字表示返回该数值。 RM
  rm 使用小写的罗马数字表示返回该数值。 rm
  FM 返回不含前导和后继空格的数值。 FM99.09
  
  格式控制符位数一定要大于或等于NUMBER的位数，不能小于。

用RR解决跨世纪问题：
     小于50的认为是1950-2050
     大于50的认为是1951-1999   

数字和日期是不能相互转换的。

ASCIISTR(string) 

返回只包含有效的SQL字符和斜线的字符串。string中的任何无效的字符将被转换为一个相当的数字，在之前加上斜线。
   
BIN_TO_NUM(num[,num]...) 

将一位矢量转换位相当的数字。它的参数是一系列逗号隔开的NUMS，每一个都必须是0或1。
例如BIN_TO_NUM(1,0,1,1)将返回11，因为11的二进制表示是1011。当使用分组集合和GROUP BY 子句时该函数很有用。     CHARTOROWID(x)函数，

将字符串转换成一个ROWID类型的值，注意格式必须采用ROWID数据类型格式，即“数据块号:行序号:数据文件号”。
  
COMPOSE(string) 

以相同字符集中完全规格化Unicode形式返回string.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB类型。
  

DECOMPOSE(string)

返回一个Unicode字符串。它是string的规范分解。string可以是CHAR、VARCHAR2、 NCHAR、NVARCHAR2、CLOB或NCLOB类型。      

FROM_TZ(timestamp,timezone)

返回一个TIMESTAMP WITH TIMEZONE 类型值。它将TIMESTAMP(没有时区信息)和提供的TIMEZONE组合在一起。

HEXTORAW(string)

 将由STRING表示的二进制数值转换为一个RAW数值。STRING应该包含十六进制值。STRING中的每两个字符表示结果RAW中的一个字节。HEXTORAW和RAWTOHEX互为反函数。 

NUMTODSINTERVAL(x,unit)

将X转换为INTERVAL DAY TO SECOND 值，X应该是一个数字。UNIT是一个字符字符串（可以是CHAR、VARCHAR2、NCHAR或NVARCHAR2），且是'DAY'、'HOUR'、'MINUTE'、'SECOND'之一。unit是不区分大小写的，返回值的缺省精度为9。

NUMTOYMINTERVAL(x,unit)

将X转换成INTERVAL YEAR TO MONTH 值，X应该是一个数字。UNIT是一个字符字符串（可以是CHAR、VARCHAR2、NCHAR或NVARCHAR2），且是'YEAR'或'MONTH'之一。unit是不区分大小写的，返回值的缺省精度为9。 

REFTOHEX(refvalue)

返回一REF refvalue的十六进制表示。

RAWTOHEX(rawvalue)

将RAW类型值rawvalue转换为一个十六进制表示的字符串。rawvalue中的每个字节转换为一个双字符的字符串。

RAWTONHEX(rawvalue)
 
将RAW类型值rawvalue转换为一个十六进制表示的字符串。rawvalue中的每个字节转换为一个双字符的字符串。
RAWTONHEX返回值是NVARCHAR2类型而不是VARCHAR2类型。

ROWIDTOCHAR(rowid)函数，将ROWID类型值转换成字符串。与CHARTOROWID互为反函数。

ROWIDYONCHAR(rowid) 与ROWIDTOCHAR类似，返回类型是NCHAR，而不是CHAR。

TO_CLOB(string)

将string转换为CLOB。string可以是文字或另一个LOB列。如果参数包含NCHAR数据，它被转换为数据库字符集

TO_DSINTERVAL(string[,nlsparams])

将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为 INTERVAL DAY TO SECOND 类型。如果
选定nlsparams,则nlsparams只能包含小数点和千分位字符的NLS_NUMERIC_CHARARCTERS表示。

TO_LOB(long_column)

将long_column转换成LOB。这个函数用于将LONG和LONG RAW分别转换为CLOB和LOB。

TO_MULTI_BYTE(string)

返回将所有单字节字符替换为等价的多字节字符的STRING。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用。否则，STRING不会进行任何处理而被返回，与TO_SINGLE_BYTE 互为反函数。
TO_NCHAR

和TO_CHAR相似，结果是属于国家字符集而不是数据库字符集。

TO_NCLOB(string)

将STRING转换为NCLOB。STRING可以是文字或另一LOB列。

TO_SINGLE_BYTE(string)

返回将所有双字节字符替换为等价的单字节字符的STRING。。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用。否则，STRING不会进行任何处理而被返回，与TO_MULTI_BYTE 互为反函数。

TO_TIMESTAMP(string[,format[,nlsparams]])

将其参数CHAR或VARCHAR2类型string转换成TIMESTAMP类型。

TO_TIMESTAMP_TZ(string[,format[,nlsparams]])

将其参数CHAR或VARCHAR2类型string转换成 TIMESTAMP WITH TIMEZONE 类型。

TO_YMINTERVAL(string)

将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为INTERVAL YEAR TO MONTH 类型。
TO_YMINTERVAL与TO_DSINTERVAL相似，除了它不能使用NLS参数作为参数并返回YEAR TO MONTH时间间隔而不DAY TO SECOND 时间间隔。

to_label(x[,y])函数，按照格式Y将字符串X转换成MLSLABEL类型的一个值，若默认格式为Y，则按照默认格式进  行转换。

dump(w,[x[,y[,z]]])函数,用来返回字符串EXPR的数据类型，内部的存储位置和字符长度。
      dump(expr,return_datatype,start_position,length).
      return_datatype是指定返回返回位置用什么方式表示，可以为8、10、16、17，分别表示      用八进制、十进制、十六进制和字符类型。

例：
select dump(last_name,8,3,2) ,dump(last_name,10,3,2) ,
dump(last_name,16,3,2) ,dump(last_name,17,3,2) from employees
where lower(last_name) = 'smith';

greatest(x,y,...)函数，返回参数列表中的最大值。其参数的类型是由第一个参数决定的，可以为数值型、日期型  、和字符型等，后面的参数被强制转换成此种数据类型。
  进行字符串的比较时，其大小由字符在字符集中的数值决定，在字符集中的数值大，则此字  符就大，对于字符串，此函数返回VARCHAR2类型。  

least(x,y,......)函数，返回列表参数中的最小值。

与上两个函数类似的有：
greatest_lb(x,y,....)函数和least_lb(x,y,....)函数，分别求出列表中的标签的最大下限和最小上限，其参数必须为
MLSLABEL 类型，返回值为 RAW MLSLABEL类型。

user 函数，返回当前用户的数据库用户名。
uid函数，返回唯一标识当前用户的整数。
这两个函数在完整性约束检查时会用到，可以当作引用变量一样引用它们。

userenv(x)函数，返回当前会话的一些信息，由X指定返回何种信息。在写一个指定应用的审计测试表或决定为当            前会话指定哪种语言时会用到，但完整性约束时不能用。
参数：
 entryid  返回有效的审计条目标识   
 label  返回当前会话的标签
 language  以“语言.字符集”形式返回所用的语言和字符集
 sessionid  返回正在使用的审计会话号
 terminal  返回当前会话终端所用的操作系统

 

嵌套函数：

单行函数可以嵌套任意层；
嵌套函数从最深层开始执行。

 

通用函数：

BFILENAME(directory,file_name)
返回操作系统中与物理文件file_name相关的BFILE位置指示符。directory必须是数据字典中的一个DIRECTORY类型对象。

COALESCE（，，，，可以多个参数）返回从左到右的第一个非空的表达式。如果所有表达式都为NULL，则返回NULL。

EMPTY_BLOB/EMPTY_CLOB
返回一个空的LOB位置指示符。EMPTY_CLOB返回一个字符位置指示符，EMPTY_BLOB返回一个二进制位置指示符。

EXISTSNODE(XMLType_instrance,Xpath_string)
使用Xpath_string中的路径，确定由XMLType_instrance标识的XML文档的TRAVELSAL是否返回任何节点。这个函数将返回一  个NUMBER值，如果没有节点则为0，如果有节点则为大于0。

EXTRACT(XMLType_instrance,Xpath_string)
应用Xpath_string之后，返回由XMLType_instrance标识的XML文档的一部分。

GREATEST(expr1[,expr2]...)
返回其参数中最大的表达式。在进行比较之前，每个表达式都被隐式转换为EXPR1的类型，如果EXPR1是字符类型，则使用非填充空格字符比较，返回结果为VARCHAR2类型。

LEAST(expr1[,expr2]...)
返回其参数中最小的表达式，其余同上。

NVL(EXPR1,EXPR2)
类型必须匹配，如果EXPR1是NULL，则返回EXPR2，否则返回EXPR1。返回值与EXPR1类型相同，除非EXPR1是字符类型，在这种情况下将返回VARCHAR2类型。这个函数用于确保查询记录集中不包含NULL值。

NVL2(EXPR1,EXPR2,EXPR3)
如果EXPR1是NULL，则返回EXPR2，否则返回EXPR3。返回值与EXPR2类型相同，除非EXPR2是字符类型，在这种情况下将返回VARCHAR2类型。

SYS_CONNECT_BY_PATH 返回列值的从根到结点的路径，它仅在层次查询中有效。

SYS_CONTEXT(namespace,parameter[,length])
返回与namespace的内容相关联的patameter的值。使用DBMS_SESSION.SET_CONTEXT过程设置参数和namespace.返回值是VARCHAR2类型，如果没有指定length,则最大长度是255字节。

SYS_DBURIGEN
产生一个URL用于从数据库中提取XML文档。

SYS_GUID
以16位RAW类型值形式返回一个全局唯一的标识符。

SYS_TYPEID(object_type)
返回指定类型object_type的类型ID。

SYS_XMLAGG
将几个XML文档或文档片段组合为一个文档。

SYS_XMLGEN
返回一个基于数据库中数据的XML文档片段。

TREAT(expr AS [REF] [schema.]type)
TREAT用于改变一个表达式的声明类型。仅可以将声明类型改变为给定表达式的子类型或超类型。以类型[schema.]type返回
expr，如果指定了REF，则返回REF。

UID
返回一个唯一标识当前数据库用户的整数，UID没有参数。

VSIZE(x)返回X内部表示的字节数。

NULLIF(a,b)如果A等于B返回NULL，如果不等于返回B。

DUMP(expr[,number_format[,start_position][,length]])
返回一个包含EXPR内部表示信息的VARCHAR值，如果没有指定NUMBER_FORMAT，则返回结果以十进制形式返回。如果指定了start_position和length，则返回从start_position开始，长为length字节的字符串，缺省是返回整个表达式。
所返回的数据类型是内部数据类型编码的对应数字。

NUMBER_FORMAT

格式  返回结果
8  8进制符号
10  10进制符号
16  16进制符号
17  单字符

编码   数据类型       有效于
 
1   VARCHAR2     ORACLE7
2   NUMBER      ORACLE7
8   LONG      ORACLE7
12   DATE      ORACLE7
23   RAW      ORACLE7
24   LONG RAW     ORACLE7
69   ROWID      ORACLE7 
96   CHAR      ORACLE7
112   CLOB      ORACLE8
113   BLOB      ORACLE8
114   BFILE      ORACLE8
180   TIMESTAMP     ORACLE9i 
181   TIMESTAMP WITH TIMEZONE   ORACLE9i
182   INTERVAL YEAR TO MONTH   ORACLE9i
183   INTERVAL DAY TO SECOND   ORACLE9i
208   UROWID      ORACLE9i
231   TIMESTAMP WITH LOCAL TIMEZONE   ORACLE9i 

USERENV[option]
基于option返回包含有关当前会话信息的VARCHAR2值。

函数的行为

选项值    USERENV(option)的行为

'OSDBA'    如果当前会话将OSDBA角色的设置打开了，则返回'TRUE',否则返回'FALSE',注意返    回值是VARCHAR2类型，而不是BOOLEAN类型。

'LABEL'    仅对TRUSTED ORACLE 中有效，返回当前会话标志。

'LANGUAGE'   返回当前会话所使用的语言和地域，以及数据库字符集，这是NLS参数，返回形式是
    LANGUAGE_TERRITORY.CHARACTERSET.

'TERMINAL'   返回当前会话所使用终端的操作系统标识符。对于分布式的SQL语句，返回的是本地    会话的标识符。

'SESSIONID'   如果初始化参数AUDIT_TRAIL被设置为TRUE，那么将返回审计会话标识符。在分布    式SQL语句中，USERENV('SESSIONID')是无效的。

'ENTRYID'   如果初始化参数AUDIT_TRAIL被设置为TRUE，那么将返回可用的审计项标识符。在    分布式SQL语句中USERENV('ENTRYID')是无效的。

'LANG'    返回语言名称的ISO缩写符号。它的格式比USERENV('LANGUAGE')要短。

 

例：
select USERENV('TERMINAL'),USERENV('LANGUAGE') from dual;

USERENV('TERMINA USERENV('LANGUAGE')
---------------- ----------------------------------------------------
WNJ              SIMPLIFIED CHINESE_CHINA.ZHS16GBK

条件表达式：

CASE表达式（简单CASE）

语法：

CASE 表达式 WHEN  条件1 THEN 返回值1
     WHEN  条件2 THEN 返回值2
   .
   .
   .
     WHEN  条件n THEN 返回值n
ELSE 返回值
END  
  

DECODE函数

语法：

DECODE（
 条件,比较值1，返回值1
      比较值2，返回值2
      .
       .
      .
      比较值n，返回值n
      返回值（不满足条件时）        
）

select last_name,salary,
 decode( trunc(salary/2000,0),//条件
  0, 0.00,//比较值1，返回值1
  1, 0.09，
  2, 0.20，
  3, 0.30，
  4, 0.40，
  5, 0.42，
  6, 0.44，
   0.45
       )   TAX_RATE
from employees
where department_id=80;

 

 

从多表中显示数据：

SQL（老版本的）

等值查询
SELECT TABLE1.COLUMN,TABLE2.COLUMN FROM TABLE1,TABLE2 WHERE TABLE1.COLUMN1=TABLE2.COLUMN2;//自然连接
使用AND操作符增加查询条件

使用表的别名来简化查询。
提高查询功能。

SELECT E.ID，D.ID FROM EMPL E，DEP D WHERE E.NAME=D.NAME；
       ~~     ~
       表别名
多表等值连接查询
为了连接N个表，至少需要N-1个连接条件。

 
非等值查询

使用 BETWEEN AND 查询近似值作为连接条件的多表结果。
WHERE E.SALARY BETWEEN J.LOW AND J.HIGH

 

外连接查询

SELECT T1.COL,T2.COL FROM WHERE T1.COL(+)=T2.COL;左外连接
    所有T2的T1信息。 
SELECT T1.COL,T2.COL FROM WHERE T1.COL=T2.COL(+);右外连接
    所有T1的T2信息。
为了看到与连接条件不匹配的数据，就必须得用外连接。

 

 

自连接

通过表的别名来创建虚拟逻辑表,进行自连接查询。
select worker.last_name || 'work for' || manager.last_name
from employees worker,employees manager
where worker.manager_id=manager.employee_id;

 

9I适应性连接：

select t1.col,t2.col
from table
 
cross join t2 //交叉连接

natural join t2//自然连接:把两表中所有等值的字段都作为连接条件（但这些连接条件不用写）。
     从两个表中选出连接列的值相等的所有行。
     如果两个列的名称相同，但数据类型不同；或是类型相同，意义不同都会出错。

join t2 using (column_name);基于自然连接，只有在USING中出现的，才作为连接条件（在USING中列名前一定不能加前缀）。
 
join t2 on (t1.col=t2.col);基于ON的自然连接。等值、非等值或自连接都可以实现。

left|right|full outer join t2 on(t1.col=t2.col);

 select e.last_name,d.department_name,l.city
 from employees e
 left outer join departments d on e.department_id=d.department_id
        right outer join locations l  on d.location_id=l.location_id;
*&* 可以连续做左连接或右连接的操作。
 full outer join 忽略连接条件，把要查询的列的所有行全显示出来。 
  

笛卡尔乘积（多表查询容易产生的错误）形成原因：
*、忽略连接条件；
*、连接条件不正确；
*、笛卡尔乘积是由第一个表的所有行和第二个表的所有行联合形成的；
*、为了避免笛卡尔乘积的产生，一定要在WHERE条件中正确写出连接条件。
set linesize 160;设置显示行的行数。

 

 

用字函数产生的总计

对多行的计算产生单行的结果。

组函数用语对每个组的行集进行运算，每个组产生一个结果。

AVG([DISTINCT/ALL]col)只能用与数字。只能对多行的数据进行运算，不能在这个函数中做单行的数学运算。

CORR(x1,x2)

返回表达式X1和X2组成的集合的相关系数。在保证所有行中的X1和X2都不为NULL之后结果通过
COVAR_POP(x1,x2)/(STDDEV_POP(x1)*STDDEV_POP(x2))得到。

COUNT([DISTINCT/ALL]col)所有非空字段的行数。

COVAR_POP(x1,x2)返回表达式x1和x2组成的集合的人口协方差结果通过(SUM(x1*x2)-SUM(x2)*SUM(x1)/n)/n得到，n是没有  NULL项的集合的数目。

COVAR_SAMP(x1,x2)返回表达式X1和X2组成的集合的相同协方差。

CUME_DIST 返回一组值中一个值的累积分布。

DENSE_RANK返回有序分组的行中一行的秩，秩是从1开始的连续的整数。

GROUP_ID()返回一个唯一数字值用于在GROUP BY 字句中辨别组。

GROUPING_ID返回一个数字对应于一行的GROUPING位矢量。

MAX([DISTINCT/ALL]col)可以用于任何类型，当用于日期类型时代表最晚。忽略空值。字符类型时候，比较字符串首字母的  ASCLL值。

MIN([DISTINCT/ALL]col)可以用于任何类型，当用于日期类型时代表最早。忽略空值。字符类型时候，比较字符串首字母的  ASCLL值。

PERCENTILE_CONT这个函数是一个反分布函数，它假设了一个连续分布模式。

PERCENTILE_DISC一个反分布函数，它假设了一个离散分布模式。

RANK 返回给定行的秩。秩不必是连续的，因为相同的行有相同的秩。

REGR这些函数(REGR_SLOPE,REGR_INTERCEPT,REGR_COUNT,REGR_R2,REGR_AVGX,REGR_AVGY,REGR_SXX
REGR_SYY,REGR_SXY)得到了双集合的普通最小衰减线。

SUM([DISTINCT/ALL]col)返回选择列表项目的总和，只能用于数字。

STDDEV([DISTINCT/ALL]col) 标准方差

STDDEV_POP(col)计算人口标准差并返回人口方差的平方根。

STDDEV_SAMP(col)计算累计标准差并返回例子方差的平方根。

VAR_POP(x)返回提系列数字在去除了NULL值之后的人口不同。由(SUM(x*x)-SUM(x)*SUM(X)/COUNT(x))/COUNT(x)得到。

VAR_SAMP(x)返回一系列数字在去NULL值之后的范例不同。由(SUM(x*x)-SUM(x)*SUM(X)/COUNT(x))/(COUNT(x)-1)得到。

VARIANCE([DISTINCT/ALL]col)偏移方差，返回COL的方差。

 

语法：
select col,group function(col) from table where  条件  group by col;
GROUP BY
必须：出现在查询列表中的一个字段，但没有出现在函数中，那么这个字段必须要出现在GROUP BY 中。
可以：出现在GROUP BY 子句中的字段可以不出现在查询列表中。
先排列，再运算。

WHERE 子句中不能使用 group function。

限制组必须使用 HAVING 子句。

语法：
select col,group function from table
where 条件//可以没有条件限制
group by col
having group_condition //组过滤，在过滤以后，再进行分组计算。
order by col;                                                                                                                       
                                                                
组函数嵌套最多只能有两层。
select max(avg(salary))
from employees
group by employee_id;

select * from tab;查询一个用户中的所有表。

 

子查询：

语法：
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
清除格式控制
重置环境变量
释放变量

数据操作语句：

插入：INSERT INTO TABLE（字段1，字段2....）VALUES（值1，值2....）
 一次插入只插入一行。字符和日期值需要单引号扩起。
 插入空值：
  方法一：隐示插入，插入时省略列名系统就会默认省略的列为NULL。
  方法二：显示插入，在插入时指定列的值为空。

 注：（1）值的个数不能少于列名的个数。
     （2）注意非空属性的列，不能插入空值。
WITH CHECK OPTION 视图约束。 
UID 当前数据库用户ID
  
在插入日期的时候最好用TO_DATE来控制输入格式。

可以创建一个脚本用 &变量名 的形式来用一个插入语句实现多行的插入（在值列表里用 &变量名）。

插入中的子查询：将另一个表中的内容都插入被插入的表中。
insert into sales_reps(id,name,salary)
select employee_id,last_name,salary from employees where employee_id>100;
可以在子查询中做运算后插入到被插入表中。
不要使用VALUES子句。
在子查询中的列数必须匹配 INSERT 语句中的列数。

 

删除

DELERT FROM TABLE WHERE 条件；
删除所有符合 WHERE 条件的行。

基于子查询的删除。

注意及联删除。

 

更新：
UPDATE TABLE SET 列名1=值1，列名2=值2.....WHERE 条件；
如果更新错误，要用ROLLBACK回滚。

利用子查询更新另外表中的数据，在 SET 后和 WHERE 后都可以利用子查询语句。

更新的时候要注意参照完整性约束。
子表的外键字段值必须是父表主键字段值的真子集。

 DEFAULT+‘’  默认值
在创建表的时候用，在第三个参数的位置上。
例：
CREATE TABLE A
（
 C1 CHAR（10） DEFAULT
  .
  .
  . 
） 
在修改的时候，如果先给DEFAULT赋值的话，
可以直接用 列名=DEFAULT，使列名回复为默认值。

 

 

MERGE语句

提供了对表根据条件进行插入或者更新的能力。
如果行存在则执行UPDATE，如果不存在则执行INSERT。

避免了单独的修改。
提高了性能，更便于使用。
对于数据库应用很有益。

语法：别名AL
MERGE INTO table_name（目的表） table_alias USING (table/view/sub_query)//数据来源（可以用子查询）  alias(别名)
on(连接条件)
WHEN MATCHED THEN UPDATE SET(关键字) (修改目的表)
目的表AL.col1=原表AL.col_var1
目的表AL.col2=原表AL.col2_var2
WHEN NOT MATCHEN THEN（关键字）
INSERT (目的表AL.COL_LIST)
VALUES(原表AL.COL_VARS);（插入原表）

create table newtable_name(新表) as select * from oldtable_name(原表) where 1=0;
 将原表中的结构复制到新表中，但具体的数据项不进行复制。

 

事务（transaction）：由被逻辑组织在一起的多个DML语句的构成。
COMMIT：提交。
ROLLBACK：回滚。
SAVEPOINT：存储点，只在事务执行过程中有效，事务结束即被释放。

事务的组成：
一组相同改变特性的DML语句；
一个DDL：数据定义语句；
一个DCL：权限控制语句；

建立存储点：
例：SAVEPOINT A；
   .
 .
 .
ROLLBACK TO A；

 

事务的开始：
开始于第一个DML SQL语句执行时开始

结束的时候是在：
*、一个COMMIT或ROLLBACK被执行的时候。
*、一个DDL或DCL语句被执行（自动提交）注意*&*!（隐式）
*、用户退出SQLPLUS（隐式） 
*、系统崩溃（隐式）

语句级回滚：

ORACLE 服务器执行隐式的存储点。

 创建和管理表

对象：

表（TABLE）基本的存储单位，由行和列组成。

表名和列名（使用规则）：
必须是字母开头；
必须是1-30的字符长度；
只能包括A-Z，a-z,0-9,_,$,#；
在同一个用户下不能头重名的对象；
不能是ORACLE的保留字；

创建需求
必须有：
 CREATE TABLE 权限；
 足够的存储空间；
语法：
CREATE TABLE [SCHEMA（方案）.]TABLE
（COL DATATYPE [DEFAULT默认值][]）；

当前用户所有的表
select table_name from user_tables;

当前用户所有的对象：
desc user_objects

当前用户对象的别名：
select * from cat;

字段类型：
 VARCHAR(size)   变长字符串类型
 CHAR(size) 定长字符串类型
 NUMBER(p,s)p位整数，s位小数  
 DATE 
 DATETIME 秒级最多可以到小数点后的9位
 TIMESTAMP 带有小数秒的日期
  TIMESTAMP WITH TIME ZONE 带时区的类型
  TIMESTAMP WITH LOCAL TIME ZONE 带时区的并会进行时区转换的类型（同一时间在不        同地区看到的时间）

 INTERVAL YEAR TO MONTH 按年和月的间隔存储的类型
  INTERVAL ‘123-2’ TEAR(3) TO MONTH
    
 INTERVAL DAY TO SECOND 按天、小时、分和秒的间隔存储的类型
  INTERVAL 
 LONG 变长的长字符串类型
 CLOB 字符类型 4GIGABYTES 
 RAW  二进制类型与CHAR对应
 LONG RAW 二进制类型与LONG对应
 BLOB
 BFILE 以文件的形式存储在操作系统中
 ROWID 表中行的唯一地址（行地址）

方案：一个用户所有对象的命名集合。

如果想访问其他用户或方案的表要加上用户或方案作为前缀。

必须指明：
 表名称；
 列名，列类型和长度；
用户表：
 被用户创建和维护的一些表；
 包括了用户自己的信息；
数据字典表：
 被ORACLE数据库创建和维护的一些表；
 包括了数据库的信息；

CTAS（子查询建表）：

CREATE TABLE table_name
[(col,coltype,..)]
as subquery(子查询);

创建的表的列的数目匹配子查询的列的数目。
使用子查询的列的名字和默认值定义表。
注：
*、被创建表的字段名要遵循如果没有字段别名和子查询中没设置别名的话，使用子查询中的列名；
   如果有别名，使用别名；如果有字段列表（[(col,coltype,..)]），在被创建的表中使用字段列表；
*、有字段列表与子查询的列要匹配。
*、当没有字段列表的时候，而在子查询中有表达式的时候一定要在表达式后要加上别名。 
*、只会把属性当中的非空属性复制过来，其他的比如约束条件、关联...都不会复制过来。

使用ALTER TABLE 语句可以：

*、在表中增加一个新列

语法：ALTER TABLE table add (col datatype [default],...,....);
新增加的字段一定是放在表的最后。

*、修改表字段的类型和长度

ALTER TABLE table modify (col datatype [default],...,....);
对默认值的修改只会影响到新插入的行。
如果字段下有值的话，类型的修改成功率很小（要修改数据类型，要修改的列必须为空，即没有数据项）。
CHAR类型不能修改长度。

*、删除表字段

ALTER TABLE table DROP COLUMN (COLUMN_NAME_LIST);

9I2版可以修改列名 

*、SET UNUSED 设置字段为不可用。

原理：清楚掉字典信息（撤消存储空间），不可恢复。
可以使用 SET UNUSED 选项标记一列或者多列不可用。
使用DROP SET UNUSED 选项删除被被标记为不可用的列。

语法：
ALTER TABLE table SET UNUSED (COLlist多个) 或者  ALTER TABLE table SET UNUSED COLUMN col单个;
 
ALTER TABLE table DROP UNUSED COLUMNS;

删除表：

删除关联：drop table table_name cascade;

改对象名：
 RENAME 对象原名 TO 要改的对象名；
注：
    必须是对象的所有者才能进行改名的操作。

 

截取：
不能回滚；
删除表中所有数据；
释放存储空间；
语法： 
TRUNCATE TABLE 表名称；

DELETE 也可以删除所有行，但：
可以回滚。
不释放存储空间。

 

给表加注释：COMMENT
comment on table table_name is '注释内容';

 

 

约束条件：
如果经常用到约束条件的话，最好自己命名。
当定义约束的时候可以将定义的语句作为CREATE TABLE 中的参数的一部分来完成。

表级别约束定义：
CONSTRAINT 约束名 约束条件（字段名）

约束在表上强制了规则。
如果有参照的花，约束防止表的删除。
ORACLE支持的约束条件：
NOT NULL 非空
         特点：唯一一个只能在列级定义的约束条件。                                                                           
UNIQUE 唯一
  允许有空值（空值不做比较）；
 特点：当创建约束的时候，系统会自动创建对应其的索引。
PRIMARY KEY 主键
   特点：当创建约束的时候，系统会自动创建对应其的索引。
  在一个表中只允许一个主键。  
FOREIGN KEY 外键 
 外键参照的一定是主表的主键或唯一键；
 保证子表外键字段的值一定是主表中的被参照字段值的真子集；
 当主表字段被参照的时候，其值不允许被直接删除。
CONSTRAINT 约束名 FOREIGN KEY （外键字段名） REFERENCES 主表名（主表字段名）;

 如果在字段列表中定义外键就可以不写 FOREIGN KEY 关键字。
 如下格式：CONSTRAINT 约束名 REFERENCES 主表名（主表字段名）;

ON DELETE CASCADE  当主表的行被删除的时候，要删除子表中参照主表的行。
ALTER TABLE TABLE_NAME DROP (PK) CASCADE CONSTRAINTS;把作为主键的字段也同时删除了。

ON DELETE SET NULL 当主表的行被删除的时候，转换子表中的参照值为空。

CHECK

定义一个每行都必须满足的条件。
CREATE TABLE table_name
(
  ....
salary number(10,2),
CONSTRAINT 约束名 CHECK(SALARY>0),
  .... 
);

约束的使用：

约束的命名：给约束命名或者ORACLE服务器将使用SYS_Cn的格式为约束命名。

创建时期：

在创建表的同时或者在建表之后。

定义级别：

可以在表级定义或列级定义。

在数据字典中可以查看约束。

使用ALTER TABLE 语句：

*、添加或者删除约束条件，但是不能修改约束条件。
 就算列名上已经有约束条件，还可以继续添加约束条件的。
添加： ALTER TABLE table_name ADD [CONSTRAINT ] 约束名 约束条件(column);
删除： ALTER TABLE table_name drop constraint 约束名; 
 ALTER TABLE table_name PRIMARY KEY CASCADE;删除主键的时候，不用约束名。
*、启动或禁用约束条件
 ALTER TABLE table_name Disable constraint 约束名; 禁用
 ALTER TABLE table_name ENABLE constraint 约束名; 启用
            
*、通过MODIFY添加 NOT NULL 约束条件（因为NOT NULL为列级约束，只能用MODIFY添加）。
ALTER TABLE table_name MODIFY(col type NOT NULL);

查看约束条件：
//desc user_constraints
OWNER 拥有者；
CONSTRAINT_NAME 约束名称
CONSTRAINT_TYPE 约束类型
 
SEARCH_CONDITION  check的条件
 

select constraint_name, constraint_type,search_condition,status
from user_constraint where table_name='b';

 

 

                                          
视图 （VIEW） 一个或多个表的数据集的逻辑表示（虚表，不存储数据）。
视图不能提高查询的性能。
分类：
 简单
  数目：一个
  函数：不包含
  分组数据：不包含
  可以做DML操作
 复杂
  数目：一个或多个
  函数：包含
  分组数据：包含
  不一定能做DML操作

视图也可以用DESC描述。
 
创建视图：

CREATE [or replace(修改视图)] [force/noforce] VIEW view_name(col coltype ,.......)
as
subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY[CONSTRAINT constraint]];

USER_VIEWS 关于视图的字典

修改视图：

CREATE OR REPLACE 原视图名 （字段列表）
AS 子查询；

包含：
GROUP BY
DISTINCT
ROWNUM
不能对视图进行删除操作；

包含：
GROUP BY
DISTINCT
ROWNUM
通过表达式定义的列
不能对视图进行修改操作；

包含：
GROUP BY
DISTINCT
ROWNUM
通过表达式定义的列
在视图中没有包含基表中的 NOT NULL 列
不能对视图进行插入操作；

使用视图的原因；

为了限制对数据的访问；
为了使复杂的查询变得简单；
提供了数据的独立性；
提供了对相同数据的不同显示；

 

使用WITH CHECK OPTION 子句创建视图

创建视图时通过 WITH CHECK OPTION 子句确保执行的DML语句不会引起数据不出现在视图上。
在对视图做DML操作的时候，一定要符合WHERE子句中的条件。
CREATE OR REPLACE VIEW empvu20 as select * from employees
where check option constraint [empvu20_ck];

 

WITH READ ONLY
不可以进行DML操作；

删除视图：

DROP VIEW view_name;

行内视图：
是一个在SQL语句中使用的带有别名的子查询，该子查询放在FROM之后；
  

TOP-N:
select [col_list],rownum rank(排名)
from (select [col_list] from table_name order by top-n_col)
where rownum<=n;

序列（SEPUENCE）产生的顺序数字，单向递增或单向递减，且步长相同。

索引（INDEX）用于提高查询性能。

同义词（SYNONYM）对象的别名。
create public synonym e for hr.employees;

user_synonyms;

创建同义词要有权限，访问的时候也需要权限。

序列：

自动产生的唯一值；
一个共享的对象；
典型的用法是作为主键的值；
insert into 给主键提供值。
替代了应用的代码；
通过将序列CACHE（预先生成一部分序列号，放入到内存中）到内存中，可以加速对序列的访问。

CREATE SEQUENCE sequence_name
  [increment by n]//步长
  [start with n]//起始点
  [maxvalue n/nomaxvalue]//递增
  [minvalue n/nominvalue]//递减
  [cycle/nocycle]//循环
  [cache n/nocache];//n为预先生成序列号的个数，默认为20。

查询序列：
user_sequences
last_number 序列将要产生的下一个号是多少；

select sequence_name,min_value,max_value,increment_by last_number from user_sequences;

伪列：NEXTVAL 引用下一个可用的序列值，不同的用户每次引用都会获得一个唯一的值。
      CURRVAL 得到当前的值（刚被领走的号）。
在CURRVAL执行前必须先通过NEXTVAL得到一个初始的值。

序列名.NEXTVAL/CURRVAL
  
序列发生间隙是正常的，保证唯一即可。

序列的修改：
   ALTER SEQUENCE sequence_name 
   increment by
  maxvalue
  cycle    
  cache;

start with 不能修改。

删除序列：

drop  SEQUENCE sequence_name;

 

索引：

一个方案中的对象；
被ORACLE服务器用来加速对表的查询；
通过使用快速路径访问方法快速定位数据；
与表独立存放；
被ORACLE服务器使用和维护。

一定是 WHERE 条件的才有可能使用索引。

手动创建索引:
CREATE INDEX index_name on table_name (col_name);

考虑创建索引的情况：
*、包含了大量不同值的列；
*、包含了大量空值的列；
*、一个或者多个列经常被一起出现在 WHERE 条件中或者作为连接的条件出现；
*、表的数据量很大，而且对表的查询经常是得到表中数据的2%到4%（少量数据）.

不应该创建索引的情况：
*、一个很小的表；
*、列很少被用于查询的条件；
*、表上的大多数查询是得到大量数据的；
*、表中的数据经常发生变动；
*、要被索引的列被作为条件表达式的一部分。

查看：
user_indexes得到索引的定义和唯一性。

user_ind_columns 得到索引的名称，表名和列名。
select ic.index_name,ic.column_name,ic.column_position,
 ic.uniquenes
from user_indexes ix,user_ind_columns ic
where ic.index_name=ix.index_name and ic.table_name='table_name';

删除索引：
DROP INDEX index_name;
为了删除索引，必须拥有索引或者拥有DROP ANY INDEX 权限。 

 

控制用户的访问

数据库的安全性

系统安全性：
 系统权限(system privilege)，获得访问数据库的能力。
  超过一百个
  创建新用户：
   CREATE USER user_name IDENTIFIED BY password;
  删除用户
  删除表
 授予权限：
  GRANT priv_list TO user/public/role(角色);
  grant create session ,create table,create sequence to user_name;   
  在授予建表权限的同时也应该赋予存储空间。
 分配配额：ALTER  USER user_name QUOTA nM ON space_name;

数据安全性：
 对象权限(object privilege)，获得维护数据库的能力。
 
 每种对象的权限都不相同。
 对象的所有者拥有对象的所有权限。
 对象的所有者可以将自己的对象权限赋予其他人。   

 GRANT object_priv_list [(col_list)]
 ON owner.object TO user/role/public
 [WITH GRANT OPTION];--将权限授予用户的同时，该用户也拥有了授予其他用户对象权限的功能。
 （及联授予）会导致及联移除。

 移除权限：
 REVOKE priv_list/all  ON object FROM user;
 
方案：数据库对象的集合，包括表、视图、序列.....。

角色：
 CREATE ROLE role_name;
 GRANT priv_list TO role_name;
 GRANT role_name TO user_list/role_list;

修改口令：
方法 （1）ALTER USER user_name IDENTIFIED BY password;
 （2）password + 回车
 
USER_SYS_PRIVS 当前用户的系统权限。
USER_ROLE_PRIVS 当前用户的角色权限。
USER_tab_privs_made 用户对象被授予的他人的信息。

 

使用集合操作

UNION

select employee_id,job_id from employees
union
select employee_id,job_id from job_history;

两个表的并集，但不显示重复行。
执行的时候要先排序再剔重，所以结果集是有序的。

union all

也是两个表的并集，而且显示重复行。
语法同上。

intersect

select employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;

minus

select employee_id,job_id from employees
minus
select employee_id,job_id from job_history;

e-j=e-e与j的交集；
j-e=j-j与e的交集；

注：
在select 列表中的表达式必须有同样的数目和类型。

 匹配 SELECT 语句
 select employee_id,job_id,salary from employees
 union
 select employee_id,job_id,0      from job_history; 

括号可以用拉修改序列的执行顺序。

ORDER BY 子句：
 只能在整个集合的最后出现；
 可以按照第一个 SELECT 语句中的列名，别名或者位置号排序。  

 

 

GROUP BY 子句的增强

CUBE 操作符的 GROUP BY

在 GROUP BY 子句中使用 ROLLUP 或者 CUBE 来产生分组小计；

ROLLUP 分组产生包括规则的分组结果和小计的结果的组合；
GROUP BY [ROLLUP]（col_name_list）
ROLLUP:
a       ab abc

  abc
 ab ab
a a a
all all all

CUBE 分组产生包括 ROLLUP 产生的结果和交叉分组小计。
cube:

a ab abc

a ab abc
all a ab
 b ac
 all bc
  a
  b
  c
  all

GROUPING 函数
参数一定是在CUBE或ROLLUP里进行分组排序的字段或表达式之一。

通过1或0来判断结果集中的空值是由于本身列的值是空的，还是由于使用CUBE或ROLLUP产生的空值。
1 代表是由于分组产生的空值，没有参与分组。
0 代表是由于列本身产生的空值，参与了分组，但分组中没有包含它。

GROUPING SETS

可以使用 GROUPING SETS 在同一个语句中定义多个组集。

只需要访问一次基表。
不需要写很复杂的UNION语句。
GROUPING SETS 子句中组合的元素越多，语句的执行性能就越好。

group by GROUPING SETS((abc),(ab),(bc),(a),(b))

组合列：

是一个列的组合，在分组计算时被作为一个单元处理。

 

高级子查询

成对子查询：
行内视图的性能比成对子查询的性能高。

相关子查询：
主查询的字段在子查询里做条件（特征）。
主查询先执行，取出第一条数据，把该数据传入子查询做比较，返回查询结果给主查询，主查询根据这个结果再做查询
依次类推
直到主查询中没有可查询列为止。

 

EXISTS操作符

EXISTS 操作符测试子查询的结果是否存在；
返回 TRUE 或 FALSE
查询机制：

如果一个子查询找到了结果：

在内部子查询中不在继续执行
条件被设为TRUE

如果一个子查询没有找到结果：
条件被设为FALSE

select col_list from table_name tab_alias

where exists (select 'x' from table_name where col=tab_alias.col);
用的是相关子查询

NO EXISTS操作符

和NOT IN 相对应，速度要快，性能好。

UPDATE 中的相关子查询

update emp e
set department_name in(select d.department_name from departments d where e.department_id=d.department_id);

delete 中的相关子查询

层次查询

select [level],column,expr from table [where condition]
[start with]起点（自底向上/自顶向下）
[connect by prior + 主键/外键=外键/主键]//看你往哪个方向查

自顶向下 左边放主键，右边放外键

select employee_id,last_name,salary,job_id,manager_id
from employees
start with manager_id is null
connect by prior employee_id=manager_id；

自底向上 右边放主键，左边放外键

level（伪列）
层次的级别：不固定值。

使用 level 和 LPAD 层次化格式的显示

修剪分支

Oracle 9i 对DML和DDL语句的扩展

多表插入的 INSERT 语句

insert .... select 语句可以被用来在单个DML语句中向多个表插入数据。

多表插入语句：

无条件INSERT

条件 ALL INSERT

条件 FIRST INSERT

轮巡 INSERT