Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Ветвление в табличной функции (Inline Table-Valued Function)

Здравствуйте.
Помогите, пожалуйста, новичку.
Как в создаваемую табличную функцию (Inline Table-Valued Function) добавить ветвление в зависимости от введенного параметра. Ниже в коже прилагаю пример создания функции MonthList, которая выводит список месяцев в зависимости от введенного параметра - номера квартала (@QNum). Значение по умолчанию 0. Необходимо сделать так, чтобы при использовании параметра по умолчанию, функция возвращала полный список месяцев.

Заранее благодарен за помощь.

IF OBJECT_ID(N'Months', N'U') IS NOT NULL
	DROP TABLE Months;
GO
CREATE TABLE Months (MonthNum tinyint, QuarterNum tinyint);
GO
DECLARE @month as tinyint
SET @month = 1
WHILE @month <=12
	BEGIN
		INSERT INTO Months(MonthNum, QuarterNum)
		VALUES (@month
				,CASE
					WHEN @month <=3 THEN 1
					WHEN @month <=6 THEN 2
					WHEN @month <=9 THEN 3
					WHEN @month <=12 THEN 4
				END);
		SET @month = @month+1
	END;
GO

IF OBJECT_ID(N'MonthList', N'IF') IS NOT NULL
	DROP FUNCTION MonthList;
GO

CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = @QNum
	);
GO

SELECT * FROM MonthList(DEFAULT);
GO
Ответ: aleks2,

some people never learn.

Для тех, кто не писатель: в приведенном примере по плану видно, что дополнительное условие очень даже убрано оптимизатором внутрь функции. Для активных членов лиги лени - вот запрос, переделанный под использование функции (чтоб уж наверняка)

create table ttt
(
	id int, n int, primary key(n,id)
)

insert into ttt
select top(12) ROW_NUMBER() over(order by 1/0), 1
from master..spt_values
	union all
select top(12) ROW_NUMBER() over(order by 1/0), 2
from master..spt_values

go

create function f(@x int) returns table as
return (
	select * from ttt
	where @x = 0
		union all
	select * from ttt
	where @x <> 0 and id = @x
)
go


declare @x int = 5

select * from dbo.f(@x)
where n = 1

drop function dbo.f
drop table ttt
Вопрос: Как исправить запрос с table-valued функцией?

Здравствуйте!

Есть запрос
SELECT P.C_OKVED, o.LINK 
FROM CD_Partners P, SF_Text_To_VarcharTable(P.C_OKVED) o
WHERE
	p.LINK = '3875273B-57E5-4F56-B398-96B88413EB95'

, который возвращает ошибку "Не удалось привязать составной идентификатор "P.C_OKVED"."
CD_Partners - это таблица с полем P.C_OKVED.
SF_Text_To_VarcharTable - это table-valued функция, которая строится по значению в P.C_OKVED.

Как исправить запрос, чтобы на выходе получить P.C_OKVED и соответствующие ему значения столбца LINK table-valued функции SF_Text_To_VarcharTable?
Ответ: Спасибо!
Вопрос: type, table и function pipelined

Добрый день. Есть тип, на основании него создаю таблицу. Затем хочу реализовать функцию которая вернет "таблицу" с помощью pipe row и получаю "expression is of wrong type". Причем если таблицу создавать явно(с описанием столбцов), то такой проблемы нет. Если кто-то подскажет варианты решения, то буду очень благодарен

CREATE OR REPLACE TYPE typeTest AS OBJECT 
(fio VARCHAR2(200)
  ,ID NUMBER(20));

CREATE GLOBAL TEMPORARY TABLE tmp_table_test OF typeTest;

create or replace package pTest as
    TYPE pTypeTest IS TABLE OF tmp_table_test%ROWTYPE;
    function fTest return pTypeTest pipelined;
   end;
  
create or replace package body pTest as
function fTest return pTypeTest pipelined
    is
	begin
		FOR curr IN (SELECT * FROM tmp_table_test) 
		LOOP	
			pipe row (curr);
		END LOOP;
      return;
    end;
end;
 

Предупреждение: Тело пакета создано с ошибками компиляции.

>show errors
Ошибки для PACKAGE BODY PTEST:

