Спрятать колонку

Oracle. Code Analyze

Партия сказала, что нужно найти и цинично воспользоваться софтом для проверки кода на "корректность и правильность". Не могу сказать, что это очень правильная мысль, потому что большинство данных программ (не только связанных с СУБД) построено по принципу внутренних правил. Например, может быть правило, согласно которому длина имени переменной не должна быть меньше пяти символов. Это делается для того, чтобы имена были "говорящими", а не просто A1, но будет ругаться на обычные переменные, используемые для цикла вроде I, J. Раз переубедить начальство не получается, то приходится выполнять то, что говорят. Далее я приведу те способы отлова "некорректностей", которые я нашел и мне помогли найти люди с sql.ru.

1. Предупреждения компилятора.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER PROCEDURE FOO COMPILE;
SHOW ERRORS;

Этим способом можно выловить предупреждения такие, как: unreachable code, unusable vars, function returns without value. Что, согласитесь, уже очень хорошо, но может быть недостаточно.

2. CodeXpert
Здесь все просто и понятно: нажал на кнопку получил результат.

3. Анализ перегруженных функций в пакетах
Есть также набор пакетов, которые предназначены, насколько я понял, для анализа перегруженных функций, но разбираться с ними долго и тратить время желания большого нет.
ссылка

Пожалуй, на этом все.
Некоторые "некорректности", такие как: незакрытые курсоры, отсутствие секции EXCEPTION можно ловить лишь опытным глазом программиста.

JFYI

Oracle. Синонимы и DDL, DML

В Oracle есть такая замечательная возможность - создавать синонимы, а потом их использовать. Если не все их создают, то уж пользуются ими точно все: уж просто вычитывая данные из таблицы, к примеру, ALL_OBJECTS.

Ковыряясь в синонимах, обнаружил небольшую странность в их поведении. С ними нельзя проводить DDL операций, тогда как DML разрешены. Рассмотрим небольшой пример.

Создадим таблицу table_name и синоним для нее table_synonym

CREATE TABLE table_name(
    id number
);
 
CREATE synonym table_synonym FOR table_name;

Выполним к ней запрос:

SELECT * FROM table_synonym

работает и это правильно, так и должно быть.
Попытаемся теперь добавить столбец в эту таблицу, используя синоним:

ALTER TABLE table_synonym
ADD (name varchar2(20));

получаем ошибку: table or view does not exist, что правильно Такая же операция без использования синонима выполнится корректно:

ALTER TABLE table_name
ADD (name varchar2(20));

Работает это так потому, что синоним не есть таблица, а лишь объект, с помощью которого можно обратится к данным. И если есть право ALTER TABLE, то нет права ALTER_TABLE_through_synonym, что может быть и не совсем логично, потому что, имея права на изменения объекта, мы могли бы изменять этот объект через его синоним, но такова реализация.

добавлено:
и все же это странно. у нас ведь нет права на delete from synonym,а удалить мы можем данные без проблем. в то время как такая же операция с alter table synonym не проходит. странно, пока нормального объяснения не нашел.

Oracle. Когда курсор возвращается параметром

Как посмотреть содержимое курсора, который возвращается как OUT параметр из фукнции?

Зачастую на проектах бывает, что ораклисты пишут back-end и не имеют доступа на front-end, за исключением тестовой среды, на которой не развернешься особо, потому что там рулят всем тестеры. В этом случае у вас не будет возможности запустить клиент и посмотреть на набор данных, которые вернет ваша функция.

Долго копался в Toad 9.6, но так и не нашел возможности просмотреть содержимое курсора. Если он возвращается как результат - можно, как параметр - не получается. В этом случае к нам на помощь приходит старый, добрый SqlPlus:

var ds refcursor; -- объявляем переменную;
DECLARE
  res NUMBER; -- результат, который возвращает вызываемая функция
BEGIN
  res := call_function(:ds); -- непосредственно вызов
END;
print :ds; -- вывод курсора на экран

В при этом нужно не забывать о форматировании вывода, потому что ты работаем в плюсе; про символ ":", потому что это переменная.

Быстрого дебага

Oracle и NULL

