oracle学习大全（good）

来源：互联网发布：手机开淘宝店流程步骤编辑：程序博客网时间：2024/05/17 06:53

oracle学习大全（good）

一、数据库语言部分
1. SQL语言：关系数据库的标准语言
2. PL/SQL：过程化语言Procedural Language
3. SQL*Plus：简单的报表，操作系统接口

4. Oracle 8.01后出现:
(1) 数据分区技术：只适用8.01后的版本，数据分散存放，不要放在一个硬盘上，I/O性能好，安全性能好。
(2) 对象技术：存储过程、函数、包、数据库触发器、动态SQL编程
(3) 数据库权限管理
(4) 数据完整性约束(Data Integrity Constraints)

二、Oracle 数据库核心，数据库管理员DBA
数据库的管理与日常维护
数据库总体设计
数据库存储结构设计：物理结构、逻辑结构

/**************************************************************************************************

可以运行Oracle的操作系统：
UNIX：Sun Solaris, HP-UX, AIX, Compaq-Tru64, SCO-UNIX和Linux（运行在PC机上）

Windows NT/2000

P4机器上不能安装Oracle，要想安装需要对安装文件进行修改或者下载补丁程序
在P4机器上安装Oracle的方法(只限于Intel P4机器)：
将Oracle的安装光盘拷贝到硬盘上，然后将/stage/components/oracle.swp.jre/win32/bin/symcjit.dll 文件改名为symcjit.org，然后再开始安装。

***************************************************************************************************/

数据库的备份与恢复
优化与性能调整

三、应用系统开发

四、应用服务器OAS

五、在Windows NT/2000下清除Oracle8i运行环境（重新安装前的准备工作）：

1. 删除Oracle8i注册表：
regedit.exe => HKLM => Software => ORACLE

2. 删除Oracle8i服务：
regedit.exe => HKLM => System => CurrentControlset => Services => 以Oracle开头的服务

3. 删除Oracle8i事件日志：
regedit.exe => HKLM => System => CurrentControlset => Services => Eventlog => Application => 以Oracle开始的事件

4. 删除Windows NT/2000安装磁盘/Program Files/Oracle目录。

5. 删除Oracle8i环境变量
控制面板 => 系统 => 高级 => 环境变量
(1) 删除CLASSPATH
(2) 编辑PATH，将其中与Oracle有关系的路径删除。

6. 删除Oracle8i菜单

7. 重新启动Windows NT/2000，停止服务。

8. 删除Oracle8i主目录。

/*
IP: 75.64.16.X
Mask: 255.255.248.0
GateWay: 75.64.16.3
DNS: 75.64.16.3
*/

[七、Oracle 网络配置]

/*
(1) 查询数据库名：
SQL> select name from v$database;

(2) 查询数据库实例名：
SQL> select instance_name from v$instance;

(3) 查询数据库服务名：
SQL> select value from v$parameter where name='service_names'; // (小写)

(4) 查询全局数据库名(sys用户)：
SQL> select value$ from props$ where name='GLOBAL_DB_NAME'; //　字符串区分大小写
*/

1. Oracle网络驱动使用SQL*Net v2.0 连接

Oracle for Win98 -> SQL*Net Easy Configuration

2. Oracle 网络驱动使用Net8 连接

3. Oracle 网络驱动使用Net8i 连接

Oracle程序组-> Network Administration -> Net8 Assistant

[创建TNS连接过程]:
(1) 启动Net8 Assistant
(2) 本地->服务命名
(3) 编辑->创建...
(4) 第一页：网络服务名：给要使用的网络数据库在本机指定一个Host String，自己定义。
(5) 第二页：协议：TCP/IP(Internet协议)
(6) 第三页：
　　主机名：输入对方机器的IP地址或域名。
　　端口号：默认是1521，一般不需要修改。
(7) 第四页：（Oracle8i)服务名：输入网络数据库的服务名
(8) 第五页：完成。
(9) 菜单：文件－＞保存网络配置。

监视用户会话：
SQL> select username, sid, serial#, machine from v$session;
删除用户会话：
SQL> alter system kill session 'sid,serail#';

/* 练习
sqlplus stud01/stud01
SQL> show user

SQL> select * from emp;

SQL> select * from dept;

*/
[ // 使用下面的方法可以重复执行上一条SQL语句（在SQL*Plus中）
SQL> l//小写字母L，显示上一条SQL语句
1* select username, sid, serial#, machine from v$session
SQL> ///　正斜杠：重复执行上一条SQL语句

SQL> set linesize 1000//将SQL*Plus中显示行宽设成1000个字符。

SQL> connect system/manager@orasjz // 在SQL*Plus中直接连接到另一台机器上的数据库，@字符后是另一台机器的tnsname

]

4. 手工配置Oracle网络连接:主要是配置"tnsnames.ora"文件。
A. 手工配置Oracle网络连接配置文件：tnsnames.ora

(1) 在UNIX中：/u01/app/oracle/product/8.1.6/network/admin/tnsnames.ora

(2) 在Windows 98/NT/2000中：d:/oracle/ora81/network/admin/tnsnames.ora

B. 手工配置Oracle监听进程配置文件：listener.ora

(1) 在UNIX中：/u01/app/oracle/product/8.1.6/network/admin/listener.ora

UNIX下启动进程命令：
$ lsnrctl start // 启动监听进程
$ lsnrctl status// 显示监听进程状态
$ lsnrctl stop// 停止监听进程

(2) 在Windows 98/NT/2000中：d:/oracle/ora81/network/admin/listener.ora

启动进程的命令与UNIX相同。

# LISTENER.ORA Network Configuration File: d:/Oracle/Ora81/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 1521))
//注意：上面一行的HOST必须是你的主机名，否则监听会出问题，也可以使用你的机器的IP地址
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 2481))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:/Oracle/Ora81)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora54)
(ORACLE_HOME = d:/Oracle/Ora81)
(SID_NAME = ora54)// 注意：ora54为数据库的SID名称，不能更改，否则监听出问题
)
)

5. 将主机字符串(Host String)写入注册表(简化SQL*Plus的登录)
regedit.exe -> HKLM -> Software -> Oracle -> home0
增加关键字: local(字符串)，键值：主机字符串名。
/* 将下面的内容复制到一个.reg文件中，在Windows 2000中双击执行，即可实现增加或者修改local键值的作用
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0]
"local"="ora54"
*/

[SVRMGRL: Server Manager的使用方法]
D:/>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal//使用svrmgrl，进入后使用的第一个命令必须是这个命令。
口令:// 如果要求输入口令，请输入oracle
连接成功。
SVRMGR> select name from v$database;// 检查当前使用的数据库名
NAME
---------
ORA44
已选择 1 行。
SVRMGR> shutdown immediate//关闭当前使用的数据库
已关闭数据库。
已卸下数据库。
已关闭 ORACLE 实例。
SVRMGR> startup//启动当前使用的数据库，如果无效，请使用startup force
已启动 ORACLE 实例。
系统全局区域合计有 24433932个字节
Fixed Size 70924个字节
Variable Size 7507968个字节
Database Buffers 16777216个字节
Redo Buffers 77824个字节
ORA-00205: ?????????????????????
SVRMGR>

[另一个启动oracle数据库的例子]
d:/> sqlplus internal/oracle
SQL> startup force//强行重新启动数据库。

[修改口令字]
SQL> grant connect to system identified by NewPassword; //如果用数字作口令，需要使用双引号括起来
SQL> grant connect to sys identified by NewPassword;
SQL> alter user system identified by NewPassword;
SQL> alter user sys identified by NewPassword;
SQL>password//需要输入原口令

// 注：以上修改口令的方法等价；sys与system用户可以互相修改口令；如果sys与system用户的口令都忘记了，使用如下方法：

D:/>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal/oracle
连接成功。
SVRMGR> grant connect to system identified by manager;
语句已处理。
SVRMGR> exit
服务器管理程序结束。

[2002.04.18]
//=========================================================================================================

八、Oracle 产品组成

查询数据库选件产品：
SQL> select * from v$option;
一般都是True, 如果是False, 可以双击激活。
/* cartridges (小产品的)插件，(大产品的)选件(options)*/

[SYS用户是Oracle数据库中权限最大的用户。]

[SQL*Plus登录方法]
1. c:/> sqlplus "/ as sysdba"
2. c:/> sqlplus internal
// 注意：以上两种方法可以类似的使用于SQL*Plus的图形登录界面中
// 用这种登录方法登录进去，所使用的用户均为SYS。
[之所以会出现这种登录方法，是因为在NT的用户组中存在一个ORA_DBA的本地组，凡在此组中的用户使用操作系统认证，即在此组中的用户登录数据库时不需要密码。]

[orapwd命令:修改internal用户的口令字]
/* orapwd的命令行参数
D:/>orapwd
Usage: orapwd file= password=entries=

where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.*/

修改internal口令字(internal默认口令为oracle)认证方法(A B两个步骤)：

A. 修改Oracle登录认证方法：
修改文件SQLNET.ORA文件。
SQLNET.AUTHENTICATION_SERVICES=(NTS)//将这一行前面加上#号注释掉，即可将Oracle的认证方法由操作系统认证改为Oracle认证

SQLNET.ORA文件的位置：
UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet.ora
Windows NT/2000: d:/oracle/ora81/network/admin/sqlnet.ora

B. 修改Internal口令字：
Windows NT/2000:
C:/>ORAPWD file=d:/oracle/ora81/database/pwdora8i.ora
password=YourPassword// YourPassword为你要设置的密码
entries=30
/* ORAPWD file=d:/oracle/ora81/database/pwdora54.ora password=qev entries=30 */

UNIX:
$ orapwd file=$ORACLE_HOME/dbs/orapwSID
password=YourPassword
entries=30
然后重新启动Oracle服务(服务－＞OracleServiceHOSTNAME)。

/* 在执行上述命令之前，需要先将相应目录的pwdora8i.ora(或orapwSID)文件删除或者改名，因为口令字文件不能重名。*/