LINE/COL ERROR
-------- -----------------------------------------------------
7/4      PL/SQL: Statement ignored
7/14     PLS-00382: expression is of wrong type
Ответ:
Ярослав Батозский
Я говорил вот про что:
Ты можешь подразумевать всё что угодно. Но здесь вполне конкретная тема, в которой ты себя проявил с наихудшей стороны..
Вопрос: upgrade: function digest does not exist

Всем привет.
Делаю upgrade c 9.4 на 9.5, но при апдейте вылазит ошибка в теме. Странно следующее: на старой базе extension pgcrypto не включён. Ладно, включаю и апгрейджу так:
1) на старой базе добавляю необходимый extension:
create extension pgcrypto;

проверяю работу:
postgres=# SELECT digest('blah', 'sha1');
                   digest
--------------------------------------------
 \x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

2) инициализирую пустую базу для 9.5:
/usr/postgres/9.5/bin/amd64/initdb /var/postgres/9.5/data_64 -E utf8

3) на новой базе добавляю необходимый extension:
create extension pgcrypto;

проверяю работу:
postgres=# SELECT digest('blah', 'sha1');
                   digest
--------------------------------------------
 \x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

4) запускаю upgrade:
/usr/postgres/9.5/bin/amd64/pg_upgrade -d /var/postgres/9.4/data_64 -D /var/postgres/9.5/data_64 -b /usr/postgres/9.4/bin/amd64 -B /usr/postgres/9.5/bin/amd64 -v


Вот полный текст ошибки:

pg_restore: [archiver (db)] could not execute query: ERROR:  function digest(bytea, unknown) does not exist
LINE 2:     SELECT encode(digest(convert_to($1, 'UTF8')::bytea, 'sha...
                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
    SELECT encode(digest(convert_to($1, 'UTF8')::bytea, 'sha1'), 'hex')

CONTEXT:  SQL function "sha1" during inlining
    Command was:
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('553607281'...


То ли что-то забыл, то ли какой-то баг.
Заранее спасибо.
Ответ:
skeletor
function digest does not exist

function digest does not exist
Вопрос: Что делать с alter function with execute as owner?

Помогите неграмотному сделать первые шаги!!!

Есть функция:
alter function [rprt].[KlntTrn2]( 	
)
returns table
as
return
(SELECT [CONT] FROM [Table1])

Пытаюсь добавить "with execute as owner"
alter function [rprt].[KlntTrn2]( 	
)
returns table
with execute as owner
as
return
(SELECT [CONT] FROM [Table1])

Выдает ошибку:
Сообщение 487, уровень 16, состояние 1, процедура KlntTrn2, строка 24
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Сообщение 178, уровень 15, состояние 1, процедура KlntTrn2, строка 26
A RETURN statement with a return value cannot be used in this context.

Как это обойти? Очень не хочу писать конструкцию:
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END

Заранее спасибо!
Ответ:
b2w
К сожалению на схему dbo права давать нельзя.

никто и не дает. схеме rprt меняют овнера.
а ссылку тоже к сожалению прочесть не удалось?
Вопрос: IN vs. EXISTS + user function

Есть 2 таблицы и функция

drop table t1;
drop table t2;

create table t1 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=100000;
create table t2 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=10000;

create or replace function f(n number)
return number
is 
begin
  return 1;
end;
/


Пишем запрос:

select * from t2 where exists (select null from t1 where t2.n = f(t1.n))


План получается не ахти

Plan hash value: 2132554994
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   303 |   206   (1)| 00:00:03 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "T1" "T1" WHERE "F"("T1"."N")=:B1))
   3 - filter("F"("T1"."N")=:B1)


При этом, если переписать на IN, то всё OK

select * from t2 where t2.n in (select f(t1.n) from t1)


Plan hash value: 3077929639
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   606 |    54   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   101 |   606 |    54   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|   292K|    46   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T2"."N"="F"("T1"."N"))


Хинты USE_HASH, HASH_SJ не помогли.

Глюк оптимизатора?

P.S. 11.2.0.4 64-bit windows и linux
Ответ: В общем, проблема в функции.

Если заменить в 1м запросе user-defined функцию F на что-нибудь типа LENGTH, MOD, SIN и т.д., то запрос прекрасно UNNEST-ится.

трассировка 10053 говорит о том, что

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Invalid correlated predicates.
SU:   Validity checks failed.


Кроме того, там же многократно встречается следующее сообщение:

PL/SQL function (F) is not secure.


Беглый поиск по гуглу ничё полезного не дал.
Буду копать дальше.
Вопрос: PARTITION FUNCTION и PARTITION SCHEME

Создана давно схема и функция секционирования по годам.

CREATE PARTITION SCHEME [yearRange] AS PARTITION [yearRange] TO ([2012], [2013], [2014], [2015], [2016], [2017], [2018], [2019], [2020], [SECONDARY])

CREATE PARTITION FUNCTION [yearRange](smallint) AS RANGE LEFT FOR VALUES (2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)


Чтобы добавить 2021, 2022 и т.д., как я понимаю, надо написать так:
ALTER PARTITION FUNCTION yearRange ()
SPLIT RANGE (2021);
ALTER PARTITION SCHEME yearRange
NEXT USED [2021]
ALTER PARTITION FUNCTION yearRange ()
SPLIT RANGE (2022);
ALTER PARTITION SCHEME yearRange
NEXT USED [2022]
и т.д. ????


А как добавить слева 2009, 2010, 2011 (для схемы и функции)?
Чтобы было так VALUES (2009, 2010, 2011,2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021,2022...)

Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 Standard 6.2 <X64> (Build 9200: ) (Hypervisor)
Ответ:
Marchuk
Читал. Не совсем понял как правильно сделать, добавить меньшее значение.

split делит любую "секцию" функции секционирования, хоть первую, хоть последнюю, хоть "среднюю"
Вопрос: update error "can't update table in stored function/trigger"

Добрый день, подскажите пожалуйста, разбираюсь в чужой программе и необходимо дописать запрос.
UPDATE image t0 LEFT JOIN user_active_count t1 ON t0.user_id=t1.user_id SET t0.rating=t0.rating-(t1.rating*0.001) 

Хочу в таблице image уменьшить значение в поле reating, в зависимости от рейтинга пользователя из таблицы user_active_count.
Вылезает ошибка
автор
can't update table in stored function/trigger because it is already used by statement which invoked this stores function/trigger

Получается что мешает какой то тригер, только как определить какой, их в базе не мало, возможно у меня запрос просто некоректно собран
Ответ:
miksoft
darlov
Я может не ясно выразился, почему все молчат?
Действительно, не все ясно. Да и праздники - многих нет, мне лень :)

