|
|
相关文章推荐
|
慷慨的铁链 · C++ string类 用法详解 及 ...· 1 年前 · |
|
|
儒雅的碗 · 当我使用Vue的transition和v-i ...· 1 年前 · |
|
|
大力的手链 · immintrin.h的替代方案-腾讯云开发 ...· 2 年前 · |
|
|
聪明的花生 · 「datatable翻页按钮」相关问答|文档 ...· 2 年前 · |
|
|
冲动的香菇 · js实现多滚动条同步滚动（两个以上） - 掘金· 2 年前 · |
2. 小数位置（scale）
当s(scale)为正数时，Oracle就对小数点右边的s个数字进行舍入。 精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；
如果s>p，小数点右边至少有s-p个0填充。
当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。 精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|
小数位数的合法值为-48～127，其默认值取决于是否指定了精度：
如果没有知道精度，则小数位数的取值 默认 可以落在最大的取值区间，即 -48~127【Oracle 11g r2上，不只为127，但貌似小于140】 上。
如果指定了精度，且 指定了 小数位数 为n，则 小数位数的取值可以落在取值区间0 ~n 上。
如果指定了精度，而没有写出小数位数，则 小数位数 默认为0（小数点右边一位都没有）。
例如，定义为NUMBER的列会存储浮点数（有小数），而NUMBER(38)只存储整数数据（没有小数），因为在第二种情况下小数位数默认为0. 注释：
1、
ORACLE数值类型详解--NUMBER、BINARY_FLOAT、BINARY_DOUBLE、BINARY_INTEGER、FLOAT
======================
======================
当s(scale)为正数时，Oracle就对小数点右边的s个数字进行舍入。 精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；
当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。 精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|
小数位数的合法值为-48～127，其默认值取决于是否指定了精度：
如果没有知道精度，则小数位数的取值 默认 可以落在最大的取值区间，即 -48~127【Oracle 11g r2上，不只为127，但貌似小于140】 上。
如果指定了精度，且 指定了 小数位数 为n，则 小数位数的取值可以落在取值区间0 ~n 上。
如果指定了精度，而没有写出小数位数，则 小数位数 默认为0（小数点右边一位都没有）。
例如，定义为NUMBER的列会存储浮点数（有小数），而NUMBER(38)只存储整数数据（没有小数），因为在第二种情况下小数位数默认为0. 注释：
1、
对于number(p,s)格式的数值，该数值中的哪些位数算为精度值位数呢？
回答该问题，首先我们要确定第 |s|位数字 为是哪一位？
当刻度s为负数时，Oracle就对小数点左边开始向前数的第|s|位数字是 第 |s|位数字， 并对该数字进行四舍五入 。例如， 对于数据类型为number(5,-3)的列，输入数值1267.9，则 Oracle就会判断出数值 1267.9的第 |s|位数字 为2，之后 对该数字2进行四舍五入，由于2小于5，所以 第 |s|位数字相邻左边一个数字1不变没有增一，而（四舍五入后） 从 第 |s| 位数字算起其右边的所有数字都置为 0 ，故最后实际存储到列里的值为1000（显示屏幕上的不是1000.0形式）。 因此，当刻度 s 为负数时，存入 数据类型为 number(p,s) 的列的最终值都是整数形式，无论你输入的是否带有小数。
如果scale是负数怎么样，表示左边整数位舍入几位：
create table t ( msg varchar2(12.),num_col number(5,-2) );
insert into t (msg,num_col) values ('123.45', 123.45 );//执行成功，保存的是100
insert into t (msg,num_col) values ('123.456', 123.456 );//执行成功，保存的是100
当刻度s为正数时，Oracle就对小数点右边开始向前数的第|s|位数字是 第 |s|位数字， 并对该数字右边相邻一位数字进行四舍五入 。例如， 对于数据类型为number(5,3)的列，输入数值4.5679，则 Oracle就会判断出数值 4.5679的第 |s|位数字 为7，之后 对该数字7右边相邻一位数字即9进行四舍五入，由于9大于5，所以 第 |s|位数字7变为8，而（四舍五入后） 第 |s| 位数字右边的所有数字都置为 0 ，故最后实际存储到列里的值为 4.568（ 显示屏幕上的不是 4.5680 形式 ）。再例如，
create table t ( msgvarchar2(12.), num_col number(5,2) );
insert into t (msg,num_col) values ( '123.456', 123.456);//执行成功，保存的是123.46
当确定了第 |s|位数字 为是哪一位后，我们从第 |s|位数字左边相邻的一个数字算起数p个数字，这p个数字就为 为精度值p所作用的范围。这也就是为什么 当刻度s为负数时 存入 数据类型为 number(p,s) 的列的最终值的位数最大（即 有效数位 ）可为 p+|s| ，而不是为p。例如， 例如， 对于数据类型为number(5,-3)的列，输入数值11111667.9，则最终值为11112000.
简而言之，
当刻度s为正数时，小数点左边的最大位数为p-s，无论此时输入的数值的小数点右边的位数是否达到s或说这与输入的数值的小数点右边的位数为多少无关。例如：
create table t ( msg varchar2(12.), num_col number(5,2));
insert into t (msg,num_col) values ( '1234', 1234 );//执行失败，要保留2位小数，那么整数位最多3位，现在是4位。
当刻度s为负数时，小数点左边的最大位数为p+|s|，且这与输入的数值的小数点右边的位数为多少无关。
P只能为正数。
当刻度s为正数时，
P<s时，写入不了值，提示ora-01438。
当刻度s为负数时，
P<|S|时，
如果输入的数值，其后小数点左边的位数不大于|S|，且最高位数字小于５，则存储的最终值都为0。因为例如， 对于数据类型为number(３,-5)的列，输入数值64.8（我们可以将之视为00000 0 0064.8，其中0的个数随便添加只要0**064.8的整数位数大于等于 |s| ），则 Oracle就会判断出数值 4.5679的第 |s|位数字 为0（即6左边第三个的那个零），之后 对该数字0进行四舍五入，由于0小于5，所以 第 |s|位数字相邻左边一个数字0不变没有增一，而（四舍五入后） 从 第 |s| 位数字算起其右边的所有数字都置为 0 ，故最后实际存储到列里的值为0。
如果输入的数值，其后小数点左边的位数大于|S|，则存储的最终值中除 第 |s|位数字左边所有数字不变外其他数字都置为0。因为例如， 对于数据类型为number(３,-5)的列，输入数值123456.7，则 Oracle就会判断出数值 123456.7的第 |s|位数字 为2，之后 对该数字2进行四舍五入，由于2小于5，所以 第 |s|位数字相邻左边一个数字1不变没有增一，而（四舍五入后） 从 第 |s| 位数字算起其右边的所有数字都置为 0 ，故最后实际存储到列里的值为100000。
如下SQL语句： create table t ( msg varchar2(12.), num_col number(5,2) ); insert into t (msg,num_col) values ( '123.456', 123.456 );//执行成功，保存的是123.46 insert into t (msg,num_col) values ( '1234', 1234 );//执行失败，要保留2位小数，那么整数位最多3位，现在是4位。 一般而言，Oracle NUMBER类型对大多数应用来讲都是最佳的选择。不过，这个类型会带来一些性能影响。Oracle NUMBER类型是一种软件数据类型，在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER类型相加，这要在软件中模拟。不过，浮点数没有这种实现。将两个浮点数相加时，Oracle会使用硬件来执行运算。 换而言之，将一些列的number列相加，没有将一系列float列相加来得快。因为float列的精度低很多，一般是6~12位。 比如：select sum(ln(cast( num_type as binary_double ) )) from t 比：select sum(ln(cast( num_type) )) from t 要快很多。ORACLE数值类型详解--NUMBER、BINARY_FLOAT、BINARY_DOUBLE、BINARY_INTEGER、FLOAT
Oracle的数据类型转换 to_char(date)
Oracle 时间差计算【附加日期格式说明】
======================
精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。
2. s<0
精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|
Value Datatype Stored Value
123.2564 NUMBER 123.2564
1234.9876 NUMBER(6,2) 1234.99
12345.12345 NUMBER(6,2) Error
错误原因 12345.12345 按后面的小数点必须保留两位 12345.12 这样总位数7位数字超过了6,相互冲突了。
同样的原理 如果设计数据为2234563，但是设置 NUMBER(6) 也会导致一个错误
1234.9876 NUMBER(6) 1235
12345.345 NUMBER(5,-2) 12300
1234567 NUMBER(5,-2) 1234600
12345678 NUMBER(5,-2) Error
123456789 NUMBER(5,-4) 123460000
1234567890 NUMBER(5,-4) Error
12345.58 NUMBER(*, 1) 12345.6
0.1 NUMBER(4,5) Error
0.01234567 NUMBER(4,5) 0.01235
0.09999 NUMBER(4,5) 0.09999
0.099996 NUMBER(4,5)
======================
Oracle 中 NUMBER 类型如果不指定长度和小数点精度 默认 是多长
在 Oracle 中Number类型可以用来存储0，正负定点或者浮点数，可表示的数据范围在1.0 * 10(-130) —— 9.9...9 * 10(125) {38个9后边带88个0}的数字，当Oracle中的数学表达式的值>=1.0*10(126)时，Oracle就会报错。
Number的数据声明如下：
表示 作用 说明
Number(p, s) 声明一个定点数 p(precision)为精度，s(scale)表示小数点右边的数字个数，精度最大值为38，scale的取值范围为-84到127
Number(p) 声明一个整数 相当于Number(p, 0)
Number 声明一个浮点数 其精度为38，要注意的是scale的值没有应用，也就是说scale的指不能简单的理解为0，或者其他的数。
定点数的精度(p)和刻度(s)遵循以下规则：
当一个数的整数部分的长度 > p-s 时，Oracle就会报错
当一个数的小数部分的长度 > s 时，Oracle就会舍入。
当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。
当s > p 时, p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入
oracle number 默认 百度
本文实例讲述了
oracle
存储过程、函数和触发器
用法
。分享给大家供大家参考，具体如下：
一、存储过程和存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
创建存储过程
用CREATE PROCEDURE命令建立存储过程。
create [or replace] procedure 过程名(参数列表)
PLSQL子程序体;
--给指定员工涨工资
create procedure addSal(empid in
number
)
psal emp.sal%type;
begin
select sal into psal from emp wh
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
指定表空间
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index tablespace TABLE_SPACE_NAME;
2.增加外键
alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
select constraint_name,table_name,constraint_type,status from user_constraints;
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper(\'&table_name\')
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper(\'&table_owner\') and c.table_name = upper(\'&table_name\')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
5、删除主键或外键
alter table TABLE_NAME drop constraint KEY_NAME;
6、建外键
单字段时：create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
REFERENCE 表名（） on delete cascade;
7、删除带约束的表
Drop table 表名 cascade constraints;
8：索引管理
<1>.creating function-based indexes
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
<2>.create a B-tree index
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql> maxextents 50);
<3>.pctfree(index)=(maximum
number
of rows-initial
number
of rows)*100/maximum
number
of rows
<4>.creating reverse key indexes
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql> next 200k pctincrease 0 maxextents 50) tablespace indx;
<5>.create bitmap index
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx;
<6>.change storage parameter of index
sql> alter index xay_id storage (next 400k maxextents 100);
7.allocating index space
sql> alter index xay_id allocate extent(size 200k datafile \'c:/
oracle
/index.dbf\');
<8>.alter index xay_id deallocate unused;
<9>、查看索引
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
<10>、查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper(\'&index_name\');
11、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 increment by 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
12、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
13、删除同其他表相同的行
delete from a where exits
(select \'X\' from b where b.no=a.no);
delete from a where no in (select no from b);
14、查询从多少行到多少行的记录(可以用在web开发中的分页显示)
select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b )
where row_id between 15 and 20
15、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for 表名;
16、填加注释
comment on table 表名 is \'注释\';
comment on column 表名.列名 is \'注释\';
17、分布式数据库,创建数据库链路
create [public] database link LINKNAME
[connect to USERNAME identified by PASSWORD]
[using \'CONNECT_STRING\']
可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间
数据库必须可以互访，必须各有各自的别名数据库
18、查看数据库链路
select * from all_db_links;
select * from user_db_links;
查询 select * from TABLENAME@DBLNKNAME;
创建远程数据库同义词
create synonym for TABLENAME@DBLNKNAME;
操纵远程数据库记录
insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);
update TABLENAME@DBLNKNAME set a=\'this\';
delete from TABLENAME@DBLNKNAME;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
19、数据库链路用户密码有特殊字符的时候，可以用双引号把密码引起来
create public database link dblink1 connect to db1 identified by \"123*456\" using \'db11\'
20.
oracle
8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。
<1>下面的语句可以进行总计
select region_code,count(*) from aicbs.acc_woff_notify
group by rollup(region_code);
<2> 对第1个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
----------------------
570 0 3
570 1 2
570 5 --此处小计了570的记录
571 0 10
571 1 2
571 12 --此处小计了571的记录
.....
100 --此处有总计
<3> 复合rollup表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
<4> 对第1个字段小计，再对第2个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
----------------------
100 --此处有总计
0 60 --对write_status＝0的小计
1 39 --对write_status＝1的小计
3 1 --对write_status＝3的小计
570 5 --此处小计了570的记录
570 0 3
570 1 2
571 12 --此处小计了571的记录
571 0 10
571 1 2
<3> 复合cube表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
<4>下面的语句可以按照rollup不同的字段进行小计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by region_code,rollup(write_status);
21.查询view的创建语句
sql>set long 1000
sql>select * from user_views where view_name=\'MY_VIEW_NAME\';
sql>select * from all_views where view_name=\'MY_VIEW_NAME\';
22、去除数据库中特殊字符
<1>.字符串字段中含有\"\'\",如果用来组合sql语句，会造成语句不准确。
比如：replace(f1,\'\'\'\',\'\')
<2>.字符串字段中含有\"\\t \\n\",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
比如：replace(f2,\'\\t\',\'\')
<3>.清除换行和回车
比如: replace(f2,chr(13)||chr(10),\'\')
23、如何在字符串里加回车或者tab键
在sqlplus中执行
sql>select \'UserId=1233111\'||chr(10)||\'AccId=13431\'||chr(9)||\'AccId2=11111\' from dual;
24、树形查询
create table zj(
bm
number
(8),
bmmc varchar2(20),
sjbm
number
(8)
insert into zj values(1,\'aaa\',0)
insert into zj values(11,\'aaa1\',1)
insert into zj values(12,\'aaa2\',1)
insert into zj values(111,\'aaa11\',11)
insert into zj values(112,\'aaa12\',11)
insert into zj values(113,\'aaa13\',11)
insert into zj values(121,\'aaa21\',12)
insert into zj values(122,\'aaa22\',12)
insert into zj values(123,\'aaa23\',12)
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by prior bm = sjbm
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by sjbm = prior bm
25、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\\complete\\force]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next
sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH(\'snap_to_html\',\'c\');
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
26、用户管理
create a user: database authentication
sql> create user juncky identified by
oracle
default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];
<1>.查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
<2>生成用户时指定缺省表空间
create user 用户名 identified by 口令 default tablespace 表空间名;
<3>重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
<4>查看当前用户的角色
SQL>select * from user_role_privs;
<5>查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
<6>查看用户下所有的表
SQL>select * from user_tables;
<7> alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
27、查看放在
ORACLE
的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,\'Y\')>0;
28、约束条件
create table employee
(empno
number
(10) primary key,
name varchar2(40) not null,
deptno
number
(2) default 10,
salary
number
(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;
关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束
29、查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper(\'&view_name\');
30、查看同义词的名称
SQL>select * from user_synonyms;
31、用Sql语句实现查找一列中第N大值
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
32 虚拟自段
<1>. CURRVAL 和 nextval
为表创建序列
CREATE SEQUENCE EMPSEQ ... ;
SELECT empseq.currval FROM DUAL ;
自动插入序列的数值
INSERT INTO emp
VALUES (empseq.nextval, \'LEWIS\', \'CLERK\',
7902, SYSDATE, 1200, NULL, 20) ;
<2>. ROWNUM
按设定排序的行的序号
SELECT * FROM emp WHERE ROWNUM < 10 ;
<3>. ROWID
返回行的物理地址
SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
33、对CLOB字段进行全文检索
SELECT * FROM A WHERE dbms_lob.instr(a.a,\'K\',1,1)>0;
34. 特殊字符的插入，比如\"&\"
insert into a values (translate (\'at{&}t\',\'at{}\',\'at\'));
35.表管理
<1>.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache]
<2>.copy an existing table
sql> create table table_name [logging|nologging] as subquery
<3> create table ... as 方式建表的时候，指定表参数
create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
as select * from b;
<4>.创建临时表
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
在
Oracle
中，可以创建以下两种临时表:
a 会话特有的临时表:
create global temporary table () on commit preserve rows;
会话指定，当中断会话时
ORACLE
将截断表
b 事务特有的临时表:
create global temporary table () on commit delete rows;
事务指定,每次提交后
ORACLE
将截断表（删除全部行）
临时表只在当前连接内有效
临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用
数据处理比较复杂的时候时表快,反之视图快点
在仅仅查询数据的时候建议用游标: open cursor for \'sql clause\';
pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
<6>.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
<7>.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile \'c:/
oracle
/data.dbf\');
<8>.move tablespace
sql> alter table employee move tablespace users;
<9>.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
<10>.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
<11>.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
37. 中文是如何排序的？
Oracle
9i之前，中文是按照二进制编码进行排序的。
在
oracle
9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序
38. 数据表中的字段最大数:
表或视图中的最大列数为 1000
39.
oracle
中的裸设备:
裸设备就是绕过文件系统直接访问的储存空间
40. 在
Oracle
服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context(\'userenv\',\'ip_address\') from dual;
如果是登陆本机数据库，只能返回127.0.0.1
41. 在
ORACLE
中取毫秒?
9i之前不支持,9i开始有timestamp.
9i可以用select systimestamp from dual;
42. 将N秒转换为时分秒格式？
set serverout on
declare
N
number
:= 1000000;
ret varchar2(100);
begin
ret := trunc(n/3600) || \'小时\' || to_char(to_date(mod(n,3600),\'sssss\'),\'fmmi\"分 \"ss\"秒\"\') ;
dbms_output.put_line(ret);
43、在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
44. not in的替代。
一般not in的效率比较低。特别是数据量大的时候，几乎不能执行。
用下面几种方式可以替换写法
比如要查询在fee_rev_info表中已经销户的用户（不在cm_user中的）(不过下面的例子不是很好，因为bill_id是cm_user的唯一索引）
select * from fee_rev_info where bill_id not in (select bill_id from cm_user)
<1> 用not exists
select * from fee_rev_info a where not exists (select \'p\' from cm_user b where b.bill_id = a.bill_id)
<2> 用外连接(+)
select a.* from fee_rev_info a,cm_user b
where a.bill_id = b.bill_id (+)
and b.bill_id is null
<3> 用hash_aj
select /*+HASH_AJ*/* from fee_rev_info where bill_id not in (select bill_id from cm_user)
45.怎么样查询特殊字符，如通配符%与_
假如数据库中有表 STATIONTYPE,STATION_571 STATION_572 ...
select * from tab where tname like \'STATION_%\'
会显示 STATIONTYPE,STATION_571 ... 可以用下面的语句
select * from tab where tname like \'STATION\\_%\' escape\'\\\'
46.如果存在就更新，不存在就插入可以用一个语句实现吗
9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select .... from dual的子查询。
语法
为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
MERGE INTO cm_user_credit
USING (select * from dual) ON (user_id =1302514690 )
when MATCHED then update set credit_value = 1000
when NOT MATCHED then insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,\'13857141218\',10070247,\'571\',1000);
47.怎么实现一条记录根据条件多表插入
9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果没有条件的话，则完成每个表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
48.如何实现行列转换
<1>、固定列数的行列转换
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
语文 数学 英语
student1 80 70 60
student2 90 80 100
语句如下：
select student,sum(decode(subject,\'语文\', grade,null)) \"语文\",
sum(decode(subject,\'数学\', grade,null)) \"数学\",
sum(decode(subject,\'英语\', grade,null)) \"英语\"
from table
group by student
<2>、不定列行列转换
c1 c2
--------------
1 我是谁
这一
类型
的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1
NUMBER
)
RETURN VARCHAR2
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
--例子：
create table okcai_1
user_id varchar2(10),
user_
number
varchar2(10),
user_num
number
(8)
user_id user_
number
user_num
---------------------
1 123 2
1 456 5
1 789 6
2 11 2
2 22 3
2 33 4
2 44 5
2 55 6
2 66 7
2 77 8
3 1234 1
3 5678 2
create or replace function get_col(
p_userId
number
,
p_col
number
) return varchar
v_tmp varchar2(255);
begin
select user_
number
||chr(9)||user_num into v_tmp
(select user_
number
,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) .... from okcai_1
create or replace function get_col(
p_userId
number
,
p_col
number
) return varchar
v_tmp varchar2(255);
begin
select user_
number
||chr(9)||user_num into v_tmp
(select user_
number
,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
select distinct user_id,get_col_new(user_id) from okcai_1;
49.怎么设置存储过程的调用者权限
普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure ...()
AUTHID CURRENT_USER
begin
50.
Oracle
有哪些常见关键字
详细信息可以查看v$reserved_words视图
51.怎么查看数据库参数
<1> show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
其中参数名是可以匹配的。
比如show parameter cursor ,则会显示跟cursor相关的参数
select * from v$parameter
除了这部分参数，
Oracle
还有大量隐含参数，可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, \'TRUE\',\'Y\',\'N\') as \"Default\"
,decode(ISEM,\'TRUE\',\'Y\',\'N\') as SesMod
,decode(ISYM,\'IMMEDIATE\', \'I\',
\'DEFERRED\', \'D\',
\'FALSE\', \'N\') as SysMod
,decode(IMOD,\'MODIFIED\',\'U\',
\'SYS_MODIFIED\',\'S\',\'N\') as Modified
,decode(IADJ,\'TRUE\',\'Y\',\'N\') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,\'TRUE\',\'FALSE\') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,\'IMMEDIATE\',2,\'DEFERRED\',\'FALSE\') as ISYM
,decode(bitand(ksppstvf,7),1,\'MODIFIED\',\'FALSE\') as IMOD
,decode(bitand(ksppstvf,2),2,\'TRUE\',\'FALSE\') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = \'_\'
AND x.inst_id = USERENV(\'Instance\')
ORDER BY NAME
52.怎样建立基于函数索引
8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
53.怎么样移动表或表分区
[A]移动表的
语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的
语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);
54.怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
55.case的
用法
在sql语句中
CASE test_value
WHEN expression1 THEN value1
[[WHEN expression2 THEN value2] [...]]
[ELSE default_value]
SELECT last_name, job_id, salary
CASE job_id
WHEN \'IT_PROG\' THEN 1.10*salary
WHEN \'ST_CLERK\' THEN 1.15*salary
WHEN \'SA_REP\' THEN 1.20*salary
ELSE salary END \"REVISED_SALARY\"
FROM employees
select
when real_charge>=20000 and real_charge<30000 then 5000
when real_charge>=30000 and real_charge<40000 then 9000
when real_charge>=40000 and real_charge<50000 then 10000
when real_charge>=50000 and real_charge<60000 then 14000
when real_charge>=60000 and real_charge<70000 then 18000
when real_charge>=70000 and real_charge<80000 then 19000
when real_charge>=80000 and real_charge<90000 then 24000
when real_charge>=90000 and real_charge<100000 then 27000
when real_charge>=100000 and real_charge<110000 then 27000
when real_charge>=110000 and real_charge<120000 then 29000
when real_charge>=120000 then 36000
end ,acc_id,user_id,real_charge from okcai_jh_charge_200505
在存储过程中
case v_strGroupClassCode
when \'1\' then
v_nAttrNum := v_nAttrNum + 300;
v_strAttrFlag := \'1\'||substr(v_strAttrFlag,2,7);
when \'2\' then
v_nAttrNum := v_nAttrNum + 200;
v_strAttrFlag := \'2\'||substr(v_strAttrFlag,2,7);
NULL;
end case;
注意的是存储过程和sql语句有的细微差别是用end case，而不是end。语句后面跟\";\"
rank ( ) over ([partition by col] order by col )
dense_rank ( ) over ([partition by col] order by col )
row
number
( ) over ( [partition by col] order by col )
rank() 是排名的函数，该函数组内排序后会进行跳号，分数相同的作为并列。
dense_rank()
转自：http://www.blogjava.net/caojianhua/archive/2011/01/24/343461.html
Oracle
数据
类型
之
number
oracle
的
number
类型
是
oracle
的内置
类型
之一，是
oracle
的最基础数值数据
类型
。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据
类型
，在10g以后，才出现了两种新的数值
类型
，即推出本地浮点数据
number
类型
的
语法
很简单：
number
(p,s)：
p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符*表示38。
s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。
最高整数位数＝p-s
s正数，小数点右边指定位置开始四舍五入
s负数，小数点左边
第一章
Oracle
入门
一、 数据库概述
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今五十年前。简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。
常见的数据模型
1. 层次结构模型: 层次结构模型实质上是一种有根结点的定向有序树，IMS(Information Manage-mentSystem)是其典型代表。
2. 网状结构模型：按照网状数据结构建立的数据库系统称为网状数据库系统，其典型代表是DBTG(Data Base Task Group)。
3. 关系结构模型：关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。常见的有
Oracle
、mssql、mysql等
二、 主流数据库
数据库名 公司 特点 工作环境
mssql 微软 只能能运行在windows平台，体积比较庞大，占用许多系统资源， 但使用很方便，支持命令和图形化管理，收费。 中型企业
Mysql 甲骨文 是个开源的数据库server，可运行在多种平台, 特点是响应速度特别快，主要面向中小企业 中小型企业
PostgreSQL 号称“世界上最先进的开源数据库“，可以运行在多种平台下，是tb级数据库，而且性能也很好 中大型企业
oracle
甲骨文 获得最高认证级别的ISO标准安全认证，性能最高， 保持开放平台下的TPC-D和TPC-C的世界记录。但价格不菲 大型企业
db2 IBM DB2在企业级的应用最为广泛， 在全球的500家最大的企业中,几乎85%以上用DB2数据库服务器。收费 大型企业
Access 微软 Access是一种桌面数据库，只适合数据量少的应用，在处理少量 数据和单机访问的数据库时是很好的，效率也很高 小型企业
三、
Oracle
数据库概述
ORACLE
数据库系统是美国
ORACLE
公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。
拉里•埃里森
就业前景
从就业与择业的角度来讲，计算机相关专业的大学生从事
oracle
方面的技术是职业发展中的最佳选择。
其一、就业面广：全球前100强企业99家都在使用
ORACLE
相关技术，中国政府机构，大中型企事业单位都能有
ORACLE
技术的工程师岗位。
其二、技术层次深：如果期望进入IT服务或者产品公司（类似毕博、DELL、IBM等），
Oracle
技术能够帮助提高就业的深度。
其三、职业方向多：
Oracle
数据库管理方向、
Oracle
开发及系统架构方向、
Oracle
数据建模数据仓库等方向。
四、 如何学习
认真听课、多思考问题、多动手操作、有问题一定要问、多参与讨论、多帮组同学
五、 体系结构
oracle
的体系很庞大，要学习它，首先要了解
oracle
的框架。
oracle
的框架主要由物理结构、逻辑结构、内存分配、后台进程、
oracle
例程、系统改变号 (System Change
Number
)组成
物理结构
物理结构包含三种数据文件:
1) 控制文件
2) 数据文件
3) 在线重做日志文件
逻辑结构
功能：数据库如何使用物理空间
组成：表空间、段、区、块的组成层次
六、
oracle
安装、卸载和启动
硬件要求
物理内存：1GB
可用物理内存：50M
交换空间大小：3.25GB
硬盘空间：10GB
1. 安装程序成功下载，将会得到如下2个文件：
解压文件将得到database文件夹，文件组织如下：
点击setup.exe执行安装程序，开始安装。
2. 点击安装程序将会出现如下安装界面，步骤 1/9：配置安全更新
填写电子邮件地址(可以不填)，去掉复选框，点击下一步
3. 步骤2/9：选择安装选项
勾选第一个，安装和配置数据库，点击下一步
4. 步骤3/8：选择系统类
勾选第一个：桌面类，点击下一步
5. 步骤4/8：配置数据库安装
选择安装路径，选择数据库版本(企业版)，选择字符集(默认值)
填写全局数据库名，管理口令
6. 步骤5/8：先决条件检查
如果你的电脑满足要求但仍然显示检查失败，这时候直接忽略，勾选全部忽略
7. 步骤6/8：概要信息
核对将要安装数据的详细信息，并保存响应文件，以备以后查看。然后点击完成数据库安装
8. 步骤7/8：安装产品
产品安装过程中将会出现以上2个界面
9. 步骤8/8：完成安装
卸载
Oracle
1. 在运行services.msc打开服务，停止
Oracle
的所有服务。
2.
oracle
11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat
3. 运行该批处理程序将自动完成
oracle
卸载工作，最后手动删除\app文件夹（可能需要重启才能删除）
4. 运行regedit命令，打开注册表窗口。删除注册表中与
Oracle
相关的内容，具体如下：
删除HKEY_LOCAL_MACHINE/SOFTWARE/
ORACLE
目录。
删除HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services中所有以
oracle
或OraWeb为开头的键。
删除HKEY_LOCAL_MACHINE/SYSETM/CurrentControlSet/Services/Eventlog/application中所有以
oracle
开头的键。
删除HKEY_CLASSES_ROOT目录下所有以Ora、
Oracle
、Orcl或EnumOra为前缀的键。
删除HKEY_CURRENT_USER/SOFTWARE/Microsoft/windows/CurrentVersion/Explorer/MenuOrder/Start Menu/Programs中所有以
oracle
开头的键。
删除HKDY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI中除Microsoft ODBC for
Oracle
注册表键以外的所有含有
Oracle
的键。
删除环境变量中的PATHT CLASSPATH中包含
Oracle
的值。
删除“开始”/“程序”中所有
Oracle
的组和图标。
删除所有与
Oracle
相关的目录，包括：
C:\Program file\
Oracle
目录。
ORACLE
_BASE目录。
C:\Documents and Settings\系统用户名、LocalSettings\Temp目录下的临时文件。
七、
oracle
中的数据库
八、 常用的工具
Sql Plus
Sql Developer
Oracle
Enterprise Manager
第二章 用户和权限
一、 用户介绍
ORACLE
用户是学习
ORACLE
数据库中的基础知识，下面就介绍下类系统常用的默认
ORACLE
用户:
1. sys用户：超级用户，完全是个SYSDBA(管理数据库的人)。拥有dba，sysdba，sysoper等角色或权限。是
oracle
权限最高的用户，登录时不能用normal。
2. system用户：超级用户，默认是SYSOPT(操作数据库的人)，不过它也能以SYSDBA的权限登陆。拥有普通dba角色权限。
3. scott用户：是个演示用户，是让你学习
Oracle
用的。
二、 常用命令
学习
oracle
，首先我们必须要掌握常用的基本命令，
oracle
中的命令比较多，常用的命令如下：
1. 登录命令(sqlplus)
说明：用于登录到
oracle
数据库
用法
：sqlplus 用户名/密码 [as sysdba/sysoper]
注意：当用特权用户登录时，必须带上sysdba或sysoper
普通用户登录
sys用户登录
操作系统的身份登录
2. 连接命令(conn)
说明：用于连接到
oracle
数据库，也可实现用户的切换
用法
：conn 用户名/密码 [as sysdba/sysoper]
注意：当用特权用户连接时，必须带上sysdba或sysoper
3. 断开连接(disc)
说明：断开与当前数据库的连接
用法
：disc
4. 显示用户名(show user)
说明：显示当前用户名
用法
：show user
5. 退出(exit)
说明：断开与当前数据库的连接并会退出
用法
：exit
6. 编辑脚本(edit/ed)
说明：编辑指定或缓冲区的sql脚本
用法
：edit [文件名]
7. 运行脚本 (start/@)
说明：运行指定的sql脚本
用法
：start/@ 文件名
8. 印刷屏幕 (spool)
说明：将sql*plus屏幕中的内容输出到指定的文件
用法
：开始印刷->spool 文件名 结束印刷->spool off
9. 显示宽度 (linesize)
说明：设置显示行的宽度，默认是80个字符
用法
：set linesize 120
10. 显示页数 (pagesize)
说明：设置每页显示的行数，默认是14页
用法
：set pagesize 20
三、 用户管理
1. 创建用户
说明：
Oracle
中需要创建用户一定是要具有dba(数据库管理员)权限的用户才能创建，而且创建的新用户不具备任何权限，连登录都不可以。
用法
：create user 新用户名 identified by 密码
2. 修改密码
说明：修改用户密码一般有两种方式，一种是通过命令password修改，另一种是通过语句alter user实现，如果要修改他人的密码，必须要具有相关的权限才可以
方式一 password [用户名]
方式二 alert user 用户名 identified by 新密码
修改当前用户(方式一)
修改当前用户(方式二)
修改其他用户(方式一)
修改其他用户(方式二)
3. 用户禁用与启用
说明：
Oracle
中想要禁用或启用一个账户也同样是使用alter user 命令来完成，只是
语法
和修改密码有所不同。
禁用 alert user 用户名 account lock
启用 alert user 用户名 account unlock
4. 删除用户
说明：
Oracle
中要删除一个用户，必须要具有dba的权限。而且不能删除当前用户，如果删除的用户有数据对象，那么必须加上关键字cascade。
用法
：drop user 用户名 [cascade]
四、 用户权限与角色
1. 权限
Oracle
中权限主要分为两种，系统权限和实体权限。
系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录
Oracle
，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
授予系统权限
说明：要实现授予系统权限只能由DBA用户授出。
用法
：grant 系统权限1[,系统权限2]… to 用户名1[,用户名2]….
系统权限回收：
说明：系统权限只能由DBA用户回收
用法
：revoke 系统权限 from 用户名
实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。主要包括select, update, insert, alter, index, delete, all其中all包括所有权限。
授予实体权限
用法
：grant 实体权限1[,实体权限2]… on 表名 to用户名1[,用户名2]….
实体权限回收
用法
：revoke 实体权限 on 表名from 用户名
查询用户拥有哪里权限：
SQL> select * from role_tab_privs;//查询授予角色的对象权限
SQL> select * from role_role_privs;//查询授予另一角色的角色
SQL> select * from DBA_tab_privs;//查询直接授予用户的对象权限
SQL> select * from dba_role_privs;//查询授予用户的角色
SQL> select * from dba_sys_privs;//查询授予用户的系统权限
SQL> select * from role_sys_privs;//查询授予角色的系统权限
SQL> Select * from session_privs;// 查询当前用户所拥有的权限
2. 角色
角色。角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。
系统预定义角色
预定义角色是在数据库安装后，系统自动创建的一些常用的角色。下面我们就简单介绍些系统角色：
CONNECT, RESOURCE, DBA这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。
oracle
建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。
DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE，SELECT_CATALOG_ROLE这些角色主要用于访问数据字典视图和包。
EXP_FULL_DATABASE， IMP_FULL_DATABASE这两个角色用于数据导入导出工具的使用。
自定义角色
Oracle
建议我们自定义自己的角色，使我们更加灵活方便去管理用户
创建角色
SQL> create role admin;
授权给角色
SQL> grant connect,resource to admin;
撤销角色的权限
SQL> revoke connect from admin;
删除角色
SQL> drop role admin;
第三章 Sql查询与函数
一、 SQL概述
SQL（Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。
SQL语言主要包含5个部分
数据定义语言Data Definition Language(DDL），用来建立数据库、数据对象和定义其列。例如：CREATE、DROP、ALTER等语句。
数据操作语言Data Manipulation Language(DML），用来插入、修改、删除、查询，可以修改数据库中的数据。例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句
数据查询语言 (Data Query Language, DQL) 是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。例如：SELECT（查询）
数据控制语言Data Controlling Language（DCL），用来控制数据库组件的存取允许、存取权限等。例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。
事务控制语言（Transactional Control Language，TCL），用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句
二、
Oracle
的数据
类型
类型
参数 描述
字符
类型
char 1~2000字节 固定长度字符串，长度不够的用空格补充
varchar2 1~4000字节 可变长度字符串，与CHAR
类型
相比，使用VARCHAR2可以节省磁盘空间，但查询效率没有char
类型
高
数值
类型
Number
(m,n) m(1~38)
n(-84~127) 可以存储正数、负数、零、定点数和精度为38位的浮点数，其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数
日期
类型
date 7字节 用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，7个字节分别表示世纪、年、月、日、时、分和秒
二进制数据
类型
row 1~2000字节 可变长二进制数据，在具体定义字段的时候必须指明最大长度n
long raw 1~2GB 可变长二进制数据
LOB数据
类型
clob 1~4GB 只能存储字符数据
nclob 1~4GB 保存本地语言字符集数据
blob 1~4GB 以二进制信息保存数据
三、 DDL语言
1. Create table命令
用于创建表。在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等
语法
结构
create table 表名(
[字段名] [
类型
] [约束]
………..
CONSTRAINT fk_column
FOREIGN KEY(column1,column2,…..column_n)
REFERENCES tablename(column1,column2,…..column_n)
例子：
create table student(
stuNo char(32) primary key,--主键约束
stuName varchar2(20) not null,--非空约束
cardId char(20) unique,--唯一约束
sex char(2) check(sex='男' or sex='女'),--检查约束
address varchar2(100) default '地址不详'--默认约束
create table mark(
mid int primary key,--主键约束
stuNo char(32) not null,
courseName varchar2(20) not null,--非空约束
score
number
(3) not null check(score>=0 and scoreselect * from em--查询所有数据
SQL>select ename,job from em--查询指定的字段数据
SQL> select * from emp where sal>1000--加条件
2. 聚合函数
聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。不能在 WHERE 子句中使用组函数。
AVG(expression): 返回集合中各值的平均值
--查询所有人都的平均工资
select avg(sal) from emp
COUNT(expression): 以 Int32 形式返回集合中的项数
--查询工资低于2000的人数
select count(*) from emp where sal2000
5. 连接查询
连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。
内连接
内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
等值连接:
select * from emp inner join dept on emp.deptno=dept.deptno
select * from emp,dept where emp.deptno=dept.deptno
不等值连接:
select * from emp inner join dept on emp.deptno!=dept.deptno
外连接
外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。
左外连接(left join): 是以左表的记录为基础的
select * from emp left join dept on emp.deptno=dept.deptno
右外连接(right join): 和left join的结果刚好相反,是以右表(BL)为基础的
select * from emp right join dept on emp.deptno=dept.deptno
全外连接(full join): 左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充
select * from emp full join dept on emp.deptno=dept.deptno
交叉连接
交叉连接即笛卡儿乘积，是指两个关系中所有元组的任意组合。一般情况下，交叉查询是没有实际意义的。
select * from cross full join dept
6. 常用查询
like模糊查询
--查询姓名首字母为S开始的员工信息
select * from emp where ename like 'S%'
--查询姓名第三个字母为A的员工信息
select * from emp where ename like '__A%'
is null/is not null 查询
--查询没有奖金的雇员信息
select * from emp where comm is null
--查询有奖金的雇员信息
select * from emp where comm is not null
in查询
--查询雇员编号为7566、7499、7844的雇员信息
select * from emp where empno in(7566,7499,7844)
exists/not exists查询(效率高于in)
--查询有上级领导的雇员信息
select * from emp e where exists
(select * from emp where empno=e.mgr)
--查询没有上级领导的雇员信息
select * from emp e where not exists
(select * from emp where empno=e.mgr)
all查询
--查询比部门编号为20的所有雇员工资都高的雇员信息
select * from emp where sal > all(select sal from emp where deptno=20)
union合并不重复
select * from emp where comm is not null
union
select * from emp where sal>3000
union all合并重复
select * from emp where comm is not null
union all
select * from emp where sal>3000
7. 子查询
当一个查询是另一个查询的条件时，称之为子查询。子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。
在CREATE TABLE语句中使用子查询
--创建表并拷贝数据
create table temp(id,name,sal) as select empno,ename,sal from emp
在INSERT语句中使用子查询
--当前表拷贝
insert into temp(id,name,sal) select * from temp
--从其他表指定字段拷贝
insert into temp(id,name,sal) select empno,ename,sal from emp
在DELETE语句中使用子查询
--删除SALES部门中的所有雇员
delete from emp where deptno in
(select deptno from dept where dname='SALES')
在UPDATE语句中使用子查询
--修改scott用户的工资和smith的工资一致
update emp set sal=(select sal from emp where ename='SMITH') where ename='SCOTT'
--修改black用户的工作,工资,奖金和scott一致
update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SCOTT') where ename='BLAKE'
在SELECT语句中使用子查询
--查询和ALLEN同一部门的员工信息
select * from emp where deptno in
(select deptno from emp where ename='ALLEN')
--查询工资大于部门平均工资的雇员信息
select * from emp e
(select avg(sal) asal,deptno from emp group by deptno) t
where e.deptno=t.deptno and e.sal>t.asal
六、 TCL语言
1. COMMIT
commit --提交事务
2. ROLLBACK
rollback to p1 --回滚到指定的保存点
rollback --回滚所有的保存点
3. SAVEPOINT
savepoint p1 --设置保存点
4. 只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作的事务，它的作用是确保用户只能取得某时间点的数据。
set transaction read only
七、
oracle
函数
1. 字符串函数
字符串函数是
oracle
中比较常用的，下面我们就介绍些常用的字符串函数:
concat：字符串连接函数，也可以使用’||’
--将职位和雇员名称显示在一列中
select concat(ename,concat('(',concat(job,')'))) from emp
select ename || '(' || job || ')' from emp
length：返回字符串的长度
--查询雇员名字长度为5个字符的信息
select * from emp where length(ename)=5
lower：将字符串转换成小写
--以小写方式显示雇员名
select lower(ename) from emp
upper：将字符串转换成大写
--以大写方式显示雇员名
select upper (ename) from emp
substr：截取字符串
--只显示雇员名的前3个字母
select substr(ename,0,3) from emp
replace：替换字符串
--将雇员的金额显示为*号
select ename,replace(sal,sal,’*’) from emp
instr：查找字符串
--查找雇员名含有’LA’字符的信息
select * from emp where instr(ename,’LA’)>0
2. 日期函数
sysdate：返回当前session所在时区的默认时间
--获取当前系统时间
select sysdate from dual
add_months：返回指定日期月份+n之后的值，n可以为任何整数
--查询当前系统月份+2的时间
select add_months(sysdate,2) from dual
--查询当前系统月份-2的时间
select add_months(sysdate,-2) from dual
last_day：返回指定时间所在月的最后一天
--获取当前系统月份的最后一天
select last_day(sysdate) from dual
months_between：返回月份差，结果可正可负，当然也有可能为0
--获取入职日期距离当前时间多少天
select months_between(sysdate, hiredate) from emp
trunc：为指定元素而截去的日期值
--获取当前系统年，其他默认
select trunc(sysdate,'yy') from dual
--查询81年2月份入职的雇员
select * from emp
where trunc(hiredate,'mm')=trunc(to_date('1981-02','yyyy-mm'),'mm')
3. 转换函数
to_char：将任意
类型
转换成字符串
--日期转换
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
--数字转换
select to_char(-100.789999999999,'L99G999D999') from dual
数字格式控制符
符号 描述
9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)
0 强制显示该位，如果当前位有数字，显示数字，否则显示0
$ 增加美元符号显示
L 增加本地货币符号显示
. 小数点符号显示
, 千分位符号显示
to_date：将字符串转换成日期对象
--字符转换成日期
select to_date('2011-11-11 11:11:11', 'yyyy-mm-dd hh24:mi:ss') from dual
to_
number
：将字符转换成数字对象
--字符转换成数字对象
select to_
number
('209.976')*5 from dual
select to_
number
('209.976', '9G999D999')*5 from dual
4. 数学函数
abs：返回数字的绝对值
select abs(-1999) from dual
ceil：返回大于或等于n的最小的整数值
select ceil(2.48) from dual
floor：返回小于等于n的最大整数值
select floor(2.48) from dual
round：四舍五入
select round(2.48) from dual
select round(2.485,2) from dual
bin_to_num：二进制转换成十进制
select bin_to_num(1,0,0,1,0) from dual
第四章 锁
一、 概述
锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。
在数据库中有两种基本的锁
类型
：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。
根据保护的对象不同，
Oracle
数据库锁可以分为以下几大类：
DML锁（data locks，数据锁），用于保护数据的完整性
DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义
内部锁和闩（internal locks and latches），保护数据库的内部结构
二、 DML锁
DML锁的目的在于保证并发情况下的数据完整性，在
Oracle
数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
1. 行级锁
当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁
--不允许其他用户对雇员表的部门编号为20的数据进行修改
select * from emp where deptno=20 for update
--不允许其他用户对雇员表的所有数据进行修改
select * from emp for update
--如果已经被锁定，就不用等待
select * from emp for update nowait
--如果已经被锁定，更新的时候等待5秒
select * from emp for update wait 5
2. 锁模式
0(none)
1(null)
2(rs)：行共享
3(rx)：行排他
4(s)：共享
5(srx)：共享行排他
6(x)：排他
数字越大，锁级别越高
3. 表级锁
当事务获得行锁后，此事务也将自动获得该行的表锁(行排他),以防止其它事务进行DDL语句影响记录行的更新
行共享锁(RS锁)：允许用户进行任何操作，禁止排他锁
lock table emp in row share mode
行排他锁(RX锁)：允许用户进行任何操作，禁止共享锁
lock table emp in row exclusive mode
共享锁(R锁)：其他用户只能看，不能修改
lock table emp in share mode
排他锁(X锁)：其他用户只能看，不能修改，不能加其他锁
lock table emp in exclusive mode
共享行排他(SRX锁)：比行排他和共享锁级别高，不能添加共享锁
lock table emp in share row exclusive mode
4. 锁兼容性
S X RS RX SRX N/A
S Y N Y N N Y
X N N N N N Y
RS Y N Y Y Y Y
RX N N Y Y N Y
SRX N N Y N N Y
N/Y Y Y Y Y Y Y
5. 死锁
当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。
1) 用户A修改A表，事务不提交
2) 用户B修改B表，事务不提交
3) 用户A修改B表，阻塞
4) 用户B修改A表，阻塞
Oracle
系统能自动发现死锁，并会自动选择工作量最少的事务进行撤销和释放所有锁
6. 悲观锁和乐观锁
数据的锁定分为两种方法，第一种叫做悲观锁，第二种叫做乐观锁
悲观锁：就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。
乐观锁：就是认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。
三、 DDL锁
1. 排它DDL锁
创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。
2. 共享DDL锁
需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁
3. 分析锁
分析锁是一种独特的DDL锁
类型
，
ORACLE
使用它追踪共享池对象及它所引用数据库对象之间的依赖关系
四、 内部锁和闩
这是
ORACLE
中的一种特殊锁，用于顺序访问内部系统结构。当事务需向缓冲区写入信息时，为了使用此块内存区域，
ORACLE
首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。
第五章 数据库对象
一、 概述
ORACLE
数据库主要有如下数据库对象：
tablespace and datafile(表空间和数据文件)
table(表)
constraints(约束)
index(索引)
view(试图)
sequence(序列)
synonyms(同义词)
DB-link(数据库链路)
二、 表空间和数据文件
表空间是数据库的逻辑组成部分，从物理上讲，数据库数据是存放在数据文件中，从逻辑上讲数据库则是存放在表空间中，表空间是由一个或多个数据文件组成。
表空间
某一时刻只能属于一个数据库
由一个或多个数据文件组成
可进一步划分为逻辑存储
表空间主要分为两种
System表空间
随数据库创建
包含数据字典
包含system还原段
非system表空间
用于分开存储段
易于空间管理
控制分配给用户的空间量
数据文件
只能属于一个表空间和一个数据库
是方案对象数据的资料档案库
创建表空间
CREATE TABLESPACE tablespacename
[DATAFILE clause]
[MINIMUM EXTENT integer[k|m]]
[BLOCKSIZE integer[k]]
[LOGGING|NOLOGGING]
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management_clause]
[segment_management_clause]
--创建本地管理表空间
create tablespace firstSpance
datafile 'e:/firstspance.dbf'size 100M
extent management local uniform size 256k
--修改文件大小
alter database datafile 'e:/firstspance.dbf' resize 110m
--删除表空间
drop tablespace firstSpance INCLUDING CONTENTS and datafiles
--使用数据库表空间
--创建用户指定表空间
create user guest identified by 123456
default tablespace firstSpance
--表中指定表空间
create table account(
accountid
number
(4),
accountName varchar2(20)
)tablespace firstSpance
--表空间脱机
alter tablespace firstSpance offline
--表空间联机
alter tablespace firstSpance online
--表空间只读，不能进行dml操作
alter tablespace firstSpance read only
三、 同义词
Oracle
数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。
Oracle
同义词有两种
类型
，分别是公用
Oracle
同义词与私有
Oracle
同义词。
公有同义词
CREATE [OR REPLACE] PUBLIC SYNONYM sys_name FOR [SCHEMA.] object_name
创建（需拥有CREATE PUBLIC SYNONYM权限才可以创建）
--创建同义词
create public synonym syn_emp for scott.emp
--访问同义词
select * from syn_emp
drop public synonym syn_emp
私有同义词
CREATE [OR REPLACE] SYNONYM sys_name FOR [SCHEMA.] object_name
--创建同义词
create synonym syn_pri_emp for emp
--访问同义词
select * from syn_ pri _emp
drop public synonym syn_emp
四、 表分区
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
使用场合
表的大小超过2GB
表中包含历史数据，新的数据被增加都新的分区中
常见分区方法:
范围 --- 8
Hash --- 8i
列表 --- 9i
组合 --- 8i
1. 范围分区
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。
特点:
最早、最经典的分区算法
Range分区通过对分区字段值的范围进行分区
Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。
数据管理能力强(数据迁移、数据备份、数据交换)
范围分区的数据可能不均匀
范围分区与记录值相关，实施难度和可维护性相对较差
按值划分
CREATE TABLE book (
bookid
NUMBER
(5),
bookname VARCHAR2(30),
price
NUMBER
(8)
)PARTITION BY RANGE (price)--分区字段
PARTITION P1 VALUES LESS THAN (4) TABLESPACE system,
PARTITION P2 VALUES LESS THAN (8) TABLESPACE system,
PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE system,
--MAXVALUE代表了一个不确定的值,这个值高于其它分区中的任何分区键的值
按日期划分
CREATE TABLE student (
stuno
NUMBER
(5),
stuname VARCHAR2(30),
birthday date
)PARTITION BY RANGE (birthday)--分区字段
PARTITION P1990 VALUES LESS THAN (to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system,
PARTITION P1991 VALUES LESS THAN (to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system
2. Hash分区(散列分区)
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区
类型
。如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
基于分区字段的HASH值，自动将记录插入到指定分区。
分区数一般是2的幂
易于实施
总体性能最佳
适合于静态数据
HASH分区适合于数据的均匀存储
数据管理能力弱
HASH分区对数据值无法控制
CREATE TABLE classes (
clsno
NUMBER
(5),
clsname VARCHAR2(30)
)PARTITION BY HASH(clsno)--分区字段
PARTITION ph1 tablespace system,
PARTITION ph2 tablespace system
3. List分区(列表分区)
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
List分区通过对分区字段的离散值进行分区
List分区是不排序的，而且分区之间也没有关联
List分区适合于对数据离散值进行控制
List分区只支持单个字段
List分区具有与range分区相似的优缺点
数据管理能力强
各分区的数据可能不均匀
CREATE TABLE users (
userid
NUMBER
(5),
username VARCHAR2(30),
province char(5)
)PARTITION BY list(province)--分区字段
PARTITION pl1 values('广东') tablespace system,
PARTITION pl2 values('江西') tablespace system,
PARTITION pl3 values('广西') tablespace system,
PARTITION pl4 values('湖南') tablespace system
4. 组合分区
常见的组合分区主要有范围散列分区和范围列表分区
既适合于历史数据，又适合于数据均匀分布
与范围分区一样提供高可用性和管理性
实现粒度更细的操作
组合范围列表分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
CREATE TABLE student (
stuno
NUMBER
(5),
stuname VARCHAR2(30),
birthday date,
province char(5)
)PARTITION BY RANGE (birthday) --主分区字段
subpartition BY LIST(province)--子分区字符
PARTITION P1990 VALUES LESS THAN(to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system
SUBPARTITION pl1 values('广东') tablespace system,
SUBPARTITION pl2 values('江西') tablespace system,
SUBPARTITION pl3 values('广西') tablespace system,
SUBPARTITION pl4 values('湖南') tablespace system
PARTITION P1991 VALUES LESS THAN(to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system
SUBPARTITION p21 values('广东') tablespace system,
SUBPARTITION p22 values('江西') tablespace system,
SUBPARTITION p23 values('广西') tablespace system,
SUBPARTITION p24 values('湖南') tablespace system
组合范围散列分区
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
CREATE TABLE student (
stuno
NUMBER
(5),
stuname VARCHAR2(30),
birthday date
)PARTITION BY RANGE(birthday) --主分区字段
SUBPARTITION BY HASH(stuno)--子分区字符
PARTITION P1990 VALUES LESS THAN(to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system
SUBPARTITION ph12 tablespace system,
SUBPARTITION ph13 tablespace system
PARTITION P1991 VALUES LESS THAN(to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system
SUBPARTITION ph21 tablespace system,
SUBPARTITION ph22 tablespace system
5. 表分区常用操作
添加分区
--添加主分区
alter table book add partition p4 values less than(maxvalue) tablespace system
--添加子分区
ALTER TABLE student MODIFY PARTITION P1990
ADD SUBPARTITION pl5 values('福建')
删除分区
--删除主分区
ALTER TABLE student DROP PARTITION P1990
--删除子分区
ALTER TABLE student DROP SUBPARTITION p15
重命名表分区
ALTER TABLE student RENAME PARTITION P21 TO P2
显示数据库所有分区表的信息
select * from DBA_PART_TABLES
显示当前用户所有分区表的信息
select * from USER_PART_TABLES
查询指定表分区数据
select * from users partition(pl2)--主分区
select * from users subpartition(phl2)--子分区
删除分区表一个分区的数据
alter table book truncate partition p11
第六章 视图
一、 概述
视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。 通过创建视图可以提取数据的逻辑上的集合或组合。
为什么使用视图
控制数据访问
简化查询
数据独立性
避免重复访问相同的数据
使用修改基表的最大好处是安全性，即保证那些能被任意人修改的列的安全性
Oracle
中视图分类
关系视图
内嵌视图
对象视图
物化视图
二、 关系视图
关系视图是作为数据库对象存在的，创建之后也可以通过工具或数据字典来查看视图的相关信息。关系视图是4种视图中最简单，同时也最常用的视图。
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
1. OR REPLACE:若所创建的试图已经存在，
ORACLE
自动重建该视图
2. FORCE:不管基表是否存在
ORACLE
都会自动创建该视图
3. NOFORCE:只有基表都存在
ORACLE
才会创建该视图
4. Alias:为视图产生的列定义的别名
5. subquery:一条完整的SELECT语句，可以在该语句中定义别名
6. WITH CHECK OPTION:插入或修改的数据行必须满足视图定义的约束
7. WITH READ ONLY:该视图上不能进行任何DML操作
create or replace view view_Account_dept
select * from emp where deptno=10
--只读视图
create or replace view view_Account_dept
select * from emp where deptno=10 order by sal
with read only
--约束视图
create or replace view view_Account_dept
select * from emp where deptno=10
with check option
查询视图
select * from emp where view_Account_dept
修改视图
通过OR REPLACE 重新创建同名视图即可
删除视图
DROP VIEW VIEW_NAME语句删除视图
视图上的DML 操作原则
1. 简单视图可以执行DML操作；
2. 在视图包含GROUP函数，GROUP BY子句，DISTINCT关键字时不能执行delete语句
3. 在视图包含GROUP函数，GROUP BY子句，DISTINCT关键字，ROWNUM为例，列定义为表达式时不能执行update语句
4. 在视图包含GROUP函数，GROUP BY子句，DISTINCT关键字，ROWNUM为例，列定义为表达式，表中非空的列子视图定义中未包括时不能执行insert语句
5. 可以使用WITH READ ONLY来屏蔽DML操作
三、 内嵌视图
内嵌视图是在from语句中的可以把表改成一个子查询。内嵌视图不属于任何用户，也不是对象，内嵌视图是子查询的一种。
Select * from
(select * from emp where deptno=10)
where sal>2000
四、 对象视图
对象
类型
在数据库编程中有许多好处，但有时，应用程序已经开发完成。为了迎合对象
类型
而重建数据表是不现实的。对象视图正是解决这一问题的优秀策略。
五、 物化视图
常用于数据库的容灾，不是传统意义上虚拟视图，是实体化视图，和表一样可以存储数据、查询数据。主备数据库数据同步通过物化视图实现，主备数据库通过data link连接，在主备数据库物化视图进行数据复制。当主数据库垮掉时，备数据库接管，实现容灾。
create materialized view materialized_view_name
build [immediate|deferred] --1.创建方式
refresh [complete|fast|force|never] --2.物化视图刷新方式
on [commit|demand] --3.刷新触发方式
start with (start_date) --4.开始时间
next (interval_date) --5.间隔时间
with [primary key|rowid] --默认 primary key
ENABLE QUERY REWRITE --7.是否启用查询重写
as --8.关键字
select statement; --9.基表选取数据的select语句
1. 创建方式
immediate(默认):立即
deferred:延迟，至第一次refresh时，才生效
2. 物化视图刷新方式
force(默认):如果可以快速刷新，就执行快速刷新,否则，执行完全刷新
complete:完全刷新，即刷新时更新全部数据，包括视图中已经生成的原有数据
fast:快速刷新，只刷新增量部分。前提是，需要在基表上创建物化视图日志。该日志记录基表数据变化情况，所以才能实现增量刷新
never:从不刷新
3. 刷新触发方式
on commit:基表有commit动作时，刷新视图，不能跨库执行(因为不知道别的库的提交动作)
on demand，在需要时刷新，根据后面设定的起始时间和时间间隔进行刷新，或者手动调用dbms_mview包中的过程刷新时再执行刷新。
4. 开始时间和间隔时间
4和5即开始刷新时间和下次刷新的时间间隔。如：start with sysdate next sysdate+1/1440表示马上开始，刷新间隔为1分钟。（与 on commit选项冲突）
5. 创建模式
primary key(默认):基于基表的主键创建
rowed:不能对基表执行分组函数、多表连结等需要把多个rowid合成一行的操作
6. 是否启用查询重写
如果设置了初始化参数query_rewrite_enabled=true则默认就会启用查询重写。但是，数据库默认该参数为false。并且，不是什么时候都应该启用查询重写。所以，该参数应该设置为false，而在创建特定物化视图时，根据需要开启该功能。
7. 注意
如果选择使用了上面第4,5选项，则不支持查询重写功能（原因很简单，所谓重写，就是将对基表的查询定位到了物化视图上，而4、5选项会造成物化视图上部分数据延迟，所以，不能重写）。
--创建增量刷新的物化视图时应先创建存储的日志空间
--在scott.emp表中创建物化视图日志
create materialized view log on emp
tablespace users with rowid;
--开始创建物化视图
--方式一
create materialized view mv_emp
tablespace users --指定表空间
build immediate --创建视图时即生成数据
refresh fast --基于增量刷新
on commit --数据DML操作提交就刷新
with rowid --基于ROWID刷新
as select * from emp
--方式二
create materialized view mv_emp2
tablespace users --指定表空间
refresh fast --基于增量刷新
start with sysdate --创建视图时即生成数据
next sysdate+1/1440 /*每隔一分钟刷新一次*/
with rowid --基于ROWID刷新
as select * from emp
--删除物化视图日志
drop materialized view mv_emp
第七章 索引
一、 概述
索引是建立在表上的可选对象，设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担，进行影响系统的性能。
索引一旦建立后，当在表上进行DML操作时，
Oracle
会自动维护索引，并决定何时使用索引。
索引的使用对用户是透明的，用户不需要在执行SQL语句时指定使用哪个索引及如何使用索引，也就是说，无论表上是否创建有索引，SQL语句的
用法
不变。用户在进行操作时，不需要考虑索引的存在，索引只与系统性能相关。
索引的原理
当在一个没有创建索引的表中查询符合某个条件的记录时，DBMS会顺序地逐条读取每个记录与查询条件进行匹配，这种方式称为全表扫描。全表扫描方式需要遍历整个表，效率很低。
索引的
类型
Oracle
支持多种
类型
的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。
单列索引和复合索引
B树索引
位图索引
函数索引
创建索引
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT]
UNIQUE：表示唯一索引，默认情况下，不使用该选项。
BITMAP：表示创建位图索引，默认情况下，不使用该选项。
PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。
NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项。
ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认情况下，不使用该选项。
NOSORT：默认情况下，不使用该选项。则
Oracle
在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。
二、 单列索引和复合索引
一个索引可以由一个或多个列组成。基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引。
三、 B树索引
B树索引是
Oracle
数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时，默认创建的索引就是B树索引。B树索引就是一棵二叉树，它由根、分支节点和叶子节点三部分构成。叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。
B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询。
--创建B树索引，属于单列索引
create index idx_emp_job on emp(job)
--创建B树索引，属于复合索引
create index idx_emp_nameorsal on emp(ename,sal)
--创建唯一的B树索引，属于单列索引
create unique index idx_emp_ename on emp(ename)
--删除索引
drop index idx_emp_job
drop index idx_emp_nameorsal
drop index idx_emp_ename
--如果表已存在大量的数据，需要规划索引段
create index idx_emp_nameorsal on emp(ename,sal)
pctfree 30 tablespace system
四、 位图索引
在B树索引中，保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为2（只有男和女）。
因此，对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的B树索引。
--创建位图索引,单列索引
create bitmap index idx_bm_job on emp(job)
--创建位图索引,复合索引
create bitmap index idx_bm_jobordeptno on emp(job,deptno)
--删除位图索引
drop index idx_bm_job
drop index idx_bm_jobordeptno
五、 函数索引
函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B树索引，当函数或表达式的结果是固定的几个值时采用位图索引。
--合并索引
alter index idx_emp_ename COALESCE
六、 并和重建索引
表在使用一段时间后，由于用户不断对其进行更新操作，而每次对表的更新必然伴随着索引的改变，因此，在索引中会产生大量的碎片，从而降低索引的使用效率。有两种方法可以清理碎片：合并索引和重建索引。
合并索引就是将B树叶子节点中的存储碎片合并在一起，从而提高存取效率，但这种合并并不会改变索引的物理组织结构。
--创建B树
类型
的函数索引
create index idx_fun_emp_hiredate on emp(to_char(hiredate,'yyyy-mm-dd'))
--创建位图
类型
的函数索引
create index idx_fun_emp_job on emp(upper(job))
重建索引相当于删除原来的索引，然后再创建一个新的索引，因此，CREAT INDEX语句中的选项同样适用于重建索引。如果在索引列上频繁进行UPDATE和DELETE操作，为了提高空间的利用率，应该定期重建索引。
七、 管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行DML操作后维护索引数据。
在新的SQL标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则:
1. 小表不需要建立索引。
2. 对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
3. 对于大部分列值不重复的列可建立索引。
4. 对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
5. 对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。
6. LONG和LONG RAW列不能创建索引。
7. 经常进行连接查询的列上应该创建索引。
8. 在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。
9. 维护索引需要开销，特别时对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。
10. 在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，
Oracle
都必须更改每个索引。
八、 ROWID和ROWNUM
1. ROWID
rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来, 利用rowid是访问表中一行的最快方式。rowid的是基于64位编码的18个字符显示（数据对象编号(6)+文件编号(3) +块编号(6)+行编号(3)=18位）
select rowid from emp
ROWID的使用
--快速删除重复的记录
delete from temp t where rowid not in(
select max(rowid) from temp
where t.id=id and t.name=name and t.sal = sal
2. ROWNUM
ROWNUM是一个序列，是
oracle
数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。
select rownum,emp.* from emp
ROWID的使用
--取前3条记录
select * from emp where rownum<=3--方式一
select * from emp where rownum!=4--方式二
select * from emp where empno not in(
select empno from emp where rownum<5--方式一
) and rownum <4
第八章 PL/SQL编程
一、 介绍
PL/SQL是
oracle
在标准sql语言上的扩展，PL/SQL不仅允许嵌入sql语言，还可以定义变量和常量，允许使用例外处理各种错误，这样使它的功能变得更加强大。
PL/SQL也是一种语言，叫做过程化sql语言(procedural language/sql),通过此语言可以实现复杂功能或者复杂的计算。
1. 提高应用程序的运行性能
2. 模块化的设计思想
3. 减少网络传输量
4. 提高安全性
1. 可移植性差
2. 违反MVC设计模式
3. 无法进行面向对象编程
4. 无法做成通用的业务逻辑框架
5. 代码可读性差，相当难维护
二、 PL/SQL基础
1. 编写规范
1) 注释
--单行注释
/*块注释*/
2) 标识符的命名规范
定义变量：建议用v_作为前缀v_price
定义常量：建议用c_作为前缀c_pi
定义游标：建议用_cursor作为后缀emp_cursor
定义例外：建议用e_作为前缀e_error
2. 块结构
PL/SQL块由三个部分组成：定义部分、执行部分、例外处理部分
Declare
定义部分(可选)：定义常量、变量、游标、例外，复杂数据
类型
begin
执行部分(必须):要执行的PL/SQL语句和SQL语句
exception
/*例外部分(可选):处理运行各种错误*/
案例一 ：只定义执行部分
begin
dbms_output是
oracle
提供的包(类似java开发包)
该包包含一些过程，put_line就是其一个过程
dbms_output.put_line('HELLO WORLD'); --控制台输出
案例二 ：定义声明部分和执行部分
declare
--声明变量
v_name varchar2(20);
v_sal
number
(7,2);
begin
--执行查询
select ename,sal into v_name,v_sal
from emp where rownum=1;
--控制台输出
dbms_output.put_line('用户名：' || v_name);
dbms_output.put_line('工资：' || v_sal);
案例三 ：定义声明部分、执行部分和例外部分
declare
--声明变量
v_name varchar2(20);
v_sal
number
(7,2);
begin
--执行查询,条件中的&表示从控制接受数据
select ename,sal into v_name,v_sal
from emp where empno=&no;
--控制台输出
dbms_output.put_line('用户名：' || v_name);
dbms_output.put_line('工资：' || v_sal);
exception
--例外处理(no_data_found)
when no_data_found then
dbms_output.put_line('执行查询没有结果');
3. 预定义例外
1) case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外。
2) cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外。
3) dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
4) invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
5) invalid_
number
预定义例外
当输入的数据有误时，会触发该例外
6) no_data_found预定义例外
当执行select into没有返回行，就会触发该例外
7) too_many_rows预定义例外
当执行select into语句时，如果返回超过了一行，则会触发该例外
8) zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9) value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
10) others
4. 变量
类型
分类
在编写PL/SQL时，可以定义变量和常量，常用的
类型
主要有：
标量
类型
(scalar)
复合
类型
(composite)
参照
类型
(reference)
lob(large object)
5. 标量
类型
:常用
类型
declare
--定义一个变长字符串
v_name varchar2(20);
--定义小数,并赋值
v_sal
number
(7,2) :=9.8;
--定义整数
v_num
number
(4);
--定义日期
v_birthday date;
--定义布尔
类型
，不能为空，初始值为false
v_flg boolean not null default false;
--使用%type
类型
v_job emp.job%type;
begin
v_flg := true;
v_birthday :=sysdate;
dbms_output.put_line('当前时间:' || v_birthday);
6. 复合
类型
：可以存放多个值。主要包括PL/SQL记录、PL/SQL表、嵌入表和varray这四种
类型
记录
类型
：类似于c中的结构体
declare
--定义记录
类型
type emp_record_type is record(
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type
--定义变量引用记录
类型
v_record emp_record_type;
begin
--使用记录
类型
select empno,ename,sal into v_record from emp where rownum=1;
--控制台输出
dbms_output.put_line('雇员编号：' || v_record.empno);
dbms_output.put_line('雇员姓名：' || v_record.ename);
dbms_output.put_line('雇员工资：' || v_record.sal);
表
类型
:类似于java语言中的数组
declare
--声明表
类型
type emp_table_type is table of varchar2(20)
index by PLS_INTEGER;--表示表按整数来排序
v_enames emp_table_type;--定义变量引用表
类型
begin
select ename into v_enames(0) from emp where rownum=1;
select ename into v_enames(1) from emp where empno=7499;
select ename into v_enames(2) from emp where empno=7698;
dbms_output.put_line('下标0：' || v_enames(0));
dbms_output.put_line('下标1：' || v_enames(1));
dbms_output.put_line('下标2：' || v_enames(2));
varray
类型
:可变长数组
declare
--定义varray
类型
type varray_list is varray(20) of
number
(4);
--定义变量引用varray
类型
v_list varray_list:=varray_list(7369,7499,7566);
begin
--for i in v_list.first..v_list.last
for i in 1..v_list.count
dbms_output.put_line(v_list(i));
end loop;
PL/SQL集合方法
1) exists():用于确定特定集合元素是否存在
2) count：用于返回集合变量的元素总个数
3) limit：用于返回varray变量所允许的最大元素个数
4) first：用于返回集合变量中的一个元素的下标
5) last：用于返回集合变量中最后一个元素的下标
6) prior()：返回当前元素前一个元素的下标
7) next()：返回当前元素后一个元素的下标
8) extend：为集合变量添加元素，此方法适合用于嵌套表和varray
9) trim：从集合变量尾部删除元素，此方法适用于嵌套表和varray
10) delete：从集合变量中删除特定的元素，此方法适用于嵌套表和index-by表
7. 参照
类型
:类似c语言中的指针，
oracle
的游标
三、 PL/SQL控制语句
1. 条件分支语句
1) if—then
declare
--声明变量
v_empno emp.empno%type;
v_sal emp.sal%type;
begin
--根据雇员编号查询工资
select empno,sal into v_empno,v_sal from emp where empno=&no;
--如果工资小于2000就加100
if v_sal<2000
--工资加100
update emp set sal = sal+100 where empno=v_empno;
commit;
end if;
2) if—then—else
declare
--声明变量
v_loginname varchar2(10);
v_password varchar2(10);
begin
--从控制台接收数据
v_loginname := '&ln';
v_password := '&pw';
if v_loginname = 'admin' and v_password = '123456'
dbms_output.put_line('用户登录成功！');
dbms_output.put_line('用户登录失败！');
end if;
3) if—then—elsif—else
declare
--声明变量
v_empno emp.empno%type;
v_job emp.job%type;
begin
--根据雇员编号查询职位
select empno,job into v_empno,v_job from emp where empno=&no;
/*如果雇员所属职位是manager工资加1000
职位是salesman工资加500
其他职位加200
if v_job = 'MANAGER' then
--MANAGER职位工资加1000
update emp set sal = sal+1000 where empno=v_empno;
elsif v_job = 'SALESMAN' then
--SALESMAN职位工资加500
update emp set sal = sal+500 where empno=v_empno;
--其他职位工资加200
update emp set sal = sal+200 where empno=v_empno;
end if;
commit;
4) case
declare
--声明变量
v_mark
number
(4);
v_outstr varchar2(40);
begin
--从控制台接收成绩
v_mark := &m;
when v_mark=90 then
v_outstr := '优秀';
when v_mark=80 then
v_outstr := '良好';
when v_mark=70 then
v_outstr := '中等';
when v_mark=60 then
v_outstr := '及格';
when v_mark=0 then
v_outstr := '不及格';
v_outstr := '成绩输入有误';
end case;
--控制台输出
dbms_output.put_line(v_outstr);
2. 循环语句
1) loop
LOOP 要执行的语句;
EXIT WHEN /*条件满足，退出循环语句*/
END LOOP;
其中：EXIT WHEN 子句是必须的，否则循环将无法停止。
declare
v_num
number
(4):=1;
begin
--从控制台接收数据并插入到account表中
insert into account values(v_num,'&name');
exit when v_num =10;
v_num :=v_num+1;
end loop;
2) while
WHILE LOOP要执行的语句;END LOOP;
循环语句执行的顺序是先判断的真假，如果为真则循环执行，否则退出循环
在WHILE循环语
Oracle
JOB
用法
小结
一、设置初始化参数 job_queue_processes
sql> alter system set job_queue_processes=n;（n>0）
job_queue_processes最大值为1000
查看job queue 后台进程
sql>select name,description from v$bgprocess;
二，dbms_job package
用法
介绍
包含以下子过程：
Broken()过程。
change()过程。
Interval()过程。
Isubmit()过程。
Next_Date()过程。
Remove()过程。
Run()过程。
Submit()过程。
User_Export()过程。
What()过程。
1、Broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。
这个过程有三个参数：job 、broken与next_date。
PROCEDURE Broken (job IN
binary
_integer,
Broken IN boolean,
next_date IN date :=SYSDATE)
job参数是工作号，它在问题中唯一标识工作。
broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。 dl.bitsCN.com网管软件下载
next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。
job如果由于某种原因未能成功之行，
oracle
将重试16次后，还未能成功执行，将被标记为broken重新启动状态为broken的job，有如下两种方式;
a、利用dbms_job.run()立即执行该job
sql>begin
sql>dbms_job.run(:jobno) 该jobno为submit过程提交时返回的job
number
sql>end;
sql>/
b、利用dbms_job.broken()重新将broken标记为false
sql>begin
sql>dbms_job.broken (:job,false,next_date)
sql>end;
sql>/
2、Change()过程用来改变指定工作的设置。
这个过程有四个参数：job、what 、next_date与interval。
PROCEDURE Change (job IN
binary
_integer,
What IN varchar2,
next_date IN date,
interval IN varchar2)
此job参数是一个整数值，它唯一标识此工作。
What参数是由此工作运行的一块PL/SQL代码块。
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度。 dl.bitsCN.com网管软件下载
3、Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job与interval。
PROCEDURE Interval (job IN
binary
_integer,
Interval IN varchar2)
job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。
4、ISubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、interval与no_parse。
PROCEDURE ISubmit (job IN
binary
_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。
5、Next_Date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与next_date。
PROCEDURE Next_Date(job IN
binary
_ineger,
next_date IN date) so.bitsCN.com网管资料库任你搜
job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。
6、Remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：
PROCEDURE Remove(job IN
binary
_ineger);
job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。已正在运行的工作不能由调用过程序删除。
7、Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：
PROCEDURE Run(job IN
binary
_ineger)
job参数标识将被立即执行的工作。
8、使用Submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与no_parse。
PROCEDURE Submit ( job OUT
binary
_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
job参数是由Submit()过程返回的
binary
_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。 bitsCN全力打造网管学习平台
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行
语法
分析——TRUE指示此PL/SQL代码在它第一次执行时应进行
语法
分析，而FALSE指示本PL/SQL代码应立即进行
语法
分析。
9、User_Export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。
此程序有两个参数：job与my_call。
PROCEDURE User_Export(job IN
binary
_ineger,
my_call IN OUT varchar2)
job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的正文。
10、What()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与what。
PROCEDURE What (job IN
binary
_ineger,
What IN OUT varchar2)
job参数标识一个存在的工作。what参数指示将被执行的新的PL/SQL代码。
三、查看相关job信息
1、相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息
blog.bitsCN.com网管博客等你来搏
2、查看相关信息
SQL>SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
SQL>FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES B
------- --------- -------- -------- -
9125 01-JUN-01 00:00:00 4 N
14144 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y
3 rows selected.
正在运行的JOB相关信息
SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;
SID JOB LOG_USER THIS_DATE THIS_SEC
----- ---------- ------------- --------- --------
12 14144 HR 24-OCT-94 17:21:24
25 8536 QS 24-OCT-94 16:45:12
2 rows selected.
JOB QUEUE LOCK相关信息
SELECT SID, TYPE, ID1, ID2
FROM V$LOCK
WHERE TYPE = 'JQ';
SID TY ID1 ID2
--------- -- --------- ---------
12 JQ 0 14144
1 row selected.
四、简单例子
一个简单例子：
play.bitsCN.com累了吗玩一下吧
创建测试表
SQL> create table TEST(a date);
表已创建。
创建一个自定义过程
SQL> create or replace procedure MYPROC as
2 begin
3 insert into TEST values(sysdate);
4 end;
过程已创建。
创建JOB
SQL> variable job1
number
;
SQL> begin
2 dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440'); --每天1440分钟，即一分钟运行test过程一次
3 end;
PL/SQL 过程已成功完成。
运行JOB
SQL> begin
2 dbms_job.run(:job1);
3 end;
PL/SQL 过程已成功完成。
SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from TEST;
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
删除JOB
SQL> begin
2 dbms_job.remove(:job1);
3 end;
4 / bitsCN.com中国网管联盟
PL/SQL 过程已成功完成。
原书名： Pro
Oracle
SQL
原出版社： Apress
作者： (美)Karen Morton Kerry Osborne Robyn Sands Riyaj Shamsudeen Jared Still
译者： 朱浩波
丛书名： 图灵程序设计丛书
出版社：人民邮电出版社
ISBN：9787115266149
上架时间：2011-11-9
出版日期：2011 年11月
开本：16开
页码：502
版次：1-1
资深
Oracle
专家力作，OakTable团队推荐
全面、独到、翔实，题材丰富
Oracle
开发人员和DBA必备
Oracle
数据库中的SQL是当今市场上功能最强大的SQL实现之一，而本书全面展示了这一工具的威力。如何才能让更多人有效地学习和掌握SQL呢？Karen Morton及其团队在本书中提供了专业的方案：先掌握语言特性，再学习
Oracle
为提升语言效率而加入的支持特性，进而将两者综合考虑并在工作中加以应用。作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握
Oracle
SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：
掌握
Oracle
数据库中独有的SQL强大特征；
读取并理解SQL执行计划；
快速分析并改进表现欠佳的SQL；
通过提示及配置文件等来控制执行计划；
在程序中优化查询而无需改动代码。
作为
Oracle
SQL经典著作之一，本书为SQL开发人员指明了前行的方向，赋予了他们不断开拓的动力。
KAREN MORTON 研究人员、教育家及顾问，Fidelity信息服务公司的资深数据库管理员和性能调优专家。她从20世纪90年代初就开始使用
Oracle
，从事
Oracle
的教学工作也已经超过10年的时间。她是
Oracle
ACE，也是OakTable（
Oracle
社区中著名的“
Oracle
科学家”的非正式组织）的成员，经常在技术会议上演讲。她的著作还包括 Expert
Oracle
Practices和Beginning
Oracle
SQL，博客主页是karenmorton.blogspot.com。
KERRY OSBORNE 专注于
Oracle
咨询的Enkitec公司的创始人之一。从1982年开始使用
Oracle
（第2版）。他当过开发人员，也做过DBA，目前是
Oracle
ACE总监和OakTable成员。最近几年，他专注于研究
Oracle
内部原理以及解决性能问题。他的博客主页是 kerryosborne.
oracle
-guy.com。
ROBYN SANDS 思科公司的软件工程师，为思科的客户设计开发嵌入式
Oracle
数据库产品。从1996年开始使用
Oracle
，在应用开发、大型系统实现以及性能评估方面具有丰富的经验。她是OakTable的成员，同时是Expert
Oracle
Practices (2010年 Apress出版)一书的合著者。
RIYAJ SHAMSUDEEN 专注于性能/数据恢复/电子商务的咨询公司OraInternals的首席数据库管理员和董事长。有近20年使用
Oracle
技术产品以及
Oracle
数据库管理员/
Oracle
数据库应用管理员的经验，是真正应用集群、性能调优以及数据库内部属性方面的专家。同时是一位演讲家及
Oracle
ACE。
JARED STILL 从1994年就开始使用
Oracle
。他认为对于SQL的学习是永无止境的，相信每一个查询
Oracle
数据库的人都需要精通SQL语言，才能写出高效的查询。他参与本书的编写就是为了帮助别人实现这一目标。
封面 -11
封底 -10
扉页 -9
版权 -8
版权声明 -7
致谢 -6
目录 -5
第1章 SQL核心 1
1.1 SQL语言 1
1.2 数据库的接口 2
1.3 SQL*Plus 回顾 3
1.3.1 连接到数据库 3
1.3.2 配置SQL*Plus环境 4
1.3.3 执行命令 6
1.4 5 个核心的SQL语句 8
1.5 SELECT语句 8
1.5.1 FROM子句 9
1.5.2 WHERE子句 11
1.5.3 GROUP BY子句 11
1.5.4 HAVING子句 12
1.5.5 SELECT列表 12
1.5.6 ORDERBY子句 13
1.6 INSERT语句 14
1.6.1 单表插入 14
1.6.2 多表插入 15
1.7 UPDATE语句 17
1.8 DELETE语句 20
1.9 MERGE语句 22
1.10 小结 24
第2章 SQL执行 25
2.1
Oracle
架构基础 25
2.2 SGA-共享池 27
2.3 库高速缓存 28
2.4 完全相同的语句 29
2.5 SGA-缓冲区缓存 32
2.6 查询转换 35
2.7 视图合并 36
2.8 子查询解嵌套 39
2.9 谓语前推 42
2.10 使用物化视图进行查询重写 44
2.11 确定执行计划 46
2.12 执行计划并取得数据行 50
2.13 SQL执行——总览 52
2.14 小结 53
第3章 访问和联结方法 55
3.1 全扫描访问方法 55
3.1.1 如何选择全扫描操作 56
3.1.2 全扫描与舍弃 59
3.1.3 全扫描与多块读取 60
3.1.4 全扫描与高水位线 60
3.2 索引扫描访问方法 65
3.2.1 索引结构 66
3.2.2 索引扫描
类型
68
3.2.3 索引唯一扫描 71
3.2.4 索引范围扫描 72
3.2.5 索引全扫描 74
3.2.6 索引跳跃扫描 77
3.2.7 索引快速全扫描 79
3.3 联结方法 80
3.3.1 嵌套循环联结 81
3.3.2 排序-合并联结 83
3.3.3 散列联结 84
3.3.4 笛卡儿联结 87
3.3.5 外联结 88
3.4 小结 94
第4章 SQL是关于集合的 95
4.1 以面向集合的思维方式来思考 95
4.1.1 从面向过程转变为基于集合的思维方式 96
4.1.2 面向过程vs.基于集合的思维方式：一个例子 100
4.2 集合运算 102
4.2.1 UNION和UNION ALL 103
4.2.2 MINUS 106
4.2.3 INTERSECT 107
4.3 集合与空值 108
4.3.1 空值与非直观结果 108
4.3.2 集合运算中的空值行为 110
4.3.3 空值与GROUP BY和ORDER BY 112
4.3.4 空值与聚合函数 114
4.4 小结 114
第5章 关于问题 116
5.1 问出好的问题 116
5.2 提问的目的 117
5.3 问题的种类 117
5.4 关于问题的问题 119
5.5 关于数据的问题 121
5.6 建立逻辑表达式 126
5.7 小结 136
第6章 SQL执行计划 137
6.1 解释计划 137
6.1.1 使用解释计划 137
6.1.2 理解解释计划可能达不到目的的方式 143
6.1.3 阅读计划 146
6.2 执行计划 148
6.2.1 查看最近生成的SQL语句 149
6.2.2 查看相关执行计划 149
6.2.3 收集执行计划统计信息 151
6.2.4 标识SQL语句以便以后取回计划 153
6.2.5 深入理解DBMS_XPLAN的细节 156
6.2.6 使用计划信息来解决问题 161
6.3 小结 169
第7章 高级分组 170
7.1 基本的GROUP BY
用法
171
7.2 HAVING子句 174
7.3 GROUP BY的“新”功能 175
7.4 GROUP BY的CUBE扩展 175
7.5 CUBE的实际应用 179
7.6 通过GROUPING()函数排除空值 185
7.7 用GROUPING()来扩展报告 186
7.8 使用GROUPING_ID()来扩展报告 187
7.9 GROUPING SETS与ROLLUP() 191
7.10 GROUP BY局限性 193
7.11 小结 196
第8章 分析函数 197
8.1 示例数据 197
8.2 分析函数剖析 198
8.3 函数列表 199
8.4 聚合函数 200
8.4.1 跨越整个分区的聚合函数 201
8.4.2 细粒度窗口声明 201
8.4.3 默认窗口声明 202
8.5 Lead和Lag 202
8.5.1
语法
和排序 202
8.5.2 例1：从前一行中返回一个值 203
8.5.3 理解数据行的位移 204
8.5.4 例2：从下一行中返回一个值 204
8.6 First_value和Last_value 205
8.6.1 例子：使用First_value来计算最大值 206
8.6.2 例子：使用Last_value来计算最小值 207
8.7 其他分析函数 207
8.7.1 Nth_value(11gR2) 207
8.7.2 Rank 209
8.7.3 Dense_rank 210
8.7.4 Row_
number
211
8.7.5 Ratio_to_report 211
8.7.6 Percent_rank 212
8.7.7 Percentile_cont 213
8.7.8 Percentile_disc 215
8.7.9 NTILE 215
8.7.10 Stddev 216
8.7.11 Listagg 217
8.8 性能调优 218
8.8.1 执行计划 218
8.8.2 谓语 219
8.8.3 索引 220
8.9 高级话题 221
8.9.1 动态SQL 221
8.9.2 嵌套分析函数 222
8.9.3 并行 223
8.9.4 PGA大小 224
8.10 组织行为 224
8.11 小结 224
第9章 Model子句 225
9.1 电子表格 225
9.2 通过Model子句进行跨行引用 226
9.2.1 示例数据 226
9.2.2 剖析Model子句 227
9.2.3 规则 228
9.3 位置和符号引用 229
9.3.1 位置标记 229
9.3.2 符号标记 230
9.3.3 FOR循环 231
9.4 返回更新后的行 232
9.5 求解顺序 233
9.5.1 行求解顺序 233
9.5.2 规则求解顺序 235
9.6 聚合 237
9.7 迭代 237
9.7.1 一个例子 238
9.7.2 PRESENTV与空值 239
9.8 查找表 240
9.9 空值 242
9.10 使用Model子句进行性能调优 243
9.10.1 执行计划 243
9.10.2 谓语前推 246
9.10.3 物化视图 247
9.10.4 并行 249
9.10.5 Model子句执行中的分区 250
9.10.6 索引 251
9.11 子查询因子化 252
9.12 小结 253
第10章 子查询因子化 254
10.1 标准
用法
254
10.2 SQL优化 257
10.2.1 测试执行计划 257
10.2.2 跨多个执行的测试 260
10.2.3 测试查询改变的影响 263
10.2.4 寻找其他优化机会 266
10.2.5 将子查询因子化应用到PL/SQL中 270
10.3 递归子查询 273
10.3.1 一个CONNECT BY的例子 274
10.3.2 使用RSF的例子 275
10.3.3 RSF的限制条件 276
10.3.4 与CONNECT BY的不同点 276
10.4 复制CONNECT BY的功能 277
10.4.1 LEVEL伪列 278
10.4.2 SYS_CONNECT_BY_PATH函数 279
10.4.3 CONNECT_BY_ROOT运算符 281
10.4.4 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数 284
10.4.5 CONNECT_BY_ISLEAF伪列 287
10.5 小结 291
第11章 半联结和反联结 292
11.1 半联结 292
11.2 半联结执行计划 300
11.3 控制半联结执行计划 305
11.3.1 使用提示控制半联结执行计划 305
11.3.2 在实例级控制半联结执行计划 308
11.4 半联结限制条件 310
11.5 半联结必要条件 312
11.6 反联结 312
11.7 反联结执行计划 317
11.8 控制反联结执行计划 326
11.8.1 使用提示控制反联结执行计划 326
11.8.2 在实例级控制反联结执行计划 327
11.9 反联结限制条件 330
11.10 反联结必要条件 333
11.11 小结 333
第12章 索引 334
12.1 理解索引 335
12.1.1 什么时候使用索引 335
12.1.2 列的选择 337
12.1.3 空值问题 338
12.2 索引结构
类型
339
12.2.1 B-树索引 339
12.2.2 位图索引 340
12.2.3 索引组织表 341
12.3 分区索引 343
12.3.1 局部索引 343
12.3.2 全局索引 345
12.3.3 散列分区与范围分区 346
12.4 与应用特点相匹配的解决方案 348
12.4.1 压缩索引 348
12.4.2 基于函数的索引 350
12.4.3 反转键索引 353
12.4.4 降序索引 354
12.5 管理问题的解决方案 355
12.5.1 不可见索引 355
12.5.2 虚拟索引 356
12.5.3 位图联结索引 357
12.6 小结 359
第13章 SELECT以外的内容 360
13.1 INSERT 360
13.1.1 直接路径插入 360
13.1.2 多表插入 363
13.1.3 条件插入 364
13.1.4 DML错误日志 364
13.2 UPDATE 371
13.3 DELETE 376
13.4 MERGE 380
13.4.1
语法
和
用法
380
13.4.2 性能比较 383
13.5 小结 385
第14章 事务处理 386
14.1 什么是事务 386
14.2 事务的ACID属性 387
14.3 事务隔离级别 388
14.4 多版本读一致性 390
14.5 事务控制语句 391
14.5.1 Commit(提交) 391
14.5.2 Savepoint(保存点) 391
14.5.3 Rollback(回滚) 391
14.5.4 Set Transaction(设置事务) 391
14.5.5 Set Constraints(设置约束) 392
14.6 将运算分组为事务 392
14.7 订单录入模式 393
14.8 活动事务 399
14.9 使用保存点 400
14.10 序列化事务 403
14.11 隔离事务 406
14.12 自治事务 409
14.13 小结 413
第15章 测试与质量保证 415
15.1 测试用例 416
15.2 测试方法 417
15.3 单元测试 418
15.4 回归测试 422
15.5 模式修改 422
15.6 重复单元测试 425
15.7 执行计划比较 426
15.8 性能测量 432
15.9 在代码中加入性能测量 432
15.10 性能测试 436
15.11 破坏性测试 437
15.12 通过性能测量进行系统检修 439
15.13 小结 442
第16章 计划稳定性与控制 443
16.1 计划不稳定性：理解这个问题 443
16.1.1 统计信息的变化 444
16.1.2 运行环境的改变 446
16.1.3 SQL语句的改变 447
16.1.4 绑定变量窥视 448
16.2 识别执行计划的不稳定性 450
16.2.1 抓取当前所运行查询的数据 451
16.2.2 查看一条语句的性能历史 452
16.2.3 按照执行计划聚合统计信息 454
16.2.4 寻找执行计划的统计方差 454
16.2.5 在一个时间点附近检查偏差 456
16.3 执行计划控制：解决问题 458
16.3.1 调整查询结构 459
16.3.2 适当使用常量 459
16.3.3 给优化器一些提示 459
16.4 执行计划控制：不能直接访问代码 466
16.4.1 选项1：改变统计信息 467
16.4.2 选项2：改变数据库参数 469
16.4.3 选项3：增加或移除访问路径 469
16.4.4 选项4：应用基于提示的执行计划控制机制 470
16.4.5 大纲 470
16.4.6 SQL概要文件 481
16.4.7 SQL执行计划基线 496
16.4.8 基于提示的执行计划控制机制总结 502
16.5 结论 502
本书作者全部是OakTable的成员，且具有15~29年丰富的
Oracle
开发经验。在研究一些被其他专门讨论
Oracle
SQL语言的参考书直接忽略的问题时，这种对
Oracle
数据库的长期钻研无疑是一个巨大的优势。
——亚马逊读者评论
SQL核心
凯伦?莫顿（Karen Morton）
不管你是刚开始写SQL语句还是已经写过很多年了，学会写出“好的”SQL这个过程都需要具有很扎实的SQL核心
语法
和概念基础知识。本章对SQL语言的核心概念及其性能做了回顾，同时还描述了一些你应该已经很熟悉的常用SQL命令。对于那些以前曾经使用过SQL并且基础知识相当牢靠的读者来说，本章就是一个简要的复习，让你为后面更详细的SQL论述做好准备。如果你是一位SQL新人，你可能想要先阅读Beginning
Oracle
SQL这本书以确保掌握SQL的基础。不管是哪种情况，第1章的目的就是通过对5个核心SQL语句的快速浏览来衡量一下你的SQL水平，同时还概述了我们用来执行SQL语句的工具：SQL*Plus。
1.1 SQL语言
SQL语言最早是IBM公司于20世纪70年代开发出来的，称为结构化英文查询语言，简称为SEQUEL。该语言是基于E.F.Codd在1969年提出的关系型数据库管理系统（RDBMS）的。后来因为商标的纠纷，其简称又进一步缩写为SQL。1986年和1987年，ANSI（美国国家标准化组织）和ISO（国际标准化组织）先后将SQL语言采纳为标准语言。而人们并不熟悉的是，ANSI官方曾将SQL语言的读音确定为“S-Q-L”。绝大多数人，包括我本人，都还在使用“sequel”的读音，只是因为这样读起来更顺口一些。
SQL的目的就是简单地提供一个到数据库的接口，在本书指的是
Oracle
数据库。每一条SQL语句对于数据库来说就是一条命令或指令。SQL与其他编程语言（如C或Java）的区别就在于它是要处理数据集合而不是一行一行的数据。语言本身也不需要你提供如何导航到数据的指令——这是在后台透明地进行的。但你将在后面的章节中看到，如果想在
Oracle
中写出高效的SQL语句，了解数据及其在数据库中的存储方式与存储位置是很重要的。
由于不同的供应商（例如甲骨文、IBM和微软）实现SQL核心功能的机制相差无几，所以基于某一种数据库所学的技巧同样可以应用到其他
类型
的数据库上。你基本上可以利用同样的SQL语句来进行数据的查询、插入、更新和删除，以及创建、修改和删除对象，而不必管数据库的供应商是哪家。
尽管SQL是各种关系型数据库管理系统的标准语言，但实际上它并不一定是关系型的。在本书后面我将就这一点稍作扩展。如果想要了解更多的细节，我推荐大家阅读C.J.Date的SQL and Relational Theory一书。需要铭记于心的一点是SQL语言并不总是严格遵守关系模型的——它根本就没有实现关系模型的某些要素，同时还不恰当地实现了一些要素。事实上，既然SQL是基于关系模型的，那么要想写出尽可能正确高效的SQL语句，你不仅必须要理解SQL语言，还要理解关系模型。
1.2 数据库的接口
多年以来人们开发出多种途径来传递SQL语句到数据库并获得结果。
Oracle
数据库的本地接口界面是
Oracle
调用界面（OCI）。OCI将由
Oracle
内核传送而来的查询语句发送到数据库。当使用某种
Oracle
工具如SQL*Plus或者SQL Developer时，你都在使用OCI。其他的
Oracle
工具如SQL*Loader、数据泵（Data Pump）以及Real Application Testing （RAT）既使用OCI，也可以使用语言特定的接口，如
Oracle
JDBC-OCI、ODP.Net、
Oracle
预编译器、
Oracle
ODBC以及
Oracle
C++调用接口（OCCI）驱动器。
当使用编程语言（如COBOL或C语言）时，你所写的语句被称为嵌入式的SQL语句并且在应用程序编译之前会由SQL预处理器进行预处理。代码清单1-1是一段可以在C/C++程序块中使用的SQL语句的例子。
代码清单1-1 C/C++程序块中所嵌入的SQL语句
其他工具，例如SQL*Plus和SQL Developer，都是交互式的工具。你输入并执行命令，然后获得相应的输出。交互式工具并不需要在运行代码前先精确编译，你只需要输入想要执行的命令即可。代码清单1-2是一段使用SQL*Plus执行语句的例子。
代码清单1-2 使用SQL*Plus执行SQL语句
在本书中，为了保持一致性我们所用的示例代码清单都使用SQL*Plus工具，但需要记住的是，不管你是用什么方法或工具来输入和执行SQL语句，所有的事情最后都要通过OCI来传递到数据库。这里的主旨就是不管你所使用的是什么工具，其本地接口都是一样的。
1.3 SQL*Plus回顾
SQL*Plus是一个不管采用哪个安装平台（Windows或Unix）都会提供的命令行工具。它是一个用来输入和执行SQL语句并显示输出结果的纯文本环境。用该工具可以直接输入、编辑命令，可以一条条地保存和执行命令或者通过脚本文件来进行，然后将输出结果以很精美格式的报表输出。要启动SQL*Plus你只需要在主机的命令提示符后敲入sqlplus即可。
1.3.1 连接到数据库
有多种方法可以通过SQL*Plus连接数据库。然而在连接之前，你还需要在$
ORACLE
_HOME/ network/admin/tnsnames.ora这个文件中登记想要连接的数据库。有两种通常使用的方法，或者如代码清单1-3所示那样在启动SQL*Plus时提供连接信息，或者如代码清单1-4所示那样在启动SQL*Plus以后使用connect命令。
代码清单1-3 通过窗口命令提示符连接到SQL*Plus
如果想要启动SQL*Plus而又不显示登录到数据库后的提示，可以在启动SQL*Plus时使用/nolog选项。
代码清单1-4 通过SQL>提示符连接SQL*Plus并登录到数据库
1.3.2 配置SQL*Plus环境
SQL*Plus有很多的命令可以让你来定制工作环境和显示选项。代码清单1-5所示是在SQL>提示符下输入help index命令后显示出来的可用的命令。
代码清单1-5 SQL*Plus命令列表
set命令是用来定制工作环境的最基本的命令。代码清单1-6为set命令的帮助文本。
代码清单1-6 SQL*Plus的SET命令
有了上面这些可用命令，你就能够很轻松地定制最适合你的运行环境了。但有一点要铭记于心的就是当你退出或关闭SQL*Plus的时候，这些设置命令就不再被保留了。为了避免每次使用SQL*Plus时都重新敲入一遍这些设置命令，你可以创建一个login.sql文件。事实上每次启动SQL*Plus的时候它都会默认去读两个文件。第一个是$
ORACLE
_HOME/sqlplus/admin目录下的glogin.sql文件。如果找到了这个文件，它就会被读进来，文件中的命令语句也会被执行。这样就可以把那些定制你的会话体验的SQL*Plus命令和SQL语句保存起来。
在读取glogin.sql文件以后，SQL*Plus会进一步寻找login.sql文件。这个文件必须在SQL*Plus的启动文件夹中或者包含在环境变量SQLPATH所指向的文件夹路径中。在login.sql文件中的所有命令优先级都比glogin.sql文件中的命令高。从10g开始，
Oracle
在每次你启动SQL*Plus或者从SQL*Plus里执行connect命令的时候都会同时去读取glogin.sql和login.sql这两个文件。在
Oracle
10g之前，login.sql脚本文件只有在SQL*Plus启动的时候才会被执行。代码清单1-7是一个常见的login.sql文件内容。
代码清单1-7 一个常见的login.sql文件
注意这里在SET SQLPROMPT中使用的变量_user和_connect_identifier。它们是预定义变量的两个示例。你可以在login.sql文件中或者任何你创建的脚本文件中使用下面这些预定义变量：
·_connect_identifier
·_date
·_editor（这个变量指定了当你使用edit命令的时候启动哪个编辑器）
·_o_version
·_o_release
·_privilege
·_sqlplus_release
·_user
1.3.3 执行命令
有两种命令可以在SQL*Plus中执行：SQL语句和SQL*Plus命令。代码清单1-5和代码清单1-6中所列出的SQL*Plus命令对于SQL*Plus来说是特有的命令，可以用来定制运行环境并且可以运行SQL*Plus特有的命令，例如DESCRIBE和CONNECT。要想执行一个SQL*Plus命令，你只需在命令提示符后输入该命令然后敲回车，命令会自动被执行。另一方面，如果要执行SQL语句，就必须使用一个特定字符来表明你想要执行输入的语句，分号（;）或者斜线（/）都可以。使用分号的话可以直接放在输入命令的后面或者放在接下来的空行中，而斜线则必须放在接下来的空行中才可以被识别。代码清单1-8展示了如何使用这两种符号。
代码清单1-8 执行字符的
用法
注意第5个在语句最后面加了一个斜线（/）的例子。光标移动到了下一行而不是立即执行语句命令。接下来，如果你再按一下回车键，语句就会被放入SQL*Plus的缓冲器中，但是也不执行。如果想要查看SQL*Plus缓冲器中的内容，可以使用list命令（也可以简写为l）。接下来如果你想在缓冲器中通过使用斜线（/）来执行语句[尽管斜线（/）命令本来就是这样来用的]在这里也将会返回一个错误。这是因为你最初在SQL语句的结尾敲入了一个斜线（/），而斜线（/）并不是一个有效的SQL命令，从而在语句想要执行的时候报错。
另外一种执行命令的方法是把命令放到一个文件中。你可以在SQL*Plus之外直接用文本编辑器生成这些文件，也可以在SQL*Plus中使用EDIT命令来直接调用编辑器。如果已经有了一个文件，EDIT命令可以打开这个文件，如果没有的话就会创建新的文件。文件必须放在默认文件夹中，否则你必须指定文件的全路径。想要设定所选择的编辑器，你只需要利用命令define_ editor='//myeditor.exe'来设置预定义变量_editor。具有.sql扩展名的文件在执行的时候不必敲入扩展名，通过@或START命令都可以执行。代码清单1-9中列出了这两个命令的
用法
。
代码清单1-9 执行.sql脚本文件
SQL*Plus具有很多特性和选项，以致于多得在这里不能一一列举。就本书需要而言，这种概述就已经足够了。但是，
Oracle
文档对SQL*Plus的
用法
给出了指导，而且很多的书，比如Beginning
Oracle
SQL，都对SQL*Plus作了更为深入的阐述，如果感兴趣你可以参考。
1.4 5个核心的SQL语句
SQL语言有很多不同的语句，但在整个职业生涯中，你可能只会用到其中很少的一部分。不过你所使用的几乎其他任何产品不也是这样的吗？据说有一个统计结果是，绝大多数人都仅使用了他们常用的软件产品或编程语言所有功能的20%甚至更少。我不知道这个统计真实与否，但以我的经验来看，这似乎是很准确的。我发现同样的基本SQL语句格式在大多数应用中使用了将近20年了。极少数的人使用过SQL提供的所有功能——即使对于那些他们确实经常使用的功能也常常用得不是很恰当。显而易见，我们不可能覆盖SQL语言的所有语句以及它们的选项。本书的目的在于让你能够深入理解那些最常用的SQL语句并帮助你更高效地使用它们。
在本书中，我们将重点讨论5个最常用的SQL语句，它们分别为SELECT、INSERT、UPDATE、DELETE以及MERGE。尽管这些核心语句都将逐个讲解，但重中之重还是SELECT语句。将这5个语句用好了将会为你在日常工作中用好SQL语言打下坚实的基础。
1.5 SELECT语句
SELECT语句用来从一个或多个表中或者其他数据库对象中提取数据。你应该已经很熟悉SELECT语句的基础知识了，所以我将不再从一个初学者的角度来介绍SELECT语句，而是首先回顾一下SELECT语句的执行逻辑。对于如何来写一个基本的SELECT语句你应该已经学习过了，但为了培养基本的思维模式，你要一直写出符合
语法
规则的高效SQL语句，你需要理解SQL语句是如何执行的。
一个查询语句在逻辑上的处理方式可能会与实际物理处理过程大相径庭。
Oracle
基于查询成本的优化器（cost-based optimizer , CBO）用来产生实际的执行计划。我们在后面的章节中将会讲解优化器是干什么的，如何来实现其功能的以及为什么要进行优化。目前，我们需要关心的是优化器将会决定如何访问表、按照什么样的顺序来处理它们，以及如何将多个表联结起来及如何使用筛选器。查询的处理在逻辑上是按照特定的顺序进行的，但是，优化器所选择的物理执行计划可能会按照完全不同的顺序来实际执行这些步骤。代码清单1-10是一段包含SELECT语句的主要子句的查询片段，在其中标出了每一个子句的逻辑处理顺序。
代码清单1-10 查询语句的逻辑处理顺序
你应该立刻注意到SQL有别于其他编程语言的一点在于首先处理的并不是写在第一行的语句（SELECT语句），而是FROM子句。注意在这个代码清单中我给出了两个不同的FROM子句。标记为1.1的那个FROM子句表示的是当使用ANSI
语法
时的不同。我们可以把处理过程中的每一个步骤想象为生成一个临时的数据集。随着每个处理步骤的进行，这个数据集被不断地操作直到生成最终的处理结果。查询返回给调用者的就是这个最终结果数据集。
为了更详细地了解SELECT语句的每个部分，你可以参考代码清单1-11所示的查询语句，该语句返回的结果集为下订单超过4次的女顾客的列表。
代码清单1-11 下订单超过4次的女顾客查询语句
1.5.1 FROM子句
FROM子句列出了所查询数据的源对象。这个子句可以包含表、视图、物化视图、分区或子分区，或者你可以建立一个子查询来生成子对象。如果使用了多个源对象，其逻辑处理阶段也将会应用到每一个联结
类型
以及谓词ON（如步骤1.1所示）。在本书后面的章节中你将会进一步了解联结
类型
的更多细节，但注意在处理联结语句的时候是按照下面的顺序来进行的：
(1) 交叉联结，也称为笛卡儿乘积；
(2) 内联结；
(3) 外联结。
在代码清单1-11所示的查询例子中，FROM子句列出了两张表：customers和orders，通过customer_id列来联结。因此，当处理这一信息时，FROM子句所生成的初始数据集将会包含这两张表中customer_id相匹配的行。在本例中结果集将会包含105行。为了验证这一点，只要执行例子中的前4行，如代码清单1-12所示。
代码清单1-12 仅通过FROM子句的部分查询语句的执行
注意 为了使之很好地适应页面我手工调整了输出结果，实际输出结果在页面上超过105行。
1.5.2 WHERE子句
WHERE子句提供了一种方法，可以按照条件来限制查询最终返回结果集的行数。每个条件或者谓语都是以两个值或表达式相比较的形式出现的。比较的结果要么是匹配（值为TRUE）要么是不匹配（值为FALSE）。如果比较的结果是FALSE，那么相应的行不会被包含在最终结果集中。
这里我需要稍微偏离一下主题，来谈一谈与这一步相关的SQL中的一个重要方面。事实上，SQL中逻辑比较的可能结果是TRUE、FALSE以及未知。当其中包含空值（null）的时候比较的结果就会是未知。空值与任何值比较或者用在表达式中都会得到空值，或者是未知。一个空值代表一个相应值的缺失，并且可能因为SQL语言中的不同部分对空值的处理不同而令人费解。关于空值是如何影响SQL语句执行的话题将会贯穿本书，但在这里我不得不先提及一下这个话题。我之前所说的还是基本正确的，一个比较的返回值将会是TRUE或者FALSE。你会发现当进行筛选的比较条件中包含空值的时候，将作为FALSE来对待。
在我们的例子中，只有一个将结果限定为下了订单的女性消费者的谓语。如果你查看FROM子句执行之后的中间结果（见代码清单1-12），你会发现105行中仅有31行是由女性消费者所下的订单（gender = 'F'）。因此，在应用了WHERE子句以后，中间结果集将从105行减少到31行。
应用WHERE子句以后得到了更精确的结果集。注意，在这里使用的是“精确的结果集”。我的意思是说现在已经得到了能够满足你查询需求的数据行。其他子句（GROUP BY, HAVING）也许可以用来聚合并且进一步限制调用程序会接收到的最终的结果集，但需要注意的很重要的一点是，目前已经得到了查询计算最终结果所需的所有数据。
WHERE子句的目的是限制或者减小结果集。你所使用的限制条件越少，最终返回的结果集中包含的数据就会越多。你需要返回的数据越多，执行查询的时间也就越长。
1.5.3 GROUP BY子句
GROUP BY子句将执行FROM和WHERE子句后得到的经过筛选后的结果集进行聚合。查询出来的结果按照GROUP BY子句中列出的表达式进行分组，来为每一个分组得出一行汇总结果。你可以按照FROM子句中所列出对象的任意字段进行分组，即使你并不想在输出结果列表中显示该列。相反，Select列表中的任何非聚合字段都必须包括在GROUP BY表达式中。
GROUP BY子句中还可以包含两个附加的运算：ROLLUP 和CUBE。ROLLUP运算用来产生部分求和值，CUBE运算用来求得交互分类值。当你使用这两种运算中任意一个的时候，你将会得到不止一行的汇总信息。在第7章中将会对这两个运算进行更详细的讨论。
在示例查询中，需要按照customer_id来进行分组。这就意味着对于每一个唯一的customer_id只会返回一行值。在WHERE子句执行后所得到的代表下订单的女性消费者的31行订单中，有11个独特的customer_id值，如代码清单1-13所示。
代码清单1-13 截至GROUP BY子句的部分查询执行
你会发现查询的结果是经过分组的，但并没有排序。表面上看结果好像是按照order_ct字段排序的，但这仅仅是个巧合而不是确定的行为。需要记住的很重要的一点是：GROUP BY子句并不确定结果数据的排序。如果你需要结果按照特定的顺序排列，则必须指定一个order by子句。
1.5.4 HAVING子句
HAVING子句将分组汇总后的查询结果限定为只有该子句中的条件为真的数据行。除非你使用HAVING子句，否则将返回所有的汇总行。事实上，GROUP BY子句和HAVING子句的位置是可以互换的，谁先谁后都无关紧要。但是，似乎在编码中将GROUP BY子句放在前面更有意义一些，因为GROUP BY子句在逻辑上是先执行的。从本质上来说，HAVING子句是在GROUP BY子句执行后用来筛选汇总值的第二个WHERE子句。
在我们的查询例子中，HAVING子句HAVING COUNT(o.order_id) > 4，将分组数据从11行减少到2行。这一点你可以通过查看GROUP BY子句应用后返回的结果行来确认，如代码清单1-13所示。注意仅有146和147号消费者所下的订单数超过4次。这样就产生了组成最终结果集的两行数据。
1.5.5 SELECT列表
SELECT列表列出查询的返回最终结果集中需要显示哪些列。这些列可以是数据表中一个实际的列、一个表达式，或者甚至是一个SELECT语句的结果，如代码清单1-14所示。
代码清单1-14 展现SELECT列表各种可能情况的查询实例
SQL> select.customer_id, c.cust_first_name||''||c.cust_last_name,
当使用另外一个SELECT语句来产生结果中的一列的值的时候，这个查询必须只能返回一行一列的值。这种
类型
的子查询被称为标量子查询。尽管这可能是一个非常有用的
语法
，但需要牢记于心的是标量查询在结果集中的每一行结果产生时都要执行一遍。在某些情况下可以进行优化以减少标量子查询的重复执行，但更糟糕的场景是每一行都需要标量子查询执行。你可以想象如果你的结果集中有几千行甚至上百万行数据的时候所需要付出的查询代价！在后面的章节中我们还将回顾标量子查询并讨论如何更好地来使用它们。
在SELECT列表中你还有可能用到的一个选项是DISTINCT子句。在例子中并没有使用它，但我想要简要地提及一下。DISTINCT子句用来在其他子句执行完毕以后从结果集中去除重复的行。
SELECT列表执行完以后，你就得到了最终的查询结果集。所剩的唯一需要做的事情，如果包含了的话，就是将查询结果集按照所需的顺序排序。
1.5.6 ORDER BY子句
ORDER BY子句用来对查询最终返回的结果集进行排序。在本例中，需要按照orders_ct和customer_id进行排序。orders_ct这一列是通过GROUP BY子句中的COUNT聚合函数计算得到的值。如代码清单1-13中所示，有两个消费者的订单超过4个。由于这两个消费者的订单数都是5份，orders_ct这一列的值是相同的，所以要由第二个排序列来确定最终结果的显示顺序。如代码清单1-15中所示，该查询的最终经过排序的输出结果是按照customer_id排序的两行数据集。
代码清单1-15 示例查询的最终输出
当输出结果需要排序的时候，
Oracle
必须在其他所有子句都执行完之后按照指定的顺序对最终结果集进行排序。需要排序的数据量大小是非常重要的。我这里所说的大小是指结果集中所包含的总字节数。你可以通过用行数乘以每一行的字节数来估计数据集的大小。每行所包含的字节数通过将选择列表中包含的每一列的平均长度相加来确定。
上面的查询实例在选择列表中仅需要列出customer_id 和orders_ct两列的值。我们可以估算每一行输出值的字节数为10。在第6章中我将阐述从哪里能找到优化器所估计的值。因此，如果我们在结果集中只有两行数据，排序的大小实际上是很小的，大约20字节。请记住这仅仅是估算，但这样的估算也是很重要的。
较小的排序会完全在内存中来实现，而较大的排序将不得不使用临时磁盘空间来完成。如你可能推断的那样，在内存中完成的排序比必须使用磁盘的排序要快。因此，当优化器估算排序数据的影响时，它必须要考虑排序数据集的大小，以此来调整如何能够以最有效的方法来获得查询的结果。一般来说，排序是查询过程中开销相当大的一个处理步骤，尤其是当返回结果集很大的时候。
1.6 INSERT语句
INSERT语句用来向表、分区或视图中添加行。可以向单表或者多个表方法中添加数据行。单表插入将会向一个表中插入一行数据，这行数据可以显式地列出插入值也可以通过一个子查询来获取。多表插入将会向一个或多个表中插入行，并且会通过子查询获取值来计算所插入行的值。
1.6.1 单表插入
代码清单1-16中的第一个例子阐明了使用values子句实现的单表插入。每一列的值都显式地输入。如果你要插入表中所定义的所有列的值，那么列的列表是可选的。但是，如果你只想提供部分列的值，则必须在列的列表中指明所需的列名。好的做法是不管是不是需要插入所有列的值，都把所有列的列表列出来。这样做就像该语句的自述文件一样，并且也可以减少将来别人要插入一个新列到表中的时候可能出现的错误。
代码清单1-16 单表插入
第二个例子阐述了通过子查询来实现插入。这是插入数据行的一个非常灵活的选项。所写的子查询可以返回一行或多行数据。返回的每一行都会用来生成需要插入的新行的列值。根据你的需要这个子查询可以很简单也可以很复杂。在本例中，我们使用子查询实现了在现有薪水的基础上为每一位员工发放10%奖金的计算。事实上奖金表包含4列，但在这个插入中我们只列出了3个字段。comm这一列在子查询中并没有占据一列并且我们也没有将它包括在列表中。因为我们没有包含这一列，它的值将会是null。注意如果comm列具有非空约束，那么可能已返回一个约束错误，语句的执行也已失败。
1.6.2 多表插入
代码清单1-17所示的多表插入的例子阐明了一个子查询返回的数据行是如何被用来插入多个表中的。我们从3个表开始：small_customers、medium_customers以及large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定该消费者的消费金额是小（所有订单的累加金额小于10 000美元）、中等（介于10 000美元与99 999.99美元之间）还是大（大于等于100 000美元），然后按照条件将这些行插入对应的表中。
代码清单1-17 多表插入
注意INSERT关键字后面ALL子句的使用。当指定了ALL子句的时候，这个语句就会执行无条件的多表插入。也就意味着每一个WHEN子句按照子查询所返回的每一行来确定值而不管前一个条件的输出结果是什么。因此，你需要注意如何来指定每个条件。例如，如果我使用WHEN sum_orders < 100 000这个条件而不是像上面一样列出范围，插入medium_customers表中的行有可能也会插入small_customers表中。
你需要指明FIRST选项来实现每一个WHEN子句按照其出现在语句中的顺序进行评估，并且对于一个给定的子查询行跳过接下来的WHEN子句评估。关键在于要记住哪一个选项能够更好地满足你的需要，ALL还是FIRST，然后使用最适合的选项。
1.7 UPDATE语句
UPDATE语句的作用是改变表中原有行的列值。这个语句的
语法
由3部分组成：UPDATE、SET和WHERE。UPDATE子句用来指定要更新的表，SET子句用来指明哪些列改变了以及调整的值，WHERE子句用来按条件筛选需要更新的行。WHERE子句是可选的，如果忽略了这个子句的话，更新操作将针对指定表中的所有行进行。
代码清单1-18列出了几种UPDATE语句的不同写法。首先，我建立了一个employees表的副本，名称为employees2，然后我将执行几个完成基本相同任务的不同更新操作：将90部门的员工工资增加10%。在例5中，commission_pct这一列也进行了更新。下面就是采用的不同方法。
例1：使用表达式更新一个单列的值。
例2：通过子查询更新一个单列的值。
例3：通过在WHERE子句中使用子查询确定要更新的数据行来更新单列的值。
例4：通过使用SELECT语句定义表及列的值来更新表。
例5：通过子查询更新多列。
代码清单1-18 UPDATE语句的例子
1.8 DELETE语句
DELETE语句用来从表中移除数据行。该语句的
语法
结构由3部分组成：DELETE、FROM和WHERE。DELETE关键字是单独列出的。除非你决定使用我们后面将会讨论到的提示（hint），没有其他选项与DELETE关键字相结合。FROM子句用来指定要从哪个表中删除数据行。如代码清单1-19中的例子所示，这个表可以直接指定也可以通过子查询来确定。WHERE子句提供筛选条件有助于确定哪些行是要删除的。如果忽略了WHERE子句，删除操作将删除指定表中的所有数据行。
代码清单1-19展示出了DELETE语句的几种不同写法。注意，在这些例子中我使用了代码清单1-18中创建的employees2表。下面你将看到的就是这些不同的删除方法。
例1：使用WHERE子句中的筛选条件来从指定表中删除行。
例2：使用FROM子句中的子查询来删除行。
例3：使用WHERE子句中的子查询来从指定表中删除行。
代码清单1-19 DELETE语句的例子
1.9 MERGE语句
MERGE语句具有按条件获取要更新或插入到表中的数据行，然后从1个或多个源头对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据，但它的应用不仅限于数据仓库环境下。这个语句提供的一个很大的附加值在于你可以很方便地把多个操作结合成一个。这就使你可以避免使用多个INSERT、UPDATE以及DELETE语句。并且，在本书后面的内容中你将看到，如果你避免去做那些不是必须做的事情，响应时间可能得到相应的改善。
MERGE语句的
语法
是：
为了说明MERGE语句的
用法
，代码清单1-20展示出了如何建立一个测试表，然后恰当地利用MERGE条件来向表中插入或更新行。
代码清单1-20 MERGE语句例子
MERGE语句完成了下面这些事情。
·插入了两行（员工id 106和107）。
·更新了一行（员工id 105）。
·删除了一行（员工id 103）。
·一行保持不变（员工id 104）。
如果没有MERGE语句，你必须最少写3条不同的语句来完成同样的事情。
1.10 小结
正如你可以从到目前为止的例子中看出的，SQL语言提供了很多不同的选择来得到同样的结果集。你可能还注意到了一点就是这5个核心的SQL语句都可以使用类似的构造，例如子查询。关键是需要搞清楚在各种不同的使用场景下哪种构造是最高效的。我们将在本书后面的内容中阐述如何做到这一点。
如果你对本章的例子的理解有任何困难，请一定花点时间复习Beginning
Oracle
SQL或者
Oracle
文档中的SQL Reference Guide。在本书中接下来的部分我们假设你已经很好地理解了5个核心SQL语句的基本构造：SELECT、INSERT、UPDATE、DELETE和MERGE。
表示 作用 说明
Number
(p, s) 声明一个定点数 p(precision)为精度，s(scale)表示小数点右边的数字个数，精度最大值为38，scale的取值范围为-84到127
Number
(p) 声明一个整数 相当于
Number
(p, 0)
Number
声明一个浮点数 其精度为38，要注意的是scale的值没有应用，也就是说scale的指不能简单的理解为0，或者其他的数。
定点数的精度(p)和刻度(s)
当一个数的整数部分的长度 > p-s 时，
Oracle
就会报错
可以使用TO_CHAR函数将
Oracle
的
NUMBER
类型
转换为VARCHAR
类型
。
例如，将一个名为num的
NUMBER
类型
字段转换为VARCHAR
类型
，可以使用以下语句：
SELECT TO_CHAR(num) FROM 表名;
其中，TO_CHAR函数将num字段转换为字符串
类型
。如果需要指定转换后的格式，可以在TO_CHAR函数中添加第二个参数，例如：
SELECT TO_CHAR(num, '999,999.99') FROM 表名;
这将把num字段转换为带有千位分隔符和两位小数的字符串
类型
。
### 回答2：
Oracle
数据库中，将
Number
类型
转换为Varchar
类型
需要使用TO_CHAR函数。使用该函数可以将
Number
类型
的数据转换为Varchar
类型
的字符串表示。
TO_CHAR函数的
语法
如下：
TO_CHAR(
number
_value, [format_mask], [nls_language])
其中，
number
_value指需要转换的
Number
类型
的数据，format_mask指格式模板，nls_language指定语言环境（可选）。
如果format_mask未指定，则会按默认格式将
Number
类型
转换为Varchar
类型
。如果需要指定格式模板，则可以通过格式化元素来更改日期格式。
例如，要将
Number
类型
的数据转换为字符串，可以使用以下语句：
SELECT TO_CHAR(123.45) FROM DUAL;
这会将
Number
类型
的数据“123.45”转换为Varchar
类型
的字符串：“123.45”。
如果需要指定格式模板，则可以使用以下语句：
SELECT TO_CHAR(123.45, '$999G999D99') FROM DUAL;
这会将
Number
类型
的数据“123.45”转换为Varchar
类型
的字符串：“$123.45”。
需要注意的是，TO_CHAR函数返回的结果是Varchar
类型
的字符串，其长度可能不适合用作列的数据
类型
。因此，在使用TO_CHAR函数时需要将字符串长度限制在列长度之内，以免发生截断。并且，还需要考虑到字符集和语言环境的影响。
### 回答3：
Oracle
数据库中，我们经常需要将一个
number
类型
的字段转换为varchar
类型
，这在很多应用场景中都非常常见，比如我们需要将数字
类型
字段转成字符串
类型
进行拼接或格式化输出等。那么在
Oracle
数据库中，如何将
number
类型
转换成varchar
类型
呢？主要有以下三种方法：
1. 使用TO_CHAR函数进行
类型
转换
在
Oracle
中，可以使用TO_CHAR函数将
number
类型
字段转换为varchar
类型
。TO_CHAR函数需要两个参数，第一个参数为需要转换的
number
类型
字段，第二个参数为转换后的格式，如下所示：
TO_CHAR(字段名, '格式')
其中‘格式’为转换后的字符串格式，如‘9999’代表将数字
类型
转为四位，保留至少1位的字符
类型
。具体格式参数可以参考
Oracle
官方文档。
SELECT TO_CHAR(num, '999999') AS varchar_num FROM table;
2. 使用CAST函数进行
类型
转换
另一种将
number
类型
转换成varchar
类型
的方法是使用CAST函数。CAST函数需要两个参数，第一个参数为需要转换的
number
类型
字段，第二个参数为转换后的varchar
类型
。如下所示：
CAST(字段名 AS VARCHAR2(长度))
其中‘长度’为转换后的varchar
类型
的长度。
SELECT CAST(num AS VARCHAR2(6)) AS varchar_num FROM table;
3. 使用CONVERT函数进行
类型
转换：
最后一种将
number
类型
转换成varchar
类型
的方法是使用CONVERT函数。CONVERT函数需要两个参数，第一个参数为需要转换的
number
类型
字段，第二个参数为转换后的varchar
类型
格式。如下所示：
CONVERT(字段名, VARCHAR2(长度))
其中‘长度’为转换后的varchar
类型
的长度。
SELECT CONVERT(num, VARCHAR2(6)) AS varchar_num FROM table;
总的来说，上述三种方法都可以将
number
类型
字段转换成varchar
类型
。不过需要注意的是，在进行
类型
转换时，需要考虑精度问题，以免产生数据丢失。
推荐文章
|
|
冲动的香菇 · js实现多滚动条同步滚动（两个以上） - 掘金 2 年前 |