Informix存储过程详细笔记3-存储过程语言

三、存储过程语言
 
变量

局部变量
仅在本存储过程中有效的变量。 局部变量不允许有缺省值
全局变量
在同一用户活动期间， 存储过程中的可被同一数据库的其它存储过程访问的的变量。全局变量必须在所有使用的存储过程中定义，并且必须给出缺省值，实际的缺省值是第一次被访问时定义的缺省值。
 

变量（局部变量）的范围
变量在语句块内有效， 如语句块嵌套，则同名的内层定义覆盖其外层定义， 内层语句执行完后，外层定义重新有效。

存储过程表达式
存储过程表达式可以是除聚集函数表达式外的任何SQL算术表达式
例子： var_value1; var_value1+var_value2;

变量定义
用DEFINE 语句定义变量，其类型可以是除SERIAL数据类型外 的所有SQL 数据类型
定义TEXT 或BYTE 类型变量时， 需在其变量前加关键字REFERENCES， 以表明该变量并不含有真正的数据， 而只是指向数据的指针。
可以使用LIKE定义与字段类型一致的数据类型
传入参数变量的定义在create procedure procname(var_value int)定义
 

例子：
DEFINE i,j INT;
DEFINE name VARCHAR(12);
DEFINE time_stamp DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SCECOND;
DEFINE date_value DATE;
DEFINE txt REFERENCES TEXT;
DEFINE by REFERENCES BYTE;
DEFINE p_customer like users.customer_num;
 
 

定义全局变量
define global global_var int default 0;
 
 
局部BLOB数据类型
只使用描述符：
LET
DEFINE
CALL
SELECT
存取BLOB数据：
INSERT
UPDATE
RETURN（到前端应用程序）
 
 
 
 
变量赋值

四种方式
1、利用LET 语句
LET i, j=1, 0;
LET var_name, var_num=(SELECT username, usernum FROM users WHERE usernum=100);

2、利用SELECT 或SELECT ... INTO 语句
SELECT username INTO var_name FROM users WHERE usernum=100;

3、利用CALL 语句
call proc_new(22)  returning var_no1;

4、利用EXECUTE PROCEDURE ...INTO 语句
execute procedure proc_name(username,address into p_name,p_addr);
 
 

语句块
create procedure pro_test()
returning int;
    begin 
    
    define i int;
    let i=1;
    end
--begin,end 语句块（显示）
retrun i;
--returning,return语句块（隐含）
end procedure;
 
 
 

foreach循环
 
create procedure pro_foreach()
define id int;
select userid into id from users where age>30;
--select语句返回大于1的行，可以理解为将select到的值放在id这个列表中一个一个执行。
update others set uservalue=345 where userid=id;
end foreach;
end procedure;
 
 

使用update游标
条件分支
if then
elif then
else
end if
 
 

--------------------------------------------------
drop procedure pro_dele;
create procedure pro_dele()
define p_time date;
begin work;
foreach curl for
--update游标必须命名
select time_stamp into p_time from pro_dele_tbl
where num > 100
if p_time is not null then
delete from pro_del_tbl where current of curl;
--删除当前记录
end if;
end foreach;
commit work;
--所有修改记录的锁被释放。
end procedure;
--------------------------------------------------
 
 
 
 
if语句中的表达式

If exists(select username from users
   where usernum = 13) then
 …
end if
if var_num > all(select usernum from users
    where usernum = 23) then
 …
end if
if var_name matches “A*” then
 …
end if
 
 
 
 
 
循环语句while
 
格式： WHILE 条件语句    语句块   END WHILE

执行过程
先测试条件，若为真，则执行语句块，否则退出循环。
重复上述步骤，开始下一次循环，直到条件为假而退出循环

WHERE 与FOREACH 的区别
WHILE 循环的条件是不定的，循环次数不定，因而可能出现无穷循环
FOR和FOREACH 循环的条件是确定的，循环次数也是明确的，不可能出现无穷循环

例子
create procedure prowhile()
define i int;
define sum int;
let i=1;
let sum=0;
while i<100
let sum=sum+i;
let i=i+1;
end while;
end procedure;
 
 
 

