Procedury i funkcje pl/sql

Witam,
Na początku zaznaczę, że właśnie uczę się tego języka i mam problem z funkcjami i procedurami. Problem jest bardzo podobny, ponieważ, w przypadku poniższej procedury, chciałbym aby do tabeli faktury i kolumny o nazwie f_kara_za_zwloke dodawana byla obliczona kwota (jak ponizej), ale tylko w przypadku gdy faktura nie jest oplacona (warunek if). Procedura dodaje niestety do wszystkich krotek jedną i tę samą kwotę. Proszę o wskazówki, gdzie leży przyczyna błędu.

CREATE OR REPLACE PROCEDURE uaktualnij_faktury IS
BEGIN
  FOR c_rec IN (SELECT * FROM faktury) LOOP
    IF(c_rec.f_czy_zaplacona = 'N') THEN
      UPDATE faktury 
      SET f_kara_za_zwloke = (to_date('01-01-2008', 'DD-MM-YYYY')-c_rec.f_data_platnosci)*0.001;
    END IF;
  END LOOP;
END uaktualnij_faktury;

W przypadku funkcji jest podobnie, gdyż chciałbym, aby wyznaczała dla każdego id_faktury kwotę za wszystkie produkty z danej faktury. W wyniku otrzymuje jednak kwotę dla pierwszej faktury ale we wszystkich krotkach.

CREATE OR REPLACE FUNCTION kwota_faktury(f_id_faktury NUMBER) RETURN NUMBER IS
CURSOR temp IS
SELECT SUM(round(((p_cena_jednostkowa*p_stawka_vat)+p_cena_jednostkowa)*p_ilosc, 2)) 
FROM faktury, pozycje WHERE f_id_faktury = p_f_id_faktury
GROUP BY f_id_faktury;
v_id faktury.f_id_faktury%type;
v_kwota_faktury NUMBER;
BEGIN
  OPEN temp;
  LOOP 
    FETCH temp INTO v_kwota_faktury;
    RETURN v_kwota_faktury;
    EXIT WHEN temp%NOTFOUND;
  END LOOP;
  CLOSE temp;
END kwota_faktury;

Z góry dzięki za pomoc

Co do pierwszej procedury - jeżeli w UPDATE nie podasz warunku (takie WHERE na końcu :) ) to uaktualnia wszystkie wiersze, musisz podać id faktury w warunku aby zaktualizował tylko tę co chcesz.

Dzięki, z funkcją też już sobie poradziłem:

CREATE OR REPLACE FUNCTION kwota_faktury(id_faktura IN NUMBER) 
RETURN NUMBER IS
wynik NUMBER; 
BEGIN
  SELECT SUM(round(((p_cena_jednostkowa*p_stawka_vat)+p_cena_jednostkowa)*p_ilosc, 2)) INTO wynik 
  FROM pozycje
  WHERE p_f_id_faktury = id_faktura;
  RETURN wynik; 
END kwota_faktury;

Jeszcze dwa pytanka. Czy podczas tworzenia sekwencji istnieje możliwość pobrania najwyższej liczby z jakiejś tabeli, aby właśnie tę wartość ustawić jako "START WITH"?
Np.:

CREATE SEQUENCE faktury_seq
START WITH 80  -- I tu zamiast 80 (to nie dziala, ale cos w ten desen czy jest mozliwe?) (SELECT MAX(f_id_faktury) FROM faktury) 
INCREMENT BY 10
MINVALUE 10;

I drugie, jak mogę zabezpieczyć (korzystając z wyzwyalacza), by nie można było zmieniać id_faktury. To nie działa:

CREATE OR REPLACE TRIGGER korekta2
BEFORE UPDATE OF p_f_id_faktury ON pozycje 
FOR EACH ROW 
DECLARE
zmienna NUMBER;
BEGIN
  SELECT p_f_id_faktury INTO zmienna
  FROM pozycje WHERE zmienna = :NEW.p_f_id_faktury;
  IF :NEW.p_f_id_faktury != zmienna THEN
    RAISE_APPLICATION_ERROR(-20001,'Nie wolno przenieść pozycji do innej faktury');
  END IF;
END;

Dzięki.

Czy podczas tworzenia sekwencji istnieje możliwość pobrania najwyższej liczby z jakiejś tabeli, aby właśnie tę wartość ustawić jako "START WITH"?

