SQL Programming

shrubberystatuesqueData Management

Dec 1, 2012 (4 years and 4 months ago)

238 views

N
OTES
:

SQL

P
ROGRAMMING
:

V
IEWS
,

S
TORED
P
ROCEDURES
,

AND
F
UNCTIONS


T
ABLE OF
C
ONTENTS

Notes: SQL Programming: Views, Stored Procedures, and Functions

................................
................................
...........

1

Learning Objectives

................................
................................
................................
................................
...................

1

SQL Goals

................................
................................
................................
................................
...............................

2

Part 1: External Model 101
-

SQL Views

................................
................................
................................
....................

2

Part 2: External Model 201: Program Units

................................
................................
................................
...............

3

More Database Objects

................................
................................
................................
................................
.........

3

Procedures

................................
................................
................................
................................
.............................

4

Functions

................................
................................
................................
................................
...............................

4

Advantages of Progra
m Units

................................
................................
................................
................................

5

SQL Programming Fundamentals

................................
................................
................................
..............................

6

SQL Variables

................................
................................
................................
................................
.........................

6

The Conditional Execution of SQL statements
................................
................................
................................
.......

6

Other Helpful Commands, Expressions, and Special Variables

................................
................................
.................

8

Stored Procedures

................................
................................
................................
................................
.................

8

User
-
Defined Functions

................................
................................
................................
................................
.........

9

Triggers

................................
................................
................................
................................
................................

10



L
EARNING
O
BJECTI VES

We’r
e now in our 3rd week of SQL, and getting ready to jettison ourselves from the internal
data model, to the
external data model. This week
we will

extend o
u
r SQL knowledge as we explore some of the additional
programming capabilities of SQL: language constructs, views, stored procedures, functions, and triggers.



Describe views, functions, sto
red procedures and triggers.



Explain the importance of procedural language constructs in the SQL environment.



Demonstrate how to solve business problems with SQL programming.



Describe the many advantages of the external data model.



SQL

G
OALS

Our SQL goals for this learning unit will be to learn about the following language constructs:



DECLARE and SET statements



Program Flow control with I
F


ELSE

statement and
BEGIN

END

blocks



Using special
built
-
in functions such as

exists
,
@@idenity

and
@@rowcount


And to understand use
-
cases for the following SQL objects:



Views



Stored Procedures / the EXECUTE command



Functions

P
ART
1:

E
XTERNAL
M
ODEL
101

-

SQL

V
IEWS

Part of building a database involves creating SQL Select statements to display output matching the end
-
user’s

requirements. Queries such as

Total quantity on hand by region


and

“E
mployees, departments,
and

managers

are easy to understand in concept, but can be quite difficult for the end
-
user to implement in SQL because of the
inherent complexity of the SELECT statement. If we could abstract the complexity of these SQL statements into
their own virtua
l tab
les it would be easier for the end user to digest that complexity. This is the fundamental
purpose of the SQL View.


An SQL View is a type of meta
-
data. It allows you to store an SQL SELECT statement under an object name, which
in turn can be queried as a
table. Since views are meta
-
data, they reside in the DML family of SQL statements, and
have corresponding
CREATE

VIEW
,
ALTER

VIEW

and
DROP

VIEW

statements. Here
’s

the syntax of the
CREATE
VIEW

statement:



CREATE

VIEW

name
-
of
-
view

AS


select statement


Once you’
ve created your view you can access it just like it was a normal table. In addition you can update, delete,
and insert data into a view as long the SELECT statement defined in the view
output the primary key of the
underlying table,
does not conta
in aggregates,
or joins and does not
violate any table constraints. The benefit of
the View to the external model cannot be overlooked; views are the vehicle a database designer uses to abstract
the complexities of the internal model from the users of the
database.


Example:

Creating a view

and then executing a SELECT statement using the view


P
ART
2:

E
XTERNAL
M
ODEL
201:

P
ROGRAM
U
NITS

In the last section, we learned ab
out SQL Views. Views allow the database designer to abstract the internal model
(how the actual tables are built in the database) into an external model (a representation which makes sense for
the average user). Using views, you can make your database desi
gn less complicated to the end user by joining
tables and performing aggregate operations in the view definition.

You might think of views as the greatest thing since sliced bread, but in reality, they only abstract the
“read”

operation for the end
-
user. I
f you want to do the same thing for create, update, or delete operations, then you

ll
need to dive into some of the other database objects used to implement the External model.

M
ORE
D
ATABASE
O
BJECTS

SQL is a declarative language. As a declarative language most SQL implementations do not support flow control; or
what is referred to as procedural language constructs like decision control (IF, THEN, CASE, GOTO) or iterative
control (FOR,

LOOP, WHILE.) Decision and iterative control extensions make SQL computationally complete,
meaning you can build application systems that can do everything that procedural
-
level languages, like C, C++,
COBOL, Java, and Visual Basic, can do.


