Lab #11 Worksheet – Views, Procedural SQL

Views

Views are virtual tables. Users have access to specified portions of data: increased privacy and data security. Views are dynamically updated when updates/inserts/deletes are made to the base tables.

Syntax:

CREATE VIEW ViewName AS SelectStatement;

• To create a view of expensive products:

CREATE VIEW vwProduct AS
SELECT productCode, description, price
FROM Product
WHERE price >= 100.00;

• The above view can be used to retrieve products just like working with tables. Try the following query:

SELECT * FROM vwProduct;

• Insert some new products in the Product table; write the INSERT statement.
• Retrieve rows from the view vwProduct again. Those products should appear in the view if their price is $100.00 or more; do they?
• Create a view vwProduct2 of all products that need to be reordered (their quantity on hand is less than the minimum quantity).
• Create a view vwProduct3 of all products combined with their vendor name.

Procedural SQL

SQL has some shortcomings; it doesn’t support some important programming constructs such as functions, conditional execution (if ... then), loops, variables...

Procedural SQL (PL/PGSQL) is an extension of SQL that provides procedural constructs that can be used to implement logic around standard SQL statements. The procedural code is executed by the DBMS when it is invoked (directly or indirectly) by the end user. Procedural SQL can be used to create stored procedures, functions, and triggers, that can use variables, loops, if statements, cursors...

1) Stored Procedures

A stored procedure is a named collection (block) of procedural and SQL statements held by the DBMS. A stored procedure is invoked by name and executed as a unit.

Syntax