Что за триггер? И зачем в запросе LEFT, хотя случай t1.rating IS NULL вы никак не обрабатываете.

Тригер по update таблицы image
LEFT - присоединяю к таблице image рейтинг пользователя, он есть у всех, не может быть NULL
Вопрос: Create function(array like "in", dt)

Товарищи и профи, салют!
Сталкнулся с интерестной задачкой:
Имеется функция типа (уже написаны):
1. Иерархические справочник Бизнес-единиц через сцепку Parent_key и Key
2. CREATE FUNCTION ParentsAndChildsBUs(@BUnit int, @curDate datetime)
RETURNS @BU TABLE ([key] int)

Но здесь у нас @BUnit имеет тип INT.
А у меня переменная @BUnit передается не как INT а как массив (не знаю как точно, типа IN)
одним словом на обычном запросе это работает как ... where BU in (@BUnit )

Вот и необходимо както (как? - я хз) вывернуться и написать такую функцию, где будет вместо single стоять Multi
Ответ:
НиколайСН
но на входе я ХЗ что может придти в переменной: или "1" или "2,5,8,23,75.."
Ну, то есть переменная всё таки одна? И вызов получается
SELECT *  
from AllCreditorsForBUnit(одна переменная, другая переменная) 
?
Вот теперь посмотрите, какого типа переменная, в которой приходит
НиколайСН
или "1" или "2,5,8,23,75.."
Вопрос: Internal и external function с одинаковым названием

FB 3.0. В базе есть UDF с именем FORMAT_DATE. И я создал FUNCTION с именем FORMAT_DATE. Теперь я не могу сделать вызов UDF, так как она перекрывается обычной функцией. Надо ли идти с этим в трекер?
Ответ:
Симонов Денис
В UDF ты не можешь нормальными способами:


в скольких процентах случаев это реально нужно?

Arioch
простота [UDF] - сама по себе достоинство.