基于Oracle的SQL编程

lambda.

已于 2022-04-10 22:04:50 修改

阅读量2.1k

点赞数

分类专栏： Oracle 文章标签： oracle

于 2022-03-18 21:42:49 首次发布

本文链接：https://blog.csdn.net/m0_46357847/article/details/123584169

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

基于Oracle的SQL编程

你知道吗？SQL从某种程度上说也是一门编程语言！SQL也有相应的标识符、变量流程控制语句、函数、存储过程等具备了编程语言的特性。而新的SQL规范甚至可以使用Java类并引入了包等一些概念增加了编程语言的特点，易于我们编写一些流程化的程序，还被人们称为第四代编程语言。

1、SQL编程基础

PL/SQL(Procedural Language/SQL)是一种过程化语言，它与C、C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中，使PL/SQL成为一个功能强大的事务处理语言。在甲骨文数据库管理方面，PL/SQL是对结构化查询语言（SQL）的过程语言扩展。PL/SQL的目的是联合数据库语言和过程语言。PL/SQL的基本单位叫做块，由三个部分组成：一个声明部分，一个执行部分和异常处理部分。

1.1、变量的声明与赋值

前面我们也说了在SQL结构化程序设计中，程序块是PL/SQL最基本的结构，一个程序块可以分为三个部分：变量声明部分、程序执行部分和异常处理部分。声明部分由DECLARE关键字开始，包含变量和常量的数据类型初始化。由关键字BEGIN声明程序执行部分，所有可执行语句都存放在BEGIN声明部分。而异常处理部分由EXCEPTION关键字开始，处理异常和错误，这一部分是可选的。最终由关键字END结束。例如：

/** 程序块结构 / 固定结构 **/
DECLARE
	-- 变量与常量初始化
BEGIN 
	-- 执行部分
EXCEPTION
	-- 异常处理部分 / 可省略
END

/** 输出 hello world 示例 **/
DECLARE
	message VARCHAR2(50) := 'hello world';
BEGIN
	DBMS_OUTPUT.PUT_LINE(message);
END;

在这里插入图片描述
这个hello world案例也包含了变量的声明与赋值(使用:=进行赋值)，赋值的时候也可以像其他编程语言一样，既可以同时声明与赋值，也可以先声明后赋值。

DECLARE
	-- 声明变量基本方式
	v_number NUMBER; -- 如果变量与SQL关键字相同一般变量前面加上 v_ (variable)
	message VARCHAR2(50) := 'hello world'; -- 字符型变量必须指定长度
	V_PI CONSTANT NUMBER := 3.14; -- 定义一个常量使用 CONSTANT 修饰
	v_date TIMESTAMP := SYSDATE;
	
BEGIN
	v_number := 520; -- 变量赋值
	DBMS_OUTPUT.PUT_LINE(v_number); -- 520
	DBMS_OUTPUT.PUT_LINE(V_PI); -- 3.14
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date, 'yyyy-mm-dd hh24:mi:ss')); -- 2022-03-19 10:21:18
END;

除了以上变量声明方式外，还可以使用表里面的字段作为程序中的一个变量，例如

DECLARE
	fileName NVARCHAR2(30);
BEGIN
	SELECT D.FILE_NAME INTO fileName FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	DBMS_OUTPUT.PUT_LINE('fileName = ' || fileName); -- 输出 DL_PRE_FILE/FILE_NAME 的字段值
END;
-- 多字段同时赋值
DECLARE
	fileName NVARCHAR2(30);
	filePath NVARCHAR2(60);
BEGIN
	SELECT D.FILE_NAME, D.FILE_PATH INTO fileName, filePath FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	DBMS_OUTPUT.PUT_LINE('fileName = ' || fileName);
	DBMS_OUTPUT.PUT_LINE('filePath = ' || filePath);
END;
/*
SELECT ... INTO ... 语句会使用一个隐式游标处理数据，这种隐式游标只允许结果集中有且仅有一行数据！！！
	1.没有查询到任何结果集时，会抛出OCI_NO_DATA异常
	2.结果集中含有两行或者多行，会抛出TOO_MANY_ROWS异常
	
因此有多行数据时，需要我们开发者定义一个显示游标，通过与游标有关的语句进行处理。
*/

/*
使用 %TYPE 定义变量
	1.为了是PL/SQL中的变量类型和数据表中的字段的数据类型一致，Oracle 9i 提供了新的变量定义方法 %TYPE
	2.这样当数据表的字段类型修改后，PL/SQL程序中的相应的变量类型也会自动适应与修改
*/
DECLARE
	fileName GJ_INST.DL_PRE_FILE.FILE_NAME%TYPE;
	filePath GJ_INST.DL_PRE_FILE.FILE_PATH%TYPE;
BEGIN
	SELECT D.FILE_NAME, D.FILE_PATH INTO fileName, filePath FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	DBMS_OUTPUT.PUT_LINE('fileName = ' || fileName);
	DBMS_OUTPUT.PUT_LINE('filePath = ' || filePath);
END;
/*
使用 %ROWTYPE 定义变量
	1.使用 %TYPE 可以是变量获得字段的数据类型，使用 %ROWTYPE 可以使变量获得整条记录(所有字段)的数据类型
	2.定义方式 variable table_name%rowtype
*/
DECLARE
	V_FILE GJ_INST.DL_PRE_FILE%ROWTYPE;
BEGIN
	SELECT D.FILE_NAME, D.FILE_PATH INTO V_FILE.FILE_NAME, V_FILE.FILE_PATH FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	DBMS_OUTPUT.PUT_LINE(V_FILE.FILE_NAME);
	DBMS_OUTPUT.PUT_LINE(V_FILE.FILE_PATH);
