Oracle 开发常用代码

基本

获取环境信息

USEREVN()方法返回用户环境的信息。

option 可以是 ENTRYID,SESSIONID,TERMINAL,ISDBA,LANGUAGE,CLIENT_INFO,LANG,VSIZE

--查看当前用户是否是 DBA，如果是返回 true

select userevn('isbda') from dual;

--返回 SESSION 会话 ID 

select userevn('sessionid') from dual;

--返回多人会话人数

select userevn('entryid') from dual;

--返回当前 instance 标志

select userevn('instance') from dual;

--返回当前语言环境

select userevn('language') from dual;

--返回当前语言环境的缩写，如 ZHS,EN 等

select userevn('lang') from dual;

--返回用户终端或机器标识

select userevn('terminal') from dual;

--返回传入字符的字节数，以 X 为例，返回的就是 X 字符的大小

select userevn(X) from dual;

‍

截取到某一个字符串前面的内容

select substr('210-1106000001',1,instr('210-1106000001','-')-1) from dual;

‍

设置当前会话日期格式

设置当前会话日期格式为英文

alter session set nls_date_language=american;

‍

开启控制台输出

用于使 dbms_output.put_line() 生效

set serveroutput on;
/

‍

关闭参数输入

使Oracle的参数符号 & 失效

set define off;
/

‍

数据库指标

查看占用表空间大小

SELECT se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) AS Space,
       tablespace,
       segtype,
       sql_text
  FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
 WHERE p.name = 'db_block_size'
   AND su.session_addr = se.saddr
   AND s.hash_value = su.sqlhash
   AND s.address = su.sqladdr
 ORDER BY se.username, se.sid;

‍

查看占用的磁盘空间

对于产生大量DML的场景，会需要监控磁盘空间。如果空间爆满会导致Oracle宕机。

SELECT TRUNC(first_time) "Date",
       to_char(first_time, 'Dy') "Day",
       COUNT(1) "Total(GB)",
       SUM(DECODE(to_char(first_time, 'hh24'), '00', 1, 0)) "h0",
       SUM(DECODE(to_char(first_time, 'hh24'), '01', 1, 0)) "h1",
       SUM(DECODE(to_char(first_time, 'hh24'), '02', 1, 0)) "h2",
       SUM(DECODE(to_char(first_time, 'hh24'), '03', 1, 0)) "h3",
       SUM(DECODE(to_char(first_time, 'hh24'), '04', 1, 0)) "h4",
       SUM(DECODE(to_char(first_time, 'hh24'), '05', 1, 0)) "h5",
       SUM(DECODE(to_char(first_time, 'hh24'), '06', 1, 0)) "h6",
       SUM(DECODE(to_char(first_time, 'hh24'), '07', 1, 0)) "h7",
       SUM(DECODE(to_char(first_time, 'hh24'), '08', 1, 0)) "h8",
       SUM(DECODE(to_char(first_time, 'hh24'), '09', 1, 0)) "h9",
       SUM(DECODE(to_char(first_time, 'hh24'), '10', 1, 0)) "h10",
       SUM(DECODE(to_char(first_time, 'hh24'), '11', 1, 0)) "h11",
       SUM(DECODE(to_char(first_time, 'hh24'), '12', 1, 0)) "h12",
       SUM(DECODE(to_char(first_time, 'hh24'), '13', 1, 0)) "h13",
       SUM(DECODE(to_char(first_time, 'hh24'), '14', 1, 0)) "h14",
       SUM(DECODE(to_char(first_time, 'hh24'), '15', 1, 0)) "h15",
       SUM(DECODE(to_char(first_time, 'hh24'), '16', 1, 0)) "h16",
       SUM(DECODE(to_char(first_time, 'hh24'), '17', 1, 0)) "h17",
       SUM(DECODE(to_char(first_time, 'hh24'), '18', 1, 0)) "h18",
       SUM(DECODE(to_char(first_time, 'hh24'), '19', 1, 0)) "h19",
       SUM(DECODE(to_char(first_time, 'hh24'), '20', 1, 0)) "h20",
       SUM(DECODE(to_char(first_time, 'hh24'), '21', 1, 0)) "h21",
       SUM(DECODE(to_char(first_time, 'hh24'), '22', 1, 0)) "h22",
       SUM(DECODE(to_char(first_time, 'hh24'), '23', 1, 0)) "h23",
       ROUND(COUNT(1) / 24, 2) "Avg"
  FROM gv$log_history
 WHERE thread# = inst_id
   AND TRUNC(first_time) >= to_date('2022/01/25', 'yyyy/mm/dd')
 GROUP BY TRUNC(first_time), to_char(first_time, 'Dy')

