SQL kii ṣe C ++, kii ṣe JavaScript. Nitorinaa, iṣiro ti awọn ọrọ ọgbọn waye ni oriṣiriṣi, ati pe kii ṣe ohun kanna rara:
WHERE fncondX() AND fncondY()
= fncondX() && fncondY()
Ninu ilana ti iṣapeye ero ipaniyan ibeere PostgreSQL
Nitorinaa, ti o ba tun fẹ ṣakoso pataki, o nilo lati ṣeto rẹ ṣe awọn ipo ko dọgba lilo majemu
Data ati ṣiṣẹ pẹlu wọn ni ipilẹ
#0: RTFM
Bibẹrẹ
Nigba ti aṣẹ ti igbelewọn jẹ pataki, o le wa ni sile nipa lilo awọn ikole
CASE
. Fun apẹẹrẹ, eyi jẹ ọna lati yago fun pipin nipasẹ odo ninu gbolohun ọrọ kanWHERE
ti ko le gbẹkẹle:SELECT ... WHERE x > 0 AND y/x > 1.5;
Aṣayan ailewu:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Apẹrẹ ti a lo ni ọna yii
CASE
ṣe aabo ikosile lati iṣapeye, nitorinaa o yẹ ki o lo nikan nigbati o jẹ dandan.
# 1: okunfa majemu
BEGIN
IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
...
END IF;
RETURN NEW;
END;
Ohun gbogbo dabi pe o dara, ṣugbọn ... Ko si ẹniti o ṣe ileri pe idoko-owo naa SELECT
kii yoo ṣiṣẹ ti ipo akọkọ ba jẹ eke. Jẹ ki a ṣe atunṣe pẹlu iteeye IF
:
BEGIN
IF cond(NEW.fld) THEN
IF EXISTS(SELECT ...) THEN
...
END IF;
END IF;
RETURN NEW;
END;
Bayi jẹ ki a wo ni pẹkipẹki - gbogbo ara ti iṣẹ okunfa “ti a we” sinu IF
. Eyi tumọ si pe ko si ohun ti o ṣe idiwọ fun wa lati yọ ipo yii kuro ninu ilana lilo WHEN
-awọn ipo
BEGIN
IF EXISTS(SELECT ...) THEN
...
END IF;
RETURN NEW;
END;
...
CREATE TRIGGER ...
WHEN cond(NEW.fld);
Ọna yii jẹ iṣeduro lati ṣafipamọ awọn orisun olupin nigbati ipo naa jẹ eke.
# 2: OR / AND pq
SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)
Bibẹẹkọ, o le pari pẹlu awọn mejeeji EXISTS
yoo jẹ "otitọ", ṣugbọn mejeeji yoo ṣẹ.
Ṣugbọn ti a ba mọ daju pe ọkan ninu wọn jẹ “otitọ” pupọ diẹ sii nigbagbogbo (tabi “eke” - fun AND
-awọn ẹwọn) - ṣe o ṣee ṣe lati bakan “pọ si pataki rẹ” ki ekeji ko ba ṣiṣẹ lekan si?
O wa ni jade pe o ṣee ṣe - ọna algorithmic ti sunmọ koko ọrọ naa
Jẹ ki a kan “sọ” awọn ipo mejeeji labẹ CASE:
SELECT ...
WHERE
CASE
WHEN EXISTS(... A) THEN TRUE
WHEN EXISTS(... B) THEN TRUE
END
Ni idi eyi a ko setumo ELSE
-iye, iyẹn ni, ti awọn ipo mejeeji ba jẹ eke CASE
yoo pada NULL
, eyi ti o tumọ bi FALSE
в WHERE
-awọn ipo.
Apeere yii le ni idapo ni awọn ọna miiran - da lori itọwo ati awọ:
SELECT ...
WHERE
CASE
WHEN NOT EXISTS(... A) THEN EXISTS(... B)
ELSE TRUE
END
# 3: bawo ni [kii ṣe] kọ awọn ipo
A lo ọjọ meji ni itupalẹ awọn idi fun iṣẹ “ajeji” ti okunfa yii - jẹ ki a wo idi.
Orisun:
IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
AND ( OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
OR OLD."Удален" <> NEW."Удален"
OR OLD."Дата" <> NEW."Дата"
OR OLD."Время" <> NEW."Время"
OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...
Isoro #1: aidogba ko bọwọ fun NULL
Jẹ ká fojuinu wipe ohun gbogbo OLD
-awọn aaye ní itumo NULL
. Kini yoo ṣẹlẹ?
SELECT NULL <> 1 OR NULL <> 2;
-- NULL
Ati lati awọn ojuami ti wo ti sise jade awọn ipo NULL
deede FALSE
, bi darukọ loke.
Ipinnu: lo onišẹ IS DISTINCT FROM
ROW
-Oṣiṣẹ, ṣe afiwe gbogbo awọn igbasilẹ ni ẹẹkan:
SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE
Isoro #2: awọn imuse oriṣiriṣi ti iṣẹ-ṣiṣe kanna
Jẹ ki a ṣe afiwe:
NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
Kini idi ti afikun idoko-owo wa nibi? SELECT
? Iṣẹ kan to_regclass
? Kini idi ti o yatọ? ..
Jẹ ki a ṣe atunṣe:
NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid
Isoro #3: ayo awọn iṣẹ bool
Jẹ ki a ṣe agbekalẹ orisun naa:
{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )
Oops... Ni otitọ, o wa ni pe ti eyikeyi ninu awọn ipo akọkọ meji ba jẹ otitọ, gbogbo ipo naa yipada si TRUE
, lai mu sinu iroyin awọn aidọgba. Ati pe eyi kii ṣe ohun ti a fẹ rara.
Jẹ ki a ṣe atunṣe:
(
{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате}
) AND
( {... неравенства} )
Isoro #4 (kekere): eka OR majemu fun aaye kan
Ni otitọ, a ni awọn iṣoro ni No.. 3 gangan nitori awọn ipo mẹta wa. Ṣugbọn dipo wọn o le gba nipasẹ ọkan, lilo ẹrọ naa coalesce ... IN
:
coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')
Nitorina awa NULL
"a yoo mu", ati ki o soro OR
Ko si ye lati faramọ pẹlu awọn akọmọ.
Lapapọ
Jẹ ki a ṣe igbasilẹ ohun ti a ni:
IF (
coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
(
OLD."ДокументНашаОрганизация"
, OLD."Удален"
, OLD."Дата"
, OLD."Время"
, OLD."ЛицоСоздал"
) IS DISTINCT FROM (
NEW."ДокументНашаОрганизация"
, NEW."Удален"
, NEW."Дата"
, NEW."Время"
, NEW."ЛицоСоздал"
)
) THEN ...
Ati pe ti o ba ro pe iṣẹ okunfa yii le ṣee lo ninu UPDATE
-nfa nitori wiwa OLD/NEW
ni ipo ipele oke, lẹhinna ipo yii le ni gbogbo igba gbe sinu WHEN
-condition, bi han ni #1...
orisun: www.habr.com