2021-01-22

简单的数据库命令

 

upper全大写
全小写
select lower(business_id) from sp_business_def t；

nvl（$1,$2）有1的话是1，1没有值的话是2

select t.*,t.rowid from sp_business_order t where t.business_id in('foasFStp','')and t.order_id = 'ORDER_NULL';

update bd_type_attrdef t set t.sortorder = (t.sortorder + 2) where t.bureaudatatypeid = 'L1DIMS_IOTA1PNINFO' and t.sortorder >= 11;
update PARAMETER_VALUE t set t.paramvalue=replace(t.paramvalue,'133.96.114.195:8401','133.96.114.10:7030') where t.PARAMVALUE like '%gdb%';

触发器
create or replace view sys.user_source as select语句
drop view sys.user_source;

create or replace procedure p_prodid_out
drop procedure p_prodid_out;

alter table CTBSPAYMENT modify FINANCEACCTCODE VARCHAR2(32);

ALTER TABLE common.AGENT ADD   "CMIOTFLAG" VARCHAR2(16)
/
COMMENT ON COLUMN common.AGENT.CMIOTFLAG IS '是否上传CMIOT';

ALTER TABLE LocPaymentDataRecord ADD ORGBIZCODE VARCHAR2(32);
comment on column LOCPAYMENTDATARECORD.orgbizcode is '省公司组织机构标识';

alter table SMTEMPLATE add  DATASOURCES  VARCHAR2(8)

alter table CTBSPAYMENT modify "datasources" to datasources
select t.*,t.rowid from SMTEMPLATE t;

alter table SMTEMPLATE drop column datasources

--创建同义词
create or replace PC_BD_IOT5GSLICEINFO for common.PC_BD_IOT5GSLICEINFO;

 Error Infomation:ORA-14400
表的分区是不是有时间内限制
alter table CB_REC_MFCASYNARCHIVE_MID drop 
partition PART_999_201912 values less than (999, TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

alter table CB_REC_MFCASYNARCHIVE_MID add 
partition PART_999_202012 values less than (999, TO_DATE(' 2029-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))