oracle undo 解析

  声明一下:关于oracle的文章基于boobooke小布老师视频,在我学习的过程中,每有体会拿来分享,虽然从理解到整理分享很耗时,但我想这样的学习是扎实的。

Undo是干嘛用的?                                        

  在介绍undo之前先说一下另外一个东西 transaction ,翻译成交易或事务。我们在进行一个事务的过程中需要申请许多资源,一个复杂的事务也需要很多步来完成。那么一个复杂的事务是只有两个结果,要么成功,要么失败(相当于从来没发生过)。

  一个很典型的列子,银行转账,其实其需要两步操作,第一步先将你账户上的钱减去,第二步把被转账户的钱加上,(先减后加,出了问题银行不吃亏。呵呵!)这样就是一个完整的事务。如果执行了一半,你的钱减了,被转账户的钱没加上,这个时候事务就要回滚,回滚到原始状态。也就是在转账之前,需要先记录你和被转账户上的金额。这就样能保证,一旦事务失败就回滚到事务的发生之前的状态。

  为那保证一个事务的原始性和完整性,就引这入undo 的概念。Undo就是用来记录保存事务操作过程中的数据,如果事务发生错误,可以之前的数据进行填补。

Undo Segment 还原段:

  从来面的视图,我们就可以很清晰的看出,我们要对表(table)中的一个数据进行修改,在修改之前,先把老的映像(old image)放到undo 上面。然后再在table中放入new image 。假如过程失败,我们还可以把undo 上的old image 再拿回来放在原先的位置,从而使这件事儿看起来像没发生过一样。

  Undo segment 是保存在表空间上的。Undo 大小是固定的,既然是固定的也就是有限的。如果保存的记录非常多,那么它就会被占满,新记录的数据会覆盖掉最早的数据。所以用一个圆形的盘片能更加形象的表示。数据从一个位置开始写入,当写满一圈后,最新的数据就会覆盖最早写入的数据。

 

 

undo可以做哪些事儿?                                        

 

  Transaction rollback  事务反转, Transaction recovery 事务恢复。事务反转与事务恢复的效果是一样的。事务反转是人主动去做的,人在操作的过程中反悔了,相当于我在写文档时按个ctrl+z 。事务恢复是机器自动完成的,比如在事务进行过程中简拼突然断电了,下次重启服务之后,系统自动回滚。

  Read consistency  读一执行。读一致性对于多用户操作是非常重要的。如果你在多人开发中使用过版本控制工具(git\cvs\svn)的话,下面的概念你将很容易理解。

 

