回忆是一座桥
却是通往寂寞的牢

SQL语法 - 使用存储过程定义模块化操作

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集合,可以包含逻辑控制语句和数据操纵语句,并且可以返回结果集。其经编译后存储在数据库中。用户可以通过指定存储过程的名称并传递参数(如果需要)来执行它。下面给出MySQL与SQL Server的存储过程的语法对比。

MySQL

语法:

create procedure 存储过程名称([ [IN|OUT|INOUT] 参数名称 参数的数据类型 , ...])
begin
...
end;

#存储过程的执行或调用:
CALL Procedure_name([参数1,[参数2]...]);
  • 存储过程的参数名称不需要带@前缀,参数的赋值与使用同DECLARE声明的局部变量一致。
  • IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能返回到外部
  • OUT 输出参数:该值可在存储过程内部被改变,并可返回,但其初始值为NULL,读取不了外部的值
  • INOUT 输入输出参数:调用时指定,在存储过程内部可被改变并返回到外部,且其初始值为外部的值
  • 注意:OUT与INOUT参数在调用时必须以变量的形式赋值,而不能用常量

示例:

1、IN参数的例子:

DROP PROCEDURE IF EXISTS test_proc;

CREATE PROCEDURE test_proc(in p_in int)  
BEGIN   
    SELECT p_in;   
    SET p_in = 2;   
    SELECT p_in;   
END;

set @a = 1;
CALL test_proc(@a);
SELECT @a;

-- 结果:

+------+  
| p_in |  
+------+  
|  1   |   
+------+  

+------+  
| p_in |  
+------+  
|  2   |   
+------+  

+------+  
| @a   |  
+------+  
|  1   |  
+------+  

2、OUT参数的例子:

DROP PROCEDURE IF EXISTS test_proc;

CREATE PROCEDURE test_proc(out p_in int)  
BEGIN   
    SELECT p_in;   
    SELECT 2 into p_in;   
    SELECT p_in;   
END;

set @a = 1;
CALL test_proc(@a);
SELECT @a;

-- 结果:

+------+  
| p_in |  
+------+  
| null |   
+------+  

+------+  
| p_in |  
+------+  
|  2   |   
+------+  

+------+  
| @a   |  
+------+  
|  2   |  
+------+  

3、INOUT参数的例子:

DROP PROCEDURE IF EXISTS test_proc;

CREATE PROCEDURE test_proc(inout p_in int)  
BEGIN   
    SELECT p_in;   
    SELECT 2 into p_in;   
    SELECT p_in;   
END;

set @a = 1;
CALL test_proc(@a);
SELECT @a;

-- 结果:

+------+  
| p_in |  
+------+  
|  1   |   
+------+  

+------+  
| p_in |  
+------+  
|  2   |   
+------+  

+------+  
| @a   |  
+------+  
|  2   |  
+------+  

存储过程的查看与删除:

#存储过程的查看:

#查看所有存储过程的状态:
SHOW PROCEDURE STATUS  [ like ‘pattern’] ;
#查看指定存储过程的定义:
SHOW CREATE PROCEDURE proc_name ;|\G
#从系统表里查看存储过程的信息:
SELECT * FROM information_schema.Routines Where ROUTINE_NAME = ‘proc_name’; 

-------------------------------------------------------------------

#存储过程的删除:
DROP PROCEDURE [IF EXISTS] proc_name;

SQL Server

语法:

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

#存储过程的执行或调用:
DELCARE @other_param int
SET @other_param = 333

EXEC Procedure_name;
EXECUTE Procedure_name 1,2;                                         --为参数赋值时,可以不带参数名
EXECUTE Procedure_name @param1=1,@other_param output;               --但也可以带上参数名,与Python函数的参数赋值类似
EXECUTE Procedure_name @param1=1,@param2=@other_param output;       --输出型参数,若需要输出,则在调用时必须带output关键字
  • 注意:OUTPUT参数在调用时必须以变量的形式赋值,而不能用常量
  • 存储过程的参数如果不带output则是输入型参数,只能接受而不能输出,类似MySQL里的in类型
  • 存储过程的参数如果带了output则是输出型参数,既可以接受也可以输出,类似MySQL的inout类型
  • 参数列表中的output关键字表示对应的参数可以被输出,但前提是调用存储过程且为该参数赋值时有带上output关键字,如果赋值时没带output关键字,那即使参数列表有带output关键字,该参数也会被当做输入型参数。
  • 即参数列表里的output关键字表示该参数既可当作输入型参数,也可当作输出型参数,具体取决于调用与赋值时有没有带上output关键字。但如果参数列表里不带output关键字,那调用赋值时就不能带output关键字,即该参数只能当作输入性参数。

示例:

#  不带参数的存储过程

-- 创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;

-- 调用、执行存储过程
exec proc_get_student;

---------------------------------------------------------------------------

#  带输入型参数的存储过程

-- 创建存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT 
    @city nvarchar(50)
AS
    SELECT COUNT(OrderID) FROM Orders WHERE City=@city

-- 调用、执行存储过程
EXEC PROC_ORDER_COUNT  N'GuangZhou';
EXEC PROC_ORDER_COUNT  @city=N'GuangZhou';

---------------------------------------------------------------------------

#  带输出型参数的存储过程

-- 创建存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT 
    @city nvarchar(50),
    @count_num int output
AS
    SELECT @count_num = COUNT(OrderID) FROM Orders WHERE City=@city

-- 调用、执行存储过程
DECLARE @count int
EXEC PROC_ORDER_COUNT  N'GuangZhou', @count output;
EXEC PROC_ORDER_COUNT  N'GuangZhou', @count_num=@count output;
select @count;

存储过程的查看与删除:

#存储过程的查看:
select * from sysobjects where xtype='P'
select * from sys.procedures

-------------------------------------------------------------------

#存储过程的删除:
DROP PROCEDURE sp_name;

IF OBJECT_ID (N'PROC_NAME', N'P') IS NOT NULL
    DROP procedure PROC_NAME;
未经允许不得转载:夕枫 » SQL语法 - 使用存储过程定义模块化操作
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论