ITPub博客

一个简单的行列转换例子...[@more@]SQL> select * from test; ID NAME---------- ---------- 12 a 17 a 3 b 19 a 79 c 8 b6 rows selected.SQL> select sum(decode(name,'a',id,0)) a, 2 sum(decode(name,'b',id,0)) b, 3 sum(decode(name,'c',id,0)) c 4 from (select id,name, 5 row_number()over(partition by name order by id) rn 6 from test) 7 group by rn; A B C---------- ---------- ----------

Asked：ORA-04098 建立Trigger错误 触发器目的：当用户表tblusers 中的修改者reviser发生改变时，将记录所代表的用户的等级employeelevel修改为2。[@more@]如下：--------------------------------------------------------------------------------------------------------Create or replace trigger z_tr_sysuser_u after update of reviser on tblusers for each rowDeclare cursor cur_change is select employeelevel, reviser, revisedate, employeeno, employeename from tblusers where reviser <> 'MISGRO

是否要求满足约束ValidateNovalidate已有记录新增/修改记录已有记录新增/修改记录EnableYes　　YesNo　YesDisableYes　 NoNoNo[@more@]Enable/Disable/Validate/Novalidate 2007/06/25 是否要求满足约束ValidateNovalidate已有记录新增/修改记录已有记录新增/修改记录EnableYes　　YesNo　YesDisableYes　 NoNoNoValidate确保已有数据符合约束；Novalidate不必考虑已有数据是否符合约束。除非Novalidate被指定，Enable默认Validate；除非Validate被指定，Disable默认Novalida

一、无条件 INSERT ALL1、指定所有跟随着的多表 insert_into_clauses 执行无条件的多表插入；2、对于每个由子查询返回的行， Oracle 服务器执行每一个 insert_into_clause一次。二、条件 INSERT ALL1、指定 conditional_insert_clause 来执行一个条件多表插入；2、Oracle 服务器通过相应的 WHEN 条件过滤每一个 insert_into_clause，确定是否执行这个 insert_into_clause；3、一个单个的多表插入语句可以包含最多 127 个 WHEN 子句。三、条件 INSERT FIRST1、Oracle 服务器对每一个出现在语句顺序中的 WHEN 子句求值；2、如果第一个 WHEN

Rank（）Over（Partiton by … Order By …Nulls First/Last）Dense_rank （）Over（Partiton by … Order By …Nulls First/Last）Row_number （）Over（Partiton by … Order By …Nulls First/Last）Lag（…，…）Over（Partiton by … Order By …Nulls First/Last）Lead（…，…）Over（Partiton by … Order By …Nulls First/Last）Aggregate_function（…）Over（Partition by … Order By …Nulls First/Last）Aggregate_function（…） Keep（Dense_rank First/Last Order By …Nulls First/Last） Over （Partition by …）[@m

TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.偷个懒，直接拷贝了Oracle的在线文档，嘿嘿![@more@]REPLACESyntaxreplace::=Text description of replacePurposeREPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.Both se

Sum(col_name) Over(partition by col_name_2,col_name_3,… order by col_name_i,col_name_j,…)将由Partition By语句中指定的属性列进行分组，并依据Order By语句指定的属性列实现组内排序，对分组内由Sum指定的属性列求和。该函数与单独的Sum（）函数的区别在于：每一条记录求和的结果是由同一分组内所有该记录之前的记录中属性列的值求和得来的！[@more@]Analytic ExampleThe following example calculates, for each manager in the sample table hr.employees, a cumulative total of salaries of employees who answer to that man

体验惠州乘务员的幽默．．．[@more@]武汉的公交属全国知名的了，应该说坐过了武汉的公交，再乘其它地方的公交车没有会令人奇怪的事了，不过那天惠州一乘务员以句话令人记忆深刻！！！阴天，下雨，部门出去聚餐，一行二十来人，上了一辆388路公汽，车上人有点多，大家前前后后的也没怎么集中，然后，乘务员开始数人收钱了。由主管统一付款，然后大家开始聊谈，闲扯。一小会后，那个乘务员跑过来说少算了一个人，差1.50元。我算了这个乘务员应该不会太严肃了，开了个玩笑，说：一个人嘛，就打个折呗！结果那个乘务员回了一句令车上人哄“车”

PurposeSYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.[@more@]EGTable：z_test( id int, name varchar2(20))idname9a9b9d10c10eSQL Statements： Select id , ltrim(max( sys_connect_by_path( name , ',' ) ) , ',') results From ( Select id , name , row_number( )over( partition by id order by name ) pno, row_number( )over( partition by