存储过程的if，else怎么写？

不同的数据库中，存储过程中if else 语句写法有一些差别。

如果是SQLServer数据库，存储过程的if, else语句可以这样写：

if a>b

Begin

   print 'a'

End

Else if a<b

Begin

   print 'b'

End

Else 

Begin

    print '代码' 

End

Oracle 采用下面这种写法:

IF testvalue > 100 THEN

  dbms_output.put_line( '100+' )

ELSIF testvalue = 100 THEN

  dbms_output.put_line( '100' )

ELSE

  dbms_output.put_line( '100-' )

END IF

DB2,  MYSQL  是下面这种写法:  （ 与 Oracle 区别在于那个    ELSIF   ）

IF p_val > 100 THEN 

  INSERT INTO output_debug VALUES( '100+' ) 

ELSEIF p_val = 100 THEN 

  INSERT INTO output_debug VALUES( '100' ) 

ELSE 

  INSERT INTO output_debug VALUES( '100-' ) 

END IF

oracle触发器格式：

在CODE上查看代码片派生到我的代码片

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name

[FOR EACH ROW]

DECLARE arg_name type [CONSTANT] [NOT NULL] [:=value]

BEGIN

pl/sql语句

END

MySQL触发器格式：

在CODE上查看代码片派生到我的代码片

CREATE TRIGGER trigger_name

BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name

[FOR EACH ROW]

BEGIN

DECLARE arg_name1[,arg_name2,...] type [DEFAULT value]

sql语句

END

创建测试表（建表语句适用于Oracle、MySQL）：

在CODE上查看代码片派生到我的代码片

CREATE TABLE test(

idint,

name varchar(10),

age int,

birthday date,

description varchar(50),

PRIMARY KEY (id)

)

CREATE TABLE test_log(

idint,

dealtime date,

dealtype varchar(10),

PRIMARY KEY (`id`)

)

Oracle触发器和MySQL触发器的区别如下：

1，创建语句格式不同

Oracle：create or replace（Oracle客户端需要手动提交，MySQL客户端设置的自动提交）

在CODE上查看代码片派生到我的代码片

SQL>CREATE OR REPLACE TRIGGER trigger_test_insert

2 BEFORE INSERT ON test

3 FOR EACH ROW

4 BEGIN

5 insert into test_log values(1,sysdate,'insert')

6 END

7 /

Trigger created

SQL>insert into test(id, name) values(1, 'name')

1 row inserted

SQL>commit

Commit complete

SQL>select * from test_log

ID DEALTIMEDEALTYPE

--------------------------------------- ----------- ----------

1 2014/7/16 1 insert

MySQL：不包含or replace

在CODE上查看代码片派生到我的代码片

mysql>delimiter $

CREATE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

insert into test_log values(1,now(),'insert')

END$

delimiter

Query OK, 0 rows affected

mysql>insert into test(id, name) values(1, 'name')

Query OK, 1 row affected

mysql>select * from test_log

+----+------------+----------+

| id | dealtime | dealtype |

+----+------------+----------+

| 1 | 2014-07-16 | insert |

+----+------------+----------+

1 row in set

2，变量的声明位置、声明格式均不相同

Oracle：声明位置在触发时的执行语句块外部

通过%type的方式将变量与表特定字段类型相关联的好处是：在某些情况下，修改该字段类型时不需要修改触发器（如：字段类型由varchar(10)修改为varchar(20)时，不需要修改触发器）

在CODE上查看代码片派生到我的代码片

SQL>CREATE TRIGGER trigger_test_insert

2 BEFORE INSERT ON test

3 FOR EACH ROW

4 DECLARE id1 int default 1

5 id2 int:=1

6 id3 test_log.id%type:=1

7 BEGIN

8 insert into test_log values(id1+id2+id3,sysdate,'insert')

9 END

10 /

Trigger created

SQL>insert into test(id, name) values(1, 'name')

1 row inserted

SQL>commit

Commit complete

SQL>select * from test_log

ID DEALTIMEDEALTYPE

--------------------------------------- ----------- ----------

3 2014/7/16 1 insert

MySQL：声明位置在触发时的执行语句块内部

在CODE上查看代码片派生到我的代码片

mysql>delimiter $

CREATE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

DECLARE id1 int DEFAULT 1

