几个高级查询

1、层次化查询（CONNECT BY查询、树结构）

 例子：

 Select lpad('',(Level-1)*2,'')||emp.ename ename,
       dname,
       sys_connect_by_path(emp.ename,'-') path,  --当运行层次化查询的时候，我们可以使用这个函数返回从根节点到终端节点的完整路径。
       Level                                  --LEVEL是存在于Oracle所执行的所有查询中的伪列。它是一个数值，可以指出用户在树中所处的层次。在非层次化的查询中，由于所有的行都处于相同的层次，所以层次就没有用处。它的值总会是0。
From emp,dept
Where emp.deptno=dept.deptno --以前的Oracle版本中，层次化查询必须要基于单独的表
Start With mgr Is Null
--Connect By mgr=Prior empno
Connect By Prior empno=mgr
Order Siblings By ename--这个子句可以让我们独立于结果集合中的其它部分对各个子树进行排序，可以和Order By ename比较一下
--Order By ename
--SYS_CONNECT_BY_PATH()函数的语法为：SYS_CONNECT_BY_PATH(,)

结果：

1 KING ACCOUNTING -KING 1
2 BLAKE SALES -KING-BLAKE 2
3 ALLEN SALES -KING-BLAKE-ALLEN 3
4 JAMES SALES -KING-BLAKE-JAMES 3
5 MARTIN SALES -KING-BLAKE-MARTIN 3
6 TURNER SALES -KING-BLAKE-TURNER 3
7 WARD SALES -KING-BLAKE-WARD 3
8 CLARK ACCOUNTING -KING-CLARK 2
9 MILLER ACCOUNTING -KING-CLARK-MILLER 3
10 JONES RESEARCH -KING-JONES 2
11 FORD RESEARCH -KING-JONES-FORD 3
12 SMITH RESEARCH -KING-JONES-FORD-SMITH 4
13 SCOTT RESEARCH -KING-JONES-SCOTT 3
14 ADAMS RESEARCH -KING-JONES-SCOTT-ADAMS 4

2、用户定义聚集函数

Oracle自定义聚集函数使用简介

1 . oracle自定义聚集函数接口简介
   a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type )   
               return number
   自定义聚集函数初始化设置,从这儿开始一个聚集函数
   b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
               return number
    自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联
   c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT  varchar2,flags IN number)
             return number
     用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
    d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)
     终止聚集函数的处理,返回聚集函数处理的结果.

2. 实现的例子.

CODE:

create type strcat_type as object (
    cat_string varchar2(4000),
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
)
/

create type body strcat_type is
  static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
  is
  begin
      cs_ctx := strcat_type( null );
      return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT strcat_type,
                                       value IN varchar2 )
  return number
  is
  begin
      self.cat_string := self.cat_string || ','|| value;
      return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN Out strcat_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
  return number
  is
  begin
      returnValue := ltrim(rtrim(self.cat_string,','),',');
      return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT strcat_type,
                                     ctx2 IN Out strcat_type)
  return number
  is
  begin
      self.cat_string := self.cat_string || ',' || ctx2.cat_string;
      return ODCIConst.Success;
  end;

end;
/

CREATE or replace
FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
/

3. 具体应用,

CODE:

15:16:52 SQL> select empno,ename,deptno,job from scott.emp;

     EMPNO ENAME          DEPTNO JOB
---------- ---------- ---------- ---------
      7369 SMITH              20 CLERK
      7499 ALLEN              30 SALESMAN
      7521 WARD               30 SALESMAN
      7566 JONES              20 MANAGER
      7654 MARTIN             30 SALESMAN
      7698 BLAKE              30 MANAGER
      7782 CLARK              10 MANAGER
      7788 SCOTT              20 ANALYST
      7839 KING               10 PRESIDENT
      7844 TURNER             30 SALESMAN
      7876 ADAMS              20 CLERK
      7900 JAMES              30 CLERK
      7902 FORD               20 ANALYST
      7934 MILLER             10 CLERK

