å建SQLåå¨è¿ç¨ï¼CREATE PROCEDURE (SQL) statement ï¼
CREATE PROCEDURE procedure-name(IN | OUT | INOUT parameter-name data-type,...) ) ---åå¨è¿ç¨å¯ä»¥è®¾å®è¾å ¥åæ°åè¾åºåæ°åæ°è¯æ³è¯´æ
1ãprocedure-name: åå¨è¿ç¨çååï¼å¨åä¸ä¸ªæ°æ®åºçåä¸æ¨¡å¼ä¸ï¼ä¸è½åå¨åå¨è¿ç¨åç¸ååæ°æ°ç®ç¸åçåå¨è¿ç¨ï¼å³ä½¿åæ°çç±»åä¸åä¹ä¸è¡ã
2ã(IN | OUT | INOUT parameter-name data-type,...) ï¼ä¼ å ¥åæ° INï¼è¾å ¥åæ°OUTï¼è¾åºåæ°INOUTï¼ä½ä¸ºè¾å ¥è¾åºåæ° parameter-nameï¼åæ°ååï¼å¨æ¤åå¨è¿ç¨ä¸å¯ä¸çæ è¯ç¬¦ãdata-type:åæ°ç±»åï¼å¯ä»¥æ¥æ¶SQLç±»ååå建ç表ãä¸æ¯æLONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCEåç¨æ·èªå®ä¹ç±»åã
3ãSPECIFIC specific-nameï¼å¯ä¸çç¹å®å称ï¼å«åï¼ï¼å¯ä»¥ç¨åå¨è¿ç¨å代æ¿ï¼è¿ä¸ªç¹å®å称ç¨äºdorpåå¨è¿ç¨ï¼æè ç»åå¨è¿ç¨æ·»å 注è§ç¨ï¼ä½ä¸è½è°ç¨åå¨è¿ç¨ãå¦æä¸æå®ï¼åæ°æ®åºä¼èªå¨çæä¸ä¸ªyymmddhhmmsshhnæ¶é´æ³çååãæ¨èç»åºå«åã
4ãDYNAMIC RESULT SETS integerï¼æå®åå¨è¿ç¨è¿åç»æçæ大æ°éãåå¨è¿ç¨ä¸è½ç¶æ²¡æreturnè¯å¥ï¼ä½æ¯å´è½è¿åç»æéã
5ãCONTAINS SQL, READS SQL DATA, MODIFIES SQL DATAï¼ æå®åå¨è¿ç¨ä¸çSQL访é®çº§å« CONTAINS SQLï¼ è¡¨ç¤ºåå¨è¿ç¨å¯ä»¥æ§è¡ä¸ï¼æ¢ä¸å¯è¯»å SQL æ°æ®ï¼ä¹ä¸å¯ä¿®æ¹ SQL æ°æ®ã READS SQL DATAï¼ è¡¨ç¤ºåå¨è¿ç¨å¯ä»¥æ§è¡ä¸ï¼å¯è¯»åSQLï¼ä½ä¸å¯ä¿®æ¹ SQL æ°æ®ã MODIFIES SQL DATAï¼ è¡¨ç¤ºåå¨è¿ç¨å¯ä»¥æ§è¡ä»»ä½ SQL è¯å¥ãå¯ä»¥å¯¹æ°æ®åºä¸çæ°æ®è¿è¡å¢å ãå é¤åä¿®æ¹ã
6ãDETERMINISTIC or NOT DETERMINISTICï¼è¡¨ç¤ºåå¨è¿ç¨æ¯å¨ææè éå¨æçãå¨æçè¿åçå¼æ¯ä¸ç¡®å®çãéå¨æçåå¨è¿ç¨æ¯æ¬¡æ§è¡è¿åçå¼æ¯ç¸åçã
7ãCALLED ON NULL INPUTï¼è¡¨ç¤ºå¯ä»¥è°ç¨åå¨è¿ç¨èä¸ç®¡ä»»ä½çè¾å ¥åæ°æ¯å¦ä¸ºNULLï¼å¹¶ä¸ï¼ä»»ä½çOUTæè INOUTåæ°å¯ä»¥è¿åä¸ä¸ªNULLæè é空å¼ãæ£éªåæ°æ¯å¦ä¸ºNULLæ¯å¨è¿ç¨ä¸è¿è¡çã
8ãINHERIT SPECIAL REGISTERSï¼è¡¨ç¤ºç»§æ¿ä¸ç¨å¯åå¨ã
9ãOLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVELï¼å»ºç«åå¨ç¹ãOLD SAVEPOINT LEVELæ¯é»è®¤çåå¨ç¹ã
10ãLANGUAGE SQLï¼æå®ç¨åºç主ä½ç¨çæ¯SQLè¯è¨ã
11ãEXTERNAL ACTION or NO EXTERNAL ACTIONï¼è¡¨ç¤ºåå¨è¿ç¨æ¯å¦æ§è¡ä¸äºæ¹åçæ°æ®åºç¶æçæ´»å¨ï¼èä¸éè¿æ°æ®åºç®¡çå¨ç®¡ãé»è®¤æ¯ EXTERNAL ACTIONãå¦ææå®ä¸ºNO EXTERNAL ACTION ,åæ°æ®åºä¼ç¡®å®ææä½³ä¼åæ¹æ¡ã
12ãPARAMETER CCSIDï¼æå®ææè¾åºå符串æ°æ®çç¼ç ,é»è®¤ä¸ºUNICODEç¼ç æ°æ®åºä¸ºPARAMETER CCSID UNICODE ï¼å ¶ä»çæ°æ®åºé»è®¤ä¸ºPARAMETER CCSID 3 ASCIIã
13ãSQL-procedure-bodyï¼åå¨è¿ç¨ç主ä½
上面的是我常用的一个存储过程的代码样本
异常处理需要加在游标声明之后,临时表声明之前(见DECLARE UNDO HANDLER FOR SQLEXCEPTION部分)
常用的异常处理方式分为三种:UNDO(回滚)、EXIT(退出)、CONTINUE(继续执行)
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR异常处理的对象可以为针对指定的sqlstate代码
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR SQLSTATE XXXX也可以针对某些condition,如sql异常,sql警告,以及游标末尾
异常处理的内容 ,可以是一些回滚,提交语句,也可以是日志记录等
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR SQLEXCEPTION