oracle系统操作教程,Oracle数据库系统调优方法

oracle系统操作教程,Oracle数据库系统调优方法详细介绍

本文目录一览： Oracle数据库系统使用经验

oracle数据库是一种大型数据库系统，一般应用于商业，政府部门，它的功能很强大，能够处理大批量的数据，在网络方面也用的非常多。Oracle数据库管理系统是一个以关系型和面向对象为中心管理数据的数据库管理软件系统，其在管理信息系统、企业数据处理、因特网及电子商务等领域有着非常广泛的应用。1.having 子句的用法having 子句对 group by 子句所确定的行组进行控制，having 子句条件中只允许涉及常量，聚组函数或group by 子句中的列。2.外部联接"+"的用法外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。若二者均不带？+?,则二者中无法匹配的均被返回。利用外部联接"+",可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢select　a.empno from emp a where a.empno not in（select empno from emp1 where job=?SALE?）；倘若利用外部联接，改写命令如下：select a.empno from emp a ,emp1 bwhere a.empno=b.empno（+）and b.empno is nulland b.job=?SALE?;可以发现，运行速度明显提高。3.删除表内重复记录的方法可以利用这样的命令来删除表内重复记录：delete from table_name awhere rowid （select max（rowid） from table_namewhere column1=a.column1 and column2=a.column2and colum3=a.colum3 and ）；不过，当表比较大（例如50万条以上）时，这个方法的效率之差令人无法忍受，需要另想办法（可参看拙文《电信计费中长途重复话单的技术处理》，《计算机与通信》，1999-07）。4.set transaction 命令的用法在执行大事务时，有时oracle会报出如下的错误：ORA-01555:snapshot too old （rollback segment too small）这说明oracle给此事务随机分配的回滚段太小了，这时可以为它指定一个足够大的回滚段，以确保这个事务的成功执行。例如set transaction use rollback segment roll_abc;delete from table_name where commit;回滚段roll_abc被指定给这个delete事务，commit命令则在事务结束之后取消了回滚段的指定。5.使用索引的注意事项select,update,delete 语句中的子查询应当有规律地查找少于20%的表行。如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高。索引可能产生碎片，因为记录从表中删除时，相应也从表的索引中删除。表释放的空间可以再用，而索引释放的空间却不能再用。频繁进行删除操作的被索引的表，应当阶段性地重建索引，以避免在索引中造成空间碎片，影响性能。在许可的条件下，也可以阶段性地truncate表，truncate命令删除表中所有记录，也删除索引碎片。6.数据库重建应注意的问题在利用import进行数据库重建过程中，有些视图可能会带来问题，因为结构输入的顺序可能造成视图的输入先于它低层次表的输入，这样建立视图就会失败。要解决这一问题，可采取分两步走的方法：首先输入结构，然后输入数据。命令举例如下 （uesrname:jfcl,password:hfjf,host sting:ora1,数据文件：expdata.dmp）：imp jfcl/hfjf@ora1 file=empdata.dmp rows=Nimp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000commit=Y ignore=Y第一条命令输入所有数据库结构，但无记录。第二次输入结构和数据，64000字节提交一次。ignore=Y选项保证第二次输入既使对象存在的情况下也能成功。以上六条小经验是从平时的工作中总结出来的，拿出来与大家分享，希望能帮到大家。

如何将oracle的日期格式转换为yyyymmdd？

oracle软件已经在中国使用了很多年，这种软件专业性特别强，而且数据库功能非常强大，它的数据库系统在银行业使用是比较多的，对于初学者来说，想踏入这个领域却不知道从哪儿开始学才好，还是从基础操作学起，日期格式转换也是大家在操作时经常用到的，下面来分享一下oracle日期格式转换yyyymmdd的操作方法，这是oracle的基础操作，都是由浅入深的。有各种软件的课程教程，非常适合想要自学的小伙伴。
oracle日期格式转换yyyymmdd的操作教程：
1.日期转化为字符串，我们以(以2016年10月20日为例)，操作如下图所示。
2.字符串和时间相互转换，操作如下图所示。
3.查询某天是星期几，操作如下图所示。
4.两个日期间的天数，操作如下图所示。
以上就是oracle日期格式转换yyyymmdd的操作过程了，以上四种操作就是常用到的最基本的日期转换格式。

win8系统如何安装oracle10g？win8系统无法直接安装oralce10g的解决方法