Most DBMS ve
ndors have recognized this shortcoming and have added their proprietary versions of procedural
language capabilities. SQL Server and Sybase call their procedural language extensions Transact
-
SQL, DB/2 calls
theirs SQL PL and Oracle calls theirs PL/SQL. Pos
tgreSQL uses PGSQL, which has variants in Perl, Python, and PL
syntax. MySQL also has many of the same procedural language capabilities as the other DBMSs. Bottom line here is
each vendor has their own implementation. So what can you do with procedural lan
guage constructs?


You can build enterprise
-
wide applications just like you can with other procedural
-
level languages. These
applications run on the server and can be called to perform pre
-
processing i.e. you can process your data before
storing in the dat
abase or post
-
processing i.e. processing the data after retrieving it from the database. These
applications are built using a variety of database objects generically known as

program units



These
program
units can be built in variety of configurations called program unit types with the three most popular being
Procedures, Functions and Triggers.

In the table below you can see that there are a variety of program unit types. In this course we

ll dis
cuss just three
of them.


Figure:

Program Units of Oracle DBMS



P
ROCEDURES

A procedure, sometimes called a stored procedure, is one type of reusable program unit that uses input and
output pa
rameters. A stored procedure can have none or any number of input and output parameters. Think of a
stored procedure as a block of instructions that performs work via executable logic that can use input parameters
to customize the logic flow and output par
ameters to return the result back to a calling procedure block. The term
block

is an apt description since these blocks of instructions create the
building blocks used for building an
application. Since procedures are server
-
side executables they can be u
sed (i.e. called by other program units) by
any application that needs the functionality they provide.

F
UNCTI ONS

Similar to a procedure is another stored program unit called a function. A functi
on is also a reusable program unit
that performs work via executable logic. A function also accepts user input and can have many input parameters;
however, a function does not have output parameters. It can return only a single value. You are already famil
iar
with Microsoft supplied functions via Lab 6.


Another major difference between a function and a stored procedure is how they are used. A function cannot be
run as a stand
-
alone statement. A function can be used anywhere a column or an expression is us
ed. You use a
user
-
defined function just like you would use SQL Server or Oracle
-
supplied built
-
in functions.

A
DVANTAGES OF
P
ROGRAM
U
NITS

If you’
re having trouble understanding

the rationale behind the correlation between program units and the
external model let

s explore this example. Imagine a form on a website where a new customer can add
information before signing up for an account. Some of the information is personal (name,

address, phone), some is
demographic (age, gender, race) and some is based on personal preferences (favorite music, how many purchases
are made each month, etc.) While it makes sense to collect this all on one form, it might not make sense to store it
all

in one table. As a matter of fact, it is quite common for the internal model of the database tables to not
correspond to the layout of the user
-
interface. After all database design and user
-
interface design
'are
' two
separate disciplines!


Whenever there

is a disconnect between the UI and the underlying tables which support the data from that UI,
there needs to be some computer program code to
translate

the data from that web form to the SQL insert
statements in the tables. A decision needs to be made on
where to place
that

program c
ode,

and your options are:



In the web application using program code such as Java, ASP.NET or PHP



In the DBMS as stored procedures and/or functions


Using stored procedures and functions offer the following additional benefits
over the execution of the equivalent
SQL statements:

1.

Abstraction
. Java programmers
won’t

need to know complex SQL to insert data into the proper tables in
the proper order. Since all the SQL inserts are encapsulated into on new, logical command it is also easier
for the programmer to interact with the database as the database designer intende
d.

2.

Improved performance
. The execution plan is compiled with the stored procedure definition. This means
the SQL server does not have to parse and interpret the SQL statements inside the stored procedure each
time it executes the procedure, resulting in a
performance improvement over executing the equivalent
SQL from the web application programming language.

3.

Encryption
. Stored procedures can be encrypted so the programming of the underlying procedure
definition can be hidden from prying eyes.

4.

Security

isola
ted from the table level. A user can be denied permissions to modify the data in a table, yet
granted permissions to execute a stored procedure or function making the same modifications. This
forces the end
-
users to execute the stored procedures to perform

the underlying CRUD operations on the
table. Neato!

SQL

P
ROGRAMMING
F
UNDAMENTALS

Before you can start whipping off fancy stored procedures, fu
nctions, triggers and such, you’
ll need to understand
some basics in SQL procedural language programming.

NOTE: IN THIS CLASS YOU WILL LE
ARN TO PROGRAM WITH MICROSOFT’
S VARIANT, T
-
SQL. WHILE T
-
SQL IS
SIMILAR TO OTHER DBMS VENDORS SQL PROGRAMMING IMPLEMEN
TATIONS, YOUR MILEAGE MAY VARY…

SQ
L

V
ARIABLES

Variables behave similar to variables in other programming languages, allowing the SQL programmer to store any
scalar value

