ORACLE数据库开发基础

这是万物可述的第30篇原创文章

一、Oracle数据库介绍

什么是Oracle？

Oracle Database，又别名Oracle RDBMS，或简称Oracle。

ORACLE 数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。

比如SilverStream就是基于数据库的一种中间件。

ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；

作为一个关系数据库，它是一个完备关系的产品；

作为分布式数据库它实现了分布式处理功能。

是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。

它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。

Oracle的特色：

1. 完整的数据管理功能：

   数据的大量性

   数据的保存的持久性

   数据的共享性

   数据的可靠性

   
   

2. 完备关系的产品：

   信息准则---关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示

   
   

3. 保证访问的准则

   视图更新准则---只要形成视图的表中的数据变化了，相应的视图中的数据同时变化

   数据物理性和逻辑性独立准则

   
   

4. 分布式处理功能：

   ORACLE数据库自第5版起提供了分布式处理能力，到第7版有比较完善的分布式数据库功能了，

   一个ORACLE分布式数据库由oraclerdbms、sql*Net、SQL*CONNECT和其他非ORACLE的关系型产品构成。

   用ORACLE能轻松的实现数据仓库的操作

   以上是Oracle数据库的优势，从这些优势中不难看出这是一款功能强大的数据库系统。

二、Oracle语法

Oracle中的sql语句：

Sql命令包括 （DDL）（DCL）（DML）（TCL）

DDL：数据定义语言  包括 Create语句，drop语句，alter语句。

数据定义语言主要由CRETER、DROP、ALTER 3个语句组成

1、CRETER语句

    CRETER语句用来创建数据库对象，包括创建数据库、数据表、索引或子程序、触发器等。

    CRETER INDEX：创建数据表索引

    CRETER PROCEDURE：创建存储结构

    CRETER FUNCTION：创建用户函数

    CRETER VIEW：创建视图

    CRETER TRIGGER：创建触发器

2、ALTER语句

     ALTER 语句用来修改数据库对象，包括修改数据库、数据表、索引或子程序、触发器等。

     ALTER语句只需要修改数据库对象的局部，因此不需要定义完整的数据库对象参数.

    如：向表company_emp表中添加列description：

    ALTER TABLE company_emp ADD description VARCHAR2（200） NULL；

          从表company_emp表中删除列description：

      ALTER TABLE company_emp DROP COLUMN description ；

3、DROP语句

  DROP用来删除数据库对象，包括移除数据库、数据表、索引或子程序、触发器等。

   所有的DDL语句的一个必须牢记的特色就是：每一条DDL语句都包含了一条隐式提交的事物语句，因此只要这些命令执行，系统就会向数据库提交更改，所有具有访问数据库对象权限的用户马上就可以看到DDL语句的执行效果。

DCL: 数据控制语言  包括 grant语句。

• Revoke语句 （简介）

1.GRANT 赋于权限

常用的系统权限集合有以下三个:

CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

• 常用的数据对象权限有以下五个:

1. ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,

2. DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名
   

3. GRANT CONNECT, RESOURCE TO 用户名;

4. GRANT SELECT ON 表名 TO 用户名;

5. GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

2.REVOKE 回收权限

REVOKE CONNECT, RESOURCE FROM 用户名;

REVOKE SELECT ON 表名 FROM 用户名;

REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;

DML：数据控制语言  包括 增，删，改，查语句。（简介）

Insert、Update、Delete的方法

insert into values 一次插入一行

insert into  select 插入0条或多条

如果省略列名*是系统自动查数据字典 user_tab_column 或者 dba_tab_column 基于数据字典的顺序进行排序

update 表名 set 列1=值...where限制更新的行数

delete from  表名 where限制删除的行数

select * |column[,...] from 表名

例：

1.查询全体学生的学号和姓名

select sid ,name from student;

2.查询全体学生

select * from studeent;

TCL：事物控制语言  包括  commit，rollback，savepoint

• 数据类型：
  

1 、字符类型

① CHAR : 定长，固定长度。

例如：CAHR（20）name，一个为 CHAR 类型的名为 name 的字段，长度为 20。

② VARCHAR：不定长，长度由内容的长度决定空间（这是SQL的标准，基本每个数据库里都会有这个数据类型）。

例如：VARCHAR（32）name，一个为 VARCHAR 类型的名为 name 的字段，长度为32。

③ VARCHAR2：同上，与 VACHAR 作用一样，这是 Oracle 独有的数据类型

例如：VARCHAR2（32）name，一个为 VARCHAR2 类型的名为 name 的字段，长度为 32。

④ LONG：不定长，保存内容为2GB（这个字段的应用场景：一般文章内容会保存比较多的字符，那么，你可以用他，我相信，不管你的文章内容再多，2GB 的大小，足够你放东西了）。

