代码改变世界

随笔分类 -  Oracle's SQL and SQL*Plus

Oracle: SQL精妙SQL语句讲解

2011-12-01 15:20 by Tracy., 792 阅读, 收藏, 编辑
摘要: 一、重复操作查询--where条件得distinct systemdicid作为唯一标识select * from dmis_zd_systemdic t WHERE typeid = '06012' and t.systemdicid in (select min(systemdicid) from dmis_zd_systemdic where typeid = '06012' group by name) order by orde... 阅读全文

Oracle: wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

2011-12-01 14:58 by Tracy., 2393 阅读, 收藏, 编辑
摘要: 构建测试表:Sql代码create table TABLE1 ( ID INTEGER, NAME VARCHAR2(10) ) create table TABLE2 ( ID INTEGER, ROLE VARCHAR2(10) ) insert into TABLE1 (ID, NAME) values (1, '张三'); insert into TABLE1 (ID, NAME) values (2, '李四'); commit; insert into TABLE2 (ID, ROLE) val... 阅读全文

Oracle:指定时间范围内的周分组输出.

2011-11-23 14:48 by Tracy., 1921 阅读, 收藏, 编辑
摘要: 今日QQ群有人询问 “大家好,我最近接到一项任务,在给定的数据库中添加周字段,一致为2000-01-01~2020-12-31的日期字段 已知 大家能提点意见吗?谢谢”结果类似下图,本着为以后不会忘记这个问题的想法,记录一下SQL.PS: 周六开始为1周起点,周五为周末。/* Formatted on 11/23/2011 2:35:33 PM (QP5 v5.163.1008.3004) */WITH src AS (SELECT TRUNC (DATE '2000-1-1') fromdate, TRUNC (DATE '2001-2-1') toda... 阅读全文

Oracle:DBMS_RANDOM.VALUE取随机数.

2011-11-03 16:50 by Tracy., 781 阅读, 收藏, 编辑
摘要: 两种取随机记录的写法。第二种比较好。WITH src AS (SELECT COLUMN_VALUE str FROM TABLE (fn_split ('ABDE,EDCE,FEDC', ','))), num AS (SELECT str, ROW_NUMBER () OVER (ORDER BY NULL) rn FROM src), ran AS (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 4)) rand FROM DUAL)SELECT * FROM num WHERE rn = (SELECT rand ... 阅读全文

Oracle:左外联outer join 与(+) From Itpub.

2011-10-14 15:01 by Tracy., 1354 阅读, 收藏, 编辑
摘要: 们知道,从Oracle9i开始,对于外连接(Outer join)Oracle支持SQL92标准:这个标准有很多新的连接语法,当然不仅是外连接了,这里,我主要讨论的是外连接的新语法:Left Outer Join和Right Outer Join,Full Outer Join这里不会讨论,我会额外总结所有的SQL92新的连接语法。对于接触Oracle比较早的开发人员或DBA来说,外连接用+号已经很习惯了,但是新语法有很多好的优点,Oracle也强烈建议9i以及之后的版本使用新的外连接语法。OK,下面进入正题,那么外连接的新旧语法之间有什么差异呢?请看DINGJUN123>drop ta 阅读全文

.NET调用ORACLE存储过程使用数组参数

2011-10-13 16:00 by Tracy., 394 阅读, 收藏, 编辑
摘要: ----------包定义create or replace package packtestas type string_array is table of varchar2(21) index by binary_integer; type int_array is table of number(4) index by binary_integer; procedure test(v_string in string_array, v_int out int_array); end packtest; --------包体 Create or replace package bo... 阅读全文

Oracle:DEFERRABLE 约束和级联更新

2011-09-23 15:11 by Tracy., 1262 阅读, 收藏, 编辑
摘要: DEFERRABLE 约束和级联更新从Oracle8.0 开始,我们还能够延迟约束检查,对于许多操作来说,这很有好处。首先能想到的是,可能需要将一个主键的UPDATE 级联到子键。也许很多人会说:这没有必要,因为主键是不可变的(我就是这些人之一),但是还有人坚持要有级联UPDATE。有了可延迟的约束,就使得级联更新成为可能。注意一般认为,完成更新级联来修改主键是很不好的做法。这会破坏主键的意图。如果你必须做一次级联更新来修正不对的信息,这倒是可以的;但是如果你发现自己在不停地完成级联更新,并把这当做应用的一部分,那就是另一码事了,你应该退一步,重新考虑一下这个过程。倘若真是这样,能你就是错把鸡 阅读全文

Oracle学习网址收集中

2011-09-01 15:45 by Tracy., 360 阅读, 收藏, 编辑
摘要: 官方文档:http://tahiti.oracle.com/metalink:http://metalink.oracle.comitpub:www.itpub.comTOM:asktom.oracle.comoracle forum:http://forums.oracle.com/forums/main.jspa?categoryID=84OTN:http://www.oracle.com/technology/index.htmlwww.oracle.com.cnhttp://yangtingkun.itpub.net/http://space.itpub.net/?4227www.ey 阅读全文

Oracle:Virtual Columns in Oracle Database 11g Release 1

2011-08-24 10:35 by Tracy., 403 阅读, 收藏, 编辑
摘要: When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]If the datatype is omitted, it is determined bas 阅读全文

Oracle:PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1

2011-08-24 10:25 by Tracy., 713 阅读, 收藏, 编辑
摘要: This article shows how to use the new PIVOT and UNPIVOT operators in 11g, as well as giving a pre-11g solution to the same problems.PIVOTUNPIVOTPIVOTThe PIVOT operator takes data in separate rows, aggregates it and converts it into columns. To see the PIVOT operator in action we need to create a tes 阅读全文

