落叶无声的专栏

This was an error occurred when I followed the oracle doc guide trying to run the sqlldr command. This was a silly mistake because I was supposed to run it under cmd cmd> sqlldr ... but I accidental

例子DECLARECURSOR emp ISSELECT first_name, last_name FROM hr.employees;my_emp2 emp%ROWTYPE;fir_name hr.employees.first_name%TYPE;BEGINWITH emp_temp AS (SELECT first_name FROM hr.employees WHERE first

控制文件test.ctlLOAD DATAINFILE 'C:\case.csv'APPENDINTO TABLE TFIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'(A CHAR,B CHAR)数据文件case.csv1,2a,bb,cc,d完整例子import java.io.BufferedReader;i

How to know what date it is today SELECT TO_CHAR(SYSDATE, 'D') INTO W_D FROM DUAL; or SELECT TO_CHAR(SYSDATE, 'DAY') INTO W_D FROM DUAL;1 means Sunday 7 means SaturdayHow to get Weekends from a p

语法 connect by prior exp1 = exp2使用connect by可以实现级联查询prior的作用是指定列exp1所在的行为父行，根据此父行的数据去找其子行。表每一行数据都被会当成父行按照表达式exp1 = exp2 计算一遍。例子

分析函数的语法结构一般是： 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成： 分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等； over： 关键字，表示前面的函数是分析函数，不是普通的集合函数； 分析子句：over关键字后面挂号内的内容；分析子句又由下

妨碍优化器选择更优的执行计划，比如说索引扫描不必要的数据占用网络传输带宽，比如一个有100个字段的表你只需要若干字段，用select*将大大耗费更多的带宽资源。增删字段可能导致代码崩溃

Alter语句写法繁多，为避免忘记特意记一记。CREATE TABLE ALTER_TABLE_ACTIONS( COL1 VARCHAR2(10) NOT NULL PRIMARY KEY, COL2 VARCHAR2(10) NOT NULL);增字段col3ALTER TABLE ALTER_TABLE_ACTIONS ADD COL3 VARCHAR2(10) NOT NULL;删

例子所需材料 测试表数据文件(.csv或.dat等格式，我们将使用.csv)control文件创建测试表 假设我们有一张员工表 EMP create table EMP( EMPNO NUMBER(4) not null, ename VARCHAR2(10), JOB VARCHAR2(9), ...

例子 DECLARE TYPE salaryTab IS TABLE OF plch_employees.salary%TYPE; s_tab salaryTab; TYPE eTab IS RECORD ( last_name plch_employees.last_name%TYPE, salary plch_employees.salary%TYPE );

Floor(n) 作用 返回小于等于n的最大整数例子SELECT FLOOR(15.1) FROM dual;FLOOR(15.1)----------- 15SELECT FLOOR(-15.1) FROM dual;FLOOR(-15.1)------------SELECT FLOOR(0.1) FROM dual;FLOOR(0.1)----------

需求 有表成绩T1，记录学生成绩，表T2是一张加分的表，现在用表T2给少数名族学生加分。表成绩表T1 PID SCORE ISMINORITY---- ---------- ---------- 1 670 N 2 620 N 3 600 N 4 520 Y 5 480 N 6

set system variable

-Can I use ```INSERT INTO TABLE VALUES (...),(...),(...)```?(20161202)-How to create Trigger for auto-increment ID when inserting rows.(20161202)

Sync table A to Table B

求各个部门在员工入职时(年份-月份)的该部门的总人数写法1：SELECT DISTINCT HIRE_YEAR_MONTH, DEPARTMENT_ID, COUNT_FROM (SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'yyyy/mm') HIRE_YEAR_MONTH, COUNT(*) OVER(PARTITION BY DEPARTMEN

我们将向OE.ORDERS表中插入10万行数据，这10万行数据是随机生成的DECLARE order_id INT; customer_id INT; ORDER_MODE VARCHAR(10); ORDER_TOTAL NUMBER(10,2); ORDER_STATUS INT;BEGIN SELECT MAX(order_id) INTO order_id F

情况介绍 现有表OE.PRODUCT_INFORMATION数据量为800万，求出各个供应商供应产品的数量无使用物化视图，只在建立SUPPLIER_ID建立索引，执行时间为15s左右SELECT SUPPLIER_ID, COUNT(*)FROM OE.PRODUCT_INFORMATIONGROUP BY SUPPLIER_IDORDER BY SUPPLIER_ID;使用物化视图(M

前言 There are a couple of ways to add constraints to restrict the values in the database. This article is about how to define a foreign key constraint and a check constraint in two ways.Table T1CREAT

执行顺序Oracle会先设置行的rownum，再进行order by 执行顺序：rownum > order by举例有以下数据，现在要倒序后取前三条记录。SQL> select sysdate + level level_ from dual connect by level <= 10;LEVEL_-----------2017/8/520...