EPL342 Databases Lecture 19: Internal DB …dzeina/courses/epl342/lectures/19.pdf · •...

19-1EPL342: Databases - Demetris Zeinalipour (University of Cyprus)

EPL342 –Databases

Lecture 19: Internal DB

Programming II

Internal DB Programming,

Scripts/Batches in TSQL(Chapter 9.1, Elmasri-Navathe 5ED +

ΣransactSQL Reference Guide

Department of Computer Science

University of Cyprus

Demetris Zeinalipour

http://www.cs.ucy.ac.cy/courses/EPL342

http://images.google.com/imgres?imgurl=http://www.mas.ucy.ac.cy/NEWMAS/images/ucy_logo_clean.png&imgrefurl=http://www.mas.ucy.ac.cy/NEWMAS/images/&h=182&w=185&sz=41&tbnid=zwk0cIvSOnVJ3M:&tbnh=94&tbnw=96&hl=en&start=20&prev=/images%3Fq%3Ducy%2Blogo%26svnum%3D10%26hl%3Den%26lr%3D%26rls%3DGGLG,GGLG:2006-01,GGLG:en%26sa%3DN


http://www2.cs.ucy.ac.cy/~dzeina/


Πεξηερόκελν Γηάιεμεο

Ολοκλήπυζη Γιάλεξηρ 18.

Internal DB Programming II

• Δζσηεξηθόο Πξνγξακκαηηζκόο ΓΒΓ

• Scripts/Batches ζε TSQL

• Stored Procedures (Sprocs) ζε TSQL

• User Defined Functions (UDFs) ζε TSQL



Πξνγξακκαηηζκόο Λεηηνπξγηώλ

κηαο DBMS• Δλώ ε SQL μεθίλεζε σο δηλυηική γλώζζα δηαηύπσζεο

επεξσηήζεσλ ζηε ζπλέρεηα επεθηάζεθε κε ενηολέρ DDL

θαη νη ζύγρξνλεο ΒΓ παξέρνπλ ζήκεξα θαη δπλαηόηεηεο γηα

δηαδηθαζηηθό πξνγξακκαηηζκό ΜΔΑ ζηελ ίδηα ηελ βάζε.

• Γεληθά, ππάξρνπλ νη αθόινπζεο θαηεγνξίεο:

– Δζυηεπικόρ Ππογπαμμαηιζμόρ: Scripts/Batches,

Sprocs, UDFs, Views, Triggers, Assertions, θηι

– Δξυηεπικόρ Ππογπαμμαηιζμόρ: Μέζσ Γλώζζας

Προγραμμαηιζμού (Host Language): Embedded SQL ,

Dynamic SQL, APIs θαη Διεπαθών Βάζεων (ODBC,

JDBC, MS OLEDB, ADO.NET θηι) επόκελε δηάιεμε.

• ηελ εξρόκελε δηάιεμε ζα αζρνιεζνύκε κε ηνλ Δμσηεξηθό

Πξνγξακκαηηζκό (Φξνληηζηήξην: JDBC)



Πξνγξακκαηηζκόο Λεηηνπξγηώλ

κηαο DBMS

DBMSProgramming Language

(π.σ., JAVA, C, C#,…)

Catalog/ Indexes Tables

SProcs UDFs

Interfaces:

JDBC,

ODBC, κηλ.

Triggers

Views

Administration Consoles

Web Languages (PHP,

Python, ASP, Ruby, Perl)

# SQLCMD

1> SELECT *

FROM EMPLOYEE

2> goΔζυηεπικόρ

Ππογπαμμαηιζμόρ Δξυηεπικόρ Ππογπαμμαηιζμόρ

(Γιαδπαζηικόρ)

Δξυηεπικόρ Ππογπαμμαηιζμόρ

(Μέζυ Δθαπμογήρ)



Δζσηεξηθόο Πξνγξακκαηηζκόο

κηαο DBMS• ήκεξα ζα δνύκε πσο κπνξνύκε λα

πξνγξακκαηίζνπκε ηνλ SQL Server κε ηνπο αθόινπζνπο ηξόπνπο:A. Scripts / Batches: Απιή αθνινπζία SQL εληνιώλ κε

ηειεζηέο έιεγρνπ, επαλάιεςεο, κεηαβιεηέο, θηι. (αληίζηνηρε ινγηθή κε shell scripts ζην UNIX)

B. Stored Procedures (Sprocs): Αθνινπζίεο SQL εληνιώλ κε παξακέηξνπο απνζεθεπκέλεο ζηελ βάζε.

C. User Defined Functions (UDFs): Δπηηξέπνπλ ζηνλ ρξήζηε λα νξίδεη ζπλαξηήζεηο πνπ κπνξνύλ λα ρξεζηκνπνηεζνύλ ζηα πιαίζηα ηνπ SELECT ή WHERE (π.ρ., MetersToInches(decimal(10,3))



A) Scripts ζε TSQL

• Scripts: Ακολοςθία (Σ)SQL Δνηολών ε νπνία αποθηκεύεηαι ζε έλα απσείο γηα επανασπηζιμοποίηζη.

• Παξαδείγκαηα Υξήζεο:• Backup / Restore πηλάθσλ ή νιόθιεξεο ηεο DB.

• Θπκεζείηε ην Northwind.sql (Δξγαζηήξην 8) ην νπνίν ρξεζηκνπνηήζαηε γηα λα δεκηνπξγήζεηε απηόκαηα όιε ηελ βάζε.

• Δπανάλητη ςσνών

Λειηοςπγιών (πληήξεζε,

θηι)




(Μεηαβιεηέο)

USE epl342;

DECLARE @TEST int

SET @TEST = (SELECT MAX(salary) FROM Emp1)

SELECT @TEST AS Result -- Αληίζηνηρν ηνπ STDOUT

PRINT @TEST – Αληίζηνηρν ηνπ STDERR. To κήλπκα είλαη

κέρξη 8000 ραξαθηήξεο θαη επηζηξεθεηαη ζηνλ ρξήζηε.

Set current Database (otherwise script will be

executed on whatever database is currently open)

Declare Variable with Name TEST of TYPE int

(default = NULL)

Assign Value to Variable to TEST

Display the TEST Variable with Column Name

Result (

Παξάδεηγκα Script ζε TSQL




(Μεηαβιεηέο)

SELECTPRINT (δεο θαη RAISERROR

γηα try…catch)




• Υαξαθηεξηζηηθά ησλ Scripts1. Σα Scripts δημιοςπγούνηαι θαη εκηελούνηαι από

θάπνην σπήζηη ή σο κέξνο άιινπ script.

2. Σν Script εθηειείηαη γπαμμή-γπαμμή από πάνυ πξνο ηα κάηυ (ε ζύληαμε ειέγρεηε πξηλ ηελ εθηέιεζε) από ηνλ κεηαθξαζηή ηεο βάζεο.

3. Δάλ πποκύτει λάθορ (runtime λάθορ) ηόηε ΑΚΤΡΩΝΔΣΑΙ νιόθιεξν ην script.

• Έλα Script εθηειείηαη σο κηα δνζνιεςία (transaction). πλεπώο απνηειεί κηα αηομική ππάξη: “Δίηε εθηειείηαη νιόθιεξν ή θαζόινπ”!

• Μεηά από θάπνην ιάζνο επαναθέπεηαι(ROLLBACK) πίζσ ζηελ αξρηθή θαηάζηαζε ε βάζε δεδνκέλσλ.



A) Scripts ζε TSQL(Παξάδεηγκα κε ρξήζε @@IDENTITY)

USE Northwind

DECLARE @NewOrderID int

INSERT INTO Orders(CustomerID, OrderDate)

VALUES (15, DATEADD(day,-1,GETDATE()))

SET @NewOrderID = @@IDENTITY

-- ή SELECT @NewOrderID = @@IDENTITY

INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, Quantity)

VALUES (@NewOrderID, 1, 50, 25)

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(varchar(8),

@NewOrderID)

@@: System Function

(last recorded Identity)

Current Date Function

Casting integer to string

Assign value to variable

Παράδειγμα Ειζαγωγής

Σσζτεηιζόμενων Δεδομένων ζε Δσο

Πίνακες (Order και OrderDetails).



A) Scripts ζε TSQL(Υξήζε πλάξηεζεο πζηήκαηνο @@ ROWCOUNT)

