如何声明一个存储过程
CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型,OUT 输出变量名 输出变量类型)
紧跟其后的是存储过程属性列表
常用的有:LANGUAGE SQL
、
MODIFIES SQL DATA
、RESULT SETS 1(返回结果集个数)
l
存储过程体以begin开始
l
存储过程体以end结束
存储过程约束规则
存储过程中调用存储过程
CALL 存储过程名(参数1,参数2,参数n)
例:
call spco_init_custom(bankcode,errno,errmsg);
GET DIAGNOSTICS retval=RETURN_STATUS;
if(retval<>0) then
set errno=errno;
set errmsg=errmsg;
return errno;
end if;
变量的定义
变量使用前必须先定义,方法为
DECLARE
变量名 变量类型 (default 默认值)
例:
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE inum INTEGER DEFAULT 0;
DECLARE curtime char(8);
DECLARE bcode char(6);
DECLARE sqlstate char(5);
if 表达式
if 条件1 then
逻辑体;
elseif 条件2 then
逻辑体;
else
逻辑体;
end if;
例:
IF
rating = 1 THEN
UPDATE
employee
SET
salary = salary * 1.10, bonus = 1000
WHERE
empno = employee_number;
ELSEIF
rating = 2
THEN
UPDATE
employee
SET
salary = salary * 1.05, bonus = 500
WHERE
empno = employee_number;
ELSE
UPDATE
employee
SET
salary = salary * 1.03, bonus = 0
WHERE
empno = employee_number;
END IF
;
case表达式
case 变量名 when
变量值1 then
. . .
when
变量值2 then
- - -
else
. . .
end case;
或
case when
变量名=变量值1 then
. . .
when
变量名=变量值2 then
- - -
else
. . .
end case;
例一:
CASE
v_workdept
WHEN
'A00'
THEN UPDATE
department
SET
deptname = 'DATA ACCESS 1';
WHEN
'B01'
THEN UPDATE
department
SET
deptname = 'DATA ACCESS 2';
ELSE UPDATE
department
SET
deptname = 'DATA ACCESS 3';
END CASE
;
例二:
CASE
WHEN
v_workdept = 'A00'
THEN UPDATE
department
SET
deptname = 'DATA ACCESS 1';
WHEN
v_workdept = 'B01'
THEN UPDATE
department
SET
deptname = 'DATA ACCESS 2';
ELSE UPDATE
department
SET
deptname = 'DATA ACCESS 3';
END CASE
;
for 表达式
for
循环名 as
游标名或select 表达式
do
sql
表达式;
end for;
例:
1
)
DECLARE
fullname CHAR(40);
FOR
vl
AS
SELECT
firstnme, midinit, lastname
FROM
employee
DO
SET
fullname = lastname || ',' || firstnme ||' ' || midinit;
INSERT INTO
tnames VALUE (fullname);
END FOR
2)
for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
select market_code as market_code
from tb_market_code
for update
do
end for;
goto表达式
goto 标示名;
标示名:
逻辑体;
例:
GOTO
FAIL
;
...
SUCCESS:
RETURN
0
FAIL:
RETURN
-200
while表达式
while 条件表达式 do
逻辑体;
end while;
LOOP表达式
LOOP... END LOOP;
例:
OPEN
c1;
ins_loop:
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; --
下一个循环
END IF
;
INSERT INTO
department (deptno, deptname, admrdept)
VALUES
('NEW', v_deptname, v_admdept);
END LOOP
;
CLOSE
c1;