例如：LONG content，一个为 LONG 类型的名为 content 的字段，可以存储 2GB 的数据。

2 、数值类型

① INT：整型（这是 SQL 标准数据类型，每种数据库都有这个类型，也是最常用的类型）

例如：INT age，一个为 INT 类型的名为 age的字段，没有限制大小。

② NUMBER（P，S）：P 表示总长，S 表示小数点位数。

例如：NUMBERage，一个为 NUMBER类型的名为 age 的字段，没有限制大小。

例如：NUMBER（8，2） age，一个为 NUMBER 类型的名为 age 的字段，长度为 8 ，小数位占 2 位，小数点占 1 位，整数部分占 5 位。

3 、日期类型

DATE：时间日期格式  注意：存储日期和时间，精确到秒

4 、 Oracle 特有数据类型（不怎么用到，这里只会介绍）

RAW、LOG RAW、LOB、CLOB、BLOB、BFILE

5、可能会用到的知识

ROWID : 保存的为某一行数据保存到磁盘上的物理的位置。

ROWNUM : 行号。代表查询结果中显示的行号。

• 运算符

通常使用()可以改变运算符的优先级。

需要注意的是and的优先级要优于or,也就是说下面的语句

select * from emp where sal < 1500 or sal >= 2000 and job = 'analyst';

 等价于：select * from emp where sal < 1500 or (sal >= 2000 and job = 'analyst');

而不是你所预期的select * from emp where (sal < 1500 or sal >= 2000) and job = 'analyst';

一般即使我们要表达第一个语句所要表达的意思，为了避免误解，都不采取第一种写法，而是用括号来表明我们要先算后面的部分。.

• 窗口函数

1、排名

a、row_number() over([partition by 子句] order by子句):返回按照指定顺序依次递增的序号，不会重复，通常用于排序的分页

b、rank() over([partition by 子句] order by子句)：返回按照指定顺序依次递增的序号，如果排序字段值一样，返回一样的名次，下一个跳号，通常用于排名

c、dense_rank() over([partition by 子句] order by子句)：返回按照指定顺序依次递增的序号，如果排序字段值一样，返回一样的名次，下一个不跳号，很少使用

tips：如果有partition by子句，则是整体结果集排名，如果有partition by子句则每一个partition by子句对应的分组都重新排名

2、求累计

sum(列) over([partition by 子句] order by子句)，没有partition by子句一直累加，有partition by子句则每一个partition by子句对应的分组都重新累加单行函数

有五大类：字符、数字、日期、转换、通用

单行函数：数据的加工机

Oracle之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以

在Oracle数据库之中为了用户查询方便，所以专门提供了一个“dual”的虚拟表。

• 数字类型：

ceil(n)：返回大于等于n的最小整数

floor(n)：返回小于等于n的最大整数

abs(n)：返回n的绝对值

round(n,p)：四舍五入函数，p=0精确到整数位，p>0四舍五入小数点后几位，

p<0四舍五入小数点前几位（整数的后面至少要有p个0），如果p缺省，则p=0< span="">

trunc(n,p):截断函数，直接去掉，无需四舍五入，p=0精确到整数位，p>0保留

小数点后几位，p<0截断小数点前几位（整数的后面至少要有p个0），如果p缺省，则p=0< span="">

mod(m,n)：取余，返回m/n的余数

• 字符类型：

lower(c)：全部转成小写

upper(c)：全部转成大写  SELECT UPPER('hello') FROM dual;

initcap(c)：首字母大写，其他都小写

length(c)：返回c的字符数

lengthb(c)：返回c的字节数，utf编码一个汉字3个字节，gbk编码一个汉字2个字节

ltrim(c)：去除c的首空格

rtrim(c)：去除c的尾空格

trim(c)：去除c的收尾空格