Wprost się nie da - składnia nie przewiduje.
Ale możesz użyć dynamicznego sql-a, na przykład tak:

DECLARE
   x PLS_INTEGER;
BEGIN
  SELECT max(f_id_faktury) INTO x FROM faktury;
  EXECUTE IMMEDIATE  q'{
       CREATE SEQUENCE faktury_seq
       START WITH }' || x ||
       q'# INCREMENT BY 10
       MINVALUE 10 #';
END;
/

Carter napisał(a)

I drugie, jak mogę zabezpieczyć (korzystając z wyzwyalacza), by nie można było zmieniać id_faktury. To nie działa:

Najprościej chyba tak:

CREATE OR REPLACE TRIGGER korekta2
BEFORE UPDATE OF p_f_id_faktury ON pozycje 
FOR EACH ROW 
BEGIN
    RAISE_APPLICATION_ERROR(-20001,'Nie wolno przenieść pozycji do innej faktury');
END;
/ 

Insert działa, delete działa, ale update się wywali

insert into pozycje( p_f_id_faktury) values( 1 );
insert into pozycje( p_f_id_faktury) values( 2 );
delete from pozycje where p_f_id_faktury = 1;
update pozycje set p_f_id_faktury = 3 where p_f_id_faktury = 2;

1 rows inserted.
1 rows inserted.
1 rows deleted.

Error starting at line 41 in command:
update pozycje set p_f_id_faktury = 3 where p_f_id_faktury = 2
Error report:
SQL Error: ORA-20001: Nie wolno przenieść pozycji do innej faktury
ORA-06512: at "TEST.KOREKTA2", line 2
ORA-04088: error during execution of trigger 'TEST.KOREKTA2'
 

Wielkie dzięki!

Carter napisał(a)

Jeszcze dwa pytanka. Czy podczas tworzenia sekwencji istnieje możliwość pobrania najwyższej liczby z jakiejś tabeli, aby właśnie tę wartość ustawić jako "START WITH"?
Np.:

CREATE SEQUENCE faktury_seq
START WITH 80  -- I tu zamiast 80 (to nie dziala, ale cos w ten desen czy jest mozliwe?) (SELECT MAX(f_id_faktury) FROM faktury) 
INCREMENT BY 10
MINVALUE 10;

Ja bym chciał wiedzieć po co Ci taki trik? Pachnie mi to problemami. Sekwencje powinny żyć swoim życiem bez wyciągania żadnych max czy czegoś innego. Zawsze jest zagrożenie że zostanie zrobiony insert i max już nie będzie taki sam gdy wyciągałeś a wstawiłeś. Tutaj trzeba uważać i dlatego pytam o cel takiego działania.

Bo (oczywiście nie wiem, czy dobrze kombinuje) mam tabelę faktury, gdzie kluczem głównym jest id faktury. Chciałem zrobić wyzwalacz, który obsługuje dodawanie do tabeli faktury pozycji i dzięki sekwencji pozwala dodać wiersz, gdy id faktury nie jest zdefiniowane przez użytkownika. Sekwencja wyszukuje wtedy maxa id z tabeli faktury i ustawia go automatycznie na poziomie max+10. W razie błędu w myśleniu proszę o korektę. Z góry dzięki.

Carter napisał(a)

Bo (oczywiście nie wiem, czy dobrze kombinuje) mam tabelę faktury, gdzie kluczem głównym jest id faktury. Chciałem zrobić wyzwalacz, który obsługuje dodawanie do tabeli faktury pozycji i dzięki sekwencji pozwala dodać wiersz, gdy id faktury nie jest zdefiniowane przez użytkownika. Sekwencja wyszukuje wtedy maxa id z tabeli faktury i ustawia go automatycznie na poziomie max+10. W razie błędu w myśleniu proszę o korektę. Z góry dzięki.

Gdybyś podał konkretny przykład to byłoby dla mnie jaśniej(świąteczna atmosfera i procesy trawienne zaburzają myślenie abstrakcyjne ;)). Ogólnie zastanawia mnie co kryje się u Ciebie pod pojęciem:

(...) id faktury nie jest zdefiniowane przez użytkownika.

Przy tego typu systemach powinien być jakiś domyślny nagłówek/schemat i on zawsze będzie działał z automatu, chyba że użytkownik zmieni. Wtedy nie będziesz chyba musiał go ustawiać poprzez max(). Napisz przykładową sytuację z Twoje systemu i wymyślimy coś innego - ja bym w programie nie modyfikował żadnych sekwencji - chyba że podczas pierwszego startu bazy. Jak Ci się sypnie sekwencja to wtedy będzie problem.

