h1

Modalitati de executie a statement-urilor SQL

February 27, 2010

a) Constructie dinamica a instructiunilor (statement-urilor) SQL

b)

a) Folosing BATCHES (CREATE PROCEDURE –creaza o procedura stocata, CREATE VIEW – creeaza o tabela virtuala, CREATE TRIGGER, CREATE RULE, CREATE DEFAULT)

Se foloseste cuvantul rezervat GO la sfarsit pt.a  semnala sfarsitul unui BATCH

Intr-un batch pot aparea mai multe instructiuni (statement-uri).

Exemple:

CREATE DATABASE

CREATE TABLE

GO

CREATE VIEW1

GO

CREATE VIEW2

Ambele sunt corecte!

GRESIT:

CREATE DATABASE

CREATE DATABASE

GO

c) Folosind Scripts

Pot fi scrise intr-un editor de text (sau query editor din SQL Server si sunt salvate cu extensia .sql)

Pot fi executate in SQL Query Analyzer sau Utilitarul osql.

d) Folosind Tranzactii

Tranzactiile ca si batch-urile sunt grupe de statement-uri care sunt submitted as a set.

Prefatam o tranzatie cu BEGIN TRANSACTION si o terminam cu COMMIT TRANSACTION sau ROLLBACK TRANSACTION.

La COMMIT TRANSACTION, SQL SERVER face schimbarile pt.tranzactia respective permanente.

La ROLLBACK TRANSACTION , SQL SERVER intoarce orice rand afectat de tranzactie la stari de pretranzactie.

Exemplu de tranzactie:

100$ sunt debitati din contul savings al clientului cu id-ul 78910 si 100$ sunt creditati contului checking. Cumparatorul transfera 100$ din savings in checking.

BEGIN TRANSACTION

UPDATE savings

SET balance = (amount-100)

WHERE custid = 78910

BEGIN

RAISERROR(‘Transaction not completed  due to savings account problem.’,16,-1)

ROLLBACK TRANSACTION

END

UPDATE checking

SET balance = (amount+100)

WHERE custid = 78910

IF @@ERROR <> 0

BEGIN

RAISERROR(‘Transaction not completed due to checking amount problem.’,16,-1)

ROLLBACK TRANSACTION

END

COMMIT TRANSACTION

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: