oraclemod函数 oracle取模函数

2025-03-29 10:31 旅游天气

Oracle笔记-优化策略与工具

另外，为了保证小数点的正确显示，还需要在to_char函数的第三个参数中指定小数点的显示字符为"."，千位分隔符的显示字符为","。具体地，可以使用"NLS_NUMERIC_CHARACTERS=''.,'''"来指定这些参数。

第章优化策略与工具

oraclemod函数 oracle取模函数

标识问题

我的方法

绑定变量与分析（再次）

不使用绑定变量将增加语句分析除了消耗CPU时间外还会增加字典高速缓存上的闩锁

CURSOR_SHARING

CURSOR_SHARING参数缺省为EXACT 若指定为FORCE 则优化器可能将语句中所有的常数转换为绑定变量虽然减少了语句分析但是也会带来如下副作用

优化器可供利用的信息可能减少从而改变执行路径例如条件中对于某个特定值索引有较好的选择性改为绑定变量时优化器并不会发现这一点

查询输出格式发生变化虽然返回的数据长度不变但列的长度可能改变例如对于SELECT id tom name from emp name应该为VARCHAR （）但是由于 tom 被改为绑定变量则可能name的显示长度变为

查询更难评估由于语句的改变 EXPLAIN PLAN看到的查询与数据库看到的可能不一致从而使AUTOTRACE等的输出与实际执行路径不一致

因此完善的应用系统不应当依靠CURSOR_SHARING来提高效率仅能作为权宜之计

SQL_TRACE TIMED_STATISTICS与TKPROF

TIMED_STATISTICS并不会对系统产生过大负担因此建议设置为TRUE

启动跟踪

SQL_TRACE可在系统或会话级激活激活后跟踪文件将产生至init ora参数USER_DUMP_DEST（专用）或 BACKGROUND_DUMP_DEST（MTS）指定的目录而文件大小通过MAX_DUMP_FILE_SIZE控制其设置有如下三种方法

仅数值以OS块为单位

数值+K/M 指定文件大小

UNLIMITED 无上限

一般只需要设置 M就足够了

激活SQL_TRACE的几种常用方式如下

ALTER SESSION SET SQL_TRACE=TRUE|FALSE

SYS DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION 这里我们需要指定SID和SERIAL#（参考V$SESSION）

ALTER SESSION SET S 可获得更详细的信息

此外也可通过DBMS_SUPPORT包相当于S跟踪的一个界面但此包需要Oracle人员支持非标配

随着WEB服务方式的普及往往一个数据库会话很短难以单独跟踪对此我们可以根据用户在数据库级建立触发器

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON ON DATABASE

BEGIN

IF ( USER= TKYTE ) THEN

EXECUTE IMMEDIATE ALTER SESSION SET S TRACE NAME CONTEXT FOREVER LEVEL ;

END IF;

END;/使用并解析TKPROF输出

激活SQL_TRACE后通过如下查询检查SPID

SELECT a spid FROM v$process a v$session b

WHERE a addr = b paddr

AND b audsid = userenv（ sessionid ）

此SPID就包含在跟踪文件的文件名中

UNIX系统中若你不在Oracle的管理组中则生成的跟踪文件所在目录可能无法访问此时需要设定init ora参数_trace_files_public = true

TKPROF语法 TKPROF trc txt

其他用法可以直接运行TKPROF查看一般常用选项就是 sort 可以根据某些参数值排序

对跟踪文件输出的一些解释

i 行

PARSE阶段包括了软分析（在SHARED_POOL中找到语句）和硬分析

EXECUTE阶段对SELECT几乎为空对UPDATE则几乎是全部工作的体现

FETCH阶段对SELECT是几乎所有的工作对UPDATE则为空

ii 列

COUNT 发生的次数

CPU 消耗的CPU时间（CPU秒）

ELAPSED 总体运行时间

QUERY 一致读模式访问的块数也包括了从回滚段读取的块数

CURRENT 访问的当前信息数据块（而不是一致读模式）例如SELECT时读取数据字典内容修改时也需要访问数据字典内容以写

ROWS 所涉及的行数

需要注意的现象

i 高的PARSE COUNT/EXECUTE COUNT（接近 %）且EXECUTE COUNT大于

即执行语句时分析的次数如果过高可能是软分析也过多了对一个会话SELECT CONNECT_BY_ROOT mc flfl 应该是分析一次反复执行

ii 对几乎所有SQL EXECUTE COUNT都是

可能没有使用绑定变量在一个真实应用中应该很少看到不同的SQL 同一个SQL应执行多次

iii CPU和ELAPSED时间相较大

