Как заполнить пользовательскую запись со значениями по умолчанию?
TL; DR:
Как я могу объявить определяемый пользователем тип записи, чтобы, если я не заполнил одно из полей, это поле соответствовало его DEFAULT ?
Подробности:
В спецификации пакета я определяю следующие типы записей и таблиц:
/* set up a custom datatypes that will allow us to pass an array of values into CCD_UI procedures and functions */
TYPE RECORD_OPTION_ATTRIBUTES IS RECORD(
option_name VARCHAR2(200) NOT NULL DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
option_value VARCHAR2(200) NOT NULL DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
option_selected_ind NUMBER(1) NOT NULL DEFAULT '0',
option_class VARCHAR2(200) DEFAULT NULL,
option_attributes VARCHAR2(200) DEFAULT NULL
);
TYPE TABLE_OPTION_ATTRIBUTES IS TABLE OF RECORD_OPTION_ATTRIBUTES
INDEX BY BINARY_INTEGER;
В теле пакета у меня очень похожая функциональность:
PROCEDURE populate_user_defined_table()
AS
v_criteria_pairs TABLE_OPTION_ATTRIBUTES;
BEGIN
SELECT some_column1 AS option_name, some_column2 AS option_value, some_column3 AS selected_ind,
some_column4 AS option_class
BULK COLLECT INTO v_criteria_pairs
FROM Some_Table
WHERE some_column='whatever';
END;
Зоркий глаз заметит, что я не вставляю никаких значений в option_attributes поле; Я заполняю только 4 из 5 доступных полей.
Когда я пытаюсь скомпилировать этот пакет, я получаю следующую ошибку от тела пакета:
PL / SQL: ORA-00913: слишком много значений
Если я уроню option_attributes поле из RECORD_OPTION_ATTRIBUTES декларация, пакет скомпилируется.
Как я могу объявить тип записи, чтобы, если я не указал значение для option_attributes , это поле будет по умолчанию NULL ?
4 ответа
AFAIK согласно Oracle doc: "Чтобы установить для всех полей в записи значения по умолчанию, присвойте ей неинициализированную запись того же типа", и это их пример:
DECLARE
TYPE RecordTyp IS RECORD (field1 NUMBER,
field2 VARCHAR2(32) DEFAULT 'something');
rec1 RecordTyp;
rec2 RecordTyp;
BEGIN
-- At first, rec1 has the values you assign.
rec1.field1 := 100; rec1.field2 := 'something else';
-- Assigning an empty record to rec1
-- resets fields to their default values.
-- Field1 is NULL and field2 is 'something'
-- due to the DEFAULT clause
rec1 := rec2;
DBMS_OUTPUT.PUT_LINE
('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
field2 = ' || rec1.field2);
END;
/
Вы не можете при использовании select [bulk collect] into синтаксис. В комментарии вы сказали:
Было бы безумием, если оба эти утверждения верны: 1) пользовательские записи позволяют вам определять значения по умолчанию, и 2) вы должны заполнять каждое поле пользовательской записи.
Первое утверждение верно; вторая верна, только если вы назначаете всю запись из запроса.
Для переменной записи типа RECORD начальное значение каждого поля равно NULL, если вы не укажете другое начальное значение для него при определении типа.
Поэтому, если вы создаете переменную записи, устанавливаются значения по умолчанию:
declare
v_rec RECORD_OPTION_ATTRIBUTES;
begin
dbms_output.put_line(v_rec.option_name ||':'|| v_rec.option_value
||':'|| v_rec.option_selected_ind ||':'|| v_rec.option_class
||':'|| v_rec.option_attributes);
end;
/
INVALID NAME":INVALID VALUE":0::
PL/SQL procedure successfully completed.
Затем вы можете переопределить значения по умолчанию, индивидуально установив значения полей.
Если вы выберите в переменной записи, то
Для каждого столбца в select_list переменная записи должна иметь соответствующее, совместимое с типом поле. Столбцы в списке select_list должны отображаться в том же порядке, что и поля записи.
Это прямо не говорит о том, что вы не можете иметь меньше значений в списке выбора, чем тип записи, но второе предложение вроде бы подразумевает это; Вы случайно добавили свое дополнительное поле в конец записи, но ничто не мешало вам поставить его в начале, что более явно нарушило бы это. Не существует механизма для указания того, какой столбец в списке выбора соответствует какому полю в записи, поэтому необходимо указывать точно такое же число, того же типа, в том же порядке.
Значения из запроса используются для заполнения записи, всегда перезаписывая значения по умолчанию. Вы не можете не предоставить значение поля. (Даже если ваш запрос оценивает значение столбца в ноль, это все равно переопределяет значение по умолчанию; если ваш запрос сделал SELECT null AS option_name, ... Вы получите ошибку числа или значения ORA-06502, потому что поле не нулевое). Так что ни одно из ваших значений по умолчанию не применяется при использовании select into, с или без bulk collect,
К сожалению, вы либо добавите новые типы записей и таблиц с дополнительным полем (которое вы не сможете передать процедурам, ожидающим исходные типы, так что это, вероятно, непрактично; вы можете добавить функции перевода, но это просто что еще хуже), или, как предложил @MartinSchapendonk, примите удар и измените существующий код.
Вам может не потребоваться изменять что-либо, что обрабатывает только коллекцию / записи, так как они просто не будут смотреть на новое поле - хотя, вероятно, вы будете вносить некоторые изменения, или не было бы никакого смысла иметь поле вообще. И вам не нужно изменять что-либо, что создает записи напрямую, поскольку они получат значение по умолчанию, равное NULL, даже если это находится в цикле курсора (который не извлекается в переменную записи). Вам только (!) Нужно изменить способ заполнения коллекции / записи из запросов SQL, select into, select bulk collect into, или же fetch into,
TYPE RECORD_OPTION_ATTRIBUTES IS RECORD(
option_name VARCHAR2(200) NOT NULL DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
option_value VARCHAR2(200) NOT NULL DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
option_selected_ind NUMBER(1) NOT NULL DEFAULT '0',
option_class VARCHAR2(200) DEFAULT NULL,
option_attributes VARCHAR2(200) DEFAULT NULL
);
TYPE TABLE_OPTION_ATTRIBUTES IS TABLE OF RECORD_OPTION_ATTRIBUTES
INDEX BY BINARY_INTEGER;
PROCEDURE populate_user_defined_table()
AS
CURSOS cur IS -- cursor selecting values without last column
SELECT some_column1 AS option_name, some_column2 AS option_value,some_column3 AS selected_ind, some_column4 AS option_class
FROM Some_Table
WHERE some_column='whatever';
TYPE t_tmp_arr IS TABLE OF cur%rowtype index by pls_integer;
v_tmp_arr t_tmp_arr;
v_criteria_pairs TABLE_OPTION_ATTRIBUTES;
BEGIN
open cur;
fetch cur bulk collect into v_tmp_arr;
close cur;
for i in 1..v_tmp_arr.count loop
-- it's better to wrap it into a function which accepts one type of record and returns another one
v_criteria_pairs(i).option_name := v_tmp_arr(i).option_name;
v_criteria_pairs(i).option_value := v_tmp_arr(i).option_value;
v_criteria_pairs(i).option_selected_ind := v_tmp_arr(i).option_selected_ind;
v_criteria_pairs(i).option_class := v_tmp_arr(i).option_class;
end loop;
END;
Вот еще один вариант с типом объекта, который не является записью PL/SQL, но имеет такое же поведение, плюс дополнительные параметры для инициализации со значениями по умолчанию в конструкторе (используйте выражения и функции PL/SQL):
Определите новый тип с помощью конструктора:
CREATE OR REPLACE TYPE RECORD_OPTION_ATTRIBUTES AS OBJECT(
option_name VARCHAR2(200),
option_value VARCHAR2(200),
option_selected_ind NUMBER(1),
option_class VARCHAR2(200),
option_attributes VARCHAR2(200),
constructor function RECORD_OPTION_ATTRIBUTES(
in_option_name VARCHAR2 DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
in_option_value VARCHAR2 DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
in_option_selected_ind NUMBER DEFAULT '0',
in_option_class VARCHAR2 DEFAULT NULL,
in_option_attributes VARCHAR2 DEFAULT NULL
)
return self as result
);
В конструкторе используйте значения по умолчанию и можете использовать сложную логику инициализации. Пожалуйста, имейте в виду, что вы можете иметь несколько конструкторов.
create or replace type body RECORD_OPTION_ATTRIBUTES
as
constructor function RECORD_OPTION_ATTRIBUTES(
in_option_name VARCHAR2 DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
in_option_value VARCHAR2 DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
in_option_selected_ind NUMBER DEFAULT '0',
in_option_class VARCHAR2 DEFAULT NULL,
in_option_attributes VARCHAR2 DEFAULT NULL
)
return self as result
as
begin
self.option_name := in_option_name;
self.option_value := in_option_value;
self.option_selected_ind := in_option_selected_ind;
self.option_class := in_option_class;
self.option_attributes := in_option_attributes;
return;
end;
end;
/
Запустим тест sql:
select RECORD_OPTION_ATTRIBUTES(table_name, tablespace_name, ini_trans)
from all_tables
where owner = 'SYS'
and rownum <= 10;
Результаты проверки:
RECORD_OPTION_ATTRIBUTES(TABLE_NAME,TABLESPACE_NAME,INI_TRANS)(OPTION_NAME, OPTI
--------------------------------------------------------------------------------
RECORD_OPTION_ATTRIBUTES('WRR$_REPLAY_CALL_FILTER', 'SYSAUX', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$EXPRESS', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWMD', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWCREATE', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWCREATE10G', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWXML', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWREPORT', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('DUAL', 'SYSTEM', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('SYSTEM_PRIVILEGE_MAP', 'SYSTEM', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('TABLE_PRIVILEGE_MAP', 'SYSTEM', 1, NULL, NULL)
10 rows selected.
Как видите, последние 2 столбца имеют значение по умолчанию (в данном случае пустое). По сравнению с исходным SQL-запросом в вашем вопросе все, что вам нужно сделать, - это обернуть выбранные столбцы с помощью RECORD_OPTION_ATTRIBUTES().