Styl SQL (SQL style)¶
Opis używanego stylu języka SQL¶
1. Wstęp¶
Celem tego dokumentu jest opisanie nazewnictwa oraz stylu pisania kodu w języku SQL dla Firebirda, z logiką przechowywaną w bazie danych. Styl ten obowiązuje:
- Dla całego rozwoju aplikacji (zespoły ZRT i ZRP, rozwój wszystkich standardowych modułów aplikacji oraz funkcjonalności prowadzonych pozazespołami ZRP i ZRT trafiający na repozytorium S4/Teneum).
- W zespołach serwisowych i wdrożeniowych (zespoły mogą wprowadzać drobne zmiany na własne potrzeby, ale nie jest to zalecane). Modyfikacje wewnątrzzespołowe stylu SQL powinny zostać zawarte w osobnych dokumentach.
Opisane tu przykłady mogą nie zawierać prawdziwych nazw pól i mogą być nieprawidłowe/niepoprawne logicznie Ewentualne uwagi lub braki w dokumencie proszę zgłaszać do zespołu ZRP.
Warning
Styl SQL jest zaleceniem, a nie obowiązkiem. Rozwijając oprogramowanie i poprawiając błędy, zaleca się jego stosowanie. Najlepiej do nowych fragmentów kodu. Nie ma obowiązku poprawiania stylu SQL w całych triggerach i procedurach, które modyfikujemy. Chodzi o to aby niepotrzebnie nie zwiększać kosztu i czasu trwania projektu rozwojowego, a także o to aby nie zaciemniać merge requesta dla osób, które będą chciały te zmiany wgrać do klientów.
2. Konfiguracja IBExperta¶
Zanim zaczniesz swoją przygodę z SQL-em, sprawdź czy masz poprawnie skonfigurowanego IBExperta. Niewłaściwie ustawienie transakcji powoduje locki na bazie i uniemożliwia prawidłową pracę kilku osobom na raz. Uwaga! Jeśli kopiujemy ustawienia bazy danych w IBExpercie, ustawienia transakcji dla kopii bazy się nie przenoszą i należy je ustawić od nowa. https://docs.google.com/document/d/1RfYce3nH3cfQyEt6Zj4752ruKuve4hQBFIX0koyfzVM/edit?usp=sharing W instrukcji ostatni punkt to ustawienie autoformatowania kodu. Niniejsza instrukcja ma na celu okreslenie ogólnych zasad, kwestia spacji, znaków itp pozostaje w kwestii autoformatowania.
3. Nazwy - ogólnie¶
Wszystkie nazwy powinny mówić cokolwiek o tym, co nazywają. W nazwach obiektów nie używamy spacji, piszemy je WIELKIMI literami. Stosujemy wyłącznie znaki alfabetu łacińskiego (A,B,C,D,E,F...), cyfry i znak podkreślnika (podłoga "_"). Nazwy obiektów nie mogą być takie same jak słowa kluczowe (w IBexpercie widoczne jako pogrubione), np. order, date, timestamp, active, itd. Nazwy obiektów dla wszystkich nowych i istniejących modułów, nazywanych w języku angielskim, nazywamy po angielsku, np. FK (FA), PER (HR), WMS (WMS). Wyjątkiem są nazwy własne, których nie tłumaczymy sztucznie, np. NIP, PESEL, REGON. Sprawdź w słowniku czy nie robisz lapsusów językowych, np. koszyk sklepowy to cart (CARTS) a nie basket. Dla modułów napisanych w języku polskim (HID) istniejące obiekty rozszerzamy/rozbudowujemy konsekwentnie w języku polskim, natomiast wszystkie nowe funkcjonalności tworzymy w języku angielskim z prefixem modułu. Skróty nowych modułów mogą mieć maksymalnie 3 znaki (zwykle są to 2 lub 3 znaki). Nowe moduły zakładają zespoły ZRP, ZRT lub zespoły wykonujące prace rozwojowe (posiadające informacje o nowym module w projekcie) po uprzednim ustaleniu z głównym architektem systemu Teneum. Wszystkie procedury i widoki fasadowe nazywamy w języku angielskim i dodajemy po nazwie modułu słowo _API_, nawet jeżeli odnoszą się do obiektu w języku polskim, np. TD_API_ORDER_ADD; TD_API_ORDERPOS_ADD; TD_API_ORDER_VIEW. Stosuj notację prefixową. Jeśli potrzebujesz więcej tabel/procedur/widoków do obsługi "koszyka", nazwij je CARTSUM, CARTDETAILS, CARTVAT, a nie SUMCARTS, DETAILCARTS, VATCARTS. Jeśli istnieją zależności między modułami, a dana nazwa (pola) już funkcjonuje i odwołujemy się do niej, to stosujemy oryginalną nazwę pola bez translacji ang->pol i pol->ang, np. KTM, DOSTAWA, PERSON. X_ w nazwie, oznaczający indywidualny obiekt u klienta, jest zawsze na samym początku nazwy, a po nim występuje znak "podłogi" (jedynym wyjątkiem są nazwy generatorów dla tabel x-owych gdzie X nie jest na samym początku: GEN_X_).
4. Obiekty bazodanowe¶
4.1 Tabele i pola¶
Nazwy tabel piszemy zawsze w liczbie mnogiej, zgodnie z obowiązującym językiem tabeli (polski, angielski). W przypadku tworzenia tabeli łączącej inne tabele w relacji wiele do jeden, w liczbie mnogiej piszemy wyłącznie ostatni człon nazwy. W przypadku tworzenia tabeli łączącej inne tabele w relacji wiele do wielu, wszystkie człony nazwy piszemy w liczbie mnogiej. W nazwach tabel nie stosujemy podkreślników, jedynie po prefixie opisującym nazwę obszaru i/lub oznaczeniem X-owym tabeli. Nazwy tabel zawierające pozycje zawierają suffix POS, a z rozpiskami dodatkowy suffix DETS (zarówno dla nazw polskich jak i angielskich). Zakładana nazwa tabeli z przedrostkiem powinna mieć do 24 znaków, nie jest zalecane zakładanie dłuższych nazw niż 27 znaków - długość tabeli zakłada aby w przyszłości móc dodać do niej powiązaną tabelę z pozycjami i rozpiskami, ale również założyć do niej bez problemu generator (GEN_), np.:
- WMS_TROLLEYS, WMS_TROLLEYPOS, WMS_TROLLEYPOSDETS [1]
- SYS_COUNTRIES, SYS_COUNTRYPOS, SYS_COUNTRYPOSDETS
- TD_ORDERS, TD_ORDERPOS, TD_ODDERPOSDETS
- X_TD_WATER, X_TD_WATERPOS, X_TD_WATERPOSDETS
- X_NAGZAMPLATNOSCI, X_NAGZAMPLATNOSCPOS, X_NAGZAMPLATOSCPOSDETS
- KLIENTEMAILE (wiele do jeden), KLIENTADRESY, KLIENTTELEFONY -> KLIENTTELEFONPOS
- TELEFONY (wiele do wielu), TELEFONYKONTRAHENCI (TELEFONYKLIENCI, TELEFONYDOSTAWCY).
Pola w tabelach: W nazwach pól nie stosujemy podkreślników, wyjątkiem jest podkreślnik po prefixie X, np. X_ILOSC, X_SYMBOL, X_CART. Jeśli pole w tabeli odwołuje się do pola z innej tabeli domena na nowym polu musi być taka sama jak na tabeli pierwotnej. Nie dajemy na polach wartości domyślnej - robimy to w triggerach blankujących BI/BU. Zwróć uwagę czy podczas dodawania pola do tabeli nie wpisał się automatycznie w jej description, description z domeny (przykłady takich domen: string, oddzial_id). Jeśli tak się stało należy go usunąć/wyczyścić.
Pole klucza głównego Pole klucza głównego tabeli nazywamy zawsze REF który jest nową domeną typu integer-owego (opisane w sekcji "Domeny"). Od razu dodajemy do nowo założonej tabeli:
- generator (opisane w sekcji "Generatory"),
-
trigger blankujący (opisane w sekcji "Triggery"). Odwołania do pól z innych tabel (przykład na podstawie tabeli TD_ORDERS która ma PK pole REF typu TD_ORDERS_ID i tabeli TD_ORDERPOS zawierającej pozycje do niej). Pole będące złączeniem na inną tabelę:
-
Pole będące foreign key-em do klucza głównego (REF) nazywamy tak samo jak nazwa tabeli, do której ten FK się odnosi, ale w liczbie pojedynczej i bez podkreślników. Dla tabeli TD_ORDERPOS pole FK na tabelę TD_ORDERS wyglądać będzie natępująco: TDORDER TD_ORDERS_ID. Dla tabeli X_TD_WATERPOS wyglądałoby to tak: X_TDWATER X_WATER_ID.
- Pole będące odwołaniem do pola niebędącego PK (redundancja). W tym przypadku nazwa takiego pola powinna składać się z połączenia nazwy tabeli i pola które jest użyte, np. TDORDERSYMBOL SYMBOL_ID (dla tabeli TD_ORDERS i pola SYMBOL), X_TDWATERNAME STRING100 (dla tabeli X_TD_WATER i pola NAME). Jeśli nazwa tabeli jest zbyt długa należy ją skrócić aby była czytelna i jednoznaczna.
Przykłady "standardowych" pól i ich użycia:
Pole SYMBOL Jeśli w tabeli będziesz przechowywać jakiś symbol identyfikujący rekord, nazwij pole SYMBOL i najlepiej domenę symbol_id.
Pole NAME Nazwa (techniczna) danego rekordu, z reguły nie powinna być dłuższa niż string100.
Pole DESCRIPT Dłuższy opis słowno-muzyczny rekordu - przeważnie domena string255 lub memo.
Pole DISPLAYNAME Nazwa wyświetlana (dla klienta).
Dla oznaczenia kto i kiedy zarejestrował dany rekord, a także ostatnio modyfikował dany rekord używamy pól: REGDATE - TIMESTAMP_ID REGOPERATOR - OPERATOR_ID CHGDATE - TIMESTAMP_ID CHGOPERATOR - OPERATOR_ID
4.2 Domeny¶
Zakładanie: Domeny tekstowe powinny mieć ustawione kodowanie na WIN1250 a collate na PXW_PLK. a) dla nowych tabel zakładamy zawsze domenę tożsamą z nazwą tabeli (typu integer) będącą jednocześnie kluczem głównym, dodając suffix _ID, np. WMS_TROLLEYS_ID, SYS_COUNTRIES_ID, X_TD_WATER_ID, KLIENTEMAILE_ID, TELEFONY_ID. b) domeny nowe nie powiązane kluczem głównym tabel: Z zasady nie zakładamy nowych domen, staramy się używać istniejących, np. jeśli potrzebujemy striga27, nic nie stoi na przeszkodzie aby użyć string30. Z zasady używamy domeny najbardziej zbliżonej do wymaganej długości. Zalecana jest zakładanie domeny dla obiektów "uregulowanych prawem", np. nip, pesel, regon, itp. Wtedy nazwa takiej domeny ma nazwę własną i suffix ID - NIP_ID, PESEL_ID, REGON_ID, itp. Założenie nowej domeny musi być zawarte w projekcie technicznym do tematu lub skonsultowane z architektem. Tworzone domeny nie mogą mieć zaznaczonego atrybutu "NOT NULL", ponieważ znacząco utrudnia to używanie domeny w zmiennej procedury/triggera.
Używanie: Wszędzie stosujemy domeny. Podczas tworzenia dowolnego obiektu w bazie nie może istnieć w nim pole niebędące zdefiniowane jako typ domenowy:
- dobrze: cenywal (ilosci_mag), ceny, string200, integer_id, smallint_id, dokumnag_id, pozzam_id, dostawa_id (wyświetlane w IBExpercie w kolorze zielonym),
- źle: numeric(14,4), numeric(14,2), varchar(200), integer, smallint (wyświetlane w IBExpercie w kolorze czarnym).
4.3 Klucze główne¶
Nazwy kluczy glównych tworzymy dodając prefix PK_ do nazwy tabeli na której jest to klucz glówny np: PK_X_TD_WATER, PK_TD_ORDERS, PK_TELEFONYKLIENCI.
4.4 Klucze obce¶
Nazwy kluczy obcych tworzymy dodając prefix FK_ do nazwy tabeli w której zakładamy klucz obcy oraz oddzieloną podkreślnikiem nazwą pola na którym on jest, np: FK_EMPLOYEES_PERSON jako klucz obcy w tabeli EMPLOYEES na polu PERSON. Gdy nazwa się nie zmieści w 31 znakach wg. powyżsyzch wytycznych należy skrócić nazwę klucza tak, aby nazwa tabeli i pola były jednoznacznie identyfikowalne, np. tabela CSO_POZZAMAVAILABILITYEVENTS pole DATADOSTAWYZEWN: FK_CSO_POZZAMAVEVENTS_DATADOSTZ.
4.5 Indeksy¶
Jeśli indeks jest na jednym polu to jego nazwa składa się z nazwy tabeli i tego pola oddzielonych podkreślnikiem, np. X_TD_WATER_SYMBOL, TELEFONYKLIENCI_NUMER. Gdy indeks zawiera więcej niż jedno pole, jego nazwa składa się z nazwy tabeli, członu _IDX oddzielonego podkreślnikiem i kolejnego numeru takiego indeksu, np. X_TD_WATER_IDX1, X_TD_WATER_IDX2, TELEFONYKLIENCI_IDX1, TELEFONYKLIENCI_IDX2, itd.
4.6 Generatory¶
Nowy generator zakładamy zwykle podczas tworzenia nowej tabeli. Nazwa składa się z prefixu GEN_ i nazwy tabeli, np. GEN_X_TD_WATER, GEN_KLIENTEMAILE. Uwaga! Jedyny wyjątek, gdzie X może pojawić się w środku nazwy!
4.7 Widoki¶
Człony nazwy w widokach oddzielane są podkreślnikami, a na końcu nazwy dodajemy suffix _VIEW. W przypadku, gdy widok korzysta z jednej tabeli (widoki tworzone w celu zapobieżenia zapętlaniu się dbi na słownikach na tej samej tabeli), jej nazwę zawieramy w nazwie widoku, np. X_TD_WATER_VIEW, TELEFONYKLIENCI_VIEW. Nazwy pól zwracane w takim widoku są takie same jak nazwy w odpytywanej tabeli.
Kiedy w widoku korzystamy z większej ilości tabel nazwa powinna informować, co ten widok zwraca (najlepiej nazwami głównych/istotnych tabel), np. KLIENT_TOWARY_VIEW, TD_ORDER_PAYMENTS_VIEW. Nazwy poszczególnych członów (liczba pojedyncza/mnoga nazywamy analogicznie jak opisano to w sekcji "Tabele i pola" - relacje jeden do wielu i wiele do wielu). Nazwy zmiennych są takie same jak nazwy pól w tabelach. W przypadku zdublowanych kolumn, np. REF dodajemy do zmiennej wyróżnik tabeli znajdujacy się na początku nazwy. Przykład dopuszczalnych nazw dla widoku NAGZAM - POZZAM i pól REF: NREF - PREF, NAGZAMREF - POZZAMREF, NAGREF - POZREF. W przypadku gdy mamy istniejący widok i dokładamy do zwracanych wartości kolejne pole, które nazywa się tak samo jak inne pole w tym widoku, to nie zmieniamy nazwy istniejącego pola, a robimy nową i unikalną nazwę dla dodawanego pola, np. NAZWA - KLIENTNAZWA, NUMER - POZNUMER, DATA - DOSTAWADATA.
4.8 Wyjątki¶
Z reguły nie tworzymy nowych wyjątków. Standardowo korzystamy z exception-a "universal", podczas dodawania testowego wyjątku używamy exception "test_break". Należy pamiętać, że na HH dla Windows Mobile wyświetlana jest nazwa i tylko kilka początkowych znaków exceptiona. Jeśli w procedurze/triggerze wywoływanym na HH są exceptiony, należy je napisać w taki sposób aby były jednoznaczne do zidentyfikowania. Inna możliwośc to stworzenie nowego wyjątku, o innej nazwie, aby był on widoczny na HH.
4.9 Ogólne informacje o triggerach i procedurach¶
Wszędzie stosujemy domeny, jak opisano to w sekcji "Domeny". Ciało procedury/triggera składa się z następujących logicznie po sobie części: 0. mile widziany komentarz (w szczególności w procedurach), opisujący co się dzieje i w którym miejscu systemu jest wykorzystywana. Dodatkowo opis nieintuicyjnych zmiennych wejściowych/wyjściowych, np. MODE, TRYB, STATUS (jeśli nie 0-1).
- Przypisanie wartości początkowych - inicjalizacja zmiennych.
- Sprawdzenie warunków początkowych i parametrów wejściowych (oraz exceptiony).
- Obróbka parametrów wejściowych, np. zmiana wartości z null na 0.
- Enterami rozdzielamy logiczne bloki kodu.
- Logika - porady: * jak robimy select to przeważnie zmienne używane w tym selecie powinny być w kolejnych liniach a nie 10 kilometrów dalej, * unikamy robienia kilku selectów z tych samych tabel (struktur). Jeżeli potrzebujemy na końcu procedury pobrać zmienną z tabeli, która jest już selectowana na początku tej procedury, dopisujemy potrzebną zmienną w początkowym selectcie (nawet w przypadkach, gdy selecty używane są dopiero w if-ach), a nie robimy kolejny select na końcu procedury, * należy pamiętać o nullowaniu zmiennych przed selectem jeśli były wcześniej przypisane/zadeklarowane i na początku w for-selectach (po begin-ie) jeżeli w środku for selecta są wyliczane. Z reguły nie używamy EXIT-ów. Jeżeli jednak istnieje taka potrzeba należy na samym początku triggera/procedury, w komentarzu napisać "UWAGA!!! EXIT-y !!!".
4.10 Triggery¶
W nazwach triggerów poszczególne człony oddzielamy podkreślnikami. Nie robimy triggerów na widokach. Nazwy triggerów tworzymy dodając po podkreślniku do nazwy tabeli, na której działają, symbol rodzaju triggera (np. BI dla before insert, AU dla after update, itp.), np. X_TD_WATER_BI, TELEFONYKLIENCI_AU. W nazwie triggera nie wpisujemy jego pozycji. Jeżeli dokładamy kolejny trigger danego typu nazywamy krótko co ten trigger robi, np. X_TD_WATER_BI_VOLUME, TELEFONYKLIENCI_AU_DOSTSYNCHRO. Triggery nie posiadające na końcu opisu na operacjach before insert i before update są zwykle triggerami blankującymi (ustawiającymi domyślne wartości zmiennych) i nie powinny zawierać rozległej logiki, np. NAGZAM_BI, NAGZAM_BU, KLIENCITELEFONY_BI, KLIENCITELEFONY_BU, itd. Każda nowa tabela musi mieć trigger z pozycją 0 do autonumerowania klucza głównego z suffixem _BI_REF, np. X_TD_WATER_BI_REF, KLIENTEMAILE_BI_REF (można go wygenerować automatycznie w IBExpercie przy tworzeniu tabeli). W triggerze BI_REF nie dopisujemy innej logiki, służy wyłącznie do autoinkrementacji. Triggery numerujemy co 5 (position), aby w razie konieczności móc wstrzelić się z x-owym triggerem pomiędzy nimi. Triggery nie mogą być uniwersalne np. before insert or update. Na każdą operację (insert, update, delete) robimy nowy trigger - jeśli triggery robią \"to samo\" nazywamy je analogicznie, np. X_TD_WATER_BI_COLOR, X_TD_WATER_BU_COLOR. Należy pamiętać, że często triggery BI i BU chodzą parami i bardzo często powiązany z nimi jest trigger AD. W triggerach na update (AU,BU) w każdym if-e ma być sprawdzone czy zmienna używana poniżej (w tym if-ie) uległa zmianie. Weryfikację zmiany robimy za pomogą wyrażenia "is distinct from", np.
if (old.ilosc is distinct from new.ilosc and coalesce(new.ilosc, 0) < 0) then
begin
exception universal 'Ilosc nie moze byc mniejsza od 0.';
end
4.11 Procedury¶
W nazwach procedur poszczególne człony oddzielamy podkreślnikami. Nazwa procedury powinna mówić jasno i zwięźle, co ta procedura robi. Jeśli działanie procedury skupia się w większości na jednej tabeli, jej nazwa powinna być na początku nazwy tej procedury, np. NAGZAM_USUN, POZAM_PRZELICZ_WARTOSC, itp. Jeśli istnieją procedury podobne lub do tej samej funkcjonalności w systemie ich początki nazw powinny być takie same, np. TD_ORDERPOS_ADD_INVENTORY, TD_ORDERPOS_DEL_INVENTORY, X_LISTYWYSD_DPD_LIST, X_LISTYWYDS_DPD_TERMIN, X_LISTYWYSD_DPD_KOSZT. Dla parametrów wejściowych nie podajemy wartości domyślnych. Jeśli procedura ma kilka parametrów, wszystkie z nich muszą zostać użyte (uzupełnione) podczas jej wywołania. Wpisanie wartości domyślnej jest dozwolone w przypadkach, gdy dodajemy kolejną zmienną do istniejącej już procedury i ma ona bardzo dużo wywołań, które ciężko będzie poprawić/zmienić lub do standardowej procedury dokładamy parametr X-owy. Jeśli dokładamy kolejny parametr do istniejącej procedury to ma on się znaleźć zawsze na końcu, nigdy nie dodajemy go w środku - unikamy w ten sposób pomyłek i niewłaściwych wywołań. Nazwy parametrów wejściowych i wyjściowych oraz ich domen piszemy wielkimi literami.
5. Komentarze¶
Nie stosujemy komentowania blokowego /* */, jednym wyjątkiem jest komentarz na początku procedury/triggera/widoku opisujący zmienne i co on/a robi. W ciele obiektów używamy wyłącznie komentarzy jednolinijkowych "--". Stosowanie takich komentarzy pozwala na szybkie wykomentowanie za pomocą /* */ u klientów lub podczas debugowania.\ Komentarze piszemy bez polskich znaków.
6. Sygnatury, descriptiony i oznaczanie X-ów¶
Sygnaturowanie zmian i komentarze ogólne w polach "Description": Każdy dodany/zmodyfikowany obiekt w bazie danych ma zawierać w swoim descriptionie numer tematu z Jira. Description z numerem tematu musi:
- być na początku w pierwszej linii pola tekstowego z descriptionem (Jeżeli sygnatura nie będzie się znajdowała w pierwszej linii, to nie zostanie ona wyszukana)
- mieć średniki po obu stronach sygnatury;
- posiadać zastąpiony myślnik "-" znakiem podkreślnika "_". np. ";PSU_14;" ";PSU_235;TSU_4567; 0 - nie, 1 - tak, 2 - domyślny"; Nowa sygnatura trafia na koniec ciągu sygnatur. Dodatkowy komentarz powinien zaczynać się od nowej linii, pod sygnaturami, np.
Example
";PSU_123;PSU_124;PSU_128; To jest przykładowy komentarz do pola".
X-y: W kodzie oznaczamy zmiany indywidualne za pomocą:
- wyrażenia XXX ,
- jednoznacznych inicjałów wykonawcy,
- numeru zgłoszenia. Mile widziane jest też wpisanie na początku descriptiona w modyfikowanym obiekcie oznaczenia XXX, że obiekt ma zmiany X-owe - nie trzeba wtedy przeglądać treści procedury/triggera aby się dowiedzieć, że zawiera zmiany X-owe. Składnię komentarzy zespoły ustalają indywidualnie, np. "--XXX PM PSU-234", "--[PM] XXX PSU-234". Oznaczamy z osobna każdą modyfikowaną linię lub w przypadku gdy jest dłuższy fragment kodu początek zmian oznaczamy słowem START a koniec STOP, np.
--XXX PM PSU-234 START
if (:wartosc = 0) then
begin
execute procedure x_nalicz_wartosc(new.ref);
end
else
begin
execute procedure x_aktualizuj_wartosc(new.ref);
end
--XXX PM PSU-234 STOP
select first 1 d.ref
from dokumnag d
where d.typ in ('WZ','WZE')
and d.akcept = 1
--and d.mwsdoc = 0 --XXX PM PSU-234
and d.magazyn in ('CWE', 'ZAS', 'OTU') --XXX PM PSU-234
into :dokumref;
7. Styl SQL i przykłady¶
7.1 Informacje ogólne¶
-
Staramy zachować się czytelność kodu podaną w poniższych zaleceniach, wystarczającym jest zachowanie się do użycia autoformatowania stylu opisanego w https://docs.google.com/document/d/1RfYce3nH3cfQyEt6Zj4752ruKuve4hQBFIX0koyfzVM/edit?usp=sharing
-
Główna reguła pisania kodu, polega na tym, żeby kod w triggerach i procedurach mieścił się w pierwszych 80 znaków linii (widoczne w edytorze kodu w IBExpercie jako szara pionowa linia). Przyjmujemy zasadę, że linia jest półką skalną na której jest fragment kodu (wyraz lub wyrażenie logiczne), jeśli fraza się przeważy i spadnie w przepaść to ma zostać ona przeniesiona do nowej linii. Zalecane jest, aby nie nachodzić w ogóle na margines. IBExpert domyślnie pokazuje taki margines. Można to włączyć w Options -> Editor Options -> zakładka Display.
- Kod może być pisany w jednej linii, pod warunkiem że całe wyrażenie się w niej mieści (nie dotyczy begin-end).
- Spacja przed nawiasem otwierającym tylko po if-ie, while'u i warunkach logicznych (and, or), w pozostałych miejscach gdzie jest otwierany nawias to bez spacji.
- Po przecinkach jest zawsze spacja.
- Cały kod piszemy małymi literami.
- Nie używamy niepotrzebnie nawiasów, bo zmniejszają czytelność kodu, np:
(((field = 0) and (field <> 9)) or (field is null))
a wystarczy:
(field = 0 and field <> 9) or field is null
- Używamy exists zamiast count(*) from do sprawdzenia czy w tabeli są rekordy w odpowiedniej dziedzinie. Korzyści: - bardziej przejrzysta konstrukcja, - nie trzeba deklarować zmiennej, - wykonuje się szybciej, bo nie zlicza rekordów.
7.2 Zmienne wejściowe/wyjściowe¶
create or alter procedure INWENTA_ZAMKNIJ (
INWENTA INWENTA_ID,
DOKPLU DEFDOKUM_ID,
DOKMIN DEFDOKUM_ID,
OPER OPERATOR_ID,
REF DOKUMNAG_ID,
DOSTAWA DOSTAWA_ID)
returns (
STATUS INTEGER_ID,
DOKSPLU SYMBOL_ID,
DOKSMIN SYMBOL_ID,
DOKREF DOKUMNAG_ID,
RDOSTAWA DOSTAWA_ID) -- w zmiennych wyjściowych dodajemy "r" (od słowa return) tylko na dublach wejście/wyjście
7.3 Deklaracje zmiennych¶
AS
declare variable dokummin dokumnag_id; --stosujemy domeny takie, jak wynikają z tabel lub zwracanych zmiennych w select-ach
declare variable dokumplu dokumnag_id;
declare variable datadost timestamp_id; --nie używać słów kluczowych, np. order, data
declare variable magazyn defmagaz_id;
declare variable zpartiami smallint_id;
declare variable dummy integer_id;
declare variable wrongktm ktm_id;
declare variable ktm ktm_id;
declare variable wersja nrwersji_id;
declare variable dostawa dostawa_id;
declare variable ilinw ilosci_mag;
declare variable ilstan ilosci_mag;
declare variable cenaplus ceny;
declare variable datawazn timestamp_id;
declare variable cena ceny;
declare variable ilroz ilosci_mag;
declare variable ilstock ilosci_mag;
declare variable ildok ilosci_mag;
declare variable toinsert ilosci_mag;
declare variable numer integer_id;
declare variable mws smallint_id;
declare variable mwsstock mwsstocks_id;
declare variable wersjaref wersje_id;
declare variable locsymbol symbol_id;
declare variable ref mwsconstlocs_id;
declare variable sref mwsconstlocs_id;
declare variable lreg mwsconstlocs_id;
7.4 Inicjalizacja zmiennych i przypisanie wartości¶
--inicjalizacja zmiennych na początku procedury/triggera, wartości początkowe
status = -1; --przed i po operatorach działań (arytmatycznych) = - + ma być spacja; tu -1 jest liczbą a nie znakiem działania (odejmowania)
doksplu = ''; --każde przypisanie od nowej linii
doksmin = '';
wartosc = :x + :y - :z + 1; -- DWUKROPEK obok zmiennej wpisujemy wszędzie gdzie się da
7.5 Select-y i aliasowanie¶
Aliasy¶
-- alias ma być krótki, najlepiej 1-3 (max 4-5) znakowy, wynikający z nazwy tabeli (poszczególnych słów lub skrótów składających się na jej nazwę)
select
from dokumnag dn
left join defdokummag ddm
left join defdokum dd
left join dokumpoz dp
left join defmagaz dm
left join klienci k
left join towary t
left join wersje w
-- w przypadku joinowania do tej samej tabeli alias powinien mówić o sposobie złączenia, a nie być kolejnym numerem, który nic nie mówi
--DOBRZE
select
from klienci k
left join klienci kparent
left join klienci kplatnik
--ŹLE
select
from klienci k1
left join klienci k2
left join klienci k3
Select-y¶
--select może być w jednej linii jeśli się w niej mieści
select k.nazwa from klienci k where k.ref = :klient into :nazwa;
--jeśli się nie mieści musi zostać rozbity szczegółowo
select i.data, i.magazyn, i.zpartiami, coalesce(d.mws, 0) --w coalesce spacja po ",", spacje odzielające poszczególne pola
from inwenta i --stosujemy aliasy, które mowią z jakiej to jest tabeli, alias nawet jak jest select z jednej tabeli/procedury
left join defmagaz d on d.symbol = i.magazyn --po słowie "on" nie dajemy nawiasów
where i.ref = :inwenta
into :data, :magazyn, :zpartiami, :mws; --przy zmiennych zawsze dwukropki, into wcięte a nie równo pod selectem
--użycie wyrażenia exists
select first 1 ip.ktm
from inwentap ip --słowa składni SQL (from, where, group by, having, into, order by) każde od nowej linii, wcięte do selecta dwoma spacjami, wszystkie w jednej "pinowej linii"
where ip.inwenta = :inwenta
and exists(select first 1 1 -- exist-y zawsze za koncu where'a
from dokumnag dn
join dokumpoz dp on dp.dokument = dn.ref --pierwszy wypisujemy argument z łaczonej tabeli
join magazyny m on m.symbol = dn.magazyn
where dn.ref = :dokumplu
and dp.ktm = containing 'M'
and dm.magmaster <> ''
and dm.typ = 'S') --nawias na końcu zapytania, nie w nowej linii
and exists(select first 1 1
from dokumnag dn
join dokumpoz dp on dp.dokument = dn.ref
join magazyny m on m.symbol = dn.magazyn
where dn.ref = :dokummin
and dp.ktm = containing 'M'
and dm.magmaster <> ''
and dm.typ = 'S') --nawias na końcu zapytania, nie w nowej linii
group by ip.ktm, ip.wersja, ip.cena, ip.dostawa
having min(ip.cenaplus) <> max(ip.cenaplus) --w havingu każdy warunek od nowej linii, jak w where
and sum(ip.ilinw) > min(ip.ilstan)
order by 1
into :wrongktm;
--przykład długiego selecta
select dp.ktm, p.docgroup, p.docid, p.docposid, dp.wersjaref, coalesce(dp.dostawa, 0),
dp.ilosco, dp.nrwersji
case --case'y staramy się dawać zawsze na końcu, aby pojedyncze zmienne były lepiej widoczne
when (d.mwsdisposition = 1 or coalesce(t1.altposmode,0) = 1) then dp.ilosc
else dp.iloscl
end - coalesce(dp.ilosconmwsacts, 0)
from docpos2real p
left join dokumnag d on d.ref = p.docid
left join dokumpoz dp on dp.ref = p.docposid
left join towary t on t.ktm = dp.ktm
left join dokumpoz p1 on p1.ref = dp.alttopoz and dp.fake = 1
left join towary t1 on t1.ktm = p1.ktm
where p.docid = :docid
and p.doctype = 'M' --każdy warunek od nowej linii
and (t.nazwa = 'aaaaaa'
or (coalesce(dp.havefake, 0) = 0
and coalesce(dp.fake, 0) = 0) --wewnętrzny blok z wcięciem
or ((coalesce(dp.fake, 0) = 1
and (t1.usluga <> 1
or tl.usluga = 2)
and coalesce(t1.altposmode, 0) = 1)))
and (t.paleta <> 1 or t.paleta is null)
and (t.nazwa = 'Ala ma kota'
or (d.akcept = 1
and d.mwsdisposition = 0)
or (d.akcept = 0
and d.mwsdisposition = 1))
and coalesce(d.mwsmag,d.magazyn) = :wh
and (dp.dostawa = 123
or p.posreal = 1
or (:mwsgenparts = 1 and p.posreal = 5)
or p.posreal = 2)
and dp.genmwsordsafter = 1
and t.usluga <> 1
and iif(p.docid = :docidand <> 0 and p.ref > 0, p.ref, d.ref) > 500 --iif w jednej linii, jak się nie mieści to użyj case'a
into :good, :docgroup1, :docid1, :docposid1, , :vers1, :lot,
:ilosco, :nrvers, --układ i łamanie zwracanych zmiennych jest taki sam jak w select'cie
:quantity1;
7.6 For select-y¶
for
select ip.ktm, ip.wersja, ip.dostawa, sum(ip.ilinw), max(ip.ilstan),
max(ip.cenaplus), max(d.datawazn)
from inwentap ip
left join dostawy d on d.ref = ip.dostawa
where ip.inwenta = :inwenta
group by ip.ktm, ip.wersja, ip.cena, ip.dostawa
having max(ip.ilstan) < sum(ip.ilinw) --bez spacji przed nawiasem przy agregacjach
into :ktm, :wersja, :dostawa, :ilinw, :ilstan,
:cenaplus, :datawazn --łamanie zmiannych analogicznie jak w select-cie
do begin --"do" na równi z "for"
if (:dokumplu is null) then
begin
numer = 0;
end
numer = :numer + 1;
end
--join z wieloma warunkami na zlaczenie
for
select r.symbfak, r.slodef, r.slopoz, r.kontofk, r.dataplat, r.dataotw,
r.opis, r.waluta, sum(rp.winien), sum(rp.ma), sum(rp.winienzl), sum(rp.mazl)
from rozrach r
left join rozrachp rp on r.symbfak = rp.symbfak and r.slodef = rp.slodef --w join warunków nie piszemy każdego od nowej linii
and r.slopoz = rp.slopoz and r.kontofk = rp.kontofk and r.company = rp.company
where r.kontofk = :account
and r.company = :company
and r.walutowy = :walutowy
and r.dataotw <= :data
and rp.data <= :data
group by r.symbfak, r.slodef, r.slopoz, r.kontofk, r.dataplat, r.dataotw,
r.opis, r.waluta
into :symbfak, :slodef, :slopoz, :kontofk, :dataplat, :dataotw,
:opis, :waluta, :winien, :ma, :winienpln, :mapln
do begin
saldo_winien = :winien - :ma;
saldo_ma = :ma - :winien;
if (:saldo_winien < 0) then
begin
saldo_winien = 0;
end
if (:saldo_ma < 0) then
begin
saldo_ma = 0;
end
suspend;
end
7.7 If-y¶
if (:data is null) then --przy zmiennych zawsze dwukropki, po if-ie spacja
begin --zawsze begin end, od nowej linii
data = current_date;
end
if (not exists(select first 1 1 from inwentap ip where ip.inwenta = :inwenta)) then --spacja po if-ie
begin
exception inwentaack_nopoz;
end
if (coalesce(wrongktm, '') <> '') then
begin
exception inwentaack_nopoz 'Niejednoznaczne ceny przychodowe dla: ' || :wrongktm; --konkatenacje ze spacją
end
if (:ref = 123 and :wartosc > 0 and coalesce(:klient, 0) = 0 --warunki w if-ie nie robimy każdego od nowlinii, tylko ciągiem (pamiętać o zasadzie linii!)
and exists(select first 1 --exists zawsze na końcu warunków w if-ie i od nowej linii
from mwsconstlocs mc --select pisany jak w nornalnym zapytaniu z zachowaniem zasad wcięć
join mwsacts ma on ma.mwsconstlocp = mc.ref
where ma.ref > :mwsconstloc
and mc.ref not in (11, 12, 13, 14)) then --słowo then na koncu if-a, nie od nowej linii
begin
exception universal 'To jest blad Pawla w if-ie!';
end
--odzielanie enterami logicznych czesci aby byly one razem w obrebie jednego warunku logicznego
--DOBRZE
if (zmienna > 1) then
begin
zmienna = zmienna + 1;
end
if (zmiennatmp > 1) then
begin
zmiennatmp = zmiennatmp + 1;
end
--ŻLE
if (zmienna > 1) then
begin
zmienna = zmienna + 1;
end
if (zmiennatmp > 1) then
begin
zmiennatmp = zmiennatmp + 1;
end
7.8 While¶
while (:a > 3) --warunki i spacja po while analogicznie do if-a
do begin --analogicznie do for selecta
select dp.wersjaref from dokumpoz dp where dp.ref = :pozycja into :wersjaref;
a = :a + 1;
end
7.9 Wywołanie procedur¶
--jak się mieści w jednej linii to może być
execute procedure gen_ref('DOKUMNAG') returning_values :dokumplu;
--jeśli się nie mieści
execute procedure inwenta_nalicz_stany(:inwenta, null, null, null, null, 1, 0) --spacje oddzielające zmiennee, przy wywowłaniu funkcji/procedury bez spacji przed nawiasem
returning_values :dummy, :dummy, :dummy; --zwracane zmienne zawsze w nowej linii
execute procedure inwenta_nalicz_stany_mag(:inwenta, null, null, null, null, 1)
returning_values :mag, :wartosc;
execute procedure inwenta_nalicz_stany(:inwenta, null, null, null, null, 1, 0,
null, null, null, 15, 12, 'Nowa inwentaryzacja')
returning_values :dummy, :dummy, :dummy;
7.10 Insert-y¶
--jak się zmieści w jednej linii to może być
insert into towary(ref, nazwa) values(:reff, :nazwaa) returning ktm into :ktm;
--jeśli się nie mieści
insert into dostawcy(ref, nazwa, id, miasto, kodp, ulica,
nrdomu, nrlokalu, telefon, fax, email, www,
aktywny, poczta, kraj, krajid, nip, regon, firma,
typ, kodzewn, waluta, bank, rachunek, synchro, company,
powiazany, cpowiat, vatactive, cpwoj16m, kontofk,
vatconnected)
values(:refout, new.nazwa, new.fskrot, new.miasto, new.kodp, new.ulica,
new.nrdomu, new.nrlokalu, neww.telefon, new.fax, new.email, new.www,
new.aktywny, new.poczta, new.kraj, new.krajid, new.nip, new.regon, new.firma,
new.typ, new.kodzewn, new.waluta, new.bank, new.rachunek, 1, new.company,
new.powiazany, new.cpowiat, new.vatactive, new.cpwoj16m, :kontofk,
new.vatconnected) --odpowiadajace kolumny i zmienne na tych samych pozycjach: typ-typ, telefon-telefon
returning ref
into :dummyref;
insert into dostawcy(ref, nazwa, id, miasto, kodp, ulica,
nrdomu, nrlokalu, telefon, fax, email, www,
aktywny, poczta, kraj, krajid, nip, regon, firma,
typ, kodzewn, waluta, bank, rachunek, synchro, company,
powiazany, cpowiat, vatactive, cpwoj16m, kontofk,
vatconnected)
select :refout, new.nazwa, new.fskrot, new.miasto, new.kodp, new.ulica,
new.nrdomu, new.nrlokalu, new.telefon, new.fax, new.email, new.www,
new.aktywny, new.poczta, new.kraj, new.krajid, new.nip, new.regon, new.firma,
new.typ, new.kodzewn, new.waluta, new.bank, new.rachunek, 1, new.company,
new.powiazany, new.cpowiat, new.vatactive, new.cpwoj16m, :kontofk,
new.vatconnected
from dokumpoz dp --aliasy
where dp.ref = :dkdkdk
and dp.sksks = kdkd;
7.11 Update-y¶
--jak się zmieści w jednej linii to może być
update wersje w set w.nazwa = 'Ala ma kota' where w.ref = :reff;
--jeśli się nie mieści
update wersje w --aliasy
set w.symbol_dost = new.symbol_dost,
w.bksymbol = new.bksymbol, --każdy od nowej linii
w.cena_zakn = new.cena_zakn,
w.cena_zakb = --tutaj case jest w środku ponieważ jest powiązany logicznie z poprzednim polem netto-brutto, a umieszczenie go na końcu znacząco utrudniłoby CR i czytelność kodu
case w.ref
when 1 then 8
when 6 then 9
else 10
end,
w.cena_zakb = new.cena_zakb
w.cena_fabn = new.cena_fabn,
w.cena_fabb = new.cena_fabb
w.cena_kosztn = new.cena_kosztn,
w.cena_kosztb = new.cena_kosztb
w.dniwazn = new.dniwazn,
w.marzamin = new.marzamin,
w.cena_zaknl = new.cena_zaknl,
w.kodkresk = new.kodkresk
where w.ktm = old.ktm
and w.nrwersji = 0;
7.12 Delete-y¶
--jak się zmieści w jednej linii to może być
delete from wersje w where w.ref = :wersjaref;
--jeśli się nie mieści
delete
from towary t
where t.nazwa containing 'test'
or (t.akt = 0
and t.usluga = 12)
or t.witryna = 0
or t.cena_zakn = 0
or not exists(select first 1 1 from pozzam pz where pz.ktm = t.ktm);
7.13 Konkatenacje¶
--konkatrenacje zawierajace mix tekstu i wartości zmiennych piszemy ciurkiem
msg = 'Nie mozesz dla dokumentu magazynowego Nie mozesz dla dokumentu '
|| 'magazynowego Nie mozesz dla dokumentu magazynowego Nie mozesz dla ' --symbol konkatenacji na początku
|| 'dokumentu magazynowego' || new.symbol || ' (' || new.symbolzewn || ') '
|| ' wpisac sposobu dostawy ' || :sposdostnazwa
|| ' poniewaz ten sposob dostawy moze wybrac tylko operator '
|| :opernazwa || ' Jak chcesz to zrobic skontaktuj sie z ' || :superadminnazwa
|| '.';
--jeżeli wypisujemy same wartości zmiennych to każda od nowej linii (np. exceptiony zwracane w celachzdebugowania)
exception test_break 'To jest error Pawla: '
|| new.nrdomu || ', ' --jeśli nie podajemy w tekście nazwy zmiennej to przecinak na końcu dla poprawieniaczytelności
|| coalesce(new.nrlokalu, '<pusty>') || ', ' --każda zmienna od nowej linii
|| coalesce(new.telefon, '<pusty>') || ', '
|| coalesce(new.fax, '<pusty>') || ', '
|| new.email
|| ', a w tym miejscu chce sobie cos tam napisac i bedzie to troche dlugi'
|| 'tekst a moze byc i nawet jeeeeeeeeeeeeeeeszcze dluzszy bo tak '
|| 'mogeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee '
|| new.www || ', '
|| new.aktywny || ', '
|| new.poczta || ', '
|| new.kraj || ', '
|| new.krajid || ', '
|| new.nip || ', '
|| new.regon || ', '
|| new.firma;
--jeśli jest tekst opisujący zmienną to każda linia osobno
exception test_break 'To jest error Pawla:'
|| ' nrdomu '|| new.nrdomu --jesli piszemy nazwę zmiennej to tekst na początku dla poprawienia czytelności
|| ', nrlokalu '|| coalesce(new.nrlokalu, '<pusty>')
|| ', telefon ' || coalesce(new.telefon, '<pusty>')
|| ', fax ' || coalesce(new.fax, '<pusty>')
|| ', email' || new.email
|| ', a w tym miejscu chce sobie cos tam napisac i bedzie to'
|| ' troche dlugi tekst a moze byc i nawet jeeeeeeeeeeeeeeeszcze dluzszy bo'
|| ' tak mogeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee. '
|| 'WWW ' || new.www
|| ', aktywny ' || new.aktywny
|| ', poczta ' || new.poczta
|| ', kraj ' || new.kraj
|| ', krajid ' || new.krajid
|| ', nip ' || new.nip
|| ', regon ' |}| new.regon
|| ', firma ' || new.firma
|| ' Dziekuje, dobranoc!';
8. Przykłady¶
8.1 DOBRZE napisana procedura INWENTA_ZAMKNIJ¶
create or alter procedure INWENTA_ZAMKNIJ (
INWENTA INWENTA_ID,
DOKPLU DEFDOKUM_ID,
DOKMIN DEFDOKUM_ID,
OPERATOR OPERATOR_ID)
returns (
STATUS INTEGER_ID,
DOKSPLU SYMBOL_ID,
DOKSMIN SYMBOL_ID)
AS
declare variable dokummin dokumnag_id;
declare variable dokumplu dokumnag_id;
declare variable datainw timestamp_id;
declare variable magazyn defmagaz_id;
declare variable zpartiami smallint_id;
declare variable dummy integer_id;
declare variable wrongktm ktm_id;
declare variable ktm ktm_id;
declare variable wersja nrwersji_id;
declare variable dostawa dostawa_id;
declare variable ilinw ilosci_mag;
declare variable ilstan ilosci_mag;
declare variable cenaplus ceny;
declare variable datawazn timestamp_id;
declare variable cena ceny;
declare variable ilroz ilosci_mag;
declare variable ilstock ilosci_mag;
declare variable ildok ilosci_mag;
declare variable toinsert ilosci_mag;
declare variable numer integer_id;
declare variable mws smallint_id;
declare variable mwsstock mwsstocks_id;
declare variable wersjaref wersje_id;
begin
--Uwaga!!! EXIT-y!!!
status = -1;
doksplu = '';
doksmin = '';
select i.data, i.magazyn, i.zpartiami, coalesce(dm.mws, 0)
from inwenta i
left join defmagaz dm on dm.symbol = i.magazyn
where i.ref = :inwenta
into :datainw, :magazyn, :zpartiami, :mws;
if (:datainw is null) then
begin
exit;
end
if (not exists (select first 1 1
from inwentap ip
where ip.inwenta = :inwenta)) then
begin
exception inwentaack_nopoz;
end
--sprawdzenie cen przychodowych
select first 1 ip.ktm
from inwentap ip
where ip.inwenta = :inwenta
group by ip.ktm, ip.wersja, ip.cena, ip.dostawa
having min(cenaplus) <> max(cenaplus)
and sum(ilinw) > min(ilstan)
into :wrongktm;
if (coalesce(wrongktm, '') <> '') then
begin
exception inwentaack_nopoz 'Niejednoznaczne ceny przychodowe dla: ' || :wrongktm;
end
--zaktualizowanie stanu wg dokumentow
execute procedure inwenta_nalicz_stany(:inwenta, null, null, null, null, 1, 0,
null)
returning_values :dummy, :dummy, :dummy;
if (:mws = 0) then
begin
--dokument przychodowy
if (:dokplu <> '') then
begin
for
select ip.ktm, ip.wersja, ip.dostawa, sum(ip.ilinw), max(ip.ilstan),
max(ip.cenaplus), max(d.datawazn)
from inwentap ip
left join dostawy d on d.ref = ip.dostawa
where ip.inwenta = :inwenta
group by ip.ktm, ip.wersja, ip.cena, ip.dostawa
having max(ip.ilstan) < sum(ip.ilinw)
into :ktm, :wersja, :dostawa, :ilinw, :ilstan,
:cenaplus, :datawazn
do begin
if (dokumplu is null) then
begin
numer = 1;
execute procedure gen_ref('DOKUMNAG') returning_values :dokumplu;
insert into dokumnag(ref, magazyn, typ, data, operator)
values(:dokumplu, :magazyn, :dokplu, :datainw, :operator);
end
insert into dokumpoz(dokument, numer, ord, ktm, wersja, ilosc, cena,
dostawa, datawazn)
values(:dokumplu, :numer, 1, :ktm, :wersja, :ilinw-:ilstan, :cenaplus,
:dostawa, :datawazn);
numer = :numer + 1;
end
end
--dokument rozchodowy
if (:dokmin <> '') then
begin
for
select ip.ktm, ip.wersja, ip.dostawa, sum(ip.ilinw), max(ip.ilstan),
ip.cena
from inwentap ip
left join dostawy d on d.ref = ip.dostawa
where ip.inwenta = :inwenta
group by ip.ktm, ip.wersja, ip.cena, ip.dostawa
having max(ip.ilstan) > sum(ip.ilinw)
into :ktm, :wersja, :dostawa, :ilinw, :ilstan,
:cena
do begin
if (:dokummin is null) then
begin
numer = 1;
execute procedure gen_ref('DOKUMNAG') returning_values :dokummin;
insert into dokumnag(ref, magazyn, typ, data, operator)
values(:dokummin, :magazyn, :dokmin, :datainw, :operator);
end
if (:zpartiami <> 1) then
begin
cena = null;
dostawa = null;
end
execute procedure rez_blok_przesun(:magazyn, :ktm, :wersja, :cena,
:dostawa, :ilstan - :ilinw);
insert into dokumpoz(dokument, numer, ord, ktm, wersja, ilosc, cena,
dostawa)
values(:dokummin, :numer, 1, :ktm, :wersja, :ilstan - :ilinw, :cena,
:dostawa);
numer = :numer + 1;
end
end
end
else
begin
--dokumenty przychodowe
for
select ms.ref, ms.quantity, ms.good, ms.vers, ms.lot, w.nrwersji
from mwsconstlocs mc
left join mwsstock ms on mc.ref = ms.mwsconstloc
left join wersje w on w.ref = ms.vers
where mc.wh = :magazyn
and mc.locdest = 6
and ms.quantity < 0
into :mwsstock, :ilroz, :ktm, :wersjaref, :dostawa, :wersja
do begin
--trzeba okreslic czy da sie wystawic dokument na minus
if (:ilroz <> 0 and coalesce(:dokumplu, 0) = 0) then
begin
execute procedure gen_ref('DOKUMNAG') returning_values :dokumplu;
insert into dokumnag(ref, magazyn, typ, data, zrodlo, operator,
operakcept)
values(:dokumplu, :magazyn, :dokplu, current_date, 0, :operator,
:operator);
end
cena = 0;
select max(ip.cenaplus)
from inwentap ip
where ip.inwenta = :inwenta
and ip.wersjaref = :wersjaref
and (ip.dostawa = :dostawa
or :zpartiami = 0)
into :cenaplus;
if (:ilroz < 0) then
begin
insert into dokumpoz (dokument, ktm, wersjaref, ilosc, cena, dostawa,
takefrommwsstock)
values (:dokumplu, :ktm, :wersjaref, abs(:ilroz), :cenaplus, :dostawa,
:mwsstock);
end
end
--dokumenty rozchodowe
for
select ip.dostawa, ip.wersjaref, ip.wersja, ip.ktm, ip.cena, abs(ip.mwsilroz)
from inwentap ip
where p.inwenta = :inwenta
and ip.mwsilroz < 0
order by ip.ref
into :dostawa, :wersjaref, :wersja, :ktm, :cena, :ilroz
do begin
--tyle trzeba - teraz sprawdzamy ile jeszcze można wygenerowac z lokacji inwentaryzacyjnej
ilstock = 0;
if (:zpartiami <> 1) then
begin
cena = null;
dostawa = null;
end
for
select ms.ref, ms.quantity - ms.blocked
from mwsconstlocs mc
left join mwsstock s on ms.mwsconstloc = mc.ref
where mc.wh = :magazyn
and mc.locdest = 6
and ms.vers = :wersjaref
and (ms.lot = :dostawa
or :zpartiami <> 1)
into :mwsstock, :ilstock
do begin
ildok = 0;
-- sprawdzenie ile juz jest z tego stanu na dokumencie
if (:dokummin is not null and :dokummin > 0 and :ilroz > 0) then
begin
select coalesce(sum(dp.ilosc),0)
from dokumpoz dp
where dp.dokument = :dokummin
and dp.wersjaref = :wersjaref
and dp.takefrommwsstock = :mwsstock
into :ildok;
ilstock = :ilstock - :ildok;
if (:ilstock < 0) then
begin
ilstock = 0;
end
end
if (:ilstock > 0 and :ilroz > 0) then
begin
toinsert = 0;
if (:ilroz > :ilstock) then
begin
toinsert = :ilstock;
end
else
begin
toinsert = :ilroz;
end
if (:toinsert > 0 and coalesce(:dokummin, 0) = 0) then
begin
execute procedure gen_ref('DOKUMNAG') returning_values :dokummin;
insert into dokumnag(ref, magazyn, typ, data, zrodlo, operator,
operakcept)
values(:dokummin, :magazyn, :dokmin, current_date, 0, :operator,
:operator);
end
execute procedure rez_blok_przesun(:magazyn, :ktm, :wersja, :cena,
:dostawa, :toinsert);
insert into dokumpoz (dokument, ktm, wersjaref, ilosc, cena, dostawa,
takefrommwsstock)
values (:dokummin, :ktm, :wersjaref, :toinsert, :cena, :dostawa,
:mwsstock);
ilroz = :ilroz - :toinsert;
end
end
end
end
if (coalesce(:dokumplu, 0) > 0) then
begin
update dokumnag dn
set dn.akcept = 1
where dn.ref = :dokumplu
returning dn.symbol
into :doksplu;
end
if (coalesce(dokummin, 0) > 0) then
begin
update dokumnag dn
set dn.akcept = 1
where dn.ref = :dokummin
returning dn.symbol
into :doksmin;
end
update inwenta i
set i.inwp = :dokumplu,
i.inwm = :dokummin,
i.datazamk = current_date
where i.ref = :inwenta;
execute procedure inwenta_oblnag(:inwenta);
status = 1;
end
8.2 ŹLE napisana procedura INWENTA_ZAMKNIJ¶
create or alter procedure INWENTA_ZAMKNIJ (
INWENTA integer,
DOKPLU varchar(3),
DOKMIN varchar(3),
OPERATOR integer)
returns (
STATUS integer,
DOKSPLU varchar(30),
DOKSMIN varchar(30))
as
declare variable dokummin integer;
declare variable dokumplu integer;
declare variable data timestamp;
declare variable magazyn varchar(3);
declare variable cnt integer;
declare variable zpartiami integer;
declare variable dummy integer;
declare variable wrongktm varchar(40);
declare variable ktm varchar(40);
declare variable wersja integer;
declare variable dostawa integer;
declare variable ilinw numeric(14,4);
declare variable ilstan numeric(14,4);
declare variable cenaplus numeric(14,4);
declare variable datawazn timestamp;
declare variable cena numeric(14,4);
declare variable ilroz numeric(14,4);
declare variable ilstock numeric(14,4);
declare variable ildok numeric(14,4);
declare variable toinsert numeric(14,4);
declare variable numer integer;
declare variable mws smallint;
declare variable mwsstock integer;
declare variable wersjaref integer;
begin
STATUS=-1;
DOKSPLU='';
DOKSMIN='';
select i.DATA, i.MAGAZYN, i.ZPARTIAMI, coalesce(d.mws,0)
from INWENTA i
left join defmagaz d on (d.symbol = i.magazyn)
where i.REF=:inwenta
into :data, :magazyn, :zpartiami, mws;
if(:data is null) then exit;
select count(*) from INWENTAP where INWENTA = :inwenta into :cnt;
if(:cnt is null) then cnt = 0;
if(:cnt = 0) then exception INWENTAACK_NOPOZ;
--sprawdzenie cen przychodowych
wrongktm = '';
select first 1 INWENTAP.KTM
from INWENTAP
where INWENTAP.INWENTA=:inwenta
group by inwentap.ktm,inwentap.wersja,inwentap.cena,inwentap.dostawa
having min(INWENTAP.CENAPLUS)<>max(INWENTAP.CENAPLUS) and sum(INWENTAP.ILINW)>min(INWENTAP.ILSTAN)
into :wrongktm;
if(:wrongktm is not null and :wrongktm<>'') then exception INWENTAACK_NOPOZ 'Niejednoznaczne ceny przychodowe dla: '||:wrongktm;
--zaktualizowanie stanu wg dokumentow
execute procedure INWENTA_NALICZ_STANY(:inwenta,NULL,NULL,NULL,NULL,1,0,NULL) returning_values :dummy,:dummy,:dummy;
if (mws = 0) then
begin
--dokument przychodowy
dokumplu = null;
if(:dokplu <> '') then
begin
for
select INWENTAP.KTM,INWENTAP.WERSJA,INWENTAP.DOSTAWA,
sum(INWENTAP.ILINW),max(INWENTAP.ILSTAN),max(INWENTAP.CENAPLUS),max(DOSTAWY.DATAWAZN)
from INWENTAP
left join DOSTAWY on (DOSTAWY.REF=INWENTAP.DOSTAWA)
where INWENTAP.INWENTA=:inwenta
group by inwentap.ktm,inwentap.wersja,inwentap.cena,inwentap.dostawa
having max(INWENTAP.ILSTAN)<sum(INWENTAP.ILINW)
into :ktm,:wersja,:dostawa,:ilinw,:ilstan,:cenaplus,:datawazn
do begin
if(:dokumplu is null) then
begin
execute procedure GEN_REF('DOKUMNAG') returning_values :dokumplu;
numer = 1;
insert into DOKUMNAG(REF,MAGAZYN,TYP,DATA, OPERATOR)
values(:dokumplu, :magazyn, :dokPLU, :data, :operator);
update INWENTA set INWP=:dokumplu, DATAZAMK=current_date where REF=:inwenta;
end
insert into DOKUMPOZ(DOKUMENT,NUMER,ORD,KTM,WERSJA,ILOSC,CENA,DOSTAWA,DATAWAZN)
values(:dokumplu, :numer, 1, :ktm, :wersja, :ilinw-:ilstan, :cenaplus, :dostawa, :datawazn);
numer = :numer + 1;
end
end
--dokument rozchodowy
dokummin = null;
if(:dokmin <> '') then
begin
for
select INWENTAP.KTM,INWENTAP.WERSJA,INWENTAP.DOSTAWA,
sum(INWENTAP.ILINW),max(INWENTAP.ILSTAN),INWENTAP.CENA
from INWENTAP
left join DOSTAWY on (DOSTAWY.REF=INWENTAP.DOSTAWA)
where INWENTAP.INWENTA=:inwenta
group by inwentap.ktm,inwentap.wersja,inwentap.cena,inwentap.dostawa
having max(INWENTAP.ILSTAN)>sum(INWENTAP.ILINW)
into :ktm,:wersja,:dostawa,:ilinw,:ilstan,:cena
do begin
if(:dokummin is null) then begin
execute procedure GEN_REF('DOKUMNAG') returning_values :dokummin;
numer = 1;
insert into DOKUMNAG(REF,MAGAZYN,TYP,DATA, OPERATOR)
values(:dokummin, :magazyn, :dokmin, :data, :operator);
update INWENTA set INWM=:dokummin, DATAZAMK=current_date where REF=:inwenta;
end
if(:zpartiami<>1) then cena = null;
if(:zpartiami<>1) then dostawa = null;
execute procedure rez_blok_przesun(:magazyn,:ktm,:wersja,:cena,:dostawa,:ilstan-:ilinw);
insert into DOKUMPOZ(DOKUMENT,NUMER,ORD,KTM,WERSJA,ILOSC,CENA,DOSTAWA)
values(:dokummin, :numer, 1, :ktm, :wersja, :ilstan-:ilinw, :cena, :dostawa);
numer = :numer + 1;
end
end
end else if (mws > 0) then
begin
-- dokumenty przychodowe
for
select s.ref, s.quantity, s.good, s.vers, s.lot, v.nrwersji
from mwsconstlocs c
left join mwsstock s on (c.ref = s.mwsconstloc)
left join wersje v on (v.ref = s.vers)
where c.wh = :magazyn and c.locdest = 6 and s.quantity < 0
into mwsstock, ilroz, ktm, wersjaref, dostawa, wersja
do begin
-- trzeba okreslic czy da sie wystawic dokument na minus
if (ilroz <> 0 and (dokumplu is null or dokumplu = 0)) then
begin
execute procedure gen_ref('DOKUMNAG') returning_values dokumplu;
insert into DOKUMNAG(REF, MAGAZYN, TYP, DATA, zrodlo, OPERATOR, OPERAKCEPT)
values(:dokumplu, :magazyn, :dokplu, current_date, 0, :operator, :operator);
update INWENTA set INWP=:dokumplu, DATAZAMK=current_date where REF=:inwenta;
end
cena = 0;
select max(p.cenaplus)
from inwentap p
where p.inwenta = :inwenta and p.wersjaref = :wersjaref and (p.dostawa = :dostawa or :zpartiami = 0)
into cenaplus;
if (ilroz < 0) then
begin
insert into dokumpoz (dokument, ktm, wersjaref, ilosc, cena, dostawa, takefrommwsstock)
values (:dokumplu, :ktm, :wersjaref, abs(:ilroz), :cenaplus, :dostawa, :mwsstock);
end
end
-- dokumenty rozchodowe
for
select p.dostawa, p.wersjaref, p.wersja, p.ktm, p.cena, abs(p.mwsilroz)
from inwentap p
where p.inwenta = :inwenta and p.mwsilroz < 0
order by p.ref
into dostawa, wersjaref, wersja, ktm, cena, ilroz
do begin
-- tyle trzeba - teraz sprawdzamy ile jeszcze można wygenerowac z lokacji inwentaryzacyjnej
ilstock = 0;
if(:zpartiami<>1) then cena = null;
if(:zpartiami<>1) then dostawa = null;
for
select s.ref, s.quantity - s.blocked
from mwsconstlocs c
left join mwsstock s on (s.mwsconstloc = c.ref)
where c.wh = :magazyn and c.locdest = 6 and (s.lot = :dostawa or :zpartiami <> 1) and s.vers = :wersjaref
into mwsstock, ilstock
do begin
ildok = 0;
-- sprawdzenie ile juz jest z tego stanu na dokumencie
if (dokummin is not null and dokummin > 0 and ilroz > 0) then
begin
select coalesce(sum(p.ilosc),0)
from dokumpoz p
where p.dokument = :dokummin and p.wersjaref = :wersjaref and p.takefrommwsstock = :mwsstock
into ildok;
ilstock = ilstock - ildok;
if (ilstock < 0) then
ilstock = 0;
end
if (ilstock > 0 and ilroz > 0) then
begin
toinsert = 0;
if (ilroz > ilstock) then
toinsert = ilstock;
else
toinsert = ilroz;
if (toinsert > 0 and (dokummin is null or dokummin = 0)) then
begin
execute procedure gen_ref('DOKUMNAG') returning_values dokummin;
insert into DOKUMNAG(REF, MAGAZYN, TYP, DATA, zrodlo, OPERATOR, OPERAKCEPT)
values(:dokummin, :magazyn, :dokmin, current_date, 0, :operator, :operator);
update INWENTA set INWM=:dokummin, DATAZAMK=current_date where REF=:inwenta;
end
execute procedure rez_blok_przesun(:magazyn,:ktm,:wersja,:cena,:dostawa,:toinsert);
insert into dokumpoz (dokument, ktm, wersjaref, ilosc, cena, dostawa, takefrommwsstock)
values (:dokummin, :ktm, :wersjaref, :toinsert, :cena, :dostawa, :mwsstock);
ilroz = ilroz - toinsert;
end
end
end
end
if(:dokumplu > 0) then begin
update DOKUMNAG set AKCEPT=1 where REF=:dokumplu;
select SYMBOL from DOKUMNAG where REF=:dokumplu into :doksplu;
update INWENTA set INWP=:dokumplu, DATAZAMK=current_date where REF=:inwenta;
end
if(:dokummin > 0) then begin
update DOKUMNAG set AKCEPT=1 where REF=:dokummin;
select SYMBOL from DOKUMNAG where REF=:dokummin into :doksmin;
update INWENTA set INWM=:dokummin, DATAZAMK=current_date where REF=:inwenta;
end
execute procedure INWENTA_OBLNAG(:inwenta);
STATUS=1;
end