首页

实际 SQL 编程

Persistent Stored Modules (PSM)
PL/SQL
Embedded SQL

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

实际程序中的 SQL

我们之前所看到的仅是 SQL 在普通查询界面(generic query interface) --- 终端 --- 下对于数据库的查询.

现实情况几乎总是不同的: 常规程序与 SQL 进行交互.

Options

使用特定语言编写的代码存储在数据库中(如: PSM, PL/SQL)
SQL 语句嵌入到宿主语言(host language)中, 如: C
使用连接工具使常规语言访问数据库, 如 CLI, JDBC, PHP/DB.

存储过程(Stored Procedures)

PSM (Persistent Stored Modules): 持久存储模块. 它们存储在数据库中, 作为模式的元素.

PSM = 常规语句(if, while 等) 与 SQL 语句的混合体.

可以做那些单用 SQL 不能做的事情.

为什么要使用存储过程

存储过程对于 MySQL 来说还是比较新的功能. 但应该要开始考虑把现有的程序转移到存储过程中来. 因为有下面几点原因:

存储过程是已经被认证的技术. 虽然在 MySQL 中是新的, 但是相同功能的函数在其他 DBMS 中早已存在.
存储过程会使系统运行更快.
存储过程是可复用的组件.
存储过程可以移植. MySQL 完全支持 SQL 2003 标准. 某些数据库 (如 DB2, Mimer) 同样支持. 但 Oracle, SQL Server 等不支持 SQL 2003 标准.

PHP

It often happens that in carrying out a database operation, a large amount of data must be transported back and forth between the PHP program and the database server: The PHP program executes a SELECT command, processes the result, executes an UPDATE command based on the results, and returns LAST_INSERT_ID, etc. If all of these steps can be executed on the server in an SP, a great deal of overhead in data transmission can be saved.

PL/SQL

PL/SQL 是 Oracle 的存储过程语言, 不同的 DBMS 使用不同的语言. PL/SQL 的功能比 MySQL 的存储过程强大.

PL/SQL 不仅允许你创建和存储过程或函数, 而且也可以从普通查询界面(generic query interface) sqlplus 运行. 就像 SQL 语句那样.

触发器是 PL/SQL 的一部分.

基本的 PSM 形式

CREATE PROCEDURE <name> (
    <parameter list> )
    <optional local declarations>
    <body>;

函数

CREATE FUNCTION <name> (
    <parameter list> ) RETURNS <type>
    <optional local declarations>
    <body>;

函数的定义与过程相仿, 区别是使用保留字 FUNCTION, 且必须指定返回值的类型.

PSM 中的参数

一般的编程语言(如: C 语言), 过程或函数的参数使用的是 name-type 配对, PSM 中过程或函数中的参数使用的是 mode-name-type 三元组, 其中 mode 可以是:

IN: 表明这是输入参数. 过程中使用的外面输入的值, 是只读的. 过程不改变该值.
OUT: 表明这是输出参数. 过程中会改变这个值.
INOUT: 既是输入参数又是输出参数.

例子: 存储过程(Stored Procedures)

我们来写一个过程, 用到两个参数 b 和 p. 它将 bar='Joe''s Bar', beer=b, price=p 这个元组插入到关系 Sells(bar,beer,price) 中.

这将方便 Joe 为自己的菜单添加新的记录.

过程(Procedure)

CREATE PROCEDURE JoeMenu (
    IN b CHAR(20),
    IN p REAL
)
INSERT INTO Sells
VALUES ('Joe''s Bar',b,p);

参数 b,p 都是只读的, 在过程中并不改变.
INSERT INTO ... 是过程的主体, 这里做的是插入一条记录.

实验(under MySQL)

mysql> CREATE PROCEDURE JoeMenu (
    ->     IN b CHAR(20),
    ->     IN p REAL
    -> )
    -> INSERT INTO Sells
    -> VALUES ('Joe''s Bar',b,p);
Query OK, 0 rows affected (0.37 sec)

Invoking Procedures

使用 SQL/PSM 的 CALL 语句, 格式是后面跟上过程名和用小括号括起来的参数表. 如:

CALL JoeMenu('Moose Drool', 5.00);

注意: 这里不允许调用函数. 在 PSM 中调用函数与在 C 中一样: 使用函数名和匹配的参数作为表达式的一部分. 也就是说, 这里函数是用在 SQL 表达式中的, 并且不管它返回类型的值是否合适.

mysql> CALL JoeMenu('Stella Artois', 5.00);
Query OK, 1 row affected (0.42 sec)

PSM 语句的种类

PSM 语句的种类 -- (1)

返回语句(return-statement), 设置函数返回的值.
RETURN <expression>
- 该语句只能出现在函数中.
- 与 C 等不同, RETURN 不会终止函数的运行.
局部变量声明(declarations of local variable), 用来声明局部变量.
DECLARE <name> <type>
- 函数或过程体的声明必须在可执行语句之前.
语句组(statement group), 可作为单个语句, 出现在任何单个语句可以出现的地方.
- 语句组中各语句之间用分号隔开.
赋值语句(assignment statements)
SET <variable> = <expression>;
- 例如: SET b='Bud';
- 表达式可以是 NULL, 甚至可以是查询, 只要该查询返回一个单值.
语句标号(statement label), 用名字(标号名)和冒号作为前缀来标识语句.

