oracle第七天

 

处理隐式游标

主要用于查询语句的处理，尤其是在查询结果为多条记录的非查询语句，如修改删除操作，则由oracle系统自动为这些操作设置其工作区。这些由系统隐含创建的游标称为隐式游标。

 

insert update delect select 语句总不必明确定义游标。

 

Sql%found  sql%notfound sql%rowcount sql%isopen

 

declare vdeptno empdeptno%type:=&deptno;

       begin

       delete from emp where deptno=vdeptno;

       if sql%notfound then

       delete from dept where 

游标的修改和删除操作

游标的修改和删除操作是在指定的游标定位下，修改或删除表中值得顶的数据行

下面是两种游标的比较：

begin

    for c2 in （select id from tt) loop

        if c2.id <=100 then

        update tt seet name='a'

        where id=c2.id;//这里会进行全表扫描,这里无论哪里的where都全表扫描

        elsif c1.id<=200 then

        update tt set name='b'

        whree id=c2.id;//这里会进行全表扫描

        else

        null;

      end if

    end;

    优化方法：在id列添加索引

    create index vtt on tt(id);

   

    select /// from /// for update [of col/];

    这样的select不再是简单的select而是变成了DML,会阻塞其他会话的操作。

    select * from tt where id=2 for update;其实这是一个修改命令，但什么都没修改。为了修改而寻找。！！在我找到它修改它之前是要加锁的。

    select * from tt where id=33 for update nowait;

    ddl锁都是nowait锁。

  这样找到后就修改。和上面的例子相比（上面的会进行全表扫描哦）更快。

  if emp.sal<100 then

  update emp set name='dd'

  where current of emp_cursor;

  表中数据大规模修改的话呢，用select  from  for update of ;并建立索引

  当commit的时候oracle会将缓存写到磁盘,也就是写磁盘的操作。

 

  大型数据处理时，没隔三五千行要提交commit

 

  pl/sql所耗用的逻辑读和物理读

  set autot trace

  select * from v$statname where name like'sess';

  select * from v$statname where name like'phys%';

  select * from v$statname a,v$sesstat b where a.statistic#=b.statistic# and

a.name in('session logical reads','physical reads')and sid=999;

 

 

  desc v$lock;

  Name                                      Null?    Type

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

 ADDR                                               RAW(4)

 KADDR                                              RAW(4)

 SID                                                NUMBER

 TYPE                                               VARCHAR2(2)

 ID1                                                NUMBER

 ID2                                                NUMBER

 LMODE                                              NUMBER

 REQUEST                                            NUMBER

 CTIME                                              NUMBER

 BLOCK                                              NUMBER

 

   select addr,sid,type,lmode from v$lock;

   select distinct type from v$lock;

TY

--

RS

RT

CF

XR

MR

PW

TM

TX  是独占锁

TS

select type,SID,ID1,ID2 from v$lock;

查看tx锁等待

 

共享池里的sql在v$sql表 中。也就是执行计划取sql的地方。或者说是sql池表

SQL> desc v$sql;

 Name                                      Null?    Type

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

 SQL_TEXT                                           VARCHAR2(1000)

 SQL_FULLTEXT                                       CLOB

 SQL_ID                                             VARCHAR2(13)

 SHARABLE_MEM                                       NUMBER

 PERSISTENT_MEM                                     NUMBER

 RUNTIME_MEM                                        NUMBER

 SORTS                                              NUMBER

/

/

/

v$sql.sql_id当前执行sql的值是什么

v$session.sql_id显示当前会话的执行状态。

select a.sql_text from v$sql a,v$session b where a.sql_id=b.sql_id and b.sid=444;

还有列event

 

锁的原理

1、资源结构resource structure

2、锁结构lock structure

TM 表锁

TX 行锁

 

 

异常处理

exception

when   exce   then

when  exce then

when others then

end;

 

预定义异常

too_many_rows

no_data_found

zero_divide

SQL> select 1/0 from dual;

select 1/0 from dual

        *

ERROR at line 1:

ORA-01476: divisor is equal to zero

非预定义异常的处理

预定义的直接：报错 退出

 

myexception exception;

pragma exception_init(myexception,-2292);override the original exception,we use this to alter the procedure

 

在pl/sql中使用sqlcode返回错误编号

,sqlerrm返回错误信息

 

存储函数和过程

建立函数

建立内嵌函数

create or replace function functionname

[argument type,argument type]

return type;

return 的时候只要类型就可以了，不用长度。经常用varchar2,number,char

end functionname;//最后多了一个函数名

 

