Oracle学习————【PL/SQL】

1 使用PL/SQL书写一个Hello World程序

1.1 什么是PL/SQL？

1.2 PL/SQL的语法

1.2.1 If语句

语法：

使用实例：

1.2.2 循环

语法：（有3种形式）

1.2.3 光标

在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。

下面是光标使用实例：（实现查询遍历表中员工的姓名和薪水并打印）

使用实例2：（给对应员工涨工资，总裁涨1000 经理涨800 其他涨400）

使用实例3：（带参数的光标使用，查询某个部门的员工姓名）

1.2.4 例外（异常）

异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

• Oracle中例外有两种：一种是系统定义的例外，另一种是自定义的例外。

1.2.4.1 系统定义异常

• timeout_on_resource发生的情景一般是在使用分布式数据库时会遇到。

下面展示一下如何定义使用系统异常。

1.2.4.2 自定义异常

在pl/sql中我们把自定义例外当成变量来处理

下面通过实例说明自定义异常如何使用

1.3 常量和变量的定义

1.3.1 引用型变量的使用举例

1.3.2 记录型变量的使用举例

1.3.3 常量的定义

常量声明：<变量名> CONSTANT类型 := 初始值
示例：pi constant number(5,3) := 3.14;

1.4 存储过程，函数和触发器

什么是存储过程和存储函数：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

那么存储过程和存储函数有什么区别呢？存储函数能够通过return返回结果，存储过程则不能。

Java中不能直接调用plsql，但能够调用存储过程和存储函数。

1.4.1 创建存储过程

• 用CREATE PROCEDURE命令建立存储过程。

• 语法：
  
  上图中AS也可以用IS替换。

1.4.1.1 使用存储过程创建一个helloworld程序

下面是调用存储过程：（有两种方式）

1.4.1.2 在plsql dev中创建存储过程

1.4.1.3 创建带参数的存储过程

1.4.2 创建存储函数

1.4.2.1 使用存储函数实例

2.填写函数名称

3.书写函数，并F8执行

F8执行后将会在Functions栏中生成。那么调用某个函数的方法就是选中该函数右键选择Test

1.4.3 过程和函数中的in和out

下面是存储过程中使用out参数的实例

1.4.3.1 什么时候用存储过程/存储函数？

1.4.3.2 在out参数中使用游标

问题的引出：我们知道，在存储过程中可以使用out类型参数表示返回出来的数据类型，但有时我们会存在返回多条数据，就会声明多次out类型参数，那么当数量变多时，我们在声明out类型参数就会变得很麻烦，我们需要将多条out参数以一个集合的形式声明出来。

例如这种需求：1.查询某个员工的所有信息 —> 需要些out参数太多 2.查询某个部门中的所有员工信息 —> 需要返回的是个集合。

像以上举例的这两种需求就不好再单单以out参数的形式声明返回。所以就需要在out参数中使用游标这种集合形式声明返回。

1.4.3.2.1 包头、包体

当我们想要在out参数中使用游标，我们可以使用包结构进行编写。

• 声明包（包头）结构，包头只负责声明不负责实现。包体来负责实现表头中所声明的所有存储过程和存储函数。
  
• 创建包体
  
  如何在plsql dev中创建包头或包体呢？如下
  

1.4.4 触发器

什么是触发器？

1.4.4.1 如何创建并使用触发器？

语法：

需求：实现每当成功插入新员工后，自动打印“成功插入新员工”

1.4.4.2 触发器可用于

• 数据确认
• 实施复杂的安全性检查
• 做审计（日志），跟踪表上所做的数据操作等
• 数据的备份和同步

1.4.4.3 触发器的类型

存儲過程寫法學習

1. 根據id刪除人員數據

procedure DELETE_SURVEY_EMP_SP(
              V_ID IN VARCHAR2,
              P_RES OUT INTEGER) as
    begin
      P_RES := 0;
      delete from CDT.R_SURVEY_EMP_LIST_T  ---這裡是物理刪除
       where ID = V_ID;
      
      commit;
      
      select nvl(count(1),0) into P_RES -- 1，代表刪除成功
        from dual			-- 其實這裡使用nvl(count(1),0)只是爲了滿足下面not exists (select 1 from CDT.R_SURVEY_EMP_LIST_T where ID = V_ID);這個條件后返回1而已，其實單獨執行select count(1) from dual也會返回1
       where not exists
       (select 1 
          from CDT.R_SURVEY_EMP_LIST_T
         where ID = V_ID);
         
      exception
        when others then
          P_RES := 0; -- 0，代表刪除失敗
          rollback;
    end;

2. 得到部門及其子部門，這是層次查詢

-- 得到部門及其子部門，這是層次查詢
SELECT *
  FROM dept_info_t
 WHERE is_invalid = '0' 
CONNECT BY PRIOR id = parent_dept_id
 START WITH 
 id IN (63324);
-- 得到部門及其父部門
SELECT ID,
       dept_name,
       dept_level_id,
       bu_code_id
  FROM dept_info_t
 WHERE is_invalid = '0'
CONNECT BY PRIOR parent_dept_id = ID
 START WITH 
 ID = TO_NUMBER(31133);

4. 怎样才产生区间数字的一个列表？

---怎样才产生区间数字的一个列表？比如说，这个列表的区间设置为90~95，就要产生一个90,91,92,93,94,95这个6条记录，怎么才能做到
with t as
(select rownum rn from dual connect by rownum<=100 )
select * from t where rn between 90 and 95

5. 怎樣以select形式展示出幾個常量結果集

---怎樣以select形式展示出幾個常量結果集	   下面是展示一列3個數據：Q0011,Q0012,Q0099
select 'Q0011' AS LEAVE_TYPE from dual
union all
select 'Q0012' from dual
union all
select 'Q0099' from dual

6. oracle 表中有很多相同的记录,怎么只取满足条件的第一条?

----oracle 表中有很多相同的记录,怎么只取满足条件的第一条?
select * from dept where rownum =1
---就在条件里面加一个伪列就行了。

7. WITH 表名 AS可以接多個設置表名

例如:
WITH 表1 AS (SELECt * FROM table1),
	表2 AS (SELECT * FROM table2),
	....
	表n AS (SELECT * FROM tablen)

8. 抓取時間區間數據

--------在一張上千萬條數據的表中，根據某個時間類型的字段去查詢某個時間段內的數據。
--------（這是個坑，舉個例子：現有一張表t1，裡頭有個work_time字段，是date類型，我現在想根據這個時間類型字段去查找某段時間（這裡舉個時間段2019/01/01到2019/10/01）的數據）
------在寫這個SQL的時候一開始我是這麼寫的：SELECT * FROM t1 WHERE work_time BETWEEN to_date('2019/01/01','yyyy/mm/dd') AND to_date('2019/10/01','yyyy/mm/dd');
-----這麼寫其實在表數據量不算巨大的時候執行是不會感覺太慢，但當數據量達到千萬基本的時候（正式庫裡這張t1表的數據量是8千萬條...），再去執行這樣的一條語句時結果將會在一分鐘後出。
-----那麼我的優化方式是：SELECT * FROM t1 WHERE to_char(work_time,'yyyy/mm/dd') BETWEEN '2019/01/01' AND '2019/10/01';這樣的寫法瞬間快到零點幾秒。。。