• Η ζπλάξηεζε ζπζηήκαηνο @@ROWCOUNT ζαο

επηζηξέθεη ηνλ απιθμό ηυν πλειάδυν πνπ επηπεάζηηκαν

ή διαβάζηηκαν από ηελ ηειεπηαία SQL επεπώηηζη.

– Θπκεζείηε ην κήλπκα: “(Υ row(s) affected)” πνπ πξνθύπηεη κεηά από

αλάγλσζε/αιιαγή δεδνκέλσλ ελόο πίλαθα

USE epl342;

DECLARE @ROWCOUNT int

SELECT * FROM Emp1

SET @ROWCOUNT = @@ROWCOUNT

SELECT 'The Rowcount is' +

CAST(@ROWCOUNT as varchar);

Με ηελ εληνιή “SET NOCOUNT ON” δελ ηππώλεηαη ην “(Υ row(s) affected)”



A) Γέζκεο (Batches) ζε TSQL

• Σα Batches (Γέζμερ) είλαη Scripts εληνιώλ TSQL ηα νπνία δηαρσξίδνληαη κε ηελ εληνιή GO θαη ηα νπνία εθηεινύληαη αλεμάξηεηα κεηαμύ ηνπο (δειαδή όρη ζηα πιαίζηα ηνπ ίδηνπ transaction)

