[es] - Automatsko određivanje broja

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

^{25.11.2011. u 15:25 - pre 150 meseci}

Citat:

Naravno da je to nepotrebno, međutim imam takve (čudne) zahtjeve radi lakšeg održavanja.

Cela prica o relacionoj teoriji izmisljena je zbog lakseg odrzavanja. Ako ovaj 'cudni' zahtev dovodi do problema u odrzavanju, onda je zahtev kontradiktoran. No, salu na stranu, da vidiomo sta se moze uciniti.

Citat:

Prijedlog je također da se odustane od id-a, te da se za PK proglase vrsta+broj. Međutim, tada dolazi do problema kod ažuriranja, potrebno je ažurirati i sve stavke (možda nepotreban utrošak resursa).

Predlog da se odustane od id-a je dobar predlog. Parovi (vrsta,broj) svakako moraju biti ejdinstveni - UNIQUE pa ispada da je (vrsta,broj) alternativni jedinstveni kljuc, a jedinstveni kljuc je kljuc. Zasto bi uvodio neprirodni kljuc, ako vec moras da odrzavas parove (vrsta,broj) da budu jedinstveni? Azuriranje slozenog kljuca neije problem, danas je 2011 godina, ne brini o resursima toliko, nismo u 1974. godini. A zar uvodjenje nove kolone Id nije takodje nekakav utrosak resursa?

Sto se tice kolone prirodni_broj, to je verovatno broj koji se vidi odstampan na temeljnici. Ocigledno je da se prirodni_broj dobija po formuli:

prirodni_broj =RIGHT( '00' + CAST(Vrsta AS int),2) + '-' + CAST(Broj AS int)

Ova formula vazi ako imamo najvise 99 vrsta. Za vise od 99, treba promeniti pocetak u RIGHT( '000' + CAST(Vrsta AS int),3) . Koliko je nula napred, toliki je broj na kraju RIGHT funkcije.

Imas dve opcije:

a) uvedes prirodni_broj kako izracunatu kolonu:

Code:

CREATE TABLE Temeljnice
(
CREATE TABLE Temeljnice
(
Vrsta int NOT NULL
, Broj int NOT NULL
, prirodni_broj AS RIGHT( '00' + CAST(Vrsta AS int),2) + '-' + CAST(Broj AS int) PERSISTED
-- PERSISTED fizicki cuva izracunatu vrednost u tabeli i ne izracunava je nanovo dok se ne promene paramatri
-- bez PERSISTED, kolona se izracunava nanovo svaki put kad se pozove
-- POsto je prirodni_broj = f(PK), ne ocekuju se ceste promene parametara pa PERSISTED stedi
-- vreme i napor CPU ali trosi prostor na disku, sto je danas jeftino
, CONSTRAINT PK_Temelnice PRIMARY KEY (Vrsta, Broj)
)
)

b) prirodni_broj ne postoji u tabel, a napravis view koji ce da vide korisnici:

Code:

CREATE TABLE Temeljnice
(
Vrsta int NOT NULL
, Broj int NOT NULL
, CONSTRAINT PK_Temelnice PRIMARY KEY (Vrsta, Broj)
)
;
CREATE VIEW vwTemeljnice AS
SELECT Vrsta , Broj, prirodni_broj = RIGHT( '00' + CAST(Vrsta AS int),2) + '-' + CAST(Broj AS int)
FROM Temeljnice
;

Sto se tice inkrementovanja kolone Broj, to se resi u stored proceduri, koju ce front end pozivati kada se kreira nova temelnica. Moze ovako nekako (piem kod iz glave direktno u poruku, shavti ovo kao pseudo-kod):

Code:

CREATE PROCEDURE uspTemeljnicaINSERT (@Vrsta int)
AS
DECLARE @TrenutniNajveciBroj int -- trenutno najveci broj za datu vrstu

@TrenutniNajveciBroj = COALESCE ((SELECT MAX(Broj) FROM Temeljnice WHERE vrsta = @vrsta),0)
-- COALSECE je za one slucajeve kad jos uvek ne postoji ni jedna temelnica za zadatu vrstu

