oracle整理笔记

–create function
create or replace function washdata.cnNum2AraDec(p_cnNum string) return varchar2 is
v_cnNum varchar2(255);
t_cnNum varchar(4);
v_dec varchar2(255);
v_num varchar2(255);
begin
t_cnNum:=’’;
if p_cnNum is not null then
v_cnNum := p_cnNum;
for i in 1 … length(v_cnNum) loop
t_cnNum := substr(v_cnNum, i, 1);
v_dec := v_dec || case
when t_cnNum = ‘一’ then
‘1’
when t_cnNum = ‘二’ then
‘2’
when t_cnNum = ‘三’ then
‘3’
when t_cnNum = ‘四’ then
‘4’
when t_cnNum = ‘五’ then
‘5’
when t_cnNum = ‘六’ then
‘6’
when t_cnNum = ‘七’ then
‘7’
when t_cnNum = ‘八’ then
‘8’
when t_cnNum = ‘九’ then
‘9’
when t_cnNum = ‘〇’ then
‘0’
when t_cnNum = ‘零’ then
‘0’
when t_cnNum = ‘百’ then
‘’
when t_cnNum = ‘千’ then
‘’
when t_cnNum = ‘十’ and ((substr(v_cnNum, i-1, 1) not in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’) or i= 1)and
substr(v_cnNum, i+1, 1) in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)) then
‘1’
when t_cnNum = ‘十’ and (substr(v_cnNum, i+1, 1) not in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)
and (substr(v_cnNum, i-1, 1) not in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)or i= 1)) then
‘10’
when t_cnNum = ‘十’ and (substr(v_cnNum, i-1, 1) in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)
and substr(v_cnNum, i+1, 1) in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)) then
‘’
when t_cnNum = ‘十’ and (substr(v_cnNum, i-1, 1) in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)
and substr(v_cnNum, i+1, 1) not in(‘一’,‘二’,‘三’,‘四’,‘五’,‘六’,‘七’,‘八’,‘九’)) then
‘0’
else
t_cnNum
end;
end loop;
v_num := v_dec;
return v_num;
else
return null;
end if;
end;

–Create procedure
create or replace procedure washdata.pro_addwashdata as
begin
insert into DATATIME(type,ADDTIME,cqsj)
select ‘T_RY_SDLK’,max(to_char(t.ZHXGSJ,‘yyyy-mm-dd hh24:mi:ss’)),sysdate from T_RY_SDLK@qqb t where id in(select id from zz_sb0410@qqb);
commit;
execute immediate ‘truncate table zk_add’;
insert into zk_add(zkid,ZHXGSJ,xjzdzz,xzdxzqh,addtime,ID,gajgdm)
select ID as zkid,ZHXGSJ,xjzdzz,t.xzdxzqh,sysdate,rownum as id,gajgdm from T_RY_SDLK@qqb t
where to_char(ZHXGSJ,‘yyyy-mm-dd hh24:mi:ss’)>(select max(ADDTIME) from DATATIME) ;
commit;
end;

– Create sequence
create sequence ADDRESSWORD_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 318742
increment by 1
cache 20;

–Create trigger
CREATE OR REPLACE TRIGGER tr_addressword
before insert on addressword
for each row
begin
select addressword_seq.nextval into :new.id from dual;
end;

–
– Create table
create table ADDRESS_BZLD
(
id VARCHAR2(100) not null,
xxly VARCHAR2(100),
qxdm VARCHAR2(10),
pcsdm VARCHAR2(12),
xzjddm VARCHAR2(50),
sqdm VARCHAR2(50),
sqmc VARCHAR2(100),
country VARCHAR2(50),
province VARCHAR2(50),
city VARCHAR2(50),
qx VARCHAR2(100),
xzjd VARCHAR2(100),
cjwh VARCHAR2(100),
cz VARCHAR2(100),
jlx VARCHAR2(100),
jlxbc1 VARCHAR2(100),
mph VARCHAR2(100),
xqbzw VARCHAR2(100),
xqbzwbc1 VARCHAR2(100),
lz VARCHAR2(100),
dy VARCHAR2(100),
lc VARCHAR2(100),
zuo VARCHAR2(50),
hs VARCHAR2(200),
fw VARCHAR2(100),
address VARCHAR2(500),
standardaddress VARCHAR2(500),
moi_id VARCHAR2(50),
x VARCHAR2(50),
y VARCHAR2(50),
ldtype VARCHAR2(50),
coords VARCHAR2(1000),
grids VARCHAR2(100),
datatype VARCHAR2(50),
bz VARCHAR2(500),
addtime DATE,
lx VARCHAR2(50),
ly VARCHAR2(50),
pic VARCHAR2(200),
shry VARCHAR2(100),
isdel VARCHAR2(50),
citydm VARCHAR2(10),
bl1 VARCHAR2(200),
bl2 VARCHAR2(200),
poi VARCHAR2(200),
zrqdm VARCHAR2(50),
qybs VARCHAR2(1),
address_lz VARCHAR2(500)
)
tablespace TBS_QINGDAO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1579M
next 8K
minextents 1
maxextents unlimited
);
– Create/Recreate indexes
create index A_INDX_DY on ADDRESS_BZLD (DY)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 38M
next 1M
minextents 1
maxextents unlimited
);
create index A_INDX_HS on ADDRESS_BZLD (HS)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 44M
next 1M
minextents 1
maxextents unlimited
);
create index A_INDX_POI on ADDRESS_BZLD (POI)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_ADDRESS_LZ on ADDRESS_BZLD (ADDRESS_LZ)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 288M
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BZDZ_ID on ADDRESS_BZLD (ID)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 265M
next 1M
minextents 1
maxextents unlimited
);
create index IDX_STANDARDADDRESS on ADDRESS_BZLD (STANDARDADDRESS)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 388M
next 1M
minextents 1
maxextents unlimited
);
create index INDX_ADDRESS on ADDRESS_BZLD (ADDRESS)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 216M
next 1M
minextents 1
maxextents unlimited
);
create index INDX_B_ZRQDM on ADDRESS_BZLD (ZRQDM)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 176M
next 1M
minextents 1
maxextents unlimited
);
create index INDX_LX on ADDRESS_BZLD (LX)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 104M
next 1M
minextents 1
maxextents unlimited
);
create index INDX_LY on ADDRESS_BZLD (LY)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 104M
next 1M
minextents 1
maxextents unlimited
);
create index INX_XQBZW_ADDRESS_BZLD on ADDRESS_BZLD (XQBZW)
tablespace TBS_QINGDAO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
– Create/Recreate primary, unique and foreign key constraints
alter table ADDRESS_BZLD
add constraint ADDRESS_BZLD_ID primary key (ID);