jump to navigation

SQL baza podataka za videoteku October 26, 2009

Posted by admin in : SQL , add a comment

Dosta tečajeva koji obrađuju problematiku baza uzima kao glavni zadatak poslovanje videoteke. Iz tog razloga će se ovdje probati riješiti takav “problem”.

Što sve osnovno treba imati takva baza?

Koje podatke člana treba zapisati?

Koje podatke kazeta odnosno objekta posudbe treba zapisati?

Ovdje ću ostaviti mogućnost dopune jer program se uvijek može proširiti u smjeru koji niti nakon pomnog planiranja nije planiran.

Prva skica tablica

MP_CLAN – član videoteke

MP_GRAD

MP_DRZAVA

MP_VALUTA

Kazete odnosno objekte posudbe treba rasšlaniti na podatke koji se odnose na sami film kao takav i na podatke koji se odnose na medij.

Recimo da ćemo nepromjenjive podatke zapisivati u tablicu

VD_NASLOV

Žanrove moramo spremiti u posebnu tablicu

MP_ZANR

Ostaju nam osobe vezane uz film – redatelji, glumci, scenaristi,…

VD_OSOBA

Kako se jedna osoba u istom filmu može pojaviti na više funkcija (npr. Clint Eastwood kao redatelj i glumac), potrebno je imati i tablicu za to (1 redatelj, 2 scenarist, 3 glavna uloga, 4 sporedna uloga, 5 producent,…)

VD_OSOBA_TIP

Sve to je potrebno kako bi se definirala tablica u koju ćemo moći povezati sve osobe bitne za taj film.

VD_NASLOV_OSOBA

… nastavak slijedi …

Zapis u tablicu s automatskim generiranjem šifre October 26, 2009

Posted by admin in : SQL , add a comment

Podaci tablice MP_MJERA će sadržavati podatke koji imaju šifru u slijedu, npr: 1 komad, 2 kilogram, 3 metar,…


CREATE PROCEDURE dbo.mp_mjera_insert
 @mjera            INT,
 @naziv            VARCHAR(60),
 @oznaka            VARCHAR(5),
 @out_mjera        INT        OUT
AS
SET NOCOUNT ON

DECLARE    @err    INT,
 @pomerr    INT,
 @ima    INT

SET @err = -1

SET @pomerr = 1
EXEC @pomerr = dbo.mp_mjera_exists
 @mjera        = @mjera,
 @out_ima    = @ima        OUT
SET @err = @err + CASE @pomerr WHEN -1 THEN 0 ELSE 1 END

IF @ima = 0
 BEGIN
 IF @mjera = 0
 BEGIN
 SELECT    @mjera    = MAX(MJERA)
 FROM    MP_MJERA
 SET @err = @err + @@ERROR

 SET @mjera = ISNULL(@mjera, 0) + 1
 END

 INSERT    INTO    MP_MJERA (
 MJERA,
 NAZIV,
 OZNAKA)
 VALUES     (
 @mjera,
 @naziv,
 @oznaka)
 SET @err = @err + @@ERROR
 END
ELSE
 BEGIN
 UPDATE    MP_MJERA
 SET    NAZIV        = @naziv,
 OZNAKA        = @oznaka
 WHERE    MJERA        = @mjera
 SET @err = @err + @@ERROR
 END

SET @out_mjera    = @mjera

RETURN @err
GO

Tablica s automatskom šifrom, mjerne jedinice, MP_MJERA October 26, 2009

Posted by admin in : SQL , add a comment

U tablici MP_MJERA se nalaze mjerne jedinice kao npr. komad, kilogram, pakiranje, metar, sat i sl.

Šifre mjernih jedinica nisu zadane te će se ovdje pokazati kako zapisivati takve podatke.


CREATE TABLE [dbo].[MP_MJERA] (
 [MJERA] [int] NOT NULL ,
 [NAZIV] [varchar] (60) COLLATE Croatian_CI_AS NOT NULL ,
 [OZNAKA] [varchar] (5) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_MJERA] ADD
 CONSTRAINT [PK_MP_MJERA] PRIMARY KEY  CLUSTERED
 (
 [MJERA]
 )  ON [PRIMARY]
GO

..

Kako provjeriti postojanje sloga na drugi način October 26, 2009

Posted by admin in : SQL , add a comment

