数据库系统-server-environment.pptVIP

Chapter 9 SQL in a Server Environment 第9章 服务器环境中的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. Options Code in a specialized language is stored in the database itself (e.g., PSM, PL/SQL). SQL statements are embedded in a host language (e.g., C). Connection tools are used to allow a conventional language to access a database (e.g., CLI, JDBC, PHP/DB). 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 name ( parameter list ) optional local declarations body; Function alternative: CREATE FUNCTION name ( parameter list ) RETURNS type 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. Example: Stored Procedure Let’s write a procedure that takes two arguments e and n, and adds a tuple to Salesman (empid, name, deptid) that has empid=e, name=n, and deptid=1. The Procedure CREATE PROCEDURE InsertToD ( IN e CHAR(10), IN n CHAR(20) ) INSERT INTO Salesman VALUES(e,n,1); Invoking Procedures Use SQL/PSM statement CALL, with the name of the desired procedure and arguments. Example: CALL InsertToD(’A0043’,’Zhangsan’); Functions used in SQL expressions wherever a value of their return type is appropriate. 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. Kinds of PS