MySQL 中写存储函数时碰到的一些问题

不能建立函数的解决办法

当使用 CREATE FUNCTION 建立函数, 但是系统提示下面的信息

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable)

mysql> show variables like "log_bin_trust_function_creators";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.05 sec)

则我们应修改 MySQL 的配置文件, Windows 下是 my.ini, Linux 下是 /etc/mysql/my.cnf. 找到 [mysqld] 段落, 加上
log_bin_trust_function_creators=1
然后重新启动 MySQL.

函数命名的问题

mysql> select count(*) from Frequents;
+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> create function count()
    -> returns int 
    -> return (select count(*) from Frequents)//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count() returns int return (select count(*) from Frequents)' at line 1

这里 delimiter // 的作用是将 MySQL 的结束符设置为 //. 也可以是其他符号. MySQL 默认的语句结束符是 ;

mysql> delimiter //
mysql> create function countF()
    -> returns int 
    -> return (select count(*) from Frequents)//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select countF();
+----------+
| countF() |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)

说明函数名称不能使用 MySQL 内部的函数名称 count.

mysql> delimiter //
mysql> create function avg() 
    -> returns int 
    -> return (select count(*) from Frequents)//
Query OK, 0 rows affected (0.00 sec)

mysql> show create function avg\G;
*************************** 1. row ***************************
            Function: avg
            sql_mode: 
     Create Function: CREATE DEFINER=`haifeng`@`localhost` FUNCTION `avg`() RETURNS int(11)
return (select count(*) from Frequents)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> delimiter ;
mysql> select avg();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> select test_bar.avg();
+----------------+
| test_bar.avg() |
+----------------+
|             18 |
+----------------+
1 row in set (0.00 sec)

RETURNS 语句后面不能跟分号;

mysql> delimiter //
mysql> create function countF2()
    -> returns int;
    -> return (select count(*) from Frequents)//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
return (select count(*) from Frequents)' at line 2

说明 RETURNS int 后面不能跟分号 ;

查看存储函数的状态

使用 SHOW CREATE FUNCTION sp_name 查看.

这里 sp 是 Stored Procedures 的缩写.

mysql> show create function test_bar.countF\G;
*************************** 1. row ***************************
            Function: countF
            sql_mode: 
     Create Function: CREATE DEFINER=`haifeng`@`localhost` FUNCTION `countF`() RETURNS int(11)
return (select count(*) from Frequents)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

或使用 SHOW FUNCTION STATUS [LIKE 'pattern'] 查看.

查看当前数据库中定义的所有函数

mysql> show function status like '%'\G;
*************************** 1. row ***************************
                  Db: test_bar
                Name: Rate
                Type: FUNCTION
             Definer: haifeng@localhost
            Modified: 2013-10-30 09:35:35
             Created: 2013-10-30 09:35:35
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: test_bar
                Name: shorten
                Type: FUNCTION
             Definer: haifeng@localhost
            Modified: 2013-10-29 09:13:17
             Created: 2013-10-29 09:13:17
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

ERROR: 
No query specified

删除存储过程或存储函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

mysql> drop function if exists avg;
Query OK, 0 rows affected (0.00 sec)

变量的声明

DECLARE varname [, varname2] ... data_type [DEFAULT value];

mysql> delimiter //
mysql> create function Rate(b char(20))
    -> returns char(10)
    -> declare c int;
    -> begin
    -> set c=1;
    -> return c;
    -> end//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare c int;
begin
set c=1;
return c;
end' at line 3

mysql> create function Rate(b char(20)) 
    -> returns char(10) 
    -> declare c int; 
    -> set c=1; 
    -> return c; //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare c int; set c=1; return c' at line 1

mysql> create function Rate(b char(20)) 
    -> returns char(10) 
    -> begin 
    ->   declare c int;
    ->   set c=1; 
    ->   return c;
    -> end //
Query OK, 0 rows affected (0.00 sec)

因此用于变量声明的 DECLARE 语句应放在 BEGIN ... END 之间.

存储过程和存储函数的其他查询方式

MySQL 中存储过程和存储函数的信息都保存在数据库 information_schema 的表 Routines 中.

可使用通常的 select ... from ... where ... 语句查看.

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME ='sp_name';