Oracle 读一致性概念                                      

 

  我知道oracle允许可以由多个用户对数据库进行操作,当你执行一个查询几百万条记录的操作时,这个过程可能需要几分钟。在这个过程中其它用户对你查询的数据时行了修改。这里就要保证你查询的结果是被修改之前的。

  这里明确一个概念,事务的开始,在我们执行一条(更新、修改、删除)语句时;事务的结束,必须执行提交动作(执行commit 或 rollback 命令

 

  我们从上面的流程图来理解一下oracle是如何保证读一致性的。

  当我们执行一个事务的时候,oracle会分配一个SCN编号,这个编号是递增的。下一个事务的编号一定比当前事务的编号大。上图中执行第一个事务分配的编号为10023,在这个事务执行的过程中,另一事务对SCN 编号为1000810021的数据块进行了修改。用来替换的数据块SCN编号为10024 ,而被替换掉的数据块会被保存到undo 上面。当第一个事务执行到被修改过的数据块时,发现1002410023大,这个时候就会到undo segment上找比自己SCN号小的数据块进行读,于是发找到了SCN号为1000810021两个块。这样就有效的保证了读一致性。

  当然,会有一种特殊情况,也就是undo segment 太小,最多放100条数据,可一下子来了120条数据,那么最先写入的20条数据被最后写入的20条数据覆盖。这个时候就会报错,这也是为什么要对数据据进行调优的原因。

 

 

Redo or Undo                                                                      

什么是Redo

Redo记录transaction logs,分为onlinearchived。以恢复为目的。

比如,机器停电,那么在重起之后需要online redo logs去恢复系统到失败点。

比如,磁盘坏了,需要用archived redo logsonline redo logs区恢复数据。

什么是Undo

  Redo 是为了重新实现你的操作,而Undo相反,是为了撤销你做的操作。Undo更像常使用的ctrl+z ,撤销到上一步的状态。而Redo 是也就会记录undo 的操作。

  当我们插入一条数据时,首先这个动作会被记录到redo log 中,操作也会被记录到到undo undo本身的动作也会做为一条数据被记录到redo log ,插入一条数据,索引(indexes)会发生变化,索引的变化也会做一条数据被记录到redo log Redo 记录着一个操作所有相关的信息,这样才能完整的保证场景的重现。

  需要说明的是在上面的图表中,不管是undo 还是redo都是写在内存里的,一旦断电,所有信息将会消失。只有写到磁盘上的信息才不会因为断电而消失。是redo log 的信息先写到磁盘上的,因为它的信息最全面,可以完整的保证场景重现。我们可以通过各种机制控制redo 写磁盘,比如每隔3秒写一次,或redo log文件大于1MB写一次。

 

 

如何配置使用undo ?                                                                 

  要想使用undo 首先需要创建undo表空间。我们可以创建多个undo表空间,但只能有一个undo表空间是处于使用状态。

查看undo配置信息:

SQL> show parameter undo

NAME TYPE VALUE
———————————— —————————————————-
undo_management
string AUTO
undo_retention integer
900
undo_tablespace
string UNDOTBS1

 

Undo配置参数含义

-DNDO_MANAGEMENT     undo的管理模式,分自动和手动

-UNDO_TABLESPACE      当前正在被使用的undo

-UNDO_RETENTION        规定多长时间内,数据不能被覆盖。

—————————————–

AUTO             表示undo 为自动管理模式。

900               表示在900秒内,undo上的数据不能被覆盖。

UNDOTBS1    是当前正在使用的undo表空间。

 

创建undo表空间,与创建一般的表空间类似,命令如下:

SQL> create undo tablespace myundotbs
2 datafile /ora10/product/oradata/ora10/myundotbs1.dbf size 10M;

Tablespace created.

查看新创建的undo表空间

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS
———————————————————— ——————
SYSTEM PERMANENT
UNDOTBS1 UNDO   //老的undo表空间
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
PAUL PERMANENT
MYUNDOTBS UNDO // 新创建的undo表空间

SQL> show parameter undo 再次查看当前使用的表空间

NAME TYPE VALUE
———————————— —————————————————-
undo_management
string AUTO
undo_retention integer
900
undo_tablespace
string UNDOTBS1

切换undo表空间:

SQL> alter system set undo_tablespace=myundotbs;

System altered.
SQL> show parameter undo 再次查看当前使用的表空间

NAME TYPE VALUE
———————————— —————————————————-
undo_management
string AUTO
undo_retention integer
900
undo_tablespace
string MYUNDOTBS //已经切换的了undo表空间

 

删除undo表空间:

SQL> drop tablespace myundotbs;
Tablespace dropped.

Drop一个undo表空间后,在磁盘上还是存在的,我们需要通过操作系统层面用rm命令将文件删除。

  思考:

  表空间的切换、删除命令非常简单,但这里我们需要思考一下实际切换场景。当我们执行一个事务时,事务执行了一半还没有提交,这个时候进行切换undo表空间是否可以成功理论上undo表空间正在使用中,是不允许切换的。但实际上undo表空间在使用中是可以切换的,但切换之后立刻删除,系统会提示错误。把事务提交后再删除,系统依然提示错误。这里只有将替换掉的undo表空间切换到使用状态,再切换到废弃状态才能被删除。上面的情况,有兴趣有同学可以验证一下。

Undo调优                                             

  Undo的设置取决于我们实际的生产系统。如何设置undo更合理地为我们工作呢?

Undo表空间的大小

  我们在创建一个undo表空间的使用,就指定了它的大小,这个大小一旦创建是不可变更的。设置过大,是一种浪费,设置过小,例如删除100万条记录,这些删除的记录都要临时存放到undo表空间中,如果undo的大小不能存储100万条记录,那么就会出问题。

Undo数据的存放时间

  也就是undo_retention 参数所对应的时间,undo上有数据存放时间与undo大小的密切关系。存放时间越长,需要的表空间越大。就像理发师的数量与理发师的效率的关系一样。理发师效率很高,一秒钟解决一个客户,那么就不需要太多的理发师傅。

Undo表空间的历史信息

如何合理设置undo表空间的大小和存放时间呢?那么就需要参考历史记录

  这个数据每隔10分钟采集一次,结束时间减去开始时间,在这段时间内使用了多少个undo数据块。

计算每秒钟使用数据块的多少?

求最大值:

SQL> select max(undoblks / ((end_time-begin_time)*24*3600)) from v$ undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
———————————————
14.15833333

求平均值:
SQL> select sum(undoblks)/sum((end_time – begin_time)*24*3600) from v$ undostat;

SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*24*3600)
————————————————
4.122282389

 

