Скрытые функции МН / [закрытого] SQL

Истинно скрытая функция оракула - это функция OVERLAPS, но, вероятно, не очень разумно использовать какие-либо неподдерживаемые функции.

select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);

Может быть, недостаточно скрыт, но мне нравится оператор Merge , который позволяет делать upserts (вставку или обновление)

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

Одна малоизвестная функция, в которой я добился большого успеха, - это возможность вставки в таблицу с использованием переменной, объявленной как ее % ROWTYPE . Например:

CREATE TABLE CUSTOMERS (
    id NUMBER,
    name VARCHAR2(100),
    birth DATE,
    death DATE
)

PROCEDURE insert_customer IS
    customer CUSTOMERS%ROWTYPE;
BEGIN
    customer.id := 45;
    customer.name := 'John Smith';
    customer.birth := TO_DATE('1978/04/03', 'YYYY/MM/DD');

    INSERT INTO CUSTOMERS VALUES customer;
END;

Хотя он занимает немного больше табличного пространства повтора, он, безусловно, делает вставку данных (особенно в большие таблицы) намного более понятной. Это также позволяет избежать множества переменных, необходимых для хранения значения каждого столбца, который вы хотите вставить.

Вы можете индексировать таблицы pl / sql по другим типам, кроме целых чисел. Таким образом вы можете создавать структуры, подобные «словарю», которые могут значительно упростить чтение вашего кода:

Пример:

DECLARE
  TYPE dictionary IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(100);
  dict dictionary;
BEGIN
  dict('NAME') := 'John Doe';
  dict('CITY') := 'New York';

  dbms_output.put_line('Name:' || dict('NAME'));
END;

1. Недокументированная функция: dbms_system.ksdwrt (записывает в файлы предупреждений / трассировки)
2. Пакет DBMS_SQL (в качестве примера его использования см. этот вопрос
3. Предложение AUTHID CURRENT_USER
4. Условная компиляция

Динамический PL / SQL некрасив, но может делать кое-что интересное. Например, имена можно рассматривать как переменные, которые я использовал ранее для обхода переменных% rowtype, таких как массивы, и для создания функции, которая для заданного имени таблицы будет возвращать курсор, который выбирает одну строку со значениями по умолчанию. каждого столбца. Оба являются полезными обходными путями для денормализованных таблиц.

Эту процедурную конструкцию PL / SQL я часто использую (спасибо Стивену Фейерштейну и Чену Шапире). Ассоциативный массив, используемый для чачинга, но он не загружает все данные заранее, но при необходимости получает данные из базы данных и помещает их в ассоциативный массив.

create or replace
PACKAGE justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE)
    RETURN hairstyles%ROWTYPE;
  TYPE hair_t IS TABLE OF hairstyles%ROWTYPE
    INDEX BY BINARY_INTEGER;
  hairs          hair_t;
END justonce;

create or replace 
PACKAGE BODY justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE) RETURN hairstyles%ROWTYPE
  IS
    return_value   hairstyles%ROWTYPE;
    FUNCTION hair_from_database RETURN hairstyles%ROWTYPE
    IS
      CURSOR hair_cur IS
      SELECT * FROM hairstyles WHERE code = code_in;
    BEGIN
      OPEN hair_cur;
      FETCH hair_cur INTO return_value;
      CLOSE hair_cur;
      RETURN return_value;
    END hair_from_database;
  BEGIN
    IF NOT (hairs.exists(code_in))
    THEN
      dbms_output.put_line('Get record from database');
      hairs (code_in) := hair_from_database;
    END IF;
    RETURN hairs (code_in);
  END hair;
END justonce;

Проверьте это:

declare
    h hairstyles%ROWTYPE;
begin
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description);
   end loop;
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description||' '||h.price);
   end loop;

end;
/

Get record from database
CREWCUT
Get record from database
BOB
Get record from database
SHAG
Get record from database
BOUFFANT
Get record from database
PAGEBOY
CREWCUT 10
BOB 20
SHAG 21
BOUFFANT 11
PAGEBOY 44

Мой ответ на Скрытые функции в Oracle уместен здесь:

Поскольку Apex теперь является частью каждой базы данных Oracle, эти служебные функции Apex полезны, даже если вы не 'Не используется Apex:

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,'|');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

PL/SQL procedure successfully completed.

Процедуры и функции могут быть определены в блоках DECLARE :

DECLARE

    PROCEDURE print(text VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.put_line(text);
    END;

BEGIN

    print('Yay!');
    print('Woo hoo!');

END;

Это удобно для создания автономных сценариев.

Знаете ли вы, что с опцией SAMPLE (K) вы можете ВЫБРАТЬ только образец, состоящий из K процентов таблицы Oracle?

SELECT *
  FROM MASSIVE_TABLE SAMPLE (5);

Предыдущий оператор извлекает случайный набор, состоящий из до 5% записей, хранящихся в массивной таблице MASSIVE_TABLE.