说明花了很长时间等待一个例如磁盘I/O 锁等

iv （FETCH COUNT）/（ROWS FETCHED）比例高

没有很好的使用批量提取批量提取数据的方法是和语言/API相关的例如Pro C中需要使用prefetch=NN预编译 Ja/JDBC下可以调用SETROWPREFETCH方法 PL/SQL可以在SELECT INTO中直接使用BULK COLLECT 而SQL PLUS缺省为每次取行

v 极大的DISK COUNT

较难推断但若DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT 则说明几乎所有数据都来自磁盘此时需要考虑SGA大小和此查询效率

vi 极大的QUERY COUNT或CURRENT COUNT

SQL工作量很大需要注意

EXPLAIN PLAN问题

跟踪文件中显示的是真正执行的路径 TKPROF也支持EXPLAIN=XXX/XXX选项不建议使用其输出是转换跟踪文件当时优化器选择的执行路径并是利用数据库的EXPLAIN工具与真实路径时不完全一致的

使用与解析原始跟踪文件

S跟踪

ALTER SESSION SET S trace name context forr ll N

N= 同标准SQL_TRACE

N= 增加获得绑定变量值

N= 增加获得查询级的等待

原始跟踪文件分段解析

文件头含有时间数据库版本 OS版本实例名等

APPNAME mod= %s mh=%lu act= %s ah=%lu

mod

传入DBMS_APPLICATION_INFO的模块名

模块哈希值

传入DBMS_APPLICATION_INFO的动作

动作哈希值

Parsing in Cursor #%d dep=%d uid=%ld oct=%d lid=%ld tim=%ld hv=%ld ad= %s

Cursor #

游标号也可以用此值获知应用打开的游标数

len

下面SQL语句的长度

dep

SQL语句的递归（recursive）深度

uid

当前方案的用户ID 注意这并不一定和后面的lid一致因为可以用

alter session set current_schema来修改分析时的方案

oct

Oracle命令类型（Oracle Command Type）

lid

用于安全性检查访问权限的用户ID

定时器 / 秒

SQL语句的哈希ID

V$SQLAREA中此SQL语句的ADDR列

EXEC Cursor# c=%d e=%d p=%d cr=%d mis=%d r=%d dep=%d og=%d tim=%d

Cursor #

游标号

cCPU时间 / 秒

e流逝（Elapsed）时间 / 秒

p物理读

一致（QUERY模式）读（逻辑I/O）

当前（Current）模式读（逻辑I/O）

mis

字典缓存中的游标不命中数说明由于过期已从共享池中清除或从未进入共享池等而不得不分析此语句

r处理的行数

dep

SQL语句的递归深度

优化器目标 =ALL ROWS =FIRST ROWS =RULE =CHOOSE

定时器

与EXEC段类似的还有（即取代 EXEC ）

PARSE

分析一个语句

FETCH

从一个游标取出数据行

UNMAP

用于显示在不需要时从中间结果释放临时段

SORT UMAP

同UNMAP 指排序段

WAIT Cursor# nam= %s ela=%d p =%ul p =%ul p =%ul

Cursor#

游标号

nam

等待名

ela

流逝时间 / 秒

p p p

等待特定的参数

以上为文件头与ALTER SESSION出现的跟踪信息此后开始出现运行的SQL语句

BIND段

cursor#

游标号

bind N

绑置从开始

dty

数据类型

mxl

绑定变量长度

mal

数组长度（当使用数组绑定或BULK作时）

scl

数值范围（scale）

pre

精度（precision）

oac

内部标记若此值为奇数则绑定变量可能为NULL（允许为NULL）

oacfl

内部标记续

size

缓冲区大小

offset

用于逐片（piecewise）绑定

bfp

绑定地址

bln

绑定缓冲区大小

真实值长度

flag

内部标记

value

绑定值的字符串表示（如果可能会是一个十六进制dump）

其中dty SELECT text FROM ALL_VIEWS WHERE view_name = USER_VIEWS 可看到一个将dty数值转换为字符串表示的函数

此后我们可以看到WAIT段即真正的等待

对于ENQUEUE 实际就是锁可用以下函数（传入参数为p ）判断类型

CREATE OR REPLACE FUNCTION enqueue_decode(l_p in number) return varchar

l_str varchar ( );

BEGIN

SELECT CHR(BITAND(l_p ) / ) ||

CHR(BITAND(l_p ) / ) || ||

DECODE(BITAND(l_p )

No lock

Row Share

Row Exclusive

Share Row Excl

Exclusive )

INTO l_str

FROM DUAL;

