db2创建最简单存储过程 db2创建存储过程语法

转载

编程艺术家 2024-02-27 09:31:08

文章标签 db2创建最简单存储过程 sql SQL 存储过程 文章分类 数据仓库大数据

db2 SQL语法和语句块大全
　以下的文章主要向大家描述的是DB2 sql 存储过程基础，存储过程就是stored 简称为procedure。要学这个我们必须要先得弄明白另外一个概念：routine，这个一般翻译成“例程” 。

　　routine:存在server端，按应用程序逻辑编写的，可以通过client或者其他routine调用的数据库对象.

　　3种类型：stored procedures,UDFs(自定义function),methods.

　　stored procedures:作为客户端的扩展但是运行在服务端;UDFs:扩展并且自定义SQL;methods:提供结构化类型的行为

　　2种形式：

　　1)sql routines:完全用sql编写，通过create statement来注册routine.

　　2)external routines:用C,C++,Java,OLE编写，stored procedure还可用cobol编写。任何语言编写的都可以包含sql。

　　不同形式的routines可以互相调用，不管是什么语言编写的。

　　再来看看stored procedure.

　　stored procedures:可以通过call statement被client或者其他routine调用;stored procedures 和它的调用程序通过create procedure statement中的参数交换数据;stored procedures还能给它的调用者返回result sets.

　　stored procedures的优点：

　　1) 多个sql statement被调用者一次调用就能全部执行，这能减少client和server间的数据传输。

　　2)将数据库逻辑与应用程序逻辑隔离开

　　3)能返回多个result sets

　　4)如果被应用程序调用，运行起来stored procedure就像应用程序的一部分

　　缺点：

　　1)不能被sql statement调用，除了用call

　　2)返回的结果集不能直接被sql statement使用

　　3)多次调用之间不能保存调用的状态，即调用之间是独立的，无法传递信息。

　　一般的应用之处：

　　1)提供一个interface给一组sql statements。比如同时对多个表的insert操作

　　2)标准化应用程序逻辑(不理解，就是把db logic与app logic隔离吗？)

　　开发特性：

　　明白了这些基本概念后再来看看开发的特性。根据以上得知开发routine的语言有很多，这篇只讲sql procedure(即sql/sql pl写的procedure)。

　　各种语言的特性

　　sql:

　　1)效率高于java routine,基本上与c/c++ routine相当

　　2)完全用sql编写，能很快就能执行(making them quick to implement)

　　3)DB2认为sql routine是’safe’的因为全是sql，正因如此sql routine能直接在db engine上运行，并且有很好的运行效率和应用范围(good performance and scalability)

　　stored procedure feathures: parameter modes:

　　3种类型的参数：

　　1)IN :传入数据到stored procedure

　　2)OUT: stored procedure 返回数据

　　3)INOUT: 传入的那部分数据，在执行过程中被返回数据覆盖

　　result sets:

　　stored procedure通过cursor来传递结果集给调用者。DB2 sql 存储过程必须为每一个需要返回的结果集保留一个游标。

　　使用with return to caller/client来指定结果集返回的对象。指定为client将使得中间调用的routine不能获得结果集，只有client才能获得。

　　使用dynamic result sets 语句来指定返回结果集的数目，这个数目保存在syscat.routines视图的result_sets字段。如果实际返回的结果集数目大于声明的这个数目，将发出一个warning(sqlcode +464,sqlstate 0100E)

　　sql stored procedure返回结果集的操作步骤：

　　1)declare cursor:

　　如：

　　declare clientcur cursor with return to caller for select * from staff;

　　2)open the cursor:如 open clientcur;

　　3)不关闭游标退出stored procedure

　　开发：

　　最后终于来到了真正的开发了，刚才讲到sql procedure是由sql,sql pl写的，sql就没什么好说的了。关键说说sql pl (procedural language)

　　功能:控制逻辑流向，声明和设置变量，处理警告和异常。可用于例程(routine),触发器，动态复合语句(单个调用中的sql语句块)

　　控制语句：declare,set,for,get diagnostics,if,iterate,leave,return,signal,while

　　sql pl不能执行的sql：table,index,view的create和drop

　　begin atomic 开头，end 结尾

　　declare :定义变量和定义出错处理

　　declare sql-var-name data-type default default-values declare condition-name condition for sqlstate value…

　　这里的condition一般做“异常”解释

　　set:声明变量和给触发器定义中的表中的列赋值

set pay = select salary from employee where empno = 5;//仅返回一个值
　　set pay = null;//空值
　　set pay = default;//变量定义的默认值
　　//专用寄存器的内容
　　set useriduserid = userid; set today = current date;
　　//同时给多个变量赋值
　　set pay =10000,bonus = 1500; set (pay,bonus) = (10000,1500); set (pay,bonus) = select (pay,bonus) from employee where empno = 5; >>if/then/else

　　三种形式：

　　1) if then/end if 语句块

　　2) if then/else/end if

　　3) if then/elseif /else/end if

　　可以在if/then/else 语句中使用sql运算符，如：

