pl sql练习（4）

　　1.ROW_NUMBER 返回连续的排位，不论值是否相等

 1 select deptno,ename,sal, row_number () over (partition by deptno
 2                            order by sal desc) rn from emp;
 3 
 4 10    KING    5000    1
 5 10    CLARK    2450    2
 6 10    MILLER    1300    3
 7 20    SCOTT    5000    1
 8 20    FORD    3000    2
 9 20    JONES    2975    3
10 20    ADAMS    1100    4
11 20    SMITH    800    5
12 30    BLAKE    2850    1
13 30    ALLEN    1600    2
14 30    TURNER    1500    3
15 30    WARD    1250    4
16 30    MARTIN    1250    5
17 30    JAMES    950    6

View Code

　　2.DENSE_RANK 具有相等值的行排位相同，序号是连续的

 1 select deptno,ename,sal, DENSE_RANK () over (partition by deptno
 2                            order by sal desc) rn from emp;
 3 
 4 10    KING    5000    1
 5 10    CLARK    2450    2
 6 10    MILLER    1300    3
 7 20    SCOTT    5000    1
 8 20    FORD    3000    2
 9 20    JONES    2975    3
10 20    ADAMS    1100    4
11 20    SMITH    800    5
12 30    BLAKE    2850    1
13 30    ALLEN    1600    2
14 30    TURNER    1500    3
15 30    WARD    1250    4
16 30    MARTIN    1250    4
17 30    JAMES    950    5

View Code

　　3.RANK 具有相等值的行排位相同，序数随后跳跃

 1 select deptno,ename,sal, RANK () over (partition by deptno
 2                            order by sal desc) rn from emp;
 3 
 4 10    KING    5000    1
 5 10    CLARK    2450    2
 6 10    MILLER    1300    3
 7 20    SCOTT    5000    1
 8 20    FORD    3000    2
 9 20    JONES    2975    3
10 20    ADAMS    1100    4
11 20    SMITH    800    5
12 30    BLAKE    2850    1
13 30    ALLEN    1600    2
14 30    TURNER    1500    3
15 30    WARD    1250    4
16 30    MARTIN    1250    4
17 30    JAMES    950    6

View Code

　　4.系统常用的预定义异常

NO_DATA_FOUND：执行查询无数据、引用一个末初使化的表、通过UTL_FILE包调用到尾的文件
TOO_MANY_ROWS：采用SELECT INTO语句，但返回的记录超过了1条
DUP_VAL_ON_INDEX:插入或者更新语句，与唯一索引相冲突

ZERO_DIVIDE:0为除数
INVALID_NUMBER: 将字符串转换成数字，但是转换失败

VALUE_ERROR: 在执行转换、截断、非法转换数据到文本出错
CURSOR_ALREADY_OPEN:打开一个已经打开的游标

　　5.声明用户定义异常

Declare
my_error EXCEPTION;

触发用户定义异常
RAISE my_error

　　6.自定义程序异常

 1 RAISE_APPLICATION_ERROR使用
 2 
 3 
 4 
 5 DECLARE
 6   rate itemfile.itemrate%TYPE;
 7   rate_exception EXCEPTION;
 8 BEGIN
 9   SELECT NVL(itemrate,0) INTO rate FROM itemfile 
10   WHERE  itemcode = 'i207';
11   IF rate = 0 THEN
12     RAISE rate_exception;
13   ELSE
14     DBMS_OUTPUT.PUT_LINE('项费率为：' || rate);
15   END IF;
16 EXCEPTION
17   WHEN rate_exception THEN
18     RAISE_APPLICATION_ERROR(-20001, '未指定项费率');
19 END;

View Code

　　7.游标

　　7.1隐式游标：

在程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标

但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT ... INTO ...。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数
SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

　　7.2显示游标

}声明游标：CURSOR ..IS select..

}打开游标：OPEN

}结果集控制：FETCH..INTO..

}关闭游标：CLOSE

注意：使用for循环来遍历游标是游标的简洁使用形式。

　　　同时还可以使用绑定变量的方式声明游标可以达到重复使用的目的，提高游表性能；

　　7.3 ref 游标

ref 游标专门为动态sql语句设计的

声明强类型的ref游标

TYPE my_curtype IS REF CURSOR

RETURN stud_det%ROWTYPE;

order_cur my_curtype;

 1 declare 
 2  type cur_type is ref cursor;
 3  cur cur_type;
 4  rec scott.emp%rowtype;
 5  str varchar2(50);
 6  letter char:= 'A';
 7 begin
 8          loop          
 9           str:= 'select ename from emp where ename like ''%'||letter||'%''';
10           open cur for str;
11           dbms_output.put_line('包含字母'||letter||'的名字：');
12           loop
13            fetch cur into rec.ename;
14            exit when cur%notfound;
15            dbms_output.put_line(rec.ename);
16 end loop;
17   exit when letter='Z';
18   letter:=chr(ascii(letter)+1);
19  end loop;
20 end;
21 
22 
23 包含字母A的名字：
24 ALLEN
25 WARD
26 MARTIN
27 BLAKE
28 CLARK
29 ADAMS
30 JAMES
31 包含字母B的名字：
32 BLAKE
33 包含字母C的名字：
34 CLARK
35 SCOTT

View Code

　　8.锁的简单使用

　　8.1当修改数据前可以使用select ....for update　　这样加上了一个行级排他锁，确保其他用户不能进行修改，但不会阻碍读。

行级排他锁：insert ,delete,update和select for update.

直到使用commit和rollback时会释放行级锁；

　　SELECT … FOR UPDATE语法:

　SELECT … FOR UPDATE [OF columns]
[WAIT n | NOWAIT]　[skip locked]

说明:　OF 子句用于指定即将更新的列，即锁定行上的特定列，多表关联使用。
　WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待

　skip locked 不提示错误，申请的资源被锁定时放弃申请；

　　9.oracle事务中注意的地方：

}在Oracle中，执行DDL语句(如Create Table、Create View等)时，会在执行之前自动发出一个Commit命令，并在随后发出一个Commit或者Rollback命令，也就是说，DDL会象如下伪码一样执行：

}

}Commit;

DDL_Statement;

If (Error) then

Rollback;

Else

Commit;

End if;

　　10.函数中注意事项：

q定义函数的限制：

q函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数

q形参不能是 PL/SQL 类型

q函数的返回类型也必须是数据库类型

q访问函数的两种方式：

q使用 PL/SQL 块

q使用 SQL 语句

　　11.触发器介绍

常用触发器为行级触发器，使用before或者after关联dml语句；

用于审计的ddl语句级触发器：如deleting，inserting，updating

用于审计的数据库级触发器：create or replace trigger trg_session before logoff on database

用于对于某些不能修改的视图操作的触发器，将对视图的操作转换为对表的操作：instead of

posted on 2013-09-06 14:48 a_badegg 阅读(370) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

pl sql练习（4）

导航

公告