U proceduri mp_valuta_exists je bio ovaj dio koda:


SELECT    @out_ima    = COUNT(VALUTA)
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

Dva načina za koji kažu da su bolji od ovo gore navedenog su:

1. Koristi se EXISTS za provjeru


IF EXISTS (SELECT VALUTA FROM MP_VALUTA WHERE VALUTA = @valuta)
 BEGIN
 SET @err = @err + @@ERROR
 SET @out_ima = 1
 END

2. Koristi se varijabla koja vraća broj zahvaćenih slogova, @@ROWCOUNT. Pomoćna varijabla @pom se koristi za prihvat podatka šifre varijable.


SELECT    @pom = VALUTA
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR
SET @out_ima = @@ROWCOUNT

Kako se radi o provjeri primarnog ključa, uvijek će broj vraćenih slogova biti 0 ili 1.

Procedura za selektiranje podataka, SELECT October 26, 2009

Posted by admin in : SQL , add a comment

U ovoj proceduri nema pozivanja neke deuge procedure niti nema provjera. Vrlo je jednostavna, vraća podatke za traženu šifru valute.


CREATE PROCEDURE dbo.mp_valuta_select
 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = ''    OUT,
 @oznaka        CHAR(3)        = ''    OUT
AS
SET NOCOUNT ON

DECLARE    @err    INT
SET @err = -1

SELECT    @naziv        = NAZIV,
 @oznaka        = OZNAKA
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

RETURN @err
GO

Razlike između COUNT(*), COUNT(field) i COUNT(const) October 25, 2009

Posted by admin in : SQL , add a comment

Jedan sažetak nakon rasprave na news grupi:

COUNT(*)

De facto standard za brojanje svih redaka u tablici.

COUNT(field) npr. COUNT(VALUTA)

Vraća broj redaka u tablici za koje polje field nema vrijednost NULL. Za polje koje je NOT NULL vraća broj redaka u tablici.

COUNT(const) npr. COUNT(1)

Uvijek vraća broj redaka u tablici.

Zapis podataka u tablicu pomoću stored procedure October 22, 2009

Posted by admin in : SQL , 15comments

Ova procedura ima dvojaku ulogu, zapisuje slog ukoliko se radi o novom zapisu i radi izmjenu sloga ako se radi o postojećem zapisu.

Zadatak procedure za zapis i iumjenu u našoj tablici MP_VALUTA je

Evo kako bi ta procedura izgledala:


CREATE PROCEDURE dbo.mp_valuta_insert
 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = '',
 @oznaka        CHAR(3)        = '',
 @insert        TINYINT,
 @postoji    TINYINT        OUT
AS
SET NOCOUNT ON

DECLARE @ima        INT,
 @err        INT,
 @pomerr        INT

SET @err = -1

SET @pomerr = 1
EXEC @pomerr = dbo.mp_valuta_exists
 @valuta        = @valuta,
 @out_ima    = @ima        OUT
SET @err = @err + CASE @pomerr WHEN -1 THEN 0 ELSE 1 END
SET @postoji = 0

IF @ima = 0
 BEGIN
 INSERT    INTO    MP_VALUTA (
 VALUTA,
 NAZIV,
 OZNAKA)
 VALUES    (
 @valuta,
 @naziv,
 @oznaka)
 SET @err = @err + @@ERROR
 END
ELSE
 IF @insert = 0
 BEGIN
 UPDATE    MP_VALUTA
 SET    NAZIV        = @naziv,
 OZNAKA        = @oznaka
 WHERE    VALUTA        = @valuta
 SET @err = @err + @@ERROR
 END
 ELSE
 SET @postoji = 1

RETURN @err
GO

Prihvaćaju se podaci u varijable

 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = '',
 @oznaka        CHAR(3)        = '',
 @insert        TINYINT,

rade se deklaracije pomoćnih varijabli, poziva se procedura mp_valuta_exists koja vraća @ima = 1 ako postoji slog sa istom šifrom valute odnosno @ima = 0 ako šifre nema.

Ako šifre nema, radi se INSERT podataka, a ako šifra postoji radi se UPDATE ukoliko smo to željeli (@insert = 0). Ako smo željeli zapisati novi slog (@insert = 1), a šifra valute je već zapisana, output varijabla @postoji će biti postavljena na 1 i neće se izvršiti niti INSERT niti UPDATE.