END;

1.2、条件控制

PL/SQL中关于条件控制的关键字有

IF THEN
IF THEN ELSE
IF THEN ELSIF
多分支条件 CASE 语句

/*
看似有多种形式，其实也就是写法不同，使用方式也比较简单，例如 IF THEN 的语法如下
IF 条件 THEN 结果; END IF;
*/
-- 单分支写法
DECLARE
	v_type GJ_INST.DL_PRE_FILE.TYPE%TYPE;
BEGIN
	SELECT D.TYPE INTO v_type FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	IF v_type = 'txt' 
		THEN DBMS_OUTPUT.PUT_LINE('不能预览[' || v_type || ']文件');
	END IF;
END;

-- 多分支写法
DECLARE
	STATE GJ_INST.DL_PRE_FILE.STATE%TYPE;
BEGIN
	SELECT D.STATE INTO STATE FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	
	IF STATE = 1
		THEN DBMS_OUTPUT.PUT_LINE('已成功读取！');
	ELSIF STATE = 2 
		THEN DBMS_OUTPUT.PUT_LINE('读取失败！');
	ELSE 
		DBMS_OUTPUT.PUT_LINE('还未读取！');
	END IF;
END;
-- 多分支 CASE WHEN 写法
DECLARE
	STATE GJ_INST.DL_PRE_FILE.STATE%TYPE;
BEGIN
	SELECT D.STATE INTO STATE FROM GJ_INST.DL_PRE_FILE D WHERE D.ID = 1;
	CASE 
		WHEN STATE = 1
			THEN DBMS_OUTPUT.PUT_LINE('已成功读取！');
		WHEN STATE = 2 
			THEN DBMS_OUTPUT.PUT_LINE('读取失败！');
		ELSE 
			DBMS_OUTPUT.PUT_LINE('还未读取！');
	END CASE;
END;

--修改表里面的数据
DECLARE 
	USERNAME LN_USER.USERNAME%TYPE;
BEGIN
	SELECT U.USERNAME INTO USERNAME FROM LN.LN_USER U WHERE U.ID = 5;
	IF USERNAME = 'alex'
		THEN UPDATE LN.LN_USER U SET U.USERNAME = 'alex99' WHERE U.ID = 5;
		COMMIT;
	END IF;
END;

1.3、循环结构

在编程语言中循环语句就是重复执行某个逻辑直到满足条件后结束。在PL/SQL中支持

LOOP循环
FOR ... LOOP循环
WHILE ... LOOP循环

其中FOR循环最为特殊，它可以用于迭代已定义结果集的行。

第1种 LOOP 循环用法：

-- 求 100 以内偶数数之和并输出
DECLARE
	v_num NUMBER := 1; -- 注意变量做计算时一定要赋初始值不能为 NULL，因为NULL做计算时都等于NULL，会导致死循环
	v_sum NUMBER := 0;
BEGIN
	LOOP
		IF MOD(v_num, 2) = 0 
			THEN v_sum := v_sum + v_num;
		END IF;
		v_num := v_num + 1;
		EXIT WHEN v_num = 100; -- 注意循环退出方式
	END LOOP;
	DBMS_OUTPUT.PUT_LINE(v_sum);
END;
-- 关于 LOOP 循环体的退出方式还有另外一种写法例如
DECLARE
	v_num NUMBER := 1;
	v_sum NUMBER := 0;
BEGIN
	LOOP
		IF MOD(v_num, 2) = 0 
			THEN v_sum := v_sum + v_num;
		END IF;
		v_num := v_num + 1;
		IF (v_num = 100) 
			THEN EXIT;
		END IF;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE(v_sum); -- 2450
END;

第2种 FOR ... LOOP 循环用法：

-- 求 100 以内偶数数之和并输出
DECLARE
	v_sum NUMBER := 0;
BEGIN
	FOR I IN 1..99 LOOP
		IF MOD(I, 2) = 0 
			THEN v_sum := v_sum + I;
		END IF;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE(v_sum); -- 2450
END;
-- 遍历结果集
DECLARE 
BEGIN
	FOR I IN (SELECT * FROM GJ_INST.DL_PRE_FILE) LOOP
		DBMS_OUTPUT.PUT_LINE(I.ID);
	END LOOP;
END;

第3种 WHILE ... LOOP 循环用法：

-- 求 100 以内偶数数之和并输出
DECLARE 
	V_SUM NUMBER := 0;
	V_N NUMBER := 1;
BEGIN
	WHILE (V_N < 100) 
	LOOP
		IF MOD(V_N, 2) = 0
			THEN V_SUM := V_N + V_SUM;
		END IF;
		V_N := V_N + 1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE(V_SUM);
END;

以上就是PL/SQL循环的最基本的用法，但是关于循环的知识点还没有完例如更高级的嵌套循环等，例如下面案例就是使用到嵌套循环：

-- 案例1：两数交互问题(其实也很简单借助一个中间变量即可)
DECLARE
	A NUMBER := 99;
	B NUMBER := 11;
	C NUMBER := 0;
BEGIN
	DBMS_OUTPUT.PUT_LINE('交换前 A = ' || A);
	DBMS_OUTPUT.PUT_LINE('交换前 B = ' || B);
	C := A;
	A := B;
	B := C;
	DBMS_OUTPUT.PUT_LINE('交换后 A = ' || A);
	DBMS_OUTPUT.PUT_LINE('交换后 B = ' || B);
