言成言成啊 | Kit Chen's Blog

SQL存储过程整理

2022-06-06 20:42:57 转载随意,文章会持续修订,请注明来源地址:https://meethigher.top/blog

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

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

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

Oracle

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

无参存储过程

无参存储过程示例

1
2
3
4
5
6
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;

展示结果如图

有参存储过程

有参存储过程示例

1
2
3
4
5
6
7
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;

如图

结果集存储过程

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

第一名,创建游标

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

第二步,创建存储过程

1
2
3
4
5
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来调用存储过程。

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

无参存储过程

示例

1
2
3
4
5
6
7
8
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;

如图

有参存储过程

示例

1
2
3
4
5
6
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$ ;

如图

结果集存储过程

示例

1
2
3
4
5
6
7
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

无参存储过程

示例

1
2
3
4
5
6
DELIMITER ;;
CREATE PROCEDURE getApps()
BEGIN
SELECT * FROM app_info;
END;;
DELIMITER ;

如图

有参存储过程

示例

1
2
3
4
5
6
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 ;

如图

结果集存储过程

示例

1
2
3
4
5
6
DELIMITER ;;
create procedure getAppLikeName(in name1 varchar(200))
begin
select * from app_info where app_name like name1;
end;;
DELIMITER ;

如图

SQLServer

无参存储过程

示例

1
2
3
4
5
create procedure getProject
as
begin
select * from project;
end;

如图

有参存储过程

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

如图

结果集存储过程

示例

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

如图

DB2

无参存储过程

示例

1
2
3
4
5
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

调用存储过程

1
call simpleProcedure

有参存储过程

示例

1
2
3
4
5
6
7
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

调用存储过程

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

调用结果如图

结果集存储过程

示例

1
2
3
4
5
6
7
8
9
10
11
12
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

调用存储过程

1
call returnSet()

调用结果如图

发布:2022-06-06 20:42:57
修改:2022-07-07 20:56:43
链接:https://meethigher.top/blog/2022/sql-procedure/
标签:sql 
付款码 打赏 分享
shift+ctrl+1可控制目录显示