本文实例讲述了postgreSQL存储过程用法。分享给大家供大家参考,具体如下:
转了N多的SQL语句,可是自己用时,却到处是坑啊,啊,啊!!!!!!!!!!!!!!!
想写一个获取表中最新ID值.
上代码
1
2
3
4
5
6
7
8
|
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的最大值:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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');
--出错!
|
看了官方文档,人家就是这么用的:
1
2
3
|
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
|
你确定你看清楚了?????
确定你读完读懂了说明书?????
1
2
3
4
5
6
7
|
--这个看了?
---------------------------------------
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
--这个看了?
---------------------------------------
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里看到的却是小写的
|
1
2
3
4
5
6
7
8
9
|
--同样,如果是在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)变量可以直接用 ||
拼接。上面没有列出,下面给个栗子:
1
2
3
4
5
6
7
8
9
10
11
12
|
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 存储过程示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
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;
|
希望本文所述对大家PostgreSQL程序设计有所帮助。
原文链接:https://www.cnblogs.com/ssqhan/p/7289931.html