SQL Views & Procedures

CMSC325
SQL Views & Procedures

DR. RICHA SHARMA
L O C K H AV E N U N I V E R S I T Y
1
Views
 Output of a relational query such as SELECT is another
relation (or table).
 If we have some query that needs to be executed
frequently, then it’s a good idea to have that query stored
as a view.
 A view is a virtual table based on a SELECT query. The
query can contain columns, computed columns, aliases,
and aggregate functions from one or more tables.
 The tables on which the view is based are called base
tables.
2
Views
 A view can be created by using CREATE VIEW command:
CREATE VIEW viewname AS SELECT query
 CREATE VIEW statement is a data definition command

that stores the subquery specification – the SELECT
statement used to generate the virtual table!
 Views are dynamically updated, i.e. the view is re-created

on demand each time it is invoked. For example: say we
have a view based on product table and we have more
products added or deleted after creating the view, then
these products will be reflected in the select query on the
view!
3
Views
 Views provide a level of security in the database because
they can restrict users to seeing only specified columns
and rows in a table.
 For example, if there is a company with hundreds of

employees in several departments, each department
administrative assistant can be given a view of certain
attributes only for the employees who belong to that
assistant’s department.
 Views may also be used as the basis for reports. For
example, we can use product view to generate a report
that product summary.
4
Procedural Extensions
 If we want to perform a conditional or looping type of
operation (i.e., a procedural type of programming using an IF-
THEN-ELSE or DO-WHILE statement) is required!
 To remedy the lack of procedural functionality in SQL, the
SQL-99 standard defined the use of persistent stored
modules (PSM) in the database software!
 A PSM is a block of code containing standard SQL statements
and procedural extensions that is stored and executed at the
DBMS server.
 PSM represents business logic that can be encapsulated,
stored, and shared among multiple database users.
5
Procedural Extensions
 Oracle implements PSMs through its procedural SQL
language (PL/SQL).
 PL/SQL makes it possible to use and store procedural code and

SQL statements within the database.
 It makes it possible to merge SQL and traditional programming

constructs, such as variables, conditional processing (IF-THEN-
ELSE), basic loops (FOR and WHILE loops), and error trapping.
 MySQL uses a procedural version of SQL that is similar in

many respects to the Oracle’s PL/SQL.
 MS SQL Server implements PSMs via Transact-SQL and other

language extensions.
6
Stored Procedures
 A stored procedure is a named collection of procedural and
SQL statements.
 Stored procedures encapsulate and represent business
transactions.
 Syntax:
CREATE OR REPLACE PROCEDURE procedure_name
[(argument
[IN/OUT] data-type, … )]
[IS/AS]
BEGIN
[ variable_namedata type[:=initial_value] ]
SQL statements;
…
END
GO 7
Example: Stored Procedures
 An example of creating a stored procedure to get details of
vendors:
CREATE PROCEDURE getAllVendors

AS
SELECT * FROM Vendor
GO
 Stored procedure can be executed as:

EXEC getAllVendors
 The created stored procedures can be deleted as:

DROP procedure getAllVendors
https://www.w3schools.com/sql/sql_stored_procedures.asp 8
Stored Procedures (ctd.)
 Stored procedures can be parametrized as well, i.e. we can
pass parameters to the stored procedures, an example:
CREATE PROCEDURE getVendor @State char(2)

AS
BEGIN
SELECT * FROM Vendor
WHERE v_state = @State;
END
GO
 We can execute this stored procedure as:

EXEC getVendor @State = 'FL'
https://learn.microsoft.com/en-us/sql/t-sql/lesson-1-creating-database-objects? 9
Exercises
 Create these stored procedures:
 By the name customerDetails that accepts customer

first name as parameter and retrieves that customer’s
details from the customer table.
 By the name printProductDetails that accepts price as

parameter and retrieves the product code, product
description, product price, vendor code from the product
table where product price is greater than the input price.
References for MySQL Workbench:

https://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
https://www.tutorialspoint.com/sql/sql-stored-procedures.htm
10
https://www.w3resource.com/mysql/mysql-procedure.php
Stored Procedures with IN/OUT
parameters
 Let’s consider an example of a stored procedure with IN as well
as OUT parameter, this procedure will fetch count of orders
placed by a customer:
CREATE PROCEDURE getOrderCount (

@c_code INT,
@order_count INT OUTPUT
) AS
BEGIN
select @order_count = count(*) from invoice where
cus_code =
@c_code;
END;
 To execute this stored procedure, we will need to declare a

variable that can capture the returned value at the time of
execution!
11
parameters
DECLARE @count INT;
EXEC getOrderCount 10011, @order_count = @count

OUTPUT;
SELECT @count AS 'Number of orders found’;
or,
PRINT 'Number of order for customer with code 10011 is

' + CONVERT(varchar(10),@count);
12
parameters (ctd.)
CREATE PROCEDURE getProductCount (
@product_count INT OUTPUT
) AS
BEGIN
SELECT * from product;
SELECT @product_count = @@ROWCOUNT;

END;
Here, @@ROWCOUNT is a system variable that returns the number of

rows returned by the previous statement. For executing this procedure:
DECLARE @count INT;
Exec getProductCount @product_count = @count OUTPUT;
SELECT @count AS 'Number of products found';
13
Procedural Code Example
 The following procedural code computes area and perimeter of a
square as:
DECLARE @v_side INT, @v_perimeter INT, @v_area INT;
BEGIN
SET @v_side = 5;
SET @v_perimeter = @v_side * 4;

SET @v_area = @v_side * @v_side;
PRINT('Perimeter is ' + CONVERT(varchar(10),

@v_perimeter));
PRINT('Area is ' + CONVERT(varchar(10), @v_area));
END;
14
Procedural Code Example (ctd.)
 The following procedural code demonstrated the use of IF-ELSE:
