Ukryte funkcje PL / SQL [zamknięte]

Możesz indeksować tabele pl / sql według innych typów poza liczbami całkowitymi. W ten sposób można tworzyć struktury typu "Słownik", które mogą znacznie ułatwić odczyt kodu:

Przykład:

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]}prawdziwie ukrytą funkcją oracle jest funkcja nakładania się, ale prawdopodobnie nie jest zbyt mądre używanie nieobsługiwanych funkcji.

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

Jedną z mało znanych funkcji, z którą odniosłem wielki sukces, jest możliwość wstawiania do tabeli za pomocą zmiennej zadeklarowanej jako jej %ROWTYPE. Na przykład:

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;

Chociaż żuje się trochę więcej przerobić tabel, to z pewnością sprawia, wstawianie danych (zwłaszcza do większych tabel) znacznie jaśniejsze. Pozwala to również uniknąć mnogości zmiennych potrzebnych do przechowywania wartości każdej kolumny, którą chcesz wstawić.

Procedury i funkcje mogą być zdefiniowane w blokach DECLARE:

DECLARE

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

BEGIN

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

END;

Jest to przydatne do tworzenia samodzielnych skryptów.

Czy wiesz, że za pomocą opcji SAMPLE (K) możesz wybrać tylko próbkę składającą się maksymalnie Z k procent tabeli Oracle?

SELECT *
  FROM MASSIVE_TABLE SAMPLE (5);

Poprzednie polecenie pobiera zbiór randomiczny złożony z maksymalnie 5% rekordów przechowywanych w masywnej tabeli o nazwie MASSIVE_TABLE.

Może nie wystarczająco ukryte, ale uwielbiam Merge oświadczenie, które pozwala make upserts (insert lub update)

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>];

Moja odpowiedź na ukryte funkcje w Oracle jest tutaj istotna:

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.

To konstrukcja proceduralna PL / SQL, której często używam (podziękowania dla Stevena Feuersteina i Chena Shapiry). Tablica asocjacyjna używana do czachowania, ale nie ładuje wszystkich danych, ale pobiera dane z bazy danych w razie potrzeby i umieszcza je w tablicy asocjacyjnej.

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;

Przetestuj to:

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

1. nieudokumentowana funkcja: dbms_system.ksdwrt (zapisuje do plików alert/trace)
2. pakiet DBMS_SQL (jako przykład jego użycia patrz to pytanie
3. AUTHID CURRENT_USER klauzula
4. kompilacja warunkowa

Dynamiczny PL / SQL jest brzydki, ale potrafi zrobić kilka ciekawych rzeczy. Na przykład nazwy mogą być traktowane jako zmienne, których używałem wcześniej do przesuwania zmiennych typu % rowtype, takich jak tablice, i do tworzenia funkcji, która dla danej nazwy tabeli zwróci kursor, który wybierze pojedynczy wiersz z domyślnymi wartościami każdej kolumny. Oba są użytecznymi obejściami dla tabel denormalizowanych.

Możesz symulować kontynuację, dodając etykietę do pętli, a następnie GOTO tej etykiety:

declare
   i integer;
begin
   i := 0;

   <<My_Small_Loop>>loop

      i := i + 1;
      if i <= 3 then goto My_Small_Loop; -- => means continue
      end if;

      exit;

   end loop;
end;