RETURN l_str;

END;

XCTEND（事务边界）段记录了提交等

回滚标记提交回滚

rd_only

只读标记变化提交或回滚事务只读

STAT段记录了运行时SQL真正的执行

cursor #

游标号

执行行号

cnt

查询中流经此步骤的行数

pid

此步骤的父ID

执行中的位置

obj

访问的对象的对象ID

作的文本描述

PARSE ERROR段

len

SQL语句长度

dep

SQL语句递归深度

uid

分析的方案

oct

Oracle命令类型

lid

权限方案ID

定时器

err

ERROR段

cursor #

游标数

err

定时器

DBMS_PROFILER

StatsPack

V$表

V$_NAME

说明名和p p p 三个参数

V$FILESTAT和V$TEMPSTAT

说明系统I/O概况

V$LOCK

说明系统锁的情况但注意Oracle并不在外部保存行锁此视图可以找到TM（DML Enqueue）锁即说明产生了行锁

V$MYSTAT

说明当前会话的统计信息需要V_$STATNAME（不用V$STATNAME 只是V_$STATNAME的一个同义词）和V_$MYSTAT上的SELECT权限

CREATE VIEW MY_STATS AS

SELECT a name b value

FROM V$STATNAME a V$MYSTAT b

WHERE a statistic# = b statistic#

V$OPEN_CURSOR

记录所有会话打开的游标由于Oracle也会缓存已关闭的游标因此此视图中也会包含已关闭的游标信息

V$PARAMETER

说明了所有的init ora参数

V$SESSION

记录数据库的每个会话需要对V_$SESSION的SELECT权限

V$SESSION_

说明会话的情况

V$SESSION_LONGOPS

记录CBO认为执行时间超过秒的命令及进展

V$SESSION_WAIT

记录所有正在等待某的会话及已等待时间

V$SESSTAT

类似V$MYSTAT 但显示所有会话

V$SESS_IO

说明会话的I/O信息

V$SQL和V$SQLAREA

记录SQL信息建议使用V$SQL V$SQLAREA是从V$SQL合并而来的视图代价较高对已经繁忙的系统是一个负担

V$ST 查询一个的父的的兄弟（伯父与叔父）ATNAME

说明了统计号到统计名的映射

V$SYSSTAT

记录实例层面的统计信息当数据库关闭时才清空也是StatsPack很多数据的来源

V$SYSTEM_

lishixinzhi/Article/program/Oracle/201311/16750

oracle 求和函数

