postgre 存储过程写法 pgsql 存储过程

PostGressql存储过程

1.创建存储过程

1.1样例

create or replace function  Name (类性 名 ，类性 名)

return s 类性 as 
$$
declare 
	变量1 类性 :=0;
	变量2 类性 :=0;
	...
begin 
	逻辑处理
end
$$
language 'plsql';

--调用存储过程
select 存储过程名 (参数1,参数2);

1.2结构

<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
--
--  创建一个子块
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;

重要的是不要把 PL/pgSQL 中用来分组语句的 BEGIN/END 与用于事务控制的同名 SQL 命令弄
混。PL/ pgSQL 的 BEGIN/END 只用于分组，它们不会开始或结束一个事务

2.声明

在一个块中使用的所有变量必须在该块的声明小节中声明
唯一的例外是在一个整数范围上迭代的 FOR 循环变量会被自动声明为一个整数变量，并且相似地在一个游标结果上迭代的 FOR 循环变量会被自动地声明为一个记录变量

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
--声明一个记录变量，没有具体结构，只有在被赋值的时候才有具体结构，并且运行时可以改变
arow RECORD;
--一般语法
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := |
= } expression ];

• 如果给定 DEFAULT 子句，它会指定进入该块时分 配给该变量的初始值。如果没有给
  出 DEFAULT 子句， 则该变量被初始化为 SQL 空值
• CONSTANT 选项阻止该变量在初始化之后被赋值， 这样它的值在块的持续期内保持不变
• COLLATE 选项指定用于该变量的一个排序规则
• 例子

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

3.赋值

--静态赋值
变量1 := '我是变量1';

--动态赋值
select money into 变量1 from bank where id = 1;

execute 'select money from bank where id = 1' into 变量1;

4.执行一个有单一行结果的查询

一个产生单一行（可能有多个列）的 SQL 命令的结果可以被赋值给一个记录变量、行类型变
量或标量变量列表。这通过书写基础 SQL 命令并增加一个 INTO 子句来达成

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

• 其中 target 可以是一个记录变量、一个行变量或一个有逗号分隔的简单变量和记录/行域列
  表。PL/ pgSQL 变量将被替换到该查询的剩余部分中，并且计划会被缓存。
• 如果一行或一个变量列表被用作目标，该查询的结果列必须完全匹配该结果的结构，包括数
  量和数据类型，否则会发生一个运行时错误。当一个记录变量是目标时，它会自动地把自身
  配置成查询结果列组成的行类型。
• INTO 子句几乎可以出现在 SQL命令中的任何位置。通常它被写成刚好在 SELECT 命令中
  的 select_expressions 列表之前或之后，或者在其他命令类型的命令最后。
• 如果 STRICT 没有在 INTO 子句中被指定，那么 target 将被设置为该查询返回的第一个行，或者
  在该查询不返回行时设置为空
• 第一行之后的任何结果行都会被抛弃。你可以检查特殊的 FOUND 变量来确定是否返回了一行：

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;

• 如果指定了 STRICT 选项，该查询必须刚好返回一行或者将会报告一个运行时错误，该错误
  可能是 NO_DATA_FOUND （没有行）或 TOO_MANY_ROWS （多于一行）。如果你希望捕
  捉该错误，可以使用一个异常块，例如：

BEGIN
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee % not unique', myname;
END;

• 如果为该函数启用了 If print_strict_params ，那么当因为 STRICT 的要求没有被满足而抛出一个错误时，该错误消息 的 DETAIL 将包括传递给该查询的参数信息。可以通过设 plpgsql.print_strict_params 为所有函数更改 print_strict_params 设置，但是只有修改后被编译的函数 才会生效。
• 也可以使用一个编译器选项来为一个函数启用它，例如：

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
SELECT users.userid INTO STRICT userid
FROM users WHERE users.username = get_userid.username;
RETURN userid;
END
$$ LANGUAGE plpgsql;
--失败时，这个函数会产生一个这样的错误消息
ERROR: query returned no rows
DETAIL: parameters: $1 = 'nosuchuser'
CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement

5.基本流程语言

1. if 语句:

if ... then ... ;
end if ;


if ... then ...;
 else ... ;
end if ; 

if ... then ... ;
else ... then ...; 
else ... then ... ;
end if ;

2. case 语句:

case ... when ... then ...;
else ...;
end case ; 

case when ... then ... ; else ...;
end else ;

3. 循环 ：

loop 
	循环体语句 ;
	exit when 判断表达式;
end loop ;
raise notice 'loop 循环';

while 判断表达式 loop
	循环体语句;
end loop ;
raise notice 'while 循环';

for 循环控制变量 in 循环范围 loop
	循环体语句;
end loop;
--例
if i in 1 .. 100 loop
sum := sum + i ;
end loop ;

for i in 1 .. 100 by 2(步长) loop 
	sun := sum + i ;
end loop ;