END;
/*
案例1 其实想引出两表交换的思想，在实际开发中比较常用。例如我们想在已存在很多数据的表的某个字段添加索引！
有些时候直接添加索引是不会成功的，最稳妥的方式就是先创建一个字段一模一样的空表/临时表，在空表加上索引。
然后再把数据全部插入到空表中！最后给空表重新命名即可(命名为原来的那张表名)。

ALTER TABLE USER RENAME TO USER_TMP;
ALTER TABLE USER_BACK RENAME TO USER;
ALTER TABLE USER_TEM RENAME TO USER_BACK; -- 最后 USER_BACK 这张表可以删除释放资源，也可以做数据备份
*/

案例2：水仙花数。水仙花数是指一个 3 位数，它的每个位上的数字(个位数、十位数、百位数)的3次幂之和等于它本身，例如 1^3 + 5^3 + 3^3 = 153

DECLARE
	G NUMBER := 0; -- 个位数
	S NUMBER := 0; -- 十位数
	B NUMBER := 0; -- 白位数
BEGIN
	FOR NUM IN 100..999 LOOP
		/*G := FLOOR(NUM / 100);
		S := MOD(FLOOR(NUM / 10), 10);
		B := MOD(NUM, 10);*/
		G := SUBSTR(NUM, 1, 1);
		S := SUBSTR(NUM, 2, 1);
		B := SUBSTR(NUM, 3, 1);
		IF POWER(G, 3) + POWER(S, 3) + POWER(B, 3) = NUM
			THEN DBMS_OUTPUT.PUT_LINE(NUM);
		END IF;
	END LOOP;
END;
-- 嵌套循环写法
DECLARE
BEGIN
	FOR G IN 1..9 LOOP
		FOR S IN 0..9 LOOP
			FOR B IN 0..9 LOOP
				IF POWER(G, 3) + POWER(S, 3) + POWER(B, 3) = G * 100 + S * 10 + B
					THEN DBMS_OUTPUT.PUT_LINE(G || S || B);
				END IF;
			END LOOP;
		END LOOP;
	END LOOP;
END;

-- 更高级的写法
WITH TEMP AS (SELECT LEVEL - 1 AS RN FROM DUAL CONNECT BY LEVEL <= 10)
SELECT 
	ROWNUM,
	A.RN || B.RN || C.RN AS NUM
FROM TEMP A
	INNER JOIN TEMP B ON 1 = 1
	INNER JOIN TEMP C ON 1 = 1
WHERE 1 = 1
	AND A.RN >= 1
	AND POWER(A.RN, 3) + POWER(B.RN, 3) + POWER(C.RN, 3) = A.RN * 100 + B.RN * 10 + C.RN

输出结果如下
在这里插入图片描述
案例3：打印九九乘法表

DECLARE
BEGIN
	FOR J IN 1..9 LOOP
		FOR K IN 1..J LOOP
			DBMS_OUTPUT.PUT(K || ' * ' || J || ' = ' || K * J || '  ');
		END LOOP;
		DBMS_OUTPUT.PUT_LINE('');
	END LOOP;
END;

在这里插入图片描述

2、SQL编程进阶

2.1、游标

游标的关键字是CURSOR，在工作中也比较常用。那么什么是游标？如何理解游标的工作方式呢？

在ORACLE中所有的CRUD操作在内存中会分配一个缓冲区，游标就是指向该缓冲区一个指针！也就是说查询出来的结果集会放在缓冲区，游标指向该结果集或是指向结果集中的某条记录。因此我们编写的SQL程序就可以通过游标灵活处理结果集。

游标的作用：

指定结果集中特定行位置
基于当前的结果集位置检索一行或连续多行
在结果集的当前位置修改行中的数据
对其他用户所做的数据更改定义不同的敏感性级别
可以以编程的方式访问数据库

游标的类型分类：静态和REF（动态，暂时还不太了解）。

静态游标就像是一个数据快照，打开游标后的结果集是对数据的一个备份，不会随着表执行DML语句而发生改变。并且静态游标又分为显示游标和隐式游标。隐式游标游标前面SELECT ... INTO ...语法已经说过了，这种隐式游标由ORACLE自动管理用户是控制不了的，并且只允许结果集中有且仅有一行数据。

处理多行数据的结果集，我们就需要定义一个显示的游标，那么显示游标的如何定义与使用呢？

# 显示游标的使用一般都有 声明--打开--LOOP--读取/处理--关闭 这几个步骤，例如
DECLARE
	# 声明游标方式：CURSOR 游标名 IS [查询语句]
	CURSOR c_temp IS SELECT F.FILE_NAME, F.FILE_PATH FROM GJ_INST.DL_PRE_FILE F WHERE 1 = 1; -- 使用关键字CURSOR声明一个游标 c_temp
	v_row c_temp%ROWTYPE;
BEGIN
	OPEN c_temp; -- 打开游标
		LOOP
			FETCH c_temp INTO v_row; -- 将一行记录赋值到 v_row 变量中
			/*
			此时我们通过变量v_row拿到了游标的一行数据，就可以通过变量灵活处理数据
			*/
			EXIT WHEN c_temp%NOTFOUND; -- 退出LOOP
			DBMS_OUTPUT.PUT_LINE(v_row.FILE_NAME || v_row.FILE_PATH);
		END LOOP;
	CLOSE c_temp; -- 关闭游标
END;

以上就是显示游标最基本的语法，除了这种写法外还有简写方式例如