现在越多越多用户将自家电脑升级为win8操作系统，细心的用户发现win8系统无法直接安装oralce10g，需要修改Oracle的安装配置参数才可完成安装，那么win8系统如何顺利安装oracle10g呢？下面小编提供win8系统顺利安装oralce10g的教程。1、修改stage\prereq\db\refhost.xml文件：当打开refhost.xml后会发现有：.....在后面添加下面的配置：2、修改oracle10g\install目录中oraparam.ini文件；3、把#Windows=4.0,5.0,5.1,5.2修改成#Windows=4.0,5.0,5.1,5.2,6.1,6.2并在后面添加以下配置：[Windows-6.1-required]#MinimumdisplaycolursforOUItorunMIN_DISPLAY_COLORS=256#MinimumCPUspeedrequiredforOUI#CPU=300[Windows-6.1-optional][Windows-6.2-required]#MinimumdisplaycoloursforOUItorunMIN_DISPLAY_COLORS=256#MinimumCPUspeedrequiredforOUI#CPU=300[Windows-6.2-optional]；4、右键setup.exe，属性-兼容性-以兼容模式运行这个程序windowsxp（servicepack3），以管理员身份运行安装就可以了；5、点击setup.exe文件运行后，oracle会先检查安装先决条件，如果满足了条件就会弹出下面的安装界面。6、后续的oracle安装与在其他的操作系统上安装是一致的，不懂的话可以用百度搜索一下oracle10g安装方法，最后完成oralce10g。win8系统无法直接安装oralce10g的解决方法讲解到这里了，有需要的用户可以掌握！

oracle触发器如何使用

触发器 是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。 1.说明 1)触发器是一种特殊的 触发器 是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。1.说明1)触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用2)触发器分类：1.DML触发器： 创建在表上，由DML事件引发2.instead of触发器： 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，，它是专门为进行视图操作的一种处理方法)3.DDL触发器： 触发事件时数据库对象的创建和修改4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发3)组成：1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句(如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。2.触发时间：即该触发器是在触发事件发生之前(BEFORE)还是之后(AFTER)触发3.触发操作：触发器触发后要完成的事情4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。(比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次)2.语法1)说明不同类型的触发器例如DML触发器，Instead of触发器，系统触发器语法格式区别较大2)一般语法CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件ON表名/视图名[FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器BEGINpl/sql语句ENDcreate [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)]]sql_statement[,...n]例如：CREATE OR REPLACE TRIGGER trigger_name ON table_name[FOR EACH ROW]WHEN (condition)DECLAREBEGINEND;3)instead of 触发器语法语法：CREATE [OR REPLACE] TRIGGER trigger_nameINSTEAD OF{INSERT|DELETE|UPDATE [OF COLUMN...]}[OR {INSERT| DELETE| UPDATE [OF COLUMN...]}]ON VIEW_NAME[REFFERENCING{OLD [AS] OLD | NEW [AS] NEW| PARENT AS PARENT}] // 可以指定相关名称，当前的默认相关名称为OLD和NEW,应用相关名称时需要加：[FOR EACH ROW] //instead of 触发器只能在行级上触发，因为没有必要指定[WHEN CONDITION]DECLAREBEGINEND;说明：INSTEAD OF 用于对视图的DML触发，由于视图可能有多个表进行联结而成，因而并非所有的联结均可更新，运用 INSTEAD OF 触发器可完成相应的操作。3.实例创建测试表格：CREATE TABLE "HNZC"."TRIGGERTEST"("ID" VARCHAR2(20 BYTE),"NAME" VARCHAR2(20 BYTE),"SCORE" NUMBER);create table tab1 select * from triggertest;1)DML触发器/行级触发器触发器如下：CREATE OR REPLACE TRIGGER TRIGGER1AFTER INSERT ON TRIGGERTEST //插入后触发FOR EACH ROW //行级触发器BEGININSERT INTO tab1(ID,NAME) VALUES('22','33');END;执行语句：insert into triggertest (id) values ('aabbcc');语句执行结束，表tab1中新增加一条数据2)限制对表的修改(例如非工作时间不能修改某些表)触发器如下：CREATE OR REPLACE TRIGGER TRIGGER1AFTER INSERT ON TRIGGERTESTFOR EACH ROWBEGINIF(TO_CHAR(SYSDATE,'DAY') IN ('星期三','星期天')) THEN RAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest'); END IF;END;执行语句：insert into triggertest (id) values ('aabbcc');今天周三因而输出结果为：在行 1 上开始执行命令时出错:insert into triggertest (id) values ('aabbcc')错误报告:SQL 错误: ORA-20001: 不是上班时间，不能修改表格triggertestORA-06512: 在 "HNZC.TRIGGER1", line 3ORA-04088: 触发器 'HNZC.TRIGGER1' 执行过程中出错通常对表的修改限制如下(即周一至周五9——18点能修改表格)CREATE OR REPLACE TRIGGER TRIGGER1BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTESTFOR EACH ROWBEGINIF(TO_CHAR(SYSDATE,'DAY') IN ('星期六','星期天')) OR(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '9:00' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest'); END IF;END;3)增加限制条件(如不能更改某个员工的记录)触发器如下：(如下实现月儿的分数只能增加)CREATE OR REPLACE TRIGGER TRIGGER1BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTESTFOR EACH ROWWHEN(OLD.NAME='月儿')BEGINCASE WHEN UPDATING('SCORE') THENIF:NEW.SCORE当前月儿的分数为20当修改为10时出错UPDATE "HNZC"."TRIGGERTEST" SET SCORE = '10' WHERE ROWID = 'AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN = '47685303'ORA-20001: 月儿的分数只能提升不能下降ORA-06512: 在 "HNZC.TRIGGER1", line 4ORA-04088: 触发器 'HNZC.TRIGGER1' 执行过程中出错当修改为30时成功UPDATE "HNZC"."TRIGGERTEST" SET SCORE = '30' WHERE ROWID = 'AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN = '47685303'提交成功4)在触发器中调用存储过程触发器为：CREATE OR REPLACE TRIGGER TRIGGER1BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTESTFOR EACH ROWBEGINTESTPRO1();END;存储过程为：create or replacePROCEDURE TESTPRO1 AS BEGINinsert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;执行完毕后tab1中增加一条数据5)级联更新触发器如下(triggertest表中name修改时同时修改tab1中的name)create or replacePROCEDURE TESTPRO1 AS BEGINinsert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;执行语句：update triggertest set name= '水儿' where;结果：tab1中name为月儿的也更改为水儿6)instead of触发器TABLE STUDENT表格数据如下创建视图student_viewCREATE OR REPLACE VIEW STUDNET_VIEWAS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;视图数据如下：对视图student_view 执行如下操作：DELETE FROM STUDNET_VIEW WHERE CLASSID='111';执行结果：错误报告:SQL 错误: ORA-01732: 此视图的数据操纵操作非法01732. 00000 - "data manipulation operation not legal on this view"解决方法：创建INSTEAD OF 视图CREATE OR REPLACE TRIGGER STUDENT_VIEW_DELETEINSTEAD OF DELETE ON STUDNET_VIEWFOR EACH ROWBEGINDELETE FROM STUDENT WHERE CLASSID=:OLD.CLASSID;END STUDENT_VIEW_DELETE;执行删除语句DELETE FROM STUDNET_VIEW WHERE CLASSID='111';执行结果：删除成功1 行已删除。4.注意事项1) 在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL语句(CREATE、ALTER、DROP)2) 触发器中不能使用commit语句，触发器的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMIT和ROLLBACK;3) 一个表上的触发器越多，对于表的DML操作性能影响越大4) 触发器最大为32K

