oracle 改pg数据库语法总结

1.存储过程中调用存储过程，无返回参数：

1）PERFORM pr_writebackuplog(1,v_nowtablename, v_sql);

2）有返回参数：select * into x from pr_writebackuplog(1,v_nowtablename, v_sql);

 

 

 

2.数字转字符串：to_char(1234, 'FM999999999999999999')

to_number(param,'99999999999999999999D99999999999999999999');

 

 

3.时间相减：select LOCALTIMESTAMP::TIMESTAMP - INTERVAL '20 minute' 减一个变量：

v_nowenddate:=LOCALTIMESTAMP::TIMESTAMP - (v_nowkeepdaynum || ' day')::interval; 

 

 

 

4.orcle 对应的user_table表：select count(1) into v_count from information_schema.tables

        where table_name = lower(v_nowtablename) and table_schema =current_schema LIMIT 1;

 

5.计算两个timestamp 之间相差的秒数；

EXTRACT(EPOCH FROM (LOCALTIMESTAMP-v_begintime))

 

 

6.sql%rowcount 修改为get diagnostics rowcnt := row_count;

 

 

 

7.多返回值接收参数： select * into v_addtreeok, v_parentid from pr_adddevice_tree(i_center, i_node, i_ip, i_user);

 

 

8.查询表结构： 

select * from information_schema.columns

        where table_name = lower(p_tablename) and table_schema ='test'