Oracle知识综合整理

1、SQL语言分类种类缩写全称常用操作数据定义语言DDLData define languagecreate, alter, drop数据操纵语言DMLData Manipulation Languageselect, insert, delete, update事务操纵语言TCLTransaction Control Languagecommit, savapoint, rollback数据操纵语言DCLData Control Languagegrant, revokeSQL常用数据类型分类关键字表示范围备注字符char12000字节固定长度varchar214000字节可自增长长度long2

2、GB可自增长长度数值number定义方式number(P,S)P:长度 S:精度日期date日期和时刻部分，精确到整个的秒timestamp存储日期、时刻和时区信息，秒值精确到小数点后6位RAWraw12000字节存储二进制数据long raw2GB存储二进制数据LOBclob4GB能够存储大量字符数据blob4GB能够存储较大的二进制对象，如图形、视频剪辑和声音文件bfile4GB用于将二进制数据存储在数据库外部的操作系统文件中Oracle中的伪列Oracle 中伪列就像一个表列，然而它并没有存储在表中伪列能够从表中查询，但不能插入、更新和删除它们的值常用的伪列有 ROWID 和ROWNUM

3、ROWIDROWID是表中行的存储地址，该地址能够唯一地标识数据库中的一行，能够使用ROWID伪列快速地定位表中的一行。ROWNUMROWNUM是查询返回的结果集中行的序号，能够使用它来限制查询返回的行数。命名规则Oracle中的各种数据对象，包括表名称，视图等等名称的命名都需要遵循Oracle的命名规则。Oracle的命名规则分为标准命名方式和非标准命名方式。标准命名方式以字符打头30个字符以内只能包含A-Z,a-z,0-9,_,$和#。不能和同一个用户下的其他对象重名，不能是oracle服务器的保留字。非标准命名方式你能够使用你想使用的任何字符，包括中文，oracle中的保留字，空格等等，