(a.k.a. a single value). You must specify a data type w
hen creating a variable, and the name of the
variable must begin with an @ symbol so that the SQL interpreter does not attempt to treat the variables as a
database object or column.


Variables are defined with the DECLARE statement, and assigned values wit
h the SET statement, the value you
assign to the variable can be a constant, the output of a function, or the result of an SQL statement, as long as it
returns a scalar (one row and one column) of the same data type.


Syntax diagram for
the DECLARE and SET

statements:

DECLARE @variablename AS datatype

SET @variablename = expression


Example:

3 variables are set using a constant, a function, and an SQL SELECT statement


T
HE
C
ONDITI ONAL
E
XECUTION OF
SQL

STATEMENTS

Like all programming languages, SQL has program
-
flow
-
control statements for the conditional execution of
statements. Her
e is the SQL version of the IF
¦ELSE statement:

IF boolean_expression

BEGIN


sql_statement_when_true;


next sql statement
...n

END

ELSE

BEGIN


sql_statement_when_false;


next sql statement
...n

END


Example:

This allows the programmer to execute SQL statements based on a Boolean (true/false) expression.



O
THER
H
ELPFUL
C
OMMANDS
,

E
XPRESSI ONS
,

AND
S
PECIAL
V
ARIABLES

The following list of special commands, expressions and variables help with your SQL programming:


S
TORED
P
ROCEDURES

A
stored procedure

is a saved collection of T
-
SQL statements. A stored procedure can accept and return a set of
user
-
supplied parameters which are defined when the procedure is created. Procedures are defined with the
CREATE PROCEDURE
,
ALTER PROCEDURE
,
D
ROP PROCEDURE

statement, and subsequently run with the
EXECUTE statement. The syntax diagrams appear below:


/* this defines the procedure */

CREATE PROC
EDURE

owner.procedure_name (


@parameter_name1 AS datatype,


@parameter_name2 AS datatype,


...n

)

AS

BEGIN


sql_statement;


next sql statement


...n


RETURN expression

END


/* run the procedure */

EXECUTE
@returnvar =

owner.procedurename
@param1
data


And, of course, here’s an example. This procedure adds a zip code to the table
zipcodetable
.


/* defined procedure */

CREATE PROCEDURE dbo.p_add_zipcode (


@zip as char(5),


@city as varchar(50),


@state as char(2)

)

AS

BEGIN


INSERT INTO zipcodetable ( ZIPCD, CITY, ST)


VALUES ( @zip, @city, @state);


RETURN @@ROWCOUNT;

END


/* execute the proced
ure */

EXECUTE p_add_zipcode ‘13039’, ‘Cicero’, ‘NY’

EXECUTE @success
= p_add_zipcode ‘90210’, ‘Bev Hills’, ‘CA’

IF @
succe
ss=1 PRINT ‘Zip code Added!’


U
SER
-
D
EFINED
F
UNCTI ONS

A cousin of the stored procedure, the
CREATE

|
ALTER

|
DROP FUNCTION

statement allows the SQL
programmer to create user
-
defined function. The key advantage a function has over a stored procedure is it can be
used inside check constraints, default values, SQL View definitions and any other place you can normally stick an
ex
pression. A function must always return a value. In many SQL implementations, there are table functions (which
return table output) and the more common scalar
-
valued functions which return a single value. We will focus on
the traditional scalar
-
valued func
tions.



Example:

This function will return the zipcode of the given city and state.

/* function definition */

CREATE FUNCTION dbo.lookup_zip(

@city as varchar(50),

@state as char(2)

) RETURNS char(5)

AS

BEGIN

RETURN (SELECT ZIPCD FROM zipcodetable

WHERE
CITY=@city AND ST=@state)

END


/* calling the f
unction */

SELECT lookup_zip(‘Clay’,’NY’) as ‘zip code’


output:

zip code

13041

T
RIGGERS

At this

point you might be thinking
stored procedures and f
unctions are grea
test thing since sliced bread.

Yes, each
has their uses, but neither of them allows the automatic execution of SQL code based on the insertion, update or
removal of data from the table. To perform this type of feat, you will need to use tr
iggers. A trigger is SQL code
which executes automatically whenever a specific update action takes place on a table. Triggers can be invoked on
INSERT, UPDATE or DELETE either AFTER the data change takes place or INSTEAD OF it.


Trigger definitions use two

virtual tables, named deleted and inserted which store the specific data affected by the
insert, update, or delete which caused the trigger. The deleted table refers t
o
the original values

before the action
that fires the trigger takes place while the ins
erted table refers to the values after that action.


Triggers let the programmer
do the seemingly impossible
-

create check constraints based on data in other tables,
keep summary tables automatically up
-
to
-
date, store the same data in two or more tables,
yet keep everything up
-
to
-
date, and perhaps most importantly, enforce business rules. Triggers are complicated, hairy beasts and not for
the faint at heart. Exercise extreme care and feeding with use.