DECLARE @v_number INT;
BEGIN
SET @v_number = -10;
IF @v_number = 0
PRINT('Zero entered');
ELSE
BEGIN
IF @v_number > 0
PRINT('Positive’);
ELSE
PRINT('Negative’);
END;
END;
15
IF/ELSE will only count the line right below.
EX
IF a > 200
Print(‘a is good’)
Print(‘Goodbye’)
The Goodbye statement is outside of the If statement and will execute
regardless.
TO add multiple lines in an if statement, preface them with begin and
finish them with end.
16
Procedural Code Example (ctd.)
 The following procedural code demonstrated the use of WHILE:
DECLARE @v_number INT;
BEGIN
SET @v_number = 5;
WHILE @v_number > 0

BEGIN
PRINT(CONVERT(varchar(10), @v_number));
SET @v_number = @v_number - 1;
--IF @v_number = 2
--BREAK
END;
END;
17
Exercises
 Create the stored procedures:
 That accepts customer code and finds the count of orders placed
by that customer. If the count of orders placed and display the
message: Custmer <code> ‘placed one order!’ if the count is 1
else display: Custmer <code> ‘placed more than one order!’.
Create or alter procedure countOrders @c_code int

as begin
declare @num int;
select * from invoice where Cus_Code = @c_code;
set @num = @@ROWCOUNT;
if @num = 1
print('Customer ' + convert(varchar,@c_code) + ' placed one order!');
else
print('Customer ' + convert(varchar,@c_code) + ' placed more than one
order!');
end;
18
Error Handling
 An example of error handling:
BEGIN TRY
select 1/0
--delete from vendor where v_code = 21231
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
MySQL Reference:
https://dev.mysql.com/doc/refman/8.0/en/condition-handling.html
https://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/
19
Cursor
 A cursor is a special construct used in procedural SQL to hold
the data rows returned by a SQL query.
 Cursor can be considered as a reserved area of memory in
which the output of the query is stored, like an array holding
columns and rows.
 Cursors are held in a reserved memory area in the DBMS
server, not in the client computer!
 An implicit cursor is automatically created in procedural SQL

when the SQL statement returns only one value.
 An explicit cursor is created to hold the output of a SQL
statement that may return two or more rows.
MySQL Reference: https://www.mysqltutorial.org/mysql-cursor/
SQL Server Ref:
20
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-
Cursor – an example
DECLARE @code INT, @name varchar(50), @state varchar(2);
DECLARE vendor_cursor CURSOR FOR

SELECT v_code, v_name, v_state FROM Vendor;
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @code, @name, @state
while @@FETCH_STATUS = 0 (Fetches all rows)

BEGIN
PRINT(CONVERT(varchar(10), @code) + ' ' + @state + ' ' +
@name);
FETCH NEXT FROM vendor_cursor INTO @code, @name, @state

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor; 21
Benefits of Stored Procedures
 Improved Performance: Stored procedures are pre-compiled and
stored on the server, so they can be executed more quickly than
SQL statements that are sent from client applications.
 Code Reuse: Stored procedures can be called from different
client applications, i.e. the same code can be reused across
different applications. This reduces development time and
maintenance costs.
 Reduced Network Traffic: Since stored procedures are executed
on the server, only the results are returned to the client, therefore
network traffic gets reduced improving application performance.
 Better Security: Stored procedures can be used to enforce
security rules and prevent unauthorized access to sensitive data.
22
Drawbacks of Stored Procedures
 Increased Overhead: Stored procedures can consume more
server resources than simple SQL statements, particularly when
they are used frequently.
 Limited Portability: Stored procedures are often specific to a
particular DBMS, therefore may not be easily portable to other
databases.
 Debugging Challenges: Debugging stored procedures can be
more challenging than debugging simple SQL statements.
 Security Risks: If not written correctly, stored procedures can
pose a security risk, if they are used to access sensitive data or
to perform actions that could compromise the integrity of the
database.
23

SQL Views & Procedures

Uploaded by

Copyright:

Available Formats

SQL Views & Procedures

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Views & Procedures

Uploaded by

Copyright:

Available Formats

CMSC325

SQL Views & Procedures

 CREATE VIEW statement is a data definition command

 Views are dynamically updated, i.e. the view is re-created

 For example, if there is a company with hundreds of

 PL/SQL makes it possible to use and store procedural code and

 It makes it possible to merge SQL and traditional programming

 MySQL uses a procedural version of SQL that is similar in

 MS SQL Server implements PSMs via Transact-SQL and other

CREATE PROCEDURE getAllVendors

 Stored procedure can be executed as:

 The created stored procedures can be deleted as:

CREATE PROCEDURE getVendor @State char(2)

 We can execute this stored procedure as:

 By the name customerDetails that accepts customer

 By the name printProductDetails that accepts price as

References for MySQL Workbench:

CREATE PROCEDURE getOrderCount (

 To execute this stored procedure, we will need to declare a

EXEC getOrderCount 10011, @order_count = @count

SELECT @count AS 'Number of orders found’;

PRINT 'Number of order for customer with code 10011 is

SELECT @product_count = @@ROWCOUNT;

Here, @@ROWCOUNT is a system variable that returns the number of

SET @v_perimeter = @v_side * 4;

PRINT('Perimeter is ' + CONVERT(varchar(10),

WHILE @v_number > 0

Create or alter procedure countOrders @c_code int

 An implicit cursor is automatically created in procedural SQL

DECLARE vendor_cursor CURSOR FOR

while @@FETCH_STATUS = 0 (Fetches all rows)

FETCH NEXT FROM vendor_cursor INTO @code, @name, @state

You might also like