SybaseIQ存储过程学习笔记

1、实践：SQL rarSYBASE IQ存储过程学习笔记1. 存储过程存储过程将过程化的SQL语句存储在数据库中，供所有程序使用。存储过程中可以包含控制语句,如LOOP循环语句、IF和CASE这样的条件语句。 存储过程通过 CALL语句进行 调用，使用变量传入参数和返回结果。可以返回结果集，也可以调用其他的存储过程。2. 存储过程和函数的区别自定义函数是只能返回单一值的特定的存储过程。函数不修改传入的参数，但是可以使 其用于查询和其他 SQL语句之中。3. 存储过程的调试参见附录 C Debugging Logic in the Database4. 存储过程概要?常用的存储过程?创建存储过程?

2、修改存储过程?调用存储过程?删除存储过程?存储过程的访问控制?返回值常用的存储过程sp_iqprocedure此存储过程可以显示系统和用户自定义的存储过程sp_iqprogram显示存储过程的参数信息，包括结果集变量和 SQLSTATE/SQLCODE误值创建存储过程 CREATE PROCEDUREiew_dept (IN id INT, IN name CHAR(35), IN head_id INT ) BEGININSERT INTO DBA.department(dept_id, dept_name, dept_head_id)VALUES(id, name, head_id);EN

3、D修改存储过程使用SQL语句ALTER PROCEDU,将整个新的存储过程包含其中。 必须重新给修改后的存储过程赋予用户权限。调用存储过程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)BEGINSELECT AVG( s

4、alary ) INTO avgsal FROM employee;END返回结果集CREATE PROCEDURE SalaryList (IN departme nt_id INT)RESULT ( "Employee ID" INT, "Salary" NUMERIC(20,3)1复合语句，放在BEGIN和END之间;复合语句可以相互嵌套；复合语句用于将多个语句组合成一个单元，其中的SQL语句用分号分隔；除了最后一个分号，其他都是必须的；复合语句中声明的局部变量只在复合语句中可；可以在BEGIN后面加上ATOMIC将复合语句声明为原子性，此时就不可

5、使用 COMMIT、ROLLBACK和ROLLBACK TO SAVEPOINT.2 必须赋予 EXECUT取限才可以执行此存储过程，GRANT EXECUTE ON new_dept TO another_user;REVOKEEXECUTE ON new_dept FROM another_user3调用之前，可以先声明一个变量保存结果 ，语句如下CREATE VARIABLE Average NUMERIC(20,3)4存储过程还可以返回多个同类型的结果集，必须在客户端中启用返回多个结果集的支持。例：CREATE PROCEDURE ListPeople() RESULT ( lname

6、 CHAR(36), fname CHAR(36)BEGINSELECT emp_lname, emp_fnameFROM employee;SELECT lname, fnameBEGINSELECT emp_id, salaryFROM employeeWHERE employee.dept_id = departme nt_id;END,然后从里面选取结果集，EXECUTE IMMEDIATE WITH如果在存储过程中一个语句动态的创建了一个临时表 为了避免如"Column not found ”这样的错误，必须使用RESULT SET Of这样的语法。例：CREATE PRO

7、CEDURE pl (IN t varchar(30) BEGINEXECUTE IMMEDIATE5'SELECT * INTO #resultSetFROM ' | t;EXECUTE IMMEDIATE WITH RESULT SET ON'SELECT * FROM #resultSet：END5. 自定义函数创建自定义函数CREATE FUNCTION full name (first name CHAR(30), last name CHAR(30)RETURNS CHAR(61)BEGIN6DECLARE name CHAR(61);SET n ame =

8、 first name | ' ' | last name;RETURN ( n ame );END和存储过程存在 3个差异参数不需要IN、OUT和INOUT所有参数默认是INRETUR N语句指明要返回的值RETURNS句用来返回值调用函数SELECT full name (emp_fname, empn ame) FROM employee;删除函数FROM customer;SELECT last_name, first_nameFROM contact;END5动态的创建了临时表 RESULTSET6和Create Variable不同之处在于只在 BEGIN END中

9、声明，而创建的变量在整个连接中都存在，知道连接断开或者Drop Variable之后7凡是授予了 EXECUTED限的用户都可以使用函数DROP FUNCTION full name授予权限GRANT EXECUTE ON full name TO ano ther_userREVOKE EXECUTE ON full name FROM ano ther_user6. BATCH既要以及BATCH中可以使用的SQL语句什么是BATCH由一些分号分隔的 SQL语句，例1 : 8INSERTINTO departme nt ( dept_id, dept_ name )VALUES ( 220,

10、 'Eastern Sales');UPDATE employeeSET dept_id = 220WHERE dept_id = 200AND state = 'MA'COMMIT ;例2:BEGINIF NOT EXISTS (SELECT * FROM SYSTABLEWHERE table_ name = 't1' ) THEN CREATE TABLE t1 (firstcol INT PRIMARY KEY, seco ndcol CHAR( 30 )；ELSEMESSAGE 'Table t1 already exists&

11、#39; END IFEND7. 控制语句Con trol stateme ntSyn taxCompo und stateme ntsBEGIN ATOMIC 8 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

12、. 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.stateme nt-listENDCon diti onal executi on: IFIF con dition THEN stateme nt-list ELSEIF con diti on THEN

13、stateme nt-list ELSEstateme nt-listEND IFCon diti onal executi on: CASECASE expressi onWHEN value THEN stateme nt-listWHEN value THEN stateme nt-listELSEstateme nt-listEND CASERepetitio n: WHILE, LOOPWHILE con ditio n LOOP stateme nt-listEND LOOPRepetiti on: FOR cursor loopFOR loop-n ameAS curs or-n

14、 ameCURSOR FOR select stateme ntDOStateme nt-listEND FORBreak: LEAVELEAVE labelCALLCALL proc name( arg, .)使用复合语句见注(1)复合语句中的声明局部变量的声明应当紧接着BEGIN语句,可以声明如下几种类型的局部变量变量游标临时表异常(错误标识符)原子复合语句见注(1)8. 存储过程的结构存储过程中的可用 SQL语句可以使用的SQL语句：SELECT, UPDATE, DELETE, INSERT, and SET VARIABLEThe CALL statement to execute

15、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存储过程声明参数类型INOUTINOUT存储过程传参C

16、REATE 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 );函数传参函数中可以设置默认参数,但是不能像存储过程那样通过变量名指定的方式传入参数参数是传的值 , 非

17、引用参数OUT INOUT不可用无法返回结果集9. 返回值使用RETURN-个值使用存储过程参数返回值SET V1 = V2SELECT V2 INTO V1 返回单个或者返回多个结果集在RESULT中声明的变量的数量必须匹配得上SELECT语句中的数量。如果类型不匹配，会做自动转换。RESULT语句是CREATE PROCEDU的一部分，不需要分隔符SELECT语句中的名称不需要和 RESUL冲的进行匹配 要让 dbisql 显示返回的多个结果集 , 必须开多个结果集返回的开关除非是视图上面返回的结果集 , 是可以修改结果集的 , 但是需要有对底层表操作 的权限返回可变结果集RESULT语句

18、是可选的，如果省略RESULT语句,那么就可以返回一个可变的结果 集。但是，如果不需要可变结果集的话，那么就最好加上一条 RESULT语句，这样可以得到更好的性能。10. 使用游标游标概要1) 为要执行的SELECTS句声明一个游标，或者使用DECLARES存储过程中声明一 个游标2) 使用OPEN丁开游标3) 使用FETCHH次获取一个结果集4) Row Not Found 的告警信号表示到达了行尾5) 使用CLOSE关闭游标游标会在事务的末尾自动关闭，使用WITH HOLD可以阻止关闭，直到显式关闭。存储过程 sp_iqcursorinfo 可以显示所有在服务器上面打开着的游标。游标的位置

