![](https://img-blog.csdnimg.cn/20201014180756928.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL & PL/SQL
chncaesar
健身 搬砖 偶尔写个代码
展开
-
Oracle物化视图2 -- Query Rewrite及参数
Query Rewrite的条件Individual materialized views must have the ENABLE QUERY REWRITE clause.The session parameter QUERY_REWRITE_ENABLED must be set to TRUE (the default) or FORCE.Cost-based optimizati原创 2013-12-13 15:54:04 · 1559 阅读 · 0 评论 -
PL/SQL Nocopy
函数或存储过程的out 或in out参数默认是传值,加nocopy申明使得PL/SQL编译器可以决定是否使用传引用。这可以减少函数调用的开销。当函数的返回值是大对象时,这点尤为重要。In 参数使用传引用。PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) ISBEGIN NULL;END;原创 2014-02-07 16:08:26 · 698 阅读 · 0 评论 -
DBMS_Utility学习
Comma_to_table以下是例子:DECLARE v_len BINARY_INTEGER; v_tab DBMS_UTILITY.UNCL_ARRAY; v_idx BINARY_INTEGER; v_str VARCHAR2(200); BEGIN DBMS_UTILITY.COMMA_TO_TABLE ( 'a,b,c', --每个元素必须原创 2013-12-15 00:09:00 · 1002 阅读 · 0 评论 -
Oracle Application Context
Application Context是内存中的一组name-value对,application context从属于某个命名空间(namespace)。用户只能通过一个自定义procedure调用dbms_session.set_context来设置application context的值。用户使用sys_context(,)来获取某个application context的值。Ap原创 2014-01-20 16:32:55 · 1562 阅读 · 0 评论 -
Oracle Dump函数
select dump('A',16) from dual;DUMP('A',16) ----------------Typ=96 Len=1: 41 返回16进制select dump('A',8) from dual;DUMP('A',8) -----------------Typ=96 Len=1: 101--返回八进制的Ora原创 2013-12-26 22:38:56 · 737 阅读 · 0 评论 -
【趣味SQL】打印菱形
热身 - 打印三角形col data format a9;Select lpad(' ',(5-level)) || lpad('x',(2*level-1),'x') datafrom dual connect by level 思路是:长宽不变,用空格填充。最终输出三角形。DATA --------- x xxx xxxxx原创 2013-12-23 22:57:03 · 2072 阅读 · 0 评论 -
Oracle 字符函数
select to_number(1,'xxxx') from dual;原创 2013-12-26 23:21:00 · 561 阅读 · 0 评论 -
Oracle replace function to delete substring
select replace('abc', 'a') from dual;REPLACE('abc','a')---------------------------bc原创 2013-12-16 10:21:42 · 546 阅读 · 0 评论 -
SQL解决过去M天内连续N天符合某条件问题
本博客是来源CSDN BBS上一个问题。原问题如下:从系统时间往前M天内,有连续M天交易笔数小于B笔的商户。表设计:CREATE TABLE prm_tbl(zoneno NUMBER, last_days NUMBER, min_days NUMBER, min_trans NUMBER);CREATE TABLE acc_tbl(d_date DATE, merch NUMBER原创 2014-01-04 13:43:40 · 9996 阅读 · 0 评论 -
Oracle 11g Pivot函数实现列转行
先上语法规范:SELECT ....FROM PIVOT ( aggregate-function() FOR IN (, ,..., ) ) AS WHERE .....通过一个例子说明其用法:select * from (select salary, department_id from employee) pivot(原创 2013-12-14 16:45:00 · 37079 阅读 · 0 评论 -
Oracle q' 简化单引号转义
从10g开始引入该特性。q'后面的字符可以是: ! [ ] { } ( ) 前提是这些字符不会出现在后续的SQL中。请看例子,这两种写法都正确,后者显然可读性更强。select 'it''s a example' from dual;select q'[it's a example]' from dual;原创 2014-03-10 22:42:51 · 8574 阅读 · 1 评论 -
Pipelined Table Function Statistics and Dynamic Sampling
This is an extraction of Adrian Billington's article:http://www.oracle-developer.net/display.php?id=429At some point, you might need to join a pipelined function to another rowsource (such as转载 2013-12-16 10:32:09 · 840 阅读 · 0 评论 -
使用exists(Semi-Join)优化distinct语句
在Oracle 官方文档,semi-join是这么解释的:A semijoin returns rows that match an EXISTS subquery without duplicating rowsfrom the left side of the predicate when multiple rows on the right side satisfy thec原创 2013-12-16 10:23:31 · 2286 阅读 · 0 评论 -
Oracle 字符串分割
1. 使用正则表达式$hr@ORCL> col splited format a20$hr@ORCL> SELECT REGEXP_SUBSTR('first field,second field,third field', '[^,]+', 1,rownum) splitedFROM DUALCONNECT BY ROWNUM <=LENGTH ('first field, seco转载 2014-01-06 16:25:10 · 918 阅读 · 0 评论 -
Export with Spool and Parallel Utl_File
Dump with SPOOLset trimspool on --removes trailing blanks at the end of each displayed or spooled line.set feedback off --Do not display the number of records returned by a queryset termout off -原创 2013-12-16 10:33:26 · 1378 阅读 · 0 评论 -
PL/SQL 表函数, Cursor Variable, pipelined table function
表函数: 返回varray 或nested table。使用方式: select * from table(func());Pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows.sys_refcur原创 2014-03-06 13:48:52 · 981 阅读 · 0 评论 -
Oracle取随机数函数
The basic function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision).Alternatively, you can get a random Oracle number原创 2013-12-16 10:22:34 · 1272 阅读 · 0 评论 -
动态列转行
来自于:http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42asktom-1653097.htmlSQL在解析时必须要知道字段的个数和类型,用pivot和decode都已经不能满足这个需求,这时必须上动态SQL了。这里将Tom的例子改写了下:create or replace procedure sal_sum_翻译 2014-03-05 23:37:58 · 1611 阅读 · 0 评论 -
PL/SQL Conditional Compilation
该特性与C语言的类似。从Oracle 9.2.0.6开始被引入,9.2.0.6默认关闭该特性,可以通过一个参数打开。10gR1默认是打开,可以关闭。10gR2或以上默认打开,不可以关闭。常用于以下场景:代码使用Oracle某新特性实现某个需求;为了兼容老版本Oracle,需要用另一个方法实现。在编译时,可以针对Oracle版本,选择性的编译。在开发时,打开Tracing, 生产服务器上关闭原创 2014-03-11 17:15:51 · 1007 阅读 · 0 评论 -
Oracle 11g 新聚集函数listagg实现列转行
LISTAGG ( column | expression, delimiter ) WITHIN GROUP (ORDER BY column | expression)这是一个聚集函数。通过一个例子来说明其用法:SELECT department_id, LISTAGG(last_name, '; ')WITHIN GROUP (ORDER BY hire_date, la原创 2013-12-16 10:23:09 · 1151 阅读 · 0 评论 -
SQL count distinct与NULL
$sh@ORCL> select * from tt2; TT2_ID TT2_NAME TT2_DATE---------- ---------- --------- 1 1 23-DEC-13 2 23-DEC-13 3 23-DEC-13 4原创 2014-01-02 14:25:05 · 4229 阅读 · 0 评论 -
Oracle Scalar Subquery Cache
本文为翻译贴,原文章请见:http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.htmlTom Kyte介绍了Oracle的ScalarSubquery Cache特性,并将之与determinstic function, function result cache做了性能比较。翻译 2013-12-17 10:24:26 · 1320 阅读 · 0 评论 -
Oracle Reporting 5 - Windowing
Winodwing clause : rows | range between ... and ....ROWS - specifies the window in physical units (rows). For windows expressed in rows,the ordering expressions should be unique to produce deter原创 2013-12-16 13:00:54 · 624 阅读 · 0 评论 -
A Study of Inline View and Analytic Function
show relrelease 1102000300desc orders;Name Null Type ------------ ---- ------------ ORDER_ID NUMBER(38) CREATE_DATE DATE CUSTOMER_ID NUMB原创 2013-12-16 10:24:25 · 673 阅读 · 0 评论 -
Oracle常见数字函数
Round - 四舍五入Trunc - 简单截取Ceil - The CEIL function determines the smallest integer greater than (or equal to) a particular numeric value.Floor - The FLOOR function determines the largest integer l原创 2013-12-15 00:07:48 · 609 阅读 · 0 评论 -
ORA-04091和Compound Trigger(Oracle 11g)
Trigger常见有两种:行(Row Trigger)和语句(Statement Trigger)还有:Instead of Trigger和Event trigger。例子1-Row Trigger:CREATE OR REPLACE TRIGGER client AFTERINSERT ON tt1 FOR EACH rowBEGIN dbms_applicatio原创 2013-12-14 14:07:11 · 1138 阅读 · 0 评论 -
Oracle Lock Information Queries
Find out what objects are locked.select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machinefrom v$locked_object a , v$ses原创 2013-12-15 00:10:39 · 494 阅读 · 0 评论 -
Oracle 时间函数NumToYMInterval()
格式:NumToYMInterval(n, interval_unit);n: 数值类型interval_unit: 'YEAR', 'MONTH' ,或其他可以转换成这两个值之一的表达式NumToYMInterval(1, 'YEAR') :一年后的间隔NumToYMInterval(-1, 'MONTH'): 一个月前小数会被计算成整数后,再做计算:原创 2013-12-15 00:08:04 · 7634 阅读 · 0 评论 -
【总结】SQL产生N行伪列几种写法
1. 必须确保dba_objects表有足够数据select rownum from dba_objects where rownum 2. 递归的withwith data(x) as( select 1 x from dual union all select x+1 from data where x )select x from data;3. conn原创 2013-12-15 00:07:33 · 2080 阅读 · 0 评论 -
Multi-table Insert Using Pipelined Function
Thanks to Adrian Billington's work. This article is an extraction of:http://www.oracle-developer.net/display.php?id=429One source, two targetsConsider an example of loading custome转载 2013-12-13 16:50:07 · 846 阅读 · 0 评论 -
Oracle Reporting 6 - Model
Example: SELECT SUBSTR(country, 1, 20) country,SUBSTR(product, 1, 15) product, year, salesFROM sales_viewWHERE country IN ('Italy','Japan' )and product in 'Bounce'and year in (2000, 2001)MODEL原创 2013-12-16 13:01:49 · 645 阅读 · 0 评论 -
Oracle Reporting 1 - Ratio_to_Report Function
The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] ).In this series of articles, I'm using Oracle's SH原创 2013-12-16 10:34:10 · 953 阅读 · 0 评论 -
Oracle Deterministic Function
Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.You must specify this keyword if you intend to call t转载 2013-12-17 10:21:56 · 735 阅读 · 0 评论 -
Oracle Timezone
数据库参数-TIME_ZONEOracle中相关的时区大体可以分为两类:数据库时区和session时区。select dbtimezone from dual;ALTER DATABASE SET TIME_ZONE='+08:00'; --修改数据库时区select sessiontimezone from dual;ALTER SESSION SET TIME_ZONE='+0原创 2013-12-17 10:25:50 · 3178 阅读 · 0 评论 -
Oracle 正则表达式函数
RegExp_Like这个是正则表达式版的LIKE。找到名字以大写S开头,然后te,第四位是v或者ph,然后e,以n结尾的员工。SELECT first_name, last_nameFROM hr.employeesWHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')ORDER BY first_name, last_name;翻译 2013-12-16 15:58:58 · 726 阅读 · 0 评论 -
Oracle Reporting 4 - Time Series Calculations
Query 2: Use lag function to put sales of both year 2000 and 2001 in one row:WITH v AS(SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year,sum(s.amount_sold) salesFROM Sales s, Times t, Product原创 2013-12-16 10:39:57 · 1315 阅读 · 0 评论 -
Mixing inner join and outer join
SQLrelease 1102000300SQLfrom dept left outer join emp on dept.deptno=emp.deptnoorder by deptno nulls last; DEPTNO ENAME---------- ---------- 10 az原创 2013-12-16 10:32:55 · 551 阅读 · 0 评论 -
Oracle Reporting 7 - Model Examples
Looping in Model:select product, country, year, week, inventory, sale, receipts from sales_fact where country in ('Australia') and product ='Xtend Memory' model return updated rows partition by原创 2013-12-16 13:02:36 · 531 阅读 · 0 评论 -
Oracle Reporting 3 - Aggregation Level
To determine the aggregation level in a report, Oracle provides grouping_id and group_id functions.Grouping_ID:GROUPING_ID returns a single number that enables you to determine the exact GROUP B原创 2013-12-16 10:38:44 · 686 阅读 · 0 评论 -
Oracle Reporting 2 - Subtotals and Grand Total
Oracle provides rollup, cube, and grouping sets extensions to group to calculate subtotals and grandtotals. Each one fits into different scenarios. To start with, let's take a look at the following qu原创 2013-12-16 10:35:59 · 815 阅读 · 0 评论