INSERT INTO Temeljnice (Vrsta,Broj)
SELECT Vrsta = @Vrsta, Broj = @TrenutniNajveciBroj + 1
RETURN

Procedura radi isto bez obzira da li u tabeli cuvas izracunatu kolonu ili ne. Izracunata kolna ne ide u INSERT INTO.

Ispade na kraju da u stvari i nemas nikakav problem.

Ima jedno prosirenje problema, koje te ceka u sledecm koraku. Ovde se radi o temelnjicama, koje imaju i stavke. Temeljnica bez stavki ne ide. Znaci da bi trebalo nekako prosiriti proceduru, da mozemo da poslajemo i stavke i zavrsimo posao u jednom potezu. Front end pokupi listu stavki, ta se lista posalje proceduri, procedura izracuna novi broj, BEGIN TRANSACTION , insertuje se novi red Temelnjice, insertuju se stavke u Stavke COMMIT TRANSACTION. Intersantno pitanje ovde je: kako poslati stavke proceduri? Odgovor zavisi od ogranicenja u procesu, da li je broj stavki po temelnici ogranicen ili nije. Kad to saznamo, onda mozemo dalje.

Srecan rad.

:-)

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Automatsko određivanje broja

^{02.12.2011. u 19:15 - pre 150 meseci}

OK, evo kako se kroz proceduru u jednom potezu dodaju i temelnica i stavke.

Obicno bih trazio da das skripte za tabele, ali posto se radi o demonstraciji samo, uradicu to sam, a ti vidi kako se cela stvar moze primeniti na tvoj konkretan slucaj.

Code:

IF Object_ID('H_DetaljiTemeljnice') IS NOT NULL DROP TABLE H_DetaljiTemeljnice
;
IF Object_ID('H_Artikli') IS NOT NULL DROP TABLE H_Artikli
;
IF Object_ID('H_Temeljnice') IS NOT NULL DROP TABLE H_Temeljnice
GO
CREATE TABLE H_Temeljnice
    (
         Vrsta INT NOT NULL,
         Broj  INT NOT NULL,
         Prirodni_Broj AS RIGHT('00' + Cast(Vrsta AS varchar), 2) + '-' + Cast(Broj AS varchar) PERSISTED
    -- PERSISTED fizicki cuva izracunatu vrednost u tabeli i ne izracunava je nanovo dok se ne promene paramatri
    -- bez PERSISTED, kolona se izracunava nanovo svaki put kad se pozove
    -- POsto je prirodni_broj = f(PK), ne ocekuju se ceste promene parametara pa PERSISTED stedi
    -- vreme i napor CPU ali trosi prostor na disku, sto je danas jeftino
        ,
         CONSTRAINT PK_H_Temeljnice PRIMARY KEY (Vrsta, Broj)
    )
;
GO
CREATE TABLE H_Artikli
(
SifraARtikla varchar(10) NOT NULL,
NazivArtikla varchar(255) NOT NULL,
JedinicaMere varchar(20) NOT NULL,
TrenutnaCena money NOT NULL,
CONSTRAINT PK_H_ARtikli PRIMARY KEY (SifraArtikla)
)
;
GO
CREATE TABLE H_DetaljiTemeljnice
    (
         Vrsta         INT NOT NULL,
         Broj          INT NOT NULL,
         RbSTavke      INT NOT NULL,
         SifraARtikla  varchar(10) NOT NULL,
         Kolicina      INT NOT NULL,
         JedinicaMere  varchar(20) NOT NULL,
         Cena          MONEY NOT NULL,
         CONSTRAINT PK_H_StavkeTemeljnice PRIMARY KEY ( Vrsta, Broj, RbSTavke ),
         CONSTRAINT FK_STavke FOREIGN KEY (Vrsta, Broj) REFERENCES H_Temeljnice (Vrsta, Broj),
         CONSTRAINT FK_Artikli FOREIGN KEY (SifraARtikla) REFERENCES H_Artikli (SifraARtikla)
    )
