DB几个重要的对象

MySQL
因为过程和函数可能有多个分号，所以下改一下定界符号

mysql> delimiter !

接着创建过程

mysql> create procedure p2(name varchar(32))--定义了一个变量

    -> begin

    -> insert into test(name) values(name);

    -> end

    -> !

Query OK, 0 rows affected (1.53 sec)



mysql> call p2('麦兜');

    -> !

Query OK, 1 row affected (0.05 sec)[/b]

创建函数

--给定参数查询记录数

mysql> create function f1(d_name varchar(32))

    -> returns int

    -> begin

    -> declare count int;

    -> set count=(select count(*)from test where name=d_name);

    -> return count;

    -> end

    -> !

Query OK, 0 rows affected (0.00 sec)[/b]

执行函数

mysql> select f1('abc');

    -> !

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

| f1('abc') |

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

|         1 |

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

1 row in set (0.00 sec)

查看内容可特征

mysql> show create procedure test.p1\g

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

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

| Procedure | sql_mode                                | Create Procedure

                                                        |

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

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

| p1        | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER | CREATE DEFINER=`root`@`l

ocalhost` PROCEDURE `p1`()

begin

select *from test;

end |

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

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

1 row in set (0.00 sec)



mysql> show function status like 'f1'

    -> !

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

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

| Db   | Name | Type     | Definer        | Modified            | Created

      | Security_type | Comment |

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

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

| test | f1   | FUNCTION | root@localhost | 2008-08-11 17:35:59 | 2008-08-11 17:

35:59 | DEFINER       |         |

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

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

1 row in set (0.00 sec)

[/b]

创建触发器


  语法

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

创建

--如果年龄在16岁以下，则改为16

mysql> create trigger t1 before insert on test for each row

    -> begin

    -> if new.age then

    -> set new.age=16;

    -> end if;

    -> end

    -> !

Query OK, 0 rows affected (0.00 sec)[/b]

MSSQL
过程

create table test(

id int primary key,

name varchar(32)

)；

create proc p1--@d_name为输入参数，@count为输出参数

 @d_name varchar(32),

 @count integer output

as

select @count= count(*) from test where name=@d_name

--执行

begin

declare @count integer

  exec p1 '麦兜',@count output

print @count

end

简单的过程调用游标

create  proc pro1 

as 

begin

declare cur1 cursor  for select name from t1

open cur1

declare  @thisname varchar(32)

fetch next from cur1 into @thisname

while @@fetch_status=0------------------运行正常

begin

print @thisname

fetch next from cur1 into @thisname

end

close cur1--------------关闭

deallocate cur1---------回收资源

end

exec pro1----执行过程

触发器

use db
go
if exists(select *from sysobjects where name='tri_setting' and type='tr')
drop trigger tri_setting
go
create trigger tri_settingon settings for 	 update as
begin
 declare @settingid int,
@settingname varchar(32),
@settingstring varchar(32),
@settinginteger varchar(32);
select @settingid=settingid,@settingname=settingname,@settingstring=settingstring,@settinginteger=settinginteger 
from inserted;
if @settingname='smtprelayer'
 begin
update hm_mailbox set sendmailserver =@settingstring ;
end
else if @settingname='smtprelayerusessl'
begin
update hm_mailbox set sendmailserverssl =@settinginteger ;
end
else if @settingname='smtprelayerport'
begin 
update hm_mailbox set smtpport =@settinginteger;
end
else if @settingname='smtprelayerusername'
begin
update hm_mailbox set sendmailserverssl =@settingstring;
end
else if @settingname='smtprelayerpassword'
begin
update hm_mailbox set smtpsafepassword =@settingstring ;
end
end
--update hm_settings set settingstring ='100~~~~~' where settingname='smtprelayer';
--------------------------------

/*触发器2*/
if exists(select *from sysobjects where name='tri_domains' and type='tr')
drop trigger tri_domains
go
create trigger tri_domains on maildomain for update as
begin
 declare @maildomainid int,
@maildomainname varchar(50)
select  @maildomainid=maildomainid,@maildomainname=maildomainname from inserted;
declare c1 cursor for select userid, maildomainid,emailaddress from hm_mailbox where maildomainid=@maildomainid;
declare  @c_userid int,@c_maildomainid int,@c_maildomainname varchar(50);
open c1;
fetch next from c1 into  @c_userid,@c_maildomainid,@c_maildomainname;
while( @@Fetch_Status=0 )
begin
if charindex('@',@c_maildomainname)>0
update hm_mailbox set emailaddress=left(@c_maildomainname,charindex('@',@c_maildomainname))+@maildomainname where userid=@c_userid;
 fetch next from c1 into @c_userid, @c_maildomainid,@c_maildomainname;
end
close c1;
deallocate c1;
 end 
--update hm_maildomain set maildomainname='126.com' where maildomainid=5;

 

Oracle

触发器

create or replace trigger tri_person_tr1
  before insert or update on person
  for each row
  when (new.age < 19)
begin
  :new.age := 19;
end;

  函数

create or replace function totalnum(firstindex in number,
                                    lastindex  in number) return number is
  totalnum number;
begin
select count(*)
      into recordnum
      from (select *
              from (select rownum num, o.* from orders o) s
             where s.num between firstindex and lastindex);
end totalnum;

 

几个