SybaseIQ存儲過程學(xué)習(xí)筆記

1、實踐:警SQL rarSYBASE IQ存儲過程學(xué)習(xí)筆記1.存儲過程存儲過程將過程化的 SQL語句存儲在數(shù)據(jù)庫中，供所有程序使用。存儲過程中可以包含控制語句,如LOOP循環(huán)語句、IF和CASE這樣的條件語句。 存儲過程通過 CALL語句進(jìn)行 調(diào)用，使用變量傳入?yún)?shù)和返回結(jié)果。可以返回結(jié)果集，也可以調(diào)用其他的存儲過程。2.存儲過程和函數(shù)的區(qū)別自定義函數(shù)是只能返回單一值的特定的存儲過程。函數(shù)不修改傳入的參數(shù)，但是可以使 其用于查詢和其他 SQL語句之中。3.存儲過程的調(diào)試參見附錄 C Debugging Logic in the Database4.存儲過程概要? 常用的存儲過程? 創(chuàng)建存儲過程?

2、 修改存儲過程? 調(diào)用存儲過程? 刪除存儲過程? 存儲過程的訪問控制? 返回值常用的存儲過程sp_iqp rocedure此存儲過程可以顯示系統(tǒng)和用戶自定義的存儲過程sp_iqp rogram顯示存儲過程的參數(shù)信息，包括結(jié)果集變量和 SQLSTATE/SQLCODE誤值創(chuàng)建存儲過程CREATE PROCEDUREiew_dept (IN id INT,beginIN name CHAR(35), IN head_id INT )INSERT INTO DBA.de partment(dep t_id, dep t_name, dep t_head_id)VALUES (id, name, he

3、ad_id);END修改存儲過程使用SQL語句ALTER PROCEDU,將整個新的存儲過程包含其中。 必須重新給修改后的存儲過程賦予用戶權(quán)限。調(diào)用存儲過程CALL new_dept(210, Eastern Sales ' , 902);刪除存儲過程DROP P ROCEDURE new_de pt存儲過程的訪問控制見注(2) 返回值可以通過三種方式傳回值：使用OUT或者INOUT返回值；返回結(jié)果集；使用 RETURN語句返回單值。使用OUT和INOUT返回值復(fù)合語句，放在BEGIN和END之間;復(fù)合語句可以相互嵌套；復(fù)合語句用于將多個語句組合成一個單元，其中的SQL語句用分號分隔；

4、除了最后一個分號，其他都是必須的；復(fù)合語句中聲明的局部變量只在復(fù)合語句中可；可以在BEGIN后面加上ATOMIC將復(fù)合語句聲明為原子性，此時就不可使用 COMMIT、ROLLBACK和ROLLBACK TO SAVE PO INT. 必須賦予 EXECUT取限才可以執(zhí)行此存儲過程，GRANT EXECUTE ON new_dept TO another_user;REVOKEEXECUTE ON new_de pt FROM another_user 調(diào)用之前，可以先聲明一個變量保存結(jié)果 ，語句如下CREATE VARIABLE Average NUMERIC(20,3) 存儲過程還可以返回多

5、個同類型的結(jié)果集，必須在客戶端中啟用返回多個結(jié)果集的支持。例：CREATE P ROCEDURE List Peo ple() RESULT ( lname CHAR(36), fname CHAR(36)BEGINSELECT emp_lname, emp_fnameFROM empi oyee;SELECT lname, fnameCREATE P ROCEDURE AverageSalary( OUT avgsal NUMERIC (20,3)BEGINSELECT AVG( salary ) INTO avgsal FROM emp loyee;END返回結(jié)果集CREATE P ROC

6、EDURE SalaryList (IN dep artme nt_id INT)RESULT ( "Em ployee ID" INT, "Salary" NUMERIC(20,3)BEGINSELECT emp_id, salaryFROM emp loyeeWHERE empio yee.de pt_id = dep artme nt_id;END,然后從里面選取結(jié)果集，EXECUTE IMMEDIATE WITH如果在存儲過程中一個語句動態(tài)的創(chuàng)建了一個臨時表 為了避免如"Column not found ”這樣的錯誤，必須使用RESUL

7、T SET Of這樣的語法。例：CREATE P ROCEDURE p1 (IN t varchar(30) BEGIN5 FROM 'll t; EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT * FROM #resultSet'ENDEXECUTE IMMEDIATE'SELECT * INTO #resultSet5.自定義函數(shù)創(chuàng)建自定義函數(shù)CREATE FUNCTION full name (first name CHAR(30), last name CHAR(30)RETURNS CHAR(61)BEGINa

8、DECLARE n ame CHAR(61);SET n ame = first name | ' ' | last name;RETURN ( n ame );END和存儲過程存在 3個差異參數(shù)不需要IN、OUT和INOUT所有參數(shù)默認(rèn)是IN RETUR N語句指明要返回的值RETURNS句用來返回值調(diào)用函數(shù)SELECT full name (emp _fname, emp_ln ame) FROM empio yee;刪除函數(shù)FROM customer;SELECT last_name, first_nameFROM contact;END5動態(tài)的創(chuàng)建了臨時表 RESULT

9、SET6和Create Variable不同之處在于只在 BEGIN END中聲明，而創(chuàng)建的變量在整個連接中都存在，知道連接斷開或者Drop Variable之后7凡是授予了 EXECUTED限的用戶都可以使用函數(shù)DROP FUNCTION full name授予權(quán)限GRANT EXECUTE ON full name TO ano ther_userREVOKE EXECUTE ON full name FROM ano ther user6.BATCH概要以及BATCH中可以使用的SQL語句 什么是BATCH由一些分號分隔的 SQL語句，例1 : 8INSERTINTO dep artme

10、 nt ( dep t_id, dept_n ame )VALUES ( 220, 'Eastern Sales');UP DATE emp loyeeSET de pt_id = 220WHERE de pt_id = 200AND state = 'MA'COMMIT ;例2:BEGINIF NOT EXISTS (SELECT * FROM SYSTABLEWHERE table_ name = 't1' ) THEN CREATE TABLE t1 (firstcol INT P RIMARY KEY, seco ndcol CHAR(

11、30 );ELSEMESSAGE 'Table t1 already exists'END IF7.ENDCon trol stateme ntSyn taxCompound 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

12、 individually to the server, not as a batch. Unless you have such p arsing code in your app lication, 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

13、THEN stateme nt-list ELSEIF con diti on THEN stateme nt-list ELSEstateme nt-listEND IFCon diti onal executi on: CASECASE exp ressi onWHEN value THEN stateme nt-listWHEN value THEN stateme nt-listELSEstateme nt-listEND CASERep etitio n: WHILE, LO OPWHILE con ditio n LOOP stateme nt-listEND LOOPRep et

14、iti on: FOR cursor loopFOR loop-n ameAS curs or-n ameCURSOR FOR select stateme ntDOStateme nt-listEND FORBreak: LEAVELEAVE labelCALLCALL procn ame( arg, .)使用復(fù)合語句見注(1)復(fù)合語句中的聲明局部變量的聲明應(yīng)當(dāng)緊接著BEGIN語句,可以聲明如下幾種類型的局部變量變量游標(biāo)臨時表異常(錯誤標(biāo)識符)原子復(fù)合語句見注(1)8.存儲過程的結(jié)構(gòu)存儲過程中的可用 SQL語句 可以使用的SQL語句：SELECT, UPDATE, DELETE, INSER

15、T, and SET VARIABLEThe CALL statement to execute other proceduresControl statements” )Control statements (see “Using cursors in procedures”)Using exception handlers in proceduresCursor statements (seeException handling statements (see”)The EXECUTE IMMEDIATE statement不可以使用的SQL語句：CONNECT statementDISC

16、ONNECT statement存儲過程聲明參數(shù)類型INOUTINOUT存儲過程傳參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 有默認(rèn)值，可省略CALL SampleProc( V1);CALL SampleProc( var1 = V1, var3 = V3 );函數(shù)傳參函數(shù)中

17、可以設(shè)置默認(rèn)參數(shù) ,但是不能像存儲過程那樣通過變量名指定的方式傳 入?yún)?shù)參數(shù)是傳的值 , 非引用參數(shù)OUT INOUT不可用無法返回結(jié)果集9. 返回值使用RETURN一個值使用存儲過程參數(shù)返回值 SET V1 = V2SELECT V2 INTO V1返回單個或者返回多個結(jié)果集在RESULT中聲明的變量的數(shù)量必須匹配得上SELECT語句中的數(shù)量。如果類型不匹配，會做自動轉(zhuǎn)換。RESULT語句是CREATE PROCEDUR一部分，不需要分隔符SELECT語句中的名稱不需要和 RESUL沖的進(jìn)行匹配 要讓 dbisql 顯示返回的多個結(jié)果集 ，必須開多個結(jié)果集返回的開關(guān)除非是視圖上面返回的結(jié)果集

18、 ,是可以修改結(jié)果集的 ,但是需要有對底層表操作 的權(quán)限返回可變結(jié)果集RESULT語句是可選的，如果省略RESUL語句,那么就可以返回一個可變的結(jié)果 集。但是，如果不需要可變結(jié)果集的話，那么就最好加上一條 RESULT句，這樣可10. 使用游標(biāo)游標(biāo)概要1)以得到更好的性能。為要執(zhí)行的SELECTS句聲明一個游標(biāo)，或者使用DECLAR在存儲過程中聲明一 個游標(biāo)2)3)4)5)使用OPEN丁開游標(biāo)使用FETCH-次獲取一個結(jié)果集Row Not Found 的告警信號表示到達(dá)了行尾使用CLOSE關(guān)閉游標(biāo)游標(biāo)會在事務(wù)的末尾自動關(guān)閉，使用WITH HOLD可以阻止關(guān)閉，直到顯式關(guān)閉。存儲過程 sp_iq