(SELECT trim(substr(',' || p2 || ',',

oracle中连续求和分析函数为sum(?)over(?)。

用法：

selectbdcode,sum(1)over(orderbybdcode)aafrombd_bd

规则：

1、sum(?)over()，对所有行求和

2、sum(?)over(orderby?)，连续求和

3、sum(?)ORA错误代码over(partitionby?)，同组内所行求和

4、sum(?)over(partitionby?orderby?)，同第1点中的排序求和原理，只是范围限制在组内。

扩FROM flfl展资料

数据库的物理存储结构是由一些多种物理文件组成，主要有数据文件、控制文件、重做日志文件、归档日志文件、参数文件、口令文件、文件等。

控制文件：存储实例、数据文件及日志文件等信息的二进制文件。altersetcontrol_files=‘路径’。V$CONTROLFILE。

日志文件：即RedoLogFiles和ArchivelogFiles。记录数据库修改信息。ALTERSYSTEMSWITCHLOGFILE;。V$LOG。

参数文件：记录基本参数。spfile和pfile。

文件：showparameterbackground_dump_dest---使用共享连接。

跟踪文件：showparameteruser_dump_dest---使用专用连接。

参考资料来源：百度百科-Oracle数据库

Oracle树查询及相关函数

mod(正,负)=负，mod(x , -y)：所得到的值为负；

Oracle树查询的最重要的就是select start with connect by prior 语法了依托于该语法我们可以将一个表形结构的中以树的顺序列出来在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等

以我做过的一个项目中的表为例表结这里使用两个技巧一个是使用了LEVEL来标识每个在表中的级别还有就是使用with语法模拟出了一张带有级别的临时表构如下

CREATE TABLE FLFL

(ID NUMBER NOT NULL

MC NVARCHAR ( )

SJFLID NUMBER

)FLJB是作为树的级别在很多查询中可以加快SQL的查询效率在下面演示的功能基本上不使用这个关键字

SJFLID存储的是上级ID 如果是父该SJFLID为null（得补充一句当初的确是这样设计的不过现在知道表中别有null记录这会引起全文扫描建议改成代替)

我们从最基本的作逐步列出树查询中常见的作所以查询出来的以家族中的辈份作比方

查找树中的所有父（辈份最长的人）设这个树是个目录结构那么个作总是找出所有的再根据该找到其下属

SELECT FROM flfl WHERE sjflid IS NULL;

这是个引子没用到树型查询

查找一个的直属子（所有儿子）如果查找的是直属子类也是不用用到树型查询的

SELECT FROM flfl WHERE sjflid = ;

这个可以找到ID为的直属子类

查找一个的所有直属子（所有后代）

SELECT FROM flfl START WITH ID = CONNECT BY sjflid = PRIOR ID;

这个查找的是ID为的下的所有直属子类包括子辈的和孙子辈的所有直属

查找一个的直属父（父亲）如果查找的是的直属父也是不用用到树型查询的

这个找到的是ID为的的直属父要用到同一张表的关联了

查找一个的所有直属父（祖宗）

SELECT FROM flfl START WITH ID = CONNECT BY PRIOR sjflid = ID;

这里查找的就是ID为的所有直属父打个比方就是找到一个人的父亲祖父等但是值得注意的是这个查询出来的结果的顺序是先列出子类再列出父类姑且认为是个倒序吧

上面列出两个树型查询方式第条语句和第条语句这两条语句之间的区别在于prior关键字的位置不同所以决定了查询的方式不同当sjflid = PRIOR ID时数据库会根据当前的ID迭代出sjflid与该ID相同的记录所以查询的结果是迭代出了所有的子类记录而PRIOR ID = sjflid时数据库会跟据当前的sjflid来迭代出与当前的sjflid相同的id的记录所以查询出来的结果就是所有的父类结果

以下是一系列针对树结构的更深层次的查询这里的查询不一定是的查询方式或许只是其中的一种实现而已

查询一个的兄弟（亲兄弟）

SELECT a

FROM flfl a

WHERE EXISTS (SELECT

FROM flfl b

WHERE a sjflid = b sjflid AND b ID = );

这里查询的就是与ID为的同属一个父的了就好比亲兄弟了

查询与一个同级的（族兄弟）如果在表中设置了级别的字段上表中的FLJB 那么在做这类查询时会很轻松同一级别的就是与那个同级的在这里列出不使用该字段时的实现!

WITH tmp AS

(SELECT a LEVEL lev

FROM flfl a

START WITH a sjflid IS NULL

CONNECT BY a sjflid = PRIOR a ID)

SELECT

FROM tmp

WHERE lev = (SELECT lev

FROM tmp

WHERE ID = )

WITH tmp AS

(SELECT flfl LEVEL lev

START WITH sjflid IS NULL

SELECT b

FROM tmp b

(SELECT

FROM tmp

WHERE ID = AND lev = ) a

WHERE b lev =

UNION ALL

SELECT

FROM tmp

WHERE sjflid = (SELECT DISTINCT x ID

FROM tmp x

tmp y

(SELECT

FROM tmp

WHERE ID = AND lev > ) z

WHERE y ID = z sjflid AND x ID = y sjflid);

这里查询分成以下几步首先将第个一样将全表都使用临时表加上级别其次根据级别来判断有几种类型以上文中举的例子来说有三种情况（）当前为即查询出来的lev值为那么它没有上级不予考虑（）当前为级查询出来的lev值为那么就只要保证lev级别为的就是其上级的兄弟（）其它情况就是以及以上级别那么就要选查询出来其上级的上级（祖父）再来判断祖父的下级都是属于该的上级的兄弟就是使用UNION将查询出来的结果进行结合起来形成结果集

查询一个的父的同级（族叔）

这个其实跟第种情况是相同的

WITH tmp AS

(SELECT a LEVEL lev

FROM flfl a

START WITH a sjflid IS NULL

CONNECT BY a sjflid = PRIOR a ID)

SELECT

FROM tmp

WHERE lev = (SELECT lev

FROM tmp

WHERE ID = )

只需要做个级别判断就成了

基本上常见的查询在里面了不常见的也有部分了其中查询的内容都是的基本信息都是数据表中的基本字段但是在树查询中还有些特殊需求是对查询数据进行了处理的常见的包括列出树路径等

补充一个概念对于数据库来说根并不一定是在数据库中设计的对于数据库来说根就是start with开始的地方

下面列出的是一些与树相关的特殊需求

这里常见的有两种情况一种是是从列出直到当前的名称（或者其它属性）一种是从当前列出直到的名称（或其它属性）举地址为例国内的习惯是从省开始到市到县到居委会的而国外的习惯正好相反（老师说的还没接过国外的邮件谁能寄个瞅瞅）

从顶部开始

SELECT SYS_CONNECT_BY_PATH (mc / )

START WITH sjflid IS NULL

CONNECT BY sjflid = PRIOR ID;

从当前开始

SELECT SYS_CONNECT_BY_PATH (mc / )

START WITH ID =

CONNECT BY PRIOR sjflid = ID;

在这里我又不得不放个牢骚了 oracle只提供了一个sys_connect_by_path函数却忘了字符串的连接的顺序在上面的例子中个SQL是从根开始遍历而第二个SQL是直接找到当前从效率上来说已经是千万别更关键的是个SQL只能选择一个而第二个SQL却是遍历出了一颗树来再次PS一下

sys_connect_by_path函数就是从start with开始的地方开始遍历并记下其遍历到的 start with开始的地方被视为根将遍历到的路径根据函数中的分隔符组成一个新的字符串这个功能还是很强大的

列出当前的根

在前面说过根就是start with开始的地方

START WITH ID =

CONNECT BY PRIOR sjflid = ID;

connect_by_root函数用来列的前面记录的是当前的根的内容

列出当前是否为N= 增加获得绑定变量值和查询级的等待叶子

这个比较常见尤其在动态目录中在查出的内容是否还有下级时这个函数是很适用的

START WITH sjflid IS NULL

CONNECT BY sjflid = PRIOR ID;

connect_by_isleaf函数用来判断当前是否包含下级如果包含的话说明不是叶子这里返回反之如果不包含下级这里返回

lishixinzhi/Article/program/Oracle/201311/11193

用oracle定义一个函数，怎么定义？

FALSE

create or replace function test(p1 in number, p2 in varchar2)

FLJB NUMBER

return varchar2 is

rlbk

v_out varchar2(200);

begin

select flag_name

into v_out

from PU_META_PLAT.MD_META_DIM_CODE

where dim_table_id = p1

and flag_code in

instr(',' || p2 || ',', ',', 1, LEVEL) + 1,

instr(',' || p2 || ',', ',', 1, LEVEL + 1) -

instr(',' || p2 || ',', ',', 1, LEVEL) - 1))

FROM dual

CONNECT BY LEVEL <=

(length(',' || p2 || ',') -

length(replace(',' || p2 || ',', ',', ''))) / 1 - 1));

