PostgreSQL存储过程用法实战详解
更新时间:2020年7月11日 17:22 点击:2025
本文实例讲述了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 $$ 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 后面的不换 结果 :
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;
希望本文所述对大家PostgreSQL程序设计有所帮助。
相关文章
- Create Procedure AtoC @ChangeMoney Money as Set Nocount ON Declare @String1 char(20) Declare @String2 char(30) ...2016-11-25
- 这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
- 存储过程在数据库的应用中我们用到的非常的多了,下面我们来看一篇关于PHP操作MSSQL存储过程修改用户密码的例子,具体的如下所示。 mssql2008 存储过程 下面可以直接...2016-11-25
- 这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
- 这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
- 这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
- 这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- 具体详情请看下文小编给大家带来的知识点。同编写程序类似,存储过程中也有对应的条件判断,功能类似于if、switch。在MySql里面对应的是IF和CASE1、IF判断IF判断的格式是这样的:IF expression THEN commands [ELSEIF ex...2015-10-21
- 这篇文章主要介绍了PostgreSQL 字符串处理与日期处理操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 这篇文章主要介绍了postgresql重置序列起始值,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-04
- 这篇文章主要介绍了postgresql 中的时间处理小技巧(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-29
- 通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning。比如,我们可以按时间切分,每天一张子表,比如我们可以按照某其他字段分割,总之了就是化整为零,提高查询的效能...2020-07-11
- 这篇文章主要介绍了基于postgresql数据库锁表问题的解决,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-30
- 本文章来给大家详细介绍在php中如何来调用执行mysql存储过程然后返回由存储过程返回的值了,有需要了解的同学可进入参考。 。调用存储过程的方法。 a。如果存储过...2016-11-25
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 这篇文章主要介绍了PostgreSQL 中的单引号与双引号用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 这篇文章主要介绍了Postgresql数据库之创建和修改序列的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- 这篇文章主要介绍了postgresql影子用户实践场景分析,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-06
- 这篇文章主要介绍了Postgresql中xlog生成和清理逻辑操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
- 这篇文章主要介绍了postgresql 利用xlog进行热备操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16