Oracle常用基本语句_过程处理部分

创建流程:

查询存储过程select * From user_source where name='CLZ_JJX_SERV' 删除存储过程drop procedure  clz_jjx_serv   declare vflag number(10);   begin for i in (select r.id from rmgz.addr r where r.grade=10 and r.amend=1 /*and r.id=638753476*/) loop select count(1) into vflag from rmgz.dp_serarea where ecfport is not null and stid=i.id and cn_way=2; ifvflag=1 then updatermgz.addr a set a.paper_cable=1,a.paper_cable_indoors=1 where a.id=i.id; commit; insert into cyq_gz_addr0831(addrid) values(i.id); commit; end if; end loop; end;

 

索引:

删除表索引： v_num number; begin ----插完数据再建索引，增加插入效率 select count(1) into v_num from user_indexes a where a.index_name='IDX_TB_WG_ADDR_1'; if v_num>0 then execute immediate 'drop index IDX_TB_WG_ADDR_1'; end if;     -- 创建索引 tab_num number;  select count(1) into tab_num from user_ind_columns a where a.TABLE_NAME=upper('DT_INFO_BUILD_LIST') and column_name='SEARCH_NAME';       if   tab_num=0   then           execute immediate 'create index IDX_DT_INFO_BUILD_LIST_5 on DT_INFO_BUILD_LIST (SEARCH_NAME)';       end   if;

 

判断字段是否存在:

declare field_num number(2); tab_num number(2);   begin select count(1) into tab_num from user_tables a where a.table_name=upper('rm_address');  -- -- 注意：表名需大写或用upper大写函数 if   tab_num>0   then   select count(1) into field_num from user_tab_columns where table_name=upper('rm_address') and column_name in ('ENTITY_FLAG','GREEN_FLAG') ;  -- 注意：表名/字段名需大写    if  field_num=0 then      execute immediate 'xxxxxx';   end if; end   if; end;

 

判断表:

declare tab_num number(2); begin select count(1) into tab_num from user_tables a where a.table_name=upper('rm_address');  -- -- 注意：表名需大写或用upper大写函数 if   tab_num=0  then     execute immediate 'xxxxxx';   end if; end   if; end;

会话临时表:

create global temporary table tmp_son_addr_statistics on commit preserve rows as select /*+parallel (cd,4)(cp,4)*/ from 表; --  on commit preserve rows 这句表示 当事务提交时 保留数据, 重新打开 一个sql窗口 再次查询 则无数据了 global temporary 是全局建表，整个库就建一次就好，无需重新再建

 

Ora-12011 ora-06512

select * from dba_role_privs where grantee='GTZZ_MM';   --拥有DBA role，也不能创建表。role在存储过程中不可用。   --遇到这种情况，一般需要显式进行系统权限，如grant create table to suk;   --但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程   --实际上，oracle给我们提供了在存储过程中使用role权限的方法：   --修改存储过程，加入Authid Current_User时存储过程可以使用role权限。 create or replace procedure p_create_table    Authid Current_User is   begin 没有指定 AUTHID CURRENT_USER 选项，则存储过程执行时，是以创建者的身份运行的，而不是运行该存储过程的用户身份运行的。

 

ORA-38029: 对象统计信息已锁定

在分析表的时候提示被锁定需要解锁： begin dbms_stats.unlock_table_stats('用户名','表名'); end;

 

Q操作符：

oracle本身默认的是单引号，但是在大家写存储过程或者写SQL语句时，有时候需要拼SQL或者是SQL的值里需要传入含单引号的值，此时就需要使用两个单引号"''"来进行转义，其实oracle本身提供了这种转换默认单引号为其他标识的方法那就是——"q" v_sql:='hello''world'; 等同于 v_sql:=q'[hello'world]';  [] 中括号可以是其他的字符 结果是：hello'world   测试案例： declare v_sql varchar2(200); v_cat varchar2(200);  begin    v_sql:='hello''world';   dbms_output.put_line(v_sql);     v_sql:=q'[hello'world]';   dbms_output.put_line(v_sql);        execute immediate q'#select 12 from dual#' into v_cat;     dbms_output.put_line( v_cat); end;

 

检查采集失效表并重建：

--检查采集失效表   for i in (select err_sql from rpt_error_log              where err_code   in (-3135)                and err_time>  sysdate-5/24                and proc_name=vs_proc_name                order by seq_log_id )   loop     begin     vs_sql:=i.err_sql;     --dbms_output.put_line(v_sql);     execute immediate vs_sql;      SP_WRITE_LOG(vs_load_date,vs_proc_name,'0','失效表重建完成',vs_sql);      exception when others then          SP_WRITE_LOG(vs_load_date,vs_proc_name,SQLCODE, SQLERRM,vs_sql);   end;   end loop;