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)
创建函数
--给定参数查询记录数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)
执行函数
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`@`localhost` PROCEDURE `p1`()beginselect *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)创建触发器
语法
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
创建
--如果年龄在16岁以下，则改为16mysql> 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)
MSSQL
过程
create table test(id int primary key,name varchar(32))；create proc p1--@d_name为输入参数，@count为输出参数 @d_name varchar(32), @count integer outputasselect @count= count(*) from test where name=@d_name--执行begindeclare @count integerexec p1 '麦兜',@count outputprint @countend
简单的过程调用游标
createproc pro1 as begindeclare cur1 cursorfor select name from t1open cur1declare@thisname varchar(32)fetch next from cur1 into @thisnamewhile @@fetch_status=0------------------运行正常beginprint @thisnamefetch next from cur1 into @thisnameendclose cur1--------------关闭deallocate cur1---------回收资源endexec pro1----执行过程触发器
use dbgoif exists(select *from sysobjects where name='tri_setting' and type='tr')drop trigger tri_settinggocreate trigger tri_settingon settings forupdate asbegin 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' beginupdate hm_mailbox set sendmailserver =@settingstring ;endelse if @settingname='smtprelayerusessl'beginupdate hm_mailbox set sendmailserverssl =@settinginteger ;endelse if @settingname='smtprelayerport'begin update hm_mailbox set smtpport =@settinginteger;endelse if @settingname='smtprelayerusername'beginupdate hm_mailbox set sendmailserverssl =@settingstring;endelse if @settingname='smtprelayerpassword'beginupdate hm_mailbox set smtpsafepassword =@settingstring ;endend--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_domainsgocreate trigger tri_domains on maildomain for update asbegin 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 )beginif charindex('@',@c_maildomainname)>0update 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;endclose c1;deallocate c1; end --update hm_maildomain set maildomainname='126.com' where maildomainid=5; Oracle
触发器
create or replace trigger tri_person_tr1before insert or update on personfor each rowwhen (new.age < 19)begin:new.age := 19;end; 函数
create or replace function totalnum(firstindex in number, lastindexin number) return number istotalnum number;beginselect count(*) into recordnum from (select * from (select rownum num, o.* from orders o) s where s.num between firstindex and lastindex);end totalnum; 几个
页:
[1]