oracle 存储过程示例

CREATE OR REPLACE PROCEDURE PROC_CITY_DATE2T_CITY_ORDERS IS
  l_maxOper_date date;
  l_maxId        number;
  l_errorcode   varchar2(100);

BEGIN

  -- author sunchengliang

  SELECT max(oper_date) into l_maxOper_date FROM city_day;
  select count(cityid) into l_maxId from t_city_orders;

  --插入
  INSERT INTO t_city_orders
    (oper_date,
     stat_type,
     cityid,
     city,
     excellent_days,
     fine_days,
     nogood_days,
     orders,
     l_excellent_days,
     l_fine_days,
     l_nogood_days,
     l_orders,
     r_excellent_days,
     r_fine_days,
     r_nogood_days,
     r_orders,
     oper_time)
    SELECT l_maxOper_date,
           '30天内',
           l_maxId + 1,
           aaa.city,
           aaa.excellent_days,
           aaa.fine_days,
           aaa.nogood_days,
           aaa.orders,
           ccc.l_excellent_days,
           ccc.l_fine_days,
           ccc.l_nogood_days,
           ccc.l_orders,
           bbb.r_excellent_days,
           bbb.r_fine_days,
           bbb.r_nogood_days,
           bbb.r_orders,
           sysdate
      FROM (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.excellent_days DESC) orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN TRUNC(l_maxOper_date) - 30 AND
                           TRUNC(l_maxOper_date)
                     group by city) aa) aaa,
           (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.r_excellent_days DESC) r_orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) r_excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) r_fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) r_nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN
                           ADD_MONTHS(TRUNC(l_maxOper_date), -1) - 30 AND
                           ADD_MONTHS(TRUNC(l_maxOper_date), -1)
                     group by city) aa) bbb,
           (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.l_excellent_days DESC) l_orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) l_excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) l_fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) l_nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN
                           ADD_MONTHS(TRUNC(l_maxOper_date), -12) - 30 AND
                           ADD_MONTHS(TRUNC(l_maxOper_date), -12)
                     group by city) aa) ccc
     WHERE aaa.city = bbb.city(+)
       AND aaa.city = ccc.city(+);
     Commit;
  exception
  When Others Then
   l_errorcode := sqlerrm;
  Rollback;
END;

create or replace procedure PROC_TO_T_PARAMETER is

  l_airBestCity  varchar2(50);
  l_currentDay   date;
  l_air30daysI   varchar2(100);
  l_air30daysII  varchar2(100);
  l_air365daysI  varchar2(100);
  l_air365daysII varchar2(100);
  l_errorcode    varchar2(100);