• Παπάδειγμα:USE epl342;

GO -- απνζηνιή δέζκεο εθθξάζεσλ TSQL ζηνλ SQL Server

INSERT INTO Emp1(SSN) VALUES („4411111993‟)

GO -- απνζηνιή δέζκεο εθθξάζεσλ TSQL ζηνλ SQL Server

INSERT INTO Emp1(SSN) VALUES („3311111993‟)

GO -- δελ πξέπεη λα ππάξρνπλ άιιεο εληνιέο ζηελ ίδηα γξακκή κε ην GO!

• H εληνιή GO ιεηηνπξγεί ΜΟΝΟ ζην πιαίζην ηνπ SQL Management Studio ή ηεο sqlcmd (ή osql).– ε πξνγξάκκαηα γίλεηαη θάηη αληίζηνηρν κέζσ εμεηδηθεπκέλσλ

εληνιώλ, π.ρ. ζηε JAVA: stmt.executeUpdate(query);



A) Γέζκεο (Batches) ζε TSQL

• Κάπνηεο ενηολέρ είλαη αναγκαζηικό λα είλαη κέξνο ηνπ δικού ηνπο Batch (δειαδή ππεπει λα ακολοςθούνηαι από GO).– Μεξηθέο από απηέο είλαη:

• CREATE TRIGGER

• CREATE VIEW

• CREATE PROCEDURE ζα ην δνύκε ζε ιίγν

• ςνηακηικά Λάθη ειέγρνληαη όπσο θαη ζηα Scripts πξηλ ηελ εθηέιεζε νιόθιεξνπ ηνπ Batch

• Δάλ πξνθύςνπλ Runtime λάθη ζε έλα batch ζην ζεκείν Υ ηόηε δελ εθηειείηαη θακία εληνιή κεηά ην X.– Δληνιέο πξηλ ην Υ δελ γίλνληαη ROLLBACK θαη αιιάδνπλ

κόληκα ηελ θαηάζηαζε ηεο βάζεο.




(Γπλακηθή SQL ζε TSQL)• Γςναμική SQL (Dynamic SQL): Δθθξάζεηο SQL

πνπ παξάγνληαη θαηά ηελ εθηέιεζε ελόο script ή πξνγξάκκαηνο κε ρξήζε ηεο εληνιήο ΔΥΔC– Υξήζηκεο εάλ δελ είλαη γλσζηή εθ ησλ πξνηέξσλ ε SQL έθθξαζε.