create or replace function getsalary(deptno number,empcount out number)return number

is vsum number

begin

     select sum(sal),count(*) into vsum,empcount from emp where detnum=deptno;

     return vsum;

     exception

     when no_data_found then

     dbms_output.putlin('data not found');

     when others then

     dbms(///);

与过程相关的数据字典

user_source,all_source,dba_

desc user_source;

select line,text from user_source;

 

SQL> select line ,text from user_source;

 

  LINE

------

TEXT

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

     1

function getrank(n userinfo.logindate%type,m userinfo.paper%type)return varchar2

 is

 

     2

          begin

 

     3

              ranking userinfo.rank%type;

 

  LINE

------

TEXT

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

 

     4

              if sysdate-n <90 and paper<100 then ranking:='????????????';

 

     5

              elsif sysdate-n <365 and paper<300 then ranking:='????????????';

 

     6

              elsif sysdate-n <365*2 and paper<1000 then ranking:='????????????'

 

  LINE

------

TEXT

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

;

 

     7

              elsif sysdate-n <365*4 and paper<3000 then ranking:='????????????'

;

 

     8

              elsif paper<8000 then ranking:='????????????';

 

 

  LINE

------

TEXT

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

     9

              elsif paper<10000 then ranking:='?????????';

 

    10

              elsif paper<10000 then ranking:='???????????????';

 

    11

              endif;

 

 

  LINE

------

TEXT

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

    12

          return ranking;

 

    13

         end;

 

 

13 rows selected.

 

SQL>

 

 

    end getsalary;

 

 

如何使用我们查询出来的function呢？？？

直接根据函数名字来使用

 

argument=>parameter[....]

这样可以不按函数声明的顺序来添加参数，可以自己指定。

function（varchar2，number）function（222=>number,'ddd'=>varchar2)类似这种使用形式，但后面不是varchar2而是参数名

 

rpad(‘abc’,10,’*’);函数意思：在字符串abc后面添加*使总长度达到10

 

 

 

type .....is record(.....);

type tb1 is table of number;定义的一个数组，很多的行，每行类型都一样

vtb1 tb1:=tb1(2,3,4,5,7);一定要初始化，而且初始化的数量决定了，数组的宽度的最大值。

有一个属性count代表数组的宽度。

select into fetch 来进行初始化会很方便

type tb is table of number index by binary_integer;

要特别注意其初始化

可变数组

declare

type tr is record(

id number,cc number);

type tb is table of tr;

vtb tb;这样就定义了一个二维数组。

可以 插入等操作

批量处理的雏形。

vtb(i).id来访问或者操作。

type ta is table of emp%rowtype;

vtab ta;

cursor c is select * from test2;注意游标要关闭哦。

fetch c bulk collect into vtab limit 30;一次抓取30行。

多用于warehouse。

非批量抓取

vtab emp%type;

cursor c is select * from test2;

 fetch c into vtab;

 dbms_out.put_line(vtab.id);

 

 任何 地方都可以使用fetch c bulk collect into vtab limit 4000;

 批量写

fetch c bulk collect into vtab limit 4000;

forall i in 1..vtab.count

insert into tes

exit when c%notfound;这里是没t1 values vatb(i);有loop的 这里批量的，4000条语句一次执行，并不是循环

主要用来数据的迁移，要注意内存的占用，别把内存挤爆。values 后面不需要用()的。

 

自己触发的错误，也就是自己定义的ORA在-20000之后，用户的数据都叫SCHEMA。方案。

所有的DDL都可以放在 before truncate on user1.schema。方案级别的触发器。

还有数据库级别的触发器，DML触发器等等。

数据库关闭之前，启动之后。等等。

表删除了  行级触发器就没有了。

schema的触发器只有用户不存在了 ，触发器才没有。

 

把触发器禁止。

做操作

把触发器打开。

 

alter trigger bef disable;

 

select trigger_name fdrom DBA_TRIGGERS;

 

dba_source里面存放的有你建立的触发器。

 

动态游标；

可以使用ddl；

 

select * from test2 where id=:x;这个变量就做绑定变量。oracle先进行解析，进行软解析和硬解析。

解析完后，可以执行的时候，oracle还不知道：X的值，这个时候就开始绑定。绑定就是把：x替换的掉。

这就是绑定变量。这里的：X是一个占位符。然后执行，抓取，

sql的执行步骤：

1、打开

2、解析

3、绑定

4、执行

5、抓取

 

游标变量

type rc is ref cursor;使用更灵活，每次可以执行不同的sql