Oto tabela przykładowa:

ID_FAKTURY 	DATA_WYSTAWIENIA 	DATA_PLATNOSCI     CZY_ZAPLACONA	
10 	        07/01/13 	        07/01/27 	  T 	
20 	        07/03/18 	        07/03/25           N 	
30 	        07/04/23 	        07/05/07 	  T 	
40 	        07/07/09 	        07/07/19 	  N 	
50 	        07/11/15 	        07/11/29 	  T 

I teraz chodzi o to, by dodając pozycję, np:

INSERT INTO faktury (data_wystawienia, data_platnosci) 
VALUES (SYSDATE, SYSDATE+14);

nie było obowiązkowe podawanie id_faktury. W takim wypadku (gdy użytkownik nie zdefiniował tego id), id faktury jest atomatycznie generowane przez sekwencję, która wyszukuje max id z tych w tabeli i wstawia kolejne (w tym przypadku 60).

W przypadku natomiast, gdy użytkownik wpisze coś takiego (oczywiście po uwzględnieniu poprzedniej linii):

INSERT INTO faktury (id_faktury, data_wystawienia, data_platnosci) 
VALUES (60, SYSDATE, SYSDATE+14);

Wyzwalacz generuje błąd, że faktura z takim id już istnieje.

Jaki używasz silnik db? Czemu potrzebujesz id_faktury jako kolejne 10? To na mój gust jest niepotrzebne. W większości silnikach bazy danych możesz pole, które jest kluczem głównym oznaczyć jako autoincrement i wtedy takie pole w przypadku podania wartości null jest wypełniane odpowiednim generatorem - wtedy nawet jest zalecane by robić insert bez podawania wartości dla klucza głównego(id_faktury). Jeśli Id_faktury ma być wartością +=10 to chyba dlatego że wykorzystujesz to w generowaniu jakiegoś czytelnego dla człowieka formatu? Jeśli tak to zarzuć to i po prostu przy generowaniu nagłówka przemnóż id_fakutry * 10 i masz już wartość bez babrania w generatorze/sekwencji.

Hostel napisał(a)

Jaki używasz silnik db?

Silnik Oraclowski ;)

Hostel napisał(a)

Czemu potrzebujesz id_faktury jako kolejne 10? To na mój gust jest niepotrzebne. W większości silnikach bazy danych możesz pole, które jest kluczem głównym oznaczyć jako autoincrement i wtedy takie pole w przypadku podania wartości null jest wypełniane odpowiednim generatorem - wtedy nawet jest zalecane by robić insert bez podawania wartości dla klucza głównego(id_faktury). Jeśli Id_faktury ma być wartością +=10 to chyba dlatego że wykorzystujesz to w generowaniu jakiegoś czytelnego dla człowieka formatu? Jeśli tak to zarzuć to i po prostu przy generowaniu nagłówka przemnóż id_fakutry * 10 i masz już wartość bez babrania w generatorze/sekwencji.

Jakiś czas temu dostałem do zrobienia niewielki projekt związany z językiem pl/sql i w jednym z wymagań było wykorzystanie sekwencji do celów podanych w poprzednim poście. Wtedy u mnie narodziło się pytanie, czy można to jakoś usprawnić, właśnie przez napisanie np. zapytania, które znajdzie jakąś liczbę, od której sekwencja będzie numerowała to id. Muszę przyznać, że nie zdawałem sobie sprawy, że istnieje opcja automatycznej inkrementacji, o której wspomniałeś. Dzięki właśnie takim informacjom, mogę podszkolić swoje umiejętności, za co dziękuje ;)

Niestety dla Ciebie w Oracle'u nie ma autoinkrementacji o której mowa. Musiałbyś jakiś prosty trigger napisać (w google jest dużo przykładów).

b napisał(a)

Niestety dla Ciebie w Oracle'u nie ma autoinkrementacji o której mowa. Musiałbyś jakiś prosty trigger napisać (w google jest dużo przykładów).

Na pewno coś mają. Postgresql bazuje sporo na Oracle i w Pg jest takie coś jak SERIAL - definiujesz takie pole i od razu baza zakłada sekwencję, klucz główny a gdy jest null przy insert to korzysta z generatora.