[tkprof 跟踪文件整理工具]
$ tkprof x.trc x.txt

Windows 2000/NT:
d:/oracle/admin/db_name/udump/*.trc

UNIX:/u01/app/oracle/amdin/db_name/udump/*.trc // */

九、Oracle数据分区技术：8.0以后开始使用

(一)LOB(Large Object)大对象类型数据：
1. BLOB:存储二进制数据，如图象、视频、声音等，用于代替Long raw类型(Oracle7.0以前的数据类型，今后不再支持)
2. CLOB:存储大字符，如：个人简历，用于代替long字段。
3. NCLOB:其它民族语言的支持
(1) 数据库字符集NLS:
Server端：NLS_CHARACTERSET: (如果为以下的字符集，Oracle的数据库可以用来存储汉字)
ZHS16GBK(Oraclei8, Oracle8)
ZHS16CGB231280(Oracle7.3, 8, 8i)

此参数位置在数据字典中，查询核心字符集(语言、日期、货币等)：
SQL>select * from nls_database_parameters;

Client端：NLS_LANG=Simplified Chinese_CHINA.ZHS16GBK (如果没有设置，默认是英文)
regedit.exe

UNIX Client中：
$ NLS_LANG="simplified chinese"_china.zhs16gbk
$ export NLS_LANG

//一般将上述环境变量放入.profile文件中。

/***********************************************插入内容********************************************************

(1) sys是一种用户，internal是一种方式，用来启动关闭数据库，9.0以后不再使用internal，全部是sys。

(2) SQL>set com v7//将8i版本暂时退回到7版

(3) SQL> alter system suspend;// 使用internal用户执行，冻结数据
SQL> alter system resume; // 取消冻结，恢复正常

****************************************************************************************************************/

(2)Oracle数据库核心字符集修改方法：

[*] 修改数据字典(使用SYS用户)：
SQL>update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';
SQL>commit;
然后重新启动数据库。

4. BFILE:外部文件存储，将数据存储在服务器硬盘

十、Oracle 8i 数据库改变：
1. 网络计算数据库
2. 支持IFS(Internet File System)
3. 集成Java虚拟机

[数据库语言]
第一章　　SQL语言基础

一、什么是SQL语言：
Structured Query Language ----　结构化查询语言

SQL*Plus支持的SQL语言：
1. SQL语言（标准的SQL语言）
2. SQL*Plus语言（非标准）：报表或接口
3. PL/SQL 语言（非标准）：程序开发（例如存储过程）

二、SQL语言特点：
1. 非过程化语言
2. 功能强
3. 提供视图功能
4. 两种使用方式
(1)交互式
(2)程序式: SQL> @filename.sql

5. 提供数据控制

三、SQL语言分类：
1. 数据查询语言(QL): Query Language
2. 数据操纵语言(DML): 包括数据的插入、更新和删除，Data Manipulation Language
3. 数据定义语言(DDL Data Definition Language): 建表、建视图、建存储过程等
4. 数据控制语言(DCL Data Control Language): 事务控制、包括权限等

四、SQL关键字（命令）
SQL关键字（以下命令为SQL命令，以分号结束）
1. Alter：修改表结构
2. Audit: 审计，　NoAudit(取消审计)
3. Commit:数据提交，相反的命令：Rollback(回退)
4. Comment:将oracle的注释写入数据字典
5. Create: 建表、数组、索引、视图等，　相反的命令：Drop
6. delete: 删除表中的数据，　Drop是把表中的数据包括结构全部删除
7. Grant:授权，　Revoke:收权(权限回收)
8. Insert:在表中插入新行
9. Lock: 将表强行锁住
10. Rename:修改表名
11. Select: 数据查询
12. Update:数据更新，修改某一个列
13. Validate:校验，对数据进行校验。

五、SQL命令的输入：
在SQL>提示符后输入命令，可以输入多行，以分号结束

六、SQL*Plus 关键字：以回车结束
1. @: 执行外部命令，格式：@路径/文件名
2. #: 注释
3. /: 执行上一条命令
4. Accept:接收键盘输入的命令
5. Append（简化为a): 在行尾增加字符串
/* 例
SQL> select * from dep;
select * from dep
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> a t
1* select * from dept
SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING
*/

6. Break: 分组，　Syntax: break on 分组列 skip n　//n为每两组之间间隔的空行
/* 例：
SQL> break on job
SQL> select * from emp order by job; // 此处务必按分组列排序

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 13-6月 -87 3000 20
7902 FORD 7566 03-12月-81 3000 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS 7788 13-6月 -87 1100 20
7934 MILLER 7782 23-1月 -82 1300 10
7900 JAMES 7698 03-12月-81 950 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7782 CLARK 7839 09-6月 -81 2450 10
7698 BLAKE 7839 01-5月 -81 2850 30
7839 KING PRESIDENT 17-11月-81 5000 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER 7698 08-9月 -81 1500 0 30
7521 WARD 7698 22-2月 -81 1250 500 30

已选择14行。

SQL> break on job skip 1
SQL> /

7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS 7788 13-6月 -87 1100 20
7934 MILLER 7782 23-1月 -82 1300 10
7900 JAMES 7698 03-12月-81 950 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20
7782 CLARK 7839 09-6月 -81 2450 10
7698 BLAKE 7839 01-5月 -81 2850 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7654 MARTIN 7698 28-9月 -81 1250 1400 30
7844 TURNER 7698 08-9月 -81 1500 0 30
7521 WARD 7698 22-2月 -81 1250 500 30

已选择14行。
*/
7. Btitle, Ttitle:设置表尾、表头
SQL> Ttitle '表头'
SQL> Btitle '表尾'
SQL> Ttitle off //失效

8. Change: 写错了改正, Syntax: C/old/new
/* 例：
SQL> select * from detp;
select * from detp
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> c/tp/pt
1* select * from dept
SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING
*/

9. Clear: 清除
SQL> clear break // 清除前面的break设置
SQL> clear buff// 清除SQL*Plus缓冲区中的SQL语句

10. Column: 列定义，用于定义列的显示格式
语法：SQL> column 列名 format 格式
例：SQL> Column sal format $99.999999

11. computer: 统计计算

12. connect: 从一个用户退出，直接进入另一个用户

13. disconnect: 中断当前用户的连接

14. copy: 远程复制（用的较少，一般数据链路)

15. define:定义

16. undefine:取消定义

17. del: 删除当前行

18. describe（简写desc): 显示表结构
例：SQL> desc emp

19. document: 文档注释

20. edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句，保存后，可以直接通过“/”运行编辑过的SQL语句

21. get: 将外部文件调入不执行，只供显示
例：SQL> get c:/sqlplus.ora

22. host: 执行操作系统命令
例：SQL> host dir

23. input(简化为I): 行插入，在当前行的后面插入。

24. List(简化为L): 列示

25. Pause: 设置屏幕暂停
SQL> set pause on//暂停
SQL> set pause off//不暂停
SQL> set 'more' pause on[???]

26. Quit: 退出

27. Remark: 注释

28. Run(R): 类似“/”

29. Save: 将已经运行过的SQL命令保存到磁盘
例：SQL> save c:/run.sql replace//覆盖保存
append//追加

30. set: 设置

31. show: 显示环境变量的值

32. Spool: 显示跟踪
例：SQL> spool c:/文件名
SQL> spool off//停止记录

33. Start: 类似@，执行外部的SQL文件

33. Timing: 服务器端某一命令的执行时间跟踪

七、数据字典：描述系统信息的表、视图、同义词等，由系统自动维护。
例：SQL>select * from tabs;

1. 显示当前所登录的用户：select * from all_users;

2. user_xxx:描述用户创建的对象，如：user_tables，user_views，user_indexes

3. dba_XXX:数据库管理员(sys, system)专用数据字典

4. all_XXX: 描述用户创建的对象，其他用户授权可以存取的对象。
all_objects: 全部的数据字典

[第二章数据库查询语言(QL)]

一、查询语句基本语法：
1. 查询全表的数据：
SQL> select * from emp;

2. 查询某（几）个列：SQL> select ename, sal from emp;

3. DISTINCT 标识：只显示不相同的列
例：
SQL> select job from emp;// 显示结果中有相同的记录
SQL> select distinct job from emp;// 只显示不相同的职业

4. 使用order by将显示结果排序：ASC升序（默认），DESC降序

5. 使用where指出查询条件：
SQL> select * from emp where sal > 2000;

[字符型或日期型用单引号括起来，并区分大小写]

6. 设置日期显示格式：
(1) 确定日历格式：设置参数nls_calendar。

SQL>alter session set nls_calendar='Japanese Imperial';//设置为日本日历
SQL>alter session set nls_calendar='ROC official';//设置为台湾日历
SQL>alter session set nls_calendar='Gregorian';//设置为格林尼治日历，这是我们要设置成的日历。

SQL> select sysdate from dual;//查询系统时间

(2) 确定日期格式：参数nls_date_format
常用的日期格式：
yyyy.mm.dd
yyyy-mm-dd
yyyy/mm/dd
yyyy"年"mm"月"dd"日" //如果要显示汉字，请用双引号括起来
yyyy"年"mm"月"dd"日"dy// dy表示星期

命令格式：
SQL>alter session set nls_date_format='格式';
[此命令只修改前端的显示，退出后即失效，要想永久有效，要修改注册表，在注册表中HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format，键值为日期格式，此处的格式不需要用单引号括起来。]

例：SQL>alter session set nls_date_format='yyyy"年"mm"月"dd"日"dy';

7. Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件，可以将一些环境变量（如linesize）的设置命令或者其它命令放入该文件中。

glogin.sql文件路径：

Windows下：d:/oracle/ora81/sqlplus/admin/glogin.sql

UNIX: /u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql

二、运算符与谓词
1、算术运算符：+, -, *, /

SQL> select sal, sal*12 from emp;//计算工资及年薪。
SQL> select sal, sal+comm from emp; //错误语句，因为comm中存在空值，会使运算结果也会出现空值。
SQL> select sal, sal + NVL(comm,0) from emp; // 正确语句，NVL为空值运算函数，当comm为空值时，该函数返回第二个值0。

