• 保存当前窗口情况 Window->Save Layout

• PLSQL美化器 edit -> PLSQL Beautifier option 设置完了保存成 *.br 文件,然后在 rules file 路径下选择相应的路径

• PLSQLdev可以导出其设置,tools -> preferences -> 如下图的标志进行导出 × 查看执行任务： × PL/SQL develeper 按F5进行查看 × sql*plus中

  EXPLAIN PLAN FOR
            SELECT * FROM SCOTT.EMP;  --要解析的SQL脚本
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

imp: import; exp: export

• 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中 exp system/manager@TEST file=d:\daochu.dmp full=y
• 将数据库中system用户与sys用户的表导出 exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
• 将数据库中的表table1 、table2导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
• imp usr_name/pwd file=d:file_path.dmp full=y

sqlldr: sql loder。

-- input.ctl 文件
load data
CHARACTERSET ZHS16GBK          -- 本地文件的编码
infile '/path/to/local/file'   -- 本地文件的位置
append into table table_name   -- 插入到oracle的表,以及相应的插入模式
fields terminated by ','       -- 字段间的分隔符
trailing nullcols              -- 将空值转成null
(id,dz,district,street)        -- 插入表的字段及顺序

• 插入到oracle表的模式如下

  • insert: 为缺省方式,在数据装载开始时要求表为空
  • append: 在表中追加新记录
  • replace: 当数据主键相同的时候,删除旧记录,替换成新装载的记录
  • truncate: 将源表清空,重新insert数据

• 字段分隔符如下：

  • tab: X'09'

• 运行程序

  • 导入文件 sqlldr userid=system/manager@orcl control=input.ctl log=sqlldr.log bad=sqlldr.bad

• sqlloader 导入数据乱码解决方案

  所有数据库批量导入没有错误但是乱码的,都是因为编码的原因。用 sqlloader 导入成功没有报错,但是在oracle中看到乱码,可以用一下方法解决sqlldr导入乱码问题的解决

  • 通过 select * from v$nls_parameters; 查看数据库端的编码,eg: NLS_CHARACTERSET = AL32UTF8
  • 如果客户端的编码和该编码不一样,就要将客户端编码设成和服务端的一样 set NLS_LANG = AMERICAN_AMERICA.AL32UTF8
  • 同时在导入控制文件 input.ctl 指定编码 CHARACTERSET AL32UTF8

× 增加表索引

ALTER TABLE TABLE_NAME
ADD CONSTRAINT PK_NAME PRIMARY KEY (FIELD1,FIELD2...FIELDN)
USING INDEX TABLESPACE INDEX_SPACE_NAME
NOLOGGING;

× sql里面的in以及exists效率问题 × in是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询,一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当,那么用in和exists差别不大；如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in；

--例如：表A(小表),表B(大表) 换言之,就是那个表大就应该用这个表的索引
SELECT * FROM A WHERE CC IN(SELECT CC FROM B)-->效率低,用到了A表上CC列的索引；
SELECT * FROM A WHERE EXISTS(SELECT CC FROM B WHERE CC=A.CC)-->效率高,用到了B表上CC列的索引。
--相反的：
SELECT * FROM B WHERE CC IN(SELECT CC FROM A)-->效率高,用到了B表上CC列的索引
SELECT * FROM B WHERE EXISTS(SELECT CC FROM A WHERE CC=B.CC)-->效率低,用到了A表上CC列的索引。

--但是,在not exists 和not in 的对比中：无论哪个表大,用not exists都比not in 要快。
--如果查询语句使用了not in,那么对内外表都进行全表扫描,没有用到索引；而not exists的子查询依然能用到表上的索引。

-- 为新建用户赋链接权限
GRANT CONNECT, RESOURCE TO user_name;

--普通授权
GRANT SELECT SCHEMA_NAME.TABLE_NAME(VIEW_NAME) ON USER_NAME
--递归授权
GRANT SELECT SCHEMA_NAME.TABLE_NAME(VIEW_NAME) ON USER_NAME WITH GRANT OPTION

