CREATE OR REPLACE FUNCTION AGE_IN_IDCARD ( parameter VARCHAR2 ) RETURN NUMBER IS val DATE; BEGIN IF parameter is NULL THEN RETURN -1; ELSE val := CASE WHEN LENGTH(parameter) <= 15 THEN TO_DATE( '19'||NVL( SUBSTR( parameter, 7, 6 ), 'a' ), 'yyyy-MM-dd' ) ELSE TO_DATE( NVL( SUBSTR( parameter, 7, 8 ), 'a' ), 'yyyy-MM-dd' ) END; END IF; RETURN TRUNC( ( TO_CHAR( SYSDATE, 'yyyyMMdd' ) - TO_CHAR( val, 'yyyyMMdd' ) ) / 10000 ); EXCEPTION WHEN OTHERS THEN RETURN - 1; END;
两台数据库服务器，一台是oarcle11g (IP：192.168.1.31)，另一台是oracle19c，需要把数据从11g导入到19c。由于11g的数据库有使用到自动表分区，exp导出报错，只能使用expdp命令了。现在首先要做的是把数据从11g导出到19c的服务器上。第一步：在19c上创建dblink，执行SQL语句创建 link_to_11g，这里的用户名和密码是11g上的用户，确保这个用户拥有exp_full_database，最好是用system用户；CREATE PUBLIC database link link_to_11g CONNECT TO username IDENTIFIED BY "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.31)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'; 第二步：在19c上创建DIRECTORY，执行SQL语句创建 expdp_dir；CREATE DIRECTORY expdp_dir AS 'D:/databak/'; 第三步：在19c服务器上创建expdp的parfile文件 D:\exp.par，userid为19c的用户，要拥有exp_full_database权限，最好用system用户，network_link和directory是前两步创建的，dumpfile是导出的文件名，logfile是日志文件名，schemas是11g的用户名，示例内容如下：userid=system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=expdp.log schemas=USER_1,USER_2,USER_3 network_link=link_to_11g 第四步：在19c服务器上打开CMD，执行导出命令：expdp parfile=D:\exp.par 第五步：在19c服务器上打开CMD，执行导入命令：impdp system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=impdp.log full=yes 参考：https://blog.csdn.net/weixin_31688465/article/details/113398278
--创建用户create user username identified by password;--给用户权限grant all privileges to username;--给用户dba权限grant connect,resource,dba to username;--导入备份文件imp username/password file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log full = y--导入指定用户到指定用户imp BM/bm123@ORCL fromuser=BM touser=BM2 file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log ignore=y--导出指定用户exp tc/"tc_19" owner=ZM file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log--远程导出exp tc/"tc_19"@172.16.0.3:1521/orcl file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log full=y--远程导入imp tc/"erp-ch"@172.16.0.3:1521/orcl file = C:/tc.dmp log = C:/tc.dmp.log full = y statistics=nonelinux安装oracle客户端参考： https://blog.csdn.net/yuki5233/article/details/115724257临时环境变量，输入以下命令起效。export ORACLE_HOME=/usr/lib/oracle/12.2/client64export TNS_ADMIN=$ORACLE_HOME/network/adminexport NLS_LANG='simplified chinese_china'.ZHS16GBKexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=$ORACLE_HOME/bin:$PATH
select username,machine,osuser,count(1) cnt from v$session where username is not null group by username,machine,osuser ORDER BY count(1) desc;
Oracle ODP.NET 读取数据库表结构时 EXPRESSION 等LONG类型字段为空
Oracle ODP.NET 读取 数据库表结构时 COLUMN_EXPRESSION 等LONG类型字段为空，比如：SELECT * FROM ALL_IND_EXPRESSIONS 读取时 COLUMN_EXPRESSION 为空值，但是System.Data.OracleClient连接方式是可以读出来的。处理方法：((Oracle.ManagedDataAccess.Client.OracleCommand)command).InitialLONGFetchSize = -1; 还有一个类似的属性：((Oracle.ManagedDataAccess.Client.OracleCommand)dbCommand).InitialLOBFetchSize https://docs.oracle.com/cd/E85694_01/ODPNT/CommandInitialLONGFetchSize.htmhttps://blogs.oracle.com/oraclemagazine/post/serving-winning-lobs
临时用的语句，能跑就行。SELECT T1.TABLE_NAME AS "表名", T3.COMMENTS AS "表注释", T1.COLUMN_NAME AS "列名", T2.COMMENTS AS "列注释", T1.NULLABLE AS "是否为空", T1.DATA_DEFAULT AS "默认值", T1.DATA_TYPE AS "字段类型", T1.DATA_LENGTH AS "字段长度", NVL2( T7.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否主键", NVL2( T8.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否外键" FROM ALL_TAB_COLUMNS T1 LEFT JOIN ALL_COL_COMMENTS T2 ON T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME LEFT JOIN ALL_TAB_COMMENTS T3 ON T1.OWNER = T3.OWNER AND T1.TABLE_NAME = T3.TABLE_NAME LEFT JOIN ( SELECT T5.OWNER, T5.TABLE_NAME, T5.COLUMN_NAME, T6.CONSTRAINT_TYPE FROM ALL_CONS_COLUMNS T5 INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER AND T5.TABLE_NAME = T6.TABLE_NAME AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME AND T6.CONSTRAINT_TYPE = 'P' ) T7 ON T1.OWNER = T7.OWNER AND T1.TABLE_NAME = T7.TABLE_NAME AND T1.COLUMN_NAME = T7.COLUMN_NAME LEFT JOIN ( SELECT T5.OWNER, T5.TABLE_NAME, T5.COLUMN_NAME, T6.CONSTRAINT_TYPE FROM ALL_CONS_COLUMNS T5 INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER AND T5.TABLE_NAME = T6.TABLE_NAME AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME AND T6.CONSTRAINT_TYPE = 'R' ) T8 ON T1.OWNER = T8.OWNER AND T1.TABLE_NAME = T8.TABLE_NAME AND T1.COLUMN_NAME = T8.COLUMN_NAME WHERE T1.OWNER = 'XXXXXXXX' --要查询的用户名 AND NOT EXISTS ( SELECT T4.OBJECT_NAME FROM ALL_OBJECTS T4 WHERE T4.OWNER = T1.OWNER AND T4.OBJECT_TYPE = 'TABLE' AND T4.TEMPORARY = 'Y' AND T4.OBJECT_NAME = T1.TABLE_NAME ) ORDER BY T1.TABLE_NAME, T1.COLUMN_ID;
oracle查询不走索引的一些情况（索引失效）
Oracle建立索引的目的是为了避免全表扫描，提高查询的效率。但是有些情况下，即使建立了索引，但是执行写出来的查询还是很慢，然后通过执行计划会发现是索引失效导致的（不走索引，走全表扫描）。所以需要了解一下有哪些些情况会导致索引失效，即查询不走索引的原因。在写SQL的层面上一些骚操作会导致索引失效没有写WHERE子句或查询条件没有建立索引既然没有WHERE子句，那么就是查询全部数据了，相当于全表扫描，当然不走索引了。而查询条件上没有建立索引的话，索引都没有还走个毛索引啊。WHERE子句上没有使用索引中的引导列要使用索引，则查询条件中必须包含索引中的引导列。比如一个复合索引包含A，B，C，D四列，则A为引导列（排在第一位置的列）。如果WHERE子句中所包含的列是BCD或者BD等情况，则只能使用非匹配索引扫描。-- 创建包含A，B，C，D四列的复合索引 CREATE INDEX INDEX_ABCD ON LETTERS(A, B, C, D); -- 下列语句不会使用复合索引 SELECT * FROM LETTERS WHERE B = 'b' AND C = 'c'; SELECT * FROM LETTERS WHERE B = 'b' AND D = 'd'; SELECT * FROM LETTERS WHERE C = 'c' AND D = 'd'; SELECT * FROM LETTERS WHERE B = 'b' AND C = 'c' AND D = 'd'; 另外，单独引用复合索引里排第一位置的索引列也会导致索引失效，复合索引必须复合使用才能生效。-- 单独使用复合索引中的引导列也不会触发复合索引 SELECT * FROM LETTERS WHERE A = 'a' WHERE子句中使用IS NULL或IS NOT NULL 使用判断空或非空的条件会导致该索引列失效。-- COMM列的索引会失效 SELECT * FROM EMP WHERE COMM IS NULL; WHERE子句中使用函数 如果没有使用基于函数的索引，那么WHERE子句中对存在索引的列使用函数时，会使优化器忽略这些索引。-- 在没有建立基于函数索引的字段上使用了函数，会导致索引失效 SELECT * FROM STUFF WHERE TRUNC(BIRTHDAY) = '2019-04-01' 非要这样的话可以通过在索引列上建立基于函数的索引来解决问题。 -- 给BIRTHDAY列创建TRUNC函数的函数索引 CREATE INDEX STUFF_BIRTHDAY_FBI_IDX ON STUFF(TRUNC(BIRTHDAY)); 这样的话函数索引就起作用了，也就能在索引列上使用函数了，而且这时候你想不用函数都不行。 但是对于MIN，MAX函数等，Oracle仍然会使用索引，因为Oracle对这类聚合分析函数做了相应的优化。对索引列进行运算导致索引失效和使用函数同样的，如果使用运算，比如加减乘除非等，也会导致索引失效。-- 对索引列进行了减法运算，导致不走索引 SELECT * FROM USERS WHERE AGE - 1 = 23 -- 正确的写法（去除运算） SELECT * FROM USERS WHERE AGE = 24 使用前导模糊查询前导模糊查询的写法是LIKE '%T'，即通配符【%】写在要匹配的字符前面，这样的情况下不走索引。-- 前导模糊查询不走索引 SELECT * FROM LOVERS WHERE NAME LIKE '%静'; WHERE子句中使用不等于条件不等于操作包括：<>!=NOT COL >= ?NOT COL <= ?为什么不等于条件会不走索引，我们可以用常规的思维去推断一下。首先【不等于】这个概念可以推断成选取结果集中的一个比较大的部分，比如我们要找出中国人口中不是姓杨的，要找出资深程序员中不会秃头的，都可以认为是要返回结果集中一个很大的部分。因为Oracle的CBO是闭源的，我们只能推断Oracle会认为既然要返回结果集中很大的一个部分，不如直接使用全表扫描而不考虑索引。而且Oracle是建议，对于这些限制条件可以使用OR代替，例如COL <> 0可以替换成COL > 0 OR COL < 0。WHERE子句中使用NOT IN或NOT EXISTS使用NOT IN或NOT EXISTS也会导致不走索引。事实上NOT IN和NOT EXISTS也可以看做是不等于条件，不走索引的原因和上面的不等于条件相同。另外有一些人说使用IN是不走索引的，这是不对的，IN是可以走索引的，只是可能效率会比EXISTS低。等于和范围索引不会被合并使用SELECT * FROM CARS WHERE COLOR = 'yellow' AND TYPE = 'B'在上面的查询中，COLOR和TYPE列上都创建了非唯一索引，在这种查询条件下Oracle并不会合并索引，而只会使用第一个索引，即只有COLOR列上索引会生效。比较不匹配类型的数据类型比如SERIAL_CODE是一个VARCHAR2类型的字段，在这个字段上建立了索引，但是下面的语句将会执行全表扫描而不走索引。SELECT * FROM MATERIALS WHERE SERIAL_CODE = 810646874;为什么呢？因为Oracle中有一个字段自动进行隐式类型转换的机制，会自动把类型不匹配的字段进行隐式类型转换，相当于：SELECT * FROM MATERIALS WHERE TO_NUMBER(SERIAL_CODE) = 810646874;可以看出，Oracle优化器自动给SERIAL_CODE加上了类型转换函数，这样就限制了索引的使用。所以正确的写法应该是条件和字段类型相匹配：SELECT * FROM MATERIALS WHERE SERIAL_CODE = '810646874';表中数据量的多少也会影响索引的使用查询的数量是大表的大部分，应该是30%以上如果查询的数量超过大表数量的30%，那就不走索引了。对小表查询举个极端的例子，表中只有一条数据，何必走索引呢。比如你看一本只有几页的书，难道你还会去看目录吗，给这本书建目录都是人才了，你还去找这本书有没有目录岂不是人才中的人才（你别去上班了，我建目录养你啊）。索引失效的解决办法下面这些解决办法是基于SQL写得没问题，而索引就是不生效的情况。选用合适的Oracle优化器Oracle的优化器有三种，一种是基于规则的（RULE），一种是基于成本的（COST），还有一种是选择性的（CHOOSE）。在缺省的情况下（未设置），Oracle默认采用CHOOSE优化器。为了避免那些不必要的全表扫描（FULL TABLE SCAN），你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或基于成本的优化器。重建索引和电脑出了问题的重启试试一样，索引出了问题也是可以重建试试的。ALTER INDEX 索引名 REBUILD强制索引强制索引是使用hint关键字。正常使用的索引突然失效的对应解决办法有些时候，同样的SQL，之前是能走索引的，突然有一天不走索引了，那么可能是：1.随着表的增长，WHERE条件出来的数据太多，大于15%，导致CBO计算出走索引扫描大于走全表扫描，就会使得索引失效。这种情况目前我也不知道该怎么办（好难啊）。2.统计信息失效。这种情况需要重新搜集统计信息。3.索引本身失效。这种情况就很玄乎了，只能重建索引试试。 来源：https://www.cnblogs.com/yanggb/p/10637595.html
ORACLE中的支持正则表达式的函数主要有下面四个：1，REGEXP_LIKE ：与LIKE的功能相似2，REGEXP_INSTR ：与INSTR的功能相似3，REGEXP_SUBSTR ：与SUBSTR的功能相似4，REGEXP_REPLACE ：与REPLACE的功能相似它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。POSIX 正则表达式由标准的元字符（metacharacters）所构成：'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 'n' 或 'r'。'.' 匹配除换行符之外的任何单字符。'?' 匹配前面的子表达式零次或一次。'+' 匹配前面的子表达式一次或多次。'*' 匹配前面的子表达式零次或多次。'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。'( )' 标记一个子表达式的开始和结束位置。'[]' 标记一个中括号表达式。'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。字符簇： [[:alpha:]] 任何字母。[[:digit:]] 任何数字。[[:alnum:]] 任何字母和数字。[[:space:]] 任何白字符。[[:upper:]] 任何大写字母。[[:lower:]] 任何小写字母。[[:punct:]] 任何标点符号。[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。各种操作符的运算优先级转义符(), (?:), (?=), [] 圆括号和方括号*, +, ?, {n}, {n,}, {n,m} 限定符^, $, anymetacharacter 位置和顺序| */--创建表 create table fzq ( id varchar(4), value varchar(10) ); --数据插入 insert into fzq values ('1','1234560'); insert into fzq values ('2','1234560'); insert into fzq values ('3','1b3b560'); insert into fzq values ('4','abc'); insert into fzq values ('5','abcde'); insert into fzq values ('6','ADREasx'); insert into fzq values ('7','123 45'); insert into fzq values ('8','adc de'); insert into fzq values ('9','adc,.de'); insert into fzq values ('10','1B'); insert into fzq values ('10','abcbvbnb'); insert into fzq values ('11','11114560'); insert into fzq values ('11','11124560'); --regexp_like --查询value中以1开头60结束的记录并且长度是7位 select * from fzq where value like '1____60'; select * from fzq where regexp_like(value,'1....60'); --查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。 --使用like就不是很好实现了。 select * from fzq where regexp_like(value,'1[0-9]{4}60'); -- 也可以这样实现，使用字符集。 select * from fzq where regexp_like(value,'1[[:digit:]]{4}60'); -- 查询value中不是纯数字的记录 select * from fzq where not regexp_like(value,'^[[:digit:]]+$'); -- 查询value中不包含任何数字的记录。 select * from fzq where regexp_like(value,'^[^[:digit:]]+$'); --查询以12或者1b开头的记录.不区分大小写。 select * from fzq where regexp_like(value,'^1[2b]','i'); --查询以12或者1b开头的记录.区分大小写。 select * from fzq where regexp_like(value,'^1[2B]'); -- 查询数据中包含空白的记录。 select * from fzq where regexp_like(value,'[[:space:]]'); --查询所有包含小写字母或者数字的记录。 select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$'); --查询任何包含标点符号的记录。 select * from fzq where regexp_like(value,'[[:punct:]]'); 例子：判断姓名是否为空，少于两个字符，包含数字和字母create or replace FUNCTION CheckName(NameStr in VARCHAR2) RETURN integer As BEGIN --符合返回1，不符合返回0 if(NameStr is null or length(NameStr)<2) then return 0; else if(NameStr like '%未取名%') then RETURN 0; end if; if regexp_like(NameStr,'^([a-z]+|[0-9]+|[A-Z]+)$') then return 0; end if; return 1; end if; END CheckName; 来源：https://www.cnblogs.com/einyboy/archive/2012/08/01/2617606.html经测试，有略微的小错误，比如--查询value中以1开头60结束的记录并且长度是7位select * from fzq where regexp_like(value,'1....60');需要加上匹配开头、结尾的字符^和$，其他部分sql也存在类似问题
Oracle 32位客户端 不安装使用 pl/sql System.Data.OracleClient
System.Data.OracleClient、pl/sql 要连接Oracle数据库需要Oracle客户端配合，不必安装Oracle客户端软件，只需要32位Oracle客户端的zip包就可以。第一步：准备好32位的Oracle客户端软件 instantclient_11_2压缩包，解压出来，比如放到D盘根目录 D:\instantclient_11_2。第二步：在 D:\instantclient_11_2\NETWORK\ADMIN 文件夹下添加 tnsnames.ora 文件。# tnsnames.ora Network Configuration File: D:\instantclient_11_2\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) 第三步：在系统环境变量 Path 里添加客户端目录：D:\instantclient_11_2 （win10直接添加一行，win7需要在Path最后添加 ;D:\instantclient_11_2，注意加分号），到这里System.Data.OracleClient 就可以连接Oracle数据库了。第四步：由于国内常用的Oracle是中文编码的，需要添加系统环境变量名：NLS_LANG，变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK 。其实到第三步 pl/sql 就可以连Oracle数据库了，但是ZHS16GBK编码的数据库中文显示会是 ？ 号。注意，这里说的 pl/sql 是 32位的，如果是 64位的 pl/sql，对应64位Oracle客户端压缩包，操作步骤一样。参考：https://www.cnblogs.com/cang12138/p/6233539.html
对于一些自表关联的表，想要查出一条记录和它的所有下属子级，可以用下面的语句：sqlserver: with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) select * from my1; 查询出来删除可以这样写： with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) delete from aaa where id in (select id from my1); oracle:SELECT id FROM rightTree t START WITH t.id = #{ID} CONNECT BY PRIOR t.id = t.parent_id 查询出来删除可以这样写：delete from rightTree where id in( SELECT id FROM rightTree t START WITH t.id = #{ID} CONNECT BY PRIOR t.id = t.parent_id ) 参考：https://bbs.csdn.net/topics/350041264https://blog.csdn.net/qq_25391785/article/details/79136853