;
GO
-- Nekoliko artikala za primer
INSERT INTO H_ARtikli (SifraArtikla, Nazivartikla, JedinicaMere,TrenutnaCena)
SELECT 'PivoLav','Pivo Lav','6 pack',10.80
UNION
SELECT 'Mleko1pc','Mleko 1%','Kesa 3*1.5 l',4.29
UNION
SELECT 'JabukeC','Jabuke crvene','kg',0.99
UNION
SELECT 'Brasno500','Brasno T500','kg',1.20
;
GO

Nama treba stored procedura koju bismo pozvali ovako:

Code:
EXECUTE usp_h_TemeljnicaINSERT (@Vrsta = 1, @Artikli = 'PivoLav,100;JabukeC,40;Mleko1pc,20')

Dakle, saljemo listu artikala i kolicina kroz string @Artikli. Pretpostavlja se da ce aplikacija da sagradi string i da iz aplikacije mozes da posoves proceduru.

Prvo cemo dati funkciju koja string pretvara u tabelu. Uoci da string @Artikli = 'PivoLav,100;JabukeC,40;Mleko1pc,20' ima delimitere ',' i ';'. Tacka-zarez odvaja paroive (artikl,cena) a zarez odvaja elemente para.

Ima jedan sajt na kome gospodin Erland Sommarskog, SQL Server MVP, diskutuje mnoge vazne probleme, a medju njima i problem pretvaranja stringa u tabelu.
Ovo je sajt i poglavlje o listama: http://www.sommarskog.se/arrays-in-sql.html

Diskusija je obimna i sveobuhvatna, i predlazem da je procitate svakako. Elem, sa Erlandovog sajta, skinuo sam jednu funkciju i ona izglea ovako:

Code:
IF Object_ID('H_iter_charlist_to_table') IS NOT NULL DROP FUNCTION H_iter_charlist_to_table
GO
CREATE FUNCTION H_iter_charlist_to_table
                 (@list      ntext,
                  @delimiter nchar(1) = N',')
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          str     varchar(4000),
                          nstr    nvarchar(2000)) AS

BEGIN
   DECLARE @pos      int,
           @textpos  int,
           @chunklen smallint,
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000),
           @tmpval   nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SET @chunklen = 4000 - datalength(@leftover) / 2
      SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
      SET @textpos = @textpos + @chunklen

      SET @pos = charindex(@delimiter, @tmpstr)

      WHILE @pos > 0
      BEGIN
         SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
         INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
         SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
         SET @pos = charindex(@delimiter, @tmpstr)
      END

      SET @leftover = @tmpstr
   END

   INSERT @tbl(str, nstr)
       VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
END
GO

Funkcija prihvata dva parametra, @List i @Delimiter. Na primer, lista '1,2,3' treba da se vrati kao tri reda, delimiter je zarez. Proverite kako radi:

Code:
SELECT * FROM dbo.H_iter_charlist_to_table ('1,2,3',',');

Za nas slucaj, mozemo da uradimo ovo:

Code:
SELECT * FROM dbo.H_iter_charlist_to_table ('PivoLav,10;Mleko1pc,20;JabukeC,30',';')
;
-- Rezultat:
listpos    str    nstr
1    PivoLav,10    PivoLav,10
2    Mleko1pc,20    Mleko1pc,20
3    JabukeC,30    JabukeC,30

Naravno da nama treba nesto malo slozenije, pa mozemo da napisemo:

Code:
SELECT Rb = F.listpos
             , Par = F.str
             , ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
             , Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
FROM   dbo.H_iter_charlist_to_table ('PivoLav,10;Mleko1pc,20;JabukeC,30', ';') AS F;
-- Rezultat:
Rb    Par            ARtiklID    Kolicina
1    PivoLav,10    PivoLav        10
2    Mleko1pc,20    Mleko1pc    20
3    JabukeC,30    JabukeC        30

Sad treba da nekako ovo sto smo napisali, ubacimo u proceduru koja ce da sev lepo pokupi i upise gde treba. Proceduru cemo napisati postepeno, da se lakse shvati o cemu se radi. Evo kako izgleda procedura koja vraca rezultat koji nam treba. Procedura prbvo izracunava potrebne vrednosti za insert novog reda u tabelu H_Temeljnice, zatim to kombinuje sa poslatom listom i vraca ono sto nam treba da insertujemo uH_DetaljiTemeljnice.