2、逻辑运算符：NOT, AND, OR，三个运算符的优先级别依次降低。

3、比较运算符：>, <, =, >=, <=, !=

4、谓词：
(1) IN(或NOT IN):等于（不等于）列表中的任意值。

SQL> select * from emp where job in ('MANAGER', 'CLERK')

(2) (NOT) BETWEEN AND : 表示从小到大的一个范围。[必须是从小到大]
SQL> select * from emp where sal not between 2000 and 3000;

(3) LIKE:模式匹配
SQL> select * from emp where ename like 'S%'; //寻找ename为S打头的记录
%: 表示任意字符串
_(下划线): 表示一个任意字符

SQL> select * from emp where ename like '李%'; //可以使用中文
SQL> select * from emp where ename like 'S_I%'; //寻找ename为S打头，第三个字母为I的记录
SQL> select object_name from all_objects where object_name like 'DBA%';//搜索数据字典中与dba有关系的对象

(4) (NOT) NULL(空值):

SQL> select * from emp where comm is NULL;//查询哪些人没有资金。

5. 伪列：

(1) rowid: 唯一行标识，行被删除之后，rowid不变。
(2) rownum: 行号，一行被删除之后，后面的行号会随之改变

SQL> select empno, ename, rowid, rownum from emp;

[查询SQL语句的相关数据字典]

（1）SQL> select sql_text from v$sqlarea;//查询以前使用过的SQL语句

（2）查询当前连接用户的SQL语句：
SQL> select user_name,sql_text from v$open_cursor;//需要用sys或system用户执行

三、列名别名
SQL> select ename, sal as Salary from emp;
SQL> select ename 职工姓名, sal 工资 from emp;

* # / / select 都不允许做别名，如果一定要用，用双引号括起来。

[第三章　数据操纵语言(DML)]

一、数据插入：

1. 对于表中全部列插入
语法：SQL> insert into　表名 values(值表达式);

例：SQL> Insert into dept values(51, '软件开发部', '北京');

[*] 用desc显示表结构、数据类型、顺序 SQL> desc dept //注意，不要将linesize设置的太大，否则看不到表结构，设置成100即可。
[*] 所插数据必须与目标列一致。
[*] 字符与日期数据使用单引号

2. 对于表中部分列插入
语法：SQL> insert into　表名（列名1，列名2...) values(值表达式);
[*]对于表中的非空列必须插入数据

SQL> insert into emp(empno, ename, job, hiredate,deptno) values(1234, '李大力', '工程师', sysdate - 30, 10);

/* sys 或 system 用户访问其它用户的表的方法

SQL> connect system/ab@ora44
已连接。
SQL> select * from user01.emp*/

/******************** commit　及 Rollback用法:下例是Rollback的一个例子。
SQL> delete dept where deptno = 51;

已删除 1 行。

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING

SQL> rollback;

重算已完成。

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING
51 软件开发部北京

已选择6行。
*************************************************/

3. 使用参数(变量)，临时输入值

SQL> insert into 表名(列名1,列名2, ...) values(&x1, &x2, ...);

SQL> insert dept(deptno, dname, loc) values(&x1, '&x2', '&x3')

/* 例：
SQL> insert into dept values(&x1, &x2, &x3)//&x2, &x3没有用单引号括起来
输入 x1 的值: 52
输入 x2 的值: 'bb'// 字符型数据输入时需要输入单引号
输入 x3 的值: 'cc'// 同上
原值 1: insert into dept values(&x1, &x2, &x3)
新值 1: insert into dept values(52, 'bb', 'cc')

已创建 1 行。

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING
51 软件开发部北京
52 bb cc

已选择7行。

SQL> insert into dept values(&x1, '&x2', '&x3');// 在命令行中给字符或日期型参数加上单引号，则下面输入时不再需要输入单引号

输入 x1 的值: 53
输入 x2 的值: dd
输入 x3 的值: ee
原值 1: insert into dept values(&x1, '&x2', '&x3')
新值 1: insert into dept values(53, 'dd', 'ee')

已创建 1 行。
********************************************************************/

// define x = 7788
// 也可以这样用：select &x1, &x2 from emp;

4. 使用子查询从另一个表中复制数据
语法：Insert into 表名 select 子句;

SQL> insert to emp(empno, hiredate, deptno)
select deptno+7300, sysdate, deptno from dept;

SQL> insert to emp(empno, hiredate, deptno)
select deptno+7300, sysdate, deptno from dept@数据库链路名; //表示dept这个表在网络上的其它服务器中，注意：@之后不是连接串。

SQL> create table pay as select ename, sal from emp;// 用复制的方法创建表
SQL> insert into pay select * from pay;//自己复制自己，可以使一个表飞速扩大。

二、数据更新
语法：SQL> Update 表名 set 列名=值表达式 where 条件;

例：
SQL> update emp set sal = sal + 100 where sal < 2000;
SQL>update emp set sal = 5000, job='MANAGER' where ename = 'SMITH';
SQL> update emp set comm = 100 where comm is null;

三、数据删除
语法：SQL> Delete from 表名 where 条件; //删除数据保留结构，可以回退
SQL>Truncate table 表名; //删除数据保留结构，不可以回退，效率高
SQL>Drop table 表名; //删除数据及结构，不可以回退

[第四章　数据控制语言(DCL)]

一、事务提交：对于数据的插入、更新、删除，只有提交后，数据才真正改变，在提交之前，只有修改了数据的用户才可以看到数据的改变，而其他用户看不到数据的改变。

1. 显式数据提交语法：SQL> commit;

/* 某一个用户对表进行更新等写操作之后，如果没有commit，那么其它用户不能对表进行更新操作，否则就会死掉。
查锁方法：
SQL> select username, sid, serial# from v$session;

解锁方法：
SQL> alter system kill session 'sid, serial#';

*****************************************************************************************************/

2. 隐式数据提交：
下列命令是隐式提交命令：
Create, Alter, Drop, Connect, Disconnect, Grant, Revoke, Rename, Exit, Quit, Audit, NoAudit

3. 自动数据提交：
SQL> set autocommit on//打开自动数据提交开关
SQL> set autocommit off//关闭自动数据提交开关（默认）

二、事务回退
语法：SQL>Rollback;//使数据库回退到最近一次提交后状态，如果一次也没有提交过，回到最原始状态
SQL>Rollbac;
SQL>Rollba;
SQL>Rollb;
SQL>Roll;
//以上的用法都是正确的。

三、设置保存点：
SQL> SavePoint a;

回退到保存点：
SQL> Rollback to a; //此处的Rollback不能再简写。

[第五章　数据定义语言(DDL)]

包括：创建基表、视图、同义词、索引、数据库链路、序列等

一、创建基表
语法：
SQL> Create Table 表名(
列名1　数据类型,
列名2　数据类型,
...);//最多可以到1000个字段

例：SQL> Create Table product(
p_name, varchar2(20),
p_id_number number(7),
p_date date);