Одним из небольших подводных камней в Oracle может является Null и операции с ним.
Для работы с ним нужно четко уяснить два правила:
1. Null <> Null всегда и везде
2. Помнить о преобразовании типов.

Рассмотрим далее пример.

Создадим таблицу:

CREATE TABLE test_null(
  v_is_null varchar2(10)
);

прогоним скрипт:

INSERT INTO test_null(v_is_null) VALUES(NULL);
INSERT INTO test_null(v_is_null) VALUES('  ');
INSERT INTO test_null(v_is_null) VALUES('');

сколько строк вернут следующие запросы?
1.

SELECT  * FROM test_null
WHERE v_is_null = NULL

2.

SELECT  * FROM test_null
WHERE v_is_null = ''

3.

SELECT  * FROM test_null
WHERE v_is_null = '  '

Правильные ответы:
1. ноль строк. Потому что Null <> Null
2. ноль строк. Потому что '' (пустая строка) всегда трактуется как Null, далее смотрите пункт 1)
3. одна строка.

Помните о преобразованиях типов.

Поиск строки в таблице

Поводом для этой записи служит довольно рутинная задача: проверить, существует ли запись в таблице. Я бы до конца своей жизни пользовался запросом типа:

SELECT count(1) FROM all_objects 
WHERE object_name = 'SOME_OBJECT_NAME'

несмотря на то, что он весьма плох, из-за использования функции count.
Мне же показали другой вариант, который работает гораздо быстрее.
Это код на pl/sql:

DECLARE
    i_found PLS_INTEGER := 0;
BEGIN
   BEGIN
     SELECT 1 / 0 INTO i_found  FROM all_objects
     WHERE object_name = 'SOME_OBJECT_NAME';
     EXCEPTION
        WHEN ZERO_DIVIDE THEN 
           DBMS_OUTPUT.put_line('record found');
        WHEN NO_DATA_FOUND THEN 
           DBMS_OUTPUT.put_line('record not found');
     END;
   END;
END;

Очень оригинальная идея использования ошибки деления на ноль. Ислючение создается всегда, вопрос лишь в том, какое это будет исключение. Если это ZERO_DIVIDE, то получается, что запись есть, потому что произошла операция деления. Если NO_DATA_FOUND, то 1 / 0 никогда не отработал и в переменную i_found ничего не занеслось.
Этот метод работает примерно раза в два быстрее (зависит от количества данных), что весьма существенно при большой повторяемости данного запроса.
Мне стало интересно, существует ли более быстрый вариант определения наличия записи в таблице. Не может же быть так, что для нахождения записи мы создавали исключительную ситуацию, тратили время на его обработку и получали самый быстрый способ.

В Oracle есть стандартный оператор EXISTS, который позволяет проверять наличие записей в курсоре, а еще есть CASE, который помогает ветвить логику в запросе. И, как результат работы, родился следующий запрос:

SELECT 
    case
       when EXISTS(SELECT 1 FROM all_objects WHERE object_name = 'SOME_OBJECT_NAME') THEN 1
       else 0
   end
FROM dual

этот запрос возвращает 0, если записей нет и 1, если есть. Сначала, во вложенном запросе, мы ищем совпадение по условию, а в самом проверяем - вернул ли вложенный запрос на что-либо. Работает быстрее второго варианта примерно на 20% (на аналогичной выборке).

Ложка дегтя в бочке меда, куда же без нее
С позиции программиста все выглядит великолепно - я нашел еще более быстрый вариант решения задачки, но потратил на это некоторое время. В данном случае затраты были невелики: 20минут рабочего времени не слишком много. Но всегда стоит помнить, что в погоне за подобной "оптимизацией" мы можем выиграть немного времени, но потерять очень много именно на поиск. Поэтому всегда сначала нужно оценить задачу и решить: стоит ли игра свеч, возможно, мы потеряем больше, чем приобретем.

Если сможете предложить более хороший способ нахождения строки - милости прошу, будет интересно посмотреть другое решение.

>> Читать далее

Oracle. Динамическая сортировка

небольшой хинт для использования сортировок.