‍

查询锁住的表

SELECT dob.object_name "对象名称",
       lo.session_id "会话ID",
       vss.serial# "穿行",
       vss.action "职责",
       vss.module "表单",
       vss.blocking_session "等待其他会话ID",
       vss.client_identifier "用户",
       vql.sql_text "SQL语句",
       'alter system kill session ' || '''' || lo.session_id || ',' || vss.serial# || '''immediate;' "命令"
  FROM v$locked_object lo,
       dba_objects     dob,
       v$session       vss,
       v$process       vps,
       v$sql           vql
 WHERE lo.object_id = dob.object_id
   AND lo.session_id = vss.sid
   AND vss.paddr = vps.addr
   AND vql.sql_id(+) = vss.sql_id
   --AND OBJECT_NAME = 'AP_INVOICES_ALL'
 ORDER BY lo.session_id;

‍

工具

数字转换成大写

FUNCTION convert_money(input_nbr1 NUMBER) RETURN VARCHAR2 IS
    input_nbr_bak  NUMBER(20);
    num_character  VARCHAR2(200) := '零壹贰叁肆伍陆柒捌玖';
    unit_character VARCHAR2(400) := '分角圆拾佰仟万拾佰仟亿拾佰仟万拾佰仟亿';
    output_string  VARCHAR2(1000) := '';
    remain_nbr     NUMBER(20);
    bit_num        NUMBER(20);
    bit_unit       VARCHAR2(20);
    bit_indic      NUMBER(1) := 0;
    i              NUMBER(2) := 0;
    spe_unit       VARCHAR2(20) := 'A';
    sign_indic     VARCHAR2(10);
    input_nbr      NUMBER(38);
  BEGIN
    input_nbr := input_nbr1 * 100;
    IF input_nbr = 0 THEN
      RETURN '零圆整';
    ELSIF input_nbr > 0 THEN
      sign_indic    := '0';
      input_nbr_bak := input_nbr;
    ELSIF input_nbr < 0 THEN
      sign_indic    := '1';
      input_nbr_bak := -input_nbr;
    END IF;
    LOOP
      remain_nbr    := floor(input_nbr_bak / 10);
      bit_num       := input_nbr_bak - remain_nbr * 10;
      input_nbr_bak := remain_nbr;
      bit_unit      := rtrim(substr(unit_character,
                                    i + 1,
                                    1));
      IF bit_num > 0 THEN
 
        bit_indic := 1;
        IF i = 6
           OR i = 14 THEN
 
          spe_unit := '万';
        ELSIF (i >= 7 AND i <= 9)
              OR (i >= 15 AND i <= 17) THEN
 
          IF spe_unit != '万' THEN
 
            output_string := '万' || output_string;
            spe_unit      := '万';
          END IF;
        END IF;
        output_string := substr(num_character,
                                bit_num + 1,
                                1) || bit_unit || output_string;
      ELSE
 
        IF bit_indic = 1 THEN
 
          output_string := '零' || output_string;
        END IF;
        IF bit_unit IN ('圆',
                        '亿') THEN
 
          spe_unit      := bit_unit;
          output_string := bit_unit || output_string;
        END IF;
        bit_indic := 0;
      END IF;
      i := i + 1;
      EXIT WHEN input_nbr_bak = 0;
    END LOOP;
    IF MOD(input_nbr,
           100) = 0 THEN
 
      output_string := output_string || '整';
    END IF;
    IF sign_indic = '1' THEN
      output_string := '负' || output_string;
    END IF;
    RETURN output_string;
  END;

‍