nkurilog

2019-01-13

Oracleで有効数字を取得する。

基本的な考え方

ある数値の桁数を取得するには常用対数を用います。
FLOOR(LOG(10, n)) は次のとおり0以外の数値が最初に出現する桁を表します。

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

注: LOG(10, 0) は次のエラーを発生させます。: ORA-01428: 引数'0'が有効範囲外です

したがって、ROUND(n, - FLOOR(LOG(10, n))) で最初の1桁で丸めを行った値を取得することができます。

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

最初の d 桁で丸めるには、ROUND(n, d - 1- FLOOR(LOG(10, n)))とします。

関数

以上を関数にまとめると次のようになります。

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        ROUND(n, d - 1 - FLOOR(LOG(10, n)));
    END IF;
END;

偶数丸めを行うには、ROUND 関数の代わりに ROUND_HALF_EVEN関数を使います。

注: Oracle Database 18c からROUND_TIES_TO_EVEN 関数が使えます。

2019-01-12

Oracleで偶数丸め（JIS丸め）

Oracle標準のROUND 関数は四捨五入です。
偶数丸め（JIS丸め、銀行丸め）を行うには関数を自作する必要があります。

基本的な考え方

単純な整数への丸めを行うケースを考えます。

端数がちょうど0.5かどうかを判定するため、絶対値を1で割ります。
余りが0.5のとき偶数丸めを行う必要がありますが、その他の場合は四捨五入と同じですので、ROUND関数が使えます。
実は、偶数丸めを行うためには、絶対値を1でなく2で割った方が良いです。
2で割った場合、余りが0.5または1.5のときに偶数丸めが必要となります。
余りが0.5のときは切り捨て（TRUNCATE）を行い、余りが1.5のときは切り上げ（四捨五入ですのでROUND関数が使えます）を行うことで偶数丸めが実現できます。

関数

以上を関数にまとめると、以下のようになります。

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;

Oracle Database 18c から ROUND_TIES_TO_EVEN 関数が実装されました。

ROUND_TIES_TO_EVEN (数値)

docs.oracle.com

2018-11-28

Get Significant Figures in Oracle

Basic Idea

You can use common logarithm (i.e. log base 10) to evaluate the number of digits.
FLOOR(LOG(10, n)) indicates the decimal places of the first non-zero digit as follows.

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

Note: LOG(10, 0) causes an error: ORA-01428: argument '0' is out of range

Therefore, you can get the value rounded to the first digit of a given number n by ROUND(n, - FLOOR(LOG(10, n))).

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

In order to round to first d digits, you can modify as ROUND(n, d - FLOOR(LOG(10, n)) - 1).

Function

These can be summarized as the following function.

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        RETURN ROUND(n, d - FLOOR(LOG(10, n)) - 1);
    END IF;
END;

If you want to round half to even, use ROUND_HALF_EVEN function instead of the standard ROUND function.
Note: From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

2018-11-27

Round Half to Even in Oracle

Oracle standard ROUND function is rounding away from 0.
In order to round half to even (aka bankers’ rounding) in Oracle, you have to create a custom function.

Basic idea

Consider the case of rounding to an integer.

To judge whether the number is half-way between two integers, you have to divide the absolute value by 1 and see the remainder.
If the remainder is 0.5, you need to round to even. Otherwise, you can use the standard ROUND function
Actually, you had better divide the absolute value by 2 rather than 1 in order to round half to even.
In this case, you need to round to even when the remainder is 0.5 or 1.5.
When the remainder is 0.5, you have to round towards 0 (i.e. truncate).
When the remainder is 1.5, you have to round away from 0. (You can use the standard ROUND function.)

Function

These ideas can be summarized as the following function.

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;

From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

ROUND_TIES_TO_EVEN (number)

docs.oracle.com

2018-11-19

ある文字を含むが、ある文字は含まない正規表現

ある文字を含むが、ある文字は含まない正規表現をネットで検索すると、
（以下、fooという文字を含み、barは含まない例）

/^(?!.*bar).*(?=foo).*$/

または、

/^(?=.*foo)(?!.*bar).*$/

がヒットするのですが、肯定先読みを使わない以下の書き方がパフォーマンスが良いようです。

/^(?!.*bar).*foo.*$/

参考

regex - Regular expression that both includes and excludes certain strings in R - Stack Overflow

stackoverflow.com

2018-02-28

Oracle 12c SCOTTユーザーの権限を確認する

前の記事「Oracle CONNECTおよびRESOURCEロールのまとめ」を書いていて、ふと気になったのですが、サンプル・スキーマのSCOTTユーザーの権限ははどうなっているのでしょうか。

SCOTTユーザーの権限は非推奨の CONNECTロールとRESOURCE ロールを使用していたはずです。Oracle12c では非推奨なので使われなくなっているのでしょうか。

サンプルスキーマの作成スクリプト ORACLE_HOME/rdbms/admin/utlsampl.sql を見ると、

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

となっていました。

まだ、非推奨の RESOURCE ロールと CONNECT ロールが使われているようですね。
また、12c から RESOURCE ロールでは付与されなくなったUNLIMITED TABLESPACE 権限が別に付与されています。コメントに、

Rem mmoore 04/08/91 - use unlimited tablespace priv

と記載されていますので、だいぶ昔から RESOURCE ロールとは別に付与されていたようです。

データディクショナリで確認してみます。

SQL> select * from user_role_privs;

GRANTEE    GRANTED_ROLE
---------- -------------
SCOTT      RESOURCE
SCOTT      CONNECT

SQL> select * from user_sys_privs;

USERNAME  PRIVILEGE            
--------- ---------------------
SCOTT     UNLIMITED TABLESPACE

現セッションで利用できる権限を見てみます。

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE CLUSTER
CREATE TABLE
UNLIMITED TABLESPACE
CREATE SESSION

昔、CONNECT ロールに付与されていた

ALTER SESSION
CREATE DATABASE LINK
CREATE SYNONYM
CREATE VIEW

は付与されてないですね。

非CDB環境で確認しているのですが、非CDBでは必要ないはずのコンテナの切替権限 SET CONTAINER が付与されています。
CONNECT ロールに新たに付与されているようです。

SQL> select * from role_sys_privs where ROLE = 'CONNECT';
ROLE                    PRIVILEGE
-------------------- --------------
CONNECT             SET CONTAINER
CONNECT             CREATE SESSION