Plsql

看完这章你会学到以下内容 转换函数有那几种，通常用得最多的是那个？为什么要用转换函数？各自转换的关系，实用条件是什么？数据类型有三类：数字，字符，日期对应就用转换它们三种类型的函数 ：转换函数！ To_Date 转日期Date 强制转换，外面肯定要有'-'能力转换，数字只会呈现谬误，例如： SELECT DATE'2019-01-01' FROM DUAL; --- 强制转换为日期格局 SELECT TO_DATE('2019-01-01','YYYY/MM/DD') FROM DUAL; SELECT TO_DATE('2019-01-01','YYYY-MM-DD') FROM DUAL; SELECT TO_DATE('2019-01-01','YYYYMMDD') FROM DUAL; ---- 谬误, 日期转换只有以上两种模式 留神：字符或数字中的年月日必须是日期范畴内的值 SELECT TO_DATE('2019-13-08','YYYY-MM-DD'), -- 月份不对 TO_DATE(20190732,'YYYY-MM-DD') -- 天数不对 FROM DUAL; -- 字符日期中有-或/转化为日期类型必须加-或/ SELECT TO_DATE('2019-07-08','YYYYMMDD'), -- 谬误 TO_DATE(20190708,'YYYY-MM-DD') FROM DUAL; --- 胜利 To_Char() 转换为字符 SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL; SELECT TO_CHAR(DATE'2019-08-30','YYYY'), -- '2019' TO_CHAR(DATE'2019-08-30','YYYYMM'), -- '201908' TO_CHAR(DATE'2019-08-30','WW'), -- '35' 第35周 TO_CHAR(DATE'2019-08-30','IW'), -- '35' 第35周（天然周） TO_CHAR(DATE'2019-08-30','Q'), -- '3' 第3季度 TO_CHAR(DATE'2019-08-30','MM'), -- '08' TO_CHAR(DATE'2019-08-30','DD'), -- '30' TO_CHAR(DATE'2019-08-30','D') -- '6' 当周的第几天（星期天为第一天） FROM DUAL; ...

