Oracle Notes

最近工作中用到了Oracle，笔记记录一下：

1. 游标cursor

   create or replace procedure sp_test(seq_id number) as

           cursor curData is select t.propertyname, t.propertyvalue from kinfoc3.new_prop_39460 t where t.id = prop_id;

   begin

           for rowData in curData loop

                     – do something with rowData

           end loop;

   end;

   使用游标查询到数据库集后再遍历里面的子集，感觉速度非常快。

   还有一种游标是SYS_REFCURSOR型游标，经过测试，发现SYS_REFCURSOR型游标在效率上比前面的那种游标差了好多！好处是可以作出参数进行传递。

   create or replace procedure test(rsCursor out SYS_REFCURSOR) is

   cursor SYS_REFCURSOR; name varhcar(20);

   begin

   OPEN cursor FOR select name from student where … –SYS_REFCURSOR只能通过OPEN方法来打开和赋值

   LOOP

   fetch cursor into name   –SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND;              –SYS_REFCURSOR中可使用三个状态属性：                                         —%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)                                         —%ROWCOUNT(然后当前游标所指向的行位置)

   dbms_output.putline(name);

   end LOOP;

   rsCursor := cursor;

   end test;

2. 建索引加快查询速度。

   假如某个表经常根据列ID和列Name来查询，则可以建立ID+Name的所以加快查询速度。3. synonyms同义词

   从字面上理解就是别名的意思，和试图的功能类似。就是一种映射关系。

   同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。4. SQL*Loader

   SQLLoader，是Oracle数据库系统提供的一个数据移植工具，它提供了一个命令行的方式，可以让用户成批的向Oracle数据库中装入大量数据。虽然Oracle数据库与SQL Server数据库都提供了图形界面的导入工具，但是，图形界面有一个很大的不足，就是不能够直接给前台程序引用。而命令行的导入模块，则可以直接被前台的应用程序所调用，这也是SQLLoader之所以成为Oracle数据库系统最通用的工具之一的原因。

   SQL*Loader其具有如下的优势：

   1. 接被前台应用程序调用。
   
   2. 可以从既定文件中大量导入数据。
   
   3. 可以实现把多个数据文件合并成一个文件。
   
   4. 修复、分离坏的记录。
   

   一般SQL*Loader模块至少需要两个文件，才可以使用。

   一是数据文件。

           数据文件，顾名思义，就是我们需要导入的数据集合。对于Oracle系统来说，其可以支持多个格式的数据文件，如逗号分隔符或者 TAB键分隔符或者分号分隔符等文本文件，也支持固定宽度的文本文件等等。不过在实际应用中，用的最多的还是逗号分隔的文本文件。

   二是控制文件。

           控制文件其起的作用就是建立数据文件与Oracle数据表字段之间的一一对应关系。简单的说，把数据文件中的某个内容放在Oracle数据表中的那个字段上，这就是控制文件所起的主要作用。5. TRUNC()函数

   截断函数语法：

   TRUNC（date[,fmt]）

   TRUNC（number[,decimals]）

   举例：假如现在是2008-9-11 9:30(sysdate)，则：

   trunc(sysdate, ‘yy’) = 2008-1-1         – 意思是将该日期截断到“年(yy)”

   trunc(sysdate, ‘mm’) = 2008-9-1       – 将日期截断到“月(mm)”

   不传第二个参数时，默认截断到“天(dd)”，

   trunc(sysdate, ‘dd’) = 2008-9-11

   当参数是数字时，如：

   trunc(1234.5678, 1) = 1234.5          – 小数点后一位开始截断

   trunc(1234.5678, -1) = 1230            – 小数点前一位开始截断6. 索引

   Cost　该操作的成本

   Card　该操作访问的行

   Bytes 该操作访问的byte 数

   1.用IN来替换OR

       这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．

       低效: 　　SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 　　

       高效 　　SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

   2.对索引列使用OR将造成全表扫描，用UNION替换OR (适用于索引列，where 子句为等号的情况)

       高效: 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE LOC_ID = 10 　　UNION 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE REGION = “MELBOURNE” 　　

       低效: 　　SELECT LOC_ID , LOC_DESC , REGION 　　FROM LOCATION 　　WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

3. OVER (PARTITION BY ..)

   可以将结果分区并获取多列的值，具体用法：http://blog.chinaunix.net/u1/38180/showart.php?id=370545

   测试了下面两种写法的效率，发现效率几乎没有什么差别：

   写法一：

   select t.id id, max(t.time) time

           from table t

          where t.id is not null

            and trunc(t.time) < trunc(sysdate)

          group by t.id

   写法二：

   select b.id id, b.time time from (select t.id id, t.time time,

          row_number() over(partition by t.id order by t.time desc) rn

           from table t

          where t.id is not null

            and trunc(t.time) < trunc(sysdate))b where b.rn = 1

4. 触发器

是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

　　功能：

　　1、 允许/限制对表的修改

　　2、 自动生成派生列，比如自增字段

　　3、 强制数据一致性

　　4、 提供审计和日志记录

　　5、 防止无效的事务处理

　　6、 启用复杂的业务逻辑 例子：

create trigger biufer_employees_department_id

        before insert or update

               of department_id

               on employees

        referencing old as old_value

                        new as new_value

        for each row

        when (new_value.department_id<>80 )

　　begin

        :new_value.commission_pct :=0;

　　end;9. instr函数

INSTR方法的格式为

INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)

例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是

Instring

——————

1410.  Oracle左连接,右连接

数据表的连接有: 1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 2、外连接: 包括 （1）左外连接(左边的表不加限制) （2）右外连接(右边的表不加限制) （3）全外连接(左右两表都不加限制) 3、自连接(连接发生在一张基表内)

select a.studentno, a.studentname, b.classname

      from students a, classes b

      where a.classid(+) = b.classid;

STUDENTNO STUDENTNAM CLASSNAME

---------- ---------- ------------------------------

            1 周虎          一年级一班

            2 周林          一年级二班

                             一年级三班

以上语句是右连接：

即"(+)"所在位置的另一侧为连接的方向，右连接说明等号右侧的所有

记录均会被显示，无论其在左侧是否得到匹配。也就是说上例中，无

论会不会出现某个班级没有一个学生的情况，这个班级的名字都会在

查询结构中出现。

反之： select a.studentno, a.studentname, b.classname

       from students a, classes b

      where a.classid = b.classid(+);

STUDENTNO STUDENTNAM CLASSNAME

---------- ---------- ------------------------------

            1 周虎          一年级一班

            2 周林          一年级二班

            3 钟林达

则是左连接，无论这个学生有没有一个能在一个班级中得到匹配的部门号，

这个学生的记录都会被显示。

select a.studentno, a.studentname, b.classname

       from students a, classes b

      where a.classid = b.classid;

这个则是通常用到的内连接,显示两表都符合条件的记录

总之,

左连接显示左边全部的和右边与左边相同的 右连接显示右边全部的和左边与右边相同的 内连接是只显示满足条件的!11. （待补充……）

[温馨提示]：该文章由原博客园导入而来，如排版效果不佳，请移步：http://www.cnblogs.com/coderzh/archive/2008/11/23/1288850.html