4、然而需要将对象名用双引号引起来。例如： create table “table” (test1 varchar2(10);创建表空间CREATE TABLESPACE tablespacenameDATAFILE filename SIZE integer K|MAUTOEXTEND OFF|ON;注：大写字母为关键字，小写部分为用户自定义部分；中的内容为可选择部分。例：create tablespace my_tablespace datafile c:myspace size 100K autoextend on;Oracle用户操作创建用户CREATE USER usernameIDEN

5、TIFIED BY passwordDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACE tablespace;例：create user scott identified by tiger;密码修改ALTER USER username IDENTIFIED BY newpassword锁定用户ALTER USER username ACCOUNT LOCK;撤销锁定ALTER USER username ACCOUNT UNLOCK;删除用户DROP USER username;权限治理Oracle权限有两种类型，系统权限和对象权限。系统权限：

6、系统规定用户使用数据库的权限。（系统权限是对用户而言）。实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。系统权限治理系统权限分类DBA：拥有全部特权，是系统最高权限，只有DBA才能够创建数据库结构。RESOURCE：拥有Resource权限的用户只能够创建实体，不能够创建数据库结构。CONNECT：拥有Connect权限的用户只能够登录Oracle，不能够创建实体，不能够创建数据库结构。关于一般用户：授予connect, resource权限。关于DBA治理用户：授予connect, resource, dba权限。系统权限授权系统权限只能由DBA用户授出：sy

7、s, system(最开始只能是这两个用户)授权命令：GRANT connect, resource, dba TO username;一般用户通过授权能够具有与system相同的用户权限，但永久不能达到与sys用户相同的权限，system用户的权限也能够被回收。例：grant connect, resource to scott;系统权限传递增加WITH ADMIN OPTION选项，则得到的权限能够传递。例：grant connect, resorce to user01 with admin option;系统权限回收REVOKE connect, resource FROM usern

8、ame;实体权限治理实体权限分类select, update, insert, alter, index, delete, all等实体权限授权GRANT select, update, insert ON tablename TO username;例：grant all on emp to scott;实体权限传递例：user01:grant select, update on product to user02 with grant option;实体权限回收REVOKE select, update ON tablename FROM username;角色治理角色是一组权限的集合，将

9、角色赋给一个用户，那个用户就拥有了那个角色中的所有权限。系统预定义角色预定义角色是在数据库安装后，系统自动创建的一些常用的角色。角色所包含的权限能够用以下语句查询：select * from role_sys_privs where role=角色名;CONNECT, RESOURCE, DBA这些预定义角色要紧是为了向后兼容。其要紧是用于数据库治理。oracle建议用户自己设计数据库治理和安全的权限规划，而不要简单的使用这些预定角色。DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE这些角色要紧用于访问数据字典视图和包SN

10、MPAGENT用于oracle enterprise manager和Intelligent AgentRECOVERY_CATALOG_OWNER用于创建拥有恢复库的用户。关于恢复库的信息，参考oracle文档Oracle9i User-Managed Backup and Recovery GuideHS_ADMIN_ROLEA DBA using Oracles heterogeneous services feature needs this role to access appropriate tables in the data dictionary.治理角色建一个角色create

11、 role role1;授权给角色grant create any table, create procedure to role1;授予角色给用户grant role1 to user1;查看角色所包含的权限select * from role_sys_privs;scott的权限如图：-查看scott用户具有什么系统权限select * from role_sys_privs;-查看scott用户自己拥有什么角色select * from user_role_privs;-查看scott用户自己具有什么的权限select * from SESSION_ROLES;-查scott用户的创建时

12、刻、用户状态、使用的默认表空间、临时表空间等信息select * from user_users;-查看scott用户中，都哪些用户把对象授予给scott用户select * from user_tab_privs;-查看scott用户中拥有的resource角色都具有什么权限select * from role_sys_privs where role=RESOURCE;-scott用户自己拥有多少表select * from user_tables;-查看scott用户差不多使用多大的空间，同意使用的最大空间是多少select tablespace_name,bytes,max_bytes

13、 from user_ts_quotas;-查看哪些表什么权限给予了其他用户select * from user_tab_privs_made-把自己的表给予给其他用户grant select on emp to mzl;-把表的某一列操作权限给予给其他用户grant update(job) on emp to mzl;创建带有口令以角色(在生效带有口令的角色时必须提供口令)create role role1 identified by password1;修改角色：是否需要口令alter role role1 not identified;alter role role1 identifie

14、d by password1;设置当前用户要生效的角色set role role1;/使role1生效set role role,role2;/使role1,role2生效set role role1 identified by password1;/使用带有口令的role1生效set role all;/使用该用户的所有角色生效set role none;/设置所有角色失效set role all except role1;/除role1外的该用户的所有其它角色生效select * from SESSION_ROLES;/查看当前用户的生效的角色修改指定用户，设置其默认角色alter use

15、r user1 default role role1;alter user user1 default role all except role1;删除角色drop role role1;Oracle用户密码过期解决方法缘故：由于oracle11g中默认在default概要文件中设置了“PASSWORD_LIFE_TIME=180天”所导致。解决方案：1、查看用户的proifle是哪个，一般是default：sqlSELECT username,PROFILE FROM dba_users;2、查看指定概要文件（如default）的密码有效期设置：sqlSELECT * FROM dba_pr

16、ofiles s WHERE file=DEFAULT AND resource_name=PASSWORD_LIFE_TIME;3、将密码有效期由默认的180天修改成“无限制”：sqlALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;修改之后不需要重启动数据库，会立即生效。数据定义语言DDL数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。用于操纵表结构的数据定义语言命令有：CREATE TABLEALTER TABLETRUNCATE TABLEDROP TABLE利用现有表创建新表语法： CREATE

17、 TABLE AS SELECT column_names FROM ;例如： CREATE TABLE newitemfile AS SELECT * FROM itemfile;创建新表语法：CREATE TABLE ( Colname datatype default XXX PRIMARY KEY,Colname1 datatype default XXX NOT NULL | NULLcolnameN datatype default XXX);例如：create table scott.mytable(uid number(5) primary key,name varchar2(

18、10);修改表结构编辑表的字段修改一个列的数据类型(一般限于修改长度，修改为一个不同类型时有诸多限制)。语法：ALTER TABLE 表名 MODIFY(列名,数据类型);例如：alter table skate_test modify (author number(10,0) );增加一个列语法：ALTER TABLE 表名 ADD(列名,数据类型);例如：ALTER TABLE skate_test ADD(author NUMBER(38,0) not null);给列改名语法：ALTER TABLE 表名 DROP COLUMN 列名;例如：alter table skate_test

19、 drop column author;将一个表改名语法：ALTER TABLE 当前表名 RENAME TO 新表名;例如：alter table skate_test rename to test_sakte;添加约束语法：ALTER TABLE 表名 ADD CONSTRAINT constraint_name 约束条件(列名) ；例如：alter table sc add constraint sc_fk_course foreign key(cid) references course(cid);/添加外键 alter table student add constraint ck_

20、student_ssex check(ssex in(男,女);/添加check约束删除约束语法：ALTER TABLE 表名 CONSTRAINT constraint_name；例如：alter table student drop constraint ck_student_sage;截断表使用TRUNCATE命令能够删除表中所有数据，且不能回滚。语法：TRUNCATE TABLE tablename;删除表语法：DROP TABLE tablename;数据操纵语言DML数据操纵语言用于检索、插入和修改数据数据操纵语言是最常见的SQL命令数据操纵语言命令包括：SELECT、INSERT

21、、UPDATE、DELETE增insert语法：INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,);例如：INSERT INTO s_dept VALUES (11,Frinace,2); INSERT INTO s_dept s_emp(id,last_name,first_name,salary,start_date)VALUES (11,Frinace,Jon,1560,03-4月-92); INSERT INTO history SELECT id,last_name,salary FROM s_emp WHERE start_date

22、 UPDATE order_master SET del_date =30-8月-05 WHERE orderno SAVEPOINT mark1;SQL DELETE FROM order_master WHERE orderno = o002;SQL SAVEPOINT mark2;SQL ROLLBACK TO SAVEPOINT mark1;SQL COMMIT;数据操纵语言DCL数据操纵语言为用户提供权限操纵命令 用于权限操纵的命令有：GRANT - 授予权限REVOKE - 撤销已授予的权限例如：SQLGRANT SELECT ON vendor_master TO account

23、s WITH GRANT OPTION;SQLREVOKE SELECT, UPDATE ON order_master FROM MARTIN;PL/SQL差不多语法：DECLARE 声明部分declarationsBEGIN 执行部分executable statementsEXCEPTION 异常捕获部分 handlersEND;声明变量和常量的语法：1.使用赋值语句 :=2.使用 SELECT INTO 语句例如：declare var_name varchar(100);-变量的定义 var_job varchar(100); var_int int; var_const const

24、ant varchar(100) :=;-);-常量的定义 var_date emp.hiredate%type;-引用变量类型 var_rowtype emp%rowtype;-提供表示表中一行的记录类型 var_boolean boolean;-布尔类型,值:TRUE,FALSE,nullbegin select * into var_rowtype from emp where ename=upper(var_name);end;操纵结构PL/SQL 支持的流程操纵结构：条件操纵IF 语句CASE 语句循环操纵LOOP 循环WHILE 循环FOR 循环顺序操纵GOTO 语句NULL 语句

25、条件操纵IF语句IF 语句依照条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF例如：declare var_sal emp1.sal%type;begin select sal into var_sal from emp1 where empno=7369; if var_sal=2000 then update emp1 set sal=sal*1.05 where empno=7369; elsif var_sal=1000 and var_sal2000 then update emp1 set sal=sal*1.1 where

26、empno=7369; else update emp1 set sal=sal*1.2 where empno=7369; end if;end;CASE语句CASE 语句用于依照单个变量或表达式与多个值进行比较。执行 CASE 语句前，先计算选择器的值。例如：begin case &grade when A then dbms_output.put_line(优异); when B then dbms_output.put_line(优秀); when C then dbms_output.put_line(良好); when D then dbms_output.put_line(一般)

27、; when E then dbms_output.put_line(较差); else dbms_output.put_line(无成绩); end case;end;循环操纵LOOP循环语法：while . loop. end loop;exit 终止当前循环 exit when . 条件满足则终止当前循环例如：begin LOOP dbms_output.put_line(哈哈); END LOOP;end;WHILE循环例如：declare var_count number(10):=0;begin while var_countvalue2;reverse表示value2-value

28、1例如：declare var_i number(10):=1; var_j number(10):=1;begin for var_i in 1.9 loop for var_j in 1.9 loop DBMS_OUTPUT.put(var_j|*|var_i|=|var_i*var_j| ); exit when var_j=var_i; end loop; DBMS_OUTPUT.put_line( ); end loop;end;顺序操纵GOTO 语句NULL语句goto - 无条件地转到标签指定的语句null -什么也不做的空语句例如：declare var_a number(2)

29、:=1;begin if var_a5 then goto label1; else goto label2; end if; DBMS_OUTPUT.put_line(label1); null;end;动态SQL动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句。语法：execute immediate dynamic_sql_string INTO define_variable_list USING bind_argument_list;例如：declare var_sql varchar2(200); var_empno number(4); var_emp emp%ro

30、wtype;begin var_empno:=&职员编号; var_sql := select *from emp1 where empno=:arg1 and sal:arg2; execute immediate var_sql into var_emp using var_empno,100; DBMS_OUTPUT.put_line(var_emp.job);end;异常处理异常有两种类型：预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发用户定义异常 - 用户能够在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发预定

31、义异常例如：declare var_id varchar2(5);begin select empno into var_id from emp1; select empno into var_id from emp1 where empno=1111; DBMS_OUTPUT.put_line(我被正常执行啦!);exception when too_many_rows then DBMS_OUTPUT.put_line(返回太多行); when NO_DATA_FOUND then DBMS_OUTPUT.put_line(查询未找到数据);end;用户定义异常例如：DECLARE inv

32、alidCATEGORY EXCEPTION; category VARCHAR2(10);BEGIN category := &Category; IF category NOT IN (附件,顶盖,备件) THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE(您输入的类不是| category); END IF;EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE(无法识不该类不);END;游标游标的类型有：1.隐式游标 2.显式游标 3.REF游标(REF 游标用于处理运行时

33、才能确定的动态 SQL 查询的结果)隐式游标在PL/SQL中使用DML语句时自动创建隐式游标，隐式游标自动声明、打开和关闭，其名为 SQL。通过检查隐式游标的属性能够获得最近执行的DML 语句的信息。隐式游标属性：%found - SQL 语句阻碍了一行或多行时为 TRUE%notfound - SQL 语句没有阻碍任何行时为 TRUE%rowcount - SQL 语句阻碍的行数例如：declare var_empno emp.empno%type;BEGIN select empno into var_empno from emp where empno=7900;IF SQL%FOUND

34、 THEN DBMS_OUTPUT.PUT_LINE(已查找到|sql%rowcount|条数据!);END IF; EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(职员未找到);end;显示游标当查询返回结果超过一行时，就需要一个显式游标。现在用户不能使用select into语句。PL/SQL治理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取数据，关闭。声明游标语法： CURSOR cursor_name IS select_statemen

35、t;打开游标 使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法是： OPEN cursor_name (cursor_name是在声明部分定义的游标名。)关闭游标 语法： CLOSE cursor_name从游标提取数据 从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。语法如下： FETCH cursor_name INTO variable,variable,.例如：declare var_ename emp.ename%type; cursor emp_cur is -声明游标 select ename from emp wher

36、e job=upper(&job);begin open emp_cur; -打开游标 loop fetch emp_cur into var_ename; -提取行 exit when emp_cur%notfound; DBMS_OUTPUT.put_line(var_ename); end loop; close emp_cur; -关闭游标end; 带参数的游标与存储过程和函数相似，能够将参数传递给游标并在查询中使用。这关于处理在某种条件下打开游标的情况特不有用。它的语法如下：CURSORcursor_name(parameter,parameter,.)ISselect_statem

37、ent;定义参数的语法如下：Parameter_nameINdata_type:=|DEFAULTvalue 与存储过程不同的是，游标只能同意传递的值，而不能返回值。参数只定义数据类型，没有大小。另外能够给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在不处引用该参数不一定可靠。在打开游标时给参数赋值，语法如下：OPENcursor_namevalue,value;参数值能够是文字或变量。例如：DECLAREvar_job emp.job%type;var_empno emp.empno%type;var_ename emp.ename%type;

38、CURSOR emp_cur(param_job emp.job%type) IS SELECT empno, ename FROM emp WHERE job=param_job;BEGINvar_job:=upper(&job);OPEN emp_cur(var_job);LOOPFETCH emp_cur INTO var_empno,var_ename;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(var_empno|, |var_ename); END LOOP;CLOSE emp_cur;END;游标中的更新和删除 在PL/SQL中

39、依旧能够使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就能够执行删除或更新记录的方法。语法： CURSOR IS FOR UPDATE OF ;UPDATE SET WHERE CURRENT OF DELETE FROM WHERE CURRENT OF 例如：declare var_sal emp1.sal%type; cursor emp_cur is select sal from emp1 where sal2000 for update of sal;begin open emp_cur; loop f

40、etch emp_cur into var_sal; exit when emp_cur%notfound; update emp1 set sal=var_sal*1.1 where current of emp_cur; end loop; close emp_cur;end;循环游标循环游标用于简化游标处理代码，当用户需要从游标中提取所有记录时使用。语法：FOR IN LOOPEND LOOP;例如：declare cursor emp_cur is select ename from emp;begin for emp_rec in emp_cur loop DBMS_OUTPUT.P

41、UT_LINE(emp_rec.ename); end loop;end;REF游标REF 游标和游标变量用于处理运行时动态执行的 SQL 查询。创建游标变量需要两个步骤：1.声明 REF 游标类型2.声明 REF 游标类型的变量用于声明 REF 游标类型的语法为：TYPE IS REF CURSORRETURN ;打开游标变量的语法如下： OPEN cursor_name FOR select_statement;声明强类型的 REF 游标(有返回值)：TYPE my_cur IS REF CURSOR RETURN emp%ROWTYPE;var_cur my_cur; 声明弱类型的 RE

42、F 游标：TYPE my_cur IS REF CURSOR;var_cur my_cur;例如：declare type my_cur is ref cursor;-声明游标类型 var_emp emp%rowtype; emp_cur my_cur;-申明游标变量begin open emp_cur for select *from emp;-打开动态SQL语句 loop fetch emp_cur into var_emp; exit when emp_cur%notfound; DBMS_OUTPUT.PUT_LINE(var_emp.ename); end loop; close e

43、mp_cur;end;子程序命名的 PL/SQL 块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程 执行某些操作函数 执行操作并返回值过程过程是用于完成特定任务的子程序。创建过程的语法：-创建过程，可指定运行过程需传递的参数CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN-包括在过程中要执行的语句 -处理异常(可选)EXCEPTION END;例如：create or replace procedureget_empno(temp_no emp.empno%type) isvar_emp emp%rowtyp

44、e;begin select * into var_emp from emp where empno=temp_no; dbms_output.put_line(var_emp.ename);exception WHEN NO_DATA_FOUND THEN dbms_output.put_line(无此人信息!); end;过程参数的三种模式：IN用于同意调用程序的值默认的参数模式OUT用于向调用程序返回值 IN OUT用于同意调用程序的值，并向调用程序返回更新的值例如：create or replace procedureswap(temp1 in out number,temp2 in

45、out number)istemp number;begin temp:=temp1; temp1:=temp2; temp2:=temp;end;测试declare a number(2):=1; b number(2):=2;begin dbms_output.put_line(a=|a); dbms_output.put_line(b=|b); swap(a,b); dbms_output.put_line(a=|a); dbms_output.put_line(b=|b); end;函数函数是能够返回值的命名的 PL/SQL 子程序。创建函数的语法：CREATE OR REPLACE

46、FUNCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;定义函数的限制：函数只能同意 IN 参数，而不能同意 IN OUT 或 OUT 参数形参不能是 PL/SQL 类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用 PL/SQL 块使用 SQL 语句例如：create or replace functionmy_funreturn varchar2 isbegin return

47、哈哈;end;测试begin dbms_output.put_line(my_fun); end;程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装。程序包由规范和主体两部分组成规范：声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等。主体：声明程序包私有对象和实现在包规范中声明的子程序和游标。语法：程序包规范：CREATE OR REPLACEPACKAGE IS|ASPublic item declarationsSubprogram specificationEND package_name;程序包主体：CREATE OR REPLACE PACKAGE

48、BODY IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND package_name;例如：-定义程序包规范create or replace package my_package isprocedure print;function print_str(str varchar2) return varchar2;end my_package;-定义程序包主体create or replace package body my_package is procedure print is begin dbms

49、_output.put_line(哈哈); end print; function print_str(str varchar2) return varchar2 is var_str varchar2(100); begin var_str:=str; return str; end print_str;end my_package;-测试begin my_package.print; dbms_output.put_line(my_package.print_str(o)/);end;程序包中的游标游标的定义分为游标规范和游标主体两部分。在包规范中声明游标规范时必须使用 RETURN 子句

50、指定游标的返回类型。RETURN子句指定的数据类型能够是：用 %ROWTYPE 属性引用表定义的记录类型程序员定义的记录类型例如：-程序包规范CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2);END cur_pack;-程序包主体CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETU

51、RN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN OPEN ord_cur(vcode); LOOP FETCH ord_cur INTO or_rec; EXIT WHEN ord_cur%NOTFOUND; DBMS_OUTPUT.PUT_LIne(返回的值为 | or_rec.orderno); END LOOP; END ord_pro;END

52、cur_pack;触发器触发器是当特定事件出现时自动执行的存储过程。特定事件能够是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑触发器由三部分组成：触发器语句（事件）定义激活触发器的 DML 事件和 DDL 事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行语法create OR REPLACE trigger after | before | instead ofinsert or update of

53、column_list or deleteon referencing old as old / new AS newfor each rowwhen (condition)例如create or replace trigger trig_sal after update of sal on emp1 for each row when(new.salold.sal)declare sal_diff number;begin sal_diff:=:new.sal-:old.sal; dbms_output.put_line(工资差额:|sal_diff);end;启用、禁用和删除触发器ALTE

54、R TRIGGER aiu_itemfile disable;ALTER TRIGGER aiu_itemfile enable;DROP TRIGGER aiu_itemfile;触发器类型DDL 触发器 - 在模式中执行 DDL 语句时执行数据库级触发器 - 在发生打开、关闭、登录和退出数据库等系统事件时执行DML 触发器 - 在对表或视图执行DML语句时执行语句级触发器 - 不管受阻碍的行数是多少，都只执行一次行级触发器 - 对DML语句修改的每个行执行一次INSTEAD OF 触发器 - 用于用户不能直接使用 DML 语句修改的视图行级触发器例如：create or replace t

