存储过程和触发器
001 介绍存储过程和如何调用系统的存储过程
存储过程---就像数据库中运行的方法(函数)
由存储过程名/存储过程参数组成/可以由返回结果
if else/while/变量/insert/select等，都可以在存储过程中使用
优点：
执行速度更快 - 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 - 类似方法的复用
减少网络流通量 - 只要传输 存储过程的名称
系统存储过程：
由系统定义，存放在master数据库中
名称以sp_开头或xp_开头，自定义的存储过程可以以usp_开头
自定义存储过程：
由用户自己在数据库中创建的存储过程usp
系统存储过程：
sp_databases 列出服务器上的所有数据库
sp_helpdb 显示有关数据库或所有数据库的信息
sp_renamedb 重命名数据库的名称
sp_tables 返回当前环境下某个表的表的信息
sp_collumns 返回某个表的列的信息
sp_help 显示某个表的所有信息
sp_helpconstraint 显示某个表的约束
sp_stored_procedures 列出当前环境下的所有存储过程
sp_password 添加或修改登录账户的密码
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
exec sp_databases //exec调用这个系统存储过程，可以没有exec
sp_helptext sp_tables //显示某个存储过程sp_tables的代码
alter替换Create 就是修改存储过程
如果C#参数类型如果是string，则SQL Server中为nvarchar(50)
----------------------------------------------
002 存储过程小案例
定义存储过程的语法
CREATE PROCEDURE 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
参数说明：参数可选，参数分为输入参数、输出参数，输入参数允许有默认值
EXEC 过程名 [参数]
1--创建存储过程：两个数相加
CREATE PROCEDURE usp_AddTwoNumber
@num1 int,
@num2 int
AS
BEGIN
SELECT @num1+@num2
END
--第一种调用：自定义存储过程的调用必须加EXEC
declare @n1 int=100,@n2 int=200
--EXEC usp_AddTwoNumber @num1=@n1,@num2=@n2
--第二章调用：
EXEC usp_AddTwoNumber @n1,@n2
--第三种调用：
EXEC usp_AddTwoNumber 100,200
2--创建存储过程：两数相加并返回
CREATE PROCEDURE usp_AddNumber
@num1 int,
@num2 int,
@sum int OUTPUT
AS
BEGIN
SET @sum=@num1+@num2
END
--调用这个存储过程：如果是存储过程中需要传出来的值，用OUTPUT进行修饰，外面调用的时候也需要加上OUTPUT
declare @n1 int=10,@n2 int=20,@sm int
EXEC usp_AddNumber @n1,@n2,@sm OUTPUT
SELECT @sm
Oralce中存储过程的重点：
|| 表示连接两个字符串，或者一个变量与字符串进行连接
" 双引号表示一个引号'
//Oracle中的求2数和：
CREATE OR REPLACE PROCEDURE PRO_AddTwoNumber( num1 NUMBER, num2 NUMBER ) IS strSql varchar(200); BEGIN strSql:='select '||num1||'+'||num2||' FROM DUAL'; execute immediate strSql; EXCEPTION WHEN OTHERS THEN DBMS_output.PUT_LINE(SQLERRM); END PRO_AddTwoNumber;
CREATE OR REPLACE PROCEDURE PRO_AddTwoNumber( num1 NUMBER, num2 NUMBER, sum OUT NUMBER ) IS strSql VARCHAR2(200); BEGIN strSql:='select '||num1||'+'||num2||' FROM DUAL'; EXECUTE IMMEDIATE strSql INTO sum; EXCEPTION WHEN OTHERS THEN Dbms_Output.put_line(SQLERRM); END PRO_AddTwoNumber; set serveroutput on var sum number exec PRO_AddTwoNumber(100,200,:sum)
--------------------------------------------------
003 模糊查询写成存储过程小案例
根据名字和年龄查询班级中的学生信系，并且把有多少个学生显示出来
select * from T_Name where name like '%m%' and age<30;
select COUNT(*) from T_Name where name like '%m%' and age<30;
3--创建存储过程：用户查询学生和数量
CREATE PROCEDURE usp_SelectStudent
@name nvarchar(50),
@age int,
@count int OUTPUT
AS
BEGIN
SET @count=(select COUNT(*) from T_Name where name like '%'+@name+'%' and age<@age);
select Id, name, age, salary from T_Name where name like '%'+@name+'%' and age<@age;
END
--调用这个存储过程：
DECLARE @num int
EXEC usp_SelectStudent 'm',30,@num OUTPUT
SELECT @num
create or replace procedure Query_By_Key ( p_key1 in varchar2, p_key2 in varchar2, p_cursor out JN_TABLE.JN_CTable )* as str_Sql varchar2(500):=' select * from TableA' || ' where pname like :v_key1 and pname like :v_key2 ' ; begin open p_cursor for str_Sql using '%' || p_key1|| '%' , '%'|| p_key2||'%'; end Query_By_Key;
SQL> CREATE OR REPLACE PROCEDURE PRO_SelectStudent( 2 name VARCHAR2, 3 pay VARCHAR2, 4 count OUT NUMBER 5 ) 6 IS 7 strSelectSql VARCHAR2(200); 8 strCountSql VARCHAR2(200); 9 BEGIN 10 strSelectSql:='SELECT NAME,MOBILE,EMAIL FROM TD_STUDENT WHERE NAME LIKE '%'||name||'%' AND PAY=''||pay||'''; 11 EXECUTE IMMEDIATE strSelectSql; 12 strCountSql:='SELECT COUNT(*) FROM TD_STUDENT WHERE NAME LIKE '%'||name||'%' AND PAY=''||pay||'''; 13 EXECUTE IMMEDIATE strCountSql INTO count; 14 EXCEPTION 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLERRM); 17 END PRO_SelectStudent; 18 / Warning: Procedure created with compilation errors
--------------------------------------------------------------
04 分页的存储过程
--分页
--每页5条，当前第3页，PageSize=5,CurrentPage=3,[(3-1)*5+1 3*5]
select * from (
select *,ROW_NUMBER() over(Order by Id) rownum from T_User ) t Where rownum Between 1 and 5;
Select ( Ceiling((select COUNT(*) from T_User)*1.0/5))
4--创建存储过程：分页及总页数
CREATE PROCEDURE usp_Pager
@PageSize int=10,
@CurrentPage int=1,
@SumPage int OUTPUT
AS
BEGIN
--总页数
SET @SumPage =Ceiling( (SELECT COUNT(*) FROM T_User)*1.0/@PageSize);
--当前页数据
select * from (
select *,ROW_NUMBER() over(Order by Id) rownum from T_User ) t Where rownum Between (@CurrentPage-1)*@PageSize+1 and @CurrentPage*@PageSize;
END
--调用分页存储过程
DECLARE @SumPage int
EXEC usp_Pager 5,3,@SumPage OUTPUT ;
SELECT @SumPage;
//Oracle中分页查询（Oracle中没有Ceiling()这个函数）
//分页查询（返回总页数） PageSize=5 （每页5条，当前2页 【(2-1)*5+1,2*5】） TotalPage //分页查询 SELECT * FROM ( SELECT NAME,MOBILE,QQ,ROW_NUMBER() over(order by Id) num FROM TD_STUDENT ) t WHERE t.num BETWEEN (2-1)*5+1 AND 2*5 //计算总页数 Select ( Ceiling((select COUNT(*) from TD_STUDENT)*1.0/5)) FROM DUAL //分页 CREATE OR REPLACE PROCEDURE PRO_Pager( PageSize NUMBER, CurrentPage NUMBER, SumPage OUT NUMBER ) IS strPageSql VARCHAR2(200); strSumSql VARCHAR2(200); BEGIN strSumSql :='Select ( Ceiling((select COUNT(*) from TD_STUDENT)*1.0/'||PageSize||')) FROM DUAL'; EXECUTE IMMEDIATE strSumSql INTO SumPage; strPageSql :='SELECT * FROM ('|| 'SELECT NAME,MOBILE,QQ,ROW_NUMBER() over(order by Id) num FROM TD_STUDENT '|| ') t WHERE t.num BETWEEN ('||CurrentPage||'-1)*'||PageSize||'+1 AND '||CurrentPage||'*'||PageSize||' '; EXECUTE IMMEDIATE strPageSql; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END PRO_Pager; //调用 set serveroutput on var SumPage NUMBER EXEC PRO_Pager(5,2,:SumPage) //错误：无法查询出总页数，因为没有Ceiling这个方法
//不返回总页数的分页 CREATE OR REPLACE PROCEDURE PRO_Pager( PageSize NUMBER, CurrentPage NUMBER ) IS strPageSql VARCHAR2(200); BEGIN strPageSql :='SELECT * FROM ('|| 'SELECT NAME,MOBILE,QQ,ROW_NUMBER() over(order by Id) num FROM TD_STUDENT '|| ') t WHERE t.num BETWEEN ('||CurrentPage||'-1)*'||PageSize||'+1 AND '||CurrentPage||'*'||PageSize||' '; EXECUTE IMMEDIATE strPageSql; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END PRO_Pager; set serveroutput on EXEC PRO_Pager(5,2)
----------------------------------------------------------
05 提分案例
编写一个存储过程，为班级中没有及格的学生提分
要求：提分后，不及格的人数必须小于总人数的一半，并且获取提分的次数
提示：需要三个参数，及格分数线，每次提分的分数，循环次数
//查询没及格的学生数量，需要及格线PassLine
//查询总学生数量
//if 没及格>总数/2 就循环
{
//给不及格学生增加成绩，需要每次增加多少分数EveryScore
//再次查询没及格数量
//技术循环次数Count
}
5--创建存储过程：提分
CREATE PROCEDURE usp_SubmitScore
@PassLine int, --及格分数线
@EveryScore int, --每次提的分数
@Count int OUTPUT --提分次数
AS
BEGIN
SET @Count=0
DECLARE @TotalCount int ,@FailCount int;
SET @TotalCount =( SELECT COUNT(*) FROM T_StudentGrade );
SET @FailCount = (SELECT COUNT(*) FROM T_StudentGrade WHERE Math<@PassLine);
WHILE(@FailCount>=@TotalCount*1.0/2) --分奇偶
BEGIN
UPDATE T_StudentGrade SET Math=Math+@EveryScore ;
SET @FailCount = (SELECT COUNT(*) FROM T_StudentGrade WHERE Math<@PassLine);
SET @Count=@Count+1;
END
END
--调用提分存储过程
DECLARE @num int
EXEC usp_SubmitScore 300,5,@num OUTPUT
SELECT @num
-------------------------------------------------------
06 C#调用存储过程
根据Id更改姓名和年龄
6--创建存储过程：更改指定id的name和age
Alter PROCEDURE usp_UpdateT_Name
@id int,
@name nvarchar(50),
@age int
AS
BEGIN
UPDATE T_Name SET name=@name,age=@age WHERE Id=@id
END
EXEC usp_UpdateT_Name 2,'我和你',20
//根据id更改Name UPDATE TP_STUDENT SET NAME='dd' WHERE ID=1 //根据id更改Name CREATE OR REPLACE PROCEDURE PRO_UpdateStudent( id NUMBER, name VARCHAR2 ) IS strSql VARCHAR2(200); BEGIN strSql:='UPDATE TP_STUDENT SET NAME='''||name||''' WHERE ID='||id||''; EXECUTE IMMEDIATE strSql; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END PRO_UpdateStudent; EXEC PRO_UpdateStudent(1,'TTTTTTT')
#region C#调用存储过程
string constr="Data Source=.;Initial Catalog=Test;User ID=sa;Password=abcd5226584";
int id=2;
string name="庞统";
int age=18;
string sql="usp_UpdateT_Name";//调用这个存储过程
SqlParameter[] param={new SqlParameter(){ParameterName="@id",Value=id},
new SqlParameter("@name",name),
new SqlParameter(){ParameterName="@age",Value=18}};
using (SqlConnection conn = new SqlConnection(constr))
using(SqlCommand cmd=new SqlCommand(sql,conn))
{
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;//指定命令文本的类型是存储过程
cmd.Parameters.AddRange(param);//调用存储过程时需要这些参数
cmd.ExecuteNonQuery();
}
#endregion
alter替换Create 就是修改存储过程
如果C#参数类型如果是string，则SQL Server中为nvarchar(50)
---------------------------------------------------------------
07 触发器案例
触发器的作用：自动化操作，减少了手动操作以及出错的几率。
触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。【在SQL内部把触发器看做事存储过程但是不能传递参数】
一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以普遍约束实现的复杂功能。
那究竟何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储工程。
触发器分为触发前执行还是触发后执行
After触发器：
在语句执行完毕之后触发
按语句触发，而不是所影响的行数，无论所影响的行数是多少都只触发一次。
只能建立在常规表上，不能建立在视图和临时表上。（*）
可以递归触发，最高可达32级。
Update(列)，在UPDATE语句触发时，判断某列是否被更新，返回BOOLEAN值。
介绍如何编写AFTER触发器。
Instead Of 触发器
用来替换原来的操作
不会递归触发
可以在约束被检查之前触发
可以建立在表和视图上（*）
介绍如何编写Insert Of触发器
触发器语法--删除
CREATE TRIGGER Tr_DeleteStudent ON Student
for delete
AS
BEGIN
INSERT INTO BackupStudent SELECT * FROM Deleted
END
触发器使用建议：
尽量不在触发器执行耗时操作，因为触发器会与SQL语句认为在同一个事务中。
触发器编写时注意多行触发时的处理。
过多触发器会造成数据库及应用程序的维护困难，同时对触发器过多的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。
能不用就不用。
INSERT、DELETE、UPDATE的原理：
数据表，insert临时表，delete临时表
当我们执行insert操作时，会同时把数据插入insert临时表和数据表;
当我们执行delete操作时，会在删除数据表中的数据时同时把这条数据放到delete临时表;
当我们执行update操作时，会先把数据从数据表干掉，进入delete临时表，然后产生一条新的数据，会把这条数据插入insert临时表，同时插入数据表。
--学生表--删除操作
--删除这条数据的时候，已经删除的数据存放到一个新的表中
--先创建一个和student表结构一样的表
SELECT top 0 * into T_NewStu from T_Student --从student表中查询所有数据的第0行放入新表newstu中
select * from T_NewStu
--触发器:删除数据表中数据时，会同时把删除后的数据放入新的表中
--创建一个触发器
CREATE Trigger Tr_DeleteTStu on T_Student
AFTER DELETE --该触发器在删除之后执行
AS
BEGIN
--把deleted表中的数据添加到这个新表中,DELETED是临时表
INSERT INTO T_NewStu SELECT * FROM DELETED
END
--执行失败，是因为原表的Id自增，则新标newstu也自增，而被删除的deleted中数据确是自带了id的，所以原表Id不能自增,至少newstu不能自增。
//创建与原表结构一样的表(不带数据) CREATE TABLE TP_STUDENT_BACKUP AS SELECT * FROM TP_STUDENT WHERE 1=0; //创建与原表结构一样的表(带数据) CREATE TABLE TP_STUDENT_BACKUP AS SELECT * FROM TP_STUDENT WHERE 1=1; //删除学生 CREATE OR REPLACE TRIGGER TRI_DeleteStudent AFTER DELETE ON TP_STUDENT BEGIN INSERT INTO TP_STUDENT_BACKUP SELECT * FROM DELETED; --错误：Oracle的删除临时表不是Deleted END; CREATE OR REPLACE TRIGGER TRI_DeleteStudent BEFORE DELETE ON TP_STUDENT BEGIN INSERT INTO TP_STUDENT_BACKUP SELECT * FROM TP_STUDENT; --Oracle的删除临时表不是Deleted,只好删除前触发，且全部加入备份表了 END;
//创建自增触发器 create or replace trigger TRI_TM_USER before insert on TM_USER for each row when (new.id is null) begin select SE_TM_USER.nextval into :new.id from dual; end ;
触发器案例（含IF）
在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
CREATE OR REPLACE TRIGGER TRI_TM_BORROW_ANDSAVE AFTER INSERT ON TM_BORROW FOR EACH ROW DECLARE currbno VARCHAR2(17); BEGIN SELECT BNO INTO currbno FROM TM_BOOKS WHERE BNAME= '数据库技术及应用'; IF currbno = :new.BNO --插入借阅记录的书籍是'数据库技术及应用' THEN INSERT INTO TM_BORROW_SAVE(ID,CNO,BNO,RDATE) VALUES(:new.id,:new.CNO,:new.BNO,:new.RDATE); END IF; END;
1035
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
