注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

0与1构筑世界,程序员创造时代

软件架构设计 Java编程

 
 
 

日志

 
 

Oracle - 常用PL/SQL | Oracle common pl/sql  

2011-06-11 17:06:50|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Oracle是电信级应用中常用的数据库,以稳定性、性能、丰富的功能深受开发人员的青睐。而近几年来,在我负责和参与的项目和产品中,使用最多的就是Oracle。将其中一些最常用的pl/sql记录下来,备忘。

一、表操作 | Create and alter table

1、新建表。
create table SUB_ACTION  (
   SUB_ACTION_ID          NUMBER                            not null,
   STATUS                          NUMBER(2)                       not null,
   PRIORITY                       NUMBER(1),
   PROMPT_CONTENT   VARCHAR2(255),
   CREATE_TIME   DATE,
   constraint PK_SUB_POLICY primary key (SUB_POLICY_ID)
)

2、给表和字段增加注释。
comment on table 表名 is '表注释信息';
comment on column 表名.字段名 is '字段注释信息';

3、更改表名。
rename  旧表名 to 新表名;

4、删除表。
drop table 表名;

5、新增字段(列)。
alter table 表名 add column 字段名 字段定义;

如:
alter table SUB_ACTION add column NEW_COLUMN NUMBER not null;

6、 修改字段(列)
alter table 表名 modify 字段名 字段定义;

如:给字段增加默认值。
alter table SUB_ACTION modify SPLIT_STATUS default 1;

7、删除字段(列)
alter table 表名 drop column 字段名;


二、序列号 | Sequence

1、新建序列号。
create sequence SEQ_SUB_ACTION
increment by 1
start with 1
maxvalue 99999999999999999999999
minvalue 0
cache 100;

其中:
  • increment by 表示序列号增量,默认为1。如:increment by 1,第一次取值为1,第二次取值为2;如increment by 3,第一次取值为1,第二次取值为4。
  • start with 表示序列号初始值。
  • maxvalue 表示序列号最大值。
  • minvalue  表示序列号最小值。
  • cache 表示序列号缓存值,默认为20。适当增大该值有利于提升性能。

2、更改序列号名。
rename 序列号旧名 to 序列号新名;

3、删除序列号。
drop sequence 序列号名;

三、存储过程 | Procedure

1、新建/更改 存储过程。
create or replace procedure 存储过程名(参数名 in 参数类型, 参数名 out 参数类型) is
    变量定义;
begin
    业务代码;
end;


其中:
  • in 表示是输入参数。
  • out 表示是输出参数。

示例:
create or replace procedure WRITE_LOG(p_programaName in varchar2,
                                      p_logDesc
in varchar2,
                                      p_resultCode out number) is
    v_temp number;
begin
    insert into run_log(
prog_name, log_desc) values(p_programaName, p_logDesc);
    commit;
   
p_resultCode := 1;
exception
    when others
       
p_resultCode := -1;
        then return;
end;


2、游标。
procedure ACTION_SPLIT(ActionId in number,
                        FetchNum in number,
                        ResultCode out number) is
      v_udpair_cold_record groupMsisdnUdpair;
      cursor cur_udpairinfo is
             select a.RECORD_ID, b.IMEI, b.MSISDN, b.MANU_ID, b.MODEL_ID, b.SW_VERSION, b.PROV_ID, b.CITY_ID
                    from MSISDNLIST_DETAIL@PLATFORM a, UDPAIR_INFO_COLD b
                    where a.MSISDN_STATUS = 0
                          and a.RECORD_ID > v_progress_record_id
                          and a.RECORD_ID <= (v_progress_record_id + FetchNum)
                          and a.MSISDN = b.MSISDN;
  begin
      open cur_udpairinfo;   -- 打开游标
     
      -- 循环处理游档中的每行记录
      loop
          fetch cur_udpairinfo into v_udpair_cold_record;
          exit when cur_udpairinfo%notfound;   -- 当到达游标末尾时退出循环
         
          insert into ACTION_SPLIT(PK_ID,
                        ACTION_ID,
                        IMEI,
                        MSISDN,
                        MANU_ID,
                        MODEL_ID,
                        SW_VERSION,
                        PROV_ID,
                        CITY_ID,
                        CREATE_TIME)
                 values(SEQ_ACTION_SPLIT.Nextval,
                        ActionId,
                        v_udpair_cold_record.IMEI,
                        v_udpair_cold_record.MSISDN,
                        v_udpair_cold_record.MANU_ID,
                        v_udpair_cold_record.MODEL_ID,
                        v_udpair_cold_record.SW_VERSION,
                        v_udpair_cold_record.PROV_ID,
                        v_udpair_cold_record.CITY_ID,
                        sysdate);
      end loop;
      close cur_udpairinfo;   -- 关闭游标
  end;

2、运态绑定参数SQL。
execute immediate 'update ACTION_GROUP set SPLIT_STATUS = :splitStatus where ACTION_ID = :actionId and GROUP_ID = :groupId'
                  using 2, ActionId, GroupId;

execute immediate 'select min(PK_ID) from TMP_ACTION'||SubPolicyId||' where  CITY_ID='||CityId
                       into v_progress_record_id;


execute immediate 'select count(1) from ACTION_GROUP where ACTION_TYPE=:actionType'
                      into v_count using v_action_type;

3、label(标签)。
  begin
      loop
          begin
              ...... 省略大量代码
             
              if (v_udpair_msisdn is null) then
                  goto label; -- 跳转到标签
              end if;
             
              ...... 省略大量代码
             
          exception
               when NO_DATA_FOUND then
                   NULL;
          end;
         
          <<label>> null;  -- 定义标签
          ResultCode := ResultCode + 1;
         
          ...... 省略大量代码
         
          -- 控制处理数量
          if (ResultCode >= FetchNum) then
              exit;
          end if;
      end loop;
      commit;
  end;

4、存储过程返回结果集(游标)。
create or replace package TypeDefine is
    TYPE ref_cursor IS REF CURSOR;
end;

create or replace procedure QueryActionInfo(p_in_msisdn in number,
                                                   p_out_cur out TypeDefine.ref_cursor ) is
   
begin
    open p_out_cur for select msisdn, city_id, prov_id, status from ACTION_GROUP where msisdn=p_in_msisdn;
end;



<正文结束> 

  文章声明

作者:傲风(aofengblog@163.com)       编写时间:2011年06月11日

网址:http://aofengblog.blog.163.com

作者保留所有权利,转载请保留文章全部内容!


  评论这张
 
阅读(1337)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017