db2笔记_6_DbVisualizer编译存储过程

--1、在存储过程开头末尾加 --/ 和 / 
--2、create 后面不需要 or replace

--/
create procedure MyProcedureName (
    OUT o_ret INTEGER,
    OUT o_info VARCHAR(500),
    IN i_name VARCHAR(50)
)
specific  MyProcedureName 
RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLCODE INT DEFAULT 0;
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE v_sqlcode INT DEFAULT 0;
  DECLARE v_sqlstate CHAR(5) DEFAULT '00000';

  BEGIN
    SET v_sqlcode = SQLCODE,v_sqlstate = SQLSTATE;
    SET o_ret = -99,o_info = '在['||coalesce(o_info,'未知错误')||']处发生异常 SQLCode：'||char(v_sqlcode)||'SQLState：'||char(v_sqlstate);
  END ;
  
  SET o_ret = -99, o_info = '';

  BEGIN 
  DECLARE cur1 CURSOR WITH RETURN TO caller FOR
    SELECT * FROM MYTABLENAME WHERE NAME = i_name;
    OPEN cur1;
  END ;
  SET o_ret = 99, o_info = ''; 
END;
/