1. 数据类型
(1)字符型
char(n): n<=2000，固定长度，如果实际长度不够，前面用空格补齐。
varchar2(n): n<=4000，可变长度，不用空格补齐。
(2) 数字型　number(n):整数，　number(n,d):小数
(3) 日期型
(4) 二进制raw(8i以前的，现在不支持了）
(5) 大字符long：建议不要使用，现在不支持了
(6) blob:存储二进制
(7) CLOB:存储大字符

SQL> Create table employee(
name varchar2(30),
salary number(7,2),
b_date date,
phote blob,
resume clob);

[*]修改数据库兼容性参数（如果在执行上面的SQL语句时，提示clob字段“默认字符集具有不同的宽度”，则需要修改数据库兼容性。）:
D:/oracle/amdin/db_name/pfile/init.ora
compatible=8.0.5 =改为=> 8.1.0或8.1.5
改完后重新启动数据库使修改生效。
/**********************************************
修改前：
SQL> Create table employee(
2 name varchar2(30),
3 salary number(7,2),
4 b_date date,
5 phote blob,
6 resume clob);
resume clob)
*
ERROR 位于第 6 行:
ORA-22866: 默认字符集具有不同的宽度

修改后：
SQL> Create table employee(
2 name varchar2(30),
3 salary number(7,2),
4 b_date date,
5 phote blob,
6 resume clob);

表已创建。

************************************************/

2. 约束条件：数据完整性约束条件(Data Integrity Constaints)

Oracle 常用约束条件：
(1) 非空约束：NOT NULL
(2) 唯一性约束：UNIQUE
(3) 主键：PRIMARY KEY，主键同时具有上面两个约束条件，一个表中只允许有一个主键。
(4) 外键：FOREIGN KEY，这一列值从其它表中取出，允许重复，但不允许修改。
(5) 检查：CHECK (e.g. check (sal > 200)
(6) 引用（参考）：REFERENCES，只能用其它表或者本表的某一列列值，不能随便修改。
(7) 缺省值：DEFAULT，如果没有输入，自动使用DEFAULT值。

3. 约束条件的定义方法：
可以定义为列的一部分，也可以定义为表的一部分。
(1)定义为列的一部分：
SQL> Create Table product(
p_name varchar2(20) unique,
p_id number(7) primary key,
p_date date not null);

[*] Check、Default约束条件：
SQL> Create table employee(
name varchar2(20),
id number(7) primary key,
sal number(11) check(sal > 200 and sal < 2000),
h_date date default sysdate);

一个列具有多个约束条件的写法：
SQL> Create Table product(
p_name varchar2(20) unique,
p_id number(7) primary key,
check(p_id >= 111 and p_id <= 999),
p_date date not null);

例：
SQL> insert into employee(name, id, sal) values('Smith', 2, 201)；
SQL> select * from employee;

NAME ID SAL H_DATE
-------------------- ---------- ---------- --------------
ChenZheng 1 500 2002年04月14日
Smith 2 201 2002年04月19日

(2)定义为表的一部分(不能用在Default 和 Not NUll的定义上)：
SQL> Create Table Product(
p_name varchar2(20),
p_id number(7),
p_date date not null,
constraint p_id_pk primary key(p_id),
constraint p_name_uk unique(p_name));

[*] 查询约束条件：
SQL> select * from user_constraints where table_name = 'PRODUCT';

(3) 另一种约束定义方法：
SQL> Create Table Product(
p_name varchar2(20),
p_id number(7) constraint pk_p_id primary key,
p_date date constraint fk_p_date not null);

4. 使用外键创建主从基表
(1) 创建主表(定义主表):
SQL> Create Table product(
p_name varchar2(20),
p_id number(7) primary key,
p_date date not null);

(2)创建子表，定义外键
SQL> create table sales_list(
sales_name varchar2(20),
sales_id number(7) primary key,
p_id number(7),
constraint p_id_fk foreign key (p_id) references product(p_id));

5. 数据完整性约束条件的修改
(1) 删除约束条件：
A. 删除主键约束：
SQL> Alter table product drop primary key;
SQL> Alter table product drop constraint p_id_pk;

// 以上两种方法等价

B. 删除唯一性约束：
SQL> Alter table product drop unique(p_name);
SQL> Alter table product drop constraint p_id_uk;

C. 删除非空约束
SQL> Alter table product modify(p_date NULL);

D. 删除缺省值：
SQL> Alter table product modify(p_date default null);

(2) 增加约束条件
A. 增加主键约束：
SQL> Alter table product add primary key(p_id);
SQL> Alter table product add constraint p_id_pk primary key (p_id);

B. 增加非空约束
SQL> alter table product modify ( p_date not null);

C. 增加缺省值
SQL> Alter table product modify (p_date default sysdate-1);

[*]查询缺省值：
SQL> select table_name, column_name, data_default from user_tab_columns;

二、修改表结构
1. 在表中增加新列：
SQL> Alter table product add (p_list number(7), p_loc varchar2(20));

2. 删除一个列(只适用于Oracle8i以后的版本）：

SQL> Alter table product drop(p_list, p_loc);//删除多个列
SQL> Alter table product drop column p_list;//删除一个列

3. 修改列宽：
SQL> Alter table product modify (p_name varchar2(40));
//增加列宽没有约束，但减小列宽要求列中数据为空。使用此命令也可以修改列的数据类型。

三、视图(View):视图是虚表。
[*]视图不存储数据
[*]数据来源于基表
[*]不是数据的复制
[*]在同一个表上可以创建多个视图

1. 创建视图的语法：
SQL> Create or Replace view 视图名
as select 语句;

例：SQL> create view manager
as
select * from emp where job='MANAGER';// select * from tab; 检查

SQL> select * from manager;// 查询视图如果查询表

// 第二次创建视图，可以使用or replace参数，不需要再删除而直接覆盖同名视图。
SQL> create or replace view manager
as
select * from emp where job='MANAGER';

[几点说明:]
(1) 在创建视图时，不得使用order by排序。
(2) 在视图中插入数据，则数据被插入到基表中，所以，如果要向视图插入数据，则创建视图时，必须包含表中全部非空列。
(3) 用户视图数据字典：
SQL> select view_name,text from user_views;

2. 视图列别名：

错误语句：
SQL> create view payment as
select sal, sal*12, nul(comm,0)/sal from emp; // 错误原因：视图可以视同为表，所以列名也要符合规定，而sal*12则是不符合规矩的列名。

正确语句：
SQL> create view payment(c1, c2, c3) as// c1, c2, c3即为视图列别名
select sal, sal*12, nvl(comm,0)/sal from emp;

3. 创建视图时增加约束条件：WITH CHECK OPTION

SQL> Create or replace view deptno20 as
select empno, ename, deptno from emp where deptno=20;

SQL> Insert into deptno20 values(1236, '李力', 30);

SQL> select * from deptno20;

// 上面的语句会出现能够通过视图入基表中插入数据，但却不能通过视图看到插入的数据的问题，解决办法：
SQL> Create or replace view deptno20 as
select empno, ename, deptno from emp where deptno=20
WITH CHECK OPTION;

4. 创建Oracle8i的实体化视图(Materialized View):视图不依赖于基表，基表被删除后，视图仍然正常。一般用于两个远程数据库之间的访问，通过数据链路来实现。

(1) 以DBA用户登录，为用户授予创建实体化视图的权限：
SQL> Grant Create Materialized View to 用户名;

(2)以获权用户登录，创建实体化视图：
SQL> Create Materialized View manager as
select * from emp where job = 'MANAGER';

(3) 删除实体化视图：
SQL> Drop Materialized View manager;

删除基表：
SQL>drop table 表名;

删除视图：
SQL> drop view 视图名;

三、创建数据库链路(Database link):
数据库链路：用于数据库之间的远程数据复制。

DB1(UNIX) <------- DB2(NT)

若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

创建数据库链路的步骤：
(1) 创建好连接串。
(2) 创建数据库链路。

1. 创建数据库链路的语法：
SQL> Create database link 数据库链路名
connect to 用户名 identified by 口令
using '主机字符串';

[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名，若没有数据库名，就是数据库名)相同
[*]用户名及口令为远程数据库的用户名及口令
[*]主机字符串为本机tnsnames.ora中网络连接串。

SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

2. 使用数据库链路：
SQL> select * from product@ora31;
SQL> insert into product@ora31 values(...);
SQL> Create table product as select * from product@ora31;

3. 删除数据库链路：
SQL> Drop database link ora31;

(一）创建数据库触发器实现两个数据库之间实时数据传输。
DB1(UNIX) <------- DB2(NT)
若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

[*]在对方的数据库(DB2)上建立触发器，即数据发送方的数据库上建立触发器。
[*]在数据发送方建立指向DB1的数据库链路。

[操作步骤（假设db1为ora31, db2为ora8i)：]
(1) 在DB1上建立用来复制远程数据的表：
SQL> Create table product
as select * from product@ora31;

(2) 在DB2上建立到DB1的数据库链路：
SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

(3) 在DB2上建立触发器：
SQL> Create or Replace trigger insert_product before insert on product
for each row
Begin
Insert into product@ora8i
values(:new.p_id, :new.p_name);
End insert_product;

// 上述创建数据库触发器的语句，请以.号结束，然后以/执行。

/* 查看SQL语句执行的错误信息：
SQL> show errors
*/

(4) 在DB2上测试数据的自动复制是否成功：
SQL> Insert into product values(1005, '测试商品');// 在db2的表中插入一条数据
SQL> select * from product;// 检查数据是否正确插入本地表
SQL> select * from product@ora31;// 检查数据是否复制到了db1的表中

查询数据库链路信息：
SQL> select username, password from user_db_links;

数据更新：
SQL> Create or Replace trigger update_product
before update on product
for each row
Begin
update product@ora8i
set p_id = :new.p_id, p_name = new.p_name where p_id = :old.p_id
End update_product;

数据删除：
SQL> Create or Replace trigger delete_product
before delete on product
for each row
Begin
delete from product@ora8i where p_id = :old.pid
End delete_product

（二）. 创建快照（实体化视图）实现两个数据库之间定时数据库传输：
快照：要求主副站点数据库的用户名相同

(1) 在主节点创建快照日志
语法：SQL> Create snapshot log on 主节点表名; //主节点基表必须含有主键

(2) 在副节点创建快照
语法：
SQL> Create snapshot 快照名
refresh 刷新方式
next 时间间隔
with primary key
for update
as select * from 主节点表名@数据库链路名;

[*]刷新方式：
Compelete:完全刷新
Force:强制刷新(建议使用，强制刷新自动先fast刷新，然后再force刷新)
Fast:快速刷新
[*] 时间间隔：以天为单位。
sysdate + 1/4// 六个小时刷新一次
sysdate + 1/1440// 一分钟刷新一次

/******************************* 教师的部分操作语句 *****************************************************

以下SQL语句为在副节点上的操作语句，主节点对应的用户为user30，例中的photo为要创建快照的表名
create user user30 identified by user30;

grant connect, resource to user30;

grant create snapshot to user30;

connect user30/user30

create database link ora31 connect to user30 identified user30 using 'ora31';

select * from photo@ora31;

create snapshot photo
refresh force
next sysdate + 1/1440
with primary key
for update
as select * from photo@ora31;

********************************************************************************************************/

/**************************　我的操作步骤 ******************************
[1] 主节点：ora8i
SQL> show user
USER 为"STUD29"
SQL> create snapshot log on dept;

实体化视图日志已创建。

SQL> show user
USER 为"STUD29"
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING

SQL> insert into dept values(60, 'test snap', 'snapshot');

已创建 1 行。

提交完成。

[2]副节点

SQL> connect system/ab
已连接。
SQL> create user stud29 identified by stud29;

用户已创建

SQL> grant connect, resource to stud29;

授权成功。

SQL> grant create snapshot to stud29;

授权成功。

SQL> connect stud29/stud29;
已连接。
SQL> connect system/ab
已连接。
SQL> grant create database link to stud29;

授权成功。

SQL> connect stud29/stud29
已连接。
SQL> create database link ora8i connect to stud29 identified by stud29 using 'tea';

数据库链接已创建。

SQL> create snapshot dept
2 refresh force
3 next sysdate+1/2880
4 with primary key
5 for update
6 as select * from dept@ora8i;

实体化视图已创建。
*******************************************************************************************/

[Oracle8i 图形界面管理工具]

Oracle 程序组->Enterprise Manager->

(1)先运行该组中的Configuration Assistant，创建一个新的档案资料库（其作用请查询相应界面上的帮助信息）。

(2) 然后运行该组中的Console程序：
登录用户：sysman密码：oem_temp

然后搜索要管理的节点（使用主机名或者IP地址都可以），节点要想被搜索到，需要先在节点上启动OracleOraHome81Agent服务，要想在节点上使用图形管理工具，要求先启动OracleOraHome81ManagementServer服务。

四、创建索引(indexes):

语法：SQL> create index 索引名 on 表名(列名);

例：SQL> create index index_dept_dname on dept(dname);

索引数据字典：
SQL>select index_name, table_owner, table_name, from user_indexes;

五、创建序列(Sequences):

语法：SQL> Create sequence 序列名
start with 起始编码
increment by 步长
maxvalue 终止编码;

SQL> create sequence id_code
start with 2
increment by 2
maxvalue 999;

序列使用方法：
id_code.nextval// 下一个值
id_code.currval// 当前值

第一次要使用nextval，然后以后每次使用currval。

insert into student values(id_code.nextval, '姓名');

[*] ||(双竖线)在oracle中是连接符号，将两个字符串连成一个，如：'A' || 'B' = 'AB'

[第六章　数据库分区技术]

一、什么是数据分区？
　　数据分区是指把一个表划分成若干小块。在创建表的结构时应考虑好分区方案，选择表中某一列或多列数据作为分区关键字，该关键字决定哪些数据分到哪些区。Oracle对分区进行管理，新插入数据自动存储到相应的分区。

二、创建分区表：

SQL> Create table employee (
id number(7),
name varchar2(20),
sal number(7,2))
Partition by range(sal)
(Partition p1 values less than(500) tablespace users,
Partition p2 values less than(800) tablespace tools,
partition p3 values less than(1000) tablespace system);

// p1, p2, p3是三个分区的名字，users, tools, system是三个表空间的名字。less than是小于（不包含）。
几点说明：
[.] 所插数据不得大于LESS THAN中的最大值
[.] 可以使用MAXVALUE（如上面的语句中，要求工资不能大于1000，如果出现这种情况，则应该改成下面的语句：
SQL> Create table employee (
id number(7),
name varchar2(20),
sal number(7,2))
Partition by range(sal)
(Partition p1 values less than(500) tablespace users,
Partition p2 values less than(800) tablespace tools,
partition p3 values less than(1000) tablespace system,
partition p4 values less then(maxvale) tablespace users);

[.] 不指定表空间时，则该区使用该用户的缺省表空间。
* 查询每个用户的用户缺省表空间：
SQL> select username, default_tablespace from dba_users; // 使用dba用户查询

[*]查询可以使用的表空间名字：
SQL> select tablespace_name, file_name from dba_data_files; // 使用dba（sys或system）来执行

SQL> select dba_users.username, dba_users.default_tablespace, dba_data_files.file_name
from dba_users, dba_data_files
where dba_users.default_tablespace = dba_data_files.tablespace_name;

[temp表空间不能用于数据分区。]

三、分区表的查询方法：
SQL> select * from employee;// 按没有分区的方法查询

SQL> select * from employee partition(p1); // 只查询p1分区的数据。
SQL> create table part3 as select * from employee partition(p3);

四、分区表的修改：
1、增加分区：
SQL> Alter table employee ADD
partition p4 values less than(1500) tablespace users;

[*]分区数据字典：
SQL> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'EMPLOYEE';

2、删除分区:
SQL> Alter table employee DROP partition p4;// 结构数据全部删除（相应分区及数据全部被删除)
SQL> Alter table employee TRUNCATE partition p4;// 保留结构(即区还存在)，数据删除

3、修改区名：
SQL> Alter table employee RENAME partition p4 to p5;

4、分区数据移动：将分区数据从一个表空间移动到另一个表空间
SQL>Alter table employee MOVE partition p4 tablespace system;

5、分区的拆分：

SQL> Alter table employee SPLIT
partition p3 at(900)
into(partition p31, partition p32);

/* 关于数据字典的几点说明：
v$打头的数据字典，后面不会以s结尾，例如：V$database, v$session;
user打头的，后面都会以s结尾(复数), 如：user_tab_partitions, user_tables
dba打头的，有的以s结尾，有的不。
***************************************************************************/

6、分区的合并：
SQL> Alter table employee MERGE partitions p31, p32 into partition p3;

[第七章　SQL*Plus 报表功能]

/********************** 插入内容：数据字典的一些说明 *****************************
(1) user_XXX：用户，例如：user_tables
(2) dba_XXX: DBA专用
(3) all_XXX: 本用户建的，或者其它用户创建本用户可以查询的（需要其它用户的授权）
(4) v$XXX: 动态数据字典，如：v$database, v$instance, v$session，这些数据字典在oracle不启动时也能查询
***********************************************************************************/
一、定义表头与表尾
SQL> ttitle '表头'

SQL> btitile '表尾'

失效：SQL> ttitle off
SQL> btittle off

二、定义列名
语法：SQL> column 列名 heading 别名 // 别名不区分大小写

三、定义列格式：
SQL> column 列名 Format 格式
常用列格式：An : A为字符，n为最大字符宽度。

$99.9999.99
9.99eeee

例：SQL> Column sal format $99.9999.99
SQL> Column comm like Sal

四、分组命令：
语法：SQL> break on 列名　skip n
例：SQL> break on deptno skip 2
SQL> select * from emp order by deptno;

五、统计计算：
语法：SQL> compute 函数 of 统计列 on skip n//可以使用的函数有：sum, max, min, avg,count, var(斜方差), std(标准差)
例：SQL> compute sum of sal on deptno
清除命令：SQL> clear compute
SQL> clear break
SQL> clear column

增加报表级统计：
SQL> break on deptno on REPORT
SQL> compute sum of sal on report
// 整个报表出一个结果，上面两行都要运行，那么整个报表会根据你的设置出一个sum of sal的总计结果。

[第八章　函数]

一、日期格式转换函数：to_char(日期变量,'格式')// 格式要用单引号括起来

(1) 日期格式构成方法:

年月日时分秒
yy mmddhh(12小时制）miss
yyyymondy(星期)hh24（24小时制）
monthday

A. yy.mm.dd, yy/mm/dd, yy-mm-dd, yyyy.mm.dd, ... 加中文也可以，中文要用又引号括起来

SQL>select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;// dual是一个虚拟表，任何用户都可以使用。

二、聚组函数：从一组中返回汇总信息
聚组函数有：Sum, count, count distinct, max, min, avg, stddev(标准差)

例：SQL>select min(sal), max(sal), avg(sal), sum(sal) form emp;
SQL> select ename, job, sal from emp where sal=(slect max(sal) from emp);
SQL> select count(*) from emp;
.....[请参考PowerPiont教程：SQL讲稿.ppt]

[第九章　复杂查询]

一、连接查询：
问：Smith在哪里工作？
答：SQL> select loc from dept, emp where ename = 'SMITH' and emp.deptno = dept.deptno;

二、集合查询[请参考PowerPiont教程：SQL讲稿.ppt P29,30,31]
集合操作是将多个基表的查询结果作UNION运算。
交操作: Intersect
差操作：MINUS

三、子查询(Subqueries):
子查询是在where子句中包含的查询语句，是由系列简单构成的复杂查询。

问：谁与smith在同一部门工作？
答：SQL> select deptno from emp where ename = 'SMITH';
SQL> select ename from emp where deptno = 20;

将两个语句合起来：Select ename from emp where deptno = ( select deptno from emp where ename = 'SMITH');

[第十章　Oracle 权限设置]

一、权限分类：
系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

二、系统权限管理：
1、系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。

2、系统权限授权命令：
[系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)]
授权命令：SQL> grant connect, resource, dba to 用户名1 [,用户名2]...;

[普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。]

例：
SQL> connect system/manager
SQL> Create user user50 identified by user50;
SQL> grant connect, resource to user50;

查询用户拥有哪里权限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;

删除用户：SQL> drop user 用户名 cascade; //加上cascade则将用户连同其创建的东西全部删除

3、系统权限传递：
增加WITH ADMIN OPTION选项，则得到的权限可以传递。

SQL> grant connect, resorce to user50 with admin option; //可以传递所获权限。

4、系统权限回收：系统权限只能由DBA用户回收
命令：SQL> Revoke connect, resource from user50;

系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。

三、实体权限管理
1、实体权限分类：select, update, insert, alter, index, delete, all//all包括所有权限
execute//执行存储过程权限

user01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;

user02:
SQL> select * from user01.product;

// 此时user02查user_tables，不包括user01.product这个表，但如果查all_tables则可以查到，因为他可以访问。

3. 将表的操作权限授予全体用户：
SQL> grant all on product to public;// public表示是所有的用户，这里的all权限不包括drop。

[实体权限数据字典]:
SQL> select owner, table_name from all_tables;// 用户可以查询的表
SQL> select table_name from user_tables;// 用户创建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs;// 获权可以存取的表（被授权的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs;// 授出权限的表(授出的权限)

4. DBA用户可以操作全体用户的任意基表(无需授权，包括删除)：
DBA用户：
SQL> Create table stud02.product(
id number(10),
name varchar2(20));
SQL> drop table stud02.emp;

SQL> create table stud02.employee
as
select * from scott.emp;

5. 实体权限传递(with grant option)：
user01:

SQL> grant select, update on product to user02 with grant option;// user02得到权限，并可以传递。

6. 实体权限回收：
user01:
SQL>Revoke select, update on product from user02;//传递的权限将全部丢失。

四、同义词(Synonym):

1、创建私有同义词：
语法：SQL> create synonym 同义词名 for 代替项;

user01:
SQL> grant select, upate on product to user02;

user02:
SQL> Create synonym product for user01.product;

SQL> select * from product;// 这里的product即user01.product。

同义词数据字典：
SQL> select synonym_name, owner, table_name from all_synonyms;
SQL> select synonym_name, table_name from user_synonyms;

2、DBA可以创建公共同义词(Public Synonym)：公共同义词全体用户可以存取
语法：SQL> create public synonym 公共同义词名 for 代替项;

SCOTT:
SQL> grant select on payment to public;

SYSTEM:
SQL> create public synonym payment for scott.payment;

3. 删除同义词：
User:SQL> drop synonym 私有同义词名;

DBA: SQL> drop public synonym 公共同义词名;

[PL/SQL程序设计]

一、PL/SQL概述
PL/SQL块结构：
Declare
-- 变量定义部分
Begin
-- 可执行语句
Exception
-- 例外处理：对于程序运行中的错误信息、警告信息的说明
End;
.// 以.号结束程序编写

二、PL/SQL语言的特点（参考PowerPoint教程）
1. PL/SQL中可以定义变量，变量有其作用范围。
2. PL/SQL是以块的方式设计，块中可以嵌套子块，子块可以位于块中任何部分。
3.

x. PL/SQL是以块为单位，SQL语句以语句为单位。

7. 减少对Oracle核心的访问，降低网络负载。

三、PL/SQL与SQL语言(参考PowerPoint教程）
1. PL/SQL语句不能使用DDL语句。
可以使用的SQL语句：Insert, Update, Delete, [select into], commit, rollback, savepoint

...

四、PL/SQL基础
1. 变量的使用
2. 单行注释：--
　多行注释：/**/

3. 数据类型：
(1)布尔型: Boolean(TRUE, FALSE, NULL）
(2)数字型：NUMBER
INT = INTEGER

(3) 字符型，基本上没有变化
(4) 日期型
(5) 二进制数据：raw, blob

4. 数据定义：
变量名数据类型
变量名数据类型:=初始值

5. 变量赋值：变量 := 值;

[*]%TYPE:数据类型匹配

s1 char(20);
s2 s1%TYPE;// 定义s2变量，其类型与s1完全匹配。

v_sal emp.sal%type; // v_sal变量的类型与emp表中的sal字段的数据类型完全匹配，%TYPE最经常使用的方法

[*] %ROWTYPE: 行类型，用于存储数据库基表的一条记录。
定义方法：变量基表名%rowtype;

例：
SQL> set serveroutput on // 使能屏幕打印函数的输出

Declare// 如果PL/SQL程序中没有定义变量，那么Declare可以省略
v_empno emp.empno%type := &empno;
r emp%rowtype;
Begin
select * into r from emp where empno = v_empno;
dbms_output.put_line('姓名'|| r.ename||'工资'||r.sal||'日期'||r.hiredate);
//　r.字段名：表示某一列的值；dbms_output.putline()是一个屏幕打印函数
End;

[*] 记录类型Record:
record:
Declare
v_empno emp.empno%type := &empno;
// 定义record类型
type r_emp is record(
v1 emp.ename%type,
v2 emp.job%type,
v3 emp.hiredate%type);

r r_emp;-- 定义变量r为record类型r_emp
Begin
select ename, job, hiredate into r from emp where empno = v_empno;
dbms_output.put_line('姓名:'|| r.v1||'职务:'||r.v2||'工作时间:'||r.v3);
End;

[*] Table类型：类似于C语言中的结构类型数组：
定义方法：TYPE [table_emp] is Table of [emp.ename%type] index by binary_integer; // []内为用户可以修改的部分

使用：mytable = table_emp;
mytable(0) := 'SCOTT';
...
例：
Declare
v_empno emp.empno%type := &empno;
type t_emp is table of emp.ename%type index by binary_integer;

t t_emp;
Begin
select ename into t(10) from emp where empno = v_empno;
dbms_output.put_line('编码为'||v_empno||'的员工是'||t(10));
End;

五、条件控制语句：条件判断语句
1. IF-THEN语句：
IF 条件成立 THEN
可执行语句;　
END IF;

2. IF-THEN-ELSE语句

IF 条件成立 THEN
执行语句1;
ELSE
执行语句2;
END IF;

Declare
v_empno emp.empno%type :=&empno;
v_ename emp.ename%type;
v_sal emp.sal%type;
Begin
select sal,ename into v_sal,v_ename from emp
where empno=v_empno;
if v_sal<2000 then Begin
update emp set sal=sal+100
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
Else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
End if;
End;

3. IF-THEN-ELSIF语句
IF 条件1成立 THEN 执行语句1;
ELSIF 条件2成立 THEN 执行语句2；
ELSIF 条件3成立 THEN 执行语句3;
...
END IF;

Declare
v_empno emp.empno%type :=&empno;
v_ename emp.ename%type;
v_sal emp.sal%type;
Begin
select sal,ename into v_sal,v_ename from emp
where empno=v_empno;
if v_sal<2000 then Begin
update emp set sal=sal+100
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
elsif v_sal<2500 then Begin
update emp set sal=sal+50
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
elsif v_sal<3000 then Begin
update emp set sal=sal+10
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
End if;
End;

六、循环语句：
1. 基本循环：
LOOP
执行语句；
EXIT WHEN 条件成立；
END LOOP;

例：
Declare
v_deptno emp.deptno%type:=&deptno;
i number(2):=0;
Begin
loop
i := i +1;
insert into emp(empno,hiredate,deptno)
values(i+7200,sysdate,v_deptno);
dbms_output.put_line('i的当前值为:'||i);
exit when i=10;
end loop;
End;

2. WHILE循环:

WHILE 条件成立
LOOP 执行语句；
END LOOP;

Declare
v_deptno emp.deptno%type:=&deptno;
i number(2):=0;
Begin
while i < 10 loop
i := i +1;
insert into emp(empno,hiredate,deptno)
values(i+7200,sysdate,v_deptno);
dbms_output.put_line('i的当前值为:'||i);
-- exit when i=10;
end loop;
End;

3. FOR循环:
FOR 计数器 IN 低界..高界
LOOP 执行语句;
END LOOP;

例：
Declare
v_deptno emp.deptno%type:=&deptno;
i number(2):=0;
Begin
while i < 10 loop
i := i +1;
insert into emp(empno,hiredate,deptno)
values(i+7200,sysdate,v_deptno);
dbms_output.put_line('i的当前值为:'||i);
-- exit when i=10;
end loop;
End;

七、光标设计(Cursor):

1、什么是光标？在PL/SQL中，当查询语句执行结果超过一行时，为处理每一行，必须定义一个cursor，叫光标。

2、光标使用方法：
(1) 定义光标：
语法：光标名 is select 语句;

(2) 打开光标：
语法：open 光标名;

(3) 取数据：
语法：Fetch 光标名 into 变量;

(4) 光标下移：使用loop循环

(5) 关闭光标：close 光标名;

-- //要求从emp表中取出按用户输入要求的前几位工资最高的人员及其工资放入topsalary表中：
-- // 我的方法：
SQL>create table topsalary (
name varchar2(20),
sal number(7,2));

SQL>Declare
vcount number(7) := &n;
i number(7) :=0;
vname emp.ename%type;
vsal emp.sal%type;
cursor c is select ename,sal from emp order by nvl(sal,0) desc;--// 使用nvl函数防止工资出现空值的情况，老师补充
Begin
delete topsalary;-- // 先清空topsalary表
open c;

for i in 1 .. vcount loop
fetch c into vname, vsal;

insert into topsalary values(vname,vsal);

end loop;
close c;
End;

--//教师的方法
Declare
i number(3) :=&i;
j number(3) :=0;
cursor c is select ename,sal from emp order by nvl(sal,0) desc;
v1 emp.ename%type;
v2 emp.sal%type;
Begin
open c;
loop
j := j+1;
fetch c into v1,v2;
insert into topsalary values(v1,v2);
exit when j=i;
end loop;
close c;
End;

3. 光标属性: 每一个光标有四种属性
%FOUND 查询语句(FETCH语句)返回记录
%NOTFOUND 查询语句(FETCH语句)无返回记录，用于循环退出条件
%ROWCOUNT FETCH已获取的记录数
%ISOPEN 光标已打开标记

例：
Declare
v1 emp.empno%type;
v2 emp.ename%type;
v3 emp.sal%type;
cursor c is select empno,ename,sal from emp;
Begin
open c;
loop
fetch c into v1,v2,v3;
if v3<3000 then Begin
update emp set sal=sal+100
where empno=v1;
dbms_output.put_line('员工'||v2||'工资已经更新!');
End;
end if;
exit when c%NOTFOUND;
end loop;
dbms_output.put_line('光标处理的行数：'||C%ROWCOUNT);
close c;
End;

4. 隐式光标处理: 隐式光标是指在处理SQL时,不需定义光标，所使用的SQL语句包括：INSERT ,UPDATE,DELETE子句。

隐式光标属性: SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

隐式光标的使用:
Declare
v_empno emp.empno%type:=&empno;
Begin
delete from emp where empno=v_empno;
if SQL%NOTFOUND then
dbms_output.put_line('你的删除失败,数据库无此人:'||v_empno);
end if;
End;

[例外处理Exception]
Declare
v_empno emp.empno%type :=&empno;
v_ename emp.ename%type;
v_sal emp.sal%type;
Begin
select sal,ename into v_sal,v_ename from emp
where empno=v_empno;
if v_sal<2000 then Begin
update emp set sal=sal+100
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
elsif v_sal<2500 then Begin
update emp set sal=sal+50
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
elsif v_sal<3000 then Begin
update emp set sal=sal+10
where empno=v_empno;
dbms_output.put_line('员工'||v_ename||'工资已经修改!');
End;
else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
End if;
Exception-- // 例外处理
when NO_DATA_FOUND then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when TOO_MANY_ROWS then
dbms_output.put_line('你的查询语句返回结果出现多行，请定义光标后重试！');
when OTHERS then
dbms_output.put_line('你的程序是错误的，请仔细检查后重试！');
End;

[用户定义的例外]
(1)在Declare段定义
(2)在Begin段中用Raise引起。
(3)在Exception段中使用。

Declare
v_empno emp.empno%type :=&empno;
no_result exception;
Begin
delete from emp where empno = v_empno;
if SQL%NOTFOUND then raise no_result;
end if;

Exception-- // 例外处理
when NO_DATA_FOUND then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when NO_RESULT then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when TOO_MANY_ROWS then
dbms_output.put_line('你的查询语句返回结果出现多行，请定义光标后重试！');
when OTHERS then
dbms_output.put_line('你的程序是错误的，请仔细检查后重试！');
End;

[存储过程(Storage Procedure)与函数(Function)设计]

一、什么是存储过程？
存储过程（函数）是把一个PL/SQL块存储到数据库中，作为一个数据库实体，可以在其它存储过程、函数、应用程序中调用。
1、存储过程的调用方法：
(1) 在SQL*Plus中调用方法：SQL> Execute 存储过程名称;// execute 可以简写为Exec。
(2) 在其它存储过程、函数、应用程序中调用方法：存储过程名称;

2、存储过程的设计方法：
编写程序->在SQL*PLUS中编译->修改错误->调用执行。

二、创建存储过程的语法：[参考PowerPoint教程:存储过程1.ppt(P3)]
说明：
IN: 调用者向过程传递参数
OUT: 过程向调用者传递参数
IN OUT: 双向传递参数
无Declare

[例1：]IN: 删除数据
Create or Replace procedure DelEmp(v_empno in emp.empno%type) is
Begin
delete from emp where empno=v_empno;
dbms_output.put_line('编码为'||v_empno||'的员工已被除名！');
End DelEmp;

调用方法：
SQL> Exec DelEmp(7788);
SQL> Exec DelEmp(7934);

[例2：]IN: 插入数据
Create or Replace procedure InsertEmp(v_empno in emp.empno%type,
v_ename in emp.ename%type,
v_deptno in emp.deptno%type) is
Begin
insert into emp(empno, ename, hiredate,deptno)
values(v_empno, v_ename, sysdate, v_deptno);
dbms_output.put_line('新员工“'||v_ename||'”录入成功！');
End InsertEmp;

[例3：]IN、OUT:数据查询
Create or Replace procedure QueryEmp(v_empno in emp.empno%type,
v_ename out emp.ename%type,
v_job out emp.job%type) is
Begin
select ename,job into v_ename,v_job from emp
where empno=v_empno;
End QueryEmp;

调用方法：
Declare
v1 emp.ename%type;
v2 emp.job%type;
v emp.empno%type:=&empno;
Begin
QueryEmp(v,v1,v2);
dbms_output.put_line('编码为'||v||'的员工姓名是：'||v1||'职业是：'||v2);
End;

[* 查询存储过程源代码：]
SQL> select text from user_source where name = 'DELEMP'; // 名字要大写

三、创建函数语法：
例1：
Create or replace Function GetSalary( v empno%type) return numbwr is
v_sal emp.sal%type;
Begin
select sal into v_sal from emp
where empno=v;
return v_sal;
End GetSalary;

[调用方法：]
(1) SQL> Exec dbms_output.put_line(GetSalary(7788));
(2) SQL> select GetSalary(7788) from dual;
(3) 使用PL/SQL的方法：
Begin
dbms_output.put_line(GetSalary(7788));
End;

[函数例2]
Create or replace function getmaxsal
return number
is
v_maxsal emp.sal%type;
begin
select max(sal) into v_maxsal from emp;
return v_maxsal;
end getmaxsal;

[调用方法：]
(1) SQL> Exec dbms_output.put_line(GetMaxSal);
(2) SQL> select GetMaxSal from dual;
(3) 使用PL/SQL的方法：
Begin
dbms_output.put_line(GetMaxSal);
End;

[练习题]: 求n!函数（n的阶乘)。

[我的答案]
Create or replace function nj(n in number)
return number
is
numtmp number;
Begin
if n = 0 then
numtmp := 1;
else
numtmp := n * nj(n - 1);
end if;

return numtmp;
End nj;

[教师的答案]// 高，　实在是高！！！
Create or replace function fn(n in number)
return number
is
Begin
if n=0 then return 1;
else return n*fn(n-1);
end if;
End fn;

四、存储过程及函数管理：
1、查询存储过程及函数的源代码：
SQL> select * from user_source;
SQL> select * from dba_source;
SQL> select * form all_source;

2、存储过程及函数的权限管理：
授权命令：
SQL> grant execute on 过程（或函数） to 用户名;
SQL> grant execute on 过程（或函数） to public; --//授权给全体用户

权限回收：
SQL> Revoke execute on 过程（或函数） from 用户名;
SQL> Revoke execute on 过程（或函数） from public;

3、查询错误信息：
SQL> select * from user_errors;
SQL> show errors;

4、查询依赖性信息：
依赖性：实体的结构定义修改时，对创建在这些实体上的存储过程及函数的影响叫依赖性。

显示依赖性关系：
SQL> select name,type, referenced_owner, referenced_name, referenced_type from user_dependencies;

5、删除存储过程及函数：
SQL> drop procedure 过程名;
SQL> drop function 函数名;

[包(Package)的设计与开发]

一、什么是包？
把一些相关的存储过程、函数、变量、光标、例外等组合在一起形成的对象叫包。包由包的说明部分及包体两部分组成。定义在包说明部分的是公共元素，定义在包体的部分是私有元素。

二、使用包的优点：
1、规范化程序设计
2、方便过程及函数的组织
3、便于管理：包的授权只需一次完成
4、优化系统性能：整个包作为一个整体一次调入内存。

三、包的开发步骤：
1、创建包的说明部分：Create package
2、创建包体部分：Create package body
3、在SQL*Plus中运行创建包
4、在存储过程、其它应用中调用。
在SQL*Plus中调用方法：SQL> exec 包名.过程名;

在存储过程、函数、其它应用中调用方法：包名.过程名;

四、创建包的语法：
1、创建包（说明部分)：请参考PowerPoint教程：存储过程1.ppt[Page10]

2、创建包体(Create Package Body): 请参考PowerPoint教程：存储过程1.ppt[Page11]

3、例：请参考PowerPoint教程：存储过程1.ppt[Page12、13、14、15]

五、包的删除：
SQL> drop package 包名;
SQL> drop package body 包名;

查询包源代码：
SQL> select text from user_source where name = '包名'; // 包名大写。

[数据库触发器(Triggers)设计与开发]

一、　什么是触发器？
数据库触发器是一个存储的PL/SQL程序块，它与一个基表联系，当在表上执行特定的数据库维护（插入、删除、更新这三种操作）时，隐含地执行一个PL/SQL程序块。

二、触发器的作用：
。防止非法的数据库操纵、维护数据库安全
。对数据库的操作进行审计，存储历史数据
。完成数据库初始化处理
。控制数据库的数据完整性
。进行相关数据的修改
。完成数据复制
。自动完成数据库统计计算
。限制数据库操作的时间、权限等，控制实体的安全性

三、触发器的组成：
1、触发时间：触发器事件的时间次序（before, afer)[2]

2、触发事件：什么SQL语句会引起触发器触发(Insert, delete, update)[3]

3、触发子体：触发器触发时要执行的操作(一个完整的PL/SQL程序)

4、触发类型：触发器被执行的次数(语句级、行级）[2]//语句级只执行一次，行级会执行多次。

[*]一个表上最多可以创建12个不同类型的触发器：3*2*2 = 12

四、创建触发器注意事项：
1、在触发器中可以调用存储过程、包；在存储过程中不得调用触发器。

2、在触发器中不得使用commit, rollback, savepoint语句。

3、在触发器中不得间接调用含有commit, rollback, savepoint的语句的存储过程及函数。

五、创建语句级触发器：
语句级触发器：请参考PowerPoint教程：存储过程1.ppt[Page19]　该触发器在数据库操作时只执行一次。
说明：
。update中的of是可选项，用于指定语句要修改的列
。要创建的触发器已经存在时，使用replace选项

//例1：before型触发器：
Create or replace trigger DelEmp
before delete on emp
Begin
if (To_Char(sysdate,'dy') in ('星期六','星期日') or
To_number(To_Char(sysdate,'hh24')) not between 8 and 18)
then dbms_output.put_line('现在是非工作时间,请退出!!!');
end if;
End;

[触发器数据字典]
SQL> select table_owner, table_name,trigger_body from user_triggers where trigger_name='DELEMP';

//例2：After型触发器：
Create or replace trigger InsertEmp
after insert on emp// 如果是before，就会比after的结果少一名。
Declare
v_empcount number(7);
Begin
select count(*) into v_empcount from emp;
dbms_output.put_line('目前员工总数已达到：'|| v_empcount|| '名。');
End;

//例3：多个触发条件
Create or replace trigger ChangeEmp
before delete or insert or update on emp
Begin
if (To_Char(sysdate,'dy') in ('星期六','星期日') or
To_number(To_Char(sysdate,'hh24')) not between 8 and 18)
then dbms_output.put_line('现在是非工作时间,请不要修改数据!!!');
end if;
End;

// 更完善的写法：
Create or replace trigger ChangeEmp
before delete or insert or update on emp
Begin
if (DELETING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
To_number(To_Char(sysdate,'hh24')) not between 8 and 18))
then dbms_output.put_line('现在是非工作时间,不要删除数据！');

elsif (UPDATING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
To_number(To_Char(sysdate,'hh24')) not between 8 and 18))
then dbms_output.put_line('现在是非工作时间,不要更新数据！');

elsif (INSERTING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
To_number(To_Char(sysdate,'hh24')) not between 8 and 18))
then dbms_output.put_line('现在是非工作时间,不要插入数据！');