if (salary between 10000 and 90000) then… if (deptno in (‘a00’,’b01’)) then.. if (exist (select * from employee)) then… if (select count(*) from employee)>0) then.. >>while label: while condition do …sql pl .. end while lable;
　　label可选
　　>>for:用于循环select返回结果集的行

　　格式：

label: for row_label as select satement do ..sql pl..
　　end for label;//label可选

　　例子：

for emp as select * from employee where bonus >1000 do set total_bonustotal_bonus = total_bonus +emp.bonus; end for;
　　>>iterate:用来回到for或者while循环的开始重新执行
　　check_bonus: for emp as select * from employee do if(emp.bonus>10000) then set total_bonustotal_bonus = total_bonus +emp.bonus; else iterate check_bonus; end if; end for check_bonus;
　　>>leave:相当于java中的break，需要一个label
　　>>signal:对出现异常的应用程序报警
　　signal sqlstate value set message_text = ‘…’;//自定义一个sqlstate,7、8、9和I~Z开头的sqlstate
　　signal condition set message_text = ‘…’;//自定义异常condition
　　>>get diagnostics:用在sql pl触发器或语句块(不是函数)内，返回update,insert,delete语句影响的记录数。
　　get diagnostics variable = row_count;

　　以上的相关内容就是对DB2 sql 存储过程基础的详细解析的介绍，望你能有所收获。

DB2循环语句怎么写31．Db2的循环控制语句loop用法?
答：loop例子：

OPEN c1; 
 SET at_end = 0; 
 SET numrec = 0; 
 fetch_loop: 1 
 LOOP 
 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 
 IF SQLCODE = 0 THEN 
 SET proc_cuscrd = proc_cuscrd * 1.2; 
 UPDATE ordapplib.customer 
 SET cuscrd = proc_cuscrd 
 WHERE CURRENT OF c1; 
 SET numrec = numrec + 1; 
 ELSE 
 LEAVE fetch_loop; 2 
 END IF; 
 END LOOP fetch_loop; 3 
 CLOSE c1;

32．Db2的循环控制语句while用法?
答：while 例子：

OPEN c1; 
 SET at_end = 0; 
 SET numrec = 0; 
 WHILE at_end = 0 DO 
 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 
 IF SQLCODE = 0 THEN 
 SET proc_cuscrd = proc_cuscrd * 1.2; 
 UPDATE ordapplib.customer 
 SET cuscrd = proc_cuscrd 
 WHERE CURRENT OF c1; 
 SET numrec = numrec + 1; 
 ELSE 
 SET at_end = 1; 
 END IF; 
 END WHILE; 
 CLOSE c1; 
 myfriend2010 (2007-8-30 12:54:02)

33．Db2的循环控制语句repeat用法？
答：repeat例子

SET numrec = 0; 
 fetch_loop: 
 REPEAT 
 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 
 IF SQLCODE = 0 THEN 
 SET proc_cuscrd = proc_cuscrd * 1.2; 
 UPDATE ordapplib.customer 
 SET cuscrd = proc_cuscrd 
 WHERE CURRENT OF c1; 
 SET numrec = numrec + 1; 
 END IF; 
 UNTIL SQLCODE <> 0 
 END REPEAT fetch_loop;

34．Db2的循环控制语句for用法？
答：for 例子

FOR each_record AS 
 cursor1 CURSOR FOR 
 SELECT cusnbr, cuscrd FROM ordapplib.customer 
 DO 
 UPDATE ordapplib.customer 
 SET cuscrd = cuscrd * 1.1 
 WHERE CURRENT OF cursor1; 
 END FOR; 
 myfriend2010 (2007-8-30 12:54:41)

34．循环控制Break,Continue在Db2用法？
答：在db2中 Break 转换为leave lab, Continue 转换为ITERATE lab
等同于GOTO语句
举例如下：
============== leave 例子

OPEN c1; 
 SET at_end = 0; 
 SET numrec = 0; 
 fetch_loop: 1 
 LOOP 
 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 
 IF SQLCODE = 0 THEN 
 SET proc_cuscrd = proc_cuscrd * 1.2; 
 UPDATE ordapplib.customer 
 SET cuscrd = proc_cuscrd 
 WHERE CURRENT OF c1; 
 SET numrec = numrec + 1; 
 ELSE 
 LEAVE fetch_loop; 2 
 END IF; 
 END LOOP fetch_loop; 
 CLOSE c1;

============ iterate 例子

BEGIN 
 OPEN c1; 
 ins_loop: 1 
 LOOP 
 FETCH c1 INTO v_dept, v_deptname, v_admdept; 
 IF at_end = 1 THEN 
 LEAVE ins_loop; 
 ELSEIF v_dept = ‘D11’ THEN 
 ITERATE ins_loop; 2 
 END IF; 
 INSERT INTO sampledb02.deptnew (deptno, deptname, admrdept) 
 VALUES (v_dept, v_deptname, v_admdept); 
 END LOOP; 
 CLOSE c1; 
 END;

35．在Db2存储过程中使用滚动游标（scrollable cursor）？
答：例子