begin
  -- author sunchengliang
  --当天时间
  select max(tt.oper_date) into l_currentDay from city_day tt;

  --当天空气质量最好的城市
  select t.city
    into l_airBestCity
    from city_day t
   where t.oper_date = l_currentDay
     and t.pollution_indeces =
         (select min(ttt.pollution_indeces)
            from city_day ttt
           where ttt.oper_date = l_currentDay);

  --最近30天内一级天数最多的城市
  select tt.city
    into l_air30daysI
    from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 30
             and t.oper_date < l_currentDay
           group by t.city
           order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 30
                         and t.oper_date < l_currentDay
                       group by t.city)
     and rownum = 1;
  --最近30天内二级天数最多的城市
  select tt.city
    into l_air30daysII
    from (select t.city, sum(decode(grade, 'Ⅱ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 30
             and t.oper_date < l_currentDay
           group by t.city
           order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, 'Ⅱ', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 30
                         and t.oper_date < l_currentDay
                       group by t.city)
     and rownum = 1;
  --最近1年内一级天数最多的城市
  select tt.city
    into l_air365daysI
    from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 365
             and t.oper_date < l_currentDay
           group by t.city
           order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 365
                         and t.oper_date < l_currentDay
                       group by t.city)
     and rownum = 1;
  --最近1年内二级天数最多的城市
  select tt.city
    into l_air365daysII
    from (select t.city, sum(decode(grade, 'Ⅱ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 365
             and t.oper_date < l_currentDay
           group by t.city
           order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, 'Ⅱ', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 365
                         and t.oper_date < l_currentDay
                       group by t.city)
     and rownum = 1;
  --更新到db
  update T_PARAMETER t
     set t.p_content = '今天空气量最好的城市是:' ¦¦ l_airBestCity ¦¦ '#' ¦¦
                       '最近30天内空气质量一级天数最多的城市:' ¦¦ l_air30daysI ¦¦ '#' ¦¦
                       '二级天数最多的城市:' ¦¦ l_air30daysII ¦¦ '#' ¦¦
                       '最近1年内空气质量一级天数最多的城市:' ¦¦ l_air365daysI ¦¦ '#' ¦¦
                       '二级天数最多的城市:' ¦¦ l_air365daysII
   where t.p_type = '1';
  Commit;
exception
  When Others Then
    l_errorcode := sqlerrm;
    Rollback;

end PROC_TO_T_PARAMETER;

CREATE   OR   REPLACE   PROCEDURE   WEBPM_SI_BROWSE 
    ( 
                  is_ulp                                             in         varchar2, 

                  os_seq                                             out       varchar2, 
                  os_si_code                                     out       varchar2, 
                  os_si_name                                     out       varchar2, 
                  os_unit_name                                 out       varchar2, 
                  os_ulp                                             out       varchar2, 
                  os_office_tel                               out       varchar2, 
                  os_fax                                             out       varchar2, 
                  os_address                                     out       varchar2, 
                  os_cont_type                                 out       varchar2, 
                  os_frame                                         out       varchar2, 
                  os_add_frame                                 out       varchar2, 
                  os_add_item                                   out       varchar2, 
                  os_sign_date                                 out       varchar2, 
                  os_contact_person                       out       varchar2, 
                  os_contact_tel                             out       varchar2, 
                  os_contact_msn                             out       varchar2, 
                  os_e_mail                                       out       varchar2, 
                  os_post_code                                 out       varchar2, 
                  os_eff_date                                   out       varchar2, 
                  os_arg_no                                       out       varchar2, 
                  os_year_flag                                 out       varchar2, 
                  os_es_prop                                     out       varchar2, 
                  os_ms_prop                                     out       varchar2, 
                  os_icspe_flag                               out       varchar2, 
                  os_icspe_list                               out       varchar2, 
                  os_icspe_amount                           out       varchar2, 
                  os_reason                                       out       varchar2, 
                  os_ms_set_mode                             out       varchar2, 
                  os_es_set_mode                             out       varchar2, 
                  os_register_man                           out       varchar2, 
                  os_register_date                         out       varchar2, 
                  os_remarks                                     out       varchar2, 
                  os_status                                       out       varchar2,         --   16   返回值 
                  os_result                                       out       varchar2           --   17   返回提示 
        ) 
      IS 

      ll_os_office_tel                           number; 
      ll_os_fax                                         number; 

      ll_os_sign_date                             date; 
      ll_os_eff_date                               date; 
      ll_os_icspe_amount                       number; 
      ll_os_register_date                     date; 

begin 

      begin 
          if   is_ulp   is   null   then 
              os_status   := '110 '; 
              os_result   :=   '单位法人不能为空 '; 
              return; 
          end   if; 
      end; 

                        os_status                           := '100 '; 
                        os_result                           := '开始查询局向信息 '   ; 
                        begin 
                          select 
                                seq                         , 
                                si_code                 , 
                                si_name                 , 
                                unit_name             , 
                                ulp                         , 
                                office_tel           , 
                                fax                         , 
                                address                 , 
                                cont_type             , 
                                frame                     , 
                                add_frame             , 
                                add_item               , 
                                sign_date             , 
                                contact_person   , 
                                contact_tel         , 
                                contact_msn         , 
                                e_mail                   , 
                                post_code             , 
                                eff_date               , 
                                arg_no                   , 
                                year_flag             , 
                                es_prop                 , 
                                ms_prop                 , 
                                icspe_flag           , 
                                icspe_list           , 
                                icspe_amount       , 
                                reason                   , 
                                ms_set_mode         , 
                                es_set_mode         , 
                                register_man       , 
                                register_date     , 
                                remarks 

                                into 

                                os_seq                                     , 
                                os_si_code                             , 
                                os_si_name                             , 
                                os_unit_name                         , 
                                os_ulp                                     , 
                                ll_os_office_tel                       , 
                                ll_os_fax                                     , 
                                os_address                             , 
                                os_cont_type                         , 
                                os_frame                                 , 
                                os_add_frame                         , 
                                os_add_item                           , 
                                ll_os_sign_date                         , 
                                os_contact_person               , 
                                os_contact_tel                     , 
                                os_contact_msn                   , 
                                os_e_mail                             , 
                                os_post_code                         , 
                                ll_os_eff_date                           , 
                                os_arg_no                               , 
                                os_year_flag                         , 
                                os_es_prop                             , 
                                os_ms_prop                           , 
                                os_icspe_flag                     , 
                                os_icspe_list                     , 
                                ll_os_icspe_amount                 , 
                                os_reason                             , 
                                os_ms_set_mode                   , 
                                os_es_set_mode                   , 
                                os_register_man                 , 
                                ll_os_register_date               , 
                                os_remarks 
                                from   WEBPM_SI   where   ulp   =   trim(is_ulp)   and   rownum=1; 
                                        os_status                           := '100 '; 
                                        os_result                           := 'hhh成功查询集成商信息!   集成商名称: '¦¦is_ulp; 
                        exception 
                                    when   others   then 
                                              os_status     := '110 '; 
                                              os_result     := '无此集成商信息!   '¦¦   is_ulp   ¦¦ '     '¦¦sqlerrm   ; 
                                              rollback; 
                        end; 
                      /*输出时间*/ 
          begin 
                        os_sign_date   :=     to_char(ll_os_sign_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误!ll_os_sign_date= '¦¦ll_os_sign_date¦¦ '     '¦¦sqlerrm   ; 
                                          return; 
          end; 
          /*   集成商编码   */ 
          begin 
                os_office_tel:=to_number(ll_os_office_tel); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_office_tel=   '¦¦ll_os_office_tel; 
          end; 
          begin 
                os_fax:=to_number(ll_os_fax); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_fax=   '¦¦ll_os_fax; 
          end; 
          begin 
                os_icspe_amount:=to_number(ll_os_icspe_amount); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_icspe_amount=   '¦¦ll_os_icspe_amount; 
          end; 
          begin 
                        os_eff_date   :=     to_char(ll_os_eff_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误!ll_os_eff_date= '¦¦ll_os_eff_date¦¦ '     '¦¦sqlerrm   ; 
                                          return; 
          end; 
          begin 
                        os_register_date   :=     to_char(ll_os_register_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误!ll_os_register_date= '¦¦ll_os_register_date¦¦ '     '¦¦sqlerrm   ; 
                                          return; 
          end; 

exception 
        when   others   then 
                return   ; 
end;   --   procedure

 

oracle中存储过程procedure的异常处理

先帖上开发时写的一段存储过程

create or replace procedure PROC_TO_T_PARAMETER is 

  l_airBestCity varchar2(50);
  l_currentDay  date;
  l_air30daysI  varchar2(100);
  l_errorcode   varchar2(100); 

begin
  -- author sunchengliang
  --当天时间
  select max(tt.oper_date) into l_currentDay from city_day tt; 

  --当天空气质量最好的城市
  select t.city
    into l_airBestCity
    from city_day t
   where t.oper_date = l_currentDay
     and t.pollution_indeces =
         (select min(ttt.pollution_indeces)
            from city_day ttt
           where ttt.oper_date = l_currentDay); 

  --30内一级天数最多的城市
     select tt.city
    into l_air30daysI
    from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 30
             and t.oper_date  < l_currentDay
           group by t.city
           order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 30
                         and t.oper_date  < l_currentDay
                       group by t.city) and rownum=1;
  --更新到db
  update T_PARAMETER t
     set t.p_content = '今天空气量最好的城市是:'  ¦ ¦ l_airBestCity  ¦ ¦ '#'  ¦ ¦
                       '最近30天内空气质量一级天数最多的城市:' ¦ ¦l_air30daysI
   where t.p_type = '1';
  Commit;
exception
  When Others Then
    l_errorcode := sqlerrm;
    Rollback; 

end PROC_TO_T_PARAMETER;

请注意格式

create or replace procedure PROC_TO_T_PARAMETER is
--your temporary variables
   ....
begin
--your sql here
  ...
  commit;
excption
     When Others Then
     l_errorcode := sqlerrm;
     Rollback;
end;

当sql语句出现异常时,就会跳到exception代码段,sqlerrm会显示出错误信息.也可以把它赋给out参数,返回给用户。并回滚。如果sql没有异常,commit,完成操作.

循环($ROWTYPE,%TYPE,SQL%ROWCOUNT….)

create or replace procedure insert_student
is
i number:=0;
begin
while i<=5000 loop
insert into student values(student_seq.nextval,’bbb’,'d’,’1236547′);
i:=i+1;
end loop;
commit;
end;

begin
insert_student;
end;

create or replace procedure hire_date(
empid in number
)
is
name emp.ename%TYPE;
sal  emp.sal%TYPE;
begin
select ename,sal
into name,sal
from emp
where empno=empid;
dbms_output.put_line(name);
dbms_output.put_line(sal);
end;

begin
hire_date(7876);
end;

 create or replace procedure hire_date1(
empid in number
)
is
emp_record emp%ROWTYPE;
begin
select *
into emp_record
from emp
where empno=empid;
dbms_output.put_line(emp_record.ename);
dbms_output.put_line(emp_record.sal);
dbms_output.put_line(emp_record.empno);
end;

create or replace procedure rowcount (
empid number
)
is
delenum number;
begin
delete emp
where empno=empid;
delenum:=SQL%ROWCOUNT;
commit;
dbms_output.put_line(delenum);
end;

set serveroutput on;
begin
rowcount(7369);
end;

begin
insert into student values(student_seq.nextval,’a',’m',’1223565′);
savepoint a;
insert into student values(student_seq.nextval,’a',’m',’1223565′);
savepoint b;
insert into student values(student_seq.nextval,’a',’m',’1223565′);
savepoint c;
insert into student values(student_seq.nextval,’a',’m',’1223565′);
savepoint d;
rollback to savaepoint b;
commit;
end;

begin
insert into student values(0000,’a',’m',’1223565′);
savepoint a;
insert into student values(1111,’a',’m',’1223565′);
savepoint b;
insert into student values(2222,’a',’m',’1223565′);
savepoint c;
insert into student values(3333,’a',’m',’1223565′);
savepoint d;
rollback to savaepoint b;
commit;
end;

set serveroutput on;
declare
i number:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when i>=20;
end loop;
end;

set serveroutput on;
declare
i number:=0;
begin
for i in 1..10  loop
dbms_output.put_line(i);
end loop;
end;

set serveroutput on;
begin
for i in 1..10  loop
dbms_output.put_line(i);
end loop;
end;

windows环境下的oracle服务

:SID – 数据库标识
HOME_NAME – Oracle Home名称,如OraHome92、OraHome81
(1)OracleServiceSID
数据库服务,这个服务会自动地启动和停止数据库。如果安装了一个数据库,它的缺省启动类型为自动。服务进程为ORACLE.EXE,参数文件initSID.ora,日志文件SIDALRT.log,控制台SVRMGRL.EXE、SQLPLUS.EXE。

(2)OracleHOME_NAMETNSListener
监 听器服务,服务只有在数据库需要远程访问时才需要(无论是通过另外一台主机还是在本地通过 SQL*Net 网络协议都属于远程访问),不用这个服务就可以访问本地数据库,它的缺省启动类型为自动。服务进程为TNSLSNR.EXE,参数文件 Listener.ora,日志文件listener.log,控制台LSNRCTL.EXE,默认端口1521、1526。

(3)OracleHOME_NAMEAgent
OEM代理服务,接收和响应来自OEM控制台的任务和事件请求,只有使用OEM管理数据库时才需要,它的缺省启动类型为自动。服务进程为DBSNMP.EXE,参数文件snmp_rw.ora,日志文件nmi.log,控制台LSNRCTL.EXE,默认端口1748。

(4)OracleHOME_NAMEClientCache
名 字缓存服务,服务缓存用于连接远程数据库的Oracle Names 数据。它的缺省启动类型是手动。然而,除非有一台Oracle Names 服务器,否则没有必要运行这个服务。服务进程为ONRSD.EXE,参数文件NAMES.ORA,日志文件ONRSD.LOG,控制台 NAMESCTL.EXE。

(5)OracleHOME_NAMECMAdmin
连 接管理服务,是构建Connection Manager服务器所用,只有服务器作为Connection Manager才需要,它的缺省启动类型是手动。服务进程为CMADMIN.EXE,参数文件CMAN.ORA,日志文件CMADM_PID.TRC,控 制台CMCTL.EXE,默认端口1830。

(6)OracleHOME_NAMECMan
连 接网关服务,是构建Connection Manager服务器所用,只有服务器作为Connection Manager才需要,它的缺省启动类型是手动。服务进程为CMGW.EXE,参数文件CMAN.ORA,日志文件CMAN_PID.TRC,控制台 CMCTL.EXE,默认端口1630。

(7)OracleHOME_NAMEDataGatherer
性能包数据采集服务,除 非使用Oracle Capacity Planner 和 Oracle Performance Manager,否则不需要启动,它的缺省启动类型是手动。服务进程为VPPDC.EXE,日志文件alert_dg.log,控制台 vppcntl.exe。

(8)OracleHOME_NAMEHTTPServer
Oracle提供的WEB服务器,一般情况 下我们只用它来访问Oracle Apache 目录下的Web 页面,比如说JSP 或者modplsql 页面。除非你使用它作为你的HTTP服务,否则不需要启动(若启动它会接管IIS的服务),它的缺省启动类型是手动。服务进程为APACHE.EXE,参 数文件httpd.conf,默认端口80。

(9)OracleHOME_NAMEPagingServer
通过一个使用调制解调器的数字传呼机或者电子邮件发出警告(没试过),它的缺省启动类型是手动。服务进程PAGNTSRV.EXE,日志文件paging.log。

(10)OracleHOME_NAMENames
Oracle Names服务,只有服务器作为Names Server才需要,它的缺省启动类型是手动。服务进程NAMES.EXE,参数文件NAMES.ORA,日志文件NAMES.LOG,控制台NAMESCTL.EXE,默认端口1575。

(11)OracleSNMPPeerMasterAgent
SNMP服务代理,用于支持SNMP的网管软件对服务器的管理,除非你使用网管工具监控数据库的情况,否则不需要启动,它的缺省启动类型是手动。服务进程为AGNTSVC.EXE,参数文件MASTER.CFG,默认端口161。

(12)OracleSNMPPeerEncapsulater
SNMP协议封装服务,用于SNMP协议转换,除非你使用一个不兼容的SNMP代理服务,否则不需要启动,它的缺省启动类型是手动。服务进程为ENCSVC.EXE,参数文件ENCAPS.CFG,默认端口1161。

(13)OracleHOME_NAMEManagementServer
OEM管理服务,使用OEM时需要,它的缺省启动类型是手动。服务进程为OMSNTSVR.EXE,日志文件oms.nohup。

非等值连接,外连接,自连接,子查询

非等值连接:

select empno,ename,sal,grade from emp,salgrade where sal between salgrade.losal and salgrade.hisal;

外连接:

 select * from emp,dept where dept.deptno=emp.deptno(+); 除了部门号相同的外,还加上了emp中没有而dept中包含的部门号deptno,也就是说扩展了emp表的deptno字段.

自连接:

比方要查找某员工ename的上司mgr:

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

查询按员工号排列的5-8次序的员工

select * from (select rownum id,empno,ename,sal from emp order by empno) where id between 5 and 8;

Oracle中的sql语句

要练好基本工,我不想工作的时候写个简单的查询语句再翻课本哟.

create table test(Name varchar2(16),Age number(3),Birth date)
select * from test
select * from emp
select ename,sal,sal*12 + 100 from emp
select ename ||’ work at ‘ || job from emp
select ename||null||job from emp
select comm*10 from emp
select ename,sal*12 “年薪” from emp
select ename,’ Today is ‘||Sysdate “日期” from emp
select * from emp
select distinct sal from emp
select distinct deptno from emp
select ename,job,deptno from emp where ename=’KING’
select ename,sal,comm from emp where sal<=1500
select empno,ename,sal,mgr from emp where mgr in(7902,7698,7566)
select ename from emp where ename like ‘%A%’
select lower(ename) from emp where lower(ename) like ‘%a%’
select lower(ename) from emp
select * from test
desc test

insert into test(name,age,birth) values(‘suncheng_hong’,
25,sysdate)
select * from test
delete test
insert into test(name,age,birth) values(‘suncheng_HONG’,
25,sysdate)
SELECT * FROM TEST
select name from test where lower(name)=’suncheng_hong’
select * from test where lower(name)=’suncheng_hong’
select ename,sal,comm from emp where sal between 1100 and 1500
select * from emp where ename like ‘_A%’
select * from emp
select * from emp where ename is null
select * from emp where comm is null
select * from emp where sal>1000 and job=’CLERK’
select * from emp where job not in (‘CLERK’,'ANALYST’)
SELECT * FROM emp where (job =’SALESMAN’ OR JOB = ‘PRESIDENT’) AND SAL>1500
SELECT * FROM emp where job =’SALESMAN’ OR JOB = ‘PRESIDENT’ AND SAL>1500
select * from emp order by deptno desc,sal asc
select sal*12 annsal from emp order by annsal
select replace(‘oracle Sal’,'aaa’) test from dual
select * from test

就到这里吧,有点累了,没有什么技术成分,我还要是打一打,练熟一些,心里踏实.