end if;
End;

六、创建行级触发器：
等级触发器：增加选项for each row, 使触发器在每一行上触发。

1、创建行级触发器注意事项：
(1) 在行级触发器中，在列名前增加old表示该列修改前值，增加new表示该列修改后值。
(2) 在PL/SQL中引用时，前边增加冒号。

[例4: 行级触发器] //必须是对所有的行进行操作才行。
Create or Replace trigger UpdateEmp
Before update on emp
for each row
Begin
dbms_output.put_line(:old.sal||'--------->'||:new.sal);
End;

[例5：保存历史数据，这种使用方法很重要，用来保存关键表的历史数据]
CReate or Replace trigger ChangeEmp
Before update or delete on emp
for each row
Begin
Insert into oldemp(empno, ename,job,hiredate,sal)
values(:old.empno,:old.ename,:old.job,sysdate,:old.sal);
End;

SQL> create table oldemp
as select empno, ename,job,hiredate,sal from emp where 1>2;

[例6：修改外键]
Create or Replace trigger UpdateDept
after update on dept
for each row
Begin
update emp
set emp.deptno = :new.deptno
where emp.deptno = :old.deptno;
End;

[例7：删除外键、删除相关数据]
Create or Replace trigger DeleteDept
before delete on dept
for each row
Begin
delete from emp where deptno = :old.empno;
End;

