- 博客(22)
- 收藏
- 关注
RSS订阅原创 2个字段有相同的项，只选择一条
表test lname，bA BA DA CB AB CC BD A 要得到如下记录 A D A C A B B C D B sql语句：SELECT * FROM test a WHERE NOT EXISTS (SELECT 1 FROM (SELECT a.* FROM tes
2009-03-12 00:40:00
578
原创 分区统计金额sql
WITH a AS (SELECT A ty , 7 stime ,10 etime, 10 price FROM dualUNION ALLSELECT A ty , 10 stime ,12 etime, 25 price FROM dualUNION ALLSELECT A ty , 12 stime ,18 etime, 15 price FROM dualUNION ALLS
2008-10-20 18:46:00
1059
原创 根据001002得到所有的路径
WITH a AS (SELECT 001 ID , a NAME FROM dualUNION ALLSELECT 001002 ID , b NAME FROM dualUNION ALLSELECT 001003 ID , c NAME FROM dualUNION ALLSELECT 001002001 ID , d NAME FROM dualUNION
2008-09-27 14:56:00
543
原创 得到相同的记录
t1的结构： year tname cnt ----------------------- 2005 type1 10 2005 type3 20 2006 type3 15 2006 type2 30 2007 type2 20 t2的结构 seq tname --------------------- 1
2008-09-18 11:35:00
643
原创 在10g以前来判断是否是根节点和叶子
with a as (select 1 id,2 p_id from dualunionselect 2 id,3 p_id from dualunionselect 2 id,4 p_id from dualunionselect 3,5 from dualunionselect 7,6 from dual)select l.id,decode(r.id,null,0,1) cnt from a
2008-04-14 21:03:00
640
转载 字符转换问题to_char
格式串 说明及示例---------- -----------------------------------------------------------, 在指定位置附加逗号(便于阅读)，注意逗号不能出现于格式字串首位，并且对于小数点的数字无效例如：SQL> select to_char(389999.00,99,999,999) from dual;TO_CHAR(
2008-04-10 13:11:00
958
原创 返回是游标的函数
CREATE OR REPLACE PACKAGE BODY pkg_testASfunction fun_get(p_rc varchar2)return SYS_REFCURSORissqlstr VARCHAR2 (500);retcusor SYS_REFCURSOR;BEGINsqlstr:=select 1 row_id,2 row_name from dual;OPEN
2008-04-07 12:52:00
748
原创 限制特定IP访问数据库
以前有文档说增加或修改protocol.ora文件， 在9i中真正起作用的是sqlnet.ora文件，我们修改sqlnet.ora其实是最好最快的方法。 在sqlnet.ora中增加如下部分 ----------------------------- #### 来自 protocol.ora 的属性 #### tcp.validnode_checking=yes #允许访问的IP t
2008-04-03 16:27:00
565
原创 根据connect by 查询有重复的节点树
with a as ( select A beginaddr ,B toaddr, 1000 long_km from dual union select A ,C , 1100 from dual union select A ,D , 900 from dual union select A ,E , 40
2008-04-03 15:08:00
801
原创 对于同一张表中没有记录就插入否则就修改
MERGE INTO temp_a ppUSING (select l.id,nvl(r.na,) na from (select 3 id from dual) l,(select id,na from temp_a where id=3) r where l.id=r.id(+)) np --表示这条记录在附表中一定要存在。ON (pp.id=np.id)when MATCHED then
2008-03-31 21:44:00
565
原创 查询记录中没有记录的就返回空,否则返回原值
with ta as (select 1 a from dual union select 2 from dual union select 3 from dual)select decode(count(*),0,无,3) a from ta where a=3 第二种能够满足所有的信息记录with a as ( select
2008-03-29 13:01:00
1083
原创 [zt]展示JDBC存取ORACLE大型数据对象LOB几种情况的示范类
import java.io.*; import java.util.*; import java.sql.*; public class LobPros { /** * ORACLE驱动程序 */ private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
2008-03-20 17:28:00
478
原创 得到动态sql信息
create or replace function fun_test(psname in varchar2,org_group_id in varchar2,model_id in varchar2,col_id in varchar2) return varchar2is v_cursor number; Result varchar2(255); lsWhere varcha
2008-03-18 19:16:00
609
原创 得到汉字的字母（包括全拼）
create or replace type spell_code as object(spell varchar2(10),code number);create or replace type t_spellcode is table of spell_code;--返回拼音与代码的对应关系create or replace function f_getSpellcode return
2008-03-18 12:49:00
1073
原创 oracle 常用功能函数汇总********************
******************************************************************************* * SQL Group Functions (num can be a column or expression) * (null values are ignored, default betwee
2008-03-17 20:13:00
477
原创 计算一个月里面有多少工作日和一年中的工作日
select count(*) from (SELECT to_char(LAST_DAY(to_date(200802,yyyymm)),dd) mdate,to_date(200802||lpad(level,2,0),yyyymmdd), to_char(to_date(200802||lpad(level,2,0),yyyymmdd),W) w,t
2008-03-17 20:05:00
5090
原创 从表中随机取5条记录
select * from (select * from (select level from dual connect by level < 50) order by dbms_random.value) where rownum<6
2008-03-17 19:22:00
913
原创 把记录行列转换
with a as (select 1 id,s name,100 fee,200711 month from dual union all select 1 id,s name,200 fee,200710 month from dual union all select 1 id,s n
2008-01-11 17:28:00
583
原创 可以用多列来作为一个条件进行查询
select * from (select 1 a1,2 a2 from dualunion select 3,4 from dual)where (a1,a2) in (select 1,2 from dual union select 3,4 from dual)
2008-01-11 15:28:00
621
原创 关于'a=1,b=2,c=3'分离成3行的SQL语句。
第一种方法：select substr(name,1,instr(name,,,1)-1) name from testcunion allselect substr(name,instr(name,,,1)+1,instr(name,,,1,2)-instr(name,,,1)-1) from testcunion allselect substr(name,instr(na
2008-01-11 12:08:00
932
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人