Oracle:【DEADLOCK】Oracle“死锁”模拟

2011-08-24 10:07 by Tracy., 2800 阅读, 收藏, 编辑
摘要: 本着实验优先的原则,先模拟死锁的发生,然后在列一下死锁产生的四个必要条件和处理死锁的一般策略。1.创建两个简单的表t1_deadlock和t2_deadlock,每个表中仅仅包含一个字段asys@ora10g> conn sec/secConnected.sec@ora10g> create table t1_deadlock (a int);Table created.sec@ora10g> create table t2_deadlock (a int);Table created.2.每张表中仅初始化一条数据sec@ora10g> insert into t1_d 阅读全文

Select for update nowait

2011-08-24 09:28 by Tracy., 545 阅读, 收藏, 编辑
摘要: Suppose steps below:1. begin TX12. in TX1, do update table1 set a = 1;3. begin TX24. in TX2, doselect * from table1 for update nowait;Since TX1 is holding a lock, so TX2 failed to acquire a lock. Then the "select * from table1 for update nowait" will return with an error. I called it " 阅读全文

在Oracle中创建视图时, 如果我们用了”*”符号

2011-08-23 10:15 by Tracy., 656 阅读, 收藏, 编辑
摘要: 在Oracle中创建视图时, 如果我们用了”*”符号, 会被自动地根据当时表的定义扩展成字段列表, 在后面再加列时, 新的列不会自动出现在视图的定义中, 直到你重建视图为止. 那么在MVIEW中呢, 通过一个不经意的操作, 发现一个有趣的问题. 总之, 不要随便地在实体视图的定义中使用”*”号.下面我们在一个表上建两个实体化视图, 角本如下:CREATE TABLE T_MVTEST AS SELECT * FROM TAB;CREATE MATERIALIZED VIEW LOG ON T_MVTEST WITH ROWID,SEQUENCE; CREATE MATERIALIZED ... 阅读全文

Oracle: What is the ORACLE_SID and DB_NAME name ?

2011-08-11 15:35 by Tracy., 292 阅读, 收藏, 编辑
摘要: Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SIDIf ORACLE_SID = DB_SID and db_name = DBNAME:To find the current value of ORACLE_SID: SVRMGR> select instance from v$thread; INSTANCE ---------------- DB_SIDTo find the current value of DB_NAME: SVRMGR> s 阅读全文

Asktom Oracle:How to multiplex single row into multiple rows

2011-08-11 10:03 by Tracy., 436 阅读, 收藏, 编辑
摘要: Hi Tom, First of all, thanks for your tremendous contribution to the Oracle Community in helping people like us solve day to day Oracle replated problems. I have a small problem. Here are the details: CREATE TABLE T ( HS_ID NUMBER(20) PRIMARY KEY, HS_NM VARCHAR2(30 BYTE), HS_STRT_DT DATE, HS_END_DT 阅读全文

Oracle:Not exists

2011-08-02 17:32 by Tracy., 625 阅读, 收藏, 编辑
摘要: I agree with using not exists. Consider a case where you want to perform more logic than just "One not in the other".Consider Item, Sale, and SaleDetail:create table Item( ItemId number(6), ItemDescription varchar(200), UnitPrice number(18,6))/Create table sale( SaleId number(6), Transacti 阅读全文

Oracle:使用pipeline function获得实时输出

2011-08-02 15:58 by Tracy., 832 阅读, 收藏, 编辑
摘要: 在普通的函数中,使用dbms_output输出的信息,需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端实时的输出函数执行过程中的一些信息,在oracle9i以后可以使用管道函数(pipeline function)。关键字PIPELINED表明这是一个管道函数,管道函数的返回值类型必须为集合,在函数中,PIPE ROW语句被用来返回该集合的单个元素,函数以一个空的 RETURN 语句结束,以表明它已经完成。create or replace type MsgType as table of varchar2(4000);/create or replace function 阅读全文

oracle嵌套表--整理的学习资料

2011-08-02 15:49 by Tracy., 685 阅读, 收藏, 编辑
摘要: oracle嵌套表--整理的学习资料自己整理了一下一、嵌套表的定义: 嵌套表是表中之表。一个嵌套表是某些行的集合,它在主表中表示为其中的一列。对主表中的每一条记录,嵌套表可以包含多个行。在某种意义上,它是在一个表中存储一对多关系的一种方法。考查一个包含部门信息的表,在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中,将需要建立两个独立的表department和project. 嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作,就可以通过department表直接访问项目表中的记录。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义 阅读全文

fetch sys_refcursor type variable to table%rowtype variable

2011-07-28 16:49 by Tracy., 330 阅读, 收藏, 编辑
摘要: SQL> CREATE TABLE EMP( 2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );Table created.SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-19 阅读全文

Oracle:临时表的统计信息

2011-07-19 14:03 by Tracy., 1867 阅读, 收藏, 编辑
摘要: 使用临时表的sql优化案例二-临时表的统计信息对于基于成本优化器的数据库来说,准确的统计信息对数据库是否选择最优化的执行方式尤其重要。然而对于临时表,利用这种方式统计信息却有可能造成明显的效率故障。因为临时表平时都是空表,如果对其进行分析,其统计信息记录数为0行,与实际使用时的情况相差甚远。 那么对于临时表,我们建议自己来指定统计信息:1,估算临时表行数 根据实际业务估算临时表在计算过程中可能产生多少数据, 2,计算临时表block数 简单的方法就是建一个与临时表结构相同的固定表,插入上面估算出的数据,例如估算出2000条,则根据临时表数据生成情况插 入2000条数据。然后分析表得到其bloc 阅读全文
点击右上角即可分享
微信分享提示