Oracle-常用PL/SQL

作者：聂勇 欢迎转载，请保留作者信息并说明文章来源！

• 一、表操作 | Create and alter table
• 二、序列号 | Sequence
• 三、存储过程 | Procedure

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 表名;
sql

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，第一次取值为１，第二次取值为２；如increment by 3，第一次取值为1，第二次取值为4。
• start with 表示序列号初始值。
• maxvalue 表示序列号最大值。
• minvalue 表示序列号最小值。
• cache 表示序列号缓存值，默认为20。适当增大该值有利于提升性能。

2、更改序列号名。

rename 序列号旧名 to 序列号新名;

3、删除序列号。

drop sequence 序列号名;

三、存储过程 | Procedure

１、新建／更改 存储过程。

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;

3、运态绑定参数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;

4、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;

5、存储过程返回结果集（游标）。

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;