19、cursorinfo 可以顯示所有在服務(wù)器上面打開著的游標(biāo)。游標(biāo)的位置游標(biāo)可以放在三個位置上 :某行/第一行前面 / 最后一行后面 新開一個游標(biāo)時，位置處于第一行前面。 使用FETCH命令，可以移動游標(biāo)，如FETCH ABSOLUTE FETCH FIRST和 FETCH LAST 或者使用 FETCH RELATIVE FETCH PRIOR 和FETCH NEXT進(jìn)行相對移動。 NEXT是在FETCH命令中隱含的參數(shù)。在UP DAT岳口 DELETE語句中，如果游標(biāo)在首行前或者末行后，那么進(jìn)行操作時會出 現(xiàn) No current row 的錯誤。在SELECT語句中使用游標(biāo)廢話版CREAT

20、E PROCEDURE TopCustomerValueOUT TopCompany CHAR(36),OUT Top Value INT )BEGIN聲明一個異常常量-1.DECLARE err_n otfou ndEXCE PTION FOR SQLSTATE '02000 詳見 Errors and warnings in procedures;-2.聲明兩個變量存儲公司名和值DECLARE ThisName CHAR(36);DECLARE ThisValue INT;-3聲明一個ThisCompnay的游標(biāo)DECLARE ThisCo mpany CURSOR FOR SEL

