熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

ORACLE之常用FAQ V1.0

2013-11-13 15:27:21  來源: Oracle 

  第一部分SQL&PL/SQL
  [Q]怎麼樣查詢特殊字符如通配符%與_
  [A]select * from table where name like A\_% escape \
  
  [Q]如何插入單引號到數據庫表中
  [A]可以用ASCII碼處理其它特殊字符如&也一樣
  insert into t values(i||chr()||m); chr()代表字符
  或者用兩個單引號表示一個
  or insert into t values(Im); 兩個可以表示一個
  
  [Q]怎樣設置事務一致性
  [A]set transaction [isolation level] read committed; 默認語句級一致性
  set transaction [isolation level] serializable;
  read only; 事務級一致性
  
  [Q]怎麼樣利用游標更新數據
  [A]cursor c is
  select * from tablename
  where name is null for update [of column]
  ……
  update tablename set column = ……
  where current of c;
  
  [Q]怎樣自定義異常
  [A] pragma_exception_init(exception_nameerror_number);
  如果立即拋出異常
  raise_application_error(error_numbererror_msgtrue|false);
  其中number從錯誤信息最大B
  異常變量
  SQLCODE 錯誤代碼
  SQLERRM 錯誤信息
  
  [Q]十進制與十六進制的轉換
  [A]i以上版本
  to_char(XX)
  to_number(DXX)
  i以下的進制之間的轉換參考如下腳本
  create or replace function to_base( p_dec in number p_base in number )
  return varchar
  is
  l_str varchar() default NULL;
  l_num number default p_dec;
  l_hex varchar() default ABCDEF;
  begin
  if ( p_dec is null or p_base is null ) then
  return null;
  end if;
  if ( trunc(p_dec) <> p_dec OR p_dec < ) then
  raise PROGRAM_ERROR;
  end if;
  loop
  l_str := substr( l_hex mod(l_nump_base)+ ) || l_str;
  l_num := trunc( l_num/p_base );
  exit when ( l_num = );
  end loop;
  return l_str;
  end to_base;
  /
  create or replace function to_dec
  ( p_str in varchar
  p_from_base in number default ) return number
  is
  l_num number default ;
  l_hex varchar() default ABCDEF;
  begin
  if ( p_str is null or p_from_base is null ) then
  return null;
  end if;
  for i in length(p_str) loop
  l_num := l_num * p_from_base + instr(l_hexupper(substr(p_stri)));
  end loop;
  return l_num;
  end to_dec;
  /
  
  [Q]能不能介紹SYS_CONTEXT的詳細用法
  [A]利用以下的查詢你就明白了
  select
  SYS_CONTEXT(USERENVTERMINAL) terminal
  SYS_CONTEXT(USERENVLANGUAGE) language
  SYS_CONTEXT(USERENVSESSIONID) sessionid
  SYS_CONTEXT(USERENVINSTANCE) instance
  SYS_CONTEXT(USERENVENTRYID) entryid
  SYS_CONTEXT(USERENVISDBA) isdba
  SYS_CONTEXT(USERENVNLS_TERRITORY) nls_territory
  SYS_CONTEXT(USERENVNLS_CURRENCY) nls_currency
  SYS_CONTEXT(USERENVNLS_CALENDAR) nls_calendar
  SYS_CONTEXT(USERENVNLS_DATE_FORMAT) nls_date_format
  SYS_CONTEXT(USERENVNLS_DATE_LANGUAGE) nls_date_language
  SYS_CONTEXT(USERENVNLS_SORT) nls_sort
  SYS_CONTEXT(USERENVCURRENT_USER) current_user
  SYS_CONTEXT(USERENVCURRENT_USERID) current_userid
  SYS_CONTEXT(USERENVSESSION_USER) session_user
  SYS_CONTEXT(USERENVSESSION_USERID) session_userid
  SYS_CONTEXT(USERENVPROXY_USER) proxy_user
  SYS_CONTEXT(USERENVPROXY_USERID) proxy_userid
  SYS_CONTEXT(USERENVDB_DOMAIN) db_domain
  SYS_CONTEXT(USERENVDB_NAME) db_name
  SYS_CONTEXT(USERENVHOST) host
  SYS_CONTEXT(USERENVOS_USER) os_user
  SYS_CONTEXT(USERENVEXTERNAL_NAME) external_name
  SYS_CONTEXT(USERENVIP_ADDRESS) ip_address
  SYS_CONTEXT(USERENVNETWORK_PROTOCOL) network_protocol
  SYS_CONTEXT(USERENVBG_JOB_ID) bg_job_id
  SYS_CONTEXT(USERENVFG_JOB_ID) fg_job_id
  SYS_CONTEXT(USERENVAUTHENTICATION_TYPE) authentication_type
  SYS_CONTEXT(USERENVAUTHENTICATION_DATA) authentication_data
  from dual
  
  [Q]怎麼獲得今天是星期幾還關於其它日期函數用法
  [A]可以用to_char來解決
  select to_char(to_date(yyyymmdd)day) from dual;
  在獲取之前可以設置日期語言
  ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
  還可以在函數中指定
  select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
  其它更多用法可以參考to_char與to_date函數
  如獲得完整的時間格式
  select to_char(sysdateyyyymmdd hh:mi:ss) from dual;
  隨便介紹幾個其它函數的用法
  本月的天數
  SELECT to_char(last_day(SYSDATE)dd) days FROM dual
  今年的天數
  select add_months(trunc(sysdateyear) ) trunc(sysdateyear) from dual
  下個星期一的日期
  SELECT Next_day(SYSDATEmonday) FROM dual
  
  [Q]隨機抽取前N條記錄的問題
  [A]i以上版本
  select * from (select * from tablename order by sys_guid()) where rownum < N;
  select * from (select * from tablename order by dbms_randomvalue) where rownum< N;
  注dbms_random包需要手工安裝位於$ORACLE_HOME/rdbms/admin/dbmsrandsql
  dbms_randomvalue()可以產生范圍的隨機數
  
  [Q]抽取從N行到M行的記錄如從行到行的記錄
  [A]select * from (select rownum idt* from table where ……
  and rownum <= ) where id > ;
  
  [Q]怎麼樣抽取重復記錄
  [A]select * from table t where where trowed !=
  (select max(rowed) from table t
  where tid=tid and tname=tname)
  或者
  select count(*) l_al_b from table t
  group by col_acol_b
  having count(*)>
  如果想刪除重復記錄可以把第一個語句的select替換為delete
  
  [Q]怎麼樣設置自治事務
  [A]i以上版本不影響主事務
  pragma autonomous_transaction;
  ……
  commit|rollback;
  
  [Q]怎麼樣在過程中暫停指定時間
  [A]DBMS_LOCK包的sleep過程
  如dbms_locksleep();表示暫停
  
  [Q]怎麼樣快速計算事務的時間與日志量
  [A]可以采用類似如下的腳本
  DECLARE
  start_time NUMBER;
  end_time NUMBER;
  start_redo_size NUMBER;
  end_redo_size NUMBER;
  BEGIN
  start_time := dbms_utilityget_time;
  SELECT VALUE INTO start_redo_size FROM v$mystat mv$statname s
  WHERE mSTATISTIC#=sSTATISTIC#
  AND sNAME=redo size;
  transaction start
  INSERT INTO t
  SELECT * FROM All_Objects;
  other dml statement
  COMMIT;
  end_time := dbms_utilityget_time;
  SELECT VALUE INTO end_redo_size FROM v$mystat mv$statname s
  WHERE mSTATISTIC#=sSTATISTIC#
  AND sNAME=redo size;
  dbms_outputput_line(Escape Time:||to_char(end_timestart_time)|| centiseconds);
  dbms_outputput_line(Redo Size:||to_char(end_redo_sizestart_redo_size)|| bytes);
  END;
  
  [Q]怎樣創建臨時表
  [A]i以上版本
  create global temporary tablename(column list)
  on commit preserve rows; 提交保留數據 會話臨時表
  on commit delete rows; 提交刪除數據 事務臨時表
  臨時表是相對於會話的別的會話看不到該會話的數據
  
  [Q]怎麼樣在PL/SQL中執行DDL語句
  [A]i以下版本dbms_sql包
  i以上版本還可以用
  execute immediate sql;
  dbms_utilityexec_ddl_statement(sql);
  
  [Q]怎麼樣獲取IP地址
  [A]服務器(以上)utl_inaddrget_host_address
  客戶端sys_context(userenvip_address)
  
  [Q]怎麼樣加密存儲過程
  [A]用wrap命令如(假定你的存儲過程保存為asql)
  wrap iname=asql
  PL/SQL Wrapper: Release Production on Tu
From:http://tw.wingwit.com/Article/program/Oracle/201311/16737.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.