Oracle SQL - 随笔分类 - Coldest Winter

Oracle 分析函数

摘要：分析函数官方帮助文档：http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174一、Top-N Queries，row_number()参考网址：http://www.oracle-base.com/articles/misc/top-n-queries.php1、row_number() TheROW_NUMBERanalytic function is similar to theROWNUMpseudocolumn in that it assigns a unique number. 阅读全文

posted @ 2012-11-15 06:56 Coldest Winter 阅读(540) 评论(0) 推荐(0)

Converting delimited lists to collections (and vice versa)

只有注册用户登录后才能阅读该文。

posted @ 2012-05-20 09:47 Coldest Winter 阅读(23) 评论(0) 推荐(0)

Oracle随机创建表并删除

摘要：1、代码: 1 DECLARE 2 v_sql VARCHAR2(100) := 'create table t(id varchar2(100))'; 3 v_id VARCHAR2(100); 4 v_table_name VARCHAR2(30) := dbms_random.string('l', 10); 5 BEGIN 6 NULL; 7 v_sql := 'create table ' || v_table_name || '(id varchar2(100))'; 8 dbms_output.put_line(v_ 阅读全文

posted @ 2012-05-10 05:57 Coldest Winter 阅读(296) 评论(0) 推荐(0)

REPLACEF

摘要：参考网址:http://www.oracle-developer.net/content/utilities/replacef.sql A function to simplify string building and debugging by replacing multiple placeholders from a collection of inputs.定义集合的类型:1 CREATE OR REPLACE TYPE replacef_ntt AS TABLE OF VARCHAR2(4000);函数: 1 CREATE OR REPLACE FUNCTION replace... 阅读全文

posted @ 2012-05-05 10:09 Coldest Winter 阅读(289) 评论(0) 推荐(0)

MERGE INTO

摘要：参考资料:http://www.oracle-developer.net/content/utilities/merge_counter.sql文档:Oracle Database SQL Reference, 10g Release 2 (10.2)---1235页Oracle MERGE INTO的用法总结: Use the MERGE statement to select rows from one or more sources for update orinsertion into a table or view. You can specify conditions to de. 阅读全文

posted @ 2012-05-03 22:09 Coldest Winter 阅读(1335) 评论(0) 推荐(0)

Oracle date

摘要：date 阅读全文

posted @ 2012-04-22 07:15 Coldest Winter 阅读(1221) 评论(0) 推荐(0)

Oracle正则表达式

摘要：参考网址:http://www.oradev.com/regular_expressions_sql_oracle.jspMetacharactersMeaningExamples\Indicates that the match character is a special character, a literal, or a backreference. (A backreference repeats the previous match.)相当于转义符\n matches the newline character,\\ matches \, \( matches (, \) matc 阅读全文

posted @ 2012-04-07 23:04 Coldest Winter 阅读(485) 评论(0) 推荐(0)

退出嵌套的FOR LOOP循环(直接退出所有的循环)

摘要：推出嵌套的FOR LOOP循环，有两种方法，第一种，使用GOTO语句跳出循环，跳到指定的位置，这时候，需要使用LABLE标记符； 1 DECLARE 2 V_COUNT NUMBER := 6; 3 BEGIN 4 FOR IDX1 IN 1 .. 5 LOOP 5 DBMS_OUTPUT.PUT_LINE('first---' || IDX1); 6 FOR IDX2 IN 1 .. 5 LOOP 7 DBMS_OUTPUT.PUT_LINE('secord---' || IDX2); 8 FOR IDX3 IN 1 .. 5 LOOP 9 ... 阅读全文

posted @ 2012-03-26 20:50 Coldest Winter 阅读(1085) 评论(0) 推荐(0)

Oracle Function-NEXT_DAY,TRUNC,ROUND,CEIL,SIGN

摘要：注意:国际通用的日期中；周末才是一个周的第一天ROUND and TRUNC Date Functionsnext lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.Date F 阅读全文

posted @ 2012-03-10 11:55 Coldest Winter 阅读(667) 评论(0) 推荐(0)

Oracle Function: Case and Decode

摘要：CASE expression perform if-then-else logic in SQL without having to use PL/SQL.CASE works in a similar manner to DECODE().CASE is ANSI-compliant.There are two types of CASE expressions: Simple case expressions use expressions to determine the returned value. Searched case expressions use condi... 阅读全文

posted @ 2012-03-10 09:44 Coldest Winter 阅读(871) 评论(0) 推荐(2)

Oracle With Clause

摘要：本文参考网址:http://psoug.org/reference/with.html http://www.oracle-base.com/articles/misc/with-clause.php http://gennick.com/with.html------Understanding the WITH Claus 参考文档下载地址:http://ishare.iask.sina.com.cn/f/21674385.html The WITH query_name clause lets you assign a nam... 阅读全文

posted @ 2012-03-08 20:56 Coldest Winter 阅读(3967) 评论(0) 推荐(1)

Flashback—Tables and Databases

摘要：As of Oracle Database 10g, you can use the flashback table and flashback databasecommands to simplify your data-recovery efforts. The flashback table commandautomates the process of restoring a full table to its prior state. The flashbackdatabase command flashes back an entire database, and it req.. 阅读全文

posted @ 2012-03-07 22:38 Coldest Winter 阅读(374) 评论(0) 推荐(0)

table join(left,right,inner)

摘要：Three different types of joins(三种不同方式的连接)inner joins - Return a row only when the columns in the join containvalues that satisfy the join condition.This means that if a row has a null value in one of the columns in thejoin condition, that row isn't returned.Outer joins - Can return a row even wh 阅读全文

posted @ 2012-02-11 17:56 Coldest Winter 阅读(431) 评论(0) 推荐(0)

一个 SQL 同时验证帐号是否存在、密码是否正确

摘要：user login 阅读全文

posted @ 2012-02-10 20:52 Coldest Winter 阅读(384) 评论(0) 推荐(0)

根据输入的用户ID串，返回用户名字串:TRIM函数的使用

摘要：substr,instr,trim,ltrim,rtrim, 阅读全文

posted @ 2012-01-12 22:44 Coldest Winter 阅读(575) 评论(0) 推荐(0)

Instr()函数的使用--计算字符串中出现某个字母或单词的个数

摘要：The following example searches the string "CORPORATE FLOOR", beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) &quo 阅读全文

posted @ 2012-01-11 15:29 Coldest Winter 阅读(963) 评论(0) 推荐(0)

RETURNING的使用:

摘要：RETURNING 阅读全文

posted @ 2012-01-01 19:18 Coldest Winter 阅读(423) 评论(0) 推荐(0)

oralce分析函数---group by || rollup || cude || grouping || grouping sets

摘要：1、group by的使用--根据DEPTNO和JOB进行分组。求相同DEPTNO,相同JOB的员工工资总和。SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY E.DEPTNO,E.JOBORDER BY E.DEPTNO结果：2、group by 配合rollup的使用rollup()--可以使用一个或者多个参数。意思是从右向左进行数据的汇总统计，并生成一行，rollup是个统计函数。是根据分组情况进行统计，最终进行全部汇总。（1）、简单的使用rollup--生成一行新数据。(要生成新的一行数据，还可以使用UNION ALL)1 SEL 阅读全文

posted @ 2011-12-10 15:47 Coldest Winter 阅读(913) 评论(0) 推荐(0)

随笔分类 - Oracle SQL