exception

when others then

return null;

end;

vb里的mod是什么意思？做什么的？

SID SERIAL# USERNAME CMD

在VB中，定义为被除数和除数先四舍五入，然后再相除求余数。

在ORACLE中,函数的调用方式分别是哪几种？

SELECT b FROM flfl a JOIN flfl b ON a sjflid = b ID WHERE a ID = ;

在ORACLE中,函数的调用方式有位置表示法调用函数、命名表示法调用函数、混合使用位置表示法和命名表示法调用函数、select bdcode,sum(1) over(order by bdcode) aa from bd_bd排除表示法、ql调用表示法 --混合表示法。

act

甲骨文股份有限公司（Oracle）是全球大型数据库软件公司，总部位于美国加州红木城的红木岸。在2008年，甲骨文股份有限公司是继Microsoft及IBM后，全球收入第三多的软件公司。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。甲骨文股份有限公司于正式进入，在、上海、广州和成都均设立了分支机构。

oracle FM去掉了小数点后的0,怎么去掉小数点？

您可以使用 `FM99990.0` 来格式化转换，这样可以将小数点后面的 0 去掉，同时可以保留一位小数。具体地：

to_char(ts, 'FM99990这个函数分为两种，一种是没有参数，则直接返回0-1之间的38位小数.0') || '天'

例如，如果 `ts` 的值是 1.0，则上述语句返回的结果是 `'1天'`；如果 `ts` 的值是 0.5，则返回的结果是 `'0.5天'`。

你可以使用以下SQL语句来将列名为"ts"的天数列保留一位小数，并将其转换为字符型，如果小数部分为0，则只显示整数部分，否则显示小数部分：

SELECT CASSql代码E

WHEN MOD(ts, 1) = 0 THEN to_char(ts, 'FM99990') || '天'

ELSE to_char(ts, 'FM99990.9', 'NLS_NUMERIC_CHARACTERS=''.,''') || '天'

END AS ts_display

上述SQL语句使用了CASE表达式和MOD函数来判断小数部分是否为0，如果小数部分为0，则使用"FM99990"格式化整数部分，否则使用"FM99990.9"格式化小数部分和整数部分。其中，"FM"表示去掉前导空格，"99990"表示至少显示5位整数，"9"表示显示一位小数。