replace(c,c1,c2)：用c2替换c中的c1，c2可以省略，如果c2省略相当于c2为''(空字符串）

lpad(c,l,c2)：左填充，如果c的长度小于l，则在c的前面用c2填充

rpad(c,l,c2)：右填充，如果c的长度小于l，则在c的后面用c2填充

substr(c,p,l)：截取子字符串，从c的p位开始（包含p位）往后截取l位，至少两个参数，

如果第三个参数缺省，则截取剩下所有的字符；如果p大于0从前往后找到p的位置，如果p<0，< span="">

从后往前找到p的位置。oracle中字符串的位置从1开始

instr(c,c1,p,l):查找c1在c中的位置，如果找到返回其实际的位置，找不到返回0；如果p>0，

从前往后找到p的位置（包含p），从p位开始往后找c1出现的第l次；如果p<0，从后往前找< span="">

到p的位置（包含p），从p位开始往前找c1出现的第l次；

p和l都可以缺省，如果缺省其默认值为1

ascii(c)：返回c的ascii值

chr(n)：将ascii码值转成字符

concat(c1,c2)：将c1和c2拼成一个新的字符串，相当于||

• 日期函数：

sysdate：返回当前（服务器）的日期

日期加减一个阿拉伯数字：+n返回当前日期n天后的日期，-n返回当前日期n天前的日期

日期1-日期2：两个日期相差的天数，如果日期1>日期2返回正数，否则返回负数

last_day(d):返回d所在月的最后一天的日期，包含时间部分

add_months(d,n)：返回日期d的n个月后或前（n<0）的日期，包含时间部分< span="">

month_between(d1,d2):返回d1比d2多几个月，如果d1<d2返回负值< span=""></d2返回负值<>

trunc(d,fmt)：对日期截断，fmt可以为yyyy、mm、dd、q、d、hh24、mi、y、day等

TO_CHAR(字符串 | 列，格式字符串)：将日期或者是数字变为字符串显示；

TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；

TO_NUMBER(字符串)：将字符串变为数字显示；

1、查询emp表，将所有员工工资上涨 11.173% 后，四舍五入到十位输出

select ename, round (sal*1.11173,-2)from emp

2、查询emp表，将入职时间格式化为'YYYY-MM-DD HH24:MI:SS' 显示

select HIREDATE, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') from emp

3、将当前时间减12个月然后输出

select add_months(sysdate,-12) from dual

4、查询当前日期月末最后一天显示输出

select last_day(sysdate) from dual

• 空值相关

nvl(c1,c2)如果c1为空返回c2，否则返回c1

nvl2(c1,c2,c3) 如果c1为空返回c3否则返回c2

coalesce(c1,c2,c3.......返回第1个不为空的值，从左往右)

在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

注意：两个参数得类型要匹配

• 递归函数

https://www.cnblogs.com/Soprano/p/10659127.html

注意：

①一个函数在他的函数体内调用他自身称为递归调用，执行递归函数将反复调用其自身，每执行一次进入新的一层。

②为防止递归函数无休止的进行，必须在函数内有终止条件。

③对于一个函数只要知道他的递归定义式和边界条件，就可以编递归函数

drop、delete、truncate的区别：

a、delete删除表的数据，可以回滚（rollback），要想使其数据生效必须要commit，可以加where条件限制删除的记录数，不释放表空间。

b、truncate是ddl语句，清空表数据，释放表空间，不需要commit，不能rollback，只能全表清空；

delete和truncate都是删除表的数据，表结构不会动

C、drop ：ddl语句，删除表结构,不释放表空间，通常drop之前先执行truncate释放表空间。

• 创建表

CREATE TABLE语句创建新表，定义新表的列、索引和其他属性。新表创建

后，表结构定义存放在Teradata的数据字典中。CREATE TABLE语句的语法如下：

CREATETABLE

• 小计

利用WITH BY 进行数据小计

WITH BY的主要特点包括：

 a.它为明细数据表创建分类小计。

 b.跟 GROUP BY不同的是，WITH BY没有剔除明细记录，而是在明细记录后面按照分类增加小计行。

 c.可以允许多于一个字段进行小计，即小计当中可以嵌套小计。

 d.输出结果将根据 BY 后面的所有字段自动进行排序。

• 总计

如果在WITH语句中不带BY则只产生总计。如下面的例子：

• 临时表

临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个bai数据库对象，它和普通表有些类似，然而又有很大区别。它只能存储在临时表空间，而非用户的表空间。ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。

1）ON COMMIT DELETE ROWS

它是临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

2）ON COMMIT PRESERVE ROWS

它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

为什么使用临时表？

临时表是一种辅助工具，能够提高SQL操作的性能。特别是针对下列情况的SQL操作：

不能使用规范化的表；

要求多条 SQL语句完成。

临时表对于非规范化非常有用，如：

• 汇总表

• 重复分组

临时表对于频繁产生的中间结果或作为后续工作基础的中间结果也非常有用。

• 索引

索引是物理模型中的一个概念，利用索引，可以直接存取表中的某一条记录而不需要搜索整个表。

因此，索引提供了一条更快速访问数据记录的途径。

当在数据库中针对某个表创建一个索引时，系统将根据此索引建立一个相应的子表。

相对原来的表(主表)而言，子表要小得多。它将存储索引的值以及一个与此索引对应的数据记录在主表中的存储位置，这好比一个指向数据记录物理位置的指针。

显然，建立索引需要占用额外的存储空间；另外，索引子表是由系统自动维护的，当主表的数据记录发生变化时，系统要自动更新索引子表的相应记录，从而占用系统资源。这就是使用索引的代价。

索引则存在于物理模型中。

选择索引的基本原则是：尽量选择那些访问频率高的属性作为索引。

• 索引

索引类似书的目录，可以提高查询的效率；f5 看是全表扫描还是索引扫描或者sqlplus 中执行 set autotrace traceonly，给耗率大的表增加索引，索引通常建在被where条件过滤的列，且列的重复度不高。     

索引并不是越多越好，因为索引需要表空间存储，对表进行insert、update、delete的时候会自动的对索引进行维护，产生开销，影响效率。

1. 单列索引和复合索引（多列）：多列索引的原理 a、b、c三列相当于创建 a 、ab、abc三个索引create index ix_a on t1(a,b,c);--多列

2. 唯一索引和非唯一索引：唯一索引和主键都要求索引列的值唯一，但是唯一索引的列可以为null，但是主键的列不能为null。create unique index ix_a on t1(a);唯一

3. 函数索引和非函数索引create function index；

4.  b_tree索引和位图索引b_tree：适合数据重复度低的字段，例如身份证号码；就像翻书目录一样，直接定位rowid，查询性能与表中数据量无关；bitmap：用于字段重复度高的，通常用于枚举型的字段，比如性别

三、优化思路及方法

• sql优化

1、避免*，省略列的情况，Select 、insert语句需要写表中具体的列

2、多写注释

3、先过滤在汇总，先过滤再关联

4、尽可能不对原有列进行算术运算，避免隐式转换，防止不走索引

5、Left join替代not in ；Union all替代union

6、在无需排序、去重的情况下不要用order by和distinct

7、对一些大表的关联字段、过滤字段考虑创建索引；

• 如何进行性能优化

1、基于跑批的日志（结束-开始时间），将耗时久的任务单列(程序多时我们可以添加详细的临时表，方便观察每一段程序的执行时间，方便我们去优化)

2、基于该任务（存储过程）的每一步的详细日志，找出耗时较久的sql

3、将sql语句单列，分析执行计划（F5或者set autotrace traceonly），通过加索引来解决（也有可能有索引但是系统判断不走索引快，而系统的判断是错误的，我们就需要其强制走索引，例：SELECT *+INDEX(TABLE INDEX_NAME)*/ 字段FROM 表 WHERE 查询条件）

4、可以考虑实现逻辑的修改（先过滤、再关联），或者将某几张表的关联先落地临时表（临时表分为

1) 会话特有的临时表