背景某我的项目接口采纳plsql开发，接口返回用户明码，但要求明码不能是明文返回，因为程序外部须要用到明文明码，所以只能在plsql中对明码进行对称加密，在程序外部进行解密，程序采纳java开发。 实现dbms_crypto是oracle自带的加密包，蕴含多种加密解密办法，非dba用户须要受权能力进行应用 grant execute on dbms_crypto to xxx;上面是一个通过DES算法加密的function function encrypt_password(p_password in varchar2) return varchar2 is v_key varchar2(32) := 'TucM2fYDaxnd1UeRL7HVvyshXvXaMKO9'; v_encrypted_raw RAW(256); begin v_encrypted_raw := dbms_crypto.Encrypt(src => UTL_RAW.CAST_TO_RAW(p_password), typ => DBMS_CRYPTO.DES_CBC_PKCS5, iv=>UTL_RAW.CAST_TO_RAW('12345678'), key => UTL_RAW.CAST_TO_RAW(v_key)); return utl_raw.cast_to_varchar2(utl_encode.base64_encode(v_encrypted_raw)); end;v_key：密钥typ：加密算法，这里采纳DES加密算法，能够应用密钥进行加密，应用雷同的密钥进行解密，DES_CBC_PKCS5分为三段，DES示意加密算法是DES，CBC示意应用CBC模式进行加密，PKCS5示意分组的填充形式，大部分状况下，明文并非刚好64位的倍数。对于最初一个分组，如果长度小于64位，则须要用数据填充至64位。PKCS5Padding是罕用的填充形式，如果没有指定，默认的形式就是它。iv：如果是采纳CBC模式进行加密，须要指定始化向量IV这里将返回值进行了base64的编码，因为加密进去的数据可能是二进制数据，为了便于传输进行了base64编码，以下是测试的后果 输出：zhengjianfeng输入：N8pbaNezTEJO34jIgJhUFg==java解密import javax.crypto.Cipher;import javax.crypto.SecretKeyFactory;import javax.crypto.spec.DESKeySpec;import javax.crypto.spec.IvParameterSpec;import java.security.Key;import java.util.Base64;/** * @Description: * @author: jianfeng.zheng * @since: 2021/2/20 12:12 上午 * @history: 1.2021/2/20 created by jianfeng.zheng */public class DesDecryptDemo { private final static String IV_PARAMETER = "12345678"; private static final String ALGORITHM = "DES"; private static final String CIPHER_ALGORITHM = "DES/CBC/PKCS5Padding"; private static final String CHARSET = "utf-8"; private static final String KEY = "TucM2fYDaxnd1UeRL7HVvyshXvXaMKO9"; public static void main(String[] args) { String encryptData = "N8pbaNezTEJO34jIgJhUFg=="; System.out.println("密文:" + encryptData); String plainText = decrypt(KEY, encryptData); System.out.println("明文:" + plainText); } public static String decrypt(String key, String data) { if (key == null || key.length() < 8) { throw new RuntimeException("加密失败，key不能小于8位"); } if (data == null) { return null; } try { DESKeySpec dks = new DESKeySpec(key.getBytes(CHARSET)); SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(ALGORITHM); Key secretKey = keyFactory.generateSecret(dks); Cipher cipher = Cipher.getInstance(CIPHER_ALGORITHM); //设置始化向量 IvParameterSpec iv = new IvParameterSpec(IV_PARAMETER.getBytes(CHARSET)); cipher.init(Cipher.DECRYPT_MODE, secretKey, iv); return new String(cipher.doFinal(Base64.getDecoder().decode(data.getBytes(CHARSET))), CHARSET); } catch (Exception e) { e.printStackTrace(); return data; } }}运行后果密文:N8pbaNezTEJO34jIgJhUFg==明文:zhengjianfeng能够看到是能够拿到明文的 ...

如果没有为PL/SQL Developer配置OCI文件，那就配置。 如果配置没问题，但PL/SQL Developer加载不了（个别在新环境遇到），那就不是oci.dll文件有问题，而是短少 Microsoft Visual C++ Redistributable Packages所蕴含的动静库。 测验一下，把SQL*PLUS的工具包同Oracle Instant Client一并下载，运行sqlplus.exe，如果运行不了，弹出MSVCR120.dll未发现等谬误，装置一下相应版本的Microsoft Visual C++ Redistributable Packages。 参考地址： https://www.oracle.com/hk/dat... https://answers.microsoft.com... https://www.drivereasy.com/kn...

如果没有为PL/SQL Developer配置OCI文件，那就配置。 如果配置没问题，但PL/SQL Developer加载不了（个别在新环境遇到），那就不是oci.dll文件有问题，而是短少 Microsoft Visual C++ Redistributable Packages所蕴含的动静库。 测验一下，把SQL*PLUS的工具包同Oracle Instant Client一并下载，运行sqlplus.exe，如果运行不了，弹出MSVCR120.dll未发现等谬误，装置一下相应版本的Microsoft Visual C++ Redistributable Packages。 参考地址： https://www.oracle.com/hk/dat... https://answers.microsoft.com... https://www.drivereasy.com/kn...

导出表构造 1.点击工具 导出用户对象 2.抉择要导出的表 3.抉择要导出sql寄存的门路 导入表构造 1.导入之前检查表名字是否是之前导出表的名字删掉table前的 BAMS. 2.抉择工具 导入表 上传完查看下是否胜利!

它可实现对列值的拼接。上面咱们来看看其具体用法。用法： 对其作用，官网文档的解释如下： For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. 即在每个分组内，LISTAGG依据order by子句对列植进行排序，将排序后的后果拼接起来。 measure_expr：能够是任何基于列的表达式。delimiter：分隔符，默认为NULorder_by_clause：order by子句决定了列值被拼接的程序。 通过该用法，能够看出LISTAGG函数不仅可作为一个一般函数应用，也可作为剖析函数。 order_by_clause和query_partition_clause的用法如下： 上面对该函数进行举例说明：一般函数，对工资进行排序，用逗号进行拼接。 select listagg(ename,',')within group(order by sal)name from emp;后果： NAME----------------------------------------------------------------------------------------------------SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING+groupby分组函数： select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno;后果： DEPTNO NAME 10 MILLER,CLARK,KING 20 SMITH,ADAMS,JONES,FORD,SCOTT 30 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE+over(partition by )剖析函数： select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from emp;后果： ...

看完这章你会学习到以下内容：它们各自的用法它们的不同之处NVL函数NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身 例子：如果EMP表上COMM奖金为NULL的，全部替换为0. SELECT NVL(COMM,0) AS NEW_COMM, COMM FROM EMP; NVL2函数NVL函数有一定局限，所以就有了NVL2函数。NVL2函数的格式如下：NVL2(expr1,expr2, expr3) NVL2函数:Oracle/PLSQL中的一个函数，NVL2(E1, E2, E3)功能：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。 例子：如果EMP表上COMM奖金为NULL全部替换为0，否则全部设置1000. SELECT NVL2(comm,1000,0) AS NEW_COMM,COMM FROM EMP; COALESCE函数从左往右数，遇到第一个非null值，则返回该非null值。换言之，之后第二个非空的任何数值都不会返回。 例子：创建一张视图，然后返回所有的非空值。第一步：创建视图如下： CREATE OR REPLACE VIEW V_VIEW AS SELECT NULL AS C1, NULL AS C2, '1' AS C3, NULL AS C4 FROM DUALUNION ALLSELECT NULL AS C1,'2' AS C2,NULL AS C3,'4'AS C4 FROM DUAL;SELECT * FROM V_VIEW; 第二步：使用Coalesce函数： SELECT COALESCE(C1,C2,C3,C4) AS RE FROM V_VIEW; ...

看完这章你会学习到以下内容：它们的共同点它们的不同点 相同点：它们都可以删除数据，清理那些无关紧要，与业务无关的数据。 不同点：1.语句方面delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.2.删除内容方面truncate和 delete只删除数据不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);delete/drop可以删除视图，索引，触发器等，truncate只针对表。3.高水位HWMdelete语句不影响表所占用的区(Extent), 高水线保持原位置不动。drop语句将表所占用的空间全部释放。truncate 语句缺省情况下将空间释放到 minextents个extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始).4.速度方面一般来说: drop> truncate > delete  

1.如果匹配上，将DictB的Desc更新成DictA中对应的字段Desc 首先创建两张表格： CREATE TABLE DictA(id1 number, desc1 varchar2(10));CREATE TABLE DictB(id1 number, desc1 varchar2(10));INSERT INTO DictA VALUES (1001, '基金');INSERT INTO DictA VALUES (2001, '保险');INSERT INTO DictA VALUES (3001, '证券');INSERT INTO DictA VALUES (3002, '信托');INSERT INTO DictB VALUES (1002, '证券');INSERT INTO DictB VALUES (3001, '银行');COMMIT;解题思路：如果ID匹配得到，则修改(如果ID匹配不到，则插入)，类似于增量同步；又可以用ID和Desc合并判断。 MERGE INTO DICTB BUSING (SELECT * FROM DICTA) AON (B.ID1 = A.ID1)WHEN MATCHED THEN UPDATE SET B.DESC1 = A.DESC1; 注意事项： --ORA.00903 表名无效 ，源表要加() 括号--ORA.00969 缺失ON关键字，ON条件也要加上()括号2.显示如右图所示，及格分数为60; ...

看完这章你会学习到以下 三种函数，To_char，Trunc, 日期函数等之间的差异和注意事项1.Trunc 函数，按照第二参数相应截取日期 SELECT TRUNC(ADD_MONTHS(SYSDATE,3),'Q')-1 AS 本季度最后一天 FROM DUAL; -- 本季度的最后一天SELECT TRUNC(SYSDATE,'q') AS 本季度第一天 FROM DUAL; -- 本季度的第一天SELECT TRUNC(SYSDATE,'Y') AS 本年的第一天 FROM DUAL; --- 本年的第一天SELECT TRUNC(ADD_MONTHS(SYSDATE,12),'Y')-1 AS 半年最后一天 FROM DUAL; --- 本年最后一天SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --- 本月的第一天SELECT TRUNC(ADD_MONTHS(SYSDATE,1),'MM')-1 FROM DUAL; --- 本月的最后一天 SELECT TRUNC(SYSDATE,'D') FROM DUAL; --- 西方以周日为起点 周日SELECT TRUNC(SYSDATE,'D')+1 FROM DUAL; --- 中国以周一为起点 周一SELECT TRUNC(SYSDATE,'D')+7 FROM DUAL; --- 本周的周末 注意事项：日期函数截取之后，还是日期函数，不会更改格式。 下面结果中的下三角箭头就代表可以返回日期格式。 2. To_Char 函数，将所有格式都转换为字符串格式。 ...

看完这篇文章你会学习到以下内容： 在创建或者写复杂逻辑时，做好备份两种方法介绍： 1）INSERT INTO Table SELECT * FROM TABLE 2）CREATE TABLE AS .... .... Select * from TABLE 两者区别： INSERT INTO 首先要建立一张表 ，然后才可以插入。 创建表格，根据不同需求更改Select后面的语句 1）Select * from; 2）Select 字段 from; 3) Select * from table where 1=2; CREATE TABLE EMP_NEWGAN AS SELECT * FROM EMP; -- 全部字段一样，表格完全复制过来CREATE TABLE EMP_0623 AS SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP; -- 字段不一样， 表格完全复制过来CREATE TABLE EMP_10623 AS SELECT * FROM EMP WHERE 1=2; -- 字段字段一样，但复制表结构SELECT * FROM EMP_NEWGAN;SELECT * FROM EMP_0623;SELECT * FROM EMP_10623;INSERT INTO TABLE 两种 1） INSERT INTO 带条件，所有字段 2） INSERT INTO 限定字段 ...

