В свете "Скрытых особенностей..." серии вопросов, какие малоизвестные функции PL/SQL стали для вас полезными?
Изменить: Функции, специфичные для PL/SQL, предпочтительнее, чем функции синтаксиса Oracle SQL. Однако, поскольку PL/SQL может использовать большинство конструкций Oracle SQL, они могут быть включены, если они облегчают программирование в PL/SQL.
Вы можете переопределить переменные, вы можете назвать анонимные блоки, и вы все равно можете ссылаться на переопределенные переменные по имени:
PROCEDURE myproc IS
n NUMBER;
BEGIN
n := 1;
<<anon>>
DECLARE
n NUMBER;
BEGIN
n := 2;
dbms_output.put_line('n=' || n);
dbms_output.put_line('anon.n=' || anon.n);
dbms_output.put_line('myproc.n=' || myproc.n);
END anon;
END myproc;
Вы можете индексировать таблицы 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;
Поистине скрытая функция оракула - это функция OVERLAPS, но, вероятно, не очень разумно использовать любые неподдерживаемые функции.
select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);
Одна малоизвестная функция, с которой я имел большой успех, - это возможность вставки в таблицу с использованием переменной, объявленной как ее %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;
Несмотря на то, что он пережевывает немного больше табличного пространства redo, он, безусловно, делает более четким добавление данных (особенно в более крупные таблицы). Он также позволяет избежать множества переменных, необходимых для хранения каждого значения столбца, которое вы хотите вставить.
Процедуры и функции могут быть определены в блоках 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.
Возможно, недостаточно скрытый, но мне нравится инструкция 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>];
Мой ответ на Скрытые функции в 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.
Это процедурная конструкция PL/SQL, которую я использую много (кредиты Стивену Фейерштейну и Чэнь Шапире). Ассоциативный массив, используемый для chaching, но он не предварительно загружает все данные, но при необходимости получает данные из базы данных и помещает их в ассоциативный массив.
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
Динамический PL/SQL уродлив, но может делать некоторые интересные вещи. Например, имена можно рассматривать как переменные, которые я использовал ранее, чтобы пересекать переменные% rowtype, такие как массивы, и создать функцию, которая для имени данной таблицы возвращает курсор, который выбирает одну строку со значениями по умолчанию каждого столбца. Оба являются полезными обходными способами для денормализованных таблиц.
Вы можете имитировать CONTINUE, добавив метку в цикл, а затем GOTO:
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;