【greenplum】greenplum的plpgsql：函数，存储过程，赋值语句，DML语句，静态语句与动态语句，流程控制语句...

--greenplum 的plpgsql
注意:
●greenplum 的plpgsql 与 postgresql 不是完全兼容的。
●plpgsql 不明确界定 函数和存储过程，“returns void” 表示 存储过程，“return 数据类型” 表示 函数。
●greenplum中的函数分成3种类型: immutable(不可变更的)，stable(稳定的),volatile(易变的)。用户自定义的函数若没特别声明，默认是volatile的。
●plpgsql 不使用 commit，rollback 语句，否则运行时会报错，但是子程序可以编译成功即创建成功。
●plpgsql 的动态语句 格式“execute <varchar变量的sql语句>”;plpgsql没有 execute [into <variable_list>] [using <variable_list>] 的写法,而ORACLE的动态语句有 execute immediate [into <variable_list>] [using <variable_list>] 的写法。
●plpgsql 不使用 游标，而是用 for 循环代替。
●plpgsql 的异常信息 变量: sqlstate,sqlerrm。没有sqlcode。
DB2 的 异常信息 变量:sqlcode,sqlstate,message_text。
ORACLE 的 异常信息 变量:sqlcode,sqlerrm。

 

【greenplum的赋值语句 与 赋值符】
1.赋值语句
select into [from];
说明:可以带from子句，也可以不带。
2.赋值符
变量 := 值;

【greenplum的DML语句】
1.select
select into [from] ;
2. insert,update,delete
静态语句，动态语句

DDL语句:建表，建函数等
动态语句

TCL语句:
注意: plpgsql中使用TCL语句，可以编译成功，但运行会报错 ERROR:can not begin/end transactions in PL/pgSQL。

 

【greenplum】的 函数 与 存储过程

说明：plpgsql 里 函数和存储过程定义的关键字都是function，只是 函数 returns <数据库类型> ，存储过程 returns void 。

 

【greenplum 存储过程】
--建表
create table zzhtest.t_int_tesr(
c1 integer,
c2 integer
) distrobuted by (c1);

select * from zzhtest.t_int_test;

--创建 存储过程
drop function zzhtest.prc_test(integer,integer);

create or replace function zzhtest.prc_test(
in ii_c1 integer,
in ii_c2 integer
)
returns void as

$$begin insert into zzhtest.t_int_test(ii_c1,ii_c2); end;$$ language plpgsql;

--psql调用 存储过程
select zzhtest.prc_test(1,200); 
select * from zzhtest.t_int_test;

 

【greenlum  函数】
--创建
create function zzhtest.dup(int)
returns integer as

$$select $1 + 1000$$
language sql;

--psql调用
select * from zzhtest.dup(42);

 

 【greenplum 调用 函数】

1. psql语句
select <schema>.<function_name>(<parameter_list>);
2. plpgsql语句
<variable> := <schema>.<function_name>(<parameter_list>);

【greenplum 调用 存储过程】
1. psql语句
select <schema>.<function_name>(<parameter_list>);

 

【plpgsql 的流程控制语句】

说明: greenplum子程序中的 流程控制语句 与 oracle 写法一样。

1. for 循环 代替 游标(plpgsql中没有游标) 
--复制表结构
create table zzhtest.t_brand_bak as select * from zzhtest.t_brand where 1=2;
--创建 函数
drop function zzhtest.fnc_test();
create or replace function zzhtest.gnc_test()
returns varchar as

$$declare rec record; vv_result varchar(200); begin for rec in (select brand_code,brand_name from zzhtest.t_brand ) loop insert into zzhtest.t_brand_bak values(rec.brand_code,rec.brand_name); end loop; vv_result := 'successful'; return vv_result; exception when others then vv_result := substr(sqlstate ||':'|| sqlerrm,1,200);  end;$$ language plpgsql;

select zzhtest.fnc_test();
select * from zzhtest.t_brand;
select * from zzhtest.t_brand_bak;
说明: loop variable of loop over rows must be a record or row variable or list of scalar variables。

--2. for i in n1..n2 loop end loop 
drop function zzhtest.fnc_test();
create or replace function zzhtest.gnc_test()
returns integer as

$$declare vi_sum integer default 0; begin for i in 1..100 loop --此处i无需定义 vi_sum := vi_sum + i; end loop; return vi_sum; exception when others then return -1; end;$$ language plpgsql;

select zzhtest.fnc_test();

--while loop end loop
drop function zzhtest.fnc_test();
create or replace function zzhtest.gnc_test()
returns integer as

$$declare vi_i integer; vi_sum integer default 0; begin vi_i := 100; while vi_i > 0 loop  vi_sum := vi_sum + vi_i; vi_i := vi_i - 1; end loop; return vi_sum; exception when others then return -1; end;$$ language plpgsql;

select zzhtest.fnc_test();

--分支 if then elsif then else end if
drop function zzhtest.fnc_test(integer,integer);

create or replace function zzhtest.gnc_test(
in iv_a integer,
in iv_b integer
)
returns varchar as

$$declare vv_result varchar(10); begin if iv_a = iv_b then vv_result := iv_a ||'与'|| iv_b ||'相等'; elsif iv_a > iv_b then vv_result := iv_a ||'大于'|| iv_b; else vv_result := iv_a ||'小于'|| iv_b; end if; return vv_result; exception when others then vv_result := substr(sqlstate ||':'|| sqlerrm,1,200);  return vv_result; end;$$ language plpgsql;

select zzhtest.fnc_test(100,58);
select zzhtest.fnc_test('0100',58);
select zzhtest.fnc_test('a',58);

 

 

【greenplum 的 静态语句 和 动态语句】
drop function zzhtest.fnc_test(varchar,varchar);

create or replace function zzhtest.fnc_test(
in iv_brand_code varchar,
in iv_brand_name varchar
)
returns varchar as $$
declare vi_cnt integer;
declare vv_sql varchar(100);
declare vv_result varchar(200);
begin
--select into语句
select count(*) into vi_cnt from zzhtest.t_brand where brand_code = upper(iv_brand_code);
--greenplum子程序中没有 execute into 的写法
--vv_sql := ' select count(*) from zzhtest.t_brand where brand_code = upper('''||iv_brand_code||''')';
--execute vv_sql into vi_cnt;

if vi_cnt = 0 then
--insert into zzhtest.t_brand values(upper(iv_brand_code),upper(iv_brand_name)); --静态语句
vv_sql := ' insert into zzhtest.t_brand values(upper('''||iv_brand_code||'''),upper('''||iv_brand_name||'''))';
execute vv_sql; --动态语句
else
--update zzhtest.t_brand set brand_name = upper(iv_brand_name) where brand_code = upper(iv_brand_code); -- 静态语句
vv_sql := 'update zzhtest.t_brand set brand_name = upper('||quote_literal(iv_brand_name)||') where brand_code = upper('||quote_literal(iv_brand_code)||') ';
execute vv_sql; --动态语句
end if;
vv_result := 'successful';
return vv_result;
exception
when others then
vv_result := substr(sqlstate ||':'|| sqlerrm,1,200); --异常信息
end;
$$ language plpgsql;

select zzhtest.fnc_test('b01','oppo');
select zzhtest.fnc_test('b01','sunxing');
select * from zzhtest.t_brand;
truncate table zzhtest.t_brand;

说明: greenplum 子程序中的'可以用2个'表示，即 '''';而一对'可以用greenplum的函数 quote_literal()处理，以简化阅读。