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

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

2、;(2查詢存儲(chǔ)過程:exec sp_helptext 'sp_add'exec:表示執(zhí)行。sp_helptext:存儲(chǔ)過程,屬于系統(tǒng)存儲(chǔ)過程,以sp_開頭(3執(zhí)行存儲(chǔ)過程:declare number1 int,number2 int,result int;select number1=100,number2=200;exec sp_add number1,number2,result output;select result;(4修改存儲(chǔ)過程: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刪除存儲(chǔ)過程:drop procedure sp_add;*自定義函數(shù)* 系統(tǒng)自帶函數(shù):數(shù)字型、字符型、日期型。自定義函數(shù):(1語法:create function 函數(shù)名參數(shù)聲明returns 返回?cái)?shù)據(jù)類型begin 語句return 返回值end練習(xí)2:實(shí)現(xiàn)兩個(gè)數(shù)相加的函數(shù)。cre

4、ate function func_add (num1 int,num2 int returns intbegin return num1+num2; end【自定義函數(shù)與存儲(chǔ)過程定義的區(qū)別】參數(shù)要用(括起來有returns 語句,聲明返回?cái)?shù)據(jù)類型函數(shù)實(shí)現(xiàn)代碼要用begin end必須有return 返回函數(shù)值(2查詢函數(shù):exec sp_helptext 'func_add'(3執(zhí)行函數(shù):select 用戶賬號(hào)名.函數(shù)名(實(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;練習(xí)3:創(chuàng)建一個(gè)普通的用戶myuser。創(chuàng)建一個(gè)數(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)建一個(gè)登錄賬號(hào),賬號(hào)名為test,密碼為123,默認(rèn)

6、數(shù)據(jù)庫為mydb;exec sp_addlogin 'test','123','mydb'為數(shù)據(jù)庫mydb創(chuàng)建一個(gè)用戶賬號(hào)myuser,對(duì)應(yīng)的登錄賬號(hào)是test;use mydb;exec sp_grantdbaccess 'test','myuser'向用戶myuser授權(quán)grant create table to myuser;grant create procedure to myuser;grant create function to myuser;以test用戶登錄并創(chuàng)建存儲(chǔ)過程和自定義函數(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;刪除用戶賬號(hào)myuserexec sp_revokedbaccess 'myuser'*觸發(fā)器* 存儲(chǔ)過程和函數(shù)需要主動(dòng)調(diào)用才能執(zhí)行,觸發(fā)器是根據(jù)事件自動(dòng)觸發(fā)執(zhí)行(1語法:create trigger 觸發(fā)器名on 表名|視圖名for |after |instead of insert,delete,updateas 執(zhí)行語句說明:for默認(rèn)情況下是after,instead of多用于視圖練習(xí)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;練習(xí)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,'計(jì)算機(jī)工程學(xué)院' (×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