DECLARE id2 int DEFAULT 1

insert into test_log values(id1+id2,now(),'insert')

END$

delimiter

Query OK, 0 rows affected

mysql>insert into test(id, name) values(1, 'name')

Query OK, 1 row affected

mysql>select * from test_log

+----+------------+----------+

| id | dealtime | dealtype |

+----+------------+----------+

| 2 | 2014-07-16 | insert |

+----+------------+----------+

1 row in set

3，注释符不同

Oracle：使用/* */作为注释符，或者两个连续的-作为注释符（PL/SQL块中至少包含一条可执行语句）

在CODE上查看代码片派生到我的代码片

CREATE OR REPLACE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

--just a test

/* just a test */

null

END

/

MySQL：使用/* */作为注释符，或者两个连续的-后加一个空格作为注释符

在CODE上查看代码片派生到我的代码片

delimiter $

CREATE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

/* just a test */

-- 两个‘-’后面必须带空格

END$

delimiter

4，赋值语法不同

Oracle：可以通过select into语句赋值，还可以通过:=进行赋值

在CODE上查看代码片派生到我的代码片

SQL>CREATE OR REPLACE TRIGGER trigger_test_insert

2 BEFORE INSERT ON test

3 FOR EACH ROW

4 DECLARE id int

5 BEGIN

6 select max(tl.id) into id from test_log tl

7 if id is null then

8 id:=1

9 else

10 id:=id+1

11 end if

12 insert into test_log values(id,sysdate,'insert')

13 END

14 /

Trigger created

SQL>insert into test(id, name) values(1, 'name')

1 row inserted

SQL>commit

Commit complete

SQL>select * from test_log

ID DEALTIMEDEALTYPE

--------------------------------------- ----------- ----------

1 2014/7/16 1 insert

MySQL：可以通过select into语句赋值，还可以通过set语句进行赋值

在CODE上查看代码片派生到我的代码片

mysql>delimiter $

CREATE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

DECLARE id int

select max(tl.id) into id from test_log tl

if id is null then

set id=1

else

set id=id+1

end if

insert into test_log values(id,now(),'insert')

END$

delimiter

Query OK, 0 rows affected

mysql>insert into test(id, name) values(1, 'name')

Query OK, 1 row affected

mysql>select * from test_log

+----+------------+----------+

| id | dealtime | dealtype |

+----+------------+----------+

| 1 | 2014-07-16 | insert |

+----+------------+----------+

1 row in set

5，对于行级更新触发器

Oracle：原有行用:old表示，新行用:new表示

在CODE上查看代码片派生到我的代码片

SQL>CREATE OR REPLACE TRIGGER trigger_test_update

2 BEFORE UPDATE ON test

3 FOR EACH ROW

4 BEGIN

5 :new.description := 'change name[' ||

6 :old.name || ']->[' ||

7 :new.name || ']'

8 END

9 /

Trigger created

SQL>insert into test(id, name) values (1, 'aaa')

1 row inserted

SQL>commit

Commit complete

SQL>update test set name = 'bbb' where id = 1

1 row updated

SQL>commit

Commit complete

SQL>select id, name, description from test

ID NAME DESCRIPTION

--------------------------------------- ---------- ----------------------

1 bbbchange name[aaa]->[bbb]

MySQL：原有行用old表示，新行用new表示

在CODE上查看代码片派生到我的代码片

mysql>delimiter $

CREATE TRIGGER trigger_test_update

BEFORE UPDATE ON test

FOR EACH ROW

BEGIN

set new.description = concat('change name[',

old.name,']->[',new.name,']')

END$

delimiter

Query OK, 0 rows affected

mysql>insert into test(id, name) values (1, 'aaa')

Query OK, 1 row affected

mysql>update test set name = 'bbb' where id = 1

Query OK, 1 row affected

Rows matched: 1 Changed: 1 Warnings: 0

mysql>select id, name, description from test

+----+------+-------------------------+

| id | name | description |

+----+------+-------------------------+

| 1 | bbb | change name[aaa]->[bbb] |

+----+------+-------------------------+

1 row in set

6，其它一些语法、函数上的区别

Oracle：使用if...elsif...else

MySQL：使用if...elseif...else

Oracle：sysdate指代系统时间

MySQL：sysdate()指代系统时间

---