时间戳

首页 > 解决方案 > Oracle LIKE operator with DECODE

sql - Oracle LIKE operator with DECODE

问题描述

I am new to learning oracle and I am having some problems in understanding the syntax.

INSERT INTO TEMP_BILL_PAY_LWDAY_PL
            WITH tmp_acc AS (
              SELECT ac_id,ac_currency,ac_category,ac_co_code
              FROM account
              WHERE ac_co_code LIKE decode(Currency,'','%','ALL','%',Currency) AND ac_category = '9986'
            )

This is the oracle pl/sql statement, could anybody please tell me what this line means.

WHERE ac_co_code LIKE decode(Currency,'','%','ALL','%',Currency)

I dont understand what decode means here with LIKE. I would really appreciate some help.

标签： sqldatabaseoraclerelational-databaserdbms

解决方案

It's producing a search pattern on the fly. The logic uses DECODE() to decide:

  • If the value Currency is an empty VARCHAR ('') then use % as the pattern for the LIKE. The condition will be equivalent to:

    WHERE ac_co_code LIKE '%'

  • Else, if the value Currency is 'ALL' then use % as the pattern for the LIKE. The condition will be equivalent to:

    WHERE ac_co_code LIKE '%'

  • Else use the value of Currency as the pattern for the LIKE. The condition will be equivalent to:

    WHERE ac_co_code LIKE Currency

See Oracle Online Docs for details.

推荐阅读