看完这篇文章你会学习到以下内容： DBMS_JOB 的语法。如何创建，如何查询，如何移除？常见的间隔时间，如何书写？在写代码时，可能会出现的问题有哪些？DBMS包下的JOB对象，语法如下： DECLARE jobno number:=1; -- 随便定义一个初始数字BEGINdbms_job.submit(:jobno,—-job号 ， 'your_procedure;',—-执行的存储过程, ';'不能省略 next_date, —-下次执行时间 'interval' —-每次间隔时间，interval以天为单位 ); END;以下简单举个例子： 1.建立一场临时表 2.写一个存储过程，将数据写入临时表 3.建立一个定时JOB，并设置相隔1分钟时间 4.查看结果 5.停止定时JOB 6.删除定时JOB CREATE OR REPLACE PROCEDURE SP_EMP_GAN_INSERT -- 创建一个存储过程，插入数据到临时表里ISBEGIN INSERT INTO /*+ append */ EMP_GAN(EMPNO,ENAME,HIREDATE) VALUES(10,'GANZEXIN',SYSDATE);END;CREATE TABLE EMP_GAN AS SELECT * FROM EMP WHERE 1 =2; -- 创建临时表SELECT * FROM EMP_GAN; -- 查看临时表DECLARE jobno NUMBER:=1; -- 定义编号并赋值，作为第一个定时执行BEGIN DBMS_JOB.SUBMIT(:JOBNO,'SP_EMP_GAN_INSERT;', SYSDATE, 'sysdate+1/24/60'); -- 每隔一分钟COMMIT; -- 提交END;SELECT * FROM USER_JOBS; --- 查看当前用户所有的JOBBEGIN DBMS_JOB.BROKEN(49, TRUE, SYSDATE); -- 用上面的语句得知这个JOB的编号是49，当日执行。 COMMIT;END;SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM USER_JOBS; -- 如果NEXT_SEC 变成00；00：00 则表示已经停止任务；BEGINDBMS_JOB.REMOVE(49); --- 移除定时JOBEND;**常见的间隔时间 +1表示加一日，代表是明日的0时0分。 ...

