1、SybaseIQ存储过程学习笔记实践:SYBASE IQ存储过程学习笔记1. 存储过程存储过程将过程化的SQL语句存储在数据库中，供所有程序使用。存储过程中可以包含控制语句,如LOOP循环语句、IF和CASE这样的条件语句。存储过程通过CALL语句进行调用，使用变量传入参数和返回结果。可以返回结果集，也可以调用其他的存储过程。2. 存储过程和函数的区别自定义函数是只能返回单一值的特定的存储过程。函数不修改传入的参数，但是可以使其用于查询和其他SQL语句之中。3. 存储过程的调试参见附录C Debugging Logic in the Database4. 存储过程概要 常用的存储过程 创建存储
2、过程 修改存储过程 调用存储过程 删除存储过程 存储过程的访问控制 返回值 常用的存储过程sp_iqprocedure此存储过程可以显示系统和用户自定义的存储过程sp_iqprogram显示存储过程的参数信息,包括结果集变量和SQLSTATE/SQLCODE错误值 创建存储过程 CREATE PROCEDURE new_dept (IN id INT, IN name CHAR(35), IN head_id INT )BEGIN INSERT INTO DBA.department(dept_id, dept_name, dept_head_id) VALUES(id, name, head
3、_id);END 修改存储过程使用SQL语句ALTER PROCEDURE，将整个新的存储过程包含其中。必须重新给修改后的存储过程赋予用户权限。 调用存储过程CALL new_dept(210, Eastern Sales, 902); 删除存储过程DROP PROCEDURE new_dept 存储过程的访问控制见注(2) 返回值可以通过三种方式传回值：使用OUT或者INOUT返回值；返回结果集；使用RETURN语句返回单值。 使用OUT和INOUT返回值CREATE PROCEDURE AverageSalary( OUT avgsal NUMERIC (20,3) )BEGIN SELE
4、CT AVG( salary ) INTO avgsal FROM employee;END 返回结果集CREATE PROCEDURE SalaryList (IN department_id INT)RESULT ( Employee ID INT, Salary NUMERIC(20,3) )BEGIN SELECT emp_id, salary FROM employee WHERE employee.dept_id = department_id;END如果在存储过程中一个语句动态的创建了一个临时表,然后从里面选取结果集,为了避免如“Column not found”这样的错误，必须
5、使用EXECUTE IMMEDIATE WITH RESULT SET ON这样的语法。例：CREATE PROCEDURE p1 (IN t varchar(30) BEGIN EXECUTE IMMEDIATE SELECT * INTO #resultSet FROM | t; EXECUTE IMMEDIATE WITH RESULT SET ON SELECT * FROM #resultSet;END5. 自定义函数 创建自定义函数CREATE FUNCTION fullname (firstname CHAR(30),lastname CHAR(30)RETURNS CHAR(6
6、1)BEGIN DECLARE name CHAR(61); SET name = firstname | | lastname; RETURN ( name );END和存储过程存在3个差异 参数不需要IN、OUT和INOUT，所有参数默认是IN RETURNS语句指明要返回的值 RETURN语句用来返回值 调用函数SELECT fullname (emp_fname, emp_lname) FROM employee; 删除函数DROP FUNCTION fullname 授予权限GRANT EXECUTE ON fullname TO another_userREVOKE EXECUTE
7、 ON fullname FROM another_user6. BATCH概要以及BATCH中可以使用的SQL语句 什么是BATCH由一些分号分隔的SQL语句， 例1：INSERTINTO department ( dept_id, dept_name )VALUES ( 220, Eastern Sales ) ;UPDATE employeeSET dept_id = 220WHERE dept_id = 200AND state = MA ;COMMIT ; 例2：BEGIN IF NOT EXISTS ( SELECT * FROM SYSTABLE WHERE table_name
8、 = t1 ) THEN CREATE TABLE t1 ( firstcol INT PRIMARY KEY, secondcol CHAR( 30 ) ) ; ELSE MESSAGE Table t1 already exists ; END IF END7. 控制语句Control statementSyntaxCompound statementsBEGIN ATOMIC statement-list ENDConditional execution: IFIF condition THEN statement-listELSEIF condition THEN statement-
9、listELSE statement-listEND IFConditional execution: CASECASE expression WHEN value THEN statement-listWHEN value THEN statement-list ELSE statement-list END CASERepetition: WHILE, LOOPWHILE condition LOOP statement-listEND LOOPRepetition: FOR cursor loopFOR loop-name AS cursor-name CURSOR FOR select
10、 statementDO Statement-list END FORBreak: LEAVELEAVE labelCALLCALL procname( arg, . ) 使用复合语句见注(1) 复合语句中的声明局部变量的声明应当紧接着BEGIN语句, 可以声明如下几种类型的局部变量: 变量 游标 临时表 异常(错误标识符) 原子复合语句见注(1)8. 存储过程的结构 存储过程中的可用SQL语句可以使用的SQL语句: SELECT, UPDATE, DELETE, INSERT, and SET VARIABLE The CALL statement to execute other proc
11、edures 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 statement 存储过程声明参数类型 IN OUT INOUT 存储过程传参CRE
12、ATE 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 ); 函数传参 函数中可以设置默认参数,但是不能像存储过程那样通过变量名指定的方式传入参数 参数是传的值,非
13、引用参数 OUT、INOUT不可用 无法返回结果集9. 返回值 使用RETURN一个值 使用存储过程参数返回值 SET V1 = V2 SELECT V2 INTO V1 返回单个或者返回多个结果集 在RESULT中声明的变量的数量必须匹配得上SELECT语句中的数量。如果类型不匹配，会做自动转换。 RESULT语句是CREATE PROCEDURE的一部分， 不需要分隔符 SELECT语句中的名称不需要和RESULT中的进行匹配 要让dbisql显示返回的多个结果集,必须开多个结果集返回的开关 除非是视图上面返回的结果集,是可以修改结果集的,但是需要有对底层表操作的权限 返回可变结果集RES
14、ULT语句是可选的，如果省略RESULT语句,那么就可以返回一个可变的结果集。但是，如果不需要可变结果集的话，那么就最好加上一条RESULT语句，这样可以得到更好的性能。10. 使用游标 游标概要1) 为要执行的SELECT语句声明一个游标, 或者使用DECLARE在存储过程中声明一个游标2) 使用OPEN打开游标3) 使用FETCH一次获取一个结果集4) Row Not Found的告警信号表示到达了行尾5) 使用CLOSE关闭游标游标会在事务的末尾自动关闭, 使用WITH HOLD可以阻止关闭, 直到显式关闭。存储过程sp_iqcursorinfo可以显示所有在服务器上面打开着的游标。 游
15、标的位置游标可以放在三个位置上:某行/第一行前面/最后一行后面新开一个游标时，位置处于第一行前面。使用FETCH命令，可以移动游标，如 FETCH ABSOLUTE、FETCH FIRST和FETCH LAST；或者使用FETCH RELATIVE、FETCH PRIOR和FETCH NEXT进行相对移动。NEXT是在FETCH命令中隐含的参数。在UPDATE和DELETE语句中，如果游标在首行前或者末行后，那么进行操作时会出现No current row的错误。 在SELECT语句中使用游标 废话版CREATE PROCEDURE TopCustomerValue ( OUT TopComp
16、any CHAR(36), OUT TopValue INT )BEGIN - 1. 声明一个异常常量 DECLARE err_notfound EXCEPTION FOR SQLSTATE 02000; - 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_pr
17、ice ) AS INTEGER ) AS value FROM customer INNER 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: LOOP FETCH NEXT ThisCompany INTO ThisName, ThisValue; IF SQLSTATE = err_notfou
18、nd THEN LEAVE CompanyLoop; END IF; IF ThisValue TopValue THEN SET 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 variable
19、SET 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 customer INNER JOIN sales_order INNER JOIN sales_order_items INNER JOIN product GROU
20、P BY ThisName DO IF ThisValue TopValue THEN SET TopCompany = ThisName; SET TopValue = ThisValue; END IF; END FOR CompanyLoop;END11. 存储过程中的错误和警告出现错误时, 则是更新SQLSTATE和SQLCODE,然后返回；出现警告时, 更新了上述两个变量后,继续执行。声明异常：DECLARE 变量名 EXCEPTION FOR SQLSTATE 02000抛出异常:SIGNAL 变量名异常处理:CREATE PROCEDURE InnerProc()BEGIN DE
21、CLARE column_not_found EXCEPTION FOR SQLSTATE 52003; MESSAGE Hello from InnerProc.; SIGNAL column_not_found; MESSAGE Line following SIGNAL.; EXCEPTION WHEN column_not_found THEN MESSAGE Column not found handling.; WHEN OTHERS THEN RESIGNAL;END异常处理中可用的语句:IFSELECT variable =CASELOOPLEAVECONTINUECALLEX
22、ECUTESIGNALRESIGNALDECLARESET variable12. 存储过程Tips 检查是否需要更改delimiter 每行SQL语句最好都进行delimiter 使用表的全称限定词 显式转换日期和时间 验证传入的参数13. 常用类型及函数 日期及相关函数日期类型有, DATE: YYYY-MM-DDDATETIME: YYYY-MM-DD HH:MM:SS.XXXXXXSMALLDATETIME: YYYY-MM-DD HH:MM:SS.XXXXXXTIME: HH:MM:SS.XXXXXXTIMESTAMP: YYYY-MM-DD HH:MM:SS.XXXXXX 常用的函数datepart(year/month/day, datetime)dateadd(year/month/day, number, datetime)today()now() 判断一个表是否存在if exists (select * from sysobjects where name = 变量) then -do somethingend if
