SybaseIQ存储过程学习笔记.docx

实践:SYBASE IQ存储过程学习笔记1. 存储过程存储过程将过程化的SQL语句存储在数据库中，供所有程序使用。存储过程中可以包含控制语句,如LOOP循环语句、IF和CASE这样的条件语句。存储过程通过CALL语句进行调用，使用变量传入参数和返回结果。可以返回结果集，也可以调用其他的存储过程。2. 存储过程和函数的区别自定义函数是只能返回单一值的特定的存储过程。函数不修改传入的参数，但是可以使其用于查询和其他SQL语句之中。3. 存储过程的调试参见附录C Debugging Logic in the Database4. 存储过程概要 常用的存储过程 创建存储过程 修改存储过程 调用存储过程 删除存储过程 存储过程的访问控制 返回值h 常用的存储过程sp_iqprocedure此存储过程可以显示系统和用户自定义的存储过程sp_iqprogram显示存储过程的参数信息,包括结果集变量和SQLSTATE/SQLCODE错误值h 创建存储过程CREATE PROCEDUREnew_dept(IN id INT,IN name CHAR(35),IN head_id INT )BEGIN 复合语句,放在BEGIN和END之间;复合语句可以相互嵌套;复合语句用于将多个语句组合成一个单元,其中的SQL语句用分号分隔;除了最后一个分号,其他都是必须的;复合语句中声明的局部变量只在复合语句中可;可以在BEGIN后面加上ATOMIC,将复合语句声明为原子性,此时就不可使用COMMIT、ROLLBACK和ROLLBACK TO SAVEPOINT.INSERT INTO DBA.department(dept_id, dept_name, dept_head_id)VALUES(id, name, head_id);ENDh 修改存储过程使用SQL语句ALTER PROCEDURE，将整个新的存储过程包含其中。必须重新给修改后的存储过程赋予用户权限。h 调用存储过程CALL new_dept(210, Eastern Sales, 902); 必须赋予EXECUTE权限才可以执行此存储过程， GRANT EXECUTE ON new_dept TO another_user;REVOKE EXECUTE ON new_dept FROM another_userh 删除存储过程DROP PROCEDURE new_depth 存储过程的访问控制见注(2)h 返回值可以通过三种方式传回值：使用OUT或者INOUT返回值；返回结果集；使用RETURN语句返回单值。 使用OUT和INOUT返回值 调用之前,可以先声明一个变量保存结果,语句如下CREATE VARIABLE Average NUMERIC(20,3)CREATE PROCEDURE AverageSalary( OUT avgsal NUMERIC (20,3) )BEGIN SELECT AVG( salary ) INTO avgsal FROM employee;END 返回结果集CREATE PROCEDURE SalaryList (IN department_id INT)RESULT ( Employee ID INT, Salary NUMERIC(20,3) ) 存储过程还可以返回多个同类型的结果集，必须在客户端中启用返回多个结果集的支持。例:CREATE PROCEDURE ListPeople() RESULT ( lname CHAR(36), fname CHAR(36) )BEGINSELECT emp_lname, emp_fname FROM employee;SELECT lname, fname FROM customer;SELECT last_name, first_name FROM contact;ENDBEGIN SELECT emp_id, salary FROM employee WHERE employee.dept_id = department_id;END如果在存储过程中一个语句动态的创建了一个临时表,然后从里面选取结果集,为了避免如“Column not found”这样的错误，必须使用EXECUTE IMMEDIATE WITH RESULT SET ON这样的语法。例：CREATE PROCEDURE p1 (IN t varchar(30) BEGIN EXECUTE IMMEDIATE SELECT * INTO #resultSet 动态的创建了临时表RESULTSET FROM | t; EXECUTE IMMEDIATE WITH RESULT SET ON SELECT * FROM #resultSet;END5. 自定义函数h 创建自定义函数CREATE FUNCTION fullname (firstname CHAR(30),lastname CHAR(30)RETURNS CHAR(61)BEGIN DECLARE name CHAR(61); 和Create Variable不同之处在于只在BEGIN END中声明, 而创建的变量在整个连接中都存在,知道连接断开或者Drop Variable之后 SET name = firstname | | lastname; RETURN ( name );END和存储过程存在3个差异h 参数不需要IN、OUT和INOUT，所有参数默认是INh RETURNS语句指明要返回的值h RETURN语句用来返回值h 调用函数SELECT fullname (emp_fname, emp_lname) FROM employee; 凡是授予了EXECUTE权限的用户都可以使用函数h 删除函数DROP FUNCTION fullnameh 授予权限GRANT EXECUTE ON fullname TO another_userREVOKE EXECUTE ON fullname FROM another_user6. BATCH概要以及BATCH中可以使用的SQL语句h 什么是BATCH由一些分号分隔的SQL语句， 例1： 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.INSERTINTO department ( dept_id, dept_name )VALUES ( 220, Eastern Sales ) ;UPDATE employeeSET dept_id = 220WHERE dept_id = 200AND state = MA ;COMMIT ; 例2：BEGINIF NOT EXISTS (SELECT * FROM SYSTABLEWHERE table_name = t1 ) THENCREATE TABLE t1 (firstcol INT PRIMARY KEY,secondcol CHAR( 30 ) ;ELSEMESSAGE Table t1 already exists ;END IF END7. 控制语句Control statementSyntaxCompound statementsBEGIN ATOMIC statement-list ENDConditional execution: IFIF condition THENstatement-listELSEIF condition THENstatement-listELSEstatement-listEND IFConditional execution: CASECASE expression WHEN value THENstatement-listWHEN value THEN statement-list ELSE statement-list END CASERepetition: WHILE, LOOPWHILE condition LOOP statement-listEND LOOPRepetition: FOR cursor loopFOR loop-nameAS cursor-nameCURSOR FOR select statementDOStatement-list END FORBreak: LEAVELEAVE labelCALLCALL procname( arg, . )h 使用复合语句见注(1)h 复合语句中的声明局部变量的声明应当紧接着BEGIN语句, 可以声明如下几种类型的局部变量: 变量 游标 临时表 异常(错误标识符)h 原子复合语句见注(1)8. 存储过程的结构h 存储过程中的可用SQL语句可以使用的SQL语句: SELECT, UPDATE, DELETE, INSERT, and SET VARIABLE The CALL statement to execute other procedures Control 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 statement DISCONNECT statementh 存储过程声明参数类型 IN OUT INOUTh 存储过程传参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 );h 函数传参 函数中可以设置默认参数,但是不能像存储过程那样通过变量名指定的方式传入参数 参数是传的值,非引用参数 OUT、INOUT不可用 无法返回结果集9. 返回值h 使用RETURN一个值h 使用存储过程参数返回值 SET V1 = V2 SELECT V2 INTO V1h 返回单个或者返回多个结果集 在RESULT中声明的变量的数量必须匹配得上SELECT语句中的数量。如果类型不匹配，会做自动转换。 RESULT语句是CREATE PROCEDURE的一部分， 不需要分隔符 SELECT语句中的名称不需要和RESULT中的进行匹配 要让dbisql显示返回的多个结果集,必须开多个结果集返回的开关 除非是视图上面返回的结果集,是可以修改结果集的,但是需要有对底层表操作的权限h 返回可变结果集RESULT语句是可选的，如果省略RESULT语句,那么就可以返回一个可变的结果集。但是，如果不需要可变结果集的话，那么就最好加上一条RESULT语句，这样可以得到更好的性能。10. 使用游标h 游标概要1) 为要执行的SELECT语句声明一个游标, 或者使用DECLARE在存储过程中声明一个游标2) 使用OPEN打开游标3) 使用FETCH一次获取一个结果集4) Row Not Found的告警信号表示到达了行尾5) 使用CLOSE关闭游标游标会在事务的末尾自动关闭, 使用WITH HOLD可以阻止关闭, 直到显式关闭。存储过程sp_iqcursorinfo可以显示所有在服务器上面打开着的游标。h 游标的位置游标可以放在三个位置上:某行/第一行前面/最后一行后面新开一个游标时，位置处于第一行前面。使用FETCH命令，可以移动游标，如 FETCH ABSOLUTE、FETCH FIRST和FETCH LAST；或者使用FETCH RELATIVE、FETCH PRIOR和FETCH NEXT进行相对移动。NEXT是在FETCH命令中隐含的参数。在UPDATE和DELETE语句中，如果游标在首行前或者末行后，那么进行操作时会出现No current row的错误。h 在SELECT语句中使用游标 废话版CREATE PROCEDURE TopCustomerValue (OUT TopCompany CHAR(36),OUT TopValue INT )BEGIN- 1. 声明一个异常常量DECLARE err_notfound EXCEPTION FOR SQLSTATE 02000 详见Errors and warnings in procedures;- 2.声明两个变量存储公司名和值DECLARE ThisName CHAR(36);DECLARE ThisValue INT;- 3声明一个ThisCompnay的游标DECLARE ThisCompany CURSOR FOR SELECT company_name, CAST( sum( sales_order_items.quantity * product.unit_price ) AS INTEGER ) AS valueFROM customerINNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY company_name;- 4. 初始化TopValue的值SET TopValue = 0;- 5. 打开游标OPEN ThisCompany;- 6. 循环CompanyLoop:LOOPFETCH NEXT ThisCompanyINTO ThisName, ThisValue;IF SQLSTATE = err_notfound THENLEAVE CompanyLoop;END IF;IF ThisValue TopValue THENSET TopCompany = ThisName;SET TopValue = ThisValue;END IF;END LOOP CompanyLoop;- 7. 关闭游标CLOSE ThisCompany;END 精简版CREATE PROCEDURE TopCustomerValue2(OUT TopCompany CHAR(36),OUT TopValue INT )BEGIN- Initialize the TopValue variableSET TopValue = 0;- Do the For Loop CompanyLoop:FOR CompanyFor AS ThisCompany CURSOR FOR SELECT company_name AS ThisName , CAST( sum( sales_order_items.quantity * product.unit_price ) AS INTEGER )AS ThisValue FROM customerINNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROUP BY ThisName DO IF ThisValue TopValue THENSET TopCompany = ThisName;SET TopValue = ThisValue;END IF;END FOR CompanyLoop;END11. 存储过程中的错误和警告出现错误时, 则是更新SQLSTATE和SQLCODE,然后返回；出现警告时, 更新了上述两个变量后,继续执行。声明异常：DECLARE 变量名 EXCEPTION FOR SQLSTATE 02000抛出异常:SIGNAL 变量名异常处理:CREATE PROCEDURE InnerProc()BEG