1. 创建PSM函数和过程
创建过程:
CREATE PROCEDURE 名字 (参数)
局部声明;
过程体;
创建函数:
CREATE FUNCTION 名字 (参数) RETURNS 类型
局部声明;
过程体;
过程的参数:模式-名字-类型的三元组。模式有 IN, OUT, INOUT,默认为IN
函数的参数:只能是IN。
CREATE PROCEDUREMove(IN oldAddr VARCHAR(255),IN newAddr VARCHAR(255)
)UPDATEMovieStarSET address =newAddressWHERE address = oldAddress;
2. PSM中的简单语句
2.1 调用语句:
CALL 过程名 (参数)
在宿主语言中调用形式:EXEC SQL CALL Foo(:x, 3);
作为另一个PSM中的语句
作为发送给基本SQL界面的SQL命令(不许调用函数):CALL Foo(1, 3);
2.2 返回语句:
RETURN 表达式;
只能出现在函数中,PSM的返回语句不会结束这个函数,在函数完成之前返回值都可能会改变。
2.3 局部变量声明
DECLARE 名字 类型;
2.4 赋值语句
SET 变量 = 表达式;
2.5 语句组
以分号结束,置于BEGIN和END之间
2.6 语句标号
用名字和冒号作为前缀来标识语句。
3.分支语句
IF THEN
ELSEIF THEN
ELSEIF
...
ELSE
END IF;
CREATE FUNCTION BandW(y INT, s CHAR(15)) RETURNBOOLEANIF NOT EXISTS(SELECT * FROM Movies WHERE year = y AND studioName =s)THEN RETURNTRUE;
ELSEIF1 <=(SELECT COUNT(*) FROM Movies WHERE year = y ANDstudioName= s AND genre = 'comedy')THEN RETURNTRUE;ELSE RETURNFALSE;END IF;
4. PSM中的查询
可以有多种查询方式:
①使用子查询
②返回单一值的查询可用在赋值语句的右边
③使用单元组选择语句
CREATE PROCEDURE SomeProc(IN studioName CHAR(15))DECLARE presNetWorth INTEGER;SELECTnetWorthINTOpresNetWorthFROMStudio, MovieExecWHERE presC# = cert# AND Studio.name =studioName;
...
④ 声明和使用游标
语句中不需要出现EXEC SQL
局部变量不使用冒号前缀
5.PSM中的循环
LOOP
END LOOP;
中断循环: LEAVE 循环标识;
定义与SQLSTATE值相对应的条件名:
DECLARE 名字 CONDITION FOR SQLSTATE 值;
CREATE PROCEDUREMeanVar(IN s CHAR(15),
OUT meanREAL,
OUT varianceREAL)DECLARE Not_Found CONDITION FOR SQLSTATE '02000';DECLARE MovieCursor CURSOR FOR
SELECT length FROM Movies WHERE studioName =s;DECLARE newLength INTEGER;DECLARE movieCount INTEGER;BEGIN
SET mean = 0.0;SET variance = 0.0;SET movieCount = 0;OPENMovieCursor;
movieLoop: LOOPFETCH FROM MovieCursor INTOnewLength;IF Not_Found THEN LEAVE movieLoop END IF;SET movieCount = movieCount + 1;SET mean = mean +newLength;SET variance = variance + newLength *newLength;ENDLOOP;SET mean = mean /movieCount;SET variance = variance / movieCount - mean *mean;CLOSEMovieCursor;END;
6.循环
FOR循环
FOR AS CURSOR FOR
DO
END FOR;
WHILE循环:
WHILE DO
END WHILE;
REPEAT循环:
REPEAT
UNTIL
END REPEAT
CREATE PROCEDUREMeanVar(IN s CHAR(15),
OUT meanREAL,
OUT varianceREAL)DECLARE movieCount INTEGER;BEGIN
SET mean = 0.0;SET variance = 0.0;SET movieCount = 0;FOR movieLoop AS MovieCursor CURSOR FOR
SELECT length FROM Movies WHERE studioName =s;
DOSET movieCount = movieCount + 1;SET mean = mean +length;SET variance = variance + length *length;END FOR;SET mean = mean /movieCount;SET variance = variance / movieCount - mean *mean;END;
注意,在FOR循环中,用属性名表示查询结果!
7. PSM中的异常处理
DECLARE HANDLER FOR
转移的方式有下面三种:
CONTINUE:表示执行异常处理语句后,继续执行产生异常语句之后的语句
EXIT:表示执行异常处理语句后,离开异常处理的BEGIN END块,下一步执行该代码块之后的语句。
UNDO:与EXIT差不多,但是撤销已执行的该块语句对数据库和局部变化的影响。
CREATE FUNCTION GetYear(t VARCHAR(255)) RETURN INTEGER
DECLARE Not_Found CONDITION FOR SQLSTATE '02000';DECLARE Too_Many CONDITION FOR SQLSTATE '21000';BEGIN
DECLARE EXIT HANDLER FORNot_Found, Too_ManyRETURN NULL;RETURN (SELECT year FROM Movies WHERE title =t);END;
8.使用PSM函数和过程
可以像下面这样使用
INSERT INTOStarsIn(movieTitle, movieYear, starName)VALUES('Remember the Titans', GetYear('Remember the Titans'), 'Denzel Washington');