# 使用for循环遍历游标，无需打开和关闭游标，无需fetch和exit
DECLARE
	CURSOR c_temp IS SELECT F.FILE_NAME, F.FILE_PATH FROM GJ_INST.DL_PRE_FILE F WHERE 1 = 1;
BEGIN
	FOR I IN c_temp LOOP
		DBMS_OUTPUT.PUT_LINE('fileName=' || I.FILE_NAME || '/' || 'filePath=' || I.FILE_PATH);
	END LOOP;
END;

到这里游标的知识点也就结束了。这里主要是了解游标的定义与使用方式！体会把数据放到游标后，想怎么使用就使用，想怎么处理就怎么处理，是游标赋予我们灵活处理数据的能力。例如可以看下以下文章关于游标的使用场景。

游标使用示例：https://www.cnblogs.com/mq0036/p/6813239.html

2.2、动态SQL的执行

动态SQL：这里可以理解为执行"不确定"的SQL。

说道动态SQL，相信大家都不陌生，开发中一定会使用到动态SQL无论是通过框架还是通过程序！因为开发中会遇到各种各样的需求，我们不可能为每一个需求都创建SQL语句！肯定是要将SQL语句写成动态的形式。

在ORACLE中提供了EXECUTE IMMEDIATE语句来执行动态SQL，具体语法如下：

/** EXECUTE IMMEDIATE 语法与使用 **/

/** 1.PL/SQL程序块中执行DDL，在程序块中执行DDL语句一定要使用 EXECUTE IMMEDIATE 包装起来否则会报错 **/
DECLARE 
	V_SQL VARCHAR(100);
BEGIN
	V_SQL := 'TRUNCATE TABLE LN.TEST'; -- 清空表
	EXECUTE IMMEDIATE V_SQL;
END;

/** 2.PL/SQL程序块中执行DML，使用usin子句动态传值 **/
DECLARE
	V_ID NUMBER;
	V_USERNAME VARCHAR2(20);
	V_PASSWORD VARCHAR2(60);
BEGIN
	V_ID := 1;
	V_USERNAME := 'alex';
	V_PASSWORD := '123';
	EXECUTE IMMEDIATE 'INSERT INTO LN.TEST(ID, USERNAME, PASSWORD) VALUES (:1, :2, :3)'
	USING V_ID, V_USERNAME, V_PASSWORD;
	COMMIT; --EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交
END;

/** 3.语法总结 **/
-- 注意 RETURNNING INTO 子句作用于 INSERT UPDATE DELETE 语句，在SELECT不行
EXECUTE IMMEDIATE 动态SQL语句 USING 绑定参数列表 RETURNNING INTO 输出的参数列表;
-- 在 SELECT 语句中应该使用 INTO 并且应该在 USING 子句前面例如
EXECUTE IMMEDIATE 动态SQL语句 INTO 输出的参数列表 USING 绑定参数列表;

/** 4.创建一个函数根据ID查询用户密码 **/
CREATE OR REPLACE FUNCTION FUN_GET_PASSWORD(ID IN INTEGER) RETURN VARCHAR2 IS
  v_password VARCHAR2(50);
  v_sql VARCHAR2(100);
BEGIN 
  v_sql := 'SELECT U.PASSWORD FROM LN.LN_USER U WHERE 1 = 1 AND U.ID = :1';
  EXECUTE IMMEDIATE v_sql INTO v_password USING ID;
  RETURN v_password; --把结果返回
END FUN_GET_PASSWORD;

SELECT FUN_GET_PASSWORD(5) FROM DUAL;
/*
EXECUTE IMMEDIATE 相关说明
1. 动态SQL是指DDL语句(使用较少)和不确定的DML语句(带参数的DML)
2. 绑定参数列表为输入的参数列表，在运行时候与动态SQL语句的形参(占位符)进行赋值与绑定
3. 输出参数列表为动态SQL语句执行后返回的参数列表
4. 由于动态SQL是在运行的时候进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性
*/

到这里EXECUTE IMMEDIATE简单使用方法也就结束了，需要注意的是这里的动态SQL不是框架上根据不同条件拼接出来的SQL（拼接SQL在PL/SQL程序块中也可以根据不同的条件使用||拼接出不同的SQL，然后使用EXECUTE IMMEDIATE执行），这里的动态主要是指处理或执行动态SQL语句。

2.3、存储过程（重点）

前面所学的东西都是为存储过程做铺垫，实际开发中存储过程使用相当广泛。那么什么是存储过程呢？

存储过程（Stored Procedure简称SP）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

简单理解就是编译好经常使用的SQL语句，下次再使用的时就不用重新编写SQL，直接调用即可，提高工作效率。

存储过程的好处：

数据库的执行动作，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比SQL语句高。
一个存储过程在程序在网络中交互时可以替代大堆的SQL语句，所以也能降低网络的通信量，提高通信速率。
通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

存储过程的创建和使用：

/** 1.创建 **/
CREATE OR REPLACE PROCEDURE procedure_name(param1 IN OUT type, param2 IN OUT type, ...) AS -- IS 也可以
	-- 声明变量 
BEGIN
	-- PL/SQL
END procedure_name;

--注意：没有参数时，只用写存储过程名称就可以了否则编译不通过，例如无参存储过程编写方式如下：
CREATE OR REPLACE PROCEDURE SP_NOW AS 
	V_DATE VARCHAR2(50);
BEGIN
	SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') INTO V_DATE FROM DUAL;
	DBMS_OUTPUT.PUT_LINE(V_DATE);
END SP_NOW;

/** 2.调用存储过程  **/
BEGIN
  -- CALL THE PROCEDURE
  SP_NOW;