-- 建表语句:
CREATE table "TABLE_COUNT"(
   "TABLE_NAME"     NUMBER(6) not null NOT NULL UNIQUE enable,
   "COUNT"          VARCHAR2(20) not null enable,
   "DESCRIPTION"    varchar2(40) not null enable,
   "FLAG"           varchar2(40) not null enable,
   "CREATE_DATE"    varchar2(40) not null enable,
   PRIMARY KEY ("TABLE_NAME","CREATE_DATE")
);
comment on table "ZX_COUNT" is '每天产生的业务条数';
comment on column "ZX_COUNT"."TABLE_NAME"  is '表名';
comment on column "ZX_COUNT"."COUNT"       is '发生条数';
comment on column "ZX_COUNT"."DESCRIPTION" is '说明';
comment on column "ZX_COUNT"."FLAG"        is '类型标记';
comment on column "ZX_COUNT"."CREATE_DATE" is '业务发生日期';

-- 查询语句
select * from user_tab_comments where table_name='TABLE_COUNT';
select * from user_col_comments where table_name='TABLE_COUNT';

--创建视图函数
--VERSION 1
CREATE VIEW view_name
AS
SELECT employee_id,first_name,last_name,email,phone_number,salary,manager_id
FROM it_employees
WHERE job_id='it_prog';
--VERSION 2
CREATE VIEW view_name
AS
SELECT employee_id,first_name,last_name,email,phone_number,salary,manager_id
FROM it_employees
WHERE job_id='it_prog'
WITH CHECK OPTION;  --在对视图进行操作时会自动加上  WHERE job_id='it_prog'

• Oracle 常用的系统视图

-- 当前用户所有表的数量
user_tab_columns  --就是根据视图
all_tables        --所有表格
user_tables       --该用户下可以看到的所有表格(里面有表名,表空间名,记录条数)
dba_tables        --dba用户下可以看到的所有表格

all_tab_columns   --列名
all_inedxes       --索引

user_tab_cols     -- 用来获取对应用户表的列信息
user_col_comments -- 用来获取对应用户表列的注释信息
user_constraints  -- 用来获取用户表的约束条件
user_cons_columns -- 约束中用户可访问列

user_sequences    -- 当前用户
all_sequences     -- 全部序列
dba_sequences     -- dba序列

--特定用户所有的表
select * from dba_tables where owner='scott';

-- 查看表结构
SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM USER_TAB_COLS WHERE TABLE_NAME='TABLE_NAME';

--创建引索
CREATE INDEX it_lastname ON it_employees (last_name);

× 一般情况下不要在索引列用not或者其他的函数,因为这样的话会导致索引失效,会停止使用索引,然后进行全表扫描 × 索引列也不要进行计算,可以将统一的计算放在索引列之后,计算结果是一样,但是效率且不同