看完这章你将会学习到以下： RowNum & MinusRow_Num_ber,Rank,Densk_rank over(partition by ..... .... )一般在SQl server,我们可以用排序和Limit取到相应范围的值，但在PL/SQL里面没有这些函数。我们可以用RowNum 或者用Row_number()over(Partition by )或者用补集。 使用场景： 查询EMP表第4行到第8行所有员工的信息； 注意： Minus 做补集的时候，两张或者多张表的字段内容和字段类型要口径一致，不会没有任何结果。 SELECT * FROM EMP;---- ROWNUM 只能返回小于或者小于等于的区域SELECT E.* ,ROWNUM AS RN FROM EMP E WHERE ROWNUM <9MINUSSELECT E.*,ROWNUM AS RN FROM EMP E WHERE ROWNUM <4 注意ROWNUM 是查询数据时生成序列，在查询条件中，如果查询条件中ROWNUM大于某一正整数，则不返还任何结果。 方法二：排序函数ROW_NUMBER() 注意不一定要搭配用PARTITION BY 按字段分组的，但排序函数RANK.,DENSERANK,ROW_NUMBER 一定要搭配Order by SELECT * FROM (SELECT E.*,ROW_NUMBER()OVER(ORDER BY EMPNO) AS RN FROM EMP E ) WHERE RN BETWEEN 4 AND 8;

看完这章你会学习到以下内容： 一般Insert语句使用多表Insert （无条件）语句使用多表Insert（有条件）语句使用一般语法使用： insert into 【User.table】[@db_link](column1, column2, column3, ... ...) Values(express1, express2, ... ..) 更经常用法： 无条件的Insert all 本语句指无条件插入所有数据。 -- 创建两张空表CREATE TABLE EMP_NEWSALHISTORY AS SELECT * FROM EMP WHERE 1= 2;CREATE TABLE EMP_NEWMGRHISTORY AS SELECT * FROM EMP WHERE 1= 2;INSERT ALLINTO EMP_NEWSALHISTORY(EMPNO,HIREDATE,SAL) VALUES(EMPNO,HIREDATE,SAL) -- 插入的字段和Values的字段 最好一一对应INTO EMP_NEWMGRHISTORY(EMPNO,HIREDATE,MGR) VALUES(EMPNO,HIREDATE,MGR) -- 插入的字段和Values的字段 最好一一对应SELECT * FROM EMP WHERE SAL> 1500; --在源表上可以进行条件筛选再插入数据据COMMIT; 记得提交SELECT * FROM EMP_NEWSALHISTORY;SELECT * FROM EMP_NEWMGRHISTORY; ...

