作者
digoal
日期
2021-05-22
标签
PostgreSQL , ecpg , oracle
背景
支持declare statement语法:
1、兼容Oracle, 方便迁移Oracle的proc程序. 大量应用在金融领域.
2、支持AT connection, 调用exec SQL ID by declared statement时, 不需要再写AT conn, 内部会帮你预连接到对应DB去执行.
https://www.postgresql.fastware.com/blog/adding-support-to-declare-statement-in-ecpg
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/ecpg/test/sql/declare.pgc;h=f9ef46874131d1241d89b2db14f6fffc59b039ba;hb=ad8305a43d1890768a613d3fb586b44f17360f29
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ad8305a43d1890768a613d3fb586b44f17360f29
```
Add DECLARE STATEMENT command to ECPG
author Michael Meskes meskes@postgresql.org
Wed, 24 Mar 2021 19:48:20 +0000 (20:48 +0100)
committer Michael Meskes meskes@postgresql.org
Wed, 24 Mar 2021 20:09:24 +0000 (21:09 +0100)
commit ad8305a43d1890768a613d3fb586b44f17360f29
tree f8873fcf8ba95baa0a258ccaaaa905715587d742 tree
parent 37c99d304dcbf12ab581ff031f394af93b750895 commit | diff
Add DECLARE STATEMENT command to ECPG
This command declares a SQL identifier for a SQL statement to be used in other
embedded SQL statements. The identifier is linked to a connection.
Author: Hayato Kuroda kuroda.hayato@fujitsu.com
Reviewed-by: Shawn Wang shawn.wang.pg@gmail.com
Discussion: https://www.postgresql.org/message-id/flat/TY2PR01MB24438A52DB04E71D0E501452F5630@TY2PR01MB2443.jpnprd01.prod.outlook.com
```
https://www.postgresql.org/docs/devel/ecpg-sql-declare-statement.html
+#include <stdio.h>
+
+EXEC SQL BEGIN DECLARE SECTION;
+char dbname[128];
+char *dym_sql = "SELECT current_database()";
+EXEC SQL END DECLARE SECTION;
+
+int main(){
+ EXEC SQL CONNECT TO postgres AS con1;
+ EXEC SQL CONNECT TO testdb AS con2;
+ EXEC SQL AT con1 DECLARE stmt STATEMENT;
+ EXEC SQL PREPARE stmt FROM :dym_sql;
+ EXEC SQL EXECUTE stmt INTO :dbname;
+ printf("%s\n", dbname);
+
+ EXEC SQL DISCONNECT ALL;
+ return 0;
+}
+EXEC SQL CONNECT TO postgres AS con1;
+EXEC SQL AT con1 DECLARE sql_stmt STATEMENT;
+EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt;
+EXEC SQL PREPARE sql_stmt FROM :dyn_string;
+EXEC SQL OPEN cursor_name;
+EXEC SQL FETCH cursor_name INTO :column1;
+EXEC SQL CLOSE cursor_name;
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
