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

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

  7. 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

  8. 触发器

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

    　　功能：

    　　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函数

    10. 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

  10. Oracle左连接,右连接

    11. 数据表的连接有:
    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. （待补充……）
posted @ 2008-11-23 10:34  CoderZh  阅读(524)  评论(0)  编辑  收藏  举报