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'