SQL Programming: Creating Procedures, IF Statements, and Cursors, Slides of Database Management Systems (DBMS)

Download SQL Programming: Creating Procedures, IF Statements, and Cursors and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Real SQL Programming

Persistent Stored Modules (PSM)

PL/SQL Embedded SQL

SQL in Real Programs

• We have seen only how SQL is used at the generic query interface --- an environment where we sit at a terminal and ask queries of a database.
• Reality is almost always different: conventional programs interacting with SQL.

Stored Procedures

• PSM, or “ persistent stored modules ,” allows us to store procedures as database schema elements.
• PSM = a mixture of conventional statements (if, while, etc.) and SQL.
• Lets us do things we cannot do in SQL alone.

Basic PSM Form

CREATE PROCEDURE (

)

;

• Function alternative:

CREATE FUNCTION (

) RETURNS

Example: Stored Procedure

• Let’s write a procedure that takes two arguments b and p , and adds a tuple to Sells(bar, beer, price) that has bar = ’Joe’’s Bar’, beer = b , and price = p. - Used by Joe to add to his menu more easily.

The Procedure

CREATE PROCEDURE JoeMenu (

IN b CHAR(20), IN p REAL

)

INSERT INTO Sells

VALUES(’Joe’’s Bar’, b, p);

8

Parameters are both read-only, not changed

The body --- a single insertion

Kinds of PSM statements – (1)

• RETURN sets the return value of a function. - Unlike C, etc., RETURN does not terminate function execution.
• DECLARE used to declare local variables.
• BEGIN... END for groups of statements.
  • Separate statements by semicolons.

Kinds of PSM Statements – (2)

• Assignment statements: SET = ; - Example: SET b = ’Bud’;
• Statement labels: give a statement a label by prefixing a name and a colon.

Example: IF

• Let’s rate bars by how many customers they have, based on Frequents(drinker,bar). - <100 customers: ‘unpopular’. - 100-199 customers: ‘average’. - >= 200 customers: ‘popular’.
• Function Rate(b) rates bar b.

Example: IF (continued)

CREATE FUNCTION Rate (IN b CHAR(20) ) RETURNS CHAR(10) DECLARE cust INTEGER; BEGIN SET cust = (SELECT COUNT(*) FROM Frequents WHERE bar = b); IF cust < 100 THEN RETURN ’unpopular’ ELSEIF cust < 200 THEN RETURN ’average’ ELSE RETURN ’popular’ END IF; END; (^14)

Number of customers of bar b

Return occurs here, not at one of the RETURN statements

Nested IF statement

Example: Exiting a Loop

loop1: LOOP

... LEAVE loop1; ...

END LOOP;

16

If this statement is executed...

Control winds up here

Other Loop Forms

• WHILE DO END WHILE;
• REPEAT UNTIL END REPEAT;

Example: Assignment/Query

• Using local variable p and Sells(bar, beer, price), we can get the price Joe charges for Bud by: SET p = (SELECT price FROM Sells WHERE bar = ’Joe’’s Bar’ AND beer = ’Bud’);

SELECT... INTO

• Another way to get the value of a query that returns one tuple is by placing INTO after the SELECT clause.
• Example:

SELECT price INTO p FROM Sells WHERE bar = ’Joe’’s Bar’ AND beer = ’Bud’;