Oracle SQL语句

1.sql查询number类型的数据比如0.95  查询结果只显示 .95 
   改变：TO_CHAR(b.pay_money/100,'fm9999990.9999'）

2.查询表在哪个存储过程中被调用

select  * from    All_Source  ff where  upper(ff.text) like '%TS_U_PROV_FEATURE%'

3.表赋权
 grantselect,insert,update,deleteonTS_R_WECHAT_RECEIVABLE to IMPORT_RPT_01;

在IMPORT_RPT_01用户下建立同义词
 create or replace synonym tf_b_order for ucr_upp.tf_b_order;

4.修改表结构，添加列
altertableTF_A_ABILITY_PAY_DOWNLOADaddPAY_ORG_ORDER_NOvarchar2(50);

5.更新表中多个字段的值

 update td_t_erpt_select_rel a

   set a.data_provider    = 'com.ailk.rpt.ecs.provider.PartnerProvider',

       a.data_origin_type = '1'

 where a.data_provider = '接入渠道';

6.创建索引

create index UCR_TRADE_01.IDX_TF_B_ORDER_1 on UCR_TRADE_01.TF_B_ORDER (ORDER_NO)

  local;

7.添加表及字段说明

comment on table ts_r_busi_tas_stat_woacnt_day

  is '代扣业务签约日统计表（全国）-按照账户统计';

-- Add comments to the columns

comment on column ts_r_busi_tas_stat_woacnt_day.clct_day

  is '日期';

8.备份表sql

CREATE TABLE ts_r_busi_audit_detail_his AS (SELECT * FROM ts_r_busi_audit_detail);

9.订单表分渠道求订单成功的百分比

select a.channel_type channel_type ,
      TO_CHAR( sum(case when order_state='02' then 1 else 0 end)/count(1),'fm9999990.9999')*100||'%'
  from tf_b_order a
  where a.create_time>sysdate-1
 group by a.channel_type