END;
-- 也可以直接在SQL窗口中调用，存储过程实际上是一种函数
CALL SP_NOW(); 

/** 3.删除存储过程  **/
DROP PROCEDURE SP_NOW;

/** 4.有参存储过程  **/
-- 存储过程参数不带取值范围，IN表示传入，OUT 表示输出，不写时默认是IN 类型可以使用任意Oracle中的合法类型

CREATE OR REPLACE PROCEDURE SP_HELLO(name VARCHAR2) AS 
	v_date VARCHAR2(50);
	v_message VARCHAR2(50);
BEGIN
  SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') INTO v_date FROM DUAL;
	v_message := '[' || v_date || '] hello ' || name;
	DBMS_OUTPUT.PUT_LINE(v_message);
END SP_HELLO;

CREATE OR REPLACE PROCEDURE SP_HELLO(name IN VARCHAR2, message OUT VARCHAR2) AS 
  v_date VARCHAR2(50);
BEGIN
  SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') INTO v_date FROM DUAL;
	message := '[' || v_date || '] hello ' || name;
	DBMS_OUTPUT.PUT_LINE(message);
END SP_HELLO;

/** 有参存储过程调用方式 **/
DECLARE
	v_message VARCHAR2(50);
BEGIN
	SP_HELLO('alex', v_message);
	DBMS_OUTPUT.PUT_LINE(v_message);
END;

存储过程的基本语法就是这样，我们可以加上上面所学的所有东西包括游标，动态SQL执行等灵活的写出符合业务的存储过程。

2.4、合并 / merge into

merge into本身就是一个英文短语，翻译过来就是合并、并入的意思。在PL/SQL中merge into是合并了INSERT和UPDATE操作，即匹配到则执行更新匹配不到则执行插入！当有更新又有插入我们可以考虑使用merge into因为它的执行效率要高于单独执行INSERT和UPDATE语句。

另外使用最多的场景就是从备份表中更新字段到正式表中，使用UPDATE批量更新大量的数据，会出现效率低下甚至会卡死！因此使用merge into代替UPDATE执行批量更新操作，会提升执行效率。

merge into语法：

MERGE INTO 目标表
USING (增量)
ON (匹配字段)
WHEN MATCHED THEN UPDATE SET 
WHEN NOT MATCHED THEN INSERT VALUES

/*
注意点：
	1.UPDATE和SET之间不需要加表名
	2.INSERT和VALUES之间不需要加INTO表名
	3.匹配字段不能被更新
*/

使用示例：

/** 来源表LYB_TEST **/
CREATE TABLE LN.LYB_TEST (
	ID NUMBER(32) NOT NULL,
	NAME NVARCHAR2(100) NOT NULL
);
INSERT INTO LN.LYB_TEST(ID, NAME) VALUES (1, 'Java');
INSERT INTO LN.LYB_TEST(ID, NAME) VALUES (2, 'JavaScript');
INSERT INTO LN.LYB_TEST(ID, NAME) VALUES (3, 'Python');
INSERT INTO LN.LYB_TEST(ID, NAME) VALUES (4, 'C/C++');

/** 目标表MBB_TEST **/
CREATE TABLE LN.MBB_TEST (
	ID NUMBER(32) NOT NULL,
	NAME NVARCHAR2(100) NOT NULL
);
INSERT INTO LN.MBB_TEST(ID, NAME) VALUES (1, 'Java88');

SELECT * FROM LN.MBB_TEST;

/** 执行SQL **/
MERGE INTO LN.MBB_TEST M
USING (SELECT * FROM LN.LYB_TEST) L ON (M.ID = L.ID)
WHEN MATCHED THEN UPDATE SET
	M.NAME = L.NAME
WHEN NOT MATCHED THEN INSERT VALUES(L.ID, L.NAME);

/** 再次查询目标表 **/
SELECT * FROM LN.MBB_TEST;

在这里插入图片描述

2.5、存储过程日志

说道存储过程的日志，说白了就是新增一条日志记录。并不是很强大的功能记录日志只是方便我们排查报错原因，看每一步的执行时间为SQL优化提供一些依据。除此之外只剩下缺点了，写入数据肯定需要事务支持！因此对数据库资源消耗比较大。另外存储过程发生异常，日志也会丢失。

/** 存储过程日志的使用 **/
/** 1.创建日志表 **/
-- Create table
DROP TABLE LN.DML_LOG;
CREATE TABLE LN.DML_LOG(
	ID NUMBER(32) NOT NULL,
	PROC_NAME VARCHAR2(100),
	DOMAIN VARCHAR2(100),
	SUBJECT VARCHAR2(100),
	STEP_NO VARCHAR2(50),
	BEGIN_TIME TIMESTAMP,
	END_TIME TIMESTAMP,
	WORK_TIME TIMESTAMP,
	ROW_NUM NUMBER(20),
	ELAPSED NUMBER(20),
	ALL_ELAPSED NUMBER(20),
	SQL_CODE VARCHAR2(100),
	SQL_ERROR VARCHAR2(1000),
	STEP_DESC VARCHAR2(1000)
);
-- Create/Recreate primary
ALTER TABLE LN.DML_LOG ADD PRIMARY KEY (ID) USING INDEX TABLESPACE LN_TEST
PCTFREE 10
INITRANS 2
MAXTRANS 255;