七、触发器管理
1、使触发器失效：
SQL> alter trigger 触发器名称 disable;// 失效
SQL> Alter Trigger 触发器名称 enable;// 生效

SQL> Alter table 表名 DISABLE all triggers;// 一个表上的所有触发器失效
SQL> Alter table 表名 ENABLE all triggers;// 使一个表上的所有触发器生效

SQL> Drop Trigger 触发器名;// 删除触发器;

[第一章]　Oracle 数据库体系结构
物理结构
逻辑结构
内存结构
进程结构

[数据库管理员DBA（Database Administrator）]
DBA职责：
。Oracle核心软件安装与产品升级
。为数据库系统分配存储空间及规划未来存储需求
。创建数据库存储结构
。用户权限与角色管理
。监视与控制用户对数据库的存储
。维护数据库安全
。完成数据库备份与恢复
。监视与优化数据库性能

一、物理结构：数据库是由构成数据库的操作系统文件所组成。
UNIX:/u01/app/oracle/oradata/db_name/ *.*
Windows NT/2000: D:/Oracle/oradata/db_name/*.*

//为保证数据安全，只要将此目录中的所有文件备份即可。 Control0X.CTL文件（X为1，2，3）是一样的，只要有一个完好就可以。

包含三类物理文件：
1、数据文件(DataFiles):存储数据库数据的文件，表、索引、存储过程等都存储在数据文件中。

[*]查询数据文件信息：
SQL> select * from dba_data_files;

/* 数据文件的大小只是表示有多少存储数据的空间，并不表示数据量的大小。*/

