{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

host-psm-plsql - RealSQLProgramming (PSM PL/SQL EmbeddedSQL...

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

View Full Document Right Arrow Icon
1 Real SQL Programming Persistent Stored Modules (PSM) PL/SQL Embedded SQL These slides are reused from Jeffrey Ullman’s class  with the author’s permission
Background image of page 1

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

View Full Document Right Arrow Icon
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.
Background image of page 2
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).
Background image of page 3

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

View Full Document Right Arrow Icon
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.
Background image of page 4
5 Basic PSM Form CREATE PROCEDURE <name> ( <parameter list> ) <optional local declarations> <body>; Function alternative: CREATE FUNCTION <name> ( <parameter list> ) RETURNS <type>
Background image of page 5

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

View Full Document Right Arrow Icon
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.
Background image of page 6
7 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.
Background image of page 7

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

View Full Document Right Arrow Icon
8 The Procedure CREATE PROCEDURE JoeMenu ( IN b CHAR(20), IN p REAL ) INSERT INTO Sells VALUES(’Joe’’s Bar’, b, p); Parameters are both read-only, not changed The body --- a single insertion
Background image of page 8
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.
Background image of page 9

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

View Full Document Right Arrow Icon
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.
Background image of page 10
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.
Background image of page 11

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

View Full Document Right Arrow Icon
12 IF Statements
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}