-- Add comments to the columns 
COMMENT ON COLUMN LN.DML_LOG.ID IS '主键';
COMMENT ON COLUMN LN.DML_LOG.PROC_NAME IS 'sp名称';
COMMENT ON COLUMN LN.DML_LOG.DOMAIN IS '作用域';
COMMENT ON COLUMN LN.DML_LOG.SUBJECT IS '主题';
COMMENT ON COLUMN LN.DML_LOG.STEP_NO IS '步骤编号';
COMMENT ON COLUMN LN.DML_LOG.BEGIN_TIME IS '开始时间';
COMMENT ON COLUMN LN.DML_LOG.END_TIME IS '结束时间';
COMMENT ON COLUMN LN.DML_LOG.WORK_TIME IS '工作日期';
COMMENT ON COLUMN LN.DML_LOG.ROW_NUM IS '影响行数';
COMMENT ON COLUMN LN.DML_LOG.ELAPSED IS '执行时间';
COMMENT ON COLUMN LN.DML_LOG.ALL_ELAPSED IS '总执行时间';
COMMENT ON COLUMN LN.DML_LOG.SQL_CODE IS '错误代码';
COMMENT ON COLUMN LN.DML_LOG.SQL_ERROR IS '错误步骤';
COMMENT ON COLUMN LN.DML_LOG.STEP_DESC IS '步骤描述';

-- Create/Recreate indexes 
CREATE INDEX DML_LOG_PROC_NAME ON LN.DML_LOG(PROC_NAME) TABLESPACE LN_TEST
PCTFREE 10
INITRANS 2
MAXTRANS 255;

/** 2.存储过程新增日志记录 **/
CREATE OR REPLACE PROCEDURE SP_LN_USER_BACK(START_TIME IN VARCHAR2, END_TIME IN VARCHAR2, SQL_CODE OUT NUMBER) AS 

V_START_TIME TIMESTAMP := TO_DATE(START_TIME, 'yyyy-mm-dd hh24:mi:ss');
V_END_TIME TIMESTAMP := TO_DATE(END_TIME, 'yyyy-mm-dd hh24:mi:ss');
V_BEGIN_TIME TIMESTAMP := SYSDATE; -- 程序执行时间

V_SQL VARCHAR2(100);
V_SP_NAME VARCHAR2(100);
V_SP_DOMAIN VARCHAR2(100);
V_SP_SUBJECT VARCHAR2(100);
V_LOG_STEP_NO VARCHAR2(100);
V_LOG_BEGIN_TIME TIMESTAMP;
V_LOG_DESC VARCHAR2(100);
V_LOG_ROWCOUNT NUMBER(20); -- 记录dml行数

BEGIN
	SQL_CODE := 0; -- 返回sp运行代码 0失败 1成功
	-- 变量赋值
	V_SP_NAME := 'SP_LN_USER_BACK';
	V_SP_DOMAIN := '测试存储过程日志记录';
	V_SP_SUBJECT := '测试';
	
	-- 清除目标表
	V_LOG_STEP_NO := 'STEP_01';
	V_LOG_BEGIN_TIME := SYSDATE;
	V_LOG_DESC := '清除目标LN_USER_BACK表数据';
	
	-- 插入日志表
	V_SQL := 'INSERT INTO LN.DML_LOG(ID, PROC_NAME, DOMAIN, SUBJECT, STEP_NO, BEGIN_TIME, WORK_TIME, STEP_DESC) VALUES(:1, :2, :3, :4, :5, :6: :7, :8)'
	EXECUTE IMMEDIATE V_SQL 
	USING S_LN_USER.NEXTVAL, V_SP_NAME, V_SP_DOMAIN, V_SP_SUBJECT, V_LOG_STEP_NO, V_LOG_BEGIN_TIME, V_LOG_BEGIN_TIME, V_LOG_DESC;
	COMMIT;
	
	-- 删除目标表数据
	EXECUTE IMMEDIATE 'DELETE FROM LN.LN_USER_BACK B WHERE B.CREATE_TIEM >= :1 AND B.CREATE_TIME < :2'
	USING V_START_TIME, V_END_TIME;
	V_LOG_ROWCOUNT := SQL%ROWCOUNT; --统计dml语句影响的行数，一定要写在 COMMIT 的前面
	COMMIT;
	
	--删除数据之后，记录日志表更新删除数据前插入的日志
	EXECUTE IMMEDIATE 
	'
		UPDATE LN.DML_LOG L SET L.END_TIME = SYSDATE, L.ROW_NUM = :1, L.ELAPSED = (SYSDATE - :2) * 86400, L.ALL_ELAPSED = (SYSDATE - :3) * 86400
		WHERE 1 = 1 AND L.PROC_NAME = :4 AND L.DOMAIN = :5 AND L.SUBJECT = :6 AND L.BEGIN_TIME = :7
	'
	USING V_LOG_ROWCOUNT, V_BEGIN_TIME, V_BEGIN_TIME, V_SP_NAME, V_SP_DOMAIN, V_SP_SUBJECT, V_LOG_BEGIN_TIME;
	COMMIT;
END SP_LN_USER_BACK;

2.6、异常处理

在程序运行时出现的错误，称为异常。异常发生后，SQL语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL程序块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时，每一个异常都会对应一个异常码和异常信息。

在Oracle异常中，为了开发和维护方便，常见的异常码定义了对应的异常名称，称为预定义异常，例如常见的预定义异常有：

异常名称	异常码	描述
DUP_VAL_ON_INDEX	ORA-00001	试图向唯一索引列插入重复值
INVALID_CURSOR	ORA-01001	试图进行非法游标操作
INVALID_NUMBER	ORA-01722	试图将字符串转换为数字
NO_DATA_FOUND	ORA-01403	SELECT INTO 语句中没有返回任何记录
TOO_MANY_ROWS	ORA-01422	SELECT INTO 语句中返回多条记录
ZERO_DIVIDE	ORA-01476	试图用0作为除数
CURSOR_ALREADY_OPEN	ORA-06511	试图打开一个已经打开的游标

