shpd2852856的专栏

Oracle -- Extended Group By Clause   ROLLUP(), CUBE(), GROUPING_SETS(), GROUPING(), GRUOPING_ID(), GROUP_ID()   1. ROLLUP() --返回n+1层小计 a) select division_id, job_id, sum(salary) from EMPLOYEE

IF condition THEN ELSIF condition THEN ELSE END IF;   LOOP     ...    CONTINUE;    CONTINUE WHEN ...    EXIT;    EXIT WHEN ... END LOOP   WHILE condition LOOP END LOOP;   FOR var IN [R

1) ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第

SQL>conn SYS/Abcd1234#@orcl as SYSDBA SQL>show user; SQL>@ D:\collection_schema.sql SQL>VARIABLE (查询Bind Variable) SQL>PRINT var_name (查询指定bind variable 中的值) SQL>DEFINE SQL>SET DEFINE '#' SQL>S

1) Bind Variable (is session specific and need to be reset if the session is lost)   (note : use F5 to run script, F9 run statement won't work) VARIABLE v_id NUMBER BEGIN   :v_id := 1; (need to e

1) 用SQLPLUS连接数据库 SQLPLUS SYS/Abcd1234#@orcl AS SYSDBA CONNECT store/store_password 2) 创建用户 CREATE USER store IDENTIFIED BY store_password; ALTER USER store IDENTIFIED BY store_pwd;   3)赋予用户权限

Oracle automatically create a B-tree Index for Primary key or the column which has UNIQUE constraint.   CREATE [UNIQUE] INDEX index_name ON tab_name(col_name[, col_name ...]) TABLESPACE tab_space;

CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] view_name AS subquery [WITH {CHECK OPTION | READ ONLY} CONSTRAINT cons_name];   FORCE -- the view can be created even the base tables doesn't exist. W

EXPORT, IMPORT are used for following tasks : Backup Oracle data in operating system files. Restore tables that were dropped Save space or reduce fragmentation in the database Move data from one o

1) CREATE [GLOBAL TEMPORARY] TABLE name (   ) [ON COMMIT {DELETE|PRESERVE} ROWS] TABLESPACE name;   ON COMMIT -- to set the duration of the rows persist in the temporary table DELETE(by defaul

1)      Avoid searching based on concatenation Select * from employee where empno || name = ’1234qiao’ Select * from employee where empno=1234 and name = ’qiao’ 2)      It is advisable to put t

1)      Some declarations may be costly process Defer the declarations till it is required Search of unwanted variables and remove them 2)      Prefer Built-in functions over the user defined.