oracle数据库安装步骤

操作方法如下：
操作设备：戴尔电脑
操作系统：win10
操作软件：oracle数据库
1、首先我们打开PLSQL，点取消进去进入数据库。
2、然后弹出PLSQL主界面，找到菜单栏的tools选择属性preferences进去。
3、弹出如下界面，配置oracleclient，配置好两个设置项，点apply，然后点ok，关闭PLSQL，重启PLSQL。
4、上边的两个填写项就是第一步中的目录，以及目录下的一个文件，如下图所示，在哪个目录就对应的去找就可以了。
5、重启PLSQL进入到登录界面，输入相关信息，用户名密码，以及连接信息连接信息：可以是ip:1521/实例名，也可以是network/admin目录下的tnsname.ora中配置的TNS别名。
6、连接进去成功之后。可以随便写一个语句查查，是否正常，查询无误。

Oracle数据库入门之函数类型

　　Oracle主要数据类型
　　概述 各种数据库所支持的数据类型大同小异 与标准SQL语言中的数据类型可能略有出入
　　类型 varchar 可以在声明字段时设置它的长度上限 而且使用它之后 就不必再考虑空格的存在
　　若插入的字符串的长度低于长度上限 系统就会自动将其缩减为字符串的真实长度
　　number(m n) 既可以用来表示整型 也可以表示浮点型 但m不可以超过 如果n为 或者省略n 就代表它是整数

