postgresql函数-012存储过程-postgresql练习001

存储过程-练习001

语法结构：

create or replace function function_name ( arguments)
returns return_datatype as $variable_name$
	declare
		declaration;
		[...]
	begin
		< function_body>
			[...]
			return {variable_name | value }
	end;
language plpgsql;

案例：求一个stu表中有多少条数据。

根据上面的表来写一个简单地存储过程，将结果存储 stucount这个参数中。

create or replace function  stored_procedure.getstucount()
returns integer as 
$$
declare 
 stucount integer;
begin 
	select count(1) into stucount from stored_procedure.stu;
	return stucount;
end;
$$ language plpgsql;

select stored_procedure.getstucount() as getall;

sql实现字符串拼接

--3 存储过程， sql用字符串拼接 
create or replace function 
stored_procedure.mycount3(schemaName text,tableName text,columnName text)
returns text as $$
declare 
	mysql text;
	--stucount integer;
	
begin 
	mysql:='select count('
				|| quote_ident(columnName)
				|| ') from '
				|| quote_ident(schemaName)
				||'.'
				|| quote_ident(tableName)
				|| ';';
	--execute mysql into stucount;
	return  mysql;--,stucount;
end;
$$ language plpgsql;

select stored_procedure.mycount3('stored_procedure','stu','name');

返回多个参数结果值 使用 out

create or replace function 
stored_procedure.mycount4(schemaName text,tableName text,columnName text,
				out mysql text,out stucount text)
as $$
begin 		
	mysql:='select count('
				|| quote_ident(columnName)
				|| ') from '
				|| quote_ident(schemaName)
				||'.'
				|| quote_ident(tableName)
				|| ';';
	execute mysql into stucount;
	
	return;
end; 
$$
language plpgsql volatile 
cost 100;

select stored_procedure.mycount4('stored_procedure','stu','name');

返回量sql语句和stu表中的数据量。