转载

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来访问

  1. SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
  2. SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功  
  3. SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
  4. 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

转载于:https://www.cnblogs.com/charlie-badegg/p/3305440.html

正文到此结束
本文目录