前面也已经说了，在PL/SQL程序块中使用EXCEPTION关键字声明处理异常方式。语法格式如下：

BEGIN
-- 可执行部分
EXCEPTION
	WHEN EXCEPTION_NAME_1 THEN
		-- 对应异常处理
	WHEN EXCEPTION_NAME_2 THEN
		-- 对应异常处理
	WHEN OTHERS THEN
		-- 其他异常处理
	END;
END;

/** 例子：0不能做除数问题 **/
declare
	a number;
	b number;
	c number;
begin
	a := 1;
	b := 0;
	c := a / b;
exception
	when ZERO_DIVIDE then
		dbms_output.put_line('0不能做除数');
	when others then 
		dbms_output.put_line('程序异常');
end;

/** 例子：处理NO_DATA_FOUND(SELECT INTO 语句中没有返回任何记录)异常 **/
create or replace function fun_get_password(id in varchar2) return varchar2 as 
	password varchar2(100);
	v_sql varchar2(100);
	v_id number;
begin
	v_sql := 'SELECT U.PASSWORD FROM LN.LN_USER U WHERE 1 = 1 AND U.ID = :1';
	v_id := TO_NUMBER(id);
	execute immediate v_sql into password using v_id;
	return password;
exception 
	when NO_DATA_FOUND then 
		password := 'id[' || id || ']查询结果为空';
		return password;
	when others then
		password := '获取失败，程序异常';
		return password;
end fun_get_password;

2.7、自定义函数

自定义函数在实际开发中非常重要，现有的系统函数很难满足某些特定的业务！因此一些通用的逻辑我们会封装成一个函数。

函数和存储过程类似（函数一般是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作），可以作为一个系统对象被存储在数据库中，可以重复调用。与存储过程不同的是函数必须返回一个值，而存储过程不能有返回值，这是返回值上的区别。在调用方式上也有区别，函数可以在查询语句中直接调用，而存储过程必须单独调用。

/** 函数创建语法 **/
create or replace function function_name(param1 [in | out] type, param2 [in | out | in out] type)
return type
as | is
	-- 变量声明与初始化
begin
	-- PL/SQL函数体(里面必需有一个 return 子句)
exception 
	-- 异常处理
end function_name;
/*
in 输入参数向存储过程传递值 in是默认类型 可以不写
out 输出参数用于返回结果
in out 既作为参数传递 同时也作为输出参数
type oracle合法数据类型
return 只能返回单个值，不能返回多个值
*/

/** 删除函数 **/
DROP FUNCTION function_name;

没有参数的函数：

/** 没有参数的函数可以省略括号 **/
CREATE OR REPLACE FUNCTION FUN_NOW RETURN VARCHAR2 AS 
v_date VARCHAR2(100);
BEGIN
	v_date := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
	RETURN v_date;
END FUN_NOW;

/* 调用函数 */
SELECT FUN_NOW() FROM DUAL;

有参数的函数：

/* 手机号中间四位变成* */
CREATE OR REPLACE FUNCTION FUN_TEL_FORMAT(tel IN VARCHAR2) RETURN VARCHAR2 AS 
	v_length NUMBER;
BEGIN 
	v_length := LENGTH(tel);
	IF (v_length <> 11) THEN
		RETURN tel;
	END IF;
	RETURN SUBSTR(tel, 1, 3) || '****' || SUBSTR(tel, 8);
END FUN_TEL_FORMAT;

SELECT FUN_TEL_FORMAT('19987131172') AS TEL FROM DUAL;

有异常有动态SQL函数：

/* 根据id获取用户表里面的密码 */
create or replace function fun_get_password(id in varchar2) return varchar2 as 
	password varchar2(100);
	v_sql varchar2(100);
	v_id number;
begin
	v_sql := 'SELECT U.PASSWORD FROM LN.LN_USER U WHERE 1 = 1 AND U.ID = :1';
	v_id := TO_NUMBER(id);
	execute immediate v_sql into password using v_id;
	return password;
exception 
	when NO_DATA_FOUND then 
		password := 'id[' || id || ']查询结果为空';
		return password;
	when others then
		password := '获取失败，程序异常';
		return password;
end fun_get_password;

等等

2.8、Package

在PL/SQL中为了满足程序模块化的需要，引入了包的构造。通过包Package分类管理存储过程(PROCEDURE)和函数(FUNCTION)。使用包是程序设计模块化的同时，对外隐藏包内所有使用的信息，当程序首次调用包内函数或存储过程时，ORACLE将整个包调入内存，当再次访问包内元素时，直接从内存中读取，不需要再进行磁盘I/O操作，从而提高程序的执行效率。

包也是一种数据库对象，相当于一个容器，将逻辑上相关的存储过程、函数、变量、游标组合成一个更大的单位。用户可以在其他PL/SQL块中对其进行引用。

一个完整的包由包规范和包体组成，称为包头和包体。

1、包头(Packages)：定义程序访问的接口，可以声明包内的数据类型、变量、常量、游标、子程序和异常错误处理等元素，类似于Java中类结构。

/* 包头元素声明语法 */
CREATE OR REPLACE PACKAGE package_name 
AS | IS
	-- 公有数据类型
	-- 公有游标
	-- 公有变量、常量
	-- 公有子程序
