韩顺平oracle笔记(自己修改精华并添加触发器内容)
韩顺平Oracle笔记1
•
--例子:如果Scott用户把访问表EMP的权限授予用户xiaoming,并允许它传递这个权限， 用户xiaoming把这个权限传递给了用户xiaohong, 所以现在无论是xiaoming或者是 xiaohong都可以访问scott用户的EMP表，但是当Scott用户收回了用户xiaoming的这个权 限之后，无论是xiaoming或者是xiaohong都没有办法在访问Scott用户的EMP表
• 条件是：is null • 条件是：is not null
删除数据
Oracle表管理
• .删除数据： • --delete from 表名;删除所有记录，但是表格的结构仍然存
在，写日志，可以恢复，速度慢 • 在删除之前，设置savepoint 点名—>删除—>rollback to 点名; 就可以 恢复
Oracle表名和列的命名规则、Oracle支持的数据类型
Oracle表管理
• *Oracle表名和列的命名规则
• • • • . . . . 必须以字母开头 长度不能超过30个字符 不能使用Oracle的保留字 只能使用如下字符A-Z、a-z、0-9、$、#等
• *Oracle支持的数据类型 • .字符型
• •
数字型、日期类型、图片类型
Oracle表管理
• .数字型
• • • --number 范围 10的-38次方到-10的38次方可以表示整数，也可以 表示小数。 --number(5,2) 表示一个小数有5为有效数字，2位小数，范围999.99到999.99 --number(5) u表示一个无谓整数，范围-99999到99999
权限分为、角色分为、数据库中的重要角色、对象权限
Oracle用户管理
韩顺平 玩转oracle 10g 实战教程第4讲
- 不包含子查询、snapshot、VIEW的 SELECT 语句 - INSERT语句的子查询中 - INSERT语句的VALUES中 - UPDATE 的 SET中
玩转oracle 10g实战教程
主讲 韩顺平
序列(sequence)如下例子： 可以看如下例子： INSERT INTO emp VALUES 'CLERK',7566 7566, (my_seq.nextval, 'TOMCAT', 'CLERK',7566, SYSDATE, 1200, NULL,20); SELECT my_seq.currval FROM DUAL; 但是要注意的是： 但是要注意的是： 第一次NEXTVAL返回的是初始值；随后的NEXTVAL NEXTVAL返回的是初始值 NEXTVAL会自动增加你定义的 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的 BY值 然后返回增加后的值。 总是返回当前SEQUENCE INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE 的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL 否则会出错。 NEXTVAL初始化之后才能使用CURRVAL， 的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。 一次NEXTVAL会增加一次SEQUENCE的值， NEXTVAL会增加一次SEQUENCE的值 一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用 多个NEXTVAL 其值就是不一样的。如果指定CACHE NEXTVAL， CACHE值 ORACLE就可以预先 多个NEXTVAL，其值就是不一样的。如果指定CACHE值，ORACLE就可以预先 在内存里面放置一些sequence 这样存取的快些。cache里面的取完后 sequence， 里面的取完后， 在内存里面放置一些sequence，这样存取的快些。cache里面的取完后， oracle自动再取一组到cache。 使用cache或许会跳号， 自动再取一组到cache cache或许会跳号 oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然 不正常down down掉 abort),cache中的sequence就会丢失 中的sequence就会丢失. 不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以 sequence的时候用nocache防止这种情况 的时候用nocache防止这种情况。 在create sequence的时候用nocache防止这种情况。
韩顺平oracle学习笔记
韩顺平oracle学习笔记第0讲：如何学习oracle一、如何学习oracleOracle目前最流行的数据库之一，功能强大，性能卓越。
学习oracle需要具备一定基础：1.学习过一门编程语言（如：java ，c）2.最好学习过一门别的数据库（sql server，mysql ， access）教程推荐：oracle使用教程，深入浅出oracle记住：欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要成为一个oracle高手过程：理解小知识点->做小练习->把小的只是点连成线->做oracle项目->形成只是面->深刻理解Oracle基础部分：oracle基础使用； oracle用户管理； oracle表管理Oracle高级部分：oracle表的查询； oracle的函数； oracle数据库管理；oracle 的权角色； pl/sql 编程；索引，约束和事物。
期望目标：1 学会安装、启动、卸载oracle2 使用sql *plus工具3 掌握oracle用户管理4 学会在oracle中编写简单的select语句第1讲：基础语法内容介绍：1.为什么学习oracle2.介绍oracle及其公司的背景3.学会安装、启动、卸载oracle4.oracle开发工具5.Sql*plus的常用命令6.oracle用户管理一、主流数据库包括：●微软：sql server 和 access●瑞典：mysql AB公司●IBM公司：DB2●美国sybase公司：sybase●IBM公司：infromix●美国oracle公司：oracle(目前最流行的之一)二、oracle安装，启动及卸载1.系统要求：操作系统最好为windows2000内存最好在256M以上硬盘空间需要2G以上2.oracle安装会自动的生成sys和system两个用户说明：○1Sys用户是超级用户，具有最高权限，具有sysdba角色，create database 的权限，默认密码是manager○2System 用户是管理操作员，权限也很大，具有sysoper角色，没有create database权限，默认密码是 change_on_install○3一般讲，对数据库维护，使用system用户登录就可以了3.启动oracle右键单击我的电脑->服务和应用程序：服务->启动OracleServiceMYORA1(MYORA1是安装oracle时起的名字各有不同)和OracleOracleHome90TNSLlistener4.卸载oracle1、先关掉oralce，net stop OracleServiceORCL(ORCL是我的实例名字，换成你的)，或者去我的电脑服务中关闭2、开始－＞程序－＞Oracle - oracle的版本号，我的是10ghome－＞Oracle Installation Products－＞Universal Installer 卸载oracle3、进注册表，regedit，删除选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下所有的key。
racle学习笔记总计
培训第二天（上午）学习视频《韩顺平oracle视频》学习中遇到的想法：1.sql server 数据——》oracle（编程是实现java，先读在插入）一，oracle的安装1.oracle数据库自带用户 sys（默认密码：manager）（最好权限，可以create database），system（默认密码：change_on_install）。
二，oracle的启动1.启动一个oracle实例，即启动一个单个数据库。
2.管理——服务 oracleservice实例名（一个实例代表一个数据库）+oracle……listener三，oracle的卸载1.卸载需要动注册表（可观看视频）四，sql plus常用命令1.显示用户show user2.链接命令conn 用户名/密码disc3.修改密码passw4.运行一个脚本start和@ 如：start d：\aa.sql || @ d：\aa.sql5.编辑一个脚本edit D：\aa.sql6.spool 该命令可以将sqlplus屏幕上的内容输出到指定的文件中1. spool d:\b.sql2. select * from3. spool off7.交互 &8 set linesize, set pagesize(分页显示)9.desc 表名（查看表结构）五，oracle常用工具1.sqlplus，sqlplusw，pl/sql developer（第三方独立开发），oracle enterprise manager（图像化界面）六，用户管理1.create user 名 identified by 密（密码不能以数字开头）（新创建的用户是没有任何权限的，甚至是没有登录权限 grant， revoke）2.password 名3.drop user 名（如果用户有表，则加参数cascade）培训第二天（下午）(自己动手遇到无法登陆oracle)（gaotuan：gaotuan、system：G6*******cxz）一，用户授权（对象权限（select insert update delete all create index），系统权限）1.grant connect to 名（登录）2.grant resource to 名（创建数据库对象）3.sqlplus "/as sysdba"(sys登陆)4.alter user scott account unlock;(解锁)二，角色connect ，dba对象权限：with grant option系统权限：with admin option收回父权限时下边的用户也没有相应得权限三，表空间一，表空间作用： 1.控制数据库占用的磁盘空间。
Oracle触发器原理、创建、修改、删除
Oracle触发器原理、创建、修改、删除本篇主要内容如下：8.1 触发器类型8.1.1 DML触发器8.1.2 替代触发器8.1.3 系统触发器8.2 创建触发器8.2.1 触发器触发次序8.2.2 创建DML触发器8.2.3 创建替代(INSTEAD OF)触发器8.2.3 创建系统事件触发器8.2.4 系统触发器事件属性8.2.5 使⽤触发器谓词8.2.6 重新编译触发器8.3 删除和使⽤触发器8.4 触发器和数据字典8.5 触发器的应⽤举例8.6 触发器的查看8.7 触发器注意点触发器是许多关系数据库系统都提供的⼀项技术。
在ORACLE系统⾥，触发器类似过程和函数，都有声明，执⾏和异常处理过程的PL/SQL 块。
8.1 触发器类型触发器在数据库⾥以独⽴的对象存储，它与存储过程和函数不同的是，存储过程与函数需要⽤户显⽰调⽤才执⾏，⽽触发器是由⼀个事件来启动运⾏。
即触发器是当某个事件发⽣时⾃动地隐式运⾏。
并且，触发器不能接收参数。
所以运⾏触发器就叫触发或点⽕（firing）。
ORACLE事件指的是对数据库的表进⾏的INSERT、UPDATE及DELETE操作或对视图进⾏类似的操作。
ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。
所以触发器常⽤来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或⽤来监视对数据库的各种操作，实现审计的功能。
8.1.1 DML触发器ORACLE可以在DML语句进⾏触发，可以在DML操作前或操作后进⾏触发，并且可以对每个⾏或语句操作上进⾏触发。
8.1.2 替代触发器由于在ORACLE⾥，不能直接对由两个以上的表建⽴的视图进⾏操作。
所以给出了替代触发器。
它就是ORACLE 8专门为进⾏视图操作的⼀种处理⽅法。
8.1.3 系统触发器ORACLE 8i 提供了第三种类型的触发器叫系统触发器。
它可以在ORACLE数据库系统的事件中进⾏触发，如ORACLE系统的启动与关闭等。
韩顺平玩转ORACLE
目录1.Oracle 认证，与其它数据库比较，安装 (4)2.Oracle 的基本使用--基本命令 (7)2.1 连接命令 (7)2.1.1 conn[ect] (7)2.1.2 disc[onnect] (7)2.1.3 psssw[ord] (7)2.1.4 show user (7)2.1.5 exit (7)2.2文件操作命令 (7)2.2.1 start 和@ (7)2.2.2 edit (7)2.2.3 spool (8)2.3交互式命令 (8)2.3.1 & (8)2.3.2 edit (8)2.3.3 spool (8)2.4显示和设置环境变量 (9)2.4.1 linesize (9)2.4.2 pagesize (9)3.oracle 用户管理 (10)3.1 oracle 用户的管理 (10)3.1.1创建用户 (10)3.1.2 给用户修改密码 (10)3.1.3 删除用户 (10)3.1.4用户管理的综合案例 (10)3.2 使用profile 管理用户口令 (12)3.2.1.账户锁定 (12)3.2.2.给账户(用户)解锁 (13)3.2.3.终止口令 (13)3.3口令历史 (13)3.4 删除profile (13)4.oracle 表的管理(数据类型，表创建删除，数据CRUD 操作) (14)4.1 oracle 的表的管理 (14)4.1.1表名和列的命名规则 (14)4.1.2 oracle 支持的数据类型 (14)4.2怎样创建表 (15)4.3添加数据 (15)4.4修改数据 (16)4.5删除数据 (16)5.oracle 表查询 (16)5.1 oracle 表基本查询 (16)5.2简单的查询语句 (17)5.3 oracle 表复杂查询 (18)5.3多表查询 (19)5.4子查询 (20)5.5 分页查询 (21)5.5.1.根据rowid 来分 (22)5.5.2.按分析函数来分 (22)5.5.3按rownum 来分 (22)5.6用查询结果创建新表 (23)5.7合并查询 (23)6 创建数据库 (23)7. java 操作oracle (24)7.1 java 连接oracle (24)7.2在oracle 中操作数据 (28)8.oracle 中事务处理 (29)8.1什么是事务 (29)8.2提交事务 (29)8.3回退事务 (29)8.4 java 程序中如何使用事务 (29)9.oracle 的函数 (32)9.1 sql 函数的使用 (32)9.1.1字符函数 (32)9.1.2数学函数 (33)9.1.3日期函数 (34)9.1.4转换函数 (35)9.1.5系统函数 (36)10.数据库管理，表的逻辑备份与恢复 (37)10.1数据库管理员 (37)10.2数据库（表）的逻辑备份与恢复 (39)10.3导出 (39)10.3.1导出表 (39)10.3.2导出方案 (39)10.3.3导出数据库 (40)10.4导入 (40)10.4.1导入表 (40)10.4.2导入方案 (40)10.4.3导入数据库 (41)11.数据字典和动态性能视图 (41)11.1数据字典 (41)11.2 用户名，权限，角色 (41)11.3动态性能视图 (44)12.数据库管理-- 管理表空间和数据文件 (45)12.1数据库的逻辑结构 (45)12.2表空间 (45)12.2.1建立表空间 (45)12.2.2 改变表空间的状态 (46)12.2.3删除表空间 (46)12.2.4扩展表空间 (46)12.2.5移动数据文件 (47)12.2.6 显示表空间信息 (47)12.2.7表空间小结 (47)12.2.8其它表空间 (48)13.约束 (48)13.1维护数据的完整性 (48)13.2约束 (48)13.2.1使用 (48)13.2.2维护 (49)13.2.3删除约束 (50)13.2.4显示约束信息 (50)14.Oracle 索引、权限 (51)14.1创建索引 (51)14.2 显示索引信息 (52)14.3 管理权限和角色 (52)14.3.1 系统权限 (52)14.3.2 对象权限 (53)15.角色 (55)15.1预定义角色 (56)15.2自定义角色 (56)15.3角色授权 (56)15.4删除角色 (57)15.5显示角色信息 (58)16.PL/SQL 块的结构和实例 (59)17.pl/sql 分类-- 过程，函数，包，触发器 (64)17.1 过程 (64)17.2 函数 (65)17.3包 (66)17.3触发器 (67)18.定义并使用变量,复合类型 (68)18.1定义并使用变量 (68)18.2复合变量(composite) (69)19.pl/sql 的进阶--控制结构(分支，循环，控制) (71)19.1 pl/sql 的进阶--控制结构 (72)19.1.1条件分支语句 (72)20.PL/SQL 分页 (75)21.例外处理 (85)21.1处理预定义例外 (86)21.2非预定义例外 (89)22.oracle 的视图 (92)1.Oracle 认证，与其它数据库比较，安装Oracle 安装会自动的生成sys 用户和system 用户:（1）sys 用户是超级用户，具有最高权限，具有sysdba 角色，有create database的权限，该用户默认的密码是change_on_install（2）system 用户是管理操作员，权限也很大。
学习笔记之韩顺平老师的MySQL优化教程
学习笔记之韩顺平⽼师的MySQL优化教程⼀、表的设计什么样的表才是符合3NF范式？表的范式，⾸先是符合1NF，才能满⾜2NF，进⼀步才能满⾜3NF范式。
1NF范式：即表的列具有原⼦性，不可再分解，即列的信息，不能分解，只有数据库是关系型数据库，就⾃动满⾜1NF2NF范式：表中的记录是唯⼀的，就满⾜2NF范式，通常我们设计⼀个主键来实现。
3NF范式：是对字段冗余性约束，要求字段没有冗余，没有冗余的数据库设计可以做到。
但是，没有冗余的数据库未必是最好的数据库，有时为了提⾼运⾏效率，就必须降低范式标准，适当保留数据冗余（即反三范式）。
具体做法是：再概念数据模型设计市遵循第三范式，降低范式标准的⼯作放到物理数据模型设计时考虑。
降低范式就是增加字段，允许冗余。
⽐如——在表的⼀对N情况下，为了提供效率，可能会在1 这表中设计字段，提⾼速率。
⼆、慢查询SQL优化，SQL语句本⾝的优化。
SQL优化的⼀般步骤：1. 通过show status命令了解各种SQL的执⾏频率2. 定位执⾏效率较低的SQL语句（重点select）3. 通过explain分析低效率的SQL语句的执⾏情况4. 确定问题并采取相应的优化措施问题是：如何从⼀个⼤项⽬中，迅速定位执⾏慢的语句（定位慢查询）？①⾸先我们了解MySQL数据库的⼀些运⾏状态如何查询（⽐如想知道当前MySQL运⾏的时间/⼀共执⾏了多少次select，update，delete/当前连接）show status常⽤的：show status like "uptime";show status like "conn_select"; show status like "conn_insert"; ...类推 update,deleteshow [session|global] status like ...... 如果你不写 [session|global]默认是session会话，只取出当前窗⼝的执⾏，如果你想看所有（从mysql 启动到现在，则应该global）show status like "connections";show status like "show_queries"; 显⽰慢查询②如何去定位慢查询构建⼀个⼤表（400万）——>存储过程构建。
Oracle数据库技术与应用课件：触发器
触发器简介
• 触发器是一种过程，与表关系密切，用于保护表中的数据 • 当一个基表被修改（INSERT、UPDATE或DELETE）时，触发器自
动执行 • 触发器可实现多个表之间数据的一致性和完整性
触发器语法
• 创建触发器的具体语法：
CREATE [OR REPLACE] TRIGGER [schema.]trigger_name {BEFORE | AFTER | INSTEAD OF} /*定义触发器种类*/ {DELETE [OR INSERT] [OR UPDATE[OF column,…n]]} ON [schema.]table_name | view_name /*指定操作对象*/ [FOR EACH ROW [WHEN(condition)]] trigger_body
ALTER TRIGGER log_creations DISABLE ; • 重新启用log_creations触发器
ALTER TRIGGER log_creations ENABLE ;
触发器的修改与删除
• 删除触发器log_creations DROP TRIGGER log_creations ;
器 • DML触发器针对INSERT、UPDATE、DELETE操作，可以在这些操作之
前或之后触发。DML触发器又包括行级触发器和语句级触发器。 • INSTEAD OF触发器主要用于建立在多张基表上的视图。
:new.sal := :old.sal; /*新的工资赋予原来的值,即工资不变*/ end;
INSTEAD OF触发器
• INSTEAD OF触发器用于对视图的DML触发,主要用于多表联接的视图 • 例：在dept和emp表上建有如下视图
韩顺平老师oracle视频教程听课笔记
韩顺平老师 oracle教程笔记1.Oracle认证，与其它数据库比较，安装Oracle安装会自动的生成sys用户和system用户:（1）sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install （2）system用户是管理操作员，权限也很大。
具有sysoper角色，没有create database的权限，默认的密码是manager （3）一般讲，对数据库维护，使用system用户登录就可以拉也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。
2.Oracle的基本使用--基本命令sql*plus的常用命令连接命令1.conn[ect]用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper 2.disc[onnect]说明: 该命令用来断开与当前数据库的连接3.psssw[ord]说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
4.show user说明: 显示当前用户名5.exit说明: 该命令会断开与数据库的连接，同时会退出sql*plus文件操作命令1.start和@说明: 运行sql脚本案例: sql>@ d:\a.sql或是sql>start d:\a.sql2.edit说明: 该命令可以编辑指定的sql脚本案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开3.spool说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d:\b.sql 并输入 sql>spool off交互式命令1.&说明：可以替代变量，而该变量在执行时，需要用户输入。
韩顺平老师oracle教程听课笔记_整理
韩顺平老师 oracle教程笔记1.Oracle认证，与其它数据库比较，安装Oracle安装会自动的生成sys用户和system用户:（1）sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install （2）system用户是管理操作员，权限也很大。
具有sysoper角色，没有create database的权限，默认的密码是manager （3）一般讲，对数据库维护，使用system用户登录就可以拉也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。
2.Oracle的基本使用--基本命令sql*plus的常用命令连接命令1.conn[ect]用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper 2.disc[onnect]说明: 该命令用来断开与当前数据库的连接3.psssw[ord]说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
4.show user说明: 显示当前用户名5.exit说明: 该命令会断开与数据库的连接，同时会退出sql*plus文件操作命令1.start和@说明: 运行sql脚本案例: sql>@ d:\a.sql或是sql>start d:\a.sql2.edit说明: 该命令可以编辑指定的sql脚本案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开3.spool说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d:\b.sql 并输入 sql>spool off交互式命令1.&说明：可以替代变量，而该变量在执行时，需要用户输入。
韩顺平 玩转oracle 10g 实战教程第3讲
玩转oracle 10g实战教程 玩转oracle 10g实战教程
主讲 韩顺平
主讲： 主讲：韩顺平 email： email：hanshunping@
玩转oracle 10g实战教程
主讲 韩顺平
oracle第3讲 第 讲
1. java程序如何操作oracle 2. 如何在oracle中操作数据 3. oracle事务处理 4. sql函数的使用
玩转oracle 10g实战教程
主讲 韩顺平
oracle中事务处理 –事务隔离级别 中事务处理 事务隔离级别
■
oracle的 read only 说明： ①遵从事务级的读一致性，仅仅能看见在本事务开始前由其它事务提 交的更改。 ②不允许在本事务中进行DML操作。 ③read only是serializable的子集。它们都避免了不可重复读和幻读。 区别是在read only中是只读；而在serializable中可以进行DML操作
■
oracle的事务隔离级在 的事务隔离级在java程序中如何使用 的事务隔离级在 程序中如何使用
connection.setTransactionIsolation(Connection.TRANSACTION_REA D_COMMITTED); 特别说明: 特别说明 ①java程序中Connection.XXXXXX有五个，但不是所有的数据库对有 对应的五个事务隔离级别实现。 ②在实际工作中，我们极少去修改各个数据库默认的隔离级别。
oracle触发器写法oracletrigger语法
oracle触发器写法oracletrigger语法
create or replace trigger t_after_table --create or replace trigger 触发器名称
after insert ---时间 after/before 事件 insert or update or delete
on student ---作⽤的表 on tablename
FOR EACH ROW -- 指定是否对受影响的每⾏都执⾏触发器，即⾏级触发器，如果不使⽤此⼦句，则为语句级触发器declare --trigger 的主题
begin
insert into student_state(SSID,Ssstate) values(:NEW.SID,:NEW.SID);
end;
注释：
before和after：指在事件发⽣之前或之后激活触发器。
instead of：如果使⽤此⼦句，表⽰可以执⾏触发器代码来代替导致触发器调⽤的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
referencing：指定新⾏（即将更新）和旧⾏（更新前）的其他名称，默认为new和old。
table_or_view_name：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每⾏都执⾏触发器，即⾏级触发器，如果不使⽤此⼦句，则为语句级触发器。
when：限制执⾏触发器的条件，该条件可以包括新旧数据值得检查。
declare---end：是⼀个标准的PL/SQL块。
Oracle触发器基础教程
Oracle触发器基础教程Oracle触发器是一种特殊的数据库对象，它是一段由PL/SQL或Java编写的代码，当指定的条件满足时，会自动地在数据库中执行。
Oracle触发器可以在插入、更新或删除数据时触发一系列的动作，从而实现对数据的自动控制和处理。
一、创建触发器在Oracle中，使用CREATE TRIGGER语句可以创建触发器。
语法如下：CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE，AFTER，INSTEADOF}{INSERT，UPDATE，DELETE}[OR {INSERT ， UPDATE ， DELETE} OF column_name][ON table_name][REFERENCING OLD AS old NEW AS new][FOREACH{ROW，STATEMENT}][WHEN (condition)][DECLARE]--声明变量和游标BEGIN--触发器代码--新增、修改或删除数据的动作END;trigger_name：触发器的名称，必须唯一BEFORE、AFTER、INSTEADOF：触发器执行的时间，BEFORE表示在数据操作之前执行，AFTER表示在数据操作之后执行。
INSERT、UPDATE、DELETE：触发器执行的操作类型，可以是插入、更新或删除。
column_name：触发器监听的列名。
table_name：触发器所在的表名。
REFERENCING：指定触发器中使用的旧值和新值的别名，可以在触发器代码中使用。
FOREACH：指定触发器是对每一行执行还是每条语句执行。
WHEN：指定触发器执行的条件。
二、触发器的类型1.行级触发器（FOREACHROW）行级触发器是对每一行进行操作的触发器，它会在每一行插入、更新或删除之后触发。
这种触发器常用于数据的验证和约束条件的实现。
2.语句级触发器（FOREACHSTATEMENT）语句级触发器是对每条语句进行操作的触发器，它会在数据操作语句执行完成后触发。
韩顺平 oracle视频教程上课笔记
一、oracle 常用sql plus 命令(1)conn[ect] 用户名/密码@网络[as sysdba /sysoper](2)一般情况下使用普通用户登录，除非需要更高权限时，在切换高级用户；(3)disc[onnect] 断开连接，又不退出当前窗口；(4)quit/exit 完全退出；(5)show user 显示当前用户；(6)管理员修改用户密码：alter user xxxx identified by yyyy;(7)Passw[ord]pass 用户名；给自己修改密码，不需要带用户名;给别的用户修改，需要带用户名；创建临时表空间create temporary tablespaceuser_temptempfile 'd:\user_temp.dbf'size 50mautoextend onnext 50m maxsize 1024mextent management local;创建表空间createtablespaceuser_tsdatafile 'd:\user_ts.dbf'size 50mautoextend onnext 50m maxsize 1024mextent management local;创建用户create user test38 identified by test38default tablespaceuser_tstemporary tablespaceuser_tempquota 5m on user_ts;给用户赋予权力grantdba to test38;给scott用户解锁alter user scott account unlock;用system 用户给scott修改密码Alter user scott identified by tiger;不常用的命令：linesize一行能显示多少个字符就换行了默认就只有80字符，所以会出现一个记录一行没有显示完全就换行了。
oracle触发器
1、触发器的概述如果希望该子程序能够自动执行，就需要将该程序定义为触发器，触发器可以看做是一种特殊的存储过程，它可以在数据库相关事件（如DELETE、UPDATE，INSERT，CREATe等）发生时自动执行，常用于管理复杂的完整性约束或监控对表的修改操作。
触发器执行的机制与Java中的事件监听机制类似，当出现特定的事件时就会自动调用，Oracle数据库中的事件包括增加数据、修改数据、删除数据等。
与Java中的事件处理机制类似，在创建触发器时也需要指定触发器执行的事件2、触发器的语法（1）TRIGGER：用于创建触发器的关键字，就类似于创建存储过程的procedure和创建自定义函数的function一样。
（2）trigger_name：指定触发器的名称。
（3）BEFORE | AFTER | INSTEAD OF：指定触发器的时间，BEFORE表示在触发器在事件发生之前被执行，AFTER表示触发器在事件发生之后执行，INSTEAD OF指定该触发器为代替触发器。
（4）trigger_event：指定触发器的触发事件，常用的事件有INSERT、UPDATE、DELETE、CREATE、DROP等，多个事件之间需要使用or关键字进行连接。
（5）ON obj_name指定发生事件的数据库对象名称，如表名称、视图名称等。
如果执行的是DDL操作就必须写为ON DATABASE。
（6）FOR EACH ROW：表示该触发器为行级触发器，如果不指定该语句就默认为语句级触发器。
（7）WHEN tri_condition：指定触发器执行的条件，例如使用update语句修改的数据满足某个条件时才执行触发器的内容。
3、触发器的分类Oracle数据库的触发器事件相对于其他数据库而言相对复杂，根据触发器触发事件和触发器执行情况可以将Oracle中的触发器分为5种类型，具体如下所示。
（1）行级触发器：对表执行DML操作时，每影响一行数据，该类型的触发器就会执行一次。
oracle11g教程从入门到精通
韩顺平—玩转oracle视频教程笔记之欧侯瑞魂创作创作时间：二零二一年六月三十日一：Oracle认证, 与其它数据库比力, 装置Oracle装置会自动的生成sys用户和system用户:（1）sys用户是超级用户, 具有最高权限, 具有sysdba角色,有create database的权限, 该用户默认的密码是change_on_install（2）system用户是管理把持员, 权限也很年夜.具有sysoper角色, 没有create database的权限, 默认的密码是manager （3）一般讲, 对数据库维护, 使用system用户登录就可以拉也就是说sys和system这两个用户最年夜的区别是在于有没有create database的权限.二： Oracle的基本使用--基本命令sql*plus的经常使用命令连接命令1.conn[ect]用法：conn 用户名/密码@网络服务名[assysdba/sysoper]当用特权用户身份连接时, 必需带上as sysdba或是as sysoper 2.disc[onnect]说明: 该命令用来断开与以后数据库的连接3.psssw[ord]说明: 该命令用于修改用户的密码, 如果要想修改其它用户的密码, 需要用sys/system登录. 4.show user说明: 显示以后用户名说明: 该命令会断开与数据库的连接, 同时会退出sql*plus 文件把持命令1.start和@说明: 运行sql脚本案例: sql>@ d:\a.sql或是sql>start d:\a.sql ,这样会把d:\a.sql这个文件翻开说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去. 案例: sql>spool d:\b.sql 并输入 sql>spool off 交互式命令1.&说明：可以替代变量, 而该变量在执行时, 需要用户输入.select * from emp where job='&job'；说明：该命令可以编纂指定的sql脚本案例：SQL>edit d:\a.sql说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去. spool d:\b.sql 并输入 spool off 显示和设置环境变量概述：可以用来控制输出的各种格式, set show如果希望永久的保管相关的设置, 可以去修改glogin.sql脚本说明：设置显示行的宽度, 默认是80个字符show linesizeset linesize 90说明：设置每页显示的行数目, 默认是14用法和linesize一样至于其它环境参数的使用也是年夜同小异三：oracle用户管理oracle用户的管理创立用户概述：在oracle中要创立一个新的用户使用create user语句, 一般是具有dba(数据库管理员)的权限才华使用. create user 用户名 identified by 密码; (oracle有个毛病, 密码必需以字母开头, 如果以字母开头, 它不会创立用户) 给用户修改密码概述：如果给自己修改密码可以直接使用 password 用户名如果给他人修改密码则需要具有dba 的权限, 或是拥有alter user的系统权限 SQL> alter user 用户名 identified by 新密码删除用户概述：一般以dba的身份去删除某个用户, 如果用其它用户去删除用户则需要具有drop user的权限. 比如 drop user 用户名【cascade】在删除用户时, 注意：如果要删除的用户, 已经创立了表, 那么就需要在删除的时候带一个参数cascade; 用户管理的综合案例概述：创立的新用户是没有任何权限的, 甚至连登岸的数据库的权限都没有, 需要为其指定相应的权限.给一个用户赋权限使用命令grant, 回收权限使用命令revoke. 为了给讲清楚用户的管理, 这里我给年夜家举一个案例. SQL> conn xiaoming/m12; ERROR: ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied 警告: 您不再连接到 ORACLE. SQL> show user; USER 为"" SQL> conn system/p; 已连接. SQL> grant connect to xiaoming; 授权胜利. SQL> conn xiaoming/m12; //后面的为密码分开来输入.已连接. SQL> 注意：grant connect to xiaoming;在这里, 准确的讲, connect不是权限, 而是角色.. 看图：现在说下对象权限, 现在要做这么件事情： * 希望xiaoming用户可以去查询emp表 * 希望xiaoming用户可以去查询scott的emp表 grant select on emp to xiaoming * 希望xiaoming 用户可以去修改scott的emp表 grant update on emp to xiaoming * 希望xiaoming用户可以去修改/删除, 查询, 添加scott的emp表 grant all on emp to xiaoming * scott希望收回xiaoming对emp表的查询权限 revoke select on emp from xiaoming //对权限的维护. * 希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限继续给他人. --如果是对象权限, 就加入 with grant option grant select on emp to xiaoming with grant option 我的把持过程： SQL> conn scott/tiger; 已连接. SQL> grant select on scott.emp to xiaoming with grant option; 授权胜利. SQL> conn system/p; 已连接. SQL> create user xiaohong identified by m123; 用户已创立. SQL> grant connect to xiaohong; 授权胜利. SQL> conn xiaoming/m12; 已连接. SQL> grant select on scott.emp to xiaohong; 授权胜利.--如果是系统权限. system给xiaoming权限时： grant connect to xiaoming with admin option问题：如果scott把xiaoming对emp表的查询权限回收, 那么xiaohong会怎样？谜底：被回收.下面是我的把持过程： SQL> conn scott/tiger; 已连接. SQL> revoke select on emp from xiaoming; 裁撤胜利. SQL> conn xiaohong/m123; 已连接. SQL> select * from scott.emp; select * from scott.emp 第 1 行呈现毛病: ORA-00942: 表或视图不存在结果显示：小红受到诛连了.使用profile管理用户口令概述：profile是口令限制, 资源限制的命令集合, 当建立数据库的, oracle会自动建立名称为default的profile.当建立用户没有指定profile选项, 那么oracle就会将default分配给用户. 概述：指定该账户(用户)登岸时最多可以输入密码的次数, 也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令. 例子：指定scott这个用户最多只能检验考试3次登岸, 锁按时间为2天, 让我们看看怎么实现. 创立profile文件 SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott profile lock_account; 2.给账户(用户)解锁SQL> alter user tea account unlock; 为了让用户按期修改密码可以使用终止口令的指令来完成, 同样这个命令也需要dba的身份来把持. 例子：给前面创立的用户tea创立一个profile文件, 要求该用户每隔10天要修改自己的登岸密码, 宽限期为2天.看看怎么做. SQL> create profile myprofile limitpassword_life_time 10 password_grace_time 2; SQL> alter user tea profile myprofile;口令历史概述：如果希望用户在修改密码时, 不能使用以前使用过的密码, 可使用口令历史, 这样oracle就会将口令修改的信息寄存到数据字典中, 这样当用户修改密码时, oracle就会对新旧密码进行比力, 当发现新旧密码一样时, 就提示用户重新输入密码. 例子： 1）建立profile SQL>create profilepassword_history limit password_life_time 10password_grace_time 2 password_reuse_time 10password_reuse_time //指定口令可重用时间即10天后就可以重用 2）分配给某个用户删除profile概述：当不需要某个profile文件时, 可以删除该文件. SQL> drop profile password_history 【casade】注意：文件删除后, 用这个文件去约束的那些用户通通也都被释放了.加了casade, 就会把级联的相关工具也给删除失落四：oracle表的管理(数据类型, 表创立删除, 数据CRUD把持) oracle的表的管理表名和列的命名规则•必需以字母开头•长度不能超越30个字符•不能使用oracle的保管字•只能使用如下字符 A-Z, a-z, 0-9, $,#等oracle支持的数据类型字符类char定长最年夜2000个字符. 例子：char(10) ‘小韩’前四个字符放‘小韩’, 后添6个空格补全如‘小韩’ varchar2(20)变长最年夜4000个字符. 例子：varchar2（10）‘小韩’ oracle分配四个字符.这样可以节省空间.clob(character large object) 字符型年夜对象最年夜4G char 查询的速度极快浪费空间, 查询比力多的数据用. varchar 节省空间数字型number范围 -10的38次方到 10的38次方可以暗示整数, 也可以暗示小数 number(5,2) 暗示一位小数有5位有效数, 2位小数范围：-999.99到999.99 number(5) 暗示一个5位整数范围99999到-99999日期类型 date 包括年月日和时分秒 oracle默认格式 1-1月-1999 timestamp 这是oracle9i对date数据类型的扩展.可以精确到毫秒. 图片blob 二进制数据可以寄存图片/声音 4G 一般来讲, 在真实项目中是不会把图片和声音真的往数据库里寄存, 一般寄存图片、视频的路径, 如果平安需要比力高的话, 则放入数据库. 怎样创立表建表--学生表 create table student ( ---表名 xh number(4), --学号 xm varchar2(20), --姓名 sex char(2), --性别 birthday date, --出身日期 sal number(7,2) --奖学金 );--班级表 CREATE TABLE class( classIdNUMBER(2), cName VARCHAR2(40));修改表添加一个字段SQL>ALTER TABLE student add (classId NUMBER(2)); 修改一个字段的长度 SQL>ALTER TABLE student MODIFY (xmVARCHAR2(30)); 修改字段的类型/或是名字（不能有数据）不建议做 SQL>ALTER TABLE student modify (xm CHAR(30)); 删除一个字段不建议做(删了之后, 顺序就变了.加就没问题, 应为是加在后面)SQL>ALTER TABLE student DROP COLUMN sal; 修改表的名字很少有这种需求 SQL>RENAME student TO stu; 删除表 SQL>DROP TABLE student; 添加数据所有字段都拔出数据INSERT INTO student VALUES ('A001','张三','男','01-5月-05', 10); oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份 yy 2位的年‘09-6月-99’ 1999年6月9日修改日期的默认格式（临时修改, 数据库重启后仍为默认；如要修改需要修改注册表） ALTER SESSION SETNLS_DATE_FORMAT ='yyyy-mm-dd'; 修改后, 可以用我们熟悉的格式添加日期类型： INSERT INTO student VALUES('A002','MIKE','男','1905-05-06', 10); 拔出部份字段INSERT INTO student(xh, xm, sex) VALUES ('A003','JOHN','女'); 拔出空值 INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004','MARTIN','男', null); 问题来了, 如果你要查询student内外birthday为null的记录, 怎么写sql呢？毛病写法：select * from student where birthday = null; 正确写法：select * from student where birthday is null; 如果要查询birthday不为null,则应该这样写： select * from student where birthday is not null; 修改数据修改一个字段UPDATE student SET sex = '女' WHERE xh = 'A001'; 修改多个字段UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001'; 修改含有null值的数据不要用 =null 而是用 is null； SELECT * FROM student WHERE birthday IS null;删除数据DELETE FROM student; 删除所有记录, 表结构还在,写日志, 可以恢复的, 速度慢. Delete 的数据可以恢复. savepoint a; --创立保管点 DELETE FROM student; rollback to a; --恢复到保管点一个有经验的DBA, 在确保完成无误的情况下要按期创立还原点. DROP TABLE student; --删除表的结构和数据； delete from student WHERE xh = 'A001'; --删除一条记录； truncate TABLE student; --删除表中的所有记录, 表结构还在, 不写日志, 无法找回删除的记录, 速度快.五：oracle表查询(1)oracle表基本查询介绍在我们讲解的过程中我们利用scott用户存在的几张表（emp, dept）为年夜家演示如何使用select语句, select语句在软件编程中非常有用, 希望年夜家好好的掌握. emp 雇员表clerk 普员工 salesman 销售 manager 经理 analyst 分析师 president 总裁 mgr 上级的编号 hiredate 入职时间sal 月工资 comm 奖金 deptno 部份 dept部份表 deptno 部份编号 accounting 财政部 research 研发部 operations 业务部 loc 部份所在地址 salgrade 工资级别 grade 级别 losal 最低工资 hisal 最高工资简单的查询语句检查表结构DESC emp; 查询所有列SELECT * FROM dept; 切忌动不动就用select * SET TIMING ON; 翻开显示把持时间的开关, 在下面显示查询时间. CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30)); INSERT INTO users VALUES('a0001','啊啊啊啊','aaaaaaaaaaaaaaaaaaaaaaa'); --从自己复制, 加年夜数据量年夜概几万行就可以了可以用来测试sql语句执行效率 INSERT INTO users(userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;统计行数查询指定列SELECT ename, sal, job, deptno FROM emp; 如何取消重复行DISTINCT SELECT DISTINCT deptno, job FROM emp; 查询SMITH所在部份, 工作, 薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH'; 注意：oracle对内容的年夜小写是区分的, 所以ename='SMITH'和ename='smith'是分歧的使用算术表达式nvl null 问题：如何显示每个雇员的年工资？ SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; 使用列的别名SELECT ename "姓名", sal*12 AS "年收入" FROM emp; 如何处置null值使用nvl函数来处置如何连接字符串（||）SELECT ename || ' is a ' || job FROM emp; 使用where子句问题：如何显示工资高于3000的员工？SELECT * FROM emp WHERE sal > 3000; 问题：如何查找1982.1.1后入职的员工？SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 问题：如何显示工资在2000到3000的员工？ SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000; 如何使用like把持符 %：暗示0到多个字符 _：暗示任意单个字符问题：如何显示首字符为S的员工姓名和工资？ SELECT ename,sal FROM emp WHERE ename like 'S%'; 如何显示第三个字符为年夜写O的所有员工的姓名和工资？ SELECT ename,sal FROM emp WHERE ename like '__O%'; 在where条件中使用in 问题：如何显示empno为7844, 7839,123,456 的雇员情况？SELECT * FROM emp WHERE empno in (7844, 7839,123,456); 使用is null的把持符问题：如何显示没有上级的雇员的情况？毛病写法：select * from emp where mgr = ''; 正确写法：SELECT * FROM emp WHERE mgr is null;六：oracle表查询(2)使用逻辑把持符号问题：查询工资高于500或者是岗位为MANAGER的雇员, 同时还要满足他们的姓名首字母为年夜写的J？SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; 使用order by 字句默认asc 问题：如何依照工资的从低到高的顺序显示雇员的信息？ SELECT * FROM emp ORDER by sal; 问题：依照部份号升序而雇员的工资降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; 使用列的别名排序问题：按年薪排序 select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; 别名需要使用“”号圈中,英文不需要“”号分页查询等学了子查询再说吧........ Clear 清屏命令 oracle表复杂查询说明在实际应用中经常需要执行复杂的数据统计, 经常需要显示多张表的数据, 现在我们给年夜家介绍较为复杂的select语句数据分组——max, min, avg, sum, count 问题：如何显示所有员工中最高工资和最低工资？ SELECT MAX(sal),min(sal) FROM emp e; 最高工资那个人是谁？毛病写法：select ename, sal from emp where sal=max(sal); 正确写法：select ename, sal from emp where sal=(select max(sal)from emp); 注意：select ename, max(sal) from emp;这语句执行的时候会报错, 说ORA-00937：非单组分组函数.因为max是分组函数, 而ename不是分组函数....... 可是select min(sal), max(sal) from emp;这句是可以执行的.因为min和max都是分组函数, 就是说：如果列里面有一个分组函数, 其它的都必需是分组函数, 否则就犯错.这是语法规定的问题：如何显示所有员工的平均工资和工资总和？问题：如何计算总共有几多员工问题：如何扩展要求：查询最高工资员工的名字, 工作岗位 SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 显示工资高于平均工资的员工信息 SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); group by 和having子句 group by用于对查询的结果分组统计, having子句用于限制分组显示结果. 问题：如何显示每个部份的平均工资和最高工资？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; （注意：这里隐藏了一点, 如果你要分组查询的话, 分组的字段deptno一定要呈现在查询的列内外面, 否则会报错.因为分组的字段都不呈现的话, 就没法子分组了）问题：显示每个部份的每种岗位的平均工资和最低工资？ SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 问题：显示平均工资低于2000的部份号和它的平均工资？SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP bydeptno having AVG(sal) < 2000; 对数据分组的总结 1 分组函数只能呈现在选择列表、having、order by子句中(不能呈现在where中) 2 如果在select语句中同时包括有group by, having, order by 那么它们的顺序是group by, having, order by 3 在选择列中如果有列、表达式和分组函数, 那么这些列和表达式必需有一个呈现在group by 子句中, 否则就会犯错. 如SELECT deptno, AVG(sal),,, d.dname FROM emp e,,, e.sal FROM emp e,,, s.grade FROM emp e,,, d.dname FROM emp e,,boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'; 子查询什么是子查询子查询是指嵌入在其他sql语句中的select语句, 也叫嵌套查询. 单行子查询单行子查询是指只返回一行数据的子查询语句请思考：显示与SMITH同部份的所有员工？思路：1 查询出SMITH的部份号 select deptno from emp WHERE ename = 'SMITH'; 2 显示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 数据库在执行sql 是从左到右扫描的, 如果有括号的话, 括号里面的先被优先执行. 多行子查询多行子查询指返回多行数据的子查询请思考：如何查询和部份10的工作相同的雇员的名字、岗位、工资、部份号 SELECT DISTINCT job FROM emp WHERE deptno = 10; SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); （注意：不能用job=.., 因为等号=是一对一的）在多行子查询中使用all把持符问题：如何显示工资比部份30的所有员工的工资高的员工的姓名、工资和部份号？SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 扩展要求：年夜家想想还有没有另外查询方法. SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 执行效率上, 函数高很多在多行子查询中使用any把持符问题：如何显示工资比部份30的任意一个员工的工资高的员工姓名、工资和部份号？ SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHEREdeptno = 30); 扩展要求：年夜家想想还有没有另外查询方法. SELECT ename, sal, deptno FROM emp WHERE sal > (SELECTmin(sal) FROM emp WHERE deptno = 30); 多列子查询单行子查询是指子查询只返回单列、单行数据, 多行子查询是指返回单列多行数据, 都是针对单列而言的, 而多列子查询是指查询返回多个列数据的子查询语句. 请思考如何查询与SMITH的部份和岗位完全相同的所有雇员. SELECT deptno, job FROM emp WHERE ename = 'SMITH'; SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename ='SMITH'); 在from子句中使用子查询请思考：如何显示高于自己部份平均工资的员工的信息思路： 1. 查出各个部份的平均工资和部份号 SELECT deptno,,,, ds.mysal FROM emp e, (SELECT deptno,,,, ds.mysal FROM emp e,(SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 在ds前不能加as, 否则会报错（给表取别名的时候, 不能加as；可是给列取别名, 是可以加as的）分页查询按雇员的id号升序取出 oracle的分页一共有三种方式 1.根据rowid来分 select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 执行时间0.03秒 2.按分析函数来分 select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 执行时间1.01秒 3.按rownum来分 select * from (select t.*,rownum rnfrom(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 执行时间0.1秒其中t_xiaoxi为表名称, cid为表的关键字段, 取按cid降序排序后的第9981-9999条记录, t_xiaoxi表有70000多条记录. 个人感觉1的效率最好, 3次之, 2最差. //测试通过的分页查询okokok select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5; 下面最主要介绍第三种：按rownum来分 1. rownum 分页 SELECT * FROMemp; 2. 显示rownum[oracle分配的] SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; rn相当于Oracle分配的行的ID号 3.挑选出6—10条记录先查出1-10条记录SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 如果后面加上rownum>=6是不成的, 4. 然后查出6-10条记录 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 5. 几个查询变动 a. 指定查询列, 只需要修改最里层的子查询只查询雇员的编号和工资 SELECT * FROM (SELECT e.*, ROWNUMrn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查询, 只需要修改最里层的子查询工资排序后查询6-10条数据 SELECT * FROM (SELECT e.*, ROWNUMrn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHEREROWNUM <= 10) WHERE rn >= 6; 用查询结果创立新表这个命令是一种快捷的建表方式 CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 创立好之后, desc mytable；和select * from mytable;看看结果如何？合并查询合并查询有时在实际应用中, 为了合并多个select语句的结果, 可以使用集合把持符号union, union all, intersect, minus 多用于数据量比力年夜的数据局库, 运行速度快. 1). union 该把持符用于取得两个结果集的并集.当使用该把持符时, 会自动去失落结果集中重复行. SELECT ename, sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 2).union all 该把持符与union相似, 可是它不会取消重复行, 而且不会排序. SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job ='MANAGER'; 该把持符用于取得两个结果集的并集.当使用该把持符时, 会自动去失落结果集中重复行. 3). intersect 使用该把持符用于取得两个结果集的交集. SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus 使用改把持符用于取得两个结果集的差集, 他只会显示存在第一个集合中, 而不存在第二个集合中的数据. SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; （MINUS就是减法的意思）创立数据库有两种方法： 1). 通过oracle提供的向导工具.√ database Configuration Assistant 【数据库配置助手】 2).我们可以用手工步伐直接创立.七：java把持oraclejava连接oracle 介绍：前面我们一直在plsql中把持oracle, 那么如何在java 法式中把持数据库呢？下面我们举例说明, 写一个java, 分页显示emp表的用户信息.Java代码1.package com.sp;2.import java.sql.Connection;3.import java.sql.DriverManager;4.import java.sql.ResultSet;5.import java.sql.Statement;6.//演示如何使用jdbc_odbc桥连接方式7.public class TestOracle {8.public static void main(String[] args){9.try {10.// 1.加载驱动11.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");12.// 2.获得连接13.Connection ct = DriverManager.getConnection(14."jdbc.odbc:testConnectOracle", "scott",15."tiger");16.// 从下面开始, 和SQL Server一模一样17.Statement sm = ct.createStatement();18.ResultSet rs = sm.executeQuery("select * from emp");19.while (rs.next()) {20.//用户名21.System.out.println("用户名："+rs.getString(2));22.//默认是从1开始编号的23.}24.} catch (Exception e) {25. e.printStackTrace();26.}27.}28.}在获得连接那里, 要去配置数据源, 点击控制面板-->系统和平安-->管理工具-->数据源(ODBC),翻开后点添加,如图：可以看到, 有个Oracle in OraDb10g_home1的驱动, 它是Oracle装置完后自动加上去的. 选中后, 点完成, 再填如下信息,如图：这样配好后基本就可以了, 但为了平安起见, 建议年夜家测试一下, 点击 Test Connection按钮, 测试通过后点ok,然后数据源就生成了, 如图：然后把数据源名称写进jdbc.odbc:里.这里要注意：jdbcodbc能不能远程连接呢？不能远程连接, 也就是你这样写的话就意味着java法式和oracle数据库应该是在同一台机器上, 因为这里没有指定IP地址, 肯定默认就是本地.如果要远程连, 就用jdbc,jdbc是可以远程连的.,控制台输出.......................可惜我没运行胜利, 说java.sql.SQLException: No suitable driver found forjdbc.odbc:testConnectOracle atjava.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle.main(TestOracle.java:18) 不知道为什么... 接下来讲解用JDBC的方式连接OracleJava代码1.package com.sp;2.import java.sql.Connection;3.import java.sql.DriverManager;4.import java.sql.ResultSet;5.import java.sql.Statement;6.//使用jdbc连接oracle7.public class TestOracle2 {8.public static void main(String[] args){9.try {10.// 1.加载驱动11.Class.forName("oracle.jdbc.driver.OracleDriver");12.// 2.获得连接13.Connection ct = DriverManager.getConnection14.("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott","tiger");15.// 从下面开始, 和SQL Server一模一样16.Statement sm = ct.createStatement();17.ResultSet rs = sm.executeQuery("select * from emp");18.while (rs.next()) {19.//用户名20.System.out.println("用户名："+rs.getString(2));21.//默认是从1开始编号的22.}23.} catch (Exception e) {24. e.printStackTrace();25.}26.}27.}记得要把驱动包引入, classes12.jar 运行, .... 再次可惜, 我还是没运行胜利, 毛病是： java.sql.SQLException: Io 异常: The Network Adapter could not establish the connection at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.ja va:134) atoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.ja va:179) atoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.ja va:334) atoracle.jdbc.driver.OracleConnection.<init>(OracleConnecti on.java:418) atoracle.jdbc.driver.OracleDriver.getConnectionInstance (OracleDriver.java:521) atoracle.jdbc.driver.OracleDriver.connect(OracleDriver.java :325) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle2.main(TestOracle2.java:18) 我也不知道为什么... 幽怨了.. 接下来建个web project, 来测试oracle的分页, 挺麻烦, 不记录了.. 在oracle中把持数据 - 使用特定格式拔出日期值使用 to_date函数请年夜家思考：如何拔出列带有日期的表, 并依照年-月-日的格式拔出？ insert into empvalues (9998,'xiaohong', 'MANAGER',7782,to_date('1988-12- 12','yyyy-mm-dd'),,, 10); 注意： insert into emp values (9998,'xiaohong','MANAGER', 7782,'12-12月-1988',,,10); 这句语句是可以胜利运行的使用子查询拔出数据介绍当使用valus子句时, 一次只能拔出一行数据, 当使用子查询拔出数据时, 一条inset语句可以拔出年夜量的数据.当处置行迁移或者装载外部表的数据到数据库时, 可以使用子查询来拔出数据. 把emp表中10号部份的数据导入到新表中 create table kkk(myId number(4), myName varchar2(50), myDeptnumber(5)); insert into kkk (myId, myName,myDept) select empno, ename, deptno from emp where deptno = 10; 介绍使用update语句更新数据时, 既可以使用表达式或者数值直接修改数据, 也可以使用子查询修改数据. 问题：希望员工SCOTT的岗位、工资、补助与SMITH员工一样.update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH') where ename='SCOTT';八：oracle中事务处置什么是事务事务用于保证数据的一致性, 它由一组相关的dml语句组成, 该组的dml(数据把持语言, 增删改, 没有查询)语句要么全部胜利, 要么全部失败. 如：网上转账就是典范的要用事务来处置, 用于保证数据的一致性. dml 数据把持语言银行转账、QQ申请、车票购买事务和锁当执行事务把持时（dml语句）, oracle会在被作用的表上加锁, 防止其它用户修改表的结构.这里对我们的用户来来讲是非常重要的. .....其它进程排序, 知道1号进程完成, 锁翻开, 2号进程进入.依次进行, 如果有进程级别较高的, 可以插队. 提交事务当执行用commit语句可以提交事务.当执行了commit语句之后, 会确认事务的变动、结束事务.删除保管点、释放锁, 当使用commit语句结束事务之后, 其它会话将可以检查到事务变动后的新数据. 保管点就是为回退做的.保管点的个数没有限制回退事务在介绍回退事务前, 我们先介绍一下保管点（savepoint）的概念和作用.保管点是事务中的一点.用于取消部份事务, 当结束事务时, 会自动的删除该事务所界说的所有保管点.当执行rollback时, 通过指定保管点可以回退到指定的点, 这里我们作图说明. 事务的几个重要把持 1.设置保管点 savepoint a 2.取消部份事务 rollback to a 3.取消全部事务 rollback 注意：这个回退事务, 必需是没有commit前使用的；如果事务提交了, 那么无论你刚才做了几多个保管点, 都统统没有. 如果没有手动执行commit,而是exit了, 那么会自动提交java法式中如何使用事务在java把持数据库时, 为了保证数据的一致性, 比如账户把持（1）从一个账户中减失落10$（2）在另一个账户上加入10$,我们看看如何使用事务？Java代码1.package com.sp;2.import java.sql.Connection;3.import java.sql.DriverManager;4.import java.sql.ResultSet;5.import java.sql.Statement;。
韩顺平Oracle笔记——表空间
•
•
<3>设置文件的自动增长：alter tablespace 表空间名称 '数据文件 路径' autoextend on next 大小 maxsize 最大容量;
管理表空间和数据文件——移动数 据文件
• • • • • • 有时，如果数据文件所在的磁盘损坏，该数据文件将不能再使用，为了能够 重新使用，需要将这些文件的副本移动到其它的磁盘，然后恢复。 下面以移动数据文件sp01.dbf举例说明: <1>确定数据文件所在的表空间 确定数据文件所在的表空间：select tablespace_name from 确定数据文件所在的表空间 dba_data_files where file_name='d:\test\sp01.dbf'; <2>使表空间脱机：确保数据文件的一致性，将表空间转变为offline的状 使表空间脱机： 使表空间脱机 态。alter tablespace sp01 offline; <3>使用命令移动数据文件到指定的目标位置：host move 使用命令移动数据文件到指定的目标位置： 使用命令移动数据文件到指定的目标位置 d:\test\sp01.dbf c:\test\sp01.dbf <4>执行 执行alter tablespace命令 命令：在物理上移动了数据后，还必须执行alter 执行 命令 tablespace命令对数据库文件进行逻辑修改。alter tablespace sp01 rename datafile 'd:\sp01.dbf' to 'c:\sp001.db'; <5>使表空间联机：在移动了数据文件以后，为了使用户可以访问该表 使表空间联机： 使表空间联机 空间，必须将其转变为online状态。alter tablespace data01 online;
韩顺平 玩转oracle 10g 实战教程第6讲
常用的预定义例外
ORACLE 预定之例外情况的处理,下列出常见几个 例外情况名 错误代码 描述
对于SELECT 叙述没有传回任何值。 只允许传回一笔记录的SELECT 叙述结果却多于一笔。 使用非法的的光标操作。 出现数值、数据形态转换、撷取字符串或强制性的错误。 字符串到数值的转换失败。 被零除。 试图向具有唯一键值的索引中插入一个重复键值。 没有case条件匹配 游标没有打开
玩转oracle 10g实战教程
主讲 韩顺平
oracle视图 视图
介绍 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一 系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据 值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在 引用视图时动态生成。[图示]
■
玩转oracle 10g实战教程
主讲 韩顺平
oracle视图 视图
■
视图与表的区别
①表需要占用磁盘空间，视图不需要 ②视图不能添加索引 ③使用视图可以简化 复杂查询 比如:学生选课系统 ④视图用利于提高安全性 比如:不同用户查看不同视图
玩转oracle 10g实战教程
主讲 韩顺平
oracle视图 视图
■
是pl/sql中最简单的循环语句,这种循环语句以loop开头，以end loop 结尾,这种循环至少会被执行一次. 案例:现有一张表users，表结构如下
循环语句 -loop
请，编写一个过程，可输入用户名，并循环添加10个用户到users表 中，用户编号从1开始增加.
玩转oracle 10g实战教程
NO_DATA_FOUND ORA-01403 TOO_MANY_ROWS ORA-01427 INVALID_CURSOR ORA-01001 VALUE_ERROR ORA-06502 INVALID_NUMBER ORA-01722 ZERO_DIVIDE ORA-01476 DUP_VAL_ON_INDEX ORA-00001 CASE_NOT_FOUND ORA-xxxxx CURSOR_NOT_OPEN ORA-xxxxxx
oracle触发器学习笔记
oracle触发器学习笔记功能：1、允许/限制对表的修改2、⾃动⽣成派⽣列，⽐如⾃增字段3、强制数据⼀致性4、提供审计和⽇志记录5、防⽌⽆效的事务处理6、启⽤复杂的业务逻辑开始create trigger biufer_employees_department_idbefore insert or updateof department_idon employeesreferencing old as old_valuenew as new_valuefor each rowwhen (new_value.department_id<>80 )begin:new_mission_pct :=0;end;/触发器的组成部分：1、触发器名称2、触发语句3、触发器限制4、触发操作1、触发器名称create trigger biufer_employees_department_id命名习惯：biufer（before insert update for each row）employees 表名department_id 列名2、触发语句⽐如：表或视图上的DML语句DDL语句数据库关闭或启动,startup shutdown 等等before insert or updateof department_idon employeesreferencing old as old_valuenew as new_valuefor each row说明：1、⽆论是否规定了department_id ，对employees表进⾏insert的时候2、对employees表的department_id列进⾏update的时候3、触发器限制when (new_value.department_id<>80 )限制不是必须的。
此例表⽰如果列department_id不等于80的时候，触发器就会执⾏。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第三讲Oracle的基本使用--基本命令sql*plus的常用命令连接命令1.conn[ect]用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper2.disc[onnect]说明: 该命令用来断开与当前数据库的连接3.psssw[ord]说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
4.show user说明: 显示当前用户名5.exit说明: 该命令会断开与数据库的连接，同时会退出sql*plus文件操作命令1.start和@说明: 运行sql脚本案例: sql>@ d:\a.sql或是sql>start d:\a.sql2.edit说明: 该命令可以编辑指定的sql脚本案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开3.spool说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d:\b.sql 并输入 sql>spool off交互式命令1.&说明：可以替代变量，而该变量在执行时，需要用户输入。
select * from emp where job='&job'；显示和设置环境变量概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以去修改glogin.sql脚本1.linesize说明：设置显示行的宽度，默认是80个字符show linesizeset linesize 902.pagesize说明：设置每页显示的行数目，默认是14用法和linesize一样至于其它环境参数的使用也是大同小异第四讲Oracle的用户管理oracle用户的管理创建用户概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。
create user用户名identified by密码; (oracle有个毛病，密码必须以字母开头，如果以字母开头，它不会创建用户)给用户修改密码概述：如果给自己修改密码可以直接使用password 用户名（PL\SQL developer里不能用）如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限SQL> alter user用户名identified by新密码删除用户概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user 的权限。
比如drop user用户名【cascade】在删除用户时，注意：如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade;用户管理的综合案例概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。
给一个用户赋权限使用命令grant，回收权限使用命令revoke。
3个主要角色：connect：DBA：resource：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> grant connect to xiaoming;授权成功。
注意：grant connect to xiaoming;在这里，准确的讲，connect不是权限，而是角色。
现在说下对象权限，现在要做这么件事情：* 希望xiaoming用户可以去查询emp表（对emp表的查询，emp表就是对象，因此是对象权限授予）* 希望xiaoming用户可以去查询scott的emp表grant select on scott.emp to xiaoming--表emp是scott的，用其他用户授权需要加“scott.”。
* 希望xiaoming用户可以去修改scott的emp表grant update on emp to xiaoming* 希望xiaoming用户可以去修改/删除，查询，添加scott的emp表grant all on emp to xiaoming* scott希望收回xiaoming对emp表的查询权限revoke select on emp from xiaoming第五讲权限维护//对权限的维护。
* 希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限继续给别人。
--如果是对象权限，就加入with grant optiongrant select on emp to xiaoming with grant option--如果是系统权限，就加入with admin option。
system给xiaoming权限时：grant connect to xiaoming with admin optionOracle中系统权限非级联收回，可以越级收回；对象权限级联收回，但不可越级收回。
系统权限所谓系统权限，就是oracle里已经写死的权限，这些权限，我们是不能自己去扩展的，比如select any table, create any table等，这里的权限已经在oracle里全部规定好了。
对象权限我们看看对象权限（object permission），这里是针对于特定的对象的权限，系统权限是限定了可以操作的功能，而object permission就更细化了，具体到了某个对象你可以操作的功能的权限。
使用profile管理用户口令概述：profile是口令限制，资源限制的命令集合，当建立数据库的，oracle会自动建立名称为default的profile。
当建立用户没有指定profile选项，那么oracle就会将default分配给用户。
1.账户锁定概述：指定该账户(用户)登陆时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。
例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看看怎么实现。
创建profile文件SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; （创建规则）SQL> alter user scott profile lock_account; （让scott接受规则）2.给账户(用户)解锁SQL> alter user tea account unlock;3.终止口令为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba的身份来操作。
例子：给前面创建的用户tea创建一个profile文件，要求该用户每隔10天要修改自己的登陆密码，宽限期为2天。
看看怎么做。
SQL> create profile myprofile limit password_life_time 10 password_grace_time 2;SQL> alter user tea profile myprofile;口令历史概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：1）建立profileSQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10password_reuse_time //指定口令可重用时间即10天后就可以重用2）分配给某个用户删除profile概述：当不需要某个profile文件时，可以删除该文件。
SQL> drop profile password_history 【casade】注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。
加了casade，就会把级联的相关东西也给删除掉第6讲建表oracle支持的数据类型字符类char定长最大2000个字符。
varchar2(20)变长最大4000个字符。
clob(character large object) 字符型大对象最大4Gchar 查询的速度极快浪费空间，查询比较多的数据用。
varchar 节省空间数字型number范围 -10的38次方到 10的38次方可以表示整数，也可以表示小数number(5,2)表示一位小数有5位有效数，2位小数日期类型date 包含年月日和时分秒 oracle默认格式 1-1月-1999timestamp 这是oracle9i对date数据类型的扩展。
可以精确到毫秒。
图片blob 二进制数据可以存放图片/声音 4G 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
建表--学生表create table student ( ---表名xh number(4), --学号xm varchar2(20), --姓名sex char(2), --性别birthday date, --出生日期sal number(7,2) --奖学金修改表添加一个字段SQL>ALTER TABLE student add (classId NUMBER(2));修改一个字段的长度SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30));修改字段的类型/或是名字（不能有数据）不建议做SQL>ALTER TABLE student modify (xm CHAR(30));删除一个字段不建议做(删了之后，顺序就变了。