CREATE PROCEDURE MYMAX 
 ( IN fld_name CHAR(30), 
 IN file_name CHAR(128), 
 INOUT max_value INTEGER) 
 LANGUAGE SQL 
 BEGIN atomic 
 DECLARE sql_stmt CHAR(256); 
 DECLARE not_found 
 CONDITION FOR ‘02000’; 
 DECLARE c1 DYNAMIC SCROLL CURSOR FOR s1; – 声明动态滚动游标 
 DECLARE CONTINUE HANDLER FOR not_found 
 SET max_value = NULL; 
 SET sql_stmt = ‘SELECT ’ || fld_name || ’ FROM ’ || file_name || 
 ’ ORDER BY 1’; –组合sql语句 
 PREPARE s1 FROM sql_stmt; 
 OPEN c1; 
 FETCH LAST FROM c1 INTO max_value; –转到最后行 
 CLOSE c1; 
 END

滚动游标的使用例子：（rpg）

EXEC SQL BEGIN DECLARE SECTION; 
 char fld_name[ 30 ]; 
 char file_name[ 128 ]; 
 integer max_value; 
 short ind3; 
 EXEC SQL END DECLARE SECTION; 
 Then the indicator variable is used in the call statement: 
 EXEC SQL 
 CALL MYMAX( :fld_name, :file_name, :max_value :ind3); 
 myfriend2010 (2007-8-30 12:55:05)

36．db2中存储过程中使用动态游标（dynamic cursor）？
答：使用PREPARE ， EXECUTE ，EXECUTE IMMEDIATE语句
例子：

CREATE PROCEDURE DYNSQLSAMPLE() 
 LANGUAGE SQL 
 BEGIN 
 DECLARE stmt VARCHAR(256); 
 SET stmt = ‘UPDATE employee SET salary = salary * 1.1 WHERE empno = ?’; 1 
 PREPARE s1 FROM stmt; 
 ins_loop: 
 FOR each_department AS 
 c1 CURSOR FOR 
 SELECT mgrno FROM department WHERE mgrno IS NOT NULL 
 DO 
 EXECUTE s1 USING mgrno; 
 END FOR; 
 END; 
 EXECUTE IMMEDIATE statement 例子： 
 PREPARE s1 FROM ‘UPDATE employee SET salary = salary * 1.1 WHERE 
 empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL); 
 EXECUTE s1; 
 等同于 
 EXECUTE IMMEDIATE ‘UPDATE employee SET salary = salary * 1.1 WHERE 
 empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL);

最基本动态游标语句
…

============================================================================================
db2中的循环语句
2008-10-07 16:22 119人阅读评论(0) 收藏举报

1．利用得到的游标在存储过程中循环：

DECLARE CUR_FEESET CURSOR WITH RETURN TO CALLER FOR (
          SELECT
                 --
          FROM 表
          WHERE 条件
          );
   OPEN CUR_FEESET;--得到游标
   --得到游标记录数
   SELECT
   count(CIF_CSTNO)
   into v_count
   FROM CB_CSTINF A ,CB_CSTBSNINF B
   WHERE A.CIF_STT<>'3' AND A.CIF_CSTNO = B.CBI_CSTNO ;   --
   FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;--
   WHILE V_COUNT>0 DO

　　　　 ……..
FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;–
END WHILE; –

２．另一种db2标准循环格式(leave、iterate 用法)：

SET V_COUNT = LENGTH(V_VALIDAUTHCOMBOS);–
SET V_INDEX = 0;–
AUTHLOOP: 
 LOOP 
 IF V_INDEX >= V_COUNT THEN 
 LEAVE AUTHLOOP;–相当于break 
 END IF;– 
 …. 
 SET V_INDEX = V_INDEX + 1;– 
 ……. 
 IF 条件　THEN 
 ITERATE AUTHLOOP;– 相当于continue 
 END IF;– 
 END LOOP;–

3.截取字符串的循环（设V_TEMP=‘CB1001|CB1002|CB1003|’）：

SET V_LENGTH = LENGTH(V_TEMP); 
 WHILE V_LENGTH>0 DO 
 SET V_POS = POSSTR(V_TEMP,’|’); 
 SET V_CURRENT_BSN = SUBSTR( V_TEMP, 1, V_POS-1 ); 
 SET V_TEMP = SUBSTR( V_TEMP, V_POS+1 ); 
 SET V_LENGTH = LENGTH(V_TEMP); 
 –最后一个字段，不再截取 
 SET V_BSNTYPE = V_CURRENT_BSN; 
 END WHILE;

4.游标循环（不用open 游标）：

DROP PROCEDURE TESTFOR; 
 CREATE PROCEDURE TESTFOR() 
 LANGUAGE SQL 
 BEGIN 
 DECLARE V_TEMP1 VARCHAR(2); 
 DECLARE V_TEMP2 VARCHAR(70); 
 FOR V1 AS CURSOR1 CURSOR FOR 
 SELECT STUDENT_ID AS TEMP1,STUDENT_NAME AS TEMP2 FROM STUDENT 
 DO 
 DELETE FROM STUDENT WHERE STUDENT_ID = TEMP1; 
 SET V_TEMP1 = TEMP1; 
 SET V_TEMP2 = TEMP2; 
 END FOR; 
 COMMIT; 
 END;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。