ITPub博客

SUM 功能描述：该函数计算组中表达式的累积和。SAMPLE：下例计算同一经理下员工的薪水累积值SELECT manager_id, last_name, salary,SUM (salary) OVER (PARTITION BY manager_id ORDER BY salaryRANGE UNBOUNDED PRECEDING) l_csumFROM employeesWHERE manager_id in (101,103,108);MANAGER_ID LAST_NAME SALARY L_CSUM---------- ------------------------- ---------- ----------101 Whalen 4400 4400101 Mavris 6500 10900101 Baer 10000 20900101 Greenberg 12000 44900101 Higgins 12000 44900103 Lorentz 4200 4200103 Austin 4800

ROW_NUMBER 功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。SAMPLE：下例返回每个员工再在每个部门中按员工号排序后的顺序号SELECT department_id, last_name, employee_id, ROW_NUMBER()OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_idFROM employeesWHERE department_id < 50;DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID------------- ------------------------- ----------- ----------10 Whalen 200 120 Hartstein 201 120 Fay 202 230 Raphaely 114 130 Khoo 115 230 Baida 116 330 T

LEAD 功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）SAMPLE：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值SELECT last_name, hire_date, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" FROM employees WHERE department_id = 30;LAST_NAME HIRE_DATE NextHired------------------------- --------- ---------Raphaely 07-DEC-94 18-MAY-95Khoo 18-MAY-95 24

PERCENT_RANK 功能描述：和CUME_DIST（累积分配）函数类似，对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组中所有的行数）。该函数总是返回0～1（包括1）之间的数。SAMPLE：下例中如果Khoo的salary为2900，则pr值为0.6，因为RANK函数对于等值的返回序列值是一样的SELECT department_id, last_name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS prFROM employeesWHERE department_id < 50ORDER BY department_id,salary;DEPARTMENT_ID LAST_NAME SALARY PR-------------

RATIO_TO_REPORT 功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。SAMPLE：下例计算每个员工的工资占该类员工总工资的百分比SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rrFROM employeesWHERE job_id = 'PU_CLERK';LAST_NAME SALARY RR------------------------- ---------- ----------Khoo 3100 .223021583Baida 2900 .208633094Tobias 2800 .201438849Himuro 2600 .18705036Colmenares 2500 .179856115REGR_ (Linear Regression) Functions 功