[*]查询表空间空闲空间：
SQL> select * from dba_free_space;// 显示结果中包括碎片
SQL>select tablespace_name, sum(bytes) from dba_free_space
group by tablespace_name;

[*] 表空间碎片合并命令：
SQL> alter tablespace 表空间名 coalesce;

SQL> alter tablespace temp coalesce;

[*]数据块大小控制参数：db_block_size = 2K - 32K
在数据库安装后，数据块大小不得修改。

系统缺省值如下：
Oracle7, Oracle8, Oracle8i 8.1.5: 2048(2K)

Oracle8i 8.1.6: 8192(8K)

Oracle8i 8.1.7: 8192(8K)

这个参数存储在d:/oracel/admin/db_name/pfile/init.ora文件中。

SQL> select bytes, blocks, blocks*8192, file_name from dba_data_files;

数据文件的大小都是数据块的整数倍。数据块的大小在数据库安装之前确实，数据库安装之后就不能再修改。

[*] 显示数据块大小：
(1) d:/svrmgrl
SVRMGR> connect internal/oracle

SVRMGR> show parameter db_black_size

SQL> select value form v$parameter where name='db_block_size'; (小写)

[1.1] 在Oracle数据库中，数据文件大小可以修改，命令如下：
SQL> Alter database datafile '数据文件名及路径' resize xxM;

