PostgreSQL存储过程⽤法实战详解本⽂实例讲述了postgreSQL存储过程⽤法。分享给⼤家供⼤家参考，具体如下：
转了N多的SQL语句，可是⾃⼰⽤时，却到处是坑啊，啊，啊
想写⼀个获取表中最新ID值.
上代码
CREATE TABLE department(
ID INT PRIMARY KEY NOT NULL,
d_code VARCHAR(50),
d_name VARCHAR(50) NOT NULL,
d_parentID INT NOT NULL DEFAULT 0
);
--insert into department values(1,'001','office');
--insert into department values(2,'002','office',1);
下⾯要写个存储过程，以获取表中ID的最⼤值：
drop function f_getNewID(text,text);
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
mysql text;
myID integer;
begin
mysql:='select max( $1 ) from $2';
execute mysql into myID using myFeildName,myTableName;
if myID is null or myID=0 then return 1;
else return myID+1;
end if;
end;
$$ language plpgsql;
--⼤家可以试⼀下，上⾯这个是会报错的
--select f_getNewID('department','ID');
--出错！
看了官⽅⽂档，⼈家就是这么⽤的：
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
你确定你看清楚了
确定你读完读懂了说明书
--这个看了？
---------------------------------------
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
--这个看了？
---------------------------------------
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);
--=============================
--好吧，我改
------------------------------------------------------
drop function f_getNewID(text,text);
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
mysql text;
myID integer;
begin
mysql:='select max('
|| quote_ident(myFeildName)
||') from '
|| quote_ident(myTableName);
execute mysql into myID;
--using myTableName,myFeildName;
if myID is null or myID=0 then return 1;
else return myID+1;
end if;
end;
$$ language plpgsql;
--==============================
--漂亮，成功了！
--But Why?
--注意对象（表名、字段名等）是不可以直接⽤变量的，要⽤ quote_ident()
-------------------------------------------------------
postgres=# select f_getnewid('department','ID');
--错误: 字段 "ID" 不存在
--第1⾏select max("ID") from department
^
--查询: select max("ID") from department
--背景: 在EXECUTE的第10⾏的PL/pgSQL函数f_getnewid(text,text)
--===============================
--什么情况，ID怎么会有双引号，引号，号，号
----------------------------------------------------------
--这⾥要感谢⼤神：权宗亮@飞象数据
--改成这样：
postgres=# select f_getnewid('department','id');
f_getnewid
------------
2
(1 ⾏记录)
----终于成功了！⼤⼩写还有区别吗 --but why? --当在命令⾏输⼊
CREATE TABLE role(
ID INT PRIMARY KEY NOT NULL,
r_name VARCHAR(50) NOT NULL,
r_paretnID INT NOT NULL DEFAULT 0
);
--结果在pgAdmin⾥看到的却是⼩写的
--同样，如果是在QUERY TOOLS 下⽤这样的语句创建还是所有的字体名为⼩写
--如果我就想⼤写怎么办
-
-要这样写
CREATE TABLE "RoleUPER"(
"ID" INT PRIMARY KEY NOT NULL,
r_name VARCHAR(50) NOT NULL,
"r_paretnID" INT NOT NULL DEFAULT 0
);
--再⽤⼤象看看
可以了！
总结⼀下：
1、存储过程（FUNCITON）变量可以直接⽤ || 拼接。上⾯没有列出，下⾯给个栗⼦：create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$ declare
mysql text;
myID integer;
begin
mysql:='select max('|| $2 || ' ) from '||$1;
execute mysql into myID using myFeildName,myTableName;
if myID is null or myID=0 then return 1;
else return myID+1;
end if;
$$ language plpgsql;
2、存储过程的对象不可以直接⽤变量，要⽤quote_ident(objVar)
3、$1 $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后⾯的不换结果：
select max(myTableName) from myFeildname
4、注意：SQL语句中的⼤写全部会变成⼩写，要想⼤写存⼤，必须要⽤双引号。
附：⼀个完整postgreSQL 存储过程⽰例
CREATE OR REPLACE FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
RETURNS integer AS
$BODY$
DECLARE
v_start_hour character varying;
v_end_hour character varying;
v_start_time character varying;
v_end_time character varying;
v_start_datetime timestamp;
v_end_datetime timestamp;
v_type int := 0;
v_rtn int;
/*
v_test9_count int;
v_test9_success int;
v_runningtime double precision;
v_availablerate double precision;
*/
BEGINpostgre trunc函数的使用方法
-- hour = even, minute > 30
-
- exists
--
if i_type = 1 then
SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour; --two hours ago
SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour; --get 'hour' of current time
select v_start_hour || ':30' into v_start_time;
select v_end_hour || ':30' into v_end_time;
select mt_f_avl_oee_period_e(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;
else
SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '3 hours' ) into v_start_hour;
SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
select v_start_hour || ':30' into v_start_time;
select v_end_hour || ':00' into v_end_time;
select mt_f_avl_oee_period_midnight(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn; end if;
RETURN 1;
EXCEPTION
WHEN others THEN
RAISE;
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
OWNER TO postgres;
希望本⽂所述对⼤家PostgreSQL程序设计有所帮助。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论