END [package_name];

包定义一定要在包主体前面编译，包主体可以没有，但包头定义一定要有。包的名称和包体的名称要保持一致。

2、包体(Package bodies)：包体则是包头声明的元素的具体内容与实现，定义包头所声明的游标、函数、子查询等。包主体中还可以声明包的私有元素。如果在包主体中的游标或子程序并没有再包头中定义，那么这个游标或子程序就是私有的。

/* 包体具体内容与实现声明语法 */
CREATE OR REPLACE PACKAGE BODY package_name 
AS | IS
-- 私有数据类型定义
-- 私有变量、常量声明
-- 私有子程序声明和定义
-- 公有子程序定义
BEGIN
	-- 执行部分(初始化)
END [package_name];

3、示例

/* 声明一个 SQL_UTILS 的包头 */
CREATE OR REPLACE PACKAGE SQL_UTILS AS
	FUNCTION FUN_NOW RETURN VARCHAR2;
	FUNCTION FUN_TEL_FORMAT(tel IN VARCHAR2) RETURN VARCHAR2;
END SQL_UTILS;

/* 
	1.注意包头和包体名字要保持一致(先编译包头在编译包体，不能一起编译)
	2.FUN_NOW 获取当前格式好的日期字符串
	3.FUN_TEL_FORMAT 格式化电话号码(中间四位变*)
*/

/* 包体声明 */
CREATE OR REPLACE PACKAGE BODY SQL_UTILS AS
	/* FUN_NOW 获取当前格式好的日期字符串 */
	FUNCTION FUN_NOW RETURN VARCHAR2 AS 
		v_date VARCHAR2(100);
	BEGIN
		v_date := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
		RETURN v_date;
	END FUN_NOW;

	/* FUN_TEL_FORMAT 格式化电话号码(中间四位变*) */
	FUNCTION FUN_TEL_FORMAT(tel IN VARCHAR2) RETURN VARCHAR2 AS 
		v_length NUMBER;
	BEGIN 
		v_length := LENGTH(tel);
		IF (v_length <> 11) THEN
			RETURN tel;
		END IF;
		RETURN SUBSTR(tel, 1, 3) || '****' || SUBSTR(tel, 8);
	END FUN_TEL_FORMAT;
END SQL_UTILS;

/* 调用(封装在包里面的元素，调用时必须加上包名称) */
SELECT SQL_UTILS.FUN_NOW() AS NOW FROM DUAL;

2.9、触发器

触发器(Triggers)：是一种特殊的存储过程，由事件触发并且数据库自动执行因此触发器对象名称只有一个，不能接受参数也不能使用语句块去调用。

由于数据库自身操作数据的多样性，会产生多种事件(增删改查、DDL语句等)因此触发器也分为多种：

DML触发器（最常用）：创建在表上，由DML事件引发
instead of触发器：创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）
DDL触发器：触发事件时数据库对象的创建和修改
数据库事件触发器：定义在数据库或者模式上，由数据库事件触发

创建触发器语法：

CREATE OR REPLACE TRIGGER [schema.]trigger_name
{BEFORE | AFTER | INSTEAD OF}

{DELETE | OR INSERT | OR UPDATE [OF column, ...n]}

ON [schema.]table_name | view_name

FOR EACH ROW [WHEN(condition)]
sql_statement[,...n]
DECLARE
BEGIN
END;

备份/同步数据示例：

/* 创建触发器(触发器最终都是由数据库根据不同事件触发，触发器名称取什么都行) */
CREATE OR REPLACE TRIGGER LN.TRI_USER_BACK 
AFTER INSERT OR UPDATE OR DELETE ON LN.LN_USER
FOR EACH ROW
DECLARE 
BEGIN
	CASE 
		WHEN INSERTING THEN
			INSERT INTO LN.LN_USER_BACK(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY, CREATE_TIME)
			VALUES(:NEW.ID, :NEW.USERNAME, :NEW.PASSWORD, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.PHONE, :NEW.SALARY, :NEW.CREATE_TIME);
		WHEN UPDATING THEN
			UPDATE LN.LN_USER_BACK SET USERNAME = :NEW.USERNAME, PASSWORD = :NEW.PASSWORD, FIRST_NAME = :NEW.FIRST_NAME, 
			LAST_NAME = :NEW.LAST_NAME, PHONE = :NEW.PHONE, SALARY = :NEW.SALARY, CREATE_TIME = :NEW.CREATE_TIME
			WHERE ID = :NEW.ID;
		WHEN DELETING THEN
			DELETE FROM LN.LN_USER_BACK WHERE ID = :OLD.ID;
	END CASE;
END;

/* 我们往 LN.LN_USER 表里面插入一条记录 */
INSERT INTO LN.LN_USER (ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY, CREATE_TIME) 
VALUES ('6', 'Python', 'qwe@123', 'ABC', 'EFG', '17708821695', '6000', SYSDATE);

/* 备份表查询 LN.LN_USER_BACK */
SELECT * FROM LN.LN_USER_BACK;

在这里插入图片描述

END

THANK YOU

lambda.

关注

0
点赞
踩
14

收藏

觉得还不错? 一键收藏
打赏
0
评论
基于Oracle的SQL编程

你知道吗？SQL从某种程度上说也是一门编程语言！SQL也有相应的标识符、变量流程控制语句、函数、存储过程等具备了编程语言的特性。而新的SQL规范甚至可以使用Java类并引入了包等一些概念增加了编程语言的特点，易于我们编写一些流程化的程序，还被人们称为第四代编程语言。
复制链接

扫一扫