SQL存储过程整理发布于2022-06-06 20:42:57，更新于2022-07-07 20:56:43，标签：sql 文章会持续修订，转载请注明来源地址：https://meethigher.top/blog

目前的存储过程支持，无参存储过程、有参存储过程、返回结果集存储过程、返回指定字段存储过程。

返回结果集存储过程在 MySQL 与 SQLServer 中，不需要设置游标或者出参即可读取数据。 Oracle 和 PostgreSQL 比较特殊，Oracle 需要设置一个出参游标，

PostgreSQL 可以通过临时表或者游标。 下面是 4 个数据库中，举例了无参、有参、返回结果集的写法示例。

Oracle

Oracle与其他数据库最大的不同是，如果要返回结果集，那么，是需要将游标来作为出参来接收数据的。

无参存储过程

无参存储过程示例

CREATE OR REPLACE procedure insertOne
as
BEGIN
DELETE FROM APP_INFO WHERE APP_CODE='9999';
INSERT INTO APP_INFO(APP_CODE,APP_DESC,APP_NAME) VALUES('9999','通过','flsja');
END;

展示结果如图

有参存储过程

有参存储过程示例

CREATE OR REPLACE PROCEDURE getAppInfo(
code1 IN varchar,
name1 OUT varchar
) as
BEGIN
SELECT app_name INTO name1 FROM APP_INFO WHERE app_code=code1;
END;

如图

结果集存储过程

返回过集存储过程示例，需要两步。

第一名，创建游标

CREATE OR REPLACE PACKAGE pk_set AS
TYPE cur IS REF CURSOR;
END pk_set;

第二步，创建存储过程

CREATE OR REPLACE PROCEDURE getApps( appcode IN VARCHAR,
rset OUT pk_set.cur ) AS
BEGIN
OPEN rset FOR SELECT * FROM APP_INFO WHERE APP_CODE = appcode;
END getApps;

如图

PostgreSQL

在PostgreSQL中，存储过程不再区分function和procedure，可以通过select或者call来调用存储过程。

目前返回结果集，是通过临时表来实现。

无参存储过程

示例

create or replace function totalCount()
returns integer as $count$
declare count integer;
begin
select count(*) into count from interface_info;
return count;
end;
$count$ language plpgsql;

如图

有参存储过程

示例

create or replace function finddescbyid(
id character varying,
out desc1 character varying)
returns character varying language sql as $function$
select interface_desc from interface_info where interface_code = id;
$function$ ;

如图

结果集存储过程

示例

create or replace function findCodeAndDescLimit(limit1 integer)
returns table("interfaceCode" character varying,"interfaceDesc" character varying)
language plpgsql as $function$
begin return query
select interface_code,interface_desc from interface_info limit limit1;
end;
$function$ ;

如图

MySQL

无参存储过程

示例

DELIMITER ;;
CREATE PROCEDURE getApps()
BEGIN
SELECT * FROM app_info;
END;;
DELIMITER ;

如图

有参存储过程

示例

DELIMITER ;;
create procedure getAppInfo(in appCode varchar(200),out appName varchar(200))
begin
select app_name into appName from app_info where app_code=appCode;
end;;
DELIMITER ;

如图

结果集存储过程

示例

DELIMITER ;;
create procedure getAppLikeName(in name1 varchar(200))
begin
select * from app_info where app_name like name1;
end;;
DELIMITER ;

如图

SQLServer

无参存储过程

示例

create procedure getProject
as
begin
select * from project;
end;

如图

有参存储过程

示例

create procedure GetProject_Out
@Code nvarchar(64),
@Name nvarchar(32) output
as
begin
 if(@Code is not null and @Code <> '')
   begin
	  select @Name=project_name from project where project_code=@Code
	 end
 else
  begin
	 set @Name='185'
	end
end;

如图

结果集存储过程

示例

create procedure getProjectLikeName(@name1 varchar(200))
as
begin
select * from project where project_name like @name1;
end

如图

DB2

无参存储过程

示例

create or replace procedure simpleProcedure
begin
  delete from data_info di where di.code='存储过程code';
  insert into data_info(code,name) values('存储过程code','存储过程name');
end

调用存储过程

call simpleProcedure

有参存储过程

示例

create or replace procedure findByCode(
  in code11 varchar(50),
  out name11 varchar(50)
)
begin
  select di.name into name11 from data_info di where di.code=code11;
end

调用存储过程

call findByCode('存储过程1',?);

调用结果如图

结果集存储过程

示例

create or replace procedure returnSet()
/*定义返回结果集(游标)个数*/
dynamic result sets 1 
begin
  begin
    /*定义一个游标，如果游标不只一个，上面的个数也要进行相应修改*/
    declare rs cursor with hold with return to client for 
    select * from data_info;
    /*打开游标*/
    open rs;
  end;
end

调用存储过程

call returnSet()

调用结果如图