19、游标可以放在三个位置上 :某行/第一行前面 / 最后一行后面新开一个游标时，位置处于第一行前面。 使用FETCH命令，可以移动游标，如FETCH ABSOLUTE FETCH FIRST禾口 FETCH LAST 或者使用 FETCH RELATIVE FETCH PRIOR 和FETCH NEXT进行相对移动。 NEXT是在FETCH命令中隐含的参数。在UPDATED DELETE语句中，如果游标在首行前或者末行后，那么进行操作时会出现 No current row 的错误。在SELECT语句中使用游标废话版CREATE PROCEDURE TopCustomerValue(OUT TopC

20、ompa ny CHAR(36),OUT TopValue INT )BEGIN-1. 声明一个异常常量DECLARE err_n otfou ndEXCEPTION FOR SQLSTATE '02000 详见 Errors and warnings in procedures;-2.声明两个变量存储公司名和值DECLARE ThisName CHAR(36);DECLARE ThisValue INT;-3声明一个ThisCompnay的游标DECLARE ThisCompa ny CURSOR FOR SELECT compa ny_n ame,CAST( sum( sales_

21、order_items.qua ntity * product.u nit_price ) AS INTEGER )AS valueFROM customerINNER JOIN sales_orderINNER JOIN sales_order_itemsINNER JOIN productGROUP BY compa ny_n ame;-4. 初始化TopValue的值SET TopValue = 0;-5.打开游标OPEN ThisCompa ny;-6. 循环Compa nyLoop:LOOPFETCH NEXT ThisCompa nyINTO ThisName, ThisValue

22、;IF SQLSTATE = err_notfound THENLEAVE Compa nyLoop;END IF;IF ThisValue > TopValue THENSET TopCompa ny = ThisName;SET TopValue = ThisValue;END IF;END LOOP Compa nyLoop;-7. 关闭游标CLOSE ThisCompa ny;END精简版CREATE PROCEDURE TopCustomerValue2(OUT TopCompany CHAR(36),OUT TopValue INT )BEGIN- Initialize th

23、e TopValue variable SET TopValue = 0;- Do the For LoopCompanyLoop:FOR CompanyFor AS ThisCompany CURSOR FOR SELECT company_name AS ThisName ,CAST( sum( sales_order_items.quantity * product.unit_price ) AS INTEGER )AS ThisValueFROM customerINNER JOIN sales_orderINNER JOIN sales_order_items INNER JOIN

24、productGROUP BY ThisNameDOIF ThisValue > TopValue THEN SET TopCompany = ThisName; SET TopValue = ThisValue; END IF;END FOR CompanyLoop;END11. 存储过程中的错误和警告出现错误时，则是更新SQLSTAT和SQLCODE然后返回； 出现警告时 , 更新了上述两个变量后 , 继续执行。声明异常：DECLARE变量名 EXCEPTION FOR SQLSTATR2000'抛出异常 :SIGNAL 变量名异常处理 :CREATE PROCEDURE InnerProc()BEGINDECLARE column_not_foun