oracle 表权限分配
---------------------------------------------------------------------------------------------
-- Create table
create table COMM.INPUT_WAY_DICT
(
word VARCHAR2(2) not null,
spell_code VARCHAR2(6),
wbzx_code VARCHAR2(4)
)
tablespace ST_RIS;
-- Create/Recreate primary, unique and foreign key constraints
alter table COMM.INPUT_WAY_DICT
add constraint PK_INPUT_WAY_DICT primary key (WORD)
using index ;
alter index COMM.PK_INPUT_WAY_DICT nologging;
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on COMM.INPUT_WAY_DICT to SYSTEM;
-----------------------------------------------------------------------------------------------
create or replace noneditionable function f_spell_code(as_name varchar2) return varchar2 is
ls_one_hz varchar2(10);
ls_temp_py varchar2(100);
li_length integer;
i integer;
ls_py varchar2(100);
begin
li_length := length(trim(as_name));
if nvl(li_length, 0) = 0 then
li_length := 0;
end if;
ls_py := '';
ls_one_hz := '';
ls_temp_py := '';
i := 1;
loop
if i > li_length then
exit;
end if;
ls_one_hz := substr(as_name, i, 1);
if ascii(ls_one_hz) > 128 then
ls_one_hz := substr(as_name, i, 1);
begin
select a.spell_code
into ls_temp_py
from comm.input_way_dict a
where a.word = ls_one_hz;
exception
when no_data_found then
ls_temp_py := '?';
when others then
return('拼音码查询错误!!!');
end;
ls_temp_py := substr(upper(ls_temp_py), 1, 1);
ls_py := ls_py || ls_temp_py;
elsif (ascii(ls_one_hz) >= 65 and ascii(ls_one_hz) < 91) or
(ascii(ls_one_hz) >= 97 and ascii(ls_one_hz) < 123) or
(ascii(ls_one_hz) >= 48 and ascii(ls_one_hz) < 58) then
ls_one_hz := upper(ls_one_hz);
ls_py := ls_py || ls_one_hz;
end if;
i := i + 1;
end loop;
return ls_py;
end;
------------------------------------------------------------------------
-- Add/modify columns
alter table INPBILL.PREPAYMENT_RCPT add pos_tradeserialno varchar2(10);
-- Create table
create table COMM.BASE_DICT
(
dict_code VARCHAR2(4) not null,
dict_name VARCHAR2(40) not null,
dict_chinese VARCHAR2(50),
code VARCHAR2(10) not null,
name VARCHAR2(50),
sequences VARCHAR2(10),
vaild CHAR(1),
memorys VARCHAR2(60),
spell_code VARCHAR2(30)
)
tablespace ST_RIS;
-- Add comments to the columns
comment on column COMM.BASE_DICT.dict_code
is '编号';
comment on column COMM.BASE_DICT.dict_name
is '字典名称';
comment on column COMM.BASE_DICT.dict_chinese
is '字典中文名';
comment on column COMM.BASE_DICT.code
is '字典代码';
comment on column COMM.BASE_DICT.name
is '字典代码对应中文值';
comment on column COMM.BASE_DICT.sequences
is '序号';
comment on column COMM.BASE_DICT.vaild
is '有效？';
comment on column COMM.BASE_DICT.memorys
is '备注';
comment on column COMM.BASE_DICT.spell_code
is '拼音码';
-- Create/Recreate primary, unique and foreign key constraints
alter table COMM.BASE_DICT
add constraint PK_BASE_DICT primary key (DICT_CODE, CODE)
using index
tablespace ST_RIS;
-- Grant/Revoke object privileges
grant select, insert, update, delete on COMM.BASE_DICT to DBA;
grant select, insert, update, delete, references, alter, index on COMM.BASE_DICT to PUBLIC;
--------------------------------------------------------------------------------
alter table INPBILL.PREPAYMENT_RCPT add pos_tradeserialno varchar2(50);
alter table INPBILL.PREPAYMENT_RCPT add pos_tradebeathno varchar2(10);
alter table INPBILL.PREPAYMENT_RCPT add pos_banktrade_date varchar2(10);
alter table INPBILL.PREPAYMENT_RCPT add pos_banktrade_time varchar2(10);
alter table INPBILL.PREPAYMENT_RCPT add pos_refence_no varchar2(12);
alter table INPBILL.PREPAYMENT_RCPT add pos_terminalno varchar2(15);
alter table INPBILL.PREPAYMENT_RCPT add pos_store_no varchar2(15);
alter table INPBILL.PREPAYMENT_RCPT add pos_card_type char(1);
alter table INPBILL.PREPAYMENT_RCPT add POS_TRADE_INDEXNO VARCHAR2(50);
alter table INPBILL.PREPAYMENT_RCPT add refund_flag char(1);
alter table INPBILL.PREPAYMENT_RCPT add refund_old_rcpt_no VARCHAR2(20);
-- Add comments to the columns
comment on column INPBILL.PREPAYMENT_RCPT.pos_tradeserialno
is 'POS交易流水号';
comment on column INPBILL.PREPAYMENT_RCPT.pos_tradebeathno
is 'POS交易批次号';
comment on column INPBILL.PREPAYMENT_RCPT.pos_banktrade_date
is 'POS交易银行主机日期';
comment on column INPBILL.PREPAYMENT_RCPT.pos_banktrade_time
is 'POS交易银行主机时间';
comment on column INPBILL.PREPAYMENT_RCPT.pos_refence_no
is 'POS交易参考号';
comment on column INPBILL.PREPAYMENT_RCPT.pos_terminalno
is 'POS交易终端号';
comment on column INPBILL.PREPAYMENT_RCPT.pos_store_no
is 'POS交易商户号';
comment on column INPBILL.PREPAYMENT_RCPT.pos_card_type
is 'POS交易卡类型（字典base_dict.XINGLEEPOS）';
comment on column INPBILL.PREPAYMENT_RCPT.POS_TRADE_INDEXNO
is 'POS交易索引号';
comment on column INPBILL.PREPAYMENT_RCPT.refund_flag
is '退款标志(1,已退款，0：未退款)';
comment on column INPBILL.PREPAYMENT_RCPT.refund_old_rcpt_no
is '退款原收据号';
-----------------------------------------------------------------
create sequence BK.XINGLEE_POS_log_seq
minvalue 1
maxvalue 9999999999999
start with 1
increment by 1
cache 200
cycle;
----------------------------------------------------------------
---创建log日志，记录每一笔银行交易
-- Create table
create table BK.XINGLEE_POS_LOG
(
sequenceno NUMBER(20) not null,
tradedatetime DATE not null,
inparm BLOB,
outparm BLOB,
intrade_code VARCHAR2(2),
intrade_cost VARCHAR2(12),
inpos_serialno VARCHAR2(6),
incashier_machine VARCHAR2(10),
incashier_no VARCHAR2(10),
inreference_no VARCHAR2(15),
inauthorize_no VARCHAR2(6),
inold_tradedate VARCHAR2(8),
incard_type VARCHAR2(1),
intrade_indexsno VARCHAR2(76),
intwotrack VARCHAR2(37),
inthreetrack VARCHAR2(104),
inoldtradecode VARCHAR2(2),
inoldterminalno VARCHAR2(15),
inoldauthorize_no VARCHAR2(6),
inpayment_code VARCHAR2(3),
instoreno VARCHAR2(20),
inapplyno VARCHAR2(30),
inorderno VARCHAR2(15),
inoldbatchno VARCHAR2(6),
inoldtradedate VARCHAR2(6),
inoldtradecost VARCHAR2(12),
outresultcode VARCHAR2(6),
outresultmsg VARCHAR2(40),
outpos_serialno VARCHAR2(6),
outauthorize_no VARCHAR2(6),
outbatchno VARCHAR2(6),
outcardno VARCHAR2(19),
outvailddate VARCHAR2(4),
outbankno VARCHAR2(2),
outreference_no VARCHAR2(12),
outterminalno VARCHAR2(15),
outmerchantno VARCHAR2(15),
outtradecost VARCHAR2(12),
outtradeindexsno VARCHAR2(16),
outcustom_field VARCHAR2(74),
outbankcardcode VARCHAR2(8),
outbankmeddate VARCHAR2(8),
outbankmedtime VARCHAR2(6),
outorderno VARCHAR2(15),
outtradecode VARCHAR2(2),
outtickettype VARCHAR2(1),
outcodeapplyno VARCHAR2(96)
)
tablespace TSP_INSURANCE;
-- Add comments to the columns
comment on column BK.XINGLEE_POS_LOG.sequenceno
is '序列号';
comment on column BK.XINGLEE_POS_LOG.tradedatetime
is '交易记录时间';
comment on column BK.XINGLEE_POS_LOG.inparm
is '入参字符串';
comment on column BK.XINGLEE_POS_LOG.outparm
is '出参字符串';
comment on column BK.XINGLEE_POS_LOG.intrade_code
is '入交易代码';
comment on column BK.XINGLEE_POS_LOG.intrade_cost
is '入交易金额';
comment on column BK.XINGLEE_POS_LOG.inpos_serialno
is '入POS流水号';
comment on column BK.XINGLEE_POS_LOG.incashier_machine
is '入收款机号';
comment on column BK.XINGLEE_POS_LOG.incashier_no
is '入收款员号';
comment on column BK.XINGLEE_POS_LOG.inreference_no
is '入参考号';
comment on column BK.XINGLEE_POS_LOG.inauthorize_no
is '入授权号';
comment on column BK.XINGLEE_POS_LOG.inold_tradedate
is '入原交易时间';
comment on column BK.XINGLEE_POS_LOG.incard_type
is '入卡片类型';
comment on column BK.XINGLEE_POS_LOG.intrade_indexsno
is '入交易索引号';
comment on column BK.XINGLEE_POS_LOG.intwotrack
is '入二磁道';
comment on column BK.XINGLEE_POS_LOG.inthreetrack
is '入三磁道';
comment on column BK.XINGLEE_POS_LOG.inoldtradecode
is '入原交易代码';
comment on column BK.XINGLEE_POS_LOG.inoldterminalno
is '入原终端号';
comment on column BK.XINGLEE_POS_LOG.inoldauthorize_no
is '入原授权号';
comment on column BK.XINGLEE_POS_LOG.inpayment_code
is '入缴费账户编码';
comment on column BK.XINGLEE_POS_LOG.instoreno
is '入门店号';
comment on column BK.XINGLEE_POS_LOG.inapplyno
is '入单据号';
comment on column BK.XINGLEE_POS_LOG.inorderno
is '入订单号';
comment on column BK.XINGLEE_POS_LOG.inoldbatchno
is '入原批次号';
comment on column BK.XINGLEE_POS_LOG.inoldtradedate
is '入原交易时间';
comment on column BK.XINGLEE_POS_LOG.inoldtradecost
is '入原交易金额';
comment on column BK.XINGLEE_POS_LOG.outresultcode
is '出返回码';
comment on column BK.XINGLEE_POS_LOG.outresultmsg
is '出返回码含义';
comment on column BK.XINGLEE_POS_LOG.outpos_serialno
is '出pos流水号';
comment on column BK.XINGLEE_POS_LOG.outauthorize_no
is '出授权码';
comment on column BK.XINGLEE_POS_LOG.outbatchno
is '出批次号';
comment on column BK.XINGLEE_POS_LOG.outcardno
is '出卡号';
comment on column BK.XINGLEE_POS_LOG.outvailddate
is '出有效期';
comment on column BK.XINGLEE_POS_LOG.outbankno
is '出银行号';
comment on column BK.XINGLEE_POS_LOG.outreference_no
is '出参考号';
comment on column BK.XINGLEE_POS_LOG.outterminalno
is '出终端号';
comment on column BK.XINGLEE_POS_LOG.outmerchantno
is '出商户号';
comment on column BK.XINGLEE_POS_LOG.outtradecost
is '出交易金额';
comment on column BK.XINGLEE_POS_LOG.outtradeindexsno
is '出交易索引号';
comment on column BK.XINGLEE_POS_LOG.outcustom_field
is '出自定义域';
comment on column BK.XINGLEE_POS_LOG.outbankcardcode
is '出发卡银行代码';
comment on column BK.XINGLEE_POS_LOG.outbankmeddate
is '出银行主机日期';
comment on column BK.XINGLEE_POS_LOG.outbankmedtime
is '出银行主机时间';
comment on column BK.XINGLEE_POS_LOG.outorderno
is '出订单号';
comment on column BK.XINGLEE_POS_LOG.outtradecode
is '出交易代码';
comment on column BK.XINGLEE_POS_LOG.outtickettype
is '出小票打印方式';
comment on column BK.XINGLEE_POS_LOG.outcodeapplyno
is '出扫码订单号';
-- Create/Recreate primary, unique and foreign key constraints
alter table BK.XINGLEE_POS_LOG
add constraint PK_XINGLEEPOS_SEQ primary key (SEQUENCENO, TRADEDATETIME)
using index
tablespace TSP_INSURANCE;
