MySql和Sql server中函数和存储过程的实现

来源：互联网发布：印特软件编辑：程序博客网时间：2024/05/18 01:39

一、存储过程

1.语法结构

Sql Server版

create proc | procedure pro_name    [{@参数数据类型} [=默认值] [output],     {@参数数据类型} [=默认值] [output],     ....    ]as    SQL_statements

MySql版

   CREATE PROCEDURE 存储过程名 (参数列表)   BEGIN   SQL语句代码块   END

2.创建不带参数的存储过程

sql server版

if (exists (select * from sys.objects where name = 'proc_get_student'))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--调用、执行存储过程exec proc_get_student;

MySqll版

create PROCEDURE proc_test()SELECT version();CALL proc_test();

3.创建带参数的存储过程

sql server 版

if (object_id('proc_find_stu', 'P') is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;

mysql版

CREATE PROCEDURE proc_test(in a int)SELECT a+1;call proc_test(2);

4.带输出参数的存储过程

sql server版

if (object_id('proc_getStudentRecord', 'P') is not null)    drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord(    @id int, --默认输入参数    @name varchar(20) out, --输出参数    @age varchar(20) output--输入输出参数)as    select @name = name, @age = age  from student where id = @id and sex = @age;go

mysql版

CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT) BEGINIF p_name is null or p_name='' THENSELECT * FROM t_user; ELSESELECT * FROM t_user WHERE USER_NAME LIKE p_name; END IF; SELECT FOUND_ROWS() INTO p_int; END

注意：mysql用alter修改存储过程是不能修改存储过程体的，只能删除后重新创建

0 0