Procedura za provjeru postoji li slog istog primarnog ključa October 22, 2009

Posted by admin in : SQL , add a comment

Procedura za provjeru postojanja sloga s istim primarnim ključem koristi se kod zapisa odnosno izmjene podataka. Koristi se output varijabla kao rezultat pretrage.

Zadatak procedure za provjeru postojanja sloga u našoj tablici MP_VALUTA je

Evo kako bi ta procedura izgledala:


CREATE PROCEDURE dbo.mp_valuta_exists
 @valuta     CHAR(3),
 @out_ima    INT        OUT
AS

SET NOCOUNT ON

DECLARE    @err    INT
SET @err = -1

SELECT    @out_ima    = COUNT(VALUTA)
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

RETURN @err
GO

Ovdje se koristi funkcija COUNT koja vraća broj slogova tablice. Kao parametar se koristi primary key polje VALUTA ali sam na puno mjesta vidio da se koristi i COUNT(*). Ta solucija je isto ispravna jedino je zanemarivo sporija ukoliko se radi o većoj tablici koja ima više polja.

Output varijabla @out_ima dobija vrijednost broja slogova koji imaju u polju VALUTA vrijednost @valuta. Kako se radi o primarnom ključu, vrijednost može biti samo 0 ili 1.

Procedura za brisanje podataka October 21, 2009

Posted by admin in : SQL , add a comment

Prva procedura koju ću opisati je procedura za brisanje. Ona je najjednostavnija i na njoj se mogu pokazati neke stvari koje će se kasnije stalno ponavljati.

Zadatak procedure za brisanje sloga u našoj tablici MP_VALUTA je

Evo kako bi ta procedura izgledala:

CREATE PROCEDURE dbo.mp_valuta_delete
@valuta        CHAR(3)
AS
SET NOCOUNT ON

DECLARE    @err    INT
SET @err = -1

DELETE
FROM    MP_VALUTA
WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

RETURN @err
GO

Varijabla @valuta prihvaća vrijednost šifre valute koju želimo obrisati.

Svaka SQL naredba vraća poruku o broju zahvaćenih slogova. U procedurama je taj podatak suvišan, a često i smeta te se koristi SET NOCOUNT ON kako se te poruke ne bi javljale.

Varijabla @err je tipa INT i dodijeljena je inicijalna vrijednost -1.

Izvršava se naredba DELETE nad tablicom MP_VALUTA za slog gdje je VALUTA = @valuta.

Odmah nakon izvršavanja naredbe DELETE postoji redak: SET @err = @err + @@ERROR.

@@ERROR je sistemska varijabla samog MS SQL Servera u koju se zapisuje rezultat izvršavanja posljednje SQL naredebe. Ako nije došlo do greške, @@ERROR je 0, a ako je bila greška, zapisuje se broj te greške koji je uvijek neki broj veći od nule. Bitno je vrijednost preuzeti nakon samog izvođenja naredbe jer se vrijednost prepisuje s novom vrijednošću nakon izvršavanja ideuće naredbe.

Analogno tome, ako nije došlo do greške na kraju vrjednost lokalne varijable mora biti -1. U slučaju bilo kakve greške vrijednost je veća od -1.

Na kraju se vraća vrijednost varijable @err. To nije output varijabla nego tzv. return value.

Jako je korisna kada se procedura poziva iz neke druge procedure.

Procedure, stored procedures ili skraćeno “store” October 21, 2009

Posted by admin in : SQL , add a comment

Za svaku jednostavnu tablicu koristim 4 procedure. Jedna je za brisanje sloga (DELETE), druga za provjeru da li postoji slog za traženi PK, treća za zapis i ispravak podataka (INSERT / UPDATE) i četvrta je za vraćanje cijelog slog (SELECT).

Kako se daju imena?

Vrlo jednostavno. Za tablicu MP_VALUTA procedure se zovu

Kao što se vidi, procedure se sastoje od naziva tablice nad kojom se odvija radnja i naziv radnje. Ovakva podjela je za veliku većinu procedura. Izvan toga pravila izlaze procedure koje rade složene poslove ali o njima nešto kasnije.

Naziv procedure je napisan malim slovima.

Store koristim za sve (dobro, skoro za sve) radnje nad podacima. Uz dobro odabrane nazive nije se teško snaći niti u tisućama procedura.