– ημείυζη: Όια ηα πξνεγνύκελα παξαδείγκαηα ήηαλ κε ζηαηηθέο εθθξάζεηο SQL (Static SQL)

• Παξάδεηγκα

USE epl342;

GO

DECLARE @Salary int;

SET @Salary = (SELECT MAX(salary) FROM EMP1);

EXEC ('SELECT * FROM Emp1 WHERE salary=' + @Salary);

GO

Έθθξαζε SQL πνπ

παξάγεηαη δπλακηθά

θαηά ηελ εθηέιεζε

concat




(Γπλακηθή SQL ζε TSQL)-- Γημιοςπγία Πίνακα Ωρ Υπήζηηρ DBO (Database Owner)

USE epl342;

GO

EXECUTE ('CREATE TABLE SalesTable (SalesID int, SalesName

varchar(10));')

AS USER = 'dbo';

GO

Δπιζημάνζειρ για ηο EXEC

• Δθηειείηαη θάησ από κε ηα ίδια δικαιώμαηα κε ηνλ Script πνπ ην θαιεί.

• Σν ΔΥΔC ηξέρεη κε ηα ίδιο connection κε ην πξόγξακκα πνπ ην θαιεί.

• Δάλ ζα γίλεη ζύκπηπμε κε ζπλάξηεζε, ηόηε απηή πξέπεη λα γίλεη πξηλ ηελ

θιήζε ηεο EXEC.

– ΛΑΘΟ: EXEC ('SELECT * FROM Emp1 WHERE date=' + GetDate());

Το EXEC είναι ζσνηομογραθία ηοσ

EXECUTE




(Έιεγρνο Ρνήο ζε TSQL)• Σελεζηήρ Έλεγσος

– IF <Βοοlean Expression>

<SQL statement> | BEGIN <code series> END

ELSE

<SQL statement> | BEGIN <code series> END

Δπιζημάνζειρ

• Πξνθαλώο επηηξέπεηαη θαη ε εμθώλεςζη απηνύ ηνπ ηειεζηή

ηόζν κε ηνλ εαπηό ηνπ όζν θαη κε άιινπο ηειεζηέο.

• Δάλ ην <Boolean Expression> είλαη NULL ηόηε είλαη FALSE ε

ινγηθή ζπλζήθε.

• ΛΑΘΟ: IF @myvar=NULL ΩΣΟ: IF @myvar IS NULL

• Τπάξρεη θαη ε CASE (δει., αληίζηνηρν ηεο SWITCH ζηε C)

http://msdn.microsoft.com/en-us/library/ms181765.aspx




(Έιεγρνο Ρνήο ζε TSQL)USE AdventureWorks;

GO

SELECT ProductNumber, Category =

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END, Name, SSN

FROM Production.Product

ORDER BY ProductNumber;

GO

H ζπλζήθε κπνξνύζε λα

είλαη θαη πην ζύλζεηε, π.ρ.,

(ProductNumber % 2) = 0




(Δπαλάιεςε ζε TSQL)

Αλακνλή 1 δεπηεξνιέπηνπ

κέρξη ηελ επόκελε θιεζε

• Σελεζηήρ Δπανάλητηρ ζε TSQL

USE epl342

GO

DECLARE @var INT

WHILE 1 = 1

BEGIN

WAITFOR DELAY '00:00:01'

SET @var = (SELECT MAX(salary) FROM EMP1)

END




(Δπαλάιεςε ζε TSQL)• Σελεζηήρ Δπανάλητηρ

– WHILE <Βοοlean Expression>

<SQL statement> |

[

BEGIN

<statement block>

[BREAK]

<sql statement> | <statement block>

[CONTINUE]

END

]


EPL342 Databases Lecture 19: Internal DB …dzeina/courses/epl342/lectures/19.pdf · •...

Documents

Transcript of EPL342 Databases Lecture 19: Internal DB …dzeina/courses/epl342/lectures/19.pdf · •...