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 table
create 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>