21、ECT companyame,CAST( sum( sales_order_items.qua ntity * p roduct.u nit_price ) AS INTEGER ) AS valueFROM customerINNER JOIN sales_orderINNER JOIN sales_order_itemsINNER JOIN p roductGRO UP BY companyame;-4. 初始化Top Value的值SET Top Value = 0;-5.打開游標(biāo)OPEN ThisCo mpany;-6. 循環(huán)Compan yLo op:LOOPFETCH NEXT T

22、hisCo mpanyINTO ThisName, ThisValue;IF SQLSTATE = err_notfound THENLEAVE Compan yLo op;END IF;IF ThisValue > Top Value THENSET TopCompany = ThisName;SET To pValue = ThisValue;END IF;END LOOP Com pan yLoo p;-7. 關(guān)閉游標(biāo)CLOSE ThisCo mpany;END精簡版CREATE P ROCEDURE Top CustomerValue2(OUT TopCompany CHAR(3

23、6)，OUT TopValue INT )BEGIN- Initialize the TopValue variable SET TopValue = O;- Do the For Loop CompanyLoop:FOR CompanyFor AS ThisCompany CURSOR FORSELECT company_name AS ThisName ， CAST( sum( sales_order_items.quantity * product.unit_price ) AS INTEGER ) AS ThisValueFROM customerINNER JOIN sales_or

24、derINNER JOIN sales_order_itemsINNER JOIN productGROUP BY ThisNameDOIF ThisValue > TopValue THEN SET TopCompany = ThisName; SET TopValue = ThisValue; END IF;END FOR CompanyLoop;END11. 存儲過程中的錯誤和警告出現(xiàn)錯誤時，則是更新SQLSTAT和SQLCODE然后返回;出現(xiàn)警告時 , 更新了上述兩個變量后 , 繼續(xù)執(zhí)行。聲明異常：DECLARE變量名 EXCE PTION FOR SQLSTATE)2OOO'拋出異常 :SIGNAL 變量名異常處理 :CREATE PROCEDURE InnerProc()BEGINDECLARE column_not_found