Code:

IF Object_ID('usp_h_TemeljnicaINSERT') IS NOT NULL DROP PROCEDURE usp_h_TemeljnicaINSERT
GO
CREATE PROCEDURE usp_h_TemeljnicaINSERT (@Vrsta INT, @Lista varchar(8000))
AS
    DECLARE @TrenutniNajveciBroj int -- trenutno najveci broj za datu vrstu
    DECLARE @SledeciBroj int         -- broj za jedan veci od trenutno najveceg
    SET @TrenutniNajveciBroj = COALESCE ((SELECT Max(Broj)
                                        FROM   H_Temeljnice
                                        WHERE  vrsta = @vrsta), 0)
                                        ;
    SET @SledeciBroj = 1 + @TrenutniNajveciBroj


    ; WITH ListToTable AS
    (
    SELECT  Rb = F.ListPos
             , Par = F.str
             , ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
             , Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
    FROM   dbo.H_iter_charlist_to_table (@Lista, ';') AS F
    )
    SELECT Vrsta = @Vrsta, Broj = @SledeciBroj,
            RbStavke = T.Rb, SifraArtikla = T.ArtiklID
            , T.Kolicina, A.JedinicaMere, A.TrenutnaCena
    FROM ListToTable AS T
    JOIN dbo.H_Artikli AS A ON A.SifraArtikla = T.ArtiklID

    RETURN
;
GO
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLav,10;Mleko1pc,20;JabukeC,30'
;

Vrsta    Broj    RbStavke    SifraArtikla    Kolicina    JedinicaMere    TrenutnaCena
1        1            1        PivoLav            10            6 pack            10.80
1        1            2        Mleko1pc        20            Kesa 3*1.5 l    4.29
1        1            3        JabukeC            30            kg                0.99

Sada nam treba da procedura koja insertuje podatke:

Code:

-- Dodajemo INSERT INTO u proceduru:
IF Object_ID('usp_h_TemeljnicaINSERT') IS NOT NULL DROP PROCEDURE usp_h_TemeljnicaINSERT
GO
CREATE PROCEDURE usp_h_TemeljnicaINSERT (@Vrsta INT, @Lista varchar(8000))
AS

-- Kontrola ulazne liste, ne zelimo na listi artikle koji ne postoje
IF EXISTS (
            SELECT L.ArtiklID
            FROM (
                    SELECT ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
                    FROM dbo.H_iter_charlist_to_table (@Lista, ';') AS F
                    ) AS L
            LEFT JOIN H_Artikli AS A ON A.SifraArtikla = L.ARtiklID
            WHERE A.SifraArtikla IS NULL
            )
    BEGIN
        PRINT 'Jedan ili vise artikala na datoj listi ne postoji u tabeli H_Artikli. Procedura stopirana. Transakcija nije ni pocela.'
        RETURN
    END

-- Izracunajmo sledeci broj temeljnice:
DECLARE @TrenutniNajveciBroj int -- trenutno najveci broj za datu vrstu
DECLARE @SledeciBroj int         -- broj za jedan veci od trenutno najveceg

-- COALSECE je za one slucajeve kad jos uvek ne postoji ni jedna temelnica za zadatu vrstu
SET @TrenutniNajveciBroj = COALESCE ((SELECT Max(Broj)
                                    FROM   H_Temeljnice
                                    WHERE  vrsta = @vrsta), 0)
;
SET @SledeciBroj = 1 + @TrenutniNajveciBroj
;