55、rigger bi_test_trgbefore insert or update of idon test_trgfor each rowbegin if inserting then select seq_test.nextval into :new.id from dual; else raise_application_error(-20020,不同意更新ID值!); end if;end;语句级触发器例如：create or replace trigger trgdemoafter insert or update or deleteon test_trgdeclareopt_use

56、r varchar2(20);begin select user into opt_user from dual; if updating then dbms_output.put_line(opt_user|在|to_char(sysdate,yyyy/mm/dd hh:mi:ss)|已更新test_trg中的数据); elsif deleting then dbms_output.put_line(opt_user|在|to_char(sysdate,yyyy/mm/dd hh:mi:ss)|已删除test_trg中的数据); elsif inserting then dbms_outpu

57、t.put_line(opt_user|在|to_char(sysdate,yyyy/mm/dd hh:mi:ss)|已在test_trg中插入数据); end if;end;INSTEAD OF 触发器INSTEAD OF 触发器只能用于视图。例如：-创建视图create or replace view emp_view asselect *from emp;-创建触发器CREATE OR REPLACE TRIGGER emp_view_trg INSTEAD OF UPDATE ON emp_view FOR EACH ROWBEGIN UPDATE emp_view SET name=

58、:NEW.name WHERE id = :NEW.id; DBMS_OUTPUT.PUT_LINE(已激活触发器);END;-测试update emp_view set sal=1000 where empno=7900;用户事件触发器例如：-创建dropped_obj表CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);-创建触发器create or replace trigger log_drop_objafter drop on schemabegin inse

59、rt into dropped_obj values( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, sysdate);END;-测试select *from dropped_obj;drop view emp_view;视图视图是存储在数据库中的预先定义好的查询，具有表的外观，能够像表一样对其进行存取，但不占据物理存储空间。差不多语法：CREATE VIEW view_name ASSELECT STATEMENTWITH CHECK OPTION视图的查询对视图的查询与对差不多表查询一样视图的更新对视图的更新最终要转换成对差不多表的更新视图更新条件：视图必须未

60、涉及连接；视图必须不包含GROUP BY子句；视图不能包含任何组合函数；不能使用DISTINCT子句；WHERE子句不能包含表的嵌套引用。删除视图DROP VIEW view_name从单独的表中创建视图语法：CREATE VIEW view_name AS SELECT * |COL1,COL2, FROM table_name WHERE expression例如：CREATE VIEW emp _ view AS SELECT emp_id,emp_name,phone FROM employee_tbl从多表中创建视图语法：CREATE VIEW VIEW_NAME AS SELECT