host-psm-plsql

host-psm-plsql - 1 Real SQL Programming Persistent Stored...

Info iconThis preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Real SQL Programming Persistent Stored Modules (PSM) PL/SQL Embedded SQL 2 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. 3 Options 1. Code in a specialized language is stored in the database itself (e.g., PSM, PL/SQL). 2. SQL statements are embedded in a host language (e.g., C). 3. Connection tools are used to allow a conventional language to access a database (e.g., CLI, JDBC, PHP/DB). 4 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. 5 Basic PSM Form CREATE PROCEDURE <name> ( <parameter list> ) <optional local declarations> <body>; Function alternative: CREATE FUNCTION <name> ( <parameter list> ) RETURNS <type> 6 Parameters in PSM Unlike the usual name-type pairs in languages like C, PSM uses mode-name-type triples, where the mode can be: IN = procedure uses value, does not change value. OUT = procedure changes, does not use. INOUT = both. 7 Example : Stored Procedure Lets write a procedure that takes two arguments b and p , and adds a tuple to Sells(bar, beer, price) that has bar = Joes Bar, beer = b , and price = p . Used by Joe to add to his menu more easily. 8 The Procedure CREATE PROCEDURE JoeMenu ( IN b CHAR(20), IN p REAL ) INSERT INTO Sells VALUES(Joes Bar, b, p); Parameters are both read-only, not changed The body ---a single insertion 9 Invoking Procedures Use SQL/PSM statement CALL, with the name of the desired procedure and arguments. Example : CALL JoeMenu(Moosedrool, 5.00); Functions used in SQL expressions wherever a value of their return type is appropriate. 10 Kinds of PSM statements (1) RETURN <expression> sets the return value of a function. Unlike C, etc., RETURN does not terminate function execution. DECLARE <name> <type> used to declare local variables. BEGIN . . . END for groups of statements. Separate statements by semicolons. 11 Kinds of PSM Statements (2) Assignment statements : SET <variable> = <expression>; Example: SET b = Bud; Statement labels : give a statement a label by prefixing a name and a colon. 12 IF Statements Simplest form:...
View Full Document

Page1 / 64

host-psm-plsql - 1 Real SQL Programming Persistent Stored...

This preview shows document pages 1 - 13. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online