14 rows selected.

Elapsed: 00:00:00.01

15:18:29 SQL> col dept_emplist format a60
15:18:41 SQL> select deptno,strcat(empno||'-'||ename) dept_emplist
15:19:01   2  from scott.emp group by deptno;

    DEPTNO DEPT_EMPLIST
---------- ------------------------------------------------------------
        10 7782-CLARK,7839-KING,7934-MILLER
        20 7369-SMITH,7902-FORD,7876-ADAMS,7788-SCOTT,7566-JONES
        30 7499-ALLEN,7698-BLAKE,7654-MARTIN,7844-TURNER,7900-JAMES,7521-WARD

Elapsed: 00:00:00.04
15:19:08 SQL> col job_emplist format a80
15:19:23 SQL> select job,strcat(empno||'-'||ename) job_emplist
15:19:43   2  from scott.emp group by job;

JOB       JOB_EMPLIST
--------- --------------------------------------------------------------------------------
ANALYST   7788-SCOTT,7902-FORD
CLERK     7369-SMITH,7900-JAMES,7876-ADAMS,7934-MILLER
MANAGER   7566-JONES,7782-CLARK,7698-BLAKE
PRESIDENT 7839-KING
SALESMAN  7499-ALLEN,7521-WARD,7844-TURNER,7654-MARTIN

Elapsed: 00:00:00.03
15:19:50 SQL>

3、表和管道函数

 

4、使用查询编写生成SQL的SQL

如果您所要运行的大量的SQL语句能够通过使用数据词典来进行回答，那么他们就是这种技术最主要的候选者。

5、分析函数

 

6、MERGE

MERGE的语法包括五个子句：

MERGE INTO——定义合并的表/视图

USING——定义要更新或者插入的数据源

ON——定义语句进行插入或者更新的条件。如果条件为TRUE，用户就可以执行更新子句；如果条件为FALSE，就要执行插入子句

WHEN MATCHED THEN UPDATE——定义要更新的列

WHEN NOT MATCHED THEN UPDATE——定义要插入的列

 

SQL> Create Table table_a(
  2    Id Number)
  3  /

表已创建。

 

SQL> insert into table_a
  2  select rownum
  3  from all_tables
  4  where rownum<7
  5  /

已创建6行。

 

SQL> select *
  2  from table_a
  3  /

        ID
----------
         1
         2
         3
         4
         5
         6

已选择6行。

 

 

SQL> create table table_b(
  2  id number,
  3  status varchar2(255))
  4  /

表已创建。

 

SQL> insert into table_b values(1,'NEW');

已创建 1 行。

SQL> insert into table_b values(3,'NEW')
  2  /

已创建 1 行。

SQL>  insert into table_b values(5,'NEW');

已创建 1 行。

SQL> select * from table_b
  2  /

        ID
----------
STATUS
-------------------------------------------------
         1
NEW

         3
NEW

         5
NEW

 

打算实现的功能：

  现在，我们想要确保TABLE_A中所有已有的ID值也都在TABLE_B中。如果ID当前处于TABLE_B中，那么就将它的值设置为OLD。否则就要插入新纪录，并将它的STATUS设置为NEW。

方法1：在PL/SQL中的实现方式

    Declare
     l_Cnt Number;
    Begin
     For c In (Select Id From Table_a) Loop
      Select Count(*) Into l_Cnt From Table_b Where Id = c.Id;
      If l_Cnt > 0 Then
       Update Table_b Set Status = 'OLD' Where Id = c.Id;
      Else
       Insert Into Table_b Values (c.Id, 'NEW');
      End If;
     End Loop;
    End;

 

方法2：

SQL> Merge Into Table_b b
  2  Using (Select * From Table_a) a
  3  On (a.Id = b.Id)
  4  When Matched Then
  5   Update Set Status = 'OLD'
  6  When Not Matched Then
  7   Insert Values (a.Id, 'NEW')
  8  /

6 行已合并。

SQL>