存储过程和触发器

 

存储过程和触发器

 

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;