plpgsql

简介

PL/pgSQL是一种程序语言，叫作过程化语言（Procedural Language），是postgres对SQL语句的扩展。

HelloWorld

postgres=# do $$
postgres$# declare
postgres$# begin
postgres$# raise notice 'Hello World';
postgres$# end $$;
NOTICE:  Hello World
DO
postgres=#

do $$的作用是将SQL语句的结束符由;修改为$$，这样在代码块中就不会因为将;解释成语句的结束而引起错误。

基本数据类型

-- 数值类型
quantity1 numeric;        -- 任何精度的数值
quantity2 numeric(4);     -- 4个精度的整数
quantity3 numeric(6,2);   -- 6个精度的数字，小数点后保留2位

-- 整数类型
uid1 smallint;  -- 2字节整数
uid2 integer;   -- 4字节整数
uid3 bigint;    -- 8字节整数

-- 字符串类型
name1 varchar(10);  -- 变长字符串
name2 char(10);     -- 定长字符串 
name3 text;         -- 无限变长字符串

示例

do $$
declare
  pename varchar(10);
  psql numeric(7,2);
begin
  -- 执行查询语句, 并将查询语句赋值给变量
  select ename,sal into pename,psql from emp where empno=1000;
  -- 打印
  raise notice '% 的工资是: %', pename,psql;
end;
$$;

高级数据类型

• 复制类型：使用%type定义一个与变量或表列类型相同的数据类型
• 行类型：将变量定义为行类型可以保存使用select命令或for命令查询到结果中的一整行
• 记录类型：记录变量和行类型变量类型，但是没有预定义的结构，而是采用在一条select命令或for命令中为其赋值的行的真实结构。

流控制

条件结构

-- 1
if condition then
    expression1;
    expression2;
end if;

-- 2
if condition then
    expression1;
    expression2;
else
    expression3;
    expression4;
end if;

-- 3
if condition then
    expression1;
    expression2;
elsif
    expression3;
    expression4;
else
    expression5;
    expression6;
end if;

示例

do $$
declare
    pnum int := 2;
begin
    if pnum = 0 then raise notice 'pnum is 0';
        elsif pnum = 1 then raise notice 'pnum is 1';
        elsif pnum = 2 then raise notice 'pnum is 2';
        else raise notice 'other number';
    end if;
end;
$$;

循环结构

-- 1
while condition
loop
    expression1;
    expression2;
end loop;

-- 2
loop
exit [when 退出条件]
    expression1;
    expression2;
end loop;

-- 3
for I in 1...3
loop
    expression1;
    expression2;
end loop;

示例

do $$
    declare
        pnum int := 1
    begin
        loop
            exit when pnum > 10;
            raise notice '%',pnum;
            pnum := pnum + 1;
        end loop;
    end;
$$;

使用游标

简单示例

do $$
    declare
        -- 定义游标, 游标名为cemp, 查询员工的姓名和工资
        cemp cursor for select ename,sal from emp;
        -- 使用复制类型
        pename emp.ename%type;
        psal emp.sal%type;
    begin
        open cemp;  -- 使用游标前需要打开
        loop
            -- 使用fetch关键字从游标中获取一条记录
            fetch cemp into pename,psql;
            -- 循环直到没游标未获取到记录
            exit when not found;
            -- 输出
            raise notice '% salary is %',pename,psql;
        end loop;
        close cemp;  -- 使用完需要关闭
    end;
$$;

定义游标还可以指定参数

do $$
    declare
        -- 定义游标, 游标名为cemp, 查询员工的姓名和工资
        cemp cursor (dno int) for select ename,sal from emp where deptno=dno;
        -- 使用复制类型
        pename emp.ename%type;
        psal emp.sal%type;
    begin
        open cemp(20);
        loop
            -- 使用fetch关键字从游标中获取一条记录
            fetch cemp into pename,psql;
            -- 循环直到没游标未获取到记录
            exit when not found;
            -- 输出
            raise notice '% salary is %',pename,psql;
        end loop;
        close cemp;
    end;
$$;

捕获异常

nil

编写存储过程

nil

编写存储函数

nil

编写触发器

nil