oracle常用函数总结(3)

SYS unlocked SYSTEM unlocked OUTLN unlocked CSMIG unlocked SCOTT unlocked EYGLE unlocked DBSNMP unlocked WMSYS

8 rows selected.

8 SYS_CONNECT_BY_PATH

在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。它一定要和connect by子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

实例如下：

create table test (a varchar2(10),b varchar2(10));

INSERT INTO TEST (A, B) VALUES ('1', '我'); INSERT INTO TEST (A, B) VALUES ('1', '们'); INSERT INTO TEST (A, B) VALUES ('2', '一'); INSERT INTO TEST (A, B) VALUES ('2', '起'); COMMIT;

SELECT A, B FROM TEST

A B ---------- ---------- 1 我 1 们 2 一 2 起

现在需要达到如下的效果， A B ---------- ---------- 1 我,们 2 一,起

只想用一句sql来返回结果。

SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B

FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN FROM TEST) START WITH RN = 1

CONNECT BY RN - 1 = PRIOR RN AND A = PRIOR A GROUP BY A;

其中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。

row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。生产序号的方法通过over()函数里面的语句来控制。

START WITH 代表你要开始遍历的的节点！

CONNECT BY PRIOR 是标示父子关系的对应！

9 oracle中 connect by prior 递归算法

Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是：

select ... from tablename start with 条件1 connect by 条件2 where 条件3; 例：

select * from table

start with org_id = 'HBHqfWGWPy' connect by prior org_id = parent_id;

简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: org_id,parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。

用上述语法的查询可以取得这棵树的所有记录。 其中：

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id是本条记录的parent_id，即本记录的父亲是上一条记录。

条件3 是过滤条件，用于对返回的所有记录进行过滤。

10 group by

group by 里必须包含所有select的直接字段

11 Having与Where的区别

?where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，

where条件中不能包含聚组函数，使用where条件过滤出特定的行。

?having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例1

select 类别, sum(数量) as 数量之和 from A group by 类别

having sum(数量) > 18

示例2：Having和Where的联合使用方法 select 类别, SUM(数量)from A where 数量 gt;8 group by 类别

having SUM(数量) gt; 10

11 查询表字段数 select count(column_name) from user_tab_columns where table_name=upper('WY_FDDEF'); 12

添加字段的语法：alter table tablename add (columnname datatype [default value][null/not null],?.);

修改字段的语法：alter table tablename modify (columnname datatype [default value][null/not null],?.);

删除字段的语法：alter table tablename drop (columnname);

添加、修改、删除多列的话，用逗号隔开。