Oracle Notes

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

游标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);

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;

建索引加快查询速度。

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

synonyms同义词

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

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

SQL*Loader

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

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

1. 接被前台应用程序调用。

2. 可以从既定文件中大量导入数据。

3. 可以实现把多个数据文件合并成一个文件。

4. 修复、分离坏的记录。

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

一是数据文件。

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

二是控制文件。

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

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            -- 小数点前一位开始截断

索引

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”

OVER (PARTITION BY ..)

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

写法一：

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

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

触发器

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

　　功能：

　　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;

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

——————

14

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 周林          一年级二班

                             一年级三班

以上语句是右连接：

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

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

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

查询结构中出现。

反之： 

       from students a, classes b

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

            3 钟林达

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

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

      where a.classid = b.classid;

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

总之,

左连接显示左边全部的和右边与左边相同的 

右连接显示右边全部的和左边与右边相同的 

内连接是只显示满足条件的!

（待补充……）

本文转自CoderZh博客园博客，原文链接：http://www.cnblogs.com/coderzh/archive/2008/11/23/1288850.html，如需转载请自行联系原作者