Замена sys_refcursor в bigquery
В bigquery у меня есть требование передать несколько значений столбцов нескольких строк в качестве одного выходного параметра в процедуре. В PLSQL это можно сделать с помощью sys_refcursor. Есть ли выход в bigquery. Использование ниже не возвращаемых значений для нескольких строк.
CREATE OR REPLACE PROCEDURE `linear-charmer-344806.2143.Test1`(v_name STRING, OUT v_dept STRING,out v_id numeric)
begin
set (v_dept,v_id) =(select as struct dept,roll from `linear-charmer-344806.2143.Employee`
where name = v_name);
end;
Ответы 1
Я думаю, у вас не должно возникнуть проблем с выполнением такого запроса. Вам просто нужно быть осторожным с типом данных параметров, которые вы передаете, и именем, которое вы маркируете переменные. Что касается курсоров, я думаю, что в BigQuery вы можете использовать альтернативы, например, петли, которые могут предложить аналогичное рабочее поведение.
Чтобы воспроизвести ваш случай, я выполняю следующее:
CREATE OR REPLACE TABLE `projectid.dataset.table`(
fullVisitorId STRING,
type STRING,
visitId INT64
)
insert into `projectid.dataset.table`(fullVisitorId,type,visitId)values("4460792451076786377","PAGE",1496345687);
insert into `projectid.dataset.table`(fullVisitorId,type,visitId)values("8359479821939482641","PAGE",1496356529);
insert into `projectid.dataset.table`(fullVisitorId,type,visitId)values("9410889263646527987","WEB",1496357061);
insert into `projectid.dataset.table`(fullVisitorId,type,visitId)values("8801528329001387919","WEB",1496336519);
Создать процедуру
CREATE OR REPLACE PROCEDURE `projectid.dataset.proc_test`(v_visitorid STRING, OUT v_type STRING,OUT v_id numeric)
begin
set (v_type,v_id) =(select as struct type,visitId from `projectid.dataset.table`
where fullVisitorId = v_visitorid);
end;
Вызов процедуры таким образом заполнит переменные type и id результатом.
DECLARE type STRING;
DECLARE id numeric;
call `projectid.dataset.proc_test`("4460792451076786377",type,id);
select type,id;
Ниже я зацикливаюсь, используя оператор сценария for, через значения table, который извлекает fullVisitorId, и использую его позже для выполнения вызова процедуры.
DECLARE seekerid STRING;
DECLARE out_type STRING;
DECLARE out_id numeric;
FOR record IN
(SELECT fullVisitorId from `projectid.dataset.table`)
DO
SET seekerid = record.fullVisitorId;
call `projectid.dataset.proc_test`(seekerid,out_type,out_id);
select out_type,out_id;
END FOR;
ОБНОВЛЕНИЕ 23.03.2022: Ниже приведен пример создания процедуры с переменными структуры массива в качестве входных данных. Обратите внимание, что вам необходимо проверить, соответствуют ли таблица и переменные вашим входным данным и структуре данных.
CREATE OR REPLACE PROCEDURE `projectid.dataset.proc_test_struct`(v_inputs ARRAY<STRUCT<type STRING,visitId INT64>>, OUT v_fullVisitorId STRING)
begin
declare v_struct STRUCT<type STRING,visitId INT64>;
set v_struct = (select (type,visitId) from unnest(v_inputs));
set v_fullVisitorId = (select fullVisitorId from `projectid.dataset.table` where type = v_struct.type and visitId = v_struct.visitId);
end;
Вот часть, где я вызываю процедуру и заполняю ее переменной struct.
declare v_inputs ARRAY<STRUCT<type STRING,visitId INT64>>;
declare v_fullVisitorId STRING;
set v_inputs = [("PAGE", 1496356529)];
call `projectid.dataset.proc_test_struct`(v_inputs,v_fullVisitorId);
select v_fullVisitorId;
Я обращаюсь к официальной документации, поэтому, если вы хотите копнуть дальше, вы можете проверить следующие ссылки:
Я обновил свой ответ, пожалуйста, посмотрите. Это заполнит рамки вопроса. Пожалуйста, если у вас есть дополнительные вопросы, не связанные с исходным вопросом, я предлагаю вам открыть новый вопрос.
Кроме того, я предлагаю проверить документацию о datatypes о массивах и структурах, поскольку у нее есть некоторые ограничения на большие запросы.
извините, но это не сработало. Я пробовал по-другому, например CALL proc `((select ARRAY<STRUCT<roll numeric, dept STRING>>[(1,'ECE'),(2,'ECE')] ), id); выбрать идентификатор;
вам удалось заставить его работать тогда? Я обновил вопрос и проверил на своей стороне, и это сработало. тем не менее, я рад, что вам удалось заставить его работать на вашей стороне.
Да, спасибо за вашу помощь. Очевидно, они помогли мне получить хотя бы представление.
Как я могу передать массив структуры в качестве входного параметра? Что-то вроде: CREATE OR REPLACE PROCEDURE
projectid.dataset.proc_test(v_visitor ARRAY<struct<type string, visitId int64>>, OUT v_id numeric) begin --your code end;--------- когда я вызываю процедуру с помощью CALLprojectid.dataset.proc_test(("СТРАНИЦА",1496345687), id); я получаю сообщение об ошибке