看完这章你会学习到以下内容： 1. 三种关联分别是什么？ 2. 什么时候那个适合用哪个？（总结） 一共有三种关联机制： Nest Loop 嵌套循环 （大小表） Sort Merge 排序合并 （添加索引+两张大表） Hash Join 哈希 （内联） 嵌套循环 对上图进行简单的解释，在进行嵌套循环的时候，通常情况是（大表+小表）的形式，并且会以小表作为内表，因为小表可以常驻内存，这样当进行磁盘IO的时候，只有大表会产生开销；反之，如果因为大表过大，则大表不能常驻内存，内外表的数据均存在与磁盘中，这样，进行磁盘IO开销是非常“可观”的 解释了，谁做内外表的问题后，看下嵌套关联的操作过程： 1.取得要操作的表空间 2.对表空间的记录进行排序 3.通过嵌套循环进行赛选（如果是双表，可能是两个for嵌套） 4.获得需求的记录 归并关联 对与归并关联，有着很好的时间复杂度（如果两个表中的元素已经排序，如果未排序，时间开销可能为XlogX + YlogY + X + Y）。 那么，如何对排序好的元素进行关联呢： 1.首先每张表，会有一个指向头元素的指针 2.两个指针，进行比较，如果相同，则提出该元素 3.比较后续元素，谁小谁先向下移动 4.如此，一直到各自表的结尾 Hash关联（散列关联） 散列关联，则是对表进行散列后（不可超过内存的大小），另外一张表进行匹配；通常来说被散列的依旧是小表，匹配的过程需要匹配全部元素。 以上三种，根据以上三种关联方式，可以大致了解到一定的sql语句的原理以及调优机制，即为什么有的时候会加索引、或者不加、或者改变内外表的关联。 总结： 版权声明：本文为CSDN博主「Anger_Coder」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：Study-Hard-数据库连接及实现内部原理（下）_Anger_Coder的专栏-CSDN博客

看完这章你会学习到以下内容： 为什要用这个Hints强制优化？使用前的注意事项？查询核心数和进程？强制优化原因： 1.对于一个大的任务，一般的做法是利用一个进程，串行的执行。如果系统资源足够，可以采用parallel技术，核心思想：把一个大的任务分成若干个小的任务。具体含义：同时启用n个进程/线程，并行的处理这些小的任务，这些并发的进程称为并行执行服务器(parallel executeion server)，这些并发进程由一个称为并发协调进程的进程来管理。 启用Parallel前注意事项：只有在需要处理一个很大的任务，如需要几十分钟，几个小时的作业中，并且要有足够的系统资源的情况下(这些资源包括cpu，内存，io)。您才应该考虑使用parallel。否则，在一个多并发用户下，系统本身资源负担已经很大的情况下，启用parallel，将会导致某一个会话试图占用了所有的资源，其他会话不得不去等待，从而导致系统系能反而下降的情况。 通常语法： Parallel(表名或者别名，并行数->偶数) select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal>300;另外：1.每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/+ /但提示内容可以有多个，可以用逗号分开，空格也可以。例如：既包含走索引，也包含用多线程。 /+INDEX_ASC(TABLE INDEX_NAME) parallel(emp,4)*/2.如果表有别名，则写别名，否则不会走Hints优化。 Select /*+ parallel(e,4)*/ --- 会走Hints优化; * from emp e where deptno=200 and sal>300; Select /*+ parallel(emp,4)*/ --- 不会走Hints优化; * from emp e where deptno=200 and sal>300;Q:如何查询本机的核心数？目的在于知道本机的核心数上限，可分配的范围。打开命令窗口Command Window,输入如下命令： SQL> show parameter CPU;NAME TYPE VALUE------------------------------------ ----------- ------------------------------cpu_count integer 8parallel_threads_per_cpu integer 2resource_manager_cpu_allocation integer 8SQL> 以下有三个参数返回： cpu_count ： 表示cpu数parallel_threads_per_cpu :表示每个cpu允许的并行进程数resource_manager_cpu_allocation :该参数决定 Resource Manager 可以利用的CPU数，该参数已弃用，仅作向后兼容。参考资料： ...