PostgreSQL存储过程用法实战详解-华尔子博-奥夏网

postgreSQL 存储过程用法。具体如下：

写一个获取表中最新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 $$

declare

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;

end;

$$ language plpgsql;

2、存储过程的对象不可以直接用变量，要用 quote_ident(objVar)

3、$1 $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后面的不换结果：

1	`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;

*/

BEGIN

-- 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;

本文刊载的所有内容，包括文字、图片、音频、视频、软件、程序、以及网页版式设计等部门来源于互联网，版权均归原作者所有！本网站提供的内容服务于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯本网站及相关权利人的合法权利。
联系信息：邮箱aoxolcom@163.com或见网站底部。

THE END