移植Oracle procedure 到 postgresql
1.登录postgresql
psql -h 192.168.137.131 -p 5432 postgres sa
tusc@6789#JKL
2.创建用户
CREATE USER name thunisoft createdb; --(equal CREATE ROLE name LOGIN createdb);
3.创建数据库
create database test_database owner = thunisoft;
4.查看帮助 psql 下敲help
[thunisoft@localhost ~]$ psql test_database psql (9.3.6) Type "help" for help. test_database=> help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test_database=>
5.与其他数据库的类比
a.支持大多数的标准SQL.
b.支持复杂查询、外键、多版本并发控制、事务....
c.数据类型和函数十分丰富.
6.连接方式可以用Navicate Premium,当然还有自带的psql类似Oracle的sqlplus.
7.将Oracle存储过程迁移到PostgreSql 上.
PostgreSQL 支持多种过程语言,PostgreSQL原生的过程语言是pl/pgsql,还支持PL/Python,PL/perl等.
这里我们演示PL/pgsql（对原始脚本进行了删减和重命名）
Porcedure in Oracle:
1 --抽数到结果表 2 procedure scheme_test_etl(in_scheme_id varchar2, 3 in_bbq varchar2, 4 table_Prefix varchar2) is 5 v_error_rows number; 6 v_row_cnt number; 7 v_bbq varchar2(10); 8 v_model varchar2(30); 9 v_table varchar2(32); 10 v_sql varchar2(3000); 11 12 begin 13 v_bbq := in_bbq; 14 v_model := table_Prefix; 15 16 /*临时表数据清理*/ 17 execute immediate 'truncate table TEMP_EDQ10_DIM_LEVEL_ANAYS'; 18 19 /*抽数到临时表*/ 20 v_sql := 'insert into TEMP_EDQ10_DIM_LEVEL_ANAYS select * from ' || 21 v_model || '_DIM_LEVEL_ANAYS'; 22 execute immediate v_sql; 23 24 --被检测表信息 25 v_table := v_model || '_checkedtb'; 26 execute immediate 'delete from ' || v_table || 27 ' t where t.schemeid_ = ' || chr(39) || in_scheme_id || 28 chr(39) || ' and t.bbq =' || v_bbq; 29 for c in (select t.PERIOD_ BBQ_, 30 t.SCHEMEID_, 31 t.HIERARCHY_, --数据级次 32 t2.facttableid_, --表名 33 100 - sum(nvl(DUETABLESCORE_, 0) - nvl(TABLESCORE_, 0)) table_score, --表分数 34 sum(nvl(t.TOTALCNT_, 0)) TOTALCNT_, --此期此户此规则全部数据行数 ==> 规则检查次数 35 sum(nvl(t.ERRORCNT_, 0)) ERRORCNT_ --错误数 ==>错误次数 36 from temp_EDQ10_STATISTICS t, EDQ10_RULES t2 37 where t.ruleid_ = t2.id_ 38 group by t.PERIOD_, 39 t.SCHEMEID_, 40 t.HIERARCHY_, 41 t2.facttableid_) loop 42 43 v_error_rows := get_scheme_error_rows(c.hierarchy_, c.facttableid_); --得到方案错误行数 44 v_row_cnt := get_scheme_total_rows(c.hierarchy_, c.facttableid_); --得到方案总数据行数 45 46 v_sql := 'insert into ' || v_table || ' 47 (bbq, 48 SCHEMEID_, 49 hierarchy_, 50 checkedtable_score, 51 checkedtable_id, 52 checkedtable_rows, 53 error_rows, 54 checked_times, 55 error_times) 56 select :1,:2,:3,:4,:5,:6,:7,:8,:9 from dual'; 57 execute immediate v_sql 58 using c.BBQ_, c.SCHEMEID_, c.HIERARCHY_, c.table_score, c.facttableid_, v_row_cnt, v_error_rows, c.TOTALCNT_, c.ERRORCNT_; 59 end loop; 60 61 commit; 62 63 exception 64 when others then 65 dbms_output.put_line(sqlcode || '--' || sqlerrm); 66 rollback; 67 end;
function in PostgreSQL:
1 create or replace function DataClaen_ETL.scheme_test_etl(in_scheme_id in varchar,in_bbq in varchar,table_Prefix in varchar) 2 returns void as $func$ 3 declare 4 --抽数到结果表 5 v_error_rows numeric; 6 v_row_cnt numeric; 7 v_bbq varchar(10); 8 v_model varchar(30); 9 v_table varchar(32); 10 v_sql text; 11 12 referrer_keys CURSOR IS 13 select t.period_ bbq, 14 t.SCHEMEID_, 15 t.hierarchy_, --数据级次 16 100 - 17 sum(t.DUERULESCORE_ - t.RULESCORE_) scheme_score, -- ==>方案得分 18 sum(t.TOTALCNT_) TOTALCNT_, --此期此户此规则全部数据行数 ==> 规则检查次数 19 sum(t.ERRORCNT_) error_times, --错误数 ==>错误次数 20 count(distinct t.ruleid_) scheme_rule_cnt, --方案规则总数 21 sum(case 22 when t.ERRORCNT_ = 0 then 23 0 24 else 25 1 26 end) error_rule_cnt --错误规则数 27 from temp_EDQ10_STATISTICS t 28 group by t.period_, t.SCHEMEID_, t.hierarchy_ 29 order by t.period_, t.SCHEMEID_, t.hierarchy_; 30 31 begin 32 v_bbq := in_bbq; 33 v_model := table_Prefix; 34 35 /*临时表数据清理*/ 36 execute 'truncate table TEMP_EDQ10_DIM_LEVEL_ANAYS'; 37 /*抽数到临时表*/ 38 v_sql := 'insert into TEMP_EDQ10_DIM_LEVEL_ANAYS select * from ' || 39 v_model || '_DIM_LEVEL_ANAYS'; 40 execute v_sql; 41 42 --模型方案总览表 43 v_table := v_model || '_scheme_vi'; 44 v_sql := 'delete from ' || v_table || ' t where t.schemeid_ = ' || 45 chr(39) || in_scheme_id || chr(39) || ' and t.PERIOD_ =' ||chr(39)||v_bbq||chr(39); 46 47 execute v_sql; 48 49 for c in referrer_keys loop 50 v_error_rows := dataclaen_etl.get_scheme_error_rows(c.hierarchy_, 'AA'); --得到方案错误行数 51 v_row_cnt := dataclaen_etl.get_scheme_total_rows(c.hierarchy_, 'AA'); --得到方案总数据行数 52 53 execute ' 54 insert into ' || v_table || ' 55 (PERIOD_, 56 SCHEMEID_, 57 hierarchy_, 58 scheme_score, 59 row_cnt, 60 ERR_ROW_CNT, 61 ruler_checked_times, 62 error_times, 63 scheme_rule_cnt, 64 error_rule_cnt) 65 values ('||chr(39)||c.bbq||chr(39)||' , '||chr(39)||c.SCHEMEID_||chr(39)||' , '||c.hierarchy_||' , '||c.scheme_score||' ,'|| v_row_cnt||' ,'|| v_error_rows||' , '||c.totalcnt_||' , '||c.error_times||' ,'|| c.scheme_rule_cnt||' , '||c.error_rule_cnt||')'; 66 end loop; 67 68 $func$ language plpgsql;
实现方式的差异性:
a.plpgsql没有package的概念(在oracle 中是用package来组织procedure和function的),用schema代替(即创建Schema DataClaen_ETL 而不是package DataClaen_ETL)。
b.PostgreSQL 没有Procedure,但是支持函数,与Oracle的function的功能相同，更厉害的是PostgreSQL的function支持 returns void,这个角度看来function与procedure无异。
c.PLpgsql 中游标必须先声明再使用，不支持Oracle For 循环直接定义的方式。
d.PLpgsql 必须指定函数开始和结束的标识符，且需要指定函数的实现语言（如此示例中 language plpgsql）
e.PostgreSQL 中没有dual伪表。
d.plpgsql 不支持自制事务，不能添加commit
