存儲過程自定義函數(shù)觸發(fā)器-

1、*存儲過程* SQLServer中的過程:無直接返回至(沒有用returnreturn語句返回執(zhí)行狀態(tài)存儲過程分類:系統(tǒng)存儲過程,存于master數(shù)據(jù)庫,以sp_開頭。擴展存儲過程,動態(tài)加載和執(zhí)行動態(tài)鏈接數(shù)據(jù)庫DLL,以xp_開頭。臨時存儲過程,以#(局部,#(全局開頭,SQLServer連接關閉后,自動刪除。自定義存儲過程(1語法:create procedure 存儲過程名參數(shù)聲明as 執(zhí)行語句練習1:創(chuàng)建一個存儲過程實現(xiàn)兩個數(shù)相加。create procedure sp_add num1 int,num2 int,sum int outputas select sum=num1+num2

2、;(2查詢存儲過程:exec sp_helptext 'sp_add'exec:表示執(zhí)行。sp_helptext:存儲過程,屬于系統(tǒng)存儲過程,以sp_開頭(3執(zhí)行存儲過程:declare number1 int,number2 int,result int;select number1=100,number2=200;exec sp_add number1,number2,result output;select result;(4修改存儲過程:alter procedure sp_add num1 int,num2 int,sum int outputas select su

3、m=num1+num2+sum;declare number1 int,number2 int,result int;select number1=100,number2=200,result=1;exec sp_add number1,number2,result output;select result;(5刪除存儲過程:drop procedure sp_add;*自定義函數(shù)* 系統(tǒng)自帶函數(shù):數(shù)字型、字符型、日期型。自定義函數(shù):(1語法:create function 函數(shù)名參數(shù)聲明returns 返回數(shù)據(jù)類型begin 語句return 返回值end練習2:實現(xiàn)兩個數(shù)相加的函數(shù)。cre

4、ate function func_add (num1 int,num2 int returns intbegin return num1+num2; end【自定義函數(shù)與存儲過程定義的區(qū)別】參數(shù)要用(括起來有returns 語句,聲明返回數(shù)據(jù)類型函數(shù)實現(xiàn)代碼要用begin end必須有return 返回函數(shù)值(2查詢函數(shù):exec sp_helptext 'func_add'(3執(zhí)行函數(shù):select 用戶賬號名.函數(shù)名(實參select dbo.func_add(10,20;(4修改函數(shù):alter function func_add (num1 int,num2 int

5、returns intbegin return (num1+num2*2; endselect dbo.func_add(10,20;(5刪除函數(shù):drop function func_add;練習3:創(chuàng)建一個普通的用戶myuser。創(chuàng)建一個數(shù)據(jù)庫mydb;create database mydbon (name=mydb_dat,filename='G:mydb_dat.mdf',size=2MBlog on(name=mydb_log,filename='G:mydb_log.ldf',size=2MB;創(chuàng)建一個登錄賬號,賬號名為test,密碼為123,默認

6、數(shù)據(jù)庫為mydb;exec sp_addlogin 'test','123','mydb'為數(shù)據(jù)庫mydb創(chuàng)建一個用戶賬號myuser,對應的登錄賬號是test;use mydb;exec sp_grantdbaccess 'test','myuser'向用戶myuser授權grant create table to myuser;grant create procedure to myuser;grant create function to myuser;以test用戶登錄并創(chuàng)建存儲過程和自定義函數(shù)create

7、 procedure sp_add num1 int,num2 int,sum int outputas select sum=num1+num2;declare number1 int,number2 int,result int;select number1=100,number2=200;exec sp_add number1,number2,result output;select result;create function func_add (num1 int,num2 int returns intbegin return num1+num2; endselect myuser.

8、func_add(10,20;drop procedure sp_add;drop function func_add;刪除用戶賬號myuserexec sp_revokedbaccess 'myuser'*觸發(fā)器* 存儲過程和函數(shù)需要主動調(diào)用才能執(zhí)行,觸發(fā)器是根據(jù)事件自動觸發(fā)執(zhí)行(1語法:create trigger 觸發(fā)器名on 表名|視圖名for |after |instead of insert,delete,updateas 執(zhí)行語句說明:for默認情況下是after,instead of多用于視圖練習4:跟蹤數(shù)據(jù)修改變化?！緄nsert:inserted表updat

9、e:deleted表,記錄的是舊數(shù)據(jù);inserted表,記錄的使修改后的數(shù)據(jù)delete:deleted表】create table emp(name varchar(20,salary int;insert into emp values('wonderful',100;insert into emp values('perfect',200;create table emplog(log1 varchar(7000;create trigger emp_terig on emp for updateas begindeclare old int,new i

10、nt,log varchar(500,name varchar(20;select old =salary from deleted ;select name=name from inserted;select new=salary from inserted;if(new/old>=10 or (new/old<=0.1begin select log=name+'修改前工資:'+str(old+'修改后工資:'+str(new;insert into emplog values(log; endendupdate emp set salary=1

11、0000 where name='wonderful' select * from emplog;update emp set salary=400 where name='perfect' select * from emplog;update emp set salary=20 where name='perfect' select * from emplog;練習5:向基于多表的視圖插入數(shù)據(jù)。create table student(name varchar(20,age int,deptid int;create table dept(d

12、eptid int,name varchar(20;create view stu_dept as select as stuname,s.age as stuage,s.deptid , as deptname from student s inner join dept d on s.deptid=d.deptidselect * from stu_dept;insert into stu_dept values('wonderful',20,1,'計算機工程學院' (×create trigger stu_dept_trig on stu_dept instead of insertas begindeclare stuname varchar(20,stuage int,deptid int,deptname varchar(20;selectstuname=stuname,stuage=stuage,deptid=deptid,deptname=deptname from inserted insert into student values (s