Oracle常用方法搜集

1、查看Oracle版本

SELECT * FROM V$VERSION;

SELECT * FROM v$instance;

2、查看数据库中的JOB

SELECT * FROM user_jobs;

3、ORA-01810: format code appears twice

使用函数TO_TIMESTAMP('2014-10-28 11:21:19', 'yyyy-MM-dd HH:mm:ss')时报的错误，这个是因为Oracle不区分大小写，format中使用的Mm重复了，所以报错，改正：TO_TIMESTAMP('2014-10-28 11:21:19', 'yyyy-MM-dd HH:mi:ss')

4、自定义类型

CREATE OR replace TYPE obj_table AS object

(tmpId VARCHAR(10),info VARCHAR(10),dt timestamp)

-- 自定义表类型，包含多个参数时，需要宁外定义一个object类型

CREATE OR replace TYPE t_table AS TABLE OF obj_table;

--创建一个方法，返回表类型（管道化表函数方法，这种方式最后的return为空）

create or replace function f_pipe(s number)

return t_table pipelined

as

    v_obj_table obj_table;   

begin    

for i in 1..s loop 

    v_obj_table :=  obj_table(i,to_char(i*i));

    pipe   row(v_obj_table);   

end loop;

return;

end f_pipe;

-- 普通创建方法

create or replace function f_normal(s number)

return t_table

as

    rs t_table:= t_table();

begin

    for i in 1..s loop

        rs.extend;

        rs(rs.count) := obj_table(rs.count,'name'||to_char(rs.count));

        --rs(rs.count).name := rs(rs.count).name || 'xxxx';

    end loop;

return rs;

end f_normal;

-- 调用

select * from table(f_pipe(5));

5、Oracle自定义聚合函数

--定义函数 

CREATE OR REPLACE FUNCTION f_catstring (p_str VARCHAR2) 

   RETURN VARCHAR2 

   AGGREGATE USING t_stringconcat; 

--定义类型 

CREATE OR REPLACE TYPE t_stringconcat AS OBJECT ( 

   str      VARCHAR2 (1000), 

   oldstr   VARCHAR2 (1000), 

--初始化函数，必须要实现的方法，初始化上下文环境

   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_stringconcat) 

      RETURN NUMBER, 

--迭代运算函数，必须实现的方法，第一个参数为聚合的上一个值，

--第二个参数为当前要处理的值，如果为空，则忽略该次迭代 

   MEMBER FUNCTION odciaggregateiterate ( 

      SELF    IN OUT   t_stringconcat, 

      VALUE   IN       VARCHAR2 

   ) 

      RETURN NUMBER, 

--聚合函数的最后一步，用于返回处理结果，必须实现的方法。第一个参数为上下文，

--第二个参数为返回值，可以是number和varchar2等类型，第三个参数是标志位

   MEMBER FUNCTION odciaggregateterminate ( 

      SELF          IN       t_stringconcat, 

      returnvalue   OUT      VARCHAR2, 

      flags         IN       NUMBER 

   ) 

      RETURN NUMBER, 

--合并上下文到一个上下文中，并行或串行有可能被执行，必须实现，但Oracle选择性执行

   MEMBER FUNCTION odciaggregatemerge ( 

      SELF   IN OUT   t_stringconcat, 

      ctx2   IN       t_stringconcat 

   ) 

      RETURN NUMBER 

); 

--定义body，实现Object type 

CREATE OR REPLACE TYPE BODY t_stringconcat 

IS 

   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_stringconcat) 

      RETURN NUMBER 

   IS 

   BEGIN 

      --初始化 

      sctx := t_stringconcat(NULL, NULL); 

      RETURN odciconst.success; 

   END; 

   MEMBER FUNCTION odciaggregateiterate ( 

      SELF    IN OUT   t_stringconcat, 

      VALUE   IN       VARCHAR2 

   ) 

      RETURN NUMBER 

   IS 

   BEGIN 

      --解决拼接多个字段重复的问题 

      IF NOT SELF.oldstr IS NULL AND SELF.oldstr = VALUE 

      THEN 

         NULL; 

      ELSE 

         SELF.str := SELF.str || VALUE || ', '; 

      END IF; 

 

      SELF.oldstr := VALUE; 

      RETURN odciconst.success; 

   END; 

   MEMBER FUNCTION odciaggregateterminate ( 

      SELF          IN       t_stringconcat, 

      returnvalue   OUT      VARCHAR2, 

      flags         IN       NUMBER 

   ) 

      RETURN NUMBER 

   IS 

   BEGIN 

      returnvalue := SUBSTR (SELF.str, 1, LENGTH (SELF.str) - 2); 

      RETURN odciconst.success; 

   END; 

   MEMBER FUNCTION odciaggregatemerge ( 

      SELF   IN OUT   t_stringconcat, 

      ctx2   IN       t_stringconcat 

   ) 

      RETURN NUMBER 

   IS 

   BEGIN 

      NULL; 

      RETURN odciconst.success; 

   END; 

END; 

并行执行聚合函数：

与内置函数一样，自定义聚合函数也可以并行执行。并指执行时，首先是各个slave执行聚合运算，然后将结果进行合并，合并是通过ODCIAggregateMerge实现的。下图展示了这个过程

注意：我们可以将自定义聚合函数作为分析函数使用，但是为了提高分析函数的执行效率，我们可以对自定义聚合函数做一些处理，详细内容可以参考《Data Cartridge Developer's Guide 》