--低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
--高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
--避免在索引列中使用is null 或者 null
--低效: (索引失效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL;
--高效: (索引有效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;

• Oracle 表空间相关

• Oracle 的表空间、用户和用户授权

• 数据文件(dbf)是Oracle存放数据的地方,所有的数据库对象都存放在数据文件中

-- 创建表空间 并指定数据文件路径 自动扩展
CREATE TABLESPACE tablespace_name DATAFILE '/path/to/datafil.dbf' SIZE 1024m AUTOEXTEND ON NEXT 600m MAXSIZE UNLIMITED;

-- 为已存在表空间增加数据文件 一个数据文件最多到32G
ALTER TABLESPACE tablespace_name ADD DATAFILE '/path/to/datafil.dbf' SIZE 1024m AUTOEXTEND ON NEXT 600m MAXSIZE UNLIMITED;

-- 创建用户并指定默认表空间
CREATE USER user_name IDENTIFIED BY passwd DEFAULT TABLESPACE tablespace_name;

-- 为用户授权 不然用户没有链接数据库的权限
GRANT CONNECT, RESOURCE TO user_name;

• alter 相关 | 对字段操作 | 操作方法 | | ----- | ----- | | 更新字段名 | ALTER TABLE table_name RENAME COLUMN column_old TO column_new | | 添加字段 | ALTER TABLE table_name ADD column_name VARCHAR(10) | | 删除字段 | ALTER TABLE table_name DROP COLUMN column_name | | 添加字段并附值 | ALTER TABLE table_name ADD column_name NUMBER(1) DEFAULT 1 | | 修改字段值 | UPDATE table_name SET filedname=VALUE WHERE filedname=VALUE | | 修改字段数据类型 | ALTER TABLE table_name MODIFY filedname VARCHAR2(20) | | 当原来表中没有数据的时候或者新的类型和原来的类型兼容的时候 | alter table table_name modify (field_name field_type) | | 当原来有数据的时候,并且新类型和原来的类型不兼容的时候 | alter table table_name rename column fieldname to fieldname_tmp | | 增加字段 | alter table table_name add field_name field_type | | 删除 update table_name set field_name = trim(to_number(fieldname_tmp)); alter table table_name drop column fieldname_tmp;

• 日期相关

--字符串和日期间的转换
SELECT TO_DATE('2005-01-01 13:14:20','YYYY-MM-DD HH24:MI:SS') AS CHAR_TO_DATE FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS DATE_TO_CHAR FROM DUAL;

-- 日期格式不统一,将其变成统一的格式YYYYMMDD
TO_DATE(TO_CHAR(BUSDATE) , 'YYYYMMDD');

-- 当前时间
SELECT SYSDATE FROM DUAL;       -- 2016-06-18 09:41:46 PM
SELECT SYSTIMESTAMP FROM DUAL;  -- 18-6月 -16 09.43.54.722000 下午 +08:00
--转换成24小时格式的时间 HH24 HH是12小时制
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS NOWTIME FROM DUAL;

• left outer join 替代 not in

-- not in
select col_1
from table_1
where col_2 not in(
    select col_2
    from table_2
    where col_3 = 'val'
    );

-- left outer join
select t1.col_1
from table_1 t1
left join table_2 t2
  on t1.col_2 = t2.col_2
where t2.col_2 is null
  and t2.col_3 = 'val'

• 其他相关

-- 比较得出两张表的不同  --把两张表分别相减,再UNION
SELECT T1.* FROM
(SELECT * FROM TEST1
MINUS
SELECT * FROM TEST2) T1
UNION
SELECT T2.* FROM
(SELECT * FROM TEST2
MINUS
SELECT * FROM TEST1) T2;

-- 获得当前的用户名和IP地址
SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
FROM DUAL;

-- 复制表的数据
-- 目标表结构不存在
CREATE TABLE SCHEMA_NAME.TABLE_NAME AS SELECT * FROM SCHEMA_NAME.TABLE_NAME;
-- 目标表结构存在
INSERT INTO SCHEMA_NAME.TABLE_NAME SELECT * FROM SCHEMA_NAME.TABLE_NAME

-- 日志操作
-- 对该操作不写日志nologing
INSERT INTO SCHEMA_NAME.TABLE_NAME NOLOGGING SELECT COLUMN_NAME FROM OTHER_TABLE_NAME;

-- 查看表结构
-- SQLPLUS
DESC TABLE_NAME;
DESCRIBE TABLE_NAME;
--PL/SQL DEVELOPER
SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM USER_TAB_COLS WHERE TABLE_NAME='TABLE_NAME';

-- grouping set
SELECT ID,MC,SL,SUM(MC)
FROM TMP100
GROUP BY GROUPING SETS(ID,MC,SL)
--相当于
SELECT ID,NULL,NULL,SUM(MC)
FROM TMP100
GROUP BY ID
UNION ALL
SELECT NULL,MC,NULL,SUM(MC)
FROM TMP100
GROUP BY MC
UNION ALL
SELECT NULL,NULL,SL,SUM(MC)
FROM TMP100
GROUP BY SL

× 删除

-- Oracle删除效率
-- 最高
DELETE FROM hr.employees t1
WHERE t1.rowid > (
                   SELECT MIN(t2.rowid)
                   FROM hr.employees t2
                   WHERE t1.employee_id = t2.employee_id -- 按照想要唯一保留的字段进行匹配
                 );

DELETE FROM hr.employees t1
WHERE t1.ROWID NOT IN (
                    SELECT MIN(t2.ROWID)
                    FROM hr.employees t2
                    GROUP BY t2.employee_id
                  )
-- 比较高
DELETE FROM hr.employees
WHERE ROWID IN
(
  SELECT ROWID
  FROM (
              SELECT first_name,
                            last_name,
                            ROWID,
                            ROW_NUMBER() OVER(PARTITION BY first_name,last_name ORDER BY employee_id) AS staff_row
              FROM hr.employees
            )
  WHERE staff_row > 1
);

• 插入

  • insert 进行多表插入

  insert all
  when sum_orders < 10000 then
  into small_customers
  when sum_orders >= 10000 and sum_orders < 100000 then
  select customer_id,
        sum(order_total) as sum_orders
  from oe.orders
  group by customer_id;

-- 查找字段含有特定字符的表名,列名,以及生成下一步的查询结果
SELECT p1.table_name, p2.column_name, 'select count(*) from ' || p1.table_name || ' where ' || p2.column_name || ' is null;'
FROM user_tables p1
INNER JOIN user_tab_cols p2
ON p1.table_name = p2.table_name
WHERE p2.column_name LIKE 'SK%'
AND p1.table_name IN ();

-- 查找用户所能查到的所有表格的特定表名的表  生成下一步的查询结果
SELECT 'SELECT * FROM ' || T1.TABLE_NAME || ';'
FROM USER_TABLES T1
WHERE T1.TABLE_NAME LIKE '%BOND%';

-- PLSQL关于同一行里面有的是有值有的是没有值得对比 SELECT 1,NULL FROM DUAL --浅色行中深色是空 UNION ALL SELECT 1,NULL FROM DUAL --深色行中浅色的是空

--查看所有表空间及表空间大小： SELECT TABLESPACE_NAME ,SUM(BYTES) / 1024 / 1024 AS MBFROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME; --查看所有表空间对应的数据文件： SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES; --修改数据文件大小： ALTER DATABASE DATAFILE 'H:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE\USERS01.DBF' RESIZE 10240M;

/======================================/ /====== DBMS:ORACLE =========/ /=== 函数返回多个值 相当于返回一个游标对象 ===/ /======================================/ CREATE OR REPLACE FUNCTION func_emp_dept (bs_insured_id IN bs_insured.indi_id%TYPE) RETURN SYS_REFCURSOR IS po_result SYS_REFCURSOR; BEGIN OPEN po_result FOR SELECT * FROM bs_insured WHERE indi_id<=bs_insured_id; RETURN po_result; END;

delete from table_name where field_name in (sub_query)   /=============================/ /* merge 语句的情况 / / =========================== / merge into <table_name> using <table_view_or_query> on when matched then <update_clause> delete <where_clause> when not matched then <insert_clause> [log errors <log_errors_clause> <>]   / =========================== / / round 和 trunc / / =========================== / round有四舍五入,trunc只是单纯的截图字符串 select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual; 5555.67,5600,5556 select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual; 5555.66,5500,5555   / =========================== / / ceil 和 floor / / =========================== / ceil(n) 取大于等于数值n的最小整数 向上取整 floor(n)取小于等于数值n的最大整数 向零取整 select ceil(9.5) from dual; 10 select floor(9.5) from dual; 9   / =========================== / / 进制的转换 / / http://www.cnblogs.com/gaizai/p/4233780.html / / =========================== */ （二、八、十六进制） → （十进制） 例：将二进制的(101011)B转换为十进制的步骤如下：

1. 第0位 1 x 2^0 = 1；
2. 第1位 1 x 2^1 = 2；
3. 第2位 0 x 2^2 = 0；
4. 第3位 1 x 2^3 = 8；
5. 第4位 0 x 2^4 = 0；
6. 第5位 1 x 2^5 = 32；
7. 读数,把结果值相加,1+2+0+8+0+32=43,即(101011)B=(43)D。 别的也是类似的 例：将十六进制的(2B)H转换为十进制的步骤如下：
8. 第0位 B x 16^0 = 11；
9. 第1位 2 x 16^1 = 32；
10. 读数,把结果值相加,11+32=43,即(2B)H=(43)D。 （十进制） → （二、八、十六进制） 用的是短除法 例：将十进制的(43)D转换为二进制的步骤如下：
11. 将商43除以2,商21余数为1；
12. 将商21除以2,商10余数为1；
13. 将商10除以2,商5余数为0；
14. 将商5除以2,商2余数为1；
15. 将商2除以2,商1余数为0；
16. 将商1除以2,商0余数为1；
17. 读数,因为最后一位是经过多次除以2才得到的,因此它是最高位,读数字从最后的余数向前读,101011,即(43)D=(101011)B。 别的也是类似的 --一般来说都是别的进制转换成十进制,然后在通过十进制转换成别的进制 特殊的 二进制转换为八进制：将二进制以小数点区分左右,然后三个为一个单元,按照转换成十进制的办法转换;八变成二就把一个变成三个 二进制转换为十六进制：将二进制以小数点区分左右,然后四个为一个单元,按照转换成十进制的办法转换;八变成二就把一个变成四个 八进制 → 十六进制：将八进制转换为二进制,然后再将二进制转换为十六进制,小数点位置不变。   /* =========================== / / Oracle sql 数据透视 / / =========================== / WITH order_item_query AS (SELECT t1.customer_id,t2.product_id,t2.quantity,t2.unit_price FROM oe.orders t1 INNER JOIN oe.order_items t2 ON t1.order_id = t2.order_id) SELECT * FROM order_item_query /pivot(SUM(quantity) AS sum_qty FOR (product_id) IN (3170 AS p3170,3176 AS p3176,3182 AS p3182,3163 AS p3163,3165 AS p3165)) WHERE customer_id = 104 AND product_id IN (3170,3176,3182,3163,3165)/ ORDER BY customer_id   / =========================== / / oracle创建表空间、创建用户、授权、夺权、删除用户、删除表空间 / / =========================== / create tablespace tablespace_name datafile 'f:\tablespace_name.dbf' size 25M; create user user_name identified by user_pwd default tablespace tablespace_name; grant connect,resource to user_name; revoke connect,resource from user_name; drop user user_name cascade;--加cascade会连带user_name下面创建的表也一起删除了 [kæˈsked]连级 drop tablespace tablespace_name;
      / =========================== / / Oracle sql 高级编程 / / =========================== / 1、一个实例由SGA(system global area系统全局内存区域)以及一系列的后台进程组成;每一个连接到数据库的用户都是通过一个客户端进程进行管理的;客户端进程是与服务器进程相联结的,每一个服务器进程都会被分配一块PGA(process global area 进程共享内存区域) 2、ODPS数据类型（string、bigint、double、datetime、boolean）   / =========================== / / RDS / / =========================== / 关系型数据库服务（Relational Database Service,简称RDS）   / =========================== / / Oracle 一些dcl / / =========================== / 关系型数据库服务（Relational Databa drop user test cascade; create user test identified by oracle; grant connect,resource,create table,create view to test;   grant all privileges to test; grant create session to test;--赋予create session的权限 grant create table,create view,create trigger, create sequence,create procedure to test;--分配创建表,视图,触发器,序列,过程 权限 grant unlimited tablespace to test; --授权使用表空间   / =========================== / / Oracle 从本地导入数据 / / =========================== / sqlplus -s test/oracle@orcl @C:\Users\zhong\Desktop\ods_2_create_tmptables.sql > C:\Users\zhong\Desktop\out.log   / =========================== / / Oracle spool导出 / / =========================== / --suppress sql output in results set echo off --eliminate row count message at end set feedback off --make line long enough to hold all row data set linesize 1000 --suppress headings and page breaks set pagesize 0 --eliminate sqlplus prompts from output set sqlprompt '' --eliminate trailing blanks set trimspool on   --send output to file spool C:\Users\zhong\Desktop\emp_sql.csv select '"employee_id","last_name","first_name","salary"' from dual union all select employee_id||',"'|| last_name||'","'|| first_name||'",'|| salary from hr.employees; spool off exit   /====================================/ /========= Oracle 物化视图 ==========/ /====================================/ 物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。 create materialized view mv_name as select * from table_name --有ON DEMAND和ON COMMIT两种形式,前者是需要的时候才更新,后者是事务提交就更新,在没有指定类型的时候就是用ON DEMAND create materialized view mv_name refresh force on demand start with sysdate next sysdate + 1 --物化视图,每天刷新一遍 create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss') --创建物化视图,并且规定了在每天晚上10点钟更新

    demo存储过程 2017年4月16日 23:38   --简单的游标 DECLARE teacher_id NUMBER(5); --定义四个变量来存放teachers表中的内容 teacher_name VARCHAR2(50); teacher_title VARCHAR2(50); teacher_sex CHAR(1); CURSOR teacher_cur IS --定义游标teacher_cur SELECT TID,TNAME,TITLE,SEX FROM TEACHERS WHERE TID < 117; BEGIN OPEN teacher_cur; --打开游标 FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex; LOOP --将第一行的数据放入变量中,游标后移 EXIT WHEN NOT teacher_cur%FOUND; --如果游标呆了尾部则结束 IF teacher_sex = 'M' THEN --将性别为男的行放入男老师表male_teachers中 INSERT INTO MALE_TEACHERS(TID,TNAME,TITLE) VALUES(teacher_id,teacher_name,teacher_title); ELSE --将性别为男的行放入男老师表female_teachers中 INSERT INTO FEMALE_TEACHERS(TID,TANME,TITLE) VALUES(teacher_id,teacher_name,teacher_title); END IF; FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex; END LOOP; CLOSE teacher_cur; --关闭游标 END;   -- 比较复杂的存储过程 CREATE OR REPLACE PROCEDURE P_GET_DIFFERENCE_ON_DICT IS --V_TABLE_NAME IN ODS_CHK_DK.TABLE_NAME%TYPE; --V_COLUMN_NAME IN ODS_CHK_DK.COLUMN_NAME%TYPE; --ODS_CHK_DK表中的字段 V_OWNER VARCHAR2(15); V_TABLE_NAME VARCHAR2(50); V_COLUMN_NAME VARCHAR2(50); V_NOT_IN_CNT INTEGER; --目标表中的字段 V_TGT_VALUES VARCHAR(50); V_SRC_VALUES VARCHAR(50); --sql字符串 V_SQL_01 VARCHAR(200); V_SQL_02 VARCHAR(200); --声明游标 CURSOR CUR_TABEL_COLUMN_NAME IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,NOT_IN_CNT FROM ODS_CHK_DK; BEGIN --判断是否打开游标 IF CUR_TABEL_COLUMN_NAME%ISOPEN THEN NULL; ELSE OPEN CUR_TABEL_COLUMN_NAME; END IF; FETCH CUR_TABEL_COLUMN_NAME INTO V_OWNER,V_TABLE_NAME,V_COLUMN_NAME,V_NOT_IN_CNT; LOOP EXIT WHEN CUR_TABEL_COLUMN_NAME%NOTFOUND; --获取mapping目标表中与字典表不一样的字段 V_SQL_01 := 'SELECT DISTINCT ' || V_COLUMN_NAME || ' FROM ods_v1.' || V_TABLE_NAME || 't1,ods_v1.comm_dict t2 WHERE t1.' || V_COLUMN_NAME || ' != t2.dict_code AND t2.dict_type = ' || 'V_COLUMN_NAME;'; EXECUTE IMMEDIATE V_SQL_01 INTO V_TGT_VALUES; --获取字典表该字段对应dict_code的值 V_SQL_02 := 'SELECT listagg(dict_code,' || ''',''' || ')' || ' WITHIN GROUP (ORDER BY dict_type) AS NAME ' || 'FROM ods_v1.comm_dict WHERE dict_type = ' || V_COLUMN_NAME || ' GROUP BY dict_type;'; EXECUTE IMMEDIATE V_SQL_02 INTO V_SRC_VALUES;

    -- 插入结果表
    MERGE INTO ODS_CHK_DK_TOTAL P1
    USING
    (
      SELECT
         CUR_TABEL_COLUMN_NAME.V_OWNER            AS OWNER
        ,CUR_TABEL_COLUMN_NAME.V_TABLE_NAME       AS TABLE_NAME
        ,CUR_TABEL_COLUMN_NAME.V_COLUMN_NAME      AS COLUMN_NAME
        ,CUR_TABEL_COLUMN_NAME.V_NOT_IN_CNT       AS NOT_IN_CNT
        ,V_TGT_VALUES                             AS TGT_TABLE_VALUES
        ,V_SRC_VALUES                             AS SRC_TABLE_VALUES
        ,SYSDATE                                  AS UPDATETIME
      FROM DUAL
    )P2
    ON (P1.OWNER = P2.OWNER AND P1.TABLE_NAME = P2.TABLE_NAME AND P1.COLUMN_NAME = P2.COLUMN_NAME AND
        P1.NOT_IN_CNT = P2.NOT_IN_CNT AND P1.TGT_TABLE_VALUES = P2.TGT_TABLE_VALUES AND P1.SRC_TABLE_VALUES = P2.SRC_TABLE_VALUES)
    WHEN MATCHED THEN
    UPDATE SET P1.UPDATETIME = P2.UPDATETIME
    WHEN NOT MATCHED THEN
    INSERT VALUES
    (
      P2.OWNER
      ,P2.TABLE_NAME
      ,P2.COLUMN_NAME
      ,P2.NOT_IN_CNT
      ,P2.UPDATETIME
    );
    COMMIT;
  
    FETCH TABEL_COLUMN_NAME INTO V_OWNER,V_TABLE_NAME,V_COLUMN_NAME,V_NOT_IN_CNT;
END LOOP;

  EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,V_SQL_01); RAISE_APPLICATION_ERROR(-20001,V_SQL_02); END; /   oracle常用系统表 2017年4月2日 13:03   /* --Oracle 系统视图
TABLE_NAME --表、视图或Clusters名称 COLUMN_NAME --列名 DATA_TYPE --数据类型 DATA_TYPE_MOD --Datatype modifier of the column DATA_TYPE_OWNER --Owner of the datatype of the column DATA_LENGTH --长度 DATA_PRECISION --精度 USER_TAB_COLUMNS --就是根据视图 USER_TAB_COLUMNS --创建的 /   --查找用户所能查到的所有表格的特定表名的表 生成下一步的查询结果 SELECT 'SELECT * FROM ' || T1.TABLE_NAME || ';' FROM USER_TABLES T1 WHERE T1.TABLE_NAME LIKE '%BOND%';   --查找字段含有特定字符的表名,列名,以及生成下一步的查询结果 SELECT p1.table_name, p2.column_name, 'select count() from ' || p1.table_name || ' where ' || p2.column_name || ' is null;' FROM user_tables p1 INNER JOIN user_tab_cols p2 ON p1.table_name = p2.table_name WHERE p2.column_name LIKE 'SK%' AND p1.table_name IN ();     拼接sql 2017年4月2日 13:01   SELECT 'SELECT * FROM ' || A.TABLE_NAME || ';' FROM (                 SELECT *                 FROM USER_TABLES T                 WHERE T.TABLE_NAME LIKE '%MAPP%'          ) A WHERE A.TABLE_NAME LIKE '%IP%'     存在更新 不存在插入 2017年4月2日 13:11   Oracle 存在修改,不存在插入记录   Oracle分析函数 2017年4月2日 13:11   oracle分析函数   关于行转列的问题 2016年10月19日 21:35   关于行转列的问题,给出了如下的情况 o -- 用decode和case when对表中已有的数据进行区分 o-- LISTAGG()可以将字段值串在一起,把它当作SUM()函数来使用 o-- wm_concat 函数将所选字段的值全部用,拼接 o-- povit和unpovit将行转列的方法     -- 用decode和case when对表中已有的数据进行区分   -- 测试列转行 CREATE TABLE TEST( student VARCHAR(12), course VARCHAR(12), score VARCHAR(12) );   -- 插入测试数据 INSERT INTO TEST(STUDENT,COURSE,SCORE) select '张三','语文',78 from dual union select '张三','数学',87 from dual union select '张三','英语',82 from dual union select '张三','物理',90 from dual union select '李四','语文',65 from dual union select '李四','数学',77 from dual union select '李四','英语',65 from dual union select '李四','物理',85 from dual;   -- 提交数据 commit   --查询插入结果 SELECT * FROM TEST;   -- #1 列转行 是以原表中其中的一个值为依据转换成行的 select student , SUM(DECODE(course, '数学', score)) AS 数学 , SUM(DECODE(Course, '物理', Score)) AS 物理 , SUM(DECODE(Course, '英语', Score)) AS 英语 , SUM(DECODE(Course, '语文', Score)) AS 语文 from TEST group by Student;   -- #2 列转行 把decode 变成了case when select student , sum(case Course when '数学' then Score else null end) as 数学 , sum(case Course when '物理' then Score else null end) as 物理 , sum(case Course when '英语' then Score else null end) as 英语 , sum(case Course when '语文' then Score else null end) as 语文 from TEST group by Student;   -- 删除表 drop table TEST;   -- LISTAGG()可以将字段值串在一起,把它当作SUM()函数来使用 -- 最基本的用法 LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)   -- 建表temp CREATE TABLE temp( nation VARCHAR2(20), city VARCHAR2(20) );   -- 插入模拟数据 INSERT INTO temp VALUES ('china', 'guangdong'); INSERT INTO temp VALUES ('china', 'shanghai'); INSERT INTO temp VALUES ('china', 'beijing'); INSERT INTO temp VALUES ('USA', 'New York'); INSERT INTO temp VALUES ('USA', 'boston'); INSERT INTO temp VALUES ('japan', 'Tokyo');   -- 检测数据 SELECT * FROM temp;   -- #1 比较基本的用法 SELECT nation -- 最基本的用法 LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX) ,listagg(city, ',') within GROUP(ORDER BY city) FROM temp GROUP BY nation;   -- 建表temp1 CREATE TABLE temp1( population NUMBER(8,0), nation VARCHAR2(20), city VARCHAR2(20) );   -- 模拟数据 INSERT INTO temp1 VALUES(500, 'china', 'guangdong'); INSERT INTO temp1 VALUES(1500, 'china', 'shanghai'); INSERT INTO temp1 VALUES(2000, 'china', 'beijing'); INSERT INTO temp1 VALUES(1000, 'usa', 'new york'); INSERT INTO temp1 VALUES(500, 'china', 'boston'); INSERT INTO temp1 VALUES(500, 'japan', 'tokyo');   -- #2 加了partition by SELECT population ,nation ,city ,listagg(city, ',') within GROUP(ORDER BY city) over(PARTITION BY nation) rank FROM temp1;   -- 删除测试表 DROP TABLE temp; DROP TABLE temp1;     -- wm_concat 函数将所选字段的值全部用,拼接 -- 相关连接 : http://www.2cto.com/database/201501/367164.html   -- 建表 create table test_1( id number, name varchar2(20) );   -- 模拟数据数据 insert into test_1 values(1,'a'); insert into test_1 values(1,'b'); insert into test_1 values(1,'c'); insert into test_1 values(2,'d'); insert into test_1 values(2,'e');   -- 查看数据 SELECT * FROM test_1;   -- wm_concat函数 select wm_concat(name) AS name from test_1;   -- wm_concat函数 replace替换分隔符 select replace(wm_concat(name),',','|') from test_1;   -- 加Group by select id,wm_concat(name) name from test_1 group by id;   -- 通过wm_concat函数,建立create table 语句 利用了系统视图 /** 这里的表名默认区分大小写 */ -- select * from user_tab_columns 系统视图 select 'create or replace view as select '|| wm_concat(column_name) || ' from TEST_1' AS sqlStr from user_tab_columns where table_name='TEST_1';   -- 删除表 DROP TABLE test_1;     -- povit和unpovit将行转列的方法 -- http://blog.csdn.net/fw0124/article/details/7943965 -- SELECT .... -- FROM -- PIVOT -- ( -- aggregate-function() -- FOR IN (, ,..., ) -- ) AS -- WHERE .....     -- 创建表 create table demo( id int, name varchar(20), nums INT );   -- 模拟数据 INSERT INTO demo VALUES (1, '苹果', 1000); INSERT INTO demo VALUES (2, '苹果', 2000); INSERT INTO demo VALUES (3, '苹果', 4000); INSERT INTO demo VALUES (4, '橘子', 5000); INSERT INTO demo VALUES (5, '橘子', 3000); INSERT INTO demo VALUES (6, '葡萄', 3500); INSERT INTO demo VALUES (7, '芒果', 4200); INSERT INTO demo VALUES (8, '芒果', 5500);   -- 查看表数据 SELECT * FROM demo;   -- 形成中间表 SELECT NAME ,SUM(nums) nums FROM demo GROUP BY NAME;   -- 行转列的函数 SELECT * FROM (SELECT NAME ,nums FROM demo) pivot(SUM(nums) FOR NAME IN( '苹果', '橘子', '葡萄', '芒果' ) );   -- 删除原表 DROP TABLE demo;     truancate 和 delete的区别 2017年4月2日 13:13   truncate不能加限制条件 而delete可以加限制条件。所以进行增量抽取的话一般是用delete     row_number 和 row_id 去重 2017年4月18日 17:27   delete from hr.employess where rowid in ( select rowid from (select first_name,last_name,rowid,row_number() over(partition by first_name,last_name order by employee_id) as staff_row from hr.employess ) where staff_row > 1 );     DDL 2017年4月2日 13:06       2016年10月16日 19:18   用户设置限权是有目的的,不要用超过指定限权的用户登录 视图表结构不会更新,但是数据会更新 内联视图就是在from后面加一个select 的子查询 oracle事务处理的四大属性 原子性、一致性、隔离性、持久性

rownum和rowid都是伪列,但是两者的本质是不同的

• rownum是根据sql查询出的结果给每行分配一个逻辑编号,所以运行的sql不同也就会导致最终rownum不同.rownum编号是从1开始的
• rowid是物理结构,在每条记录insert到数据库中时,都会有一个唯一的物理记录.使用rowid可以更快实现某些功能

-- 删除学生表中姓名重复只留学号最大的学生的记录
delete from students a
where rowid not in (
  select max(rowid)
  from students b
  where a.name = b.name
    and a.no < b.no
);