-- INSERT u tabele
BEGIN TRY    -- ovo sluzi da uhvatimo greske
    BEGIN TRANSACTION -- treba nam transakcija, jer radimo sa dve tabele

    -- Korak 1: insertujemo red u tabelu Temeljnice
        INSERT INTO H_Temeljnice (Vrsta,    Broj)
                SELECT Vrsta = @Vrsta , Broj = @SledeciBroj
        ;

    -- Korak 2: insertujemo redove u tabelu DetaljiTemeljnice
        ; WITH ListToTable AS
        (
        SELECT  Rb = F.ListPos
                 , Par = F.str
                 , ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
                 , Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
        FROM   dbo.H_iter_charlist_to_table (@Lista, ';') AS F
        )
        INSERT INTO H_DetaljiTemeljnice
                (
                 Vrsta         ,
                 Broj          ,
                 RbSTavke      ,
                 SifraARtikla  ,
                 Kolicina      ,
                 JedinicaMere  ,
                 Cena
                 )
        SELECT  Vrsta = @Vrsta
                , Broj = @SledeciBroj
                , RbStavke = T.Rb
                , SifraArtikla = T.ArtiklID
                , T.Kolicina
                , A.JedinicaMere
                , Cena = A.TrenutnaCena
        FROM ListToTable AS T
        JOIN dbo.H_Artikli AS A ON A.SifraArtikla = T.ArtiklID
        ;


    -- ako do ove tacke  nije bilo greske, mozemo da zavrsiomo posao
    COMMIT TRANSACTION
    PRINT 'TRansakcija uspeco izvrsena.'
END TRY
BEGIN CATCH
-- Ovde ulazimo ako i samo ako je u BEGIN TRY- END TRY bloku dolso do greske
    IF (XACT_State()) = -1    -- otvorena transakcija, COMMIT nije moguc
        BEGIN
            PRINT 'Doslo je do greske i transakcija je ponistena!'
            ROLLBACK TRANSACTION;
        END
    ELSE IF (XACT_State()) = 1    -- otvorena transakcija, COMMIT moguc
        BEGIN
            PRINT 'Doslo je do greske, transakcija bi mogla d aprodje, ali je ponistena za svaki slucaj!'
            ROLLBACK TRANSACTION; -- ovde je moglo i COMMIT
        END
    ELSE    -- greska pre otvaranja transakcije
        BEGIN
            PRINT 'Doslo je do greske, transakcija nije ni krenula!'
            ROLLBACK TRANSACTION;
        END
    -- Na kraju pokazemo poruku o gresci!
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

EXIT_HERE:
RETURN
;
GO

Da vidimo kako radi:

Code:

-- Ovo se ocekuje da prodje bez problema:
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLav,1;Mleko1pc,2;JabukeC,3'
;

SELECT T.Prirodni_Broj
    , D.RbStavke
    , D.SifraArtikla
    , D.Kolicina
    , D.JedinicaMere
    , D.Cena
FROM H_temeljnice AS T
JOIN H_DetaljiTemeljnice AS D ON D.Vrsta = T.Vrsta AND D.Broj =  T.Broj
ORDER BY T.Vrsta, T.Broj , D.RbStavke
;

Prirodni_Broj    RbStavke    SifraArtikla    Kolicina    JedinicaMere    Cena
01-1                1        PivoLav                1        6 pack            10.80
01-1                2        Mleko1pc            2        Kesa 3*1.5        l4.29
01-1                3        JabukeC                3        kg                 0.99

Ovo ne bi smelo da prodje, ne postoji 'PivoLeopard'.

Code:

-- Ovo ne bi smelo da prodje:
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLeopard,111;Mleko1pc,222;JabukeCrveniDelisesKarlovacki,3333'
;

Proceduru mozete nadgraditi, tako da proverava i da lista nije prazna. Ako je lista prazna, ne raditi nista. ne zelimo da imamo temeljnicu bez ijedne stavke. Ako i to dodamo, onda procedura postaje solidan interfejs za tabele Temeljnice i DetaljiTemeljnice.

Pzanja! Procedura radi sta treba da radi, ali ne vraca posebno koristan rezultat, osim poruka. Od aplikacije koja poziva proceduru, zavisi sta procedura treba da vrati. Mozete da vrartite neki integer, koji nesto znaci. A mozete da vratite i SELECT 'sve sto je uneseno'. Aplikacija koja poziva proceduru ce onda nesto uraditi sa vracenim rezultatom, na primer pretvoriti vraceni rezultat, ili jedan deo, u nekakvu poruku za korisnika,

Srecan rad.

:-)

Odgovor na temu