实践实践:SYBASE IQ 存储过程学习笔记存储过程学习笔记1.1.存储过程存储过程 存储过程将过程化的 SQL 语句存储在数据库中，供所有程序使用。存储过程中可以包含 控制语句,如 LOOP 循环语句、IF 和 CASE 这样的条件语句。存储过程通过 CALL 语句进 行调用，使用变量传入参数和返回结果。可以返回结果集，也可以调用其他的存储过程。2.2.存储过程和函数的区别存储过程和函数的区别 自定义函数是只能返回单一值的特定的存储过程。函数不修改传入的参数，但是可以使 其用于查询和其他 SQL 语句之中。3.3.存储过程的调试存储过程的调试 参见附录 C Debugging Logic in the Database4.4.存储过程概要存储过程概要 常用的存储过程 创建存储过程 修改存储过程 调用存储过程 删除存储过程 存储过程的访问控制 返回值常用的存储过程sp_iqprocedure 此存储过程可以显示系统和用户自定义的存储过程sp_iqprogram 显示存储过程的参数信息,包括结果集变量和 SQLSTATE/SQLCODE 错误值创建存储过程CREATE PROCEDUREnew_dept (IN id INT,IN name CHAR(35),IN head_id INT )BEGIN1INSERT INTO DBA.department(dept_id, dept_name, dept_head_id)VALUES(id, name, head_id);END修改存储过程 使用 SQL 语句 ALTER PROCEDURE，将整个新的存储过程包含其中。 必须重新给修改后的存储过程赋予用户权限。调用存储过程CALL new_dept(210, Eastern Sales, 902);2删除存储过程DROP PROCEDURE new_dept存储过程的访问控制 见注(2)返回值 可以通过三种方式传回值：使用 OUT 或者 INOUT 返回值；返回结果集；使用 RETURN 语句返回单值。使用 OUT 和 INOUT 返回值3CREATE PROCEDURE AverageSalary( OUT avgsal NUMERIC (20,3) ) BEGINSELECT AVG( salary ) INTO avgsal FROM employee; END返回结果集CREATE PROCEDURE SalaryList (IN department_id INT) RESULT ( “Employee ID“ INT, “Salary“ NUMERIC(20,3) )41 复合语句,放在 BEGIN 和 END 之间;复合语句可以相互嵌套;复合语句用于将多个语句组合成一个单元,其中的 SQL 语句用分号分隔;除了最后一个分号,其他都是必须的;复合语句中声明的局部变量只在复合语句中可; 可以在 BEGIN 后面加上 ATOMIC,将复合语句声明为原子性,此时就不可使用 COMMIT、ROLLBACK 和 ROLLBACK TO SAVEPOINT. 2 必须赋予 EXECUTE 权限才可以执行此存储过程， GRANT EXECUTE ON new_dept TO another_user;REVOKE EXECUTE ON new_dept FROM another_user 3 调用之前,可以先声明一个变量保存结果,语句如下 CREATE VARIABLE Average NUMERIC(20,3)4 存储过程还可以返回多个同类型的结果集，必须在客户端中启用返回多个结果集的支持。例:CREATE PROCEDURE ListPeople() RESULT ( lname CHAR(36), fname CHAR(36) ) BEGIN SELECT emp_lname, emp_fname FROM employee;BEGINSELECT emp_id, salaryFROM employee WHERE employee.dept_id = department_id; END如果在存储过程中一个语句动态的创建了一个临时表,然后从里面选取结果集, 为了避免如“Column not found”这样的错误，必须使用 EXECUTE IMMEDIATE WITH RESULT SET ON 这样的语法。例：CREATE PROCEDURE p1 (IN t varchar(30)BEGINEXECUTE IMMEDIATE SELECT * INTO #resultSet5 FROM | t;EXECUTE IMMEDIATE WITH RESULT SET ON SELECT * FROM #resultSet; END5.5.自定义函数自定义函数 创建自定义函数CREATE FUNCTION fullname (firstname CHAR(30), lastname CHAR(30) RETURNS CHAR(61) BEGINDECLARE name CHAR(61);6SET name = firstname | | lastname;RETURN ( name ); END和存储过程存在 3 个差异 参数不需要 IN、OUT 和 INOUT，所有参数默认是 IN RETURNS 语句指明要返回的值 RETURN 语句用来返回值调用函数SELECT fullname (emp_fname, emp_lname) FROM employee;7SELECT lname, fname FROM customer; SELECT last_name, first_name FROM contact; END 5 动态的创建了临时表 RESULTSET6 和 Create Variable 不同之处在于只在 BEGIN END 中声明, 而创建的变量在整个连接中都存在,知道连接断开或者 Drop Variable 之后 7 凡是授予了 EXECUTE 权限的用户都可以使用函数删除函数DROP FUNCTION fullname授予权限GRANT EXECUTE ON fullname TO another_userREVOKE EXECUTE ON fullname FROM another_user6.6.BATCHBATCH 概要以及概要以及 BATCHBATCH 中可以使用的中可以使用的 SQLSQL 语句语句 什么是 BATCH 由一些分号分隔的 SQL 语句， 例 1：8INSERT INTO department ( dept_id, dept_name ) VALUES ( 220, Eastern Sales ) ;UPDATE employee SET dept_id = 220 WHERE dept_id = 200 AND state = MA ;COMMIT ;例 2：BEGIN IF NOT EXISTS ( SELECT * FROM SYSTABLE WHERE table_name = t1 ) THEN CREATE TABLE t1 ( firstcol INT PRIMARY KEY, secondcol CHAR( 30 ) ) ; ELSE MESSAGE Table t1 already exists ; END IF END7.7.控制语句控制语句ControlControl statementstatementSyntaxSyntax8 dbisql and batches A list of semicolon-separated statements, such as the above, is parsed by dbisql before it is sent to the server. In this case, dbisql sends each statement individually to the server, not as a batch. Unless you have such parsing code in your application, the statements are sent and treated as a batch. Putting a BEGIN and END around a set of statements causes dbisql to treat them as a batch.Compound statementsBEGIN ATOMIC statement-list ENDConditional execution: IFIF condition THEN statement-list ELSEIF condition THEN statement-list ELSE statement-list END IFConditional execution: CASECASE expression WHEN value THEN statement-list WHEN value THEN statement-list ELSE statement-list END CASERepetition: WHILE, LOOPWHILE condition LOOP statement-list END LOOPRepetition: FOR cursor loopFOR loop-name AS cursor-name CURSOR FOR select statement DO Statement-list END FORBreak: LEAVELEAVE labelCALLCALL procname( arg, . )使用复合语句 见注(1)复合语句中的声明 局部变量的声明应当紧接着 BEGIN 语句, 可以声明如下几种类型的局部变量: 变量 游标 临时表 异常(错误标识符)原子复合语句 见注(1)8.8.存储过程的结构存储过程的结构 存储过程中的可用 SQL 语句 可以使用的 SQL 语句:SELECT, UPDATE, DELETE, INSERT, and SET VARIABLEThe CALL statement to execute other proceduresControl statements (see “Control statements”)Cursor statements (see “Using cursors in procedures”)Exception handling statements (see “Using exception handlers in procedures”)The EXECUTE IMMEDIATE statement 不可以使用的 SQL 语句:CONNECT statementDISCONNECT statement存储过程声明参数类型IN OUT INOUT存储过程传参CREATE PROCEDURE SampleProc(INOUT var1 INT DEFAULT 1,INOUT var2 int DEFAULT 2,INOUT var3 int DEFAULT 3 ).CREATE VARIABLE V1 INT;CREATE VARIABLE V2 INT;CREATE VARIABLE V3 INT;v2、v3 有默认值，可省略CALL SampleProc( V1);CALL SampleProc( var1 = V1, var3 = V3 );函数传参 函数中可以设置默认参数,但是不能像存储过程那样通过变