Chp 9
SQL in a Server Environment
Contents：
The Three-Tier Architecture The SQL Environment The SQL/Host-Language Interface Stored Procedures Using a Call-Level Interface
EXECUTE:
EXECUTE dbo.movieBrief SELECT * FROM movies_temp
9.4 Stored Procedures
You can create stored procedure has nothing to do with tables.
CREATE PROCEDURE PRO_Sum @a int , @b int , @sum int OUTPUT AS BEGIN SET @sum =@a+@b END
• Solution 2
EXEC dbo.new_movie 'Doraemon 2018', 2018, 120, 'cartoon', 'ABC Film', NULL
13
9.4 Stored Procedures
Example：
Give an executor’s name, and find the longest length of movie he/she has ever made.
END
9.4 Stored Procedures
Call stored procedure maxLength DECLARE @maxLength int
EXEC dbo.maxLength @execName = 'Jackson', @maxLength = @maxLength OUTPUT
Connection
Statement
9.3 The SQL/Host-Language Interface
包含 SQL 语句的典型编程系统的框架如下：
宿主语言 ＋ 嵌套 库
目标代码
9.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.
Run stored procedure and get the result.
DECLARE @mysum int EXECUTE PRO_Sum 1,2,@mysum OUTPUT print @mysum
18
9.4 Stored Procedures
创建Return返回值存储过程 CREATE PROCEDURE PR_Sum2 @a int , @b int AS BEGIN 执行存储过程获取Return型返回值： Return @a+@b declare @mysum2 int END execute @mysum2= PR_Sum2 1,2
CREATE PROCEDURE maxLength @execName varchar(20), @maxLength int OUTPUT AS BEGIN SELECT @maxLength=MAX(length) FROM Movies m, MovieExec me WHERE m.producerC# = me.cert# AND LIKE '%'+@execName+'%’
9
SQL in a Server Environment
Code in a specialized language is stored in the database itself (e.g., PSM: persistent stored modules, PL/SQL: procedural language/SQL ). SQL statements are embedded in a host language (e.g., C，Java). Connection tools are used to allow a conventional language to access a database (e.g., CLI, JDBC, PHP/DB).
The application server issues statements : queries and modifications, usually.
User
Web Server
Application Server
SQL Server
9.2
The SQL Environment
Environment
SELECT @maxLength AS '@maxLength'
15
9.4 Stored Procedures
Example：Create a function.
CREATE FUNCTION starInList (@starName varchar(20)) RETURNS TABLE AS RETURN ( SELECT movieTitle, movieYear FROM StarsIn WHERE starName LIKE '%'+@starName+'%' ) Call above function: SELECT * FROM dbo.starInList('Carrie')
user.
Application servers --- execute the
business logic.
Database servers --- get what the app
servers need from the database.
User
Web Server
Application Server
9.4 Stored Procedures
Basic PSM Form CREATE PROCEDURE <name> <parameter list> <optional local declarations> <body>; PSM uses three type , where the mode can be:
SQL Server
9.2
The SQL Environment
The database is, in many DB-access languages, an environment=installation of DBMS. Database servers maintain some number of connections, so app servers can ask queries or perform modifications.
9
SQL in a Server Environment
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.
9.4 Stored Procedures
You can crate temporary table in stored procedure. Example：Get title and networth, put the result into a temporary table. CREATE PROCEDURE movieBrief AS SELECT title, netWorth INTO movies_temp FROM movies,movieExec WHERE movies.producerC#=MovieExec.cert#
Java
Host language
JDBC
SQL
Database
Connection
9.1
The Three-Tier Architecture
A common environment for using a database has three tiers of processors:
Web（ Represent ） servers --- talk to the
9.4 Stored Procedures
Example：Insert studio if it is not existed.
CREATE PROCEDURE new_movie
@title nchar(100), @year int, @length int,
@genre nchar(10), @studioName varchar(30), @producerC# int AS BEGIN DECLARE @studioCount int SET @studioCount = ( SELECT COUNT(*) FROM Studio WHERE name LIKE '%'+@studioName+'%') IF @studioCount = 0 INSERT INTO Studio (name) VALUES (@studioName)