SQL> alter database datafile 'd:/oracle/oradata/ora8i/system01.dbf' resize 100m;
// 上一行中ora8i在具体系统中应该是相应的数据库名

更改之前，查询相应磁盘是否有空闲空间。
用相同的方法可以压缩数据文件的大小，但不能小于已经存储的数据的大小。

[1.2] 在Oracle8i数据库中，数据文件的大小可以自动扩展，命令如下：
SQL> Alter database datafile '数据文件名及路径' autoextend on; --//该数据文件允许自动扩展（缺省）

SQL> Alter database datafile '数据文件名及路径' autoextend off; --//该数据文件不允许自动扩展

[*] 查询数据文件自动扩展属性：
SQL> select bytes, autoextensible, maxbytes, file_name from dba_data_files;

SQL> Alter database datafile '数据文件名及路径' autoextend on
next 50m-- // 每次扩展50M
maxsize 1000m ;--// 最大扩展到1000M，如果unlimited，则受磁盘大小的限制

[*] 查询每次扩展量：
SQL> select bytes, increment_by, file_name from dba_data_files;

[安全起见，建议把自动扩展关闭]

[C.1] Sun Enterprise 6500: 8 * 34G
system01.dbf ==> 33G

SQL> Alter table emp deallocate unused;--// 压缩表未用空间

2、日志文件(Redo log files):记录数据库修改前后的信息，用于数据库恢复。

每一个数据库至少有两个日志文件组，每组有一个或多个日志成员文件。每个组的成员文件的缺省大小是1M。

[*]假设有三个组，每个组有三个成员文件，Oracle的日志工作流程(概括起来为循环覆盖)：

(1) 每个组中的日志成员文件之间的关系是镜像关系，每个成员分别存储到不同的物理磁盘上，只有全部损坏，这个组才算损坏。如果只有一个成员，则这个数据库是不安全的。

(2) 在同一个时刻，只有一个组在工作。当第一组不能全部存放要记录的数据时，则转换到第二组，如果第二组仍然写不下，则转到第三组，第三组仍然存不下，则再转回到第一组。如果第一组日志的归档模式为归档日志，则第一组日志中存储的数据归档到磁盘或者磁带上，然后数据被覆盖，如果是非归档日志（默认模式，建议改变为归档模式），则新的数据直接覆盖第一组的数据，这种情况就会造成数据丢失。

[*] 确认数据库（日志）归档方式：
SQL> select log_mode from v$database;

SVRMGR> archive log list

[C*] Oracle的数据库恢复
不完全数据库恢复：通过备份来回退，会造成部分数据的丢失
完全数据库恢复：数据不会丢失。
两种情况下都需要用到日志，日志对于数据库的恢复至关重要。

[*]
(1) 在线日志:v$log// 当前使用的日志，current
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 199 1048576 1 NO CURRENT 153302 2002年04月
2 1 197 1048576 1 NO INACTIVE 112335 2002年04月
3 1 198 1048576 1 NO INACTIVE 132450 2002年04月

[如果当前日志文件损坏，则数据库无法恢复。（打死也恢复不了）。]
[C*]日志文件组的名称是：REDO0X.LOG(X=1,2,3)。
[对于Oracle数据库来说，还有一个关键文件是SYSTEM01.DBF，如果这个文件坏了，整个数据库系统无法启动，数据也会全部丢失。]

[!!! 一定要做好数据库及日志的备份!!!]

日志文件：v$logfile// 日志文件
归档日志：v$archived_log// 日志组切换时归档到磁盘或者磁带上的日志

(4) 历史日志：v$log_history// 日志的历史记录
SQL> select * from v$log_history;

[*] Redo Log 文件镜像
(1) 一组中Redo Log文件具有相同的信息。
(2) 选择合适的日志文件大小（不能太小，太小容易导致频繁的切换，从而导致过多的磁盘I/O，降低系统的性能）。
[C*] 内存的多少对数据库系统性能的影响是最重要的，日志虽然对系统性能有影响，但没有内存关键，内存一定要大。

[C*] Oracle数据库最大化参数：
maxlogmembers: 最大值是5，缺省值是2。
maxlogfiles：最大254，缺省32个。// 最大的日志文件个数
maxdatafiles：最大65534, 缺省254。
maxloghistory：最大65534，缺省65534
maxinstances: 最大63，缺省1。

[组不需要太多，至少三个，四个一般就可以了]

[C* 不同日志文件组的文件大小可以不同，两个组中文件的个数也可以不同]
(3) 组中的成员同时被更新。
... [请参考PowerPoint教程]

[*] 日志切换：
DBA强制日志切换命令：SQL> alter system switch logfile;

[*] 在Oracle中，文件的扩展名没有任何意义，从以下数据字典中查询相应的文件的文件名
(1) 表空间文件*.dbf: dba_data_file, v$datafile
(2) 日志文件*.log: v$logfile
(3) 控制文件*.ctl: v$controlfile

SQL> select * from v$log;

查询数据文件同步号：SQL> seelct file#, checkpoint_change#, from v$datafile;

控制文件同步号：SQL> select checkpoint_change# from v$database;

/* 随着日志的切换或者时间的推移，文件的同步号不断改变，如果上述同步号不一致，表明数据库出现问题；由此，不能将不同机器上的单个数据库文件互相拷贝，因为同步号不一致，只能把一台机器上的所有数据文件同时拷贝到另一台机器上才可以使用。Oracle的同步号以控制文件(*.ctl)中的为准。 */

Oracle不提供修改同步号的命令。如果在一个数据库中出现同步号不一致的情况，说明这个数据库的数据已经有不一致的部分，原因可能是进行了不完全恢复，只有当数据完全恢复后，同步号才能重新变得一致。

SQL> select * from v$log;

[*]日志管理与配置命令
A. 增加日志文件组：
SQL> Alter database ADD
logfile group n('第一个日志成员文件','第二个日志成员文件') size xxM;
// n为组号。第二个日志成员文件为可选的，两个成员文件最好放到不同的物理磁盘上。

SQL>Alter database ADD
logfile group 4('d:/oracle/oradata/ora8i/log41.log', 'd:/oracle/oradata/ora8i/log42.log')
size 5m;

SQL>Alter database ADD
logfile group 4('d:/oracle/oradata/ora8i/log41.log', 'd:/oracle/oradata/ora8i/log42.log')
REUSE;// 如果在相应的路径下存在同名文件，请加Reuse直接覆盖原来的文件。

[C*]: Oracle不提供修改原来的日志组的属性(文件大小)的命令，解决办法是先创建新的，然后删除旧的

// ************************************* 实际操作内容 ************************************************
SQL> Alter database ADD
2 logfile group 4('d:/oracle/oradata/ora54/ReDo41.log', 'd:/oracle/oradata/ora54/ReDo42.log')
3 size 5m;

数据库已更改。

SQL> select * from v$log;

SQL> alter system switch logfile;

系统已更改。

SQL> /

系统已更改。

SQL> /

系统已更改。

SQL> select * from v$log;