PostgreSQL 存储过程中 in、out、in out参数

传参类型说明：

in：　　是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变。

out：　 模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程。简单说：out参数在调用的时候，不需要传入参数（尝试传入会报错），不需要rutrun该参数，function结束会自动返回该out参数。

in out： 表示参数可以向该过程中传递值，也可以将某个值传出去。简单说：传参要指定对应参数，结束的时候，还需要专门当out参数去获取。

 

这里是以类PG数据库做的实验，本质基本相同：

 

1.创建类型、数组类型：

DROP TABLE test_type CASCADE ;
DROP TYPE test_type CASCADE ;
CREATE type  test_type as (
      id INT,
      info varchar(32)
);
    
DROP TYPE test_type_Array;
CREATE TYPE test_type_Array AS TABLE OF test_type;

　　

2.创建测试表，填充测试数据：

DROP TABLE test;
CREATE TABLE test(id VARCHAR(32), info varchar(32), crt_time TIMESTAMP);
INSERT INTO test values('1', 'test1', now());
INSERT INTO test values('1', 'test11', now());
INSERT INTO test values('2', 'test2', now());

　　

3.创建四个function，分别测试简单类型和对象类型（这里创建数组来测试）：

/*
 * 测试简单类型的in out参数使用，参数需要指定，结果需要当做out参数获取*
 * exp：v_info := test_fnc(v_record_id, v_info);
 */
CREATE OR REPLACE FUNCTION test_fnc(id VARCHAR(32), info IN OUT varchar(32)) RETURNS varchar(32) AS
$$
BEGIN 
	info := info|| '_connect_' || id;
	raise notice 'info is: %', info;
END 
$$
LANGUAGE plsql;

/*
 * 测试简单类型的out参数使用，参数不需要指定，结果需要获取*
 * exp：v_info := test_fnc2(v_record_id);
 */
CREATE OR REPLACE FUNCTION test_fnc2(id VARCHAR(32), info  OUT varchar(32)) RETURNS varchar(32) AS
$$
BEGIN 
	info := info|| '_connect_' || id;
	raise notice 'info is: %', info;
END 
$$
LANGUAGE plsql;

/*
 * 测试数组类型的in out参数使用，参数需要指定，结果需要当做out参数获取*
 * exp：test_A := test_array_fnc(v_record_id, test_A);
 */
CREATE OR REPLACE FUNCTION test_array_fnc(v_id VARCHAR(32), test_A IN OUT test_type_Array) RETURNS test_type_Array AS
$$
DECLARE 
	v_num int;
	one_type_value test_type;
	CURSOR test_values IS SELECT * FROM test WHERE id = v_id;
BEGIN 
	v_num := 1;
	FOR one_value IN test_values LOOP
		one_type_value.id := one_value.id;
		one_type_value.info := one_value.info;
		
		test_A.extend;
		test_A(v_num) := one_type_value;
		
		raise notice 'v_num is: %', v_num;
		raise notice 'test_A.count is: %', test_A.count;
		
		v_num := v_num + 1;
	END LOOP;
END 
$$
LANGUAGE plsql;

/*
 * 测试数组类型的out参数使用，参数不需要指定，结果需要获取*
 * exp：test_A := test_array_fnc2(v_record_id);
 */
CREATE OR REPLACE FUNCTION test_array_fnc2(v_id VARCHAR(32), test_A  OUT test_type_Array) RETURNS test_type_Array AS
$$
DECLARE 
	v_num int;
	one_type_value test_type;
	CURSOR test_values IS SELECT * FROM test WHERE id = v_id;
BEGIN 
	v_num := 1;
	test_A := test_type_Array();
	FOR one_value IN test_values LOOP
		one_type_value.id := one_value.id;
		one_type_value.info := one_value.info;
		
		test_A.extend;
		test_A(v_num) := one_type_value;
		
		raise notice 'v_num is: %', v_num;
		raise notice 'test_A.count is: %', test_A.count;
		
		v_num := v_num + 1;
	END LOOP;
END 
$$
LANGUAGE plsql;

　　

