第十节——pluxsql

Pluxsql学习

一、pluxsql组成部分

1. 声明部分declare：在此声明pl/uxsql用到的变量、类型及游标，这一部分是可选。
2. 执行部分begin statements end：执行部分 过程及SQL语句，即程序的主要部分
3. 异常部分exception：执行一场部分

二、Pluxsql使用

1. 结构：

CREATE FUNCTION 函数名() RETURNS 返回值类型 AS $$  //函数名声明
<< label >>  //可选 
DECLARE  
//声明部分
BEGIN 
//执行部分，statements可选
return 变量名; //变量的数据类型应与返回值类型一致
END;
$$ LANGUAGE pluxsql;  //指明使用的语言，与开头的$$对应

2. 执行函数，语句：select 函数名();
3. 函数删除，语句：drop function 函数名();
4. 查看已创建的函数：\df
5. 例子：定义一个test函数，定义一个i=10变量，并打印

create function test() returns int as $$
declare
i int:=1;
begin
raise notice 'i=%',i;
return i;
end;
$$ language pluxsql;
//执行函数：
select test();

三、创建带参函数

1. 指定参数名的创建，语法：

create function 函数名(变量名 数据类型) returns 返回值类型 AS $$
begin
函数体
return 变量名;
end;
$$ language pluxsql;

2. 例子：

create function fun(a int) returns integer AS $$
begin
raise notice 'quantity is %',a;
return a;
end;
$$ language pluxsql;

3. 创建函数时，仅指定数据类型，不指定参数名，使用时用$1指代，例子：

create function fun(int) returns integer AS $$
begin
raise notice 'quantity is %',$1;  //使用$1指代第一个，$2指代第二个，只适用于函数名()内省略
return $1;
end;
$$ language pluxsql;

四、修改函数里面的变量的名称

1. 语法：nwename ALIAS FOR oldname;，用在declare声明里面
2. 例子：将带参函数的变量a改名为b并打印

create function test(a int) returns int as $$
declare 
b alias for a;
begin
raise notice 'b=%',b;
return b;
end;
$$ language pluxsql;

五、使用数据库里面的数据类型

1. %TYPE可以获取一个变量或表列的数据类型。
2. %rowtype：被称为一个行变量（或行类型变量），可以通过以下方式声明一个行变量具有和一个现有表或视图的行相同的类型。
3. 例子（使用列的数据类型）：创造一个person表，有id(int)和name(text)字段，创造一个test函数，a变量使用id的数据类型，b变量使用name的数据类型
      -查看person表的构成
   
      -创建函数test，a变量使用id的数据类型，b变量使用name的数据类型

create function test() returns int as $$
declare 
a person.id%type;
b person.name%type;
begin
a :=2;
b :='asd';
raise notice 'a=%',a;
raise notice 'b=%',b;
return 1;
end;
$$ language pluxsql;

   -执行函数，查看结果

4. 例子（使用行变量）：建立一个test函数，使用person表的行变量，获取person表中的数据

create or replace function test() returns int as $$ 
declare 
a person%rowtype;
result text;
BEGIN  
select * into a from person where id=1; 
result :=a.id||','||a.name;
raise notice 'result=%',result;
return 0;
END;
 $$ LANGUAGE pluxsql; 

六、记录变量

1. record：记录变量，并非一个真正的数据类型，只是一个占位符。它没有预定义的结构，只能通过一个SELECT或FOR命令来获得真实的行结构
2. 例子：创建一个函数test，新建一个记录变量result，使用result变量获取person表中的数据

create or replace function test() returns varchar as $$ 
declare 
result record; 
BEGIN  
select * into result from person where id=1;  
return result.id||','||result.name; 
END; 
$$ LANGUAGE pluxsql;

七、动态命令

1. 概念：用于在执行sql语句时，不直接设置常量，通过设置变量获取数值。
2. 用法：EXECUTE sql语句 [ INTO target ] [using var1,var2,……]
3. 例子：创建test函数，定义id变量，通过execute执行sql语句，将结果放进rec记录变量中，通过id控制获取的数据行

CREATE or replace FUNCTION test() RETURNS varchar as $$ 
DECLARE
id int:=1;
rec record;
BEGIN  
execute 'select * from person where id =$1 ' into rec using id; 
return rec.id || ','|| rec.name; 
END; 
$$ LANGUAGE pluxsql;

八、if语句

1. 语法：if-then-else-end if，当if里面的条件为真时，执行then里面的函数体；fi里的条件为假时，执行else里面的函数体，end if为if的结束标志。
2. 例子，定义一个变量i，若i>0则打印正数，否则打印非正数

CREATE or replace FUNCTION test(i int) RETURNS varchar as $$ 
BEGIN  
if i>0
then
return '正数';
else
return '非正数';
end if;
END; 
$$ LANGUAGE pluxsql;