循环语句for
 
三种格式
FOR 变量 IN (expr1 to expr2 STEP expr3)  语句块   END FOR
--expr1,expr2表示范围，expr3表示步长，默认为1
for i=1 to 10 step 2
...
end for;

FOR 变量 ＝ (expr1 to expr2 STEP expr3)  语句块   END FOR
--expr1,expr2表示范围，expr3表示步长，默认为1

FOR 变量 IN (expr1, expr2,...)  语句块   END FOR
for i in (1,2,3,4,5,7,8)
...
end for;
 
循环的转移
for i = 1 to 10
 if i = 5 then
  continue for;
----------------contiune将执行下一次循环
 elif i = 8 then
  exit for;
----------------exit for将退出循环，执行for循环的下一条语句
 end if;
end for;
 
 
 
 
 
循环语句foreach
 
 

用 FOREACH 语句可以查询或操作一组记录
FOREACH 隐式定义并打开一个游标
 

三种格式
FOREACH [WITH HOLD] SELECT ... INTO 语句
        语句块 
END FOREACH；
－FOREACH 游标名[WITH HOLD] FOR SELECT ... INTO 语句
        语句块 
END FOREACH；
FOREACH EXECUTE PROCEDURE 存储过程名(参数...参数)
     INTO 变量[, 变量[, ...]]    语句块 
END FOREACH；
FOREACH 定义的游标在以下情况下关闭：

无行返回
事务提交或回滚导致非保护游标关闭
循环非正常退出（使用EXIT 或 ON EXCEPTION）
 
 
 
 
操作系统命令

system "echo" "Delete Operation Completed" "|mail judy";

数据库系统一直等到上述命令执行完毕
不能使用返回值判断执行是否成功
如执行不成功，将设置适当的ISAM错误代码和SQL错误代码
 
 
 
检查NOTFOUND条件
create procedure protest()
define num integer;
returning integer;
foreach select usernum into num from users
where usernum=20
--select执行失败将不执行foreach中的语句。
return num;
end foreach;
return;
--return没有返回值将在前端应用中引发NOTFOUND
end procedure;
 
 
 
 

递归调用

create procedure digui(i int defualt 1)
 returning int;
 if i <6 then
  return 1;
 end if;
 return n * digui(n-2);
--调用自己
end procedure;
 
 
 

获取serial值

create procedure seri_inst()
 define seri int;
 insert into users
  (user_num,user_date)
  values (1,"2006-01-03");
 let seri = dbinfo("sqlca.sqlerrd1");
--获取上一条insert语句中的serial值
 insert into other(num,name)
  values(seri,"new");
end procedure;
 
 
 
 
 
获取处理的记录数
 
create procedure num_rows()
 returning int;
 define num_rows int;
 delete from orders where customer_num = 104;
 let num_rows = dbinfo("sqlca.sqlerrd2");
--select、update或update的记录数
 return num_rows;
end procedure;
 
 
 
 

跟踪调试语句trace

存储过程被正确创建后，说明无语法错误，但有可能有逻辑错误
TRACE语句用于调试存储过程， 它可以跟踪以下存储过程实体的值：
变量（Variables)
过程参数(Procedure arguments)
返回值(Return values)
SQL 错误代码（SQL error codes)
ISAM 错误代码(ISAM error codes)
TRACE 语句把跟踪结果写到一个文件中， 该文件由SQL语句SET DEBUG FILE指定
 
 
 
 

TRACE 语句的三种形式：
TRACE ON ：打开跟踪调试， 跟踪所有语句
TRACE OFF ：关闭跟踪调试
TRACE PROCEDURE： 对于过程调用， 不跟踪过程体，仅跟踪过程的输入和返回值。
 
 
 
 
create procedure tracepro(var_user_num int)
 define var_user_date date;
 set debug file to "/export/home/user/trace";
--设置输出文件
 trace on;
--跟踪所有执行的语句
 select user_date into var_user_date
  from users
  where user_num = var_user_num;
 if var_user_date is null then
  trace "user date is null";
--执行到这里输出user date is null
  execute procedure other((var_user_num );
 end if;
 trace off;
--关闭跟踪
end procedure;