4.在匿名块中测试：

DO 
$BODY$
DECLARE 
	test_A test_type_Array;
	v_record_id varchar(32);
	v_info varchar(32);
BEGIN 
	v_record_id := '1';
	
	raise notice 'test object, like array. must use "in out":';
	test_A := test_type_Array();
	test_A := test_array_fnc(v_record_id, test_A);
	raise notice 'out: test_A.count is: %', test_A.count;
	
	raise notice '';
	raise notice 'execute again, the object value will in the function:';
	test_A := test_array_fnc(v_record_id, test_A);
	raise notice 'out: test_A.count is: %', test_A.count;
	
	--NOTICE:这样的用法，不会因为是in out参数而直接给参数赋值，相当于只是将参数传递进去了
	raise notice '';
	raise notice 'do not get the result:';
	test_array_fnc(v_record_id, test_A);
	raise notice 'out: test_A.count is: %', test_A.count;
	
	raise notice '';
	raise notice 'test object, like array. use "out":';
	test_A := test_array_fnc2(v_record_id);
	raise notice 'out: test_A.count is: %', test_A.count;
	
	v_info := 'init';
	raise notice '';
	raise notice 'test vachar, use "in out":';
	v_info := test_fnc(v_record_id, v_info);
	raise notice 'out: test_fnc: v_info is: %', v_info;
	
	raise notice '';
	raise notice 'test varchar, just use "out":';
	v_info := test_fnc2(v_record_id);
	raise notice 'out: test_fnc2: v_info is: %', v_info;
END 
$BODY$

　　

5.测试结果：

test object, like array. must use "in out":
v_num is: 1
test_A.count is: 1
v_num is: 2
test_A.count is: 2
out: test_A.count is: 2

execute again, the object value will in the function:
v_num is: 1
test_A.count is: 3
v_num is: 2
test_A.count is: 4
out: test_A.count is: 4
--上面可以看出，传入和传出都获取了相应的值。

do not get the result:
v_num is: 1
test_A.count is: 5
v_num is: 2
test_A.count is: 6 
out: test_A.count is: 4

test object, like array. use "out":
v_num is: 1
test_A.count is: 1
v_num is: 2
test_A.count is: 2
out: test_A.count is: 2
--在里面做了初始化，传出值直接获取

test vachar, use "in out":
info is: init_connect_1
out: test_fnc: v_info is: init_connect_1

test varchar, just use "out":
info is: _connect_1
out: test_fnc2: v_info is: _connect_1

　　

 

6.附加：Is table of：原生的使用方法，不需要转为数组和游标进行处理

--创建测试对象

DROP TYPE test_ty CASCADE ;
DROP TYPE test_ty_Array CASCADE ;

CREATE TYPE test_ty AS 
(
id int,
name varchar(32),
birthday timestamp
);

CREATE type test_ty_Array as table of test_ty ;

　　

 

--使用is table of直接使用，需要注意两个地方：
--1）需要对数组进行实例化。
--2）使用时，如果往里面加对象，需要先extend

DO
$$
DECLARE 
	ret_array test_ty_Array; 
	one_row test_ty;
	idx int;
	CURSOR ret_cur IS SELECT * FROM test;
BEGIN 
	idx := 1;
	ret_array := test_ty_Array();

    FOR one_ret IN ret_cur loop
		raise notice 'id is: %, name is: %, birthday is: %', one_ret.id, one_ret.name, one_ret.birthday;
		
    	one_row.id := one_ret.id;
    	one_row.name := one_ret.name;
    	one_row.birthday := one_ret.birthday;
		
    	ret_array.extend;
    	ret_array(idx) := one_row;
		
    	idx := idx + 1;
    END LOOP;
    
    raise notice '---------------------------------------------------------------------------------------------------------';
    FOR idx IN 1..ret_array.count loop
    	one_row = ret_array(idx);
    	raise notice 'idx: %, --> id is: %, name is: %, birthday is: %', idx, one_row.id, one_row.name, one_row.birthday;
    END loop;
END 
$$