通过这样的方式，即可实现将小数部分为0的天数显示为整数部分加上"天"，而小数部分不为0的天数显示为带一位小SQL> select last_day(sysdate) from dual;数的数字加上"天"，并且不显示小数点的情况下保留小数部分。

oracle更改check 约束

5、NVL2函数：格式SQL> select to_char(sysdate yyyy/mm/dd hh :mi:ss ) from dual;为：NVL2(expr1,expr2, expr3)用法为如果该函数的个参数为空那么显示第二个参数的值，如果个参数的值不为空，则显示第三个参数的值。

ALTER TABLE T1 modify CONSTRAINT CK_T1_TYPE CHECK (TYPE IN('A','B','C')) ENABLE NOVALIDATE;

直接修改约束条月件就可以了

不用重建啊，运行下列语句就可以了。

ALTER TABLE T1 MODIFY CHECK (TYPE IN ('A','B','C')) ENABLE NOVALIDATE;

Oracle树查询及相关函数

以我做过的一个项目中的表为例表结构如下

CREATE TABLE FLFL

(ID NUMBER NOT NULL

MC NVARCHAR ( )

SJFLID NUMBER

)FLJB是作为树的级别在很多查询中可以加快SQL的查询效率在下面演示的功能基本上不使用这个关键字

SJFLID存储的是上级ID 如果是父该SJFLID为null（得补充一句当初的确是这样设计的不过现在知道表中别有null记录这会引起全文扫描建议改成代替)

我们从最基本的作逐步列出树查询中常见的作所以查询出来的以家族中的辈份作比方

查找树中的所有父（辈份最长的人）设这个树是个目录结构那么个作总是找出所有的再根据该找到其下属

SELECT FROM flfl WHERE sjflid IS NULL;

这是个引子没用到树型查询

查找一个的直属子（所有儿子）如果查找的是直属子类也是不用用到树型查询的

SELECT FROM flfl WHERE sjflid = ;

这个可以找到ID为的直属子类

查找一个的所有直属子（所有后代）

SELECT FROM flfl START WITH ID = CONNECT BY sjflid = PRIOR ID;

这个查找的是ID为的下的所有直属子类包括子辈的和孙子辈的所有直属

查找一个的直属父（父亲）如果查找的是的直属父也是不用用到树型查询的

这个找到的是ID为的的直属父要用到同一张表的关联了

查找一个的所有直属父（祖宗）

SELECT FROM flfl START WITH ID = CONNECT BY PRIOR sjflid = ID;

以下是一系列针对树结构的更深层次的查询这里的查询不一定是的查询方式或许只是其中的一种实现而已

查询一个的兄弟（亲兄弟）

SELECT a

FROM flfl a

WHERE EXISTS (SELECT

FROM flfl b

WHERE a sjflid = b sjflid AND b ID = );

这里查询的就是与ID为的同属一个父的了就好比亲兄弟了

WITH tmp AS

(SELECT a LEVEL lev

FROM flfl a

START WITH a sjflid IS NULL

CONNE将字符串转化为ORACLE中的一个日期CT BY a sjflid = PRIOR a ID)

SELECT

FROM tmp

WHERE lev = (SELECT lev

FROM tmp

WHERE ID = )

WITH tmp AS

