Oracle - PL/SQL
文章平均质量分 68
t0nsha
ocp/rhce/rhcds/rhca oracle dba/ebs dba/ebs dev
展开
-
LEFT OUTER JOIN with ON condition or WHERE condition?
I would like your explanation of the difference between a LEFT OUTER JOIN ON condition and a WHERE condition. Thank you.The difference is subtle, but it is a big difference. The ON condition stipulate转载 2015-04-25 10:58:27 · 1382 阅读 · 0 评论 -
A Simple Example of Weak Ref Cursor
DECLARE l_sql VARCHAR2(2000) := 'select deptno,loc from scott.dept'; l_where VARCHAR2(200); TYPE dept_curtype IS REF CURSOR; dept_cur dept_curtype; TYPE dept_rec IS RECORD( deptno VA原创 2011-05-11 11:08:00 · 1133 阅读 · 0 评论 -
这个sql咋写?
这个sql咋写?两个表的结构一模一样,数据大部分是重复,小部分不同;要求:1.如果a列数据相同,计算b列相减的结果test1:a b xxx 1yyy 2zzz 3 test2:a bxxx 3zzz 2qqq 1查询结果结果为:a bxxx 2zzz -1yyy -2qqq 1 Create Table:CREATE TABLE test1(a VAR原创 2011-05-04 15:02:00 · 1171 阅读 · 0 评论 -
Oracle PL/SQL之处理index不连续的table类型变量
测试代码:DECLARE TYPE list_of_names_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; happyfamily list_of_names_t; l_row PLS_INTEGER;BEGIN --build table data which index is not consecutive.原创 2011-05-25 16:20:00 · 1727 阅读 · 0 评论 -
Oracle PL/SQL之VARCHAR2 QUALIFIER
在声明VARCHAR2变量时,我们可以指定其最大长度单位为CHAR:v1 VARCHAR2(2 CHAR); 或BYTE:v2 VARCHAR2(2 BYTE); ,大多数默认什么都不指定:v3 VARCHAR2(2); 。 那么这三者有什么区别呢? 请看例子: DECLARE v1 VARCHAR2(2 CHAR); v2 VARCHAR2(2 BYTE); v3 VARCHAR2(原创 2011-05-25 14:52:00 · 1456 阅读 · 0 评论 -
Oracle日期计算之INTERVAL
在计算月份的时候除了add_months,还可用INTERVAL: Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpo SQL> SQL> SELECT to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') now FROM dual;原创 2011-05-20 19:14:00 · 5504 阅读 · 0 评论 -
Oracle PL/SQL之GROUP BY GROUPING SETS
使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来。当GROUPING SETS里面的分组元素越多时,使用GROUPING SETS比使用UNION ALL性能更好,这可能和使用GROUPING SETS只需要访问一次表有关。如下两段查询的结果是相等的: q1(GROUPING SETS):SELECT department_id, job原创 2011-06-12 00:00:00 · 4109 阅读 · 0 评论 -
Oracle 分析及动态采样
之前在说Oracle Optimizer中的CBO时讲到,当表没有做分析的时候,Oracle 会使用动态采样来收集统计信息。 获取准确的段对象(表,表分区,索引等)的分析数据,是CBO存在的基石,CBO的机制就是收集尽可能多的对象信息和系统信息,通过对这些信息进行计算,分析,评估,最终得出一个成本最低的执行计划。 所以对于CBO,数据段的分析就非常重要。 Oracle Optimizer CBO转载 2011-07-11 20:16:58 · 1880 阅读 · 0 评论 -
Oracle PL/SQL之内联接、外联接、交叉连接
oracle的联接分如下几种:内联接(inner join)。外联接(outer join):全联接(full join)、左联接(left join)、右联接(right join)。交叉联接(cross join)。外联接与内联接不一样,外连接返回到查询结果中的不仅包含符合条件的行,还包括左表(左外连接),右表(右外连接)或者两个连接表(全外连接)中的所有不符合条件的数据行。 0.内联接 ([转载 2011-08-13 22:41:41 · 2663 阅读 · 0 评论 -
Oracle PL/SQL之NEXT_DAY - 取得下一个星期几所在的日期
NEXT_DAY(date,char):返回指定日期(由date指定)后的第一个星期几(由char指定)所在的日期,char也可用1~7替代,1表示星期日。duzz$scott@orcl>select sysdate,to_char(sysdate,'day') weekday,next_day(sysdate,1) n_day,to_char(next_day(sysdate,1),'day')原创 2011-08-22 23:15:00 · 3051 阅读 · 0 评论 -
使用PL/SQL Developer进行DEBUG时Step into会直接跳过procedure
使用PL/SQL Developer的DEBUG功能我们可以很方便的对PL/SQL代码进行逐行跟踪,但今天突然发现不好使了,检查了一下,确保一下两点即可:1. 不要用sys用户去调试;2. 编译package时需加入debug信息:alter package t01 compile debug;原创 2011-11-25 16:37:42 · 3520 阅读 · 0 评论 -
Oracle PL/SQL之EXCEPTION -- WHEN OTHERS THEN
在处理EXCEPTION时应特别注意WHEN OTHERS THEN的使用,因为WHEN OTHERS THEN会吃掉所有的EXCEPTION。如果在WHEN OTHERS THEN后不抛出任何信息,即:WHEN OTHERS THEN NULL; 这可能会极大地增加以后程序排错的难度,因为即使出错了,也没有任何提示。所以我们需要特别留意WHEN OTHERS THEN,除了一个一个的手工查找外,原创 2011-05-25 11:12:00 · 7366 阅读 · 0 评论 -
Oracle PL/SQL之EXCEPTION
Test Code:DECLAREBEGIN >-- most normal way to handle exception. DECLARE except_test0 EXCEPTION; BEGIN RAISE except_test0; EXCEPTION WHEN except_test0 THEN dbms_output.put_原创 2011-05-24 17:42:00 · 2183 阅读 · 0 评论 -
Oracle之NULL IS NULL
NULL 不 大于/小于/等于/不等于 任何值(包括NULL本身),有且仅有:NULL IS NULLSQL> set serveroutput onSQL> SQL> BEGIN 2 IF (2 > NULL) 3 THEN 4 dbms_output.put_line('>'); 5 ELSIF (2 < NULL) 6 THEN原创 2011-05-20 18:51:00 · 2298 阅读 · 0 评论 -
Oracle PL/SQL之GROUP BY ROLLUP
ROLLUP字面意思大概就是向上卷,用在GROUP BY 里面可起到累积求和的作用: 没有ROLLUP的情况下,以下查询按department_id和job_id进行分组求和:SELECT department_id, job_id, SUM(salary)FROM employees WHERE department_id < 60GROUP BY department原创 2011-06-11 22:58:00 · 2140 阅读 · 0 评论 -
test the difference between "DEFAULT NULL" and "DEFAULT 0"
/*intent: test the difference between "DEFAULT NULL" and "DEFAULT 0"anthor: t0nsha(liaodunxia{at}gmail.com@20081219)*/DECLARE ln_dft_null NUMBER DEFAULT NULL; ln_dft_zero NUMBER DEFAULT 0;BEGIN原创 2008-12-19 20:30:00 · 1013 阅读 · 0 评论 -
一道关于AVG函数和NULL值的面试题
一道关于AVG函数和NULL值的面试题某部门有10个员工,其中9个工资为1000,另一个为NULL,当使用AVG函数取该部门平均工资时,结果应该是多少?A.1000B.900我这里把题目再延伸一下,如果我们count(工资)结果是多少? A.10, B.9实践出真知SQL> select * from v$version;BANNER--------------------------------原创 2013-11-26 21:08:40 · 3942 阅读 · 0 评论 -
Oracle EBS R12 - 利用PLSQL取得/破解apps密码和EBS中的用户密码
版本:Tested on R12.0.6/R12.1.1注意:请根据具体环境调整GUEST用户密码,默认情况都是ORACLE。 GUEST用户的密码可以在$CONTEXT_FILE文件中找到:view $CONTEXT_FILE 51 52 GUEST 53 ORACLE 54 用原创 2012-11-22 22:23:15 · 5001 阅读 · 0 评论 -
Oracle - 利用自带函数进行16进制与字符互转
在查看Apache log时,经常在url里面混杂着%3A,%2F等字符,究竟代表什么?只需通过Oracle自带函数转换下就可。根据16进制查字符:select chr(to_number('2F','xx')) from dual --/select chr(to_number('3D','xx')) from dual --=select chr(to_number('26','xx')) f原创 2012-11-22 22:04:38 · 22386 阅读 · 1 评论 -
Oracle sql语句中不支持boolean类型(decode&case)
Oracle sql语句中不支持boolean类型(decode&case)版本信息:SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Rel原创 2012-08-03 19:58:40 · 9767 阅读 · 0 评论 -
[面试]应聘某公司BI/DW/SQL开发的一道测试题(行转列)
题目:Provide: NAME COURSE SCORE Alex English 81 Lucy Math 79 Lucy English 82 Alex English 83 Alex Math 77 Lucy Math 75 Expect原创 2012-07-06 19:27:19 · 3229 阅读 · 5 评论 -
手动刷新share pool导致缓存序列(cache sequence)不连续/丢失
手动刷新share pool可导致缓存序列(cache sequence)不连续/丢失,dbms_shared_pool.keep可防止sequence cache不被flush share pool清空,但是不能防止shutdown引起sequence cache清空而导致的sequence断裂。C:\>sqlplus scott/tiger@t11aSQL*Plus: Release 11.2原创 2012-06-08 18:42:44 · 3168 阅读 · 0 评论 -
NLS_LANG与NLS_LANGUAGE的区别
1. 主要区别:NLS_LANG是环境变量,包括3部分NLS参数:NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET,需要在启动SQLPLUS等工具之前设置;NLS_LANGUAGE主要控制SESSION中提示消息的语言,可以使用ALTER SESSION在SQLPLUS里面设置;NLS_TERRITORY主要控制SESSION中的日期和货币等本地化参数的现原创 2012-05-12 14:47:22 · 19657 阅读 · 2 评论 -
Oracle Pipelined Table Functions
table型函数用来返回类似表结构的集合,常规的table型函数需要先填充完集合才能返回,如果集合对象太大会消耗大量内存;pipelined的table型函数能够在边填充时边返回,从而避免占用过多内存。Table FunctionsTable functions are used to return PL/SQL collections that mimic tables. They can be翻译 2012-04-19 22:32:09 · 1780 阅读 · 0 评论 -
Oracle查出一个用户具有的所有系统权限和对象权限
1. 系统权限SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT'UNION ALLSELECT * FROM dba_sys_privs WHERE grantee IN (SELECT granted_role FROM dba_role_privs原创 2012-04-29 18:44:32 · 1320 阅读 · 0 评论 -
利用Oracle VPD(虚拟专用数据库)实现类似EBS R12里的多OU屏蔽
EBS R11通过建立带有CLIENT_INFO过滤条件的视图来实现多OU屏蔽,而R12使用的则是VDP(Virtual Private Database)来实现,关联的package是MO_GLOBAL,ORG_SECURITY。 假设我们想让员工只能查看到本部门的所有员工信息:没有应用VDP之前:select * from scott.emp;-----------------------原创 2012-04-03 16:23:26 · 3012 阅读 · 0 评论 -
Oracle约束的4种状态小结
约束的4种状态:disable novalidate 既不会约束新增数据也不会验证已有数据,等同于disableenable novalidate 约束新增数据但不会验证已有数据disable validate 约束新增数据但不会验证已有数据,启用后禁止DMLenable validate 约束新增数据并验证已有数据,等同于enable扩展解释:disable / enable 针对新增数据(对于原创 2012-03-10 17:48:05 · 1643 阅读 · 0 评论 -
SQLPLUS 使用的一些技巧
SQLPLUS 使用的一些技巧 在ORACLE的维护过程中,目前ORACLE有很多易用的客户端工具,如plsql、delveloer、TOAD,但作为数据库管理员也需要经常和SQLPLUS 打交道,一些常用的操作往往需要在SQLPLUS完成,如果SQLPLUS不熟悉也在很大程度上降低我们的维护效率,本文主要介绍一些在管理数据库过程中比较有用的的SQLPLUS技巧。 技巧转载 2008-12-29 21:48:00 · 2276 阅读 · 0 评论 -
Oracle 10G中轻松发送email -- UTL_MAIL
10G之前,如果要从oracle中发送email,必须借助oracle的utl_smtp和utl_tcp功能包,自己包装一个email发送程序,然后使用.自从10G后,这个流程将变得非常简单.因为oracle给我们提供了一个utl_mail包,我们可以直接调用发送email.其实这个包底层也是调用了utl_smtp、utl_tcp的相关api. 不过utl_mail只能用于无安全验证的stmp服务转载 2011-06-20 16:02:00 · 4817 阅读 · 0 评论 -
ORACLE 分区表 PARTITION table
1.1 分区表PARTITION table在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。1.1.1 分区表的建立: 某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的是该公司1999年的数据(假设每月产生30M的数据),操作如下: 范围分区表:CREATE TABLE sales (invoice_no NUMBER, ... s转载 2011-07-11 20:38:03 · 3043 阅读 · 0 评论 -
select any dictionary与select_catalog_role的区别
select any dictionary与select_catalog_role相同之处,有了这两个中的一个,基本就可以查询数据字典不同之处:1、select any dictionary是一种系统权限(system privilege),而select_catalog_role 是一种角色(a role)。2、角色的话需要重新登录或者显式的set role 来生效,而赋予系统权限是立即生效的。转载 2011-07-17 15:11:39 · 4168 阅读 · 0 评论 -
Oracle PL/SQL之WITH查询
为什么要用WITH?1. 如果需要在一段复杂查询里多次应用同一个查询,用WITH可实现代码重用;2. WITH查询类似将查询结果保留到用户临时表里,在大的复杂查询中可以减少IO,有一定的性能优化作用。 WITH查询有何限制与特性?1. 如果当前schema下有与WITH查询别名相同的表,查询中WITH查询生成的表优先;2. 只能用于select 语句;3. WITH可包含一个或多个查询;4. WI原创 2011-08-29 22:54:04 · 1926 阅读 · 0 评论 -
Oracle PL/SQL之函数索引(Function-based indexes)使用示例
函数索引(Function-based indexes)只有在where条件使用了与索引中相同的基于相同列的函数时才起作用。 duzz$scott@orcl>set autotrace onduzz$scott@orcl>create table t1 as select * from dept;Table created.Elapsed: 00:00:00.01duzz$scott@原创 2011-08-29 23:30:51 · 3249 阅读 · 0 评论 -
RANK() OVER(PARTITION BY deptno ORDER BY empno)
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序)rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 http://www.itpub.net/thread-244881-1-1.html 基于[deptno]进行不具有重原创 2010-02-20 21:29:00 · 4406 阅读 · 0 评论 -
trap or bug when using CONTINUE in Oracle 11g
CONTINUE is a new feature brought in Oracle 11g, but there is a trap or bug need to pay attention, see the following code scrap:DECLARE CURSOR dept_cur IS SELECT deptno ,dname原创 2011-05-17 16:56:00 · 1437 阅读 · 0 评论 -
oracle merge into 用法详解
Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表. Oracle 10g中MERGE有如下一些改进: 1、UPDATE或INSERT子句是可选的 2、UPDATE和INSERT子句可以加WHERE子句 3、ON条件中使用常量过滤谓词来ins转载 2011-03-16 14:04:00 · 1676 阅读 · 0 评论 -
Oracle PL/SQL之嵌套表(Nested Table)
Test Code:Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpo SQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30); 2 / Type created SQL> SQ原创 2011-05-25 17:26:00 · 5115 阅读 · 0 评论 -
Oracle PL/SQL之Flashback Table与外键约束
我们知道 Flashback Table可以把drop掉的表从回收站里恢复回来,但是并不是关于该表的所有东西都能被Flashback回来,比如外键约束。duzz$scott@orcl>create table d(deptno number primary key, deptname varchar2(20));Table created.Elapsed: 00:00:00.28duzz原创 2011-08-25 23:55:47 · 1621 阅读 · 0 评论 -
Oracle PL/SQL之IN OUT NOCOPY
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual par原创 2011-06-24 13:59:00 · 3298 阅读 · 0 评论 -
Oracle PL/SQL之不能在function里面调用DBMS_LOCK(Grant to role OR Grant to user)
已知:测试用户tuser1,测试角色trole1,trole1已经授权给了tuser1。在测试一段程序时需要用到延时,于是就把dbms_lock授权给了trole1,放在匿名块里测试没有问题:SQL> set serveroutput on;SQL> SQL> BEGIN 2 dbms_output.put_line(systimestamp); 3 -- dbms_ba原创 2011-06-24 16:39:00 · 3048 阅读 · 0 评论