задачка: предположим есть нам нужно сортировать данные и порядок сортировки указан в другой таблице. 1 - asc, 2 - desc. нужно написать запрос.

создадим таблицы и внесем в них данные:

CREATE TABLE master (
 id NUMBER, -- ключ для связки
 order_by NUMBER(1) -- указан порядок сортировки для дочерней таблицы 1 -> ASC, 2 -> DESC
); 
 
CREATE TABLE detail (
 id NUMBER, -- ключ для связки
 val NUMBER -- значение
); 
 
INSERT INTO master(id, order_by)
VALUES(1, 1);
INSERT INTO master(id, order_by)
VALUES(2, 2);
 
INSERT INTO detail(id, val)
VALUES(1, 2);
INSERT INTO detail(id, val)
VALUES(1, 1);
INSERT INTO detail(id, val)
VALUES(1, 4);
INSERT INTO detail(id, val)
VALUES(1, 3);
INSERT INTO detail(id, val)
VALUES(1, 5);
 
INSERT INTO detail(id, val)
VALUES(2, 2);
INSERT INTO detail(id, val)
VALUES(2, 1);
INSERT INTO detail(id, val)
VALUES(2, 4);
INSERT INTO detail(id, val)
VALUES(2, 3);
INSERT INTO detail(id, val)
VALUES(2, 5);
COMMIT;

запрос:

SELECT DETAIL.*
FROM MASTER, DETAIL
WHERE MASTER.ID = DETAIL.ID
ORDER BY DETAIL.ID, 
CASE MASTER.ORDER_BY
    WHEN 1 THEN DETAIL.VAL 
    ELSE 1
END ASC,
CASE MASTER. ORDER_BY
    WHEN 2 THEN DETAIL.VAL
    ELSE 1 
END DESC

здесь использована возможность участия в формировании условия ORDER BY оператором CASE.
первое условие задает сортировку по группе в целом.
второе условие проверяет указана ли сортировка по возрастанию, иначе возвращается 1, т.е ничего не делается.
третье условие проверяет указана ли сортировка по убыванию, иначе возвращается 1, т.е ничего не делается.

как видно, ничего сложного нет. условия можно комбинировать, добавлять и удалять поля и так далее.
матчасть спасет нас Smile

ну и напоследок:

DROP TABLE DETAIL;
DROP TABLE MASTER;

Oracle + HTML + XML + XSLT

предположим, перед нами стоит задача формирования html отчета. в дальнейшем его можно слить в файл или отослать кому-то мыло.
на мой взгляд есть два способа решения это задачи.

1. PL/SQL

это наиболее простой, быстрый и негибкий вариант. примером его реализации может служить следующая конструкция

DECLARE
   C_HTML CLOB := '';
   V_ROW VARCHAR2(4000) := '';
BEGIN
  FOR DS IN (SELECT * FROM TABLE) LOOP
    V_ROW := '<TD>' || DS.COL1 || '</TD><TD>' || DS.COL2 || '</TD>';
    C_HTML := C_HTML || '<TR>' || V_ROW || '</TR>';
  END LOOP;
  C_HTML := '<HTML><HEAD></HEAD></BODY>' || C_HTML || '</BODY></HTML>'
END;

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

2. XML + XLST

первая часть это непосредственно инициализация переменных, получение контекста и создание XML данных, основанных на нашем запросе.

DECLARE
    V_SQL                 VARCHAR2(4000);
    X_CONTEXT         DBMS_XMLGEN.CTXHANDLE := 0;
    X_XML_DATA       XMLType;
   X_HTML_OUTPUT  XMLType
   XSLT                    LONG;
BEGIN
   V_SQL := 'SELECT * FROM TABLE';
   X_CONTEXT := DBMS_XMLGEN.NEWCONTEXT(V_SQL);
   X_XML_DATA := DBMS_XMLGEN.GETXMLTYPE(X_CONTEXT, DBMS_XMLGEN.NONE);
 
END;

теперь в дело вступает XLST.
по-простому говоря, XSLT это набор правил (спецификация) для преобразований XML документов. все что остается нам лишь определить как наши данные из XML формата перетекут в HTML.

>> Читать далее
RSS-материал

Наверх