FIRST 功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值SELECT last_name, department_id, salary,MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)OVER (PARTITION BY depar

COVAR_SAMP 功能描述：返回一对表达式的样本协方差SAMPLE：下例CUM_COVS返回定价和最小产品价格的累积样本协方差SELECT product_id, supplier_id,COVAR_POP(list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,COVAR_SAMP(list_price, min_price)OVER (ORDER BY product_id, supplier_id) AS CUM_COVS FROM product_information pWHERE category_id = 29ORDER BY product_id, supplier_id;PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS---------- ----------- ---------- ----------1774 103088 01775 103087 1473

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。少数几个例子需要访问SH用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/sales_history/sh_main.sql来创建。如果未指明缺省是在HR用户下运行例子。开窗函数的的理解：开窗函数指定了分析函数工作

从下面这个结果集中，我们就可以知道一年中的所有工作日的情况。这里只所以多用一重查询是因为select level as id from dual connect by level < 102;这个结果只能出现100行，SQL> select level as id from dual connect by level < 102;ID----------1234567891011121314151617181920ID----------212223242526272829303132333435363738394041ID----------424344454647484950515253545556575859606162ID----------636465666768697071727374757677787980818283ID----------84858687888990919293949596979899100100 rows selected如

通过这个实例可以理解如何利用该包读写文件：DECLAREFILE_HANDLE UTL_FILE.FILE_TYPE;BEGINFILE_HANDLE:=UTL_FILE.FOPEN(‘d:orant’,’tmp.txt’,’a’);UTL_FILE.PUT_LINE(FILE_HANDLE,’HELLO WORLD’);UTL_FILE.FCLOSE(FILE_HANDLE);END;DECLAREDATA_LINE VARCHAR2(80);FILE_HANDLE UTL_FILE.FILE_TYPE;BEGINFILE_HANDLE:=UTL_FILE.FOPEN(‘d:orant’,’tmp.txt’,’r’);UTL_FILE.GET_LINE(FILE_HANDLE,DATA_LINE);DBMS_OUTPUT.PUT_LINE(DATA_LINE);UTL_FILE.FCLOSE(FILE_HANDLE);EXCEPTIONWHEN UTL_FILE.invalid_path THENDBMS_OUTP

SQL> select to_char(to_date('20060831','yyyymmdd') + interval '1' month,'yyyymmdd') from dual ;select to_char(to_date('20060831','yyyymmdd') + interval '1' month,'yyyymmdd') from dualORA-01839: 指定月份的日期无效 SQL> select to_char(to_date('20060831','yyyymmdd') + interval '2' month,'yyyymmdd') from dual ;TO_CHAR(TO_DATE('20060831','YY------------------------------20061031SQL> select to_char(to_date('20060831','yyyymmdd') + interval '3' month,'yyyymmdd') from dual ;select to_char(to_da

OverviewBasically, when you would like a PLSQL (or java or c) routine to be the &laquo;source&raquo;of data -- instead of a table -- you would use a pipelined function.PIPELINED functions will operate like a table.A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either

/******************************/case语句,求助高手 几个when分支执行相同操作,怎么合并到一块比如:case selectorwhen a then A;when b then A;when c then B;when d then B;when e then Cend case;/**************************************/set serverout oncreate table test (id varchar2(10));insert into test values('1');insert into test values('2');insert into test values('3');insert into test values('4');insert into test values('5');commit;declare cursor cur_test is select id from test;beginfor row_cur in cur_

一个常见的高级计算机科学问题可以在“有向图”的范畴之下描述。有向图是由一组向量和边所连接的一组有限的节点。例如，一个节点可以想象为一座“城市”，而每个向量可以想象为两座城市间的一个“航线”。有很多算法和论文讲到如何解决每种可能路线的遍历问题以及寻找最短路径或者最小代价路径的问题。这些算法中大部分都是过程化的，或者是使用递归方面来解决的。然而 SQL 的声明性语言使得解决复杂的有向图问题更加容易，而且不需要很多代码。让我们以两座城市之间的航线为例子，创建一个表保存一些假想数据：( code char(3) constra

with a as (select dummy from dual),b as (select dummy from dual)select a.dummy,b.dummy from a,b where a.dummy = b.dummy /********************************/Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subquer

create sequence STUDENT_SEQUENCE;CREATE TABLE students (id NUMBER(5) PRIMARY KEY,stu_name VARCHAR2(20),score NUMBER(10,0),grade varchar2(2));INSERT INTO students (id, stu_name, score, grade)VALUES (student_sequence.NEXTVAL, 'Scott', 98,null);INSERT INTO students (id, stu_name, score, grade)VALUES (student_sequence.NEXTVAL, 'Margaret', 88,null);INSERT INTO students (id, stu_name, score, grade)VALUES (student_sequence.NEXTVAL, 'Joanne', 75,null);INSERT INTO students (id, stu_name, score, grade)VAL

ORACLE VPD方案DROP USER VPD CASCADE;DROP ROLE R_VPD;CREATE USER VPD IDENTIFIED BY VPD;CREATE ROLE R_VPD;GRANT RESOURCE TO VPD;--VPD.VPD_PKG_FILTER.ReturnFilterCol在运行时读取视图ALL_TAB_COLUMNS需要该权限GRANT SELECT ANY TABLE TO VPD;--LOCK ALL USERS EXCEPT SYS AND SYSTEM锁定除SYS和SYSTEM的用户SET SERVEROUT ONDECLARECURSOR CUR_USERS IS SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYSTEM','SYS');BEGINFOR R_CUR IN CUR_USERS LOOPEXECUTE IMMEDIATE 'ALTER USER '||R_CUR.USERNAME||' ACCOUNT LO

环境：oracle 817 + linux + 阵列柜swd_billdetail 表5000万条数据SUPER_USER 表2800条数据连接列上都有索引，而且super_user中的一条对应于swd_billdetail表中的很多条记录表与索引都做了分析。　　实际应用的查询为：select a.CHANNEL, B.user_classfrom swd_billdetail B, SUPER_USER Awhere A.cn = B.cn;　　这样在分析时导致查询出的数据过多，不方便，所以用count(a.CHANNEL||B.user_class)来代替，而且count(a.CHANNEL||B.user_class)操作本身并不占用过多的时间，所以可以接受此种替代。　　利用索引查询出SWD_BILLDETAIL表中所有

　　背景知识：　　为了更好的进行下面的内容我们必须了解一些概念性的术语：　　共享sql语句　为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分

优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不要以为只有SELECT语句是查询。实际上，带有任何WHERE条件的DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指DML语句中的查询部分。优化器是所有关系数据库引擎中的最神秘、最富挑战性的部件之一，从性能的角度看也是最重要的部分，它性能的高低直接关系到数据库性能的好坏。我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你要取数据时，不