(SELECT flfl LEVEL lev

START WITH sjflid IS NULL

SELECT b

FROM tmp b

(SELECT

FROM tmp

WHERE ID = AND lev = ) a

WHERE b lev =

UNION ALL

SELECT

FROM tmp

WHERE sjflid = (SELECT DISTINCT x ID

FROM tmp x

tmp y

(SELECT

FROM tmp

WHERE ID = AND lev > ) z

WHERE y ID = z sjflid AND x ID = y sjflid);

查询一个的父的同级（族叔）

这个其实跟第种情况是相同的

WITH tmp AS

(SELECT a LEVEL lev

FROM flfl a

START WITH a sjflid IS NULL

CONNECT BY a sjflid = PRIOR a ID)

SELECT

FROM tmp

WHERE lev = (SELECT lev

FROM tmp

WHERE ID = )

只需要做个级别判断就成了

补充一个概念对于数据库来说根并不一定是在数据库中设计的对于数据库来说根就是start with开始的地方

下面列出的是一些与树相关的特殊需求

从WHERE ID =顶部开始

SELECT SYS_CONNECT_BY_PATH (mc / )

START WITH sjflid IS NULL

CONNECT BY sjflid = PRIOR ID;

从当前开始4、将时间转化为汉字形式的时间格式及显示出星期。

SELECT SYS_CONNECT_BY_PATH (mc / )

START WITH ID =

CONNECT BY PRIOR sjflid = ID;

列出当前的根

在前面说过根就是start with开始的地方

START WITH ID =

CONNECT BY PRIOR sjflid = ID;

connect_by_root函数用来列的前面记录的是当前的根的内容

列出当前是否为叶子

这个比较常见尤其在动态目录中在查出的内容是否还有下级时这个函数是很适用的

START WITH sjflid IS NULL

CONNECT BY sjflid = PRIOR ID;

connect_by_isleaf函数用来判断当前是否包含下级如果包含的话说明不是叶子这里返回反之如果不包含下级这里返回

lishixinzhi/Article/program/Oracle/201311/11193

oracle存储过程中如何调用自定义的函数

update

oracle中调用函数是十分简单的，存储过车调用函数也是一样的。例子如下：

create procedure pro_name1

a显示会话等待的 V$SESSION_ 具体名和含义可以参考Oracle Reference Manual的附录Oracle Wait Eventss

v_para varchar2(200)：=‘Im Danny';

begin

--without parameters case

pro_name2;

--with parameters case

pro_DISK 磁盘物理I/Oname3(v_para);

end;

ORACLE字符串处理函数(2)

USEREN

ADD_MONTHS

增加或减去月份

SQL> select to_char(add_months(to_date( yyyymm ) ) yyyymm ) from dual;

TO_CHA

SQL> select to_char(add_months(to_date( yyyymm ) ) yyyymm ) from dual;

TO_CHA

LAST_DAY

返回日期的一天

TO_CHAR(SY TO_CHAR((S

LAST_DAY(S

MONTHS_BEEEN(date date )

给出date date 的月份

SQL> select months_beeen( 月月 ) mon_beeen from dual;

MON_BEEEN

SQL>selectmonths_beeen(to_date( yyyy mm dd ) to_date( yyyy mm dd )) mon_be from dual;

MON_BE

NEW_TIME(date Sql代码this that )

给出在this时区=other时区的日期和时间

SQL> select to_char(sysdate yyyy mm dd hh :mi:ss ) bj_time to_char(new_time

(sysdate PDT GMT ) yyyy mm dd hh :mi:ss ) los_angles from dual;

BJ_TIME LOS_ANGLES

: : : :

NEXT_DAY(date day )

给出日期date和星期x之后计算下一个星期的日期

SQL> select next_day( 月星期五 ) next_day from dual;

NEXT_DAY

SYSDATE

用来得到系统的当前日期

SQL> select to_char(sysdate dd mm yyyy day ) from dual;

TO_CHAR(SYSDATE

星期日

SQL> select to_char(trunc(sysdate hh ) yyyy mm dd hh :mi:ss ) hh

to_char(trunc(sysdate mi ) yyyy mm dd hh :mi:ss ) hhmm from dual;

HH HHMM

: : : :

CHARTOROWID

将字符数据类型转换为ROWID类型

SQL> select rowid rowidtochar(rowid) ename from scott emp;

ROWID ROWIDTOCHAR(ROWID) ENAME

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

CONVERT(c dset sset)

将源字符串 sset从一个语言字符集转换到另一个目的dset字符集

conver

strutz

HEXTORAW

将一个十六进制构成的字符串转换为二进制

RAWTOHEXT

将一个二进制构成的字符串转换为十六进制

ROWIDTOCHAR

将ROWID数据类型转换为字符类型

TO_CHAR(date format )

TO_CHAR(SYSDATE YY

/ / : :

TO_DATE(string format )

TO_MULTI_BYTE

将字符串中的单字节字符转化为多字节字符

SQL> select to_multi_byte( 高 ) from dual;

高 TO_NUMBER

将给出的字符转换为数字

SQL> select to_number( ) year from dual;

YEAR

BFILENAME(dir file)

指定一个外部二进制文件

SQL>insert into file_tb values(bfilename( lob_dir image gif ));

CONVERT( x desc source )

将x字段或变量的源source转换为desc

SQL> select sid serial# username decode(mand

none

insert

select

delete

drop

other ) cmd from v$session where type!= background ;

none

GAO select

GAO none

DUMP(s fmt start length)

DUMP函数以fmt指定的内部数字格式返回一个VARCHAR 类型的值

SQL> col global_name for a

SQL> col dump_string for a

SQL> set lin

SQL> select global_name dump(global_name ) dump_string from global_name;

GLOBAL_NAME DUMP_STRING

ORACLE WORLD Typ= Len= CharacterSet=ZHS GBK: W O R L D

EMPTY_BLOB()和EMPTY_CLOB()

这两个函数都是用来对大数据类型字段进行初始化作的函数

GREATEST

返回一组表达式中的值即比较字符的编码大小

SQL> select greatest( AA AB AC ) from dual;

SQL> select greatest( 啊安天 ) from dual;

天 LEAST

返回一组表达式中的最小值

SQL> select least( 啊安天 ) from dual;

啊 UID

返回标识当前用户的整数

SQL> show user

USER 为 GAO

SQL> select username user_id from dba_users where user_id=uid;

USERNAME USER_ID

GAO

USER

返回当前用户的名字

SQL> select user from dual;

USER

GAO

USEREVN

返回当前用户环境的信息 opt可以是:

ENTRYID SESSIONID TERMINAL ISDBA LABLE LANGUAGE CLIENT_INFO LANG VSIZE

ISDBA 查看当前用户是否是DBA如果是则返回true

SQL> select userenv( isdba ) from dual;

TRUE

SESSION

返回会话标志

SQL> select userenv( sessionid ) from dual;

USERENV( SESSIONID )

ENTRYID

返回会话人口标志

SQL> select userenv( entryid ) from dual;

USERENV( ENTRYID )

INSTANCE

返回当前INSTANCE的标志

SQL> select userenv( instance ) from dual;

USERENV( INSTANCE )

LANGUAGE

返回当前环境变量

SQL> select userenv( language ) from dual;

USERENV( LANGUAGE )

SIMPLIFIED CHINESE_CHINA ZHS GBK

LANG

SQL> select userenv( lang ) from dual;

USERENV( LANG )

ZHS

TERMINAL

返回用户的终端或机器的标志

SQL> select userenv( terminal ) from dual;

USERENV( TERMINA

GAO

VSIZE(X)

返回X的大小(字节)数

SQL> select vsize(user) user from dual;

VSIZE(USER) USER

SYSTEM

AVG(DISTINCT|ALL)

all表示对所有的值求平均值 distinct只对不同的值求平均值

SQLWKS> create table table (xm varchar( ) sal number( ));

语句已处理

SQLWKS> insert into table values( gao );

SQLWKS> insert into table values( zhu );

SQLWKS> mit;

SQL> select g(distinct sal) from gao table ;

AVG(DISTINCTSAL)

SQL> select g(all sal) from gao table ;

AVG(ALLSAL)

MAX(DISTINCT|ALL)

求值 ALL表示对所有的值求值 DISTINCT表示对不同的值求值相同的只取一次

SQL> select max(distinct sal) from scott emp;

MAX(DISTINCTSAL)

MIN(DISTINCT|ALL)

求最小值 ALL表示对所有的值求最小值 DISTINCT表示对不同的值求最小值相同的只取一次

SQL> select min(all sal) from gao table ;

MIN(ALLSAL)

STDDEV(distinct|all)

求标准 ALL表示对所有的值求标准 DISTINCT表示只对不同的值求标准

SQL> select stddev(sal) from scott emp;

STDDEV(SAL)

SQL> select stddev(distinct sal) from scott emp;

STDDEV(DISTINCTSAL)

VARIANCE(DISTINCT|ALL)

求协方

SQL> select variance(sal) from scott emp;

VARIANCE(SAL)

GROUP BY

主要用来对一组数进行统计

DEPSELECT CONNECT_BY_ISLEAF flfl TNO COUNT() SUM(SAL)

HAVING

对分组统计再加限制条件

SQL> select deptno count() sum(sal) from scott emp group by deptno hing count()>= ;

DEPTNO COUNT() SUM(SAL)

SQL> select deptno count() sum(sal) from scott emp hing count()>= group by deptno ;

DEPTNO COUNT() SUM(SAL)

ORDER BY

用于对查询到的结果进行排序输出

DEPTNO ENAME SAL

KING

CLARK

MILLER

SCOTT

FORD

JONES

ADAMS

SMITH

BLAKE

ALLEN

TURNER

WARD

MARTIN

lishixinzhi/Article/program/Oracle/201311/16630

声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系 836084111@qq.com 删除。

oraclemod函数 oracle取模函数

Oracle笔记-优化策略与工具

oracle 求和函数

Oracle树查询及相关函数

用oracle定义一个函数，怎么定义？

vb里的mod是什么意思？做什么的？

在ORACLE中,函数的调用方式分别是哪几种？

oracle FM去掉了小数点后的0,怎么去掉小数点？

oracle更改check 约束

Oracle树查询及相关函数

oracle存储过程中如何调用自定义的函数

ORACLE字符串处理函数(2)

相关文章

热门

推荐

随机