　　date 用来存放日期和时间
　　blob 通常是在应用程序中使用到它 而不是在数据库中利用SQL指令直接使用
　　比如通过JDBC技术访问数据库 读写blob或clob类型的字段 即读写长的字符串信息等等
　　char 它是一种定长的字符类型 在Oracle数据库不区分字符和字符串 它们被统称为字符型或文本型
　　所谓定长的字符型是指 插入的字符串若没有达到约定的字段长度 系统就会在字符串尾部自动补空格
　　同样 读取时的字段长度永远是声明时的字段长度 而且在比较字符串内容的时候 也需要考虑到空格的过滤
　　nchar 它也是定长的字符串类型 它是SQL语言标准中规定的 通常采用Unicode编码来保存不同国家或不同语言的字符
　　varchar SQL标准在定义varchar时并没有保证能够向前和向后兼容 即有可能随着语言标准的修改而产生不兼容的问题
　　所以Oracle定义了同varchar型类似的varchar 型 就是为了在Oracle以后的版本中 都永远支持varchar 类型
　　Oracle这么做就是为了确保此类型向前后兼容 以达到能够在Oracle系列数据库中进行数据的导入和导出的目的
　　long 它和varchar 的差别在于 它不支持对字符串内容进行检索 即查询时不可以对它的内容进行条件查询
　　而varchar 和char nchar型等等都可以在查询的时候直接检索字符串的内容
　　补充 select * from v$nls_parameters;数据库的配置信息以数据表的形式存在 通常称其为关于数据的数据或数据字典
　　实际上它查询的是数据字典中的一个视图 其中NLS_CHARACTERSET对应的是当前的数据库字符集
　　缺省均为使用数据库字符集 教程中使用的是安装时默认的ZHS GBK字符集 即汉字占 个字节 英文占 个字节
　　而NLS_NCHAR_CHARACTERSET对应的是nchar或nvarchar 类型所采用的辅助字符集 即AL UTF 字符集
　　实际上AL UTF 是一种 位定长的Unicode编码的字符集 而数据库字符集以及这种国家字符集都可以修改
　　但数据库字符集修改后可能会面临很严重的后果 除非是数据库管理员 普通用户不必对这方面进行深究
　　数据库中的数据导入导出的时候 如果源数据库和目标数据库所采用的字符集不同 也很容易出问题
　　函数
　　概述 函数可以认为是能够完成相对独立的功能的一段代码的集合 Oracle函数相当于其它语言中的方法或过程
　　Oracle函数可以分为单行函数和多行函数两大类 Oracle函数都是有返回值的
　　所谓的单行函数是针对查询结果中的每一行都起作用 都会返回一个结果
　　多行函数也就是所谓分组函数 是针对一组查询的记录 或者说多行 返回一个结果
　　单行 操作数据项 接受参数并返回处理结果 对每一返回行均起作用 可修改数据类型 可嵌套使用
　　单行函数分为字符函数 数值函数 日期函数 转换函数 通用函数
　　多行 也称分组函数 即对一组数据进行运算 针对一组数据(多行记录)只能返回一个结果
　　多行函数包括avg() count() max() min() sum()等
　　比如select avg(sal) max(sal) min(sal) sum(sal) max(hiredate) min(hiredate) from emp;
　　续一 使用Oracle的系统函数中的单行函数可实现诸多功能 如对数据进行计算 控制数据的输出格式
　　设置和改变日期的显示格式 进行数据类型转换 使用NVL等函数处理空值 实现IF THEN ELSE多路分支逻辑等等
　　续二 转换函数不会改变表中数据的字段类型和值 它就相当于将数据复制了一份 所转换的是复制之后的数据
　　数据类型转换包括隐含转换和显式转换两种方式 建议使用显式的数据类型转换 确保SQL语句的可靠性
　　续三 通用函数适用于包括空值在内的任何类型数据 通常用来实现空值的处理 空值的过滤或设置缺省值等
　　通用函数包括nvl() nvl () nullif() coalesce() case表达式 decode()等
　　嵌套 单行函数可以嵌套使用 嵌套层次无限制 分组函数最多可嵌套两层 嵌套函数的执行顺序是由内到外
　　单行比如select empno lpad(initcap(trim(ename)) ) 姓名 job sal from emp;
　　多行比如select max(avg(sal)) from emp group by deptno; 其实这里再使用分组函数就没有意义了
　　说明 通常数据库层面提供的函数 只是进行数据的简单的处理 或者说是只能实现极为常规的功能
　　所以就不应该 或者说是不要指望在数据库查询的层面来实现特别复杂的业务逻辑
　　如果应用程序的逻辑跟数据库混在一起的话 会不利于代码的维护和更新
　　而且也不利于数据库的管理 包括数据移植 数据库导入导出等等
　　日期类型
　　概述 在计算机操作系统或者各种高级编程语言中 日期通常会被保存成一个长整数 通常记录的是毫秒
　　Oracle内部以数字格式存储日期和时间信息 世纪 年 月 日 小时 分钟 秒
　　缺省的日期格式是DD—MON—YY 可使用sysdata函数获取当前系统日期和时间
　　运算 日期型数据可以直接加或减一个数值 结果认为日期 约定的该数值代表的是相加减的天数
　　两个日期型数据可以相减 结果为二者相差多少天 二者不能 因为日期相加是没有意义的
　　NVL()函数
　　概述 它用于将空值null替换为指定的缺省值 适用于字符 数字 日期等类型数据
　　格式 NVL(exp exp ) 如果表达式exp 值为null 则返回exp 值 否则返回exp 值
　　举例 select empno ename sal m sal+nvl(m ) from emp;
　　select empno ename job nvl(job No job yet ) from emp;
　　NVL ()函数
　　概述 它用于实现条件表达式功能
　　格式 NVL (exp exp exp ) 如果表达式exp 值不为null 则返回exp 值 否则返回exp 值
　　举例 select empno ename sal m nvl (m sal+m sal) 总收入 from emp;
　　NULLIF()函数
　　概述 它用于数据等价性比较并根据比较结果返回null或其中一个被比较的数值 实际开发中应用并不是很多
　　格式 nullif(exp exp ) 如果表达式exp 与exp 的值相等 则返回null 否则返回exp 的值
　　举例 select name 原名 nullif(pen_name name) 化名 from author;
　　COALESCE()函数
　　概述 它用于实现数据“接合”功能
　　格式 coalesec(exp exp ) 依次考察各参数表达式 遇到非null值即停止并返回该值
　　若表达式均为null值 则返回null 通常最后一个表达式都是能确保不是空值的字段
　　举例 select empno ename sal m coalesec(sal+m sal ) 总收入 from emp;
　　CASE表达式
　　概述 它用于实现多路分支结构
　　格式 case exp when parison_exp then return_exp
　　[when parison_exp then return_exp
　　when parison_expn then return_expn
　　else else_exp]
　　end
　　举例 select empno ename sal
　　case deptno when then 财务部
　　when then 研发部
　　when then 销售部
　　else 未知部门
　　end 部门
　　from emp;
　　说明 CASE中的每一个表达式(如deptno 财务部等)都可以是复合而成的
　　这种对齐方式的书写是为了增加可读性 当然也可以把代码写在同一行上
　　其中case到end之间的整体就相当于普通查询中的一个字段 end后面的“部门”是别名
　　DEDODE()函数
　　概述 和case表达式类似 它也用于实现多路分支结构
　　格式 decode(col|expression search result
　　[ search result ]
　　[ default])
　　举例 select empno ename sal
　　decode(deptno 财务部
　　 研发部
　　 销售部
　　 未知部门 )
　　部门
　　from emp;
　　COUNT()函数
　　格式 count(*)返回组中总记录数目
　　count(exp)返回表达式exp值非空的记录
　　count(distinct(exp))返回表达式exp值不重复的 非空的记录数目
　　举例 select count(*) from emp; 缺省的情况下 整个表就是一组
　　select count(m) from emp; 返回emp表中m字段不为空的记录(行)数目
　　select count(distinct(deptno)) from emp; 查找deptno值为非空且不重复的记录数目
　　分组函数与空值
　　概述 分组函数省略列中的空值 可使用NVL()函数强制分组函数处理空值
　　举例 select avg(m) from emp; 等价于sum(m)/count(m)
　　select sum(m) from emp; 计算表中非空的m值的总和
　　select avg(nvl(m )) from emp; 等价于avg(nvl(m ))/count(*)
　　GROUP BY子句
　　概述 它用于将表中数据分成若干小组
　　格式 select column group_function(column)
　　from table
　　[where condition]
　　[group by group_by_expression]
　　[order by column];
　　举例 select deptno avg(sal) from emp group by deptno;
　　说明 出现在SELECT列表中的字段 如果不是包含在组函数中 那么该字段必须同时在GROUP BY子句中出现
　　包含在GROPY BY子句中的字段则不必须出现在SELECT列表中 子句执行顺序是where→group by→order by缺省按升序排列
　　补充 select deptno job avg(sal) from emp group by deptno job order by deptno desc; 基于多个字段的分组
　　select deptno avg(sal) from emp; 非法
　　注意 如果没有GROUP BY子句 SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况
　　WHERE中不允许使用分组函数 如select deptno avg(sal) from emp where avg(sal)> group by deptno; 非法
　　这跟子句执行的顺序有关 where子句最先执行 在执行where子句的时候还没有执行过group by子句
　　于是程序不知道这是在分组 也不曾计算过avg(sal)的组内平均工资 所以在where子句中不允许使用分组函数
　　由于还没有执行过group by子句 所以此时就不确定如何怎么分组以及分多少个组
　　所以where子句中只能进行初级过滤 此时可以使用HAVING子句实现对平均工资的过滤
　　HAVING子句
　　概述 它用于过滤分组
　　格式 select column group_function(column)
　　from table
　　[where condition]
　　[group by group_by_expression]
　　[having group_condition]
　　[order by column];
　　举例 select deptno job avg(sal)
　　from emp
　　where hiredate >= to_date( yyyy mm dd )
　　group by by deptno job
　　having avg(sal) >
lishixinzhi/Article/program/Oracle/201311/19087