mysql> select * from information_schema.Routines\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: JoeMenu
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test_bar
            ROUTINE_NAME: JoeMenu
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: INSERT INTO Sells
VALUES ('Joe's Bar',b,p)
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2013-10-22 11:23:04
            LAST_ALTERED: 2013-10-22 11:23:04
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: haifeng@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
           SPECIFIC_NAME: JoeMenu2
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test_bar
            ROUTINE_NAME: JoeMenu2
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: insert into Sells values('Joe's Bar',b,p)
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2013-10-29 10:15:39
            LAST_ALTERED: 2013-10-29 10:15:39
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: haifeng@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
           SPECIFIC_NAME: Rate
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test_bar
            ROUTINE_NAME: Rate
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 10
  CHARACTER_OCTET_LENGTH: 30
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
          DTD_IDENTIFIER: char(10)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    DECLARE cust INT;
    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
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2013-10-30 09:35:35
            LAST_ALTERED: 2013-10-30 09:35:35
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: haifeng@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 4. row ***************************
           SPECIFIC_NAME: shorten
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test_bar
            ROUTINE_NAME: shorten
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 255
  CHARACTER_OCTET_LENGTH: 765
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
          DTD_IDENTIFIER: varchar(255)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  IF isNULL(s) THEN
    RETURN '';
  ELSEIF n<15 THEN
RETURN LEFT(s,n);
  ELSE
    IF CHAR_LENGTH(s)<=n THEN
RETURN s;
  ELSE
RETURN CONCAT(LEFT(s,n-10),'...',RIGHT(s,5));
  END IF;
END IF;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2013-10-29 09:13:17
            LAST_ALTERED: 2013-10-29 09:13:17
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: haifeng@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
4 rows in set (0.00 sec)

ERROR: 
No query specified

IF 语句

简单形式:

IF <condition> THEN
    <statement(s)>
END IF

如果需要, 加入 ELSE <statement(s)>, 如下:

IF <condition> THEN
    <statement(s)>
ELSE 
    <statement(s)>
END IF

若要加入额外的情形, 则使用 ELSEIF <statement(s)>, 如:

IF <condition> THEN
    <statement(s)>
ELSEIF <condition> THEN
    <statement(s)>
ELSE 
    <statement(s)>
END IF

例子: IF

我们来给各个酒吧评一下等级, 根据它们所拥有的顾客数. 这是基于关系 Frequents(drinker,bar) 来计算的.

顾客数 $< 100$ 的定为: 'unpopular'.
顾客数在 $100$ 到 $199$ 之间的定为: 'average'.
顾客数 $>= 200$ 的定为: 'popular'.

函数 Rate(b) 对于酒吧 b 评级.

例子: IF

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;

cust=(SELECT COUNT(*)...) 是经常去酒吧 b 的顾客数目.
函数的返回值将在最后一个 END; 处返回, 而不是在嵌套的 IF 中的 RETURN 语句处返回.

实验(under MySQL)

正确的语法是

delimiter $$
CREATE FUNCTION Rate(b CHAR(20))
    RETURNS CHAR(10)
  BEGIN
    DECLARE cust INT;
    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$$

mysql> delimiter $$
mysql> CREATE FUNCTION Rate(b CHAR(20))
    ->     RETURNS CHAR(10)
    ->   BEGIN
    ->     DECLARE cust INT;
    ->     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$$
Query OK, 0 rows affected (0.00 sec)

mysql> select Rate("HardRock");
+------------------+
| Rate("HardRock") |
+------------------+
| unpopular        |
+------------------+
1 row in set (0.00 sec)

mysql> show create function Rate\G;
*************************** 1. row ***************************
            Function: Rate
            sql_mode: 
     Create Function: CREATE DEFINER=`haifeng`@`localhost` FUNCTION `Rate`(b CHAR(20)) RETURNS char(10) CHARSET utf8
BEGIN
    DECLARE cust INT;
    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
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

另一个例子

delimiter $$
CREATE FUNCTION shorten(s varchar(255), n INT)
  RETURNS varchar(255)
BEGIN
  IF isNULL(s) THEN
    RETURN '';
  ELSEIF n<15 THEN
    RETURN LEFT(s,n);
  ELSE
    IF CHAR_LENGTH(s)<=n THEN
      RETURN s;
    ELSE
      RETURN CONCAT(LEFT(s,n-10),'...',RIGHT(s,5));
    END IF;
  END IF;
END$$

mysql> delimiter ;
mysql> select shorten("abcdefghijklmn", 5);
+------------------------------+
| shorten("abcdefghijklmn", 5) |
+------------------------------+
| abcde                        |
+------------------------------+
1 row in set (0.00 sec)

mysql> select shorten("abcdefghijklmnopqrstuvwxyz", 15);
+-------------------------------------------+
| shorten("abcdefghijklmnopqrstuvwxyz", 15) |
+-------------------------------------------+
| abcde...vwxyz                             |
+-------------------------------------------+
1 row in set (0.00 sec)

循环(Loops)

基本形式:

<loop name>: LOOP <statements>
    END LOOP;

从循环中跳出:

LEAVE <loop name>

跳出循环

loop1: LOOP
    ...
    LEAVE loop1;
    ...
END LOOP;

如果执行了上面的 LEAVE loop1; 语句, 则程序就直接跳转到 END LOOP; 的下一条语句.

实验(under MySQL)

mysql> delimiter //
mysql> create function looptest()
    -> returns int
    -> begin
    -> declare n int default 0;
    -> L:LOOP
    -> if n>100 then leave L;
    -> end if;
    -> set n=n+5;
    -> END LOOP;
    -> return n;
    -> end//

注意函数名不能使用 loop()

mysql> delimiter ;
mysql> select looptest();
+------------+
| looptest() |
+------------+
|        105 |
+------------+
1 row in set (0.00 sec)

其他循环形式

WHILE <condition>
    DO <statements>
END WHILE;

REPEAT <statements>
    UNTIL <condition>
END REPEAT;

注意: 目前 MySQL 中只有三种形式的循环: LOOP, WHILE, REPEAT.

下面这种 FOR 循环形式是在 IBM 的 DB2 中有的, MySQL 中没有.

FOR <loop name> AS <cursor name> CURSOR FOR
    <query>
DO
    <statement list>
END FOR;

实验(under MySQL)

mysql> create procedure repeattest()
    -> begin
    -> declare id int default 0;
    -> R:repeat
    -> set id=id+1;
    -> until id>=10;
    -> end repeat;
    -> end//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; end repeat R; end' at line 1

UNTIL语句后面不能加分号 ;

mysql> create procedure repeat2() 
    -> begin 
    -> declare id int default 0;
    -> R:repeat 
    -> set id=id+1;
    -> until id>=10 
    -> end repeat R; 
    -> end//
Query OK, 0 rows affected (0.00 sec)

过程名不能使用 repeat(). 当然这个过程并没有实际意义, 这里仅是提醒注意事项.

mysql> delimiter //
mysql> create function whiletest()
    -> returns int
    -> begin
    -> declare i int default 0;
    -> while i<10 do
    -> set i=i+1;
    -> end while;
    -> return i;
    -> end//

同样函数名不能使用 while().

书上的例子

书本第9章 P.236 图 9-13

CREATE FUNCTION BandW(y INT, s CHAR(15))
  RETURNS BOOLEAN
  IF NOT EXISTS (
    SELECT * FROM Movies WHERE year = y AND studioName = s
  )
    THEN RETURN TRUE;
  ELSEIF 1<=
    (SELECT COUNT(*) FROM Movies WHERE year=y AND 
         studioName=s AND genre='comedy')
    THEN RETURN TRUE;
  ELSE RETURN FALSE;
  END IF;//

书本第9章 P.238 图 9-15. 已经改写为适合 MySQL 下运行.

CREATE PROCEDURE MeanVar(
    IN s CHAR(15),
    OUT mean REAL,
    OUT variance REAL
)
BEGIN

DECLARE Not_Found CONDITION FOR SQLSTATE '02000';
DECLARE MovieCursor CURSOR FOR
    SELECT length FROM Movies WHERE studioName = s;
DECLARE newLength INTEGER;
DECLARE movieCount INTEGER;


    SET mean = 0.0;
    SET variance = 0.0;
    SET movieCount =0;
    OPEN MovieCursor;
    movieLoop: LOOP
        FETCH FROM MovieCursor INTO newLength;
        IF Not_Found THEN LEAVE movieLoop END IF;
        SET movieCount = movieCount + 1;
        SET mean = mean + newLength;
        SET variance = variance + newLength * newLength;
    END LOOP;
    SET mean = mean / movieCount;
    SET variance = variance / movieCount - mean * mean;
    CLOSE MovieCursor;
END;

查询

通常的 SELECT-FROM-WHERE 查询是不允许在 PSM 中使用的.

不过有三种方式可以使用这样的查询.

若查询返回的关系是单值的, 则可以作为表达式用于赋值语句中.
单行(单元组), SELECT ... INTO
游标(cursors)

例子: 赋值/查询

使用局部变量 p 和关系 Sells(bar,beer,price), 我们可以得到 Joe 的酒吧关于 Bud 啤酒的价格.

SET p = (SELECT price FROM Sells
    WHERE bar = 'Joe''s Bar' AND
        beer = 'Bud');

SELECT ... INTO

对于返回单个元组的查询, 获取其中各个属性的值, 有另一种方式. 即将 INTO <variable> 加在 SELECT 语句后面.

例如:

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

游标(Cursors)

游标(Cursors) 本质上是一个元组变量, 它可以跑遍查询所得关系中的所有元组.

使用下面的方式声明一个游标 c:

DECLARE c CURSOR FOR <query>;

开启和关闭游标

为使用游标 c, 必须先初始化(即开启游标):
OPEN c;

初始化游标 c 的位置, 使 c 指向查询结果关系中的第一个元组, 并从那里开始检索.

当游标 c 使用结束, 则关闭它.
CLOSE c;

取回游标所指的元组

从游标 c 获取下一个元组, 采用如下命令:
FETCH FROM c INTO x1, x2, ..., xn;

x1,...,xn 是一列变量, 对应到 c 所指元组的每个属性.

c 自动移动到下一个元组.

关于 FETCH ... INTO ... 的详细用法, 请参考 https://dev.mysql.com/doc/refman/5.7/en/fetch.html

中断游标循环 -- (1)

中断游标循环-- (1)

使用游标的通常方式是建立一个含有 FETCH 语句的循环, 并且对每个取回的元组做点什么事情.

一个技巧性的问题是当游标没有元组要传送时, 我们如何跳出循环.

中断游标循环 -- (2)

中断游标循环-- (2)

每个 SQL 操作返回一个状态(status), 它是一个长度是 5 的字符串.

例如: 00000 表示一切都正常. 而 02000 指没有找到元组.

在 PSM 中, 我们可以通过一个名为 SQLSTATE 的变量来获知系统的状态.

中断游标循环 -- (3)

中断游标循环-- (3)

我们可以定义一个 condition, 它是一个 boolean 变量. 其值为真当且仅当 SQLSTATE 返回一个特别的值.

例如: 我们可以声明一个名为 NotFound 的变量, 它代表了 02000.

DECLARE NotFound CONDITION FOR
    SQLSTATE '02000';

中断游标循环 -- (4)

中断游标循环-- (4)

游标循环的结构如下:

cursorLoop: LOOP
    ...
    FETCH c INTO ... ;
    IF NotFound THEN LEAVE cursorLoop;
    END IF;
    ...
END LOOP;

例子: 游标(Cursors)

我们来写一个过程, 检查关系 Sells(bar,beer,price) 中所有在 Joe 酒吧销售的啤酒, 如果低于 $\$ 3$, 则将它们统一提价 $\$ 1$.

当然, 我们可以用一个简单的 UPDATE 语句来完成这个任务. 但是这里的细节仍是有意义的.

例子: 采用游标(Cursors)的程序

CREATE PROCEDURE JoeGouge()
    DECLARE theBeer CHAR(20);
    DECLARE thePrice REAL;
    DECLARE NotFound CONDITION FOR
        SQLSTATE '02000';
    DELCARE c CURSOR FOR
        (SELECT beer, price FROM Sells
         WHERE bar='Joe''s Bar');
  BEGIN
    OPEN c;
    menuLoop:LOOP
        FETCH c INTO theBeer, thePrice;
        IF NotFound THEN LEAVE menuLoop END IF;
        IF thePrice < 3.00 THEN
            UPDATE Sells SET price = thePrice + 1.00
            WHERE bar = ’Joe’’s Bar’ AND beer = theBeer;
        END IF;
    END LOOP;
    CLOSE c;
  END;

前两个 DECLARE 语句是两个必须的声明.
子查询 (SELECT ... WHERE ...)返回 Joe 的菜单.
位于 BEGIN ... END 之间的是过程体.
If NotFound ...检查最近的 FETCH 语句是否没有元组返回.
IF thePrice ... END IF;

Joe

实验(under MySQL)

mysql> create procedure JoeGouge()
    -> begin
    -> declare theBeer char(20);
    -> declare thePrice real;
    -> declare c cursor for 
    -> select beer,price from Sells where bar='Joe''s bar';
    -> open c;
    -> menuLoop:LOOP
    -> fetch c into theBeer,thePrice;
    -> if thePrice<20 then
    -> update Sells set price=thePrice+1 where
    -> beer=theBeer AND bar='Joe''s bar';
    -> end if;
    -> end LOOP;
    -> close c;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> select * from Sells where price < 20//
+-----------+---------------+-------+
| bar       | beer          | price |
+-----------+---------------+-------+
| Joe's Bar | Stella Artois |     5 |
+-----------+---------------+-------+
1 row in set (0.00 sec)
mysql> call JoeGouge();
    -> //
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql> select * from Sells where price < 21;
+-----------+---------------+-------+
| bar       | beer          | price |
+-----------+---------------+-------+
| Joe's Bar | Stella Artois |     6 |
+-----------+---------------+-------+
1 row in set (0.00 sec)

尽管这个存储过程设计得并不好, LOOP 循环居然没有 LEAVE 语句, 但还是起作用了.

mysql> delimiter //
mysql> create procedure JoeGouge2()
    -> begin
    -> declare theBeer char(20);
    -> declare thePrice real;
    -> declare NotFound condition for sqlstate '02000';
    -> declare c cursor for
    -> select beer,price from Sells;
    -> open c;
    -> menuLoop: LOOP
    -> fetch c into theBeer, thePrice;
    -> if NotFound then leave menuLoop;
    -> end if;
    -> if thePrice < 20 then
    -> update Sells set price=thePrice+1
    -> where beer=theBeer;
    -> end if;
    -> end LOOP;
    -> close c;
    -> end//
Query OK, 0 rows affected (0.00 sec)

CREATE PROCEDURE JoeGouge3()
  BEGIN
    DECLARE theBeer CHAR(20);
    DECLARE thePrice REAL;
    DECLARE NotFound CONDITION FOR
        SQLSTATE '02000';
    DELCARE c CURSOR FOR
        SELECT beer, price FROM Sells WHERE bar='Joe''s Bar';
    OPEN c;
    menuLoop:LOOP
        FETCH c INTO theBeer, thePrice;
        IF NotFound THEN LEAVE menuLoop END IF;
        IF thePrice < 3.00 THEN
            UPDATE Sells SET price = thePrice + 1.00
            WHERE bar = 'Joe''s Bar' AND beer = theBeer;
        END IF;
    END LOOP;
    CLOSE c;
  END//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c CURSOR FOR
        SELECT beer, price FROM Sells WHERE bar='Joe''s Bar';
    O' at line 7

LEAVE menuLoop 后面要跟分号 ;

另一个例子

例子来源于 http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html#cursors

mysql> CREATE PROCEDURE curdemo()
    -> BEGIN
    ->   DECLARE done INT DEFAULT 0;
    ->   DECLARE a CHAR(16);
    ->   DECLARE b,c INT;
    ->   DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
    ->   DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->   OPEN cur1;
    ->   OPEN cur2;
    ->   REPEAT
    ->     FETCH cur1 INTO a, b;
    ->     FETCH cur2 INTO c;
    ->     IF NOT done THEN
    ->        IF b < c THEN
    ->           INSERT INTO test.t3 VALUES (a,b);
    ->        ELSE
    ->           INSERT INTO test.t3 VALUES (a,c);
    ->        END IF;
    ->     END IF;
    ->   UNTIL done END REPEAT;
    ->   CLOSE cur1;
    ->   CLOSE cur2;
    -> END//
Query OK, 0 rows affected (0.00 sec)

CLOSE cur1; 这个语句关闭先前打开的游标. 如果未被明确地关闭, 游标在它被声明的复合语句的末尾被关闭.

PSM 中的 For 循环

PSM 中也有 For 循环, 不过它的作用仅仅是游标的迭代. 语句形如:

FOR <loop name> AS <cursor name> CURSOR FOR
    <query>
DO
    <statement list>
END FOR;

由于不是直接获取自己的元组, 所以不能为那些被替代的元组分量指定存储变量. 查询结果的属性名可作为局部变量.

我们用 FOR 循环改写刚才的 LOOP 循环程序.

CREATE PROCEDURE JoeGouge()
  BEGIN
    FOR menuLoop AS c CURSOR FOR
      (SELECT beer AS theBeer, price AS thePrice FROM Sells
      WHERE bar='Joe''s Bar');
    DO
      IF price < 3.00 THEN
        UPDATE Sells SET price=thePrice+1.00
        WHERE bar='Joe''s Bar' AND beer=theBeer;
      END IF;
    END FOR;
  END;

这里不必声明游标, 因为 FOR 循环定义了游标 c.
也不必定义条件 NotFound.
这里使用了属性 beer, price 重命名后的属性名 theBeer, thePrice 作为局部变量.
FOR 循环中必须要对循环命名, 如这里的 menuLoop. 这是因为 PSM 解释程序将 FOR 循环解释为一个普通的 LOOP 循环. LOOP 循环需要命名.

PSM 中的异常处理

SQL 系统通过在长为 5 个字符的字符串 SQLSTATE 变量中设置非零数字序列来表明错误条件, 而 '00000' 表示没有产生任何错误.

'02000' 表示没有找到作为 SQL 查询结果组成部分的元组.
'21000' 表示单元组选择返回了多个元组.

PSM 可以声明叫做异常处理(exception handler) 的代码. 也就是说, 在语句或语句组执行过程中, 当错误代码列表中的任何一个出现在 SQLSTATE 中时, 就调用异常处理.

每个异常处理都对应一个 BEGIN ... END 代码块.

异常处理的声明形式

DECLARE <next step> HANDLER FOR <condition list>
    <statements>

这里的转移 <next step> 有如下几种选择方式:

CONTINUE: 表示执行异常处理声明中的语句之后, 继续执行产生异常的语句之后的那条语句.
EXIT: 表示执行异常处理语句之后, 控制离开声明异常处理的 BEGIN ... END 语句块. 下一步执行该代码块之后的语句.
UNDO: 与 EXIT 差不多, 但已执行的该块语句对数据库或局部变量产生的变化都被撤销. 也就是说, 块是事务, 因此它会由于异常而被撤销.

例子: 异常处理

从 Sells(bar,beer,price) 关系中查询某种啤酒的售价. 希望各个酒吧对于这一种啤酒的售价是一致的. 如果没有这种啤酒则返回 0; 如果存在售价不一致的情况则返回 -1; 如果售价一致则返回该价格.

CREATE FUNCTION GetUniquePrice (b VARCHAR(20)) RETURNS REAL

DECLARE Not_Found CONDITION FOR SQLSTATE '02000';
DECLARE Too_Many CONDITION FOR SQLSTATE '21000';

BEGIN
    DECLARE EXIT HANDLER FOR Not_Found
        RETURN 0;
    DECLARE EXIT HANDLER FOR Too_Many
        RETURN -1;
    RETURN (SELECT DISTINCT price FROM Sells WHERE beer=b); --(SELECT price FROM Sells WHERE beer=b LIMIT 1);
END;

表达式中使用函数的例子

将 Sells 关系中售价一致的啤酒插入到表 UniquePrice(beer,price) 中. 当然首先得建立这样一张表.

CREATE PROCEDURE PrintUniquePrice (
    IN b VARCHAR(20),
    OUT p REAL
    )

DECLARE Not_Found CONDITION FOR SQLSTATE '02000';
DECLARE Too_Many CONDITION FOR SQLSTATE '21000';


BEGIN
    DECLARE EXIT HANDLER FOR Not_Found;
    DECLARE EXIT HANDLER FOR Too_Many;
    SET p=(SELECT price FROM Sells WHERE beer=b LIMIT 1);
    INSERT INTO UniquePrice (beer, price) VALUES (b, p);
END;

将之改进为对所有啤酒的循环

嵌入式 SQL

SQL语言的使用有两种方式：

在终端交互方式下独立使用的SQL称为交互式SQL。
嵌入到程序设计语言中（即宿主语言）使用的SQL称为嵌入式SQL。

主要思想: 预处理器将 SQL 语句转化为适合周围宿主语言调用的过程.

所有嵌入 SQL 语句都以 EXEC SQL 开始, 因此预处理器可以很容易识别它们.

嵌入式SQL须解决下列几个问题：

如何将嵌有SQL的宿主语言程序编译成可执行码（这是首要问题）。
宿主语言和DBMS之间如何传递数据和信息。
如何将查询结果赋值给宿主语言程序中的变量（通过游标语句）。
宿主语言与SQL之间数据类型的转换问题（按系统约定转换）。

PHP 以及现在的很多scripting language, 比如 Perl, Python, Ruby, Javascript 等都不支持嵌入式SQL(Embedded SQL)。只有C和C++以及一些老的语言支持嵌入式SQL。

现在新的应用软件很少使用嵌入式SQL。这是1980s--1990s时的产品。不过使用嵌入式SQL有个好处，就是不用担心 SQL injection。因为其中的SQL语句在编译前被解析（预编译）而不是运行时解析。

现在使用的都是动态(dynamic)SQL. 也就是将SQL放到一个字符串变量中，然后利用一个API将它作为一个字符串发送到RDBMS server, 它将在运行时(runtime)被RDBMS server解析。目前大多SQL API都是这么做的, 比如 JDBC, ODBC, 以及 PHP 中的各种数据库扩展。

目前支持嵌入式SQL的数据库有 PostgreSQL 和 Firebird。请分别参考:

关于如何编译PostgreSQL的嵌入式SQL代码，参见
https://www.postgresql.org/docs/9.3/static/ecpg-process.html

因此，如果需要学习嵌入式SQL的话，建议先学习PostgreSQL或Firebird。

References: 嵌入式SQL介绍 http://www.cppblog.com/prayer/archive/2010/01/11/105415.html 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/prayforever/archive/2008/08/06/2778495.aspx Bill Karwin, https://www.quora.com/What-is-embedded-SQL-Does-PHP-support-it

共享变量(Shared Variables)

为了联系 SQL 语句和宿主语言程序, 这两个部分必须共享某些变量.

共享变量的声明加入到如下两个嵌套 SQL 语句之间:

EXEC SQL BEGIN DECLARE SECTION;
    <host-language declarations>
EXEC SQL END DECLARE SECTION;

这两个语句之间的部分称为声明节(declare section).

共享变量的使用

在 SQL 中, 共享变量必须加前缀冒号 ":".

由宿主语言程序提供的共享变量可以用在 SQL 语句中任何需要和允许常量的地方.
它们可以从 SQL 语句获得值并传递给宿主语言程序.

在宿主语言中, 共享变量的行为与其他变量的行为并无二致.

例子: 查询价格

我们将使用 C 语言和嵌入式 SQL 语言来大致描述一下我们所需要的函数.

对于关系 Sells(bar,beer,price). 通过输入参数 beer,bar, 返回该酒吧(bar)销售此啤酒(beer)的价格.

例子: C 结合 SQL

EXEC SQL BEGIN DECLARE SECTION;
    char theBar[21], theBeer[21],
    float thePrice;
EXEC SQL END DECLARE SECTION;
/* obtain values for theBar and theBeer */
EXEC SQL SELECT price INTO :thePrice
    FROM Sells
    WHERE bar = :theBar AND beer = :theBeer;
    /* do something with thePrice*/

这里的 SELECT ... INTO 类似于 PSM 中的变量赋值.

嵌入式查询(Embedded Queries)

嵌入式 SQL 语句与 PSM 中相关的查询有同样的限制:

SELECT-INTO 要保证生成一个元组.
否则必须使用游标.
- 在语法上有较小差异, 但主要思想是相同的.

游标语句

声明一个游标:

EXEC SQL DECLARE c CURSOR FOR <query>

开启和关闭游标:

EXEC SQL OPEN CURSOR c;
EXEC SQL CLOSE CURSOR c;

从游标:处获得数据.

EXEC SQL FETCH c INTO <variable(s)>;

宏 NOT_FOUND 为真当且仅当 FETCH 没有取得一个元组.

例子: 打印 Joe 的菜单

我们使用 C+SQL 来打印 Joe 的菜单, 即从关系 Sells(bar,beer,price) 中找到的 bar='Joe''s Bar' 的那些 beer-price 对.

游标将访问 Sells 关系中 bar='Joe''s Bar' 的每个元组.

例子: 声明(Declarations)

EXEC SQL BEGIN DECLARE SECTION;
    char theBeer[21];
    float thePrice;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c CURSOR FOR
    SELECT beer, price FROM Sells
    WHERE bar = 'Joe''s Bar';

注意游标的声明在声明节(declare-section)之外.

例子: 可执行部分

EXEC SQL OPEN CURSOR c;
while(1){
	EXEC SQL FETCH c
	    INTO :theBeer, :thePrice;
	if(NOT_FOUND) break;
	/* the C style of breaking loops */
	/* format and print theBeer and thePrice */
}
EXEC SQL CLOSE CURSOR c;

动态 SQL 的需求

大多数应用使用特定的查询和修改语句与数据库进行交互.

DBMS 将 EXEC SQL ... 语句编译为特定的过程调用. 并且生成一个使用库的通常的宿主语言程序.

另一种形式的嵌套 SQL 语句自身可以被宿主语言处理, 它们在编译时不可知, 因此不能被 SQL 预处理器和宿主语言编译器处理.

例如. 有的程序提示用户输入 SQL 查询, 然后读这个查询, 并执行这个查询.

动态 SQL

准备一个查询:

EXEC SQL PREPARE <query-name>
    FROM <text of the query>;

执行一个查询:

EXEC SQL EXECUTE <query-name>;

"Prepare" = optimize query.
一次准备, 可以执行多次.

例子: A Generic Interface

EXEC SQL BEGIN DECLARE SECTION;
    char query[MAX_LENGTH];
EXEC SQL END DECLARE SECTION;
while(1){
	/* issue SQL > prompt */
	/* read user's query into array query */
	EXEC SQL PREPARE q FROM :query;
	EXEC SQL EXECUTE q;
}

q 是一个 SQL 变量, 它代表的是宿主语言中变量 :query 存储的字符串. 该字符串被当作 SQL 语句.

立即执行(Execute-Immediate)

如果语句被编译后只是执行一次, 则可以将上面的 PREPARE 和 EXECUTE 两个步骤并为一个. 利用下面的语句

EXEC SQL EXECUTE IMMEDIATE <text>;

例子: Generic Interface Again

EXEC SQL BEGIN DECLARE SECTION;
    char query[MAX_LENGTH];
EXEC SQL END DECLARE SECTION;
while(1){
	/* issue SQL> prompt */
	/* read user's query into array query */
	EXEC SQL EXECUTE IMMEDIATE :query;
}

End

Thanks very much!

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

实际程序中的 SQL

实际程序中的 SQL

Options

Options

存储过程(Stored Procedures)

存储过程(Stored Procedures)

为什么要使用存储过程

为什么要使用存储过程

PHP

PL/SQL

PL/SQL

基本的 PSM 形式

基本的 PSM 形式

PSM 中的参数

PSM 中的参数

例子: 存储过程(Stored Procedures)

例子: 存储过程(Stored Procedures)

过程(Procedure)

过程(Procedure)

实验(under MySQL)

Invoking Procedures

Invoking Procedures

PSM 语句的种类

PSM 语句的种类 -- (1)

MySQL 中写存储函数时碰到的一些问题

MySQL 中写存储函数时碰到的一些问题

不能建立函数的解决办法

函数命名的问题

RETURNS 语句后面不能跟分号;

查看存储函数的状态

删除存储过程或存储函数

变量的声明

存储过程和存储函数的其他查询方式

IF 语句

IF 语句

例子: IF

例子: IF

例子: IF

例子: IF

实验(under MySQL)

循环(Loops)

循环(Loops)

实验(under MySQL)

其他循环形式

其他循环形式

实验(under MySQL)

书上的例子

书上的例子

查询

查询

例子: 赋值/查询

例子: 赋值/查询

SELECT ... INTO

SELECT ... INTO

游标(Cursors)

游标(Cursors)

开启和关闭游标

开启和关闭游标

取回游标所指的元组

取回游标所指的元组

中断游标循环 -- (1)

中断游标循环-- (1)

中断游标循环 -- (2)

中断游标循环-- (2)

中断游标循环 -- (3)

中断游标循环-- (3)

中断游标循环 -- (4)

中断游标循环-- (4)

例子: 游标(Cursors)

例子: 游标(Cursors)

例子: 采用游标(Cursors)的程序

例子: 采用游标(Cursors)的程序

实验(under MySQL)

另一个例子

PSM 中的 For 循环

PSM 中的 For 循环