sql - единична заявка за връщане на стойности, които не присъстват

Например, имате проста таблица само с една колона. т.е.

CREATE TABLE movies   (title VARCHAR2(255 BYTE))

настроен със следните данни:

INSERT INTO movies   (title) VALUES ('Scream');
INSERT INTO movies   (title) VALUES ('Blair Witch');
INSERT INTO movies   (title) VALUES ('Friday the 13th');
INSERT INTO movies   (title) VALUES ('Scary Movie');
INSERT INTO movies   (title) VALUES ('Hide and Seek');
INSERT INTO movies   (title) VALUES ('Alien vs Predator');

Има ли една заявка или PL/SQL, която ще направи следното динамично (т.е. без да се налага ръчно да прави „UNION select „scream“ from dual...“ за всяка стойност)?

Очевидно тази заявка е грешна, но схващате идеята:

Select * from movies
where title in (
'Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
)

Желаният резултат е запис за всяка стойност в клаузата „WHERE TITLE IN“, където записът не присъства в таблицата. т.е.

'Exorcist'
'Dracula'
'Saw'

sql database oracle plsql
person toop    schedule 04.11.2011    source източник
comment
Избраните редове по всички начини идват от релация (таблица). Трябва да създадете таблица в даден момент, вероятно с UNION изберете 'scream' от dual... за всяка стойност, както казахте.   -  person dani herrera    schedule 04.11.2011

Отговори (3)


arrow_upward
3
arrow_downward

Ако използвате 10g или по-висока, можете да създадете функция, която преобразува CSV низ в динамична таблица. Вижте кода за токенизатор на низове в този друг отговор.

Бихте го използвали така:

select * from movies
where title NOT in (
         select * 
          from table (string_tokenizer
                      (
                          'Scream, Scary Movie,Exorcist,Dracula,Saw,Hide and Seek'
                        )

                  )
     )
/

Ето една малко по-проста реализация, която не изисква допълнителна инфраструктура:

SQL> select * from table(sys.dbms_debug_vc2coll('Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
 ))
/
  2    3    4    5    6    7    8  
COLUMN_VALUE
--------------------------------------------------------------------------------
Scream
Scary Movie
Exorcist
Dracula
Saw
Hide and Seek

6 rows selected.

SQL>

Това е подобно на конструктора на стойността на таблицата, но работи само за „таблици“ с една колона.

person APC    schedule 04.11.2011
comment
Ключовата дума table и името sys.dbms_debug_vc2coll бяха това, което търсех. Помогна ми да избегна повтарянето на union select многократно. Благодаря! - person Krzysztof Jabłoński; 24.09.2015

arrow_upward
2
arrow_downward

Това, което искате, е функцията „Конструктори на стойности на таблици“. Не мисля, че Oracle поддържа това.

Вижте статията на Joe Celko за: Конструктори на таблични стойности в SQL Server 2008 и пример, взет от там:

SELECT *
FROM
  ( VALUES
      (101, 'Bikes'),
      (102, 'Accessories'),
      (103, 'Clothes')
  ) AS Category(CategoryID, CategoryName);
person ypercubeᵀᴹ    schedule 04.11.2011
comment
срам оракул няма това - person toop; 04.11.2011
comment
Мисля, че и DB2 го има. И SQL-Server само най-новите (2008) версии. - person ypercubeᵀᴹ; 04.11.2011
comment
Бих добавил, че НЯКОИ версии на DB2 имат това. Използвам v7r4 и по-късно доста силно и го поддържа. Някои от по-старите машини (v5) обаче ... не толкова. - person Meower68; 13.12.2017
comment
@Meower68 не съм сигурен дали тази информация трябва да бъде редакция. От любопитство поддържа ли DB2 (версията, която използвате) това (заявка без SELECT): VALUES (101, 'Bikes'), (102, 'Accessories'), (103, 'Clothes') ; (Postgres прави) - person ypercubeᵀᴹ; 13.12.2017

arrow_upward
1
arrow_downward

Можете да използвате тип таблица и функцията table(), за да прехвърлите списък към таблица.

CREATE OR REPLACE TYPE varchar_list_type as table of varchar2(100);

CREATE OR REPLACE function in_varchar_list ( p_string in varchar2 ) return varchar_list_type
as
    l_data             varchar_list_type := varchar_list_type();
    l_string           long default p_string || ',';
    l_n                number;
begin

    loop
        exit when l_string is null;

        l_data.extend;
        l_n := instr( l_string, ',' );
        l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
        l_string := substr( l_string, l_n+1 );

    end loop;
    return l_data;
end;

След това използвайте така:

select * from TABLE(select cast(in_varchar_list('foo,bar,baz') as varchar_list_type) from dual)

Разбира се, можете да използвате свързваща променлива или нормална променлива вместо твърдо кодиран низ 'foo,bar,baz'.

редактиране: правописна грешка в заявката ^_^"

person winkbrace    schedule 04.11.2011