SQL笔记 | PostgreSQL存储过程的快速入门

目录

基础语法

编写存储过程

调用存储过程

选择语句

if语句

 CASE语句

LOOP循环语句

WHILE语句

FOR循环语句

整合SQL

统计某张表具有多条数据

查询并返回多条记录(结果集)

---

基础语法

编写存储过程

简单例子：输入a，得到一个等于a的新值

CREATE OR REPLACE FUNCTION get(a int4)
RETURNS int4 AS $BODY$
declare
	b integer := 0;
begin
	b = a;
	return b;
end
$BODY$
  LANGUAGE plpgsql;

ctrl+s保存存储过程，postgre会自动格式化为

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin
	b = a;
	return b;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE OR REPLACE FUNCTION get(a int4)  //创建或者更新函数 get为函数名
RETURNS int4 AS $BODY$ //指定返回值数据类型
declare
    b integer := 0; //申明变量
begin //开始处理数据
    b = a;
    return b;
end
$BODY$
  LANGUAGE plpgsql;

调用存储过程

SELECT get(1)

输出

get

1

选择语句

if语句

如果当a=1时输出b=1，其他情况b=2

语法

(注意此处应该为elsif 非esle if)

if 条件 then 结果1;
ELSIF 条件 then 结果2;
ELSE 结果3;
END IF;

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin
  if a = 1 then b = 1;
	ELSE b = 2;
	end if;
	return b;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

 CASE语句

语法

 CASE 属性 WHEN 值 then 结果1;
 ELSE 结果2;
 END CASE;

等价

CASE WHEN 条件 then 结果1;
ELSE 结果2;
END CASE;

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin
  CASE a WHEN 1 then b = 1;
	ELSE b = 2;
	end case;
	return b;	
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

LOOP循环语句

语法

LOOP
    循环语句;

    IF 条件 THEN
        EXIT; 
    END IF; 
END LOOP;

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin
  LOOP
	    b = b + a + 1;
	IF b > 10 THEN
		EXIT; 
	END IF; 
END LOOP;
	return b;	
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

WHILE语句

语法

WHILE search_condition LOOP
    statement_list；
END LOOP;

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin
WHILE  b < 10 LOOP
	   b = b + a + 1;
END LOOP;
	return b;	
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

FOR循环语句

语法

//表示var从1到10

FOR var IN 1..10 LOOP
    statement_list；
END LOOP;

CREATE OR REPLACE FUNCTION "public"."get"("a" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	b integer := 0;
begin

FOR i IN 1..10 LOOP
	b = b + i + a + 1;
END LOOP;
	return b;	
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

整合SQL

统计某张表具有多条数据

CREATE OR REPLACE FUNCTION "public"."get"()
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
	total integer := 0;
begin
  SELECT COUNT(*) INTO total from student;
	return total;	
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

查询并返回多条记录(结果集)

查询多行单列的情况

CREATE OR REPLACE FUNCTION "public"."get"()
  RETURNS SETOF "pg_catalog"."varchar" AS $BODY$
declare
	res VARCHAR;
begin
for res in
  SELECT  role_name FROM role
	LOOP
	    RETURN NEXT res;
			END LOOP;
return;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

查询多行多列的情况

CREATE OR REPLACE FUNCTION "public"."get"()
  RETURNS SETOF "public"."role" AS $BODY$
declare
	res role;
begin
for res in
  SELECT * FROM role
	LOOP
	    RETURN NEXT res;
			END LOOP;
return;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

输出结果为

get

(1,admin)
(2,teacher)
(3,student)

 或者使用record

CREATE OR REPLACE FUNCTION "public"."function1"()
  RETURNS SETOF "public"."role" AS $BODY$ 
DECLARE 
result record; 
BEGIN 
for result in select * from role loop 
return next result; 
end loop; 
return; 
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000