sql 存储过程，函数，触发器

 

触发器：

mysql 

drop TRIGGER if EXISTS deleteScore2;
CREATE trigger deleteScore2
AFTER 
DELETE
on student
for each ROW
begin
delete from score where sid=old.id ;
end

sql server

IF EXISTS(
  SELECT *
    FROM sys.triggers
   WHERE name = N'trigger_name'
     
)
 DROP TRIGGER [dbo].[trigger_name]
GO

CREATE TRIGGER trigger_name  
ON student
after delete
AS 
IF IS_MEMBER ('db_owner') = 0
BEGIN
 declare @id int;
    --在inserted表中查询已经插入记录信息
    select @id = id from deleted;
   delete from score where sid= @id ;
END
GO

存储过程：

mysql：调用语句 call PROCEDURE_name

drop PROCEDURE if EXISTS PROCEDURE_name;
create PROCEDURE PROCEDURE_name()
begin 
 select name, case sex when '1' then '男'  when '0' then '女' END '性别' from student;
END

sql server：调用语句 exec  PROCEDURE_name

drop PROCEDURE if EXISTS procedure_name ;
GO
CREATE PROCEDURE procedure_name 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select name, case sex when '1' then '男'  when '0' then '女' END '性别' from student;
END

函数：

mysql: select FUNCTION_Name(a.id) fun from student a

create FUNCTION FUNCTION_Name(id INT)
RETURNS varchar(20)
BEGIN
RETURN(select name from student where id=id);
END

sql server:

标量值函数 调用方式：select  dbo.function_name(1)

CREATE FUNCTION function_name 
(
 -- Add the parameters for the function here
 @param1 int
)
RETURNS varchar(20)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @result varchar(20)

 -- Add the T-SQL statements to compute the return value here
 
 select @result= name from ces where id=@param1;

 -- Return the result of the function
 RETURN @result

END

 表值函数 调用方式：select * from  dbo.function_name2(1)

 CREATE FUNCTION function_name2 
( 
 -- Add the parameters for the function here
 @param1 int
)
RETURNS TABLE 
AS
RETURN 
(
 -- Add the SELECT statement with parameter references here
 SELECT * from ces where id=@param1
)