快速掌握Oracle数据库游标的使用方法

　　 显式游标
　　当查询返回结果超过一行时 就需要一个显式游标 此时用户不能使用select into语句 PL/SQL管理隐式游标 当查询开始时隐式游标打开 查询结束时隐式游标自动关闭 显式游标在PL/SQL块的声明部分声明 在执行部分或异常处理部分打开 取出数据 关闭
　　
　　 使用游标
　　这里要做一个声明 我们所说的游标通常是指显式游标 因此从现在起没有特别指明的情况 我们所说的游标都是指显式游标 要在程序中使用游标 必须首先声明游标

　　
　　 声明游标
　　语法
　　
　　CURSOR cursor_name IS select_statement;
　　在PL/SQL中游标名是一个未声明变量 不能给游标名赋值或用于表达式中
　　
　　例
　　
　　DELCARE CURSOR C_EMP IS SELECT empno ename salary FROM emp WHERE salary> ORDER BY ename; BEGIN
　　在游标定义中SELECT语句中不一定非要表可以是视图 也可以从多个表或视图中选择的列 甚至可以使用*来选择所有的列
　　 打开游标
　　使用游标中的值之前应该首先打开游标 打开游标初始化查询处理 打开游标的语法是
　　
　　OPEN cursor_name
　　cursor_name是在声明部分定义的游标名
　　
　　例
　　OPEN C_EMP; 关闭游标
　　语法
　　CLOSE cursor_name
　　
　　例
　　CLOSE C_EMP; 从游标提取数据 从游标得到一行数据使用FETCH命令 每一次提取数据后 游标都指向结果集的下一行 语法如下
　　FETCH cursor_name INTO variable[ variable ]
　　对于SELECT定义的游标的每一列 FETCH变量列表都应该有一个变量与之相对应 变量的类型也要相同
　　
　　
　　
　　例
　　
　　SET SERVERIUTPUT ON DECLARE v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; CURSOR c_emp IS SELECT ename salary FROM emp; BEGIN OPEN c_emp; FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); CLOSE c_emp; END
　　这段代码无疑是非常麻烦的 如果有多行返回结果 可以使用循环并用游标属性为结束循环的条件 以这种方式提取数据 程序的可读性和简洁性都大为提高 下面我们使用循环重新写上面的程序
　　
　　SET SERVERIUTPUT ON DECLARE v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; CURSOR c_emp IS SELECT ename salary FROM emp; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_ename v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); END
　　
　　 记录变量
　　定义一个记录变量使用TYPE命令和%ROWTYPE 关于%ROWsTYPE的更多信息请参阅相关资料
　　记录变量用于从游标中提取数据行 当游标选择很多列的时候 那么使用记录比为每列声明一个变量要方便得多
　　当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时 如果要选择表中所有列 那么在SELECT子句中使用*比将所有列名列出来要得多
　　例
　　
　　SET SERVERIUTPUT ON DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS SELECT * FROM emp; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO r_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUT PUT PUT_LINE( Salary of Employee ||r_emp ename|| is || r_emp salary); END LOOP; CLOSE c_emp; END;
　　%ROWTYPE也可以用游标名来定义 这样的话就必须要首先声明游标
　　
　　SET SERVERIUTPUT ON DECLARE CURSOR c_emp IS SELECT ename salary FROM emp; R_emp c_emp%ROWTYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO r_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUT PUT PUT_LINE( Salary of Employee ||r_emp ename|| is || r_emp salary); END LOOP; CLOSE c_emp; END;
　　 带参数的游标
　　与存储过程和函数相似 可以将参数传递给游标并在查询中使用 这对于处理在某种条件下打开游标的情况非常有用 它的语法如下
　　
　　CURSOR cursor_name[(parameter[ parameter] )] IS select_statement;
　　定义参数的语法如下
　　
　　Parameter_name [IN] data_type[{:=|DEFAULT} value]
　　与存储过程不同的是 游标只能接受传递的值 而不能返回值 参数只定义数据类型 没有大小
　　另外可以给参数设定一个缺省值 当没有参数值传递给游标时 就使用缺省值 游标中定义的参数只是一个占位符 在别处引用该参数不一定可靠
　　在打开游标时给参数赋值 语法如下
　　
　　OPEN cursor_name[value[ value] ];
　　参数值可以是文字或变量
　　例
　　
　　DECALRE CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR ) IS SELECT ename salary FROM emp WHERE deptno=p_dept ORDER BY ename r_dept DEPT%ROWTYPE; v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; v_tot_salary EMP SALARY%TYPE; BEGIN OPEN c_dept; LOOP FETCH c_dept INTO r_dept; EXIT WHEN c_dept%NOTFOUND; DBMS_OUTPUT PUT_LINE ( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; OPEN c_emp(r_dept deptno); LOOP FETCH c_emp INTO v_ename v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT PUT_LINE ( Name: || v_ename|| salary: ||v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; CLOSE c_emp; DBMS_OUTPUT PUT_LINE ( Toltal Salary for dept: || v_tot_salary); END LOOP; CLOSE c_dept; END;
　　
　　 游标FOR循环
　　在大多数时候我们在设计程序的时候都遵循下面的步骤
　　 打开游标
　　 开始循环
　　 从游标中取值
　　 那一行被返回
　　 处理
　　 关闭循环
　　 关闭游标
　　可以简单的把这一类代码称为游标用于循环 但还有一种循环与这种类型不相同 这就是FOR循环 用于FOR循环的游标按照正常的声明方式声明 它的优点在于不需要显式的打开 关闭 取数据 测试数据的存在 定义存放数据的变量等等 游标FOR循环的语法如下
　　
　　FOR record_name IN (corsor_name[(parameter[ parameter] )] | (query_difinition) LOOP statements END LOOP;
　　下面我们用for循环重写上面的例子
　　
　　DECALRE CURSOR c_dept IS SELECT deptno dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR ) IS SELECT ename salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP SALARY%TYPE; BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT PUT_LINE ( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; FOR r_emp IN c_emp(r_dept deptno) LOOP DBMS_OUTPUT PUT_LINE ( Name: || v_ename || salary: || v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; DBMS_OUTPUT PUT_LINE ( Toltal Salary for dept: || v_tot_salary); END LOOP; END;
　　 在游标FOR循环中使用查询
　　在游标FOR循环中可以定义查询 由于没有显式声明所以游标没有名字 记录名通过游标查询来定义
　　
　　DECALRE v_tot_salary EMP SALARY%TYPE; BEGIN FOR r_dept IN (SELECT deptno dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT PUT_LINE( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; FOR r_emp IN (SELECT ename salary 　　　FROM emp 　　　WHERE deptno=p_dept 　　　ORDER BY ename) LOOP DBMS_OUTPUT PUT_LINE( Name: || v_ename|| salary: ||v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; DBMS_OUTPUT PUT_LINE( Toltal Salary for dept: || v_tot_salary); END LOOP; END;
　　 游标中的子查询
　　语法如下
　　
　　CURSOR C IS SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE dname!= ACCOUNTING );
　　可以看出与SQL中的子查询没有什么区别
　　
　　
　　 游标中的更新和删除
　　在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行 显式游标只有在需要获得多行数据的情况下使用 PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法
　　UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据 要使用这个方法 在声明游标时必须使用FOR UPDATE子串 当对话使用FOR UPDATE子串打开一个游标时 所有返回集中的数据行都将处于行级（ROW LEVEL)独占式锁定 其他对象只能查询这些数据行 不能进行UPDATE DELETE或SELECT FOR UPDATE操作
　　语法
　　
　　FOR UPDATE [OF [schema ]lumn[ [schema ]lumn] [nowait]
　　在多表查询中 使用OF子句来锁定特定的表 如果忽略了OF子句 那么所有表中选择的数据行都将被锁定 如果这些数据行已经被其他会话锁定 那么正常情况下ORACLE将等待 直到数据行解锁
　　在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下
　　
　　WHERE{CURRENT OF cursor_name|search_condition}
　　例
　　
　　DELCARE CURSOR c IS SELECT empno salary FROM emp WHERE m IS NULL FOR UPDATE OF m; v_m NUMBER( ); BEGIN FOR r IN c LOOP IF r salary< THEN v_m:=r salary* ; ELSEIF r salary< THEN v_m:=r salary* ; ELSEIF r salary< THEN v_m:=r salary* ; ELSE v_m:=r salary* ; END IF; UPDATE emp; SET m=v_m WHERE CURRENT OF c l; END LOOP; END
lishixinzhi/Article/program/Oracle/201311/16865

Oracle之通过系统表创建一个几万的数据的表的方法

一直都不知道原来不管是oracle系统默认的数据库orcl还是自己创建的数据库，都会带有系统表，系统表里面都有数据，而且有的数据达 一直都不知道原来不管是Oracle系统默认的数据库orcl还是自己创建的数据库，都会带有系统表，系统表里面都有数据，而且有的数据达到几千行，几万行，如果能利用这些数据，就可以自己创建测试数据用的表且数据量比较大。多谢今天给我指导的童鞋。嘿嘿下面是具体操作了哦：-----------------------------------------------------------------------------------------------------------原来create table a as select * from b;可以创建一个跟b一样的表啊，但是创建的表a只有表的结构，而没有表b的默认值，所以实际中貌似这样不好哦。步骤1先创建一个空表，只有表的结构但是不加载数据create table big_table as selecet * from dba_objects where 1=2;步骤2执行一个pl/sql过程通过一个循环将这些数据同时加入进去，循环10次，big_table里面的数据就有十几万了吧。。嘿嘿beginfor i in 1..10loopinsert into big_table select * from dba_objects;end loop;end;这样就得到数据量特别大的表了，但是呢，这样加载的都是重复性的数据哦，只要是这样create table big_table as select...这样创建的表，，都没有主键也没有约束，即使原来的表有，所以可以加载重复性数据，这个在实际中应该用的很少吧。

Oracle数据库系统调优方法

Oracle 数据库广泛应用在社会的各个领域，特别是在Client/Server模式的应用，但是应用开发者往往碰到整个系统的性能随着数据量的增大显着下降的问题，为了解决这个问题，从以下几个方面：数据库服务器、网络I/O、应用程序等对整个系统加以调整，充分发挥Oracle的效能，提高整个系统的性能。1 调整数据库服务器的性能Oracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能，主要从以下几个方面考虑：1.1 调整操作系统以适合Oracle数据库服务器运行Oracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整，Oracle数据库服务器也无法发挥其应有的性能。1.1.1 为Oracle数据库服务器规划系统资源据已有计算机可用资源， 规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化，特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。1.1.2 调整计算机系统中的内存配置多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降。为了避免过多地使用虚存，应加大计算机的内存。1.1.3 为Oracle数据库服务器设置操作系统进程优先级不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行。1.2 调整内存分配Oracle数据库服务器保留3个基本的内存高速缓存，分别对应3种不同类型的数据：库高速缓存，字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池，共享池再加上缓冲区高速缓存便构成了系统全程区（SGA）。SGA是对数据库数据进行快速访问的一个系统全程区，若SGA本身需要频繁地进行释放、分配，则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行。1.2.1 库缓冲区的调整库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整。1.2.2 数据字典缓冲区的调整数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率，对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整。1.2.3 缓冲区高速缓存的调整用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整。2 调整 Client/Server 模式下的网络 I/OClient/Server环境中的应用处理是分布在客户应用程序和数据库服务程序之间的。在 Client/Server环境中Client与Server之间的网络I/O是整个系统性能提高的瓶颈，一个客户应用程序引起的网络I/O越少，应用及整个系统的性能越好。减少网络I/O的最重要的一条原则：将应用逻辑集中在数据库服务器中。2.1 使用Oracle数据库的完整约束性当为应用建表时，应当为一些有特殊要求的数据加上适当的完整性约束，这样就能实现由数据库本身而不是应用程序来约束数据符合一定的条件。数据库服务器端的完整约束的执行操作是在比SQL语句级别更低的系统机制上优化，它与客户端无关，只在服务器中运行，不需在Client 端和Server端之间传递SQL语句，有效地减轻网络I/O负担。2.2 使用数据库触发器完整约束性只能实现一些较简单的数据约束条件，对一些较复杂的事物处理规则就无能为力，这时最好不要在应用程序中实施复杂的程序控制，而是应当采用数据库触发器来实施复杂的事物规则。数据库触发器能实现由数据库本身，而不是应用程序，来约束数据符合复杂的事物处理规则，并且容易创建，便于管理，避免大量的网络I/O。2.3 使用存储过程、存储函数和包Oracle的存储过程和存储函数是命名的能完成一定功能并且存储在Server端的PL/SQL的集合。包是一种把有关的过程和函数组织封装成一个数据库程序单元的方法。它们相对于应用程序的过程、函数而言，把SQL命令存储在Server端。使用存储过程和存储函数，应用程序不必再包含多个网络操作的SQL语句去执行数据库服务器操作，而是简单调用存储过程和存储函数，在网络上传输的只是调用过程的名字和输出结果，这样就可减少大量的网络I/O。3 应用程序的调整3.1 SQL语句的优化SQL语句的执行速度，可以受很多因素的影响而变化。但主要的影响因素是：驱动表、执行操作的先后顺序和索引的运用。可以由很多不同的方法间接地改变这些因素，以达到最优的执行速度。这里主要探讨当对多个表进行连接查询时应遵循的优化原则：3.2 建立和使用视图、索引利用视图可以将基表中的列或行进行裁减、隐藏一部分数据，并且能够将涉及到多个表的复杂查询以视图的方式给出，使应用程序开发简洁快速。利用索引可以提高查询性能，减少磁盘 I/O，优化对数据表的查询，加速SQL语句的执行。但任何时候建立索引都能提高性能，何时建立索引应当遵循以下原则：该表常用来在索引列上查询，该表不常更新、插入、删除等操作，查询出来的结果记录数应控制在原表的2%～4%。3.3 使用 Oracle 的数组接口当一个客户应用程序插入一行或用一个查询来向服务器请求某行时，不是发送具有单个行的网络包，而是采用数组处理，即把要插入的多个行或检索出的多个行缓冲在数组中，然后通过很少的几个包就可在网上传送这些数组。例如，一个给定的Select语句返回2000行数据，每行平均大小为40个字节，数据包的大小为4kB，而数组大小参数（arraysize）设置为20 ，则需从服务器发送100个数据包到客户机。如果简单地把（arraysize）设置为2000，那么同样的操作只需要传送 20个数据包。这样就减少了网络的传输量，提高了所有应用的性能。4 总结我们在开发应用程序时，遵循上述的方法和原则，对系统进行调整，收到了令人满意的效果。但是应当指出，由于客户机、网络、服务器这3个相互依存的组成部分都必须调整和同步才能产生最佳的性能，因此还应根据系统的具体情况，具体分析和调整。