pl/sql笔记

1.对于select出的字符串型数据，在于常量字符串对比之前应对其仅向相关处理，如（trim等）。

如下：

for per_msg_rec in per_msg_cur(pf_pri_rec.portfolio_seq,                               I_TRANS_DATE,                               per_security_rec.security_id) loop                    case trim(per_msg_rec.ivt)                      when 'IVT_BO' then                        IVT_BO_VAR := per_msg_rec.mount;                                            when 'IVT_CR_B' then                        IVT_CR_B_VAR := per_msg_rec.mount;                    end case; 

若没有对 游标的ivt进行处理，则可能出现与整个case中的数据全部不匹配的情况。

2.把两个cursor整合为一个。

例如：可以把

    cursor per_security_cur1(v_portfolio  inventory_detail.portfolio_seq%TYPE,                            v_trans_date inventory_detail.trans_date%type,                            v_security   inventory_detail.security_id%TYPE) is      select distinct d.security_id        from inventory_detail d       where d.trans_date <= v_trans_date         and d.portfolio_seq = v_portfolio         and v_security = '-1';

和

    cursor per_security_cur2(y_detail.security_id%TYPE) is      select v_security        from dual       where v_security <> '-1'       order by security_id;

整合为  

cursor per_security_cur(v_portfolio  inventory_detail.portfolio_seq%TYPE,                            v_trans_date inventory_detail.trans_date%type,                            v_security   inventory_detail.security_id%TYPE) is      select distinct d.security_id        from inventory_detail d       where d.trans_date <= v_trans_date         and d.portfolio_seq = v_portfolio         and v_security = '-1'      union      select v_security        from dual       where v_security <> '-1'       order by security_id;

 3.对于单个的select查询语句，取值查询之前，应先检查是否存在记录，对于游标而言，当没取到记录时，会退出此游标。

例如：

            select sum(nvl(case d.mult                             when 1 then                              d.var_amount                             when -1 then                              -d.var_amount                             else                              d.var_amount * d.mult                           end,                           0))              into AMOUNT_VAR              from inventory_detail d             where d.trans_date <= I_TRANS_DATE               and d.portfolio_seq = per_list_rec.message               and d.security_id = per_security_rec.security_id               and d.ivt in ('IVT_BO', 'IVT_OR_S', 'IVT_CR_S');

当不存在符合条件的记录时，AMOUNT_VAR的值被置为NULL.

后面任何对AMOUNT_VAR的操作都没有意义。如：

            if (AMOUNT_VAR < 0)            remain_var := AMOUNT_VAR;

         
4. 建表备份sql。

-- Create tablecreate table INVENTORY_CONTAIN(  INV_GRP_SEQ   NUMBER(5) not null,  PORTFOLIO_SEQ NUMBER(5) not null,  PRIORITY      NUMBER(3) default 1)tablespace USERS  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );-- Add comments to the columns comment on column INVENTORY_CONTAIN.INV_GRP_SEQ  is 'inventory group number, ref cms.inventory_grp, 库存群组编号';comment on column INVENTORY_CONTAIN.PORTFOLIO_SEQ  is 'portfolio number, ref cms.portfolio, 投组编号';comment on column INVENTORY_CONTAIN.PRIORITY  is 'priority of portfolio in this inventory group, 投组优先级';-- Create/Recreate primary, unique and foreign key constraints alter table INVENTORY_CONTAIN  add constraint PK_INVENTORY_CONTAIN primary key (INV_GRP_SEQ, PORTFOLIO_SEQ)  using index   tablespace USERS  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );alter table INVENTORY_CONTAIN  add constraint UQ_INVENTORY_CONTAIN_PORTFOLIO unique (PORTFOLIO_SEQ)  using index   tablespace USERS  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );

赋予wms查询LOGON_SITE表，执行conterparty_main的权限。

grant select on LOGON_SITE to WMS;grant execute on counterparty_main to wms; 

5.嵌套表类型

CREATE OR REPLACE TYPE "MSG_ENTRY" AS OBJECT(  SEQ           int,  MESSAGE       VARCHAR2(4000))CREATE OR REPLACE TYPE "MSG_LIST" AS TABLE OF MSG_ENTRY  FUNCTION QUERY_INV_GRP_PF(    I_CUS_NUMBER IN WCUSTOMER.CUS_NUMBER%TYPE    ) return msg_list as      lc_ret msg_list := msg_list();      cursor inv_grp_cur IS      SELECT INV_GRP_SEQ, INV_GRP_NAME, ALERT_TYPE, STATUS        FROM inventory_grp t       WHERE t.cus_number = I_CUS_NUMBER         and t.status not in ('U', 'D');     procedure add_row(text in varchar2) as    begin      lc_ret.extend;      lc_ret(lc_ret.last) := MSG_ENTRY(lc_ret.last, text);    end;    begin    OPEN inv_grp_cur;      add_row('<Page>');    LOOP      FETCH inv_grp_cur        INTO inv_grp_rec;      EXIT WHEN inv_grp_cur%NOTFOUND;      add_row(CHR(9) ||         '<GRP_INFO>' || CHR(10) || CHR(9) || CHR(9) ||        '<INV_GRP_SEQ>' || inv_grp_rec.inv_grp_seq || '</INV_GRP_SEQ>' || CHR(10) || CHR(9) || CHR(9) ||        '<INV_GRP_NAME>' || trim(inv_grp_rec.inv_grp_name) || '</INV_GRP_NAME>' || CHR(10) || CHR(9) || CHR(9) ||        '<ALERT_TYPE>' || inv_grp_rec.alert_type || '</ALERT_TYPE>' || CHR(10) || CHR(9) || CHR(9) ||         '<STATUS>' || inv_grp_rec.status || '</STATUS>' || CHR(10) || CHR(9) ||        '</GRP_INFO>');    END LOOP;    add_row('</Page>');    return lc_ret;  exception    when others then      add_row('异常错误.' || CHR(10));      add_row('</Page>');      RETURN lc_ret;  END QUERY_INV_GRP_PF;

 执行sql

select message from table(inventory_control.QUERY_INV_GRP_CUSTOMER(76,'Y'))

的结果如下

<Page> <DETAIL>  <INV_GRP_SEQ>9538</INV_GRP_SEQ>  <INV_GRP_NAME>dt_1</INV_GRP_NAME>  <ALERT_TYPE>A</ALERT_TYPE>  <STATUS>A</STATUS>  <USER_NUMBER>595</USER_NUMBER>  <CUS_NUMBER>76</CUS_NUMBER>  <OPEN_REPO_ENABLE>Y</OPEN_REPO_ENABLE>  <OPEN_REPO_PRIORITY>1</OPEN_REPO_PRIORITY> </DETAIL>" <DETAIL>  <INV_GRP_SEQ>9539</INV_GRP_SEQ>  <INV_GRP_NAME>dt_2</INV_GRP_NAME>  <ALERT_TYPE>N</ALERT_TYPE>  <STATUS>A</STATUS>  <USER_NUMBER>595</USER_NUMBER>  <CUS_NUMBER>76</CUS_NUMBER>  <OPEN_REPO_ENABLE>N</OPEN_REPO_ENABLE>  <OPEN_REPO_PRIORITY>1</OPEN_REPO_PRIORITY> </DETAIL></Page>