Hostel napisał(a)

b napisał(a)

Niestety dla Ciebie w Oracle'u nie ma autoinkrementacji o której mowa. Musiałbyś jakiś prosty trigger napisać (w google jest dużo przykładów).

Na pewno coś mają. Postgresql bazuje sporo na Oracle i w Pg jest takie coś jak SERIAL - definiujesz takie pole i od razu baza zakłada sekwencję, klucz główny a gdy jest null przy insert to korzysta z generatora.

Nie mają. Szukałem wcześniej i znajdywałem wpisy na blogach, posty itd. na ogół z lat 2008, 2009 o tym, że nie ma takiej funkcjonalności. Poszukałem specjalnie jeszcze raz.

http://stackoverflow.com/questions/6992315/from-mysql-to-oracle-now-and-autoincrement-id
Odpowiedzi z 9 sierpnia 2011 też to potwierdzają.

Chyba, że ktoś mnie oświeci i jednak pokaże, że się mylę. Nie raz zdarzało mi się nie móc odnaleźć czegoś co powinno być oczywiste :)

b napisał(a)

Hostel napisał(a)

b napisał(a)

Niestety dla Ciebie w Oracle'u nie ma autoinkrementacji o której mowa. Musiałbyś jakiś prosty trigger napisać (w google jest dużo przykładów).

Na pewno coś mają. Postgresql bazuje sporo na Oracle i w Pg jest takie coś jak SERIAL - definiujesz takie pole i od razu baza zakłada sekwencję, klucz główny a gdy jest null przy insert to korzysta z generatora.

Nie mają. Szukałem wcześniej i znajdywałem wpisy na blogach, posty itd. na ogół z lat 2008, 2009 o tym, że nie ma takiej funkcjonalności. Poszukałem specjalnie jeszcze raz.

http://stackoverflow.com/questions/6992315/from-mysql-to-oracle-now-and-autoincrement-id
Odpowiedzi z 9 sierpnia 2011 też to potwierdzają.

Chyba, że ktoś mnie oświeci i jednak pokaże, że się mylę. Nie raz zdarzało mi się nie móc odnaleźć czegoś co powinno być oczywiste :)

No dobrze w Oracle nie ma tak fajnie jak w Postgres ale na podstawie wpisu:
http://blog.creativeprogramming.it/?p=92
można popełnić coś takiego w Oracle:

create table mytable (id number default mytable_seq.nextval,
txt varchar(255));

Hostel napisał(a)

b napisał(a)

Hostel napisał(a)

b napisał(a)

Niestety dla Ciebie w Oracle'u nie ma autoinkrementacji o której mowa. Musiałbyś jakiś prosty trigger napisać (w google jest dużo przykładów).

Na pewno coś mają. Postgresql bazuje sporo na Oracle i w Pg jest takie coś jak SERIAL - definiujesz takie pole i od razu baza zakłada sekwencję, klucz główny a gdy jest null przy insert to korzysta z generatora.

Nie mają. Szukałem wcześniej i znajdywałem wpisy na blogach, posty itd. na ogół z lat 2008, 2009 o tym, że nie ma takiej funkcjonalności. Poszukałem specjalnie jeszcze raz.

http://stackoverflow.com/questions/6992315/from-mysql-to-oracle-now-and-autoincrement-id
Odpowiedzi z 9 sierpnia 2011 też to potwierdzają.

Chyba, że ktoś mnie oświeci i jednak pokaże, że się mylę. Nie raz zdarzało mi się nie móc odnaleźć czegoś co powinno być oczywiste :)

No dobrze w Oracle nie ma tak fajnie jak w Postgres ale na podstawie wpisu:
http://blog.creativeprogramming.it/?p=92
można popełnić coś takiego w Oracle:

create table mytable (id number default mytable_seq.nextval,
txt varchar(255));

Niestety, przy próbie utworzenia:

Error starting at line 9 in command:
create table mytable (id number default sekwencja.nextval,
 txt varchar(255))
Error at Command Line:9 Column:40
Error report:
SQL Error: ORA-00984: column not allowed here
00984. 00000 -  "column not allowed here"
*Cause:    
*Action: 

Zgodnie z dokumentacją Oracle'a ( http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm ):

You cannot use CURRVAL and NEXTVAL in the following constructs:
(...)
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
(...)

Dla 11g też jest ten wpis. Dla wersji wcześniejszych nie sprawdzałem.