　　CREATE GLOBAL TEMPORARY ( )

　　ON COMMIT PRESERVE ROWS；

　　2) 事务特有的临时表

　　CREATE GLOBAL TEMPORARY ( )

　　ON COMMIT DELETE ROWS；），然后基于临时表再去关联

5、大表考虑分区

 性能优化，通常我们开发人员可以做的就是sql的语句的优化，通常是三种方式

 1、索引、分区表

 2、实现逻辑

 3、对列避免进行运算，避免出现*，尽可能不出现*、union、order by等

Oracle性能优化之Oracle里的执行计划

一、执行计划

执行计划是目标SQL在oracle数据库中具体的执行步骤，oracle用来执行目标SQL语句的具体执行步骤的组合被称为执行计划。

二、如何查看oracle数据库的执行计划

oracle数据库中常用的取得目标SQL语句执行计划的方法有以下几种：

（1）explain plan命令

（2）dbms_xplan包

（3）sqlplus中的autotrace开关

（4）10046事件

explain plan命令具体语法：

（1）explain plan命令

（2）explain plan for+目标SQL

（3）select * from table(dbms_xplan.display);

例：

SQL> explain plan for select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;

Explained.

SQL> select * from table(dbms_xplan.display);

dbms_xplan包

dbms_xplan包根据不同的应用场景主要有以下4种方法：

（1）select * from table(dbms_xplan.display);               --结合explain plan命令使用，不再介绍。

（2）select * from table(dbms_xplan.display_cursor(null,null,'advanced/all'));   --可以查看刚刚执行过得sql。

（3）select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced/all')); --可以查看还在缓存中的所有sql的执行计划。要结合v$sql或v$sqlarea视图进行目标sql信息定位使用。

（4）select * from table(dbms_xplan.display_cursor('sql_id'));  --可以查看不再缓存中的sql执行计划，不包括谓语信息。即查看历史执行计划。

（3）sqlplus中的autotrace开关

autotrace开关可以在sqlplus下得到目标sql的执行计划，也可以同时得到目标sql的统计信息。autotrace开关的具体语法如下：

set autotrace {off|on|traceonly} [explain] [statistics]

具体使用方法如下：

（1）set autotrace on：显示目标sql的执行结果，执行计划和统计信息。

（2）set autotrace off：只显示目标sql的执行结果，为默认值。

（3）set autotrace traceonly：显示执行结果数量，执行计划和统计信息。

（4）set autotrace traceonly explain:只显示目标sql的执行计划。

（5）set autotrace tranceonly statistics：只显示目标sql的执行结果数量和统计信息。

表连接

作者：杨建

编辑：徐菲