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”

  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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from students a, classes b

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

STUDENTNO STUDENTNAM CLASSNAME

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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 周虎&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一年级一班

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 周林&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一年级二班

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一年级三班

以上语句是右连接:

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

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

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

查询结构中出现。

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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from students a, classes b

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

STUDENTNO STUDENTNAM CLASSNAME

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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 周虎&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一年级一班

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 周林&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一年级二班

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 钟林达

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

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

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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from students a, classes b

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where a.classid = b.classid;

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

总之,

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

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

微信扫一扫交流

作者:CoderZh
微信关注:hacker-thinking (一个程序员的思考)
本文出处:https://blog.coderzh.com/2008/11/23/1288850/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。