怎样创建函数索引

create or replace procedure sp_crt_stg_index( p_src_tbl_name varchar2,p_tgt_tbl_name varchar2,p_tx_date varchar2)is v_crt_ind_sql varchar2(4000) := ''; v_drop_ind_sql varchar2(4000) := ''; v_ind_name varchar2(100) := ''; v_tgt_ind_name varchar2(100) := ''; v_col_list varchar2(1000) := ''; v_col_name varchar2(100) := ''; i_ind_col_cnt number(2,0) := 0; i_tbl_cnt number(2,0) := 0; i_ind_cnt number(2,0) := 0; v_step_no varchar2(3) :='';begin --step 1 检查索引定义是否存在 v_step_no :='1'; select count(1) into i_ind_col_cnt from all_ind_columns where table_name = p_src_tbl_name; if(i_ind_col_cnt = 0 ) then sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在索引定义'); return; end if; --step 2 检查目标表是否存在 v_step_no :='2'; select count(1) into i_tbl_cnt from tabs where table_name = p_tgt_tbl_name; if(i_tbl_cnt = 0 ) then sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在'); return; end if; --step 3 创建索引 v_step_no :='3'; for cur_ind in (select index_name from all_indexes where table_name = p_src_tbl_name) loop v_ind_name := cur_ind.index_name; v_col_name := ''; v_col_list := '('; for cur_ind_col in (select column_name from all_ind_columns where index_name = v_ind_name order by column_position) loop v_col_name := cur_ind_col.column_name; v_col_list := v_col_list||v_col_name||','; end loop; v_col_list := v_col_list||')'; v_col_list := replace(v_col_list,',)',')'); v_tgt_ind_name :=replace(v_ind_name,p_src_tbl_name,p_tgt_tbl_name); v_tgt_ind_name := v_tgt_ind_name||substr(p_tx_date,7,21); select count(1) into i_ind_cnt from all_indexes where index_name = v_tgt_ind_name; if(i_ind_cnt > 0) then v_drop_ind_sql := 'drop index '||v_tgt_ind_name; execute immediate v_drop_ind_sql; end if; sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'创建开始'); v_crt_ind_sql := 'create index '||v_tgt_ind_name||' on '||p_tgt_tbl_name||v_col_list||' nologging'; if(v_crt_ind_sql is not null) then execute immediate v_crt_ind_sql; end if; sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'创建结束'); end loop; exception when others then sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,v_tgt_ind_name||'创建异常:'||SQLERRM);end; ######################### ---- 日期类型转换to_char(sysdate,'YYYY-MM-DDHH24:MI:SS');----Oracle检查分区 selectcount(1) fromALL_TAB_PARTITIONS wheretable_name=p_tgt_tbl_name andPARTITION_NAME=v_prt_name;----码表 Select*From ict_s_dicWhereopttype='ID_TYPE';decode内部字段拼接decode(Date_Nextvisit,'','下次跟进时间：['||Date_Nextvisit||']')RMK1,----拼接字段,删除表SELECT'DROPTABLE'||TABLE_NAME||';'FROMTABS WHERETABLE_NAMELIKE'M_ICT%';Select*Fromtabs;----交换分区，分区交换后数据是否交换？'ALTERTABLE'||p_tgt_tbl_name||'EXCHANGEPARTITION'||v_prt_name||'WITHTABLE'||p_src_tbl_name;----查询某表是否存在selectcount(1)fromtabs wheretable_name=p_src_tbl_name;----重建索引ALTERindexind_id_idxrebuild----创建索引nologgingcreateindexI_ICT_CUST_INFO__0onICT_CUST_INFO_20141222(CUST_NO) nologging;----查询分区内数据select count(1)fromstg_ict_trade_infopartition(ICT_PRT_2014005);Select*FromUser_Ind_Partitions;Select*FromUser_Part_Indexes;-----查询索引selectindex_namefromALL_INDEXESWHERETABLE_NAME=p_src_tbl_name;-----查询分区select*fromALL_TAB_PARTITIONS wheretable_name=p_tgt_tbl_nameandPARTITION_NAME=v_prt_name;-----查询表名 select*fromtabswheretable_name=p_tgt_tbl_name;-----清空表分区数据'ALTERTABLE'||p_tgt_tbl_name||'TRUNCATEPARTITION'||v_prt_name;-----增加表分区'ALTERTABLE'||p_tgt_tbl_name||'ADDPARTITION'||v_prt_name||'VALUESLESSTHAN('''||v_monthend||''')TABLESPACEICLIENT_O_DATA01';-----重建索引'ALTERINDEX'||cur_ind.index_name||'REBUILDPARALLEL128COMPUTESTATISTICSNOLOGGING';-----交换分区'ALTERTABLE'||p_tgt_tbl_name||'EXCHANGEPARTITION'||v_prt_name||'WITHTABLE'||p_src_tbl_name||'INCLUDINGINDEXES'; -----oralce创建同义词------------ createorreplacepublicSYNONYMICT_ORG_BPH foriclientodata.ICT_ORG_BPH; -----oracle赋权限grantselect,insert,update,deleteonICT_ORG_BPHtoICLIENTOOPR; --赋权限 ---------------DBA查看表空间------------selecta.tablespace_name, a.bytes/1024/1024/1024"SumG", (a.bytes-b.bytes)/1024/1024/1024"usedG", b.bytes/1024/1024/1024"freeG", round(((a.bytes-b.bytes)/a.bytes)*100,2)"percent_used" from(selecttablespace_name,sum(bytes)bytes fromdba_data_files groupbytablespace_name)a, (selecttablespace_name,sum(bytes)bytes,max(bytes)largest fromdba_free_space groupbytablespace_name)bwherea.tablespace_name=b.tablespace_nameorderby((a.bytes-b.bytes)/a.bytes)desc---------------------------------------------------------------------------------------oracle生成删表语句，SELECT'DROPTABLE'||TABLE_NAME||';'FROMTABS WHERETABLE_NAMELIKE'M_ICT%'--查询oracle中ICT开头的SELECT*FROM tabs WHERETABLE_NAMELIKE'ICT%'----oracle表分析语句 'ANALYZETABLE'||v_ana_tbl_name||'estimatesystemstatistics'; 例如：ANALYZETABLE ICT_SUM_AST_DBT_CUST ESTIMATESYSTEMSTATISTICS; -----------查看锁定对象及会话SELECTOBJECT_NAME,MACHINE,S.SID,S.SERIAL#FROMGV$LOCKED_OBJECTI,DBA_OBJECTSO,GV$SESSIONSWHEREI.OBJECT_ID=O.OBJECT_IDANDI.SESSION_ID=S.SID;----------oralce解锁ALTERSYSTEMKILLSESSION'280,219'; 授权脚本生成方法：select'grantselectontabledmccrm.'||tbl_name||'topublic,ex_sdbods;'fromt_ict_tbl_typewheretbl_typein('ALL','GP')andETL_DIRIN('GP->ORACLE','无需同步');-----------oracle批量生成同义词SELECT 'createorreplacepublicsynonym'||table_name||'foriclientodata.'||table_nameFROMtabsWHERE table_namelike'ICT_%' -----------oracle批量生成修改表字段的长度select'ALTERTABLE'||TABLE_NAME||'MODIFY'||COLUMN_NAME||'NUMBER(30,8);'fromcolstwheret.DATA_TYPE='NUMBER' ANDT.DATA_SCALE>0ANDSUBSTR(TABLE_NAME,-8,8)'20140531'---------oracle批量创建同义词select'createorreplacepublicsynonym'||table_name||'foriclientodata.'||table_name||';'fromuser_tables WHEREtable_nameLIKE'ICT_%' ANDtable_nameNOTLIKE'%20140531';-----------oracle交换分区语句ALTERTABLEICT_CUST_LEVEL_HISADDPARTITIONICT_PRT_20140531VALUESLESSTHAN('2014-06-01')--------MERGE使用方法MERGEINTOICT_CUST_INFO_ALLaUSING(SELECTt.CUST_MNG_UM_NO,t.CUST_NO FROMict_cust_mnger_relt WHEREt.CUST_NOIN ('600021562650','600037441214','600036874754','600038507516', '600020226746','600038089420','600041030403','600038952992', '600039468303'))bON(a.cust_no=b.cust_no)WHENMATCHED THENUPDATE SETa.MAX_ASSET_INTRO_NO=b.CUST_MNG_UM_NO;-------------oracle树形查询，查询机构编号为‘9902’的和其下级子机构 selectorg_id,org_name,org_levelfrom( SELECTrownumrn,ioi.org_id,ioi.org_name,ioi.org_level FROMict_org_infoioi STARTWITHioi.org_id='9902' CONNECTBY PRIORioi.org_id =ioi.parent_org_id_b orderbyioi.org_leveldesc )wherern=1 -------------oracle树形查询，查询机构编号为‘9902’的和上级机构 selectorg_id,org_name,org_levelfrom( SELECTrownumrn,ioi.org_id,ioi.org_name,ioi.org_level FROMict_org_infoioi STARTWITHioi.org_id='9902' CONNECTBY ioi.org_id =PRIORioi.parent_org_id_b orderbyioi.org_leveldesc )wherern=1 -------------oracle树形查询，查询机构编号为‘9902’的上级机构 selectorg_id,org_name,org_levelfrom( SELECTrownumrn,ioi.org_id,ioi.org_name,ioi.org_level FROMict_org_infoioi STARTWITHioi.org_id='9902' CONNECTBY ioi.org_id =PRIORioi.parent_org_id_b orderbyioi.org_leveldesc )wherern=1 ----------查看表名与表空间Select*Fromuser_tablesWheretable_name='ICT_RMT_APPO';Select*FromUser_Tablespaces; ----------查询SQL预估时间SELECTSE.SID, OPNAME, TRUNC(SOFAR/TOTALWORK*100,2)||'%'ASPCT_WORK, ELAPSED_SECONDSELAPSED, ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)REMAIN_TIME, SQL_TEXT FROMV$SESSION_LONGOPSSL,V$SQLAREASA,V$SESSIONSEWHERESL.SQL_HASH_VALUE=SA.HASH_VALUE ANDSL.SID=SE.SID ANDSOFAR!=TOTALWORKORDERBYSTART_TIME; ------TYPEopty_curISREFCURSOR;整体的意思是“创建一个类型变量cur，它引用游标”，除了cur外，其余全是关键字。TYPEcur：定义类型变量,isrefcursor：相当于数据类型，不过是引用游标的数据类型。这种变量通常用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了。----查杀进程SELECTdob.OBJECT_NAMETable_Name, lo.LOCKED_MODE, lo.SESSION_ID, vss.SERIAL#, vps.spid, vss.actionAction, vss.osuserOSUSER, vss.processAP_PID, VPS.SPIDDB_PID, 'altersystemkillsession '||''''||lo.SESSION_ID||','|| vss.SERIAL#||''';'kill_command fromv$locked_objectlo,dba_objectsdob,v$sessionvss,V$PROCESSVPS wherelo.OBJECT_ID=dob.OBJECT_ID andlo.SESSION_ID=vss.SID ANDVSS.paddr=VPS.addr orderby2,3,DOB.object_name;