oracl基础知识
一、 SQL 语句基础
可转换的类型赋值
●CHAR 转换为 NUMBERT
使用 TO_NUMBER 函数来完成字符到数字的转换，如：
v_total := to_number(‘100.0’) + sal;
●NUMBERT 转换为CHAR
使用 TO_CHAR函数可以实现数字到字符的转换，如：
v_comm := to_char(‘123.45’) || ’元’ ;
●字符转换为日期
使用 TO_DATE函数可以实现字符到日期的转换，如：
v_date := to_date('2001.07.03','yyyy.mm.dd');
●日期转换为字符
使用 TO_CHAR函数可以实现日期到字符的转换，如：
v_to_day := to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') ;
可以在SQL>下输入下面命令来验证以上的语句：
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'
||to_char(sysdate,'dd')||'日' from dual;
TO_CHAR(SYSDAT
2001年07月02日
SQL> alter session set nls_date_format ='yyyy"年"mm"月"dd"日"'; Session altered.
SQL> select sysdate from dual;
SYSDATE
2001年04月24日
SQL> select to_number('133')+200 from dual;
TO_NUMBER('133')+200
333
SQL> select to_char('321')||'元' from dual;
TO_CH
321元
SQL> select to_date('2001.07.03','yyyy.mm.dd') from dual;
TO_DATE('2
03-7月 -01
DATE
Oracle 的日期型用7个字节表示，每个日期型包含如下内容：
●Century (世纪)
●Year （年）
●Month（月）
●Day （天）
●Hour （小时）
●Minute （分）
●Second （秒）
日期型字段有下面特点：
●日期型字段的插入和更新可以数据型或字符并带to_date 函数说明即可。
●缺省的日期格式有NLS_DATE_FORMAT参数控制，它的缺省格式为DD-MON-YY。
●缺省的时间是夜里 00:00:00 (即0点 0分0秒)。
●sysdate返回的是服务器的时间，见下面例子。
●日期格式的显示可以设置，见下面例子。
●日期型可以运算，见下面例子。见下面例子。
世纪用cc 表示；年用yyyy表示，月用mm表示，日用dd表示，小时用hh24表示，分用mi表示，秒用ss 表示。
SQL> create table save_info(per_id varchar2(20),name varchar2(20),tran_date date,
2 tran_val number(12,2));
SQL> insert into save_info values ( '110105*********','赵元杰',
2 to_date('2001.06.18','yyyy.mm.dd'),12345.66);
SQL> select * from save_info;
PER_ID NAME TRAN_DATE TRAN_VAL
110105********* 赵元杰 18-6月 -01 1234.66
SQL> select per_id,name,to_char(tran_date,'yyyy/mm/dd'),tran_val from save_info;
PER_ID NAME TO_CHAR(TR TRAN_VAL
110105********* 赵元杰 2001/06/18 12345.66
SQL> show parameter nls_date_format
NAME TYPE VALUE
nls_date_format string
SQL> alter session set nls_date_format=
2 '"公元"yyyy"年"mm"月"dd"日"';
SQL> select sysdate from dual;
SYSDATE
公元2001年05月18日
SQL> select to_char(sysdate,'cc yyyy.mm.dd') from dual;
TO_CHAR(SYSDA
21 2001.05.18
1.系统日期 sysdate 的显示
用sysdate 可以显示ORACLE RDBMS 所在机器的日期及时间，如：
SQL> alter session set nls_date_format ='"公元"yyyy"年"mm"月"dd"日"';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
公元2001年05月30日
2.日期类型的显示
select sysdate,to_char(sysdate,’yyyy.mm.dd hh24:mi;ss’) from dual;
SQL> connect scott/tiger
SQL> alter session set nls_date_format ='yyyy"年"mm"月"dd"日生"';
会话已更改。
SQL> col HIREDATE heading 生日
SQL> col sal heading 工资
SQL> col sal ename 姓名
SQL> select ename,sal,hiredate from emp;
姓名工资生日
SMITH 800 1980年12月17日生
ALLEN 1600 1981年02月20日生
2.8 建立简单报告
在用 SPOOL 命令将显示结果输出到一个操作系统文件中去，一般输出文件的类型为.LST。
3． SPOOL命令
SPOOL filename 将缓冲区的内容写到文件中
SPOOL off 终止写命令
SQL>SPOOL c:\all_emp
SQL>select ename,sal,deptno,hiredate from emp order by deptno;
SQL>SPOOL OFF
4.输入变量
Oracle提供一种在处理SQL语句时可以将参数作为变量来对待的技术，即在条件句中可以是变量而不是具体的值，这样的处理就是输入变量。这样做的目的就是可以重复使用同样的语句，每次只要输入相应的值即可。要实现将参数写成为变量，只要在变量前加一个&号即可。看下面语句：
Select sid, serial#,username, command from v$session
Where USERNAME = upper(‘&usr’);
这样的语句在运行中，系统会自动提示你回答变量的具体值，上面语句运行时提示和回答时显示的信息如下：SQL> Select sid, serial#,username, command from v$session
2 Where USERNAME = upper('&usr');
输入 usr 的值: sys
原值 2: Where USERNAME = upper('&usr')
新值 2: Where USERNAME = upper('sys')
SID SERIAL# USERNAME COMMAND
7 26 SYS 3
在变量说明中，可以使用多个变量，比如：
Alter system kill session ‘&sid,&ser’; 或
Alter system kill session ‘&会话号,&序列号’;
它的运行情况如下：
SQL> Select sid, serial#,username, command from v$session;
SID SERIAL# USERNAME COMMAND
1 1 0
2 1 0
3 1 0
4 1 0
5 1 0
6 1 0
7 26 SYS 3
8 16 ZHAO 0
SQL> Alter system kill session '&sid,&ser';
输入 sid 的值: 8
输入 ser 的值: 16
原值 1: Alter system kill session '&sid,&ser'
新值 1: Alter system kill session '8,16'
系统已更改。
一般系统缺省下是使用“&”符号来定义变量，你也可以使用另外的符号来代替，比如不喜欢用 & 而要用？，则有：
SQL> set define ?
SQL> select sid,serial#,username from v$session where username='?usr';
输入 usr 的值: SYS
原值 1: select sid,serial#,username from v$session where username='?usr'
新值 1: select sid,serial#,username from v$session where username='SYS'
SID SERIAL# USERNAME
726 SYS
次时由于我们修改了会话环境的参数值，可以用下面命令查看：
SQL> show define
define "?" (hex 3f)
5.建立表结构
建立表结构是每个应用系统都必须进行的工作。由于建立表结构是一项统一规划和统一设计的工作。应该是由总设计师根据用户的具体应用需要来定。表的设计是否合理关系到应用系统将来的成败与性能问题。因此，任何担当总设计师角色的人都不要轻视这项工作。
§3.1.1 建立表结构命令
由于创建表的命令非常长，这里仅给出一些主要的部分，
CREATE TABLE 命令简要语法如下：
CREATE TABLE [USER.] table_name
( { COLUMN1 DATATYPE
[DEFAULT EXPN] [COLUMN_CONSTRAINT] |TABLE_CONSTRAINT }
[, { COLUMN1 DATATYPE
[DEFAULT EXPN] [COLUMN_CONSTRAINT] |
TABLE_CONSTRAINT }] ... )
[CLUSTER CLUSTER (COLUMN1 [,COLUMN2] ...) ]
[PCTFREE N]
[PCTUSED N]
[INITRANS N]
[MAXTRANS N]
[STORAGE N]
[TABLESPACE TABLESPACE]
[ ENABLE | DISABLE]
[ AS QUERY]
其中：
schema 包括基表的模式（缺省：当前用户的帐号）
table_name 表名
column 列名（字段名），ORACLE7最多254列，ORACLE8可达1000个列。
datatype 列数据类型
DEFAULT 当前列的缺省值（常数）
Column constraint 列约束
Table_constraint 表约束
PCTFREE 用于更新（update）的空间百分比（1-99）
0表示在插入时完全填满数据块，缺省为10
PCTUSED 为表的每个数据块保留的可用空间的最小百分比. 取值1-99，缺省为40。
PCTFREE和PCTUSED的组合决定了将插入的数据放入已存在的数据块还是放入一个新的块中。
INITRANS 指定一个在每一个数据块中分配的事务入口的初始数1-255，
缺省为1，每一个更新块的事务都需要在块中有一个事务入口
（大小依OS），一般不需要指此参。
MAXTRANS 指定用于更新分配给表的数据块的并发事务的最大数，1-
255，用户一般不应改此参。
TABLESPACE 表空间。如果缺省则表建在用户缺省的表空间（如果建立用户不指定表空间
则该用户的缺省表空间为system）。
STORAGE 存储分配参数
INITIAL integer 初始大小
NEXT integer 下一次的大小
MINEXTENTS integer 最小分配次数
MAXEXTENTS integer 最大分配次数
PCTINCREASE integer 增长百分比（>=0）
ENABLE 激活完整性约束
DISABLE 取消完整性约束
As subquery 建表中查出数据给新表，此语句如果使用，则表的数据类型不需指定，
而是继承原表的类型。
FREELIST GROUP 在并行服务器中指定表或分类、索引组的列表数目。
FREEUST 在并行服务器中指定表、簇、索引的列表数。
提示1：
一般情况下，如果表含有long字段，这样势必需大量的空间，系统会在每次插入新记录时，经常分配空间给表，不久就会出现：
"ORA-01547:Failed to allocate extent of size xxxxx in
tablespace 'xxxx' "
此种情况如果表空间还剩较多的连续空间的话。则可能是该表分配的空间次数已达最大值。为了对该表能插入新数据，需对该表的存储参数作修改，比如：
SQL>alter table xxx storage(MAXEXTENTS 999 )；
提示2:建议不要对表结构或索引使用 pctincrease大于0的参数以避免将来在运行中产生空间超支问题.
提示3:建立表结构最重要的部分是存储参数（STORAGE）的说明。设置者要特别重视存储参数的估计，设置合理的大小。
6. INSERT操作
INSERT 语句可以完成对表、视图及快照（snapshot）进行数据插入。插入的数据依不同的版本而允许插入的数据类型也不同，最新版本可以在子查询中使用 LOB数据类型。现在最新的版本仍有下面限制：
●不能在语句中使用并行，也不允许从远程进行插入；
●在子查询中不允许使用带有long 类型的字段。
1.INSERT命令语法：
INSERT INTO [user.]table[@db_link][(column1[,column2]...)]
VALUES ( express1[,express2]...|subquery...);
(1)日期的插入
INSERT into emp_house_fund(name,emp_acc_no,tran_date,tran_val)
VALUES('赵元杰','123456',to_date('06/09/2000','dd/mm/yyyy'),99.9);
INSERT into emp_house_fund(name,emp_acc_no,tran_date,tran_val)
VALUES('赵元杰','123456',to_date('2000.06.09','yyyy.mm.dd'),99.9);
INSERT into emp_house_fund(name,emp_acc_no,tran_date,tran_val)
VALUES('赵元杰','123456',to_date('06092000','ddmmyyyy'),99.9);
(2).带 select 的插入
SQL> create table emp_house_fund_sum( per_id verchar2(20), tran_val number(9,2));
SQL> insert into emp_house_fund_sum
select per_id,sum(tran_val) From emp_house_fund grou by per_id;
SQL>select https://www.wendangku.net/doc/287381210.html,, b.per_id,b.tran_val
from emp_house_fund a,emp_house_fund_sum b where a.per_id=b.per_id;
§6.1.1 用文字插入操作
INSERT INTO dept
VALUES (50, ‘PRODUCTION’,’S AN FRANCISCO’);
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, ‘L INKS’,’C LERK’,1.2E3, NULL, 40);
下面语句完成同样的功能，只是用了子查询：
INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)
VALUES (7890, ‘L INKS’,’C LERK’,1.2E3, NULL, 40);
§6.1.2 用子查询插入操作
INSERT INTO bonus SELECT ename, job, sal, comm. FROM emp
WHERE comm > 0.25 * sal OR job IN (‘P RESIDENT’,’M ANAGER’);
7.UPDATE语法
UPDATE [user.]table[@db_link][alias]
SET { column1=express1[,column2=experss2]...|
(column1[,column2]...)=(subquery) }
[WHERE condition|current of cursor];
(1)一般的修改
如果起息日为空时以处理日作为该记录的起息日：
SQL>update emp_house_fund set tran_date=sysdate
Where proc_date=sysdate and tran_date is null;
(2) 带null 的修改
SQL>update emp set per_id=null where length(per_id)<15 or
Substr(per_id,15,1) > 1;
(3) 用文字更新操作
在一般情况下，我们经常使用文字更新操作，即在UPDATE 语句中直接将要更新的数值写在语句中，如：UPDATE emp SET job = ’MANAGER’, sal = sal + 1000, deptno = 20WHERE ename = ’JONES’; UPDATE accounts@boston
SET balance = balance + 500 WHERE acc_no = 5001;
(4)用查询更新操作
在Oracle中，可以根据查询结果来更新表的数据。比如，在申请基金项目时，不允许一个人在大学申请项目又在大学的研究所中申请项目，则可使用下面例1的语句来实现检查。
例1：
SQL>update colle_subjects set app_flag='0'
Where per_id in ( select per_id from univ_subjects );
SQL>update comfort set ( noon, midnigt )=
(select humiddity,temperature from weather
where city='MANCHESTER')
where City='WALPOLE' and Sampledate=to_date('22-dec-2000','DD-MON-YYYY');
UPDATE sales PARTITION (feb96) SET s.account_name = UPPER(s.account_name);
(5) 用相关子查询更新操作
同样可以在更新中使用相关的查询操作，如：
例1．复杂UPDATE 语句：
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = ’BOSTON’),
(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)
WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = ’DALLAS’
OR loc = ’DETROIT’);
例2：相互更新的例子：
UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1;
8. DETELE操作
在应用中，可以使用delete 语句实现将不需要的记录进行删除。值得注意的是，经常使用DELETE 语句对表的记录进行有条件的删除。而无条件的全表删除需要一定的技巧。请看下面的例子。
(1) 用delete删除全部记录的操作
1. DELETE 语法
DELETE [ FROM ] [user.]table [@db_link][Alias] [WHERE condition];
这里的condition 可以复杂的表达式或子查询。
2.例子：
SQL> delete from house.emp_house_fund;
SQL>delete from colle_subjects
Where per_id in ( select per_id from univ_subjects );
(2) 用delete有条件删除部分记录
删除语句用的最多应该是有条件的删除记录。如果我们不是有条件的删除的话，我们应该采用直接建立一个新表、DROP 旧表的方法来达到我们的目的。
DELETE FROM (select * from emp) WHERE JOB = ’SALESMAN’ AND COMM < 100;
修改表结构命令
修改表结构的命令由ALTER TABLE来完成。
(3)删除表中的列：
这是Oracle8i的新功能，它的基本语法为：
ALTER TABLE . . . . . . DROP COLUMN [ CASCADE CONSTRAINTS ];
Alter table emp drop column comm ;
Oracle提供DROP TABLE命令可以实现删除表数据和结构。提醒初学者，不要轻易使用DROP TABLE命令。DROP TABLE 命令
DROP TABLE [user.]table_name[CASCADE CONSTRAINTS]
CASCADE CONSTRAINTS表示所有指向本表的主键，外部键被删掉。当删除一个表时，下面的对象也随之被删掉。
●表的索引；
●指向本表的外部键；
●本表的触发器；
●本表中的分区；
●本表的快照；
●本表的角色和用户权限；
●加在本表的所有限制。
如果你在定义表结构时，采用了主键、外部键来定义了一序列表。则在删除表结构时要小心。不要轻易用CASCADE 子句。
三、数据字典查询例子
●了解数据字典的目的就是查询有关表和索引的信息，下面是简单的查询例子。
例1．查询当前用户的表的基本信息，包括表名、存放的表空间、存储参数：
SQL> select table_name ,tablespace_name,initial_extent,next_extent
2* from user_tables
SQL>
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
ACCESS$ SYSTEM 16384 106496
AQ$_MESSAGE_TYPES SYSTEM 65536 65536
AQ$_PENDING_MESSAGES SYSTEM 65536 65536
例2．查询当前用户的索引的基本信息，包括表名、索引名及表空间、存储参数：
SQL> select index_name,tablespace_name,initial_extent,next_extent
2 from all_indexes where owner=user;
INDEX_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
AQ$_MSGTYPES_PRIMARY SYSTEM 65536 65536
AQ$_PROPAGATION_STATUS_PRIMARY SYSTEM 65536 65536
ASSOC2 SYSTEM 16384 16384
这里的where owner=user 表示只查当前用户的索引.
例3．查询当前用户的索引及索引的列名：
SQL> col column_name for a40
SQL> col index_name for a18
SQL> select index_name,table_name,column_name from all_ind_columns
2* where table_owner=user;
INDEX_NAME TABLE_NAME COLUMN_NAME
I_ACCESS1 ACCESS$ D_OBJ#
I_ACCESS1 ACCESS$ ORDER#
AQ$_MSGTYPES_PRIMARY AQ$_MESSAGE_TYPES QUEUE_OID
四、数据库链接
Oracle本地要与远程进行连接，要通过数据库链接。
1.使用数据库链接进行查询和更新：
SELECT * from worker;
这样的语句表示在本地进行查询。而对于远程，则需要在语句后加相应的数据库链接。如：
SELECT * FROM worker@remote_connect;
如果想省去@号后面的字串，可以采用建立一个本地的同义词来实现。如：
CREATE SYNONYM WORKER_SYN for WORKER@remote_connect;
如果希望进行远程更新的话，类似可以在UPDATE语句中加上远程连接符。如：
UPDATE worker@remote_connect SET lodging=’CRANMER’ where lodging=’ROSE HILL’;
2.创建数据库链接的语法：
创建数据库链接的语法如下：
CREATE [PUBLIC] DATABASE LINK remote_connect
CONNECT TO username identified by password using ‘connect string’;
一般PUBLIC有DBA来创建。个人用户可以不加PUBLIC就是私有的数据库链接。
Oracle系统可以创建链接，但是连接的数量有限制。缺省的并发数是4（由init.ora文件中的OP_LINKS来限制）。为了建立数据库链接，需要有CREATE DATABASE LINKS 系统权限；要与远程的帐号进行连接还需要有CREATE SESSION普通权限。
CREATE database link zhao connect to zhaoyuanjie identified by zhao_yuan_jie
Using ‘sun450’;
这样创建后，可以用下面语句来使用：
SELECT * from tst@sun450;
需要注意的是，在创建数据库链接时是根据Oracle的Tnsnames.ora参数文件中的连接字符串来填写连接字串。比如上面的连接字串是sun450 。则在tnsmaes.ora中的要有下面的数据项：
SUN450 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbsvr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = s450)
)
)
五、使用同义词获得本地透明
在应用系统的生命周期内，为了方便和维护简便，经常采用建立同义词来实现透明的访问。
例1．没有建立本地透明时的访问：
SELECT * FROM north.worker;
例2.建立一个同义词，然后进行访问：
CREATE SYNONYM WORKER FOR NORTH.WORKER;
SELECT * FROM WORKER;
在实际应用中，为了达到隐藏表的所有权，还可以通过使用数据库链接和同义词来隐藏数据的物理位置。通过使用对应远程端的本地表同义词，可以把另一个逻辑层从应用转移到数据库中。
例3.建立一个本地同义词的远程表：
CREATE SYNONYM WORKERSKILL For WORKERSKILL@remote_connect;
建立同义词
要建立同义词，首先要有Create any synonym和drop any synonym权限方可建立和撤消，如果某个用户不能建立同义词，则应给其授该权限。
CRAETE [PUBLIC] SYNONYM [user.]synonym FOR [user.]table [@database_link];
Create public synonym emp For scott.emp@sales；
例2：为当前用户的所有对象建立公共同义词，可用下面各命令来完成创建一个脚本：
set echo off
set head off
set verify off
set linesize 200
set pages 0
set feedback off
set term on
undefine p_user
def p_user = &&p_user
Prompt Generating Script To Drop User
set term off
SPOOL create_syn.sql
select 'drop public synonym '||object_name||' ;' from user_objects;
select ' create public synonym '||object_name||
' for sale.'||object_name||' ;' from user_objects;
SPOOL OFF
Start create_syn.sql
注意：当创建同义词后，还要将该同义词授权给public ，才能使其他的Oracle用户可以访问该同义词。
同义词数据字典：
DBA_SYNONYMS实例中所有同义词
USER_SYNONYMS（=SYN）用户的同义词
删除同义词
DROP PUBLIC synonym [schema.]synonym ;
Drop synonym emp；
例1：为当前所有对象建立同义词。为了省去编辑，可用下面个命令来完成：
* 需具有 dba, -- create any synonym,drop any synonym 权限
select 'drop public synonym '||object_name||' ;' from user_objects;
select ' create public synonym '||object_name||
' for sale.'||object_name||' ;' from user_objects;
六、权限和角色
为了管理复杂系统的不同用户，Oracle系统提供了角色和权限。权限可以使用户能访问对象或执行程序。而角色是一组权限的集合，同样，角色被授予用户后，用户也具有某些权限。Oracle有三种类型的权限：
●对象权限（Object）
●系统权限（System）
●角色（Role）
1、建立角色
角色是一组权限的集合。角色包含一个或多个权限；角色可以再包含角色。
建立角色的命令语法如下：
CREATE ROLE role_name
[ NOT INDENTIFIED |
[ INDENTIFIED [BY password | EXTERNALLY|GLOABALLY] ] ];
role 指定角色名；
NOT IDENTIFIED 不需口令有数据库验证；
IDENTIFIED 需要口令，分别为三种情况：
●BY password 指定本地用户口令，如果在建立角色时用口令，则用户在使用时也要给出口令。
●EXTERNALLY 指定为外部用户，该用户用外部服务进行验证。
CREATE ROLE role
NOT IDENTIFIED
IDENTIFIED
BY password
EXTERNALLY
GLOBALLY;
CREATE ROLE vendor IDENTIFIED GLOBALLY;
2、三种标准角色：
自从Oracle6版本以后，Oracle系统一直有三种缺省的角色，即：
●CONNECT ( 连接角色)
具有CONNECT 角色用户，可以进行 SELECT ，INSERT，UPDATE和DELETE操作。
●RESOURCE (资源角色)
具有RESOURCE角色的用户可以进行CONNECT所做的工作，此外，还可以进行CREATE TABLE、CREATE SEQUENCE、CREATE PROCEDURE、CREATE TRIGGER、CREATE INDEX及CREATE CLUSTER等。
●DBA(数据库管理员角色)
具有DBA角色的用户可以进行除 RESOURCE外，还可以进行数据库的管理操作。
3、给角色授权
一旦创建完角色，用户就可以给角色授权。给角色授权的GRANT语句在前面介绍。通过GRANT语句可以对角色授各种权限，如用户对象的访问权，系统权限等。如果用户具有DBA权限的话，则用户有GRANT ANY PRIVILEGE 系统权限。可以对角色授予各种权限。如：
GRANT CREATE SESSION TO clerk;
GRANT CREATE SESSION,CREATE Database link to mamager;
4、授权角色给用户
如果角色创建完毕并且已经给角色授了相应的权限，用户就可以将角色授权给用户了。这样的操作完成后，被授角色的用户就有了相应的权限。要完成这样的操作，只要操作者具有GRANT ANY PRIVILEGE系统权限就可通过GRANT 语句对用户授各种权限。如：
例。假设clerk 是一个角色，则可以将它授予用户：
GRANT clerk TO ZHAO;
七、触发器
触发器是许多关系数据库系统都提供的一项技术。在oracle系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。
1、触发器类型
触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。在Oracle里，触发器事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。Oracle目前的版本将触发器的功能扩展到了触发Oracle系统事件，如数据库的启动与关闭等。
2、创建触发器
创建触发器的一般语法是:
CREATE [ OR REPLACE]TRIGGER trigger_name
[ BEFORE|AFTER ]trigger_event ON table_reference
[ FOR EACH ROW [WHEN trigger_condition] ]
trigger_body;
当一个基表被修改( insert,update,delete)时要执行的内嵌过程。执行时根据其所依附的基表改动而自动触发,因此与应用程序无关,用数据库触发器可以保证数据的一致性和完整性.
每张表最多可建立 12 个触发器,它们是:
before insert
before insert for each row
after insert
after insert for each row
before update
before update for each row
after update
after update for each row
before delete
before delete for each row
after delete
after delete for each row
实现: :new 修饰符访问操作完成后列的值
:old 修饰符访问操作完成前列的值
例1: 建立一个触发器,当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去.
create or replace trigger scott.del_emp
before delete on scott.emp for each row
begin
-- 将修改前数据插入到日志记录表 del_emp, 以供监督使用。
insert into emp_his( deptno , empno, ename , job ,mgr , sal , comm , hiredate )
values( :old.deptno, :old.empno, :old.ename , :old.job,
:old.mgr, :old.sal, :https://www.wendangku.net/doc/287381210.html,m, :old.hiredate );
end;
/
3、删除和使能触发器
当触发器创建完成后，程序员和DBA管理员要经常关心数据库实例中的触发器的情况。对于不必需的触发器，要进行删除或使触发器无效，从而使系统的性能有所提高。
删除触发器的命令语法如下：
DROP TRIGGER trigger_name;
例：从数据子字典中删除某个触发器：
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
SET_NLS
SQL> drop trigger set_nls;
使触发器无效的命令是ALTER TRIGGER，它的语法如下：
ALTER TRIGGER triiger_name [DISABLE | ENABLE ];
SQL> ALTER TRIGGER updatemajorstats DISABLE;
SQL> alter table students disable all triggers;
4、触发器数据字典与管理
当我们根据应用的特殊要求而不得不编写触发器来完成一定的操作时，触发器的源代码就已经存放在实例的数据字典中，我们了解数据字典的目的主要是管理触发器，比如使触发器有效或无效或删除触发器或导出触发器源代码等。
（1）触发器数据字典
与触发器有关的数据字典有：
user_triggers
all_triggers
dba_triggers
（2）无效触发器的编译
触发器与存储过程一样，也存在无效的情况。DBA要及时发现无效触发器并对它们进行编译。下面是对数据库内无效触发器的编译自动产生命令脚本的方法。
1.产生编译触发器的脚本：
SPOOL c:\comp_trig.sql
SELECT 'SET FEEDBACK ON' FROM DUAL;
SELECT 'PROMPT Compile TRIGGER ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER TRIGGER ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'TRIGGER'
ORDER BY OBJECT_NAME;
2.运行脚本comp_trig.sql实现对无效触发器的编译：
START c:\comp_trig.sql
八、SQLPLUS的一些简单命令
1、显示表结构命令DESCRIBE
SQL>DESC 表名
2、调用外部系统编辑器
SQL>edit 文件名
3、运行命令文件
SQL>START test
SQL>@test