1.plsql基础编程

实在想不到昵称
于 2023-05-06 11:02:30 发布
阅读量156
点赞数
文章标签： sql 数据库 etl
本文链接：https://blog.csdn.net/qq_45298339/article/details/130522591
版权
PL/SQL 基础
/*是数据库引入的一种过程化编程语言，它构建于SQL之上，可以用来编写包含SQL语句的程序。
在PL/SQL中可以通过过程化的语句来决定哪些代码执行以及代码的执行次数，也可以通过定义变量使数据在不同代码间得以传递。
换句话说，PL/SQL是将SQL语句强大的数据操纵能力、数据查询能力和其自身灵活的过程处理能力结合在一起，以达到各自取长
补短的效果。因此，使用PL/SQL语言可以处理比较复杂的业务逻辑。*/

PL/SQL 块结构
PL/SQL /*程序都是以块为基本单位，整个PL/SQL块由[声明部分]、[执行部分]、[异常处理部分]三部分构成，其中除执行部分是
       必选部分，其他两部分都是可选部分。无论PL/SQL程序代码量多大、逻辑有多复杂，其基本结构都是这三部分。*/
必选部分：
执行部分
/*（由关键字BEGIN开始 ，但结束位置不确定。执行部分的结束取决于执行部分中程序运行的状况。若程序正常运行，
  最终会以关键字END结束 ；若程序运行过程中出现异常，则会以报错或异常处理结束。无论程序运行时怎样结束，
  在程序编写时，BEGIN和END关键字都必须成对出现,执行部分是整个PL/SQL块的主体,包含所有的SQL语句以及PL/SQL过程化语句）*/
可选部分：
声明部分:
identidier_name [CONSTANT] data_type [NOT NULL] [:= value_expression] | [DEFAULT value_expression]
identidier_name :指定需要声明的常量、变量的名称；
data_type :      指定数据类型
“:=” :           是赋值运算符（或使用DEFAULT）；
value_expression:是赋值表达式
CONSTANT:        则表明声明的是一个常量(定义之后不能赋值)
NOT NULL:        则表明声明的变量不能为空（即在声明时必须赋值）
/*（由关键字DECLARE开始 ，到关键字BEGIN结束 。这部分内容相当于在为程序运行做准备工作，主要包括声明程序中用到的所
  有变量、常量和游标等，也可以写注释来对声明内容或程序整体做解释说明。）*/
  
异常处理部分
/*（由关键字EXCEPTION开始 ，到该关键字下所有代码执行完毕结束 。若程序运行有异常，异常处理部分结束则整个PL/SQL
  程序块结束；若程序运行无异常，则异常处理部分不会执行，PL/SQL程序块以END关键字正常结束。异常处理部分也可以像执
  行部分一样编写可执行的SQL语句和过程化语句，还可以捕获程序运行中出现的报错信息。）*/
  
标准PL/SQL块格式
DECLARE
  ---声明部分 (可选)
BEGIN
  ---执行部分（必选）
  EXCEPTION
  ---异常处理部分（可选）   
END;

1）每条语句必须以英文分号结束，单行或多行书写皆可；
2）类似于SQL语句中的子查询，块可以嵌套使用。
=======================================================================================
执行部分
DBMS_OUTPUT程序包
DBMS_OUTPUT.PUT_LINE() --打印缓存内容，再打印一行内容，并换行 
DBMS_OUTPUT.PUT()      --输出一行内容到缓存但不打印，多次执行会在一行内缓存。
NEW_LINE               --换行，执行过该命令后可将缓存中所有内容打印并换行。
多个块不能一起执行，如果一定要执行中间需要加上 / 

DECLARE
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
  DBMS_OUTPUT.PUT('HELLO');
  DBMS_OUTPUT.PUT_LINE('员工SMITH的工资是800元每月');
  DBMS_OUTPUT.PUT('HELLO');
  DBMS_OUTPUT.NEW_LINE;
END;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
  DBMS_OUTPUT.PUT_LINE('员工SMITH的工资是800元每月');
END;
================================================================================================
执行部分-DML操作
DML操作可以直接运行。
DML操作需要在程序中运行 COMMIT;命令才能自动提交！

BEGIN
  DELETE FROM EMP WHERE DEPTNO=10;
  COMMIT;
  ---清理emp表中10号部门的数据
  EXECUTE IMMEDIATE INSERT INTO EMP SELECT * FROM EMP_0419 WHERE DEPTNO=10;
  COMMIT;
  ---从备份表中把10号部门的数据局恢复
  UPDATE EMP SET SAL=SAL/2 WHERE DEPTNO=30;
  COMMIT;
  ---把30号部门的薪资调整为原来的2倍  
END; 

===================================================================================================
执行部分-DDL操作
在PL/SQL中，DDL不能直接操作，需要借助EXECUTE命令来实现。
EXECUTE IMMEDIATE ‘SQL语句’ ；
其中SQL语句可以是DML语句也可以是DDL语句
PL/SQL程序运行时，ORACLE会首先检查程序的合理性，如若有语法等问题，程序将不予执行。

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE EMP_041901 AS SELECT * FROM EMP';
  ---备份emp表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
  ---清空emp表
  /*EXECUTE IMMEDIATE 'INSERT INTO EMP SELECT * FROM  EMP_O41901 WHERE DEPTNO =30';*/
  有问题       
  ---从备份表中恢复数据
END;

/*
合法性检查是在程序运行之前，不是在程序运行过程中 但是写在EXECUTE命令中的SQL，会避开最初的合法检查，
等到该EXECUTE命令真正运行时，会检查该EXECUTE命令携带的SQL的合法性
注意：多行注释嵌套使用时，开头的/会变成*，结尾的/会变成*，如果是解除多层嵌套的注释，需要先解除外层注释，
      再解除里层注释。
*/
DECLARE
   A NUMBER(4) := 30 ;      --部门编号
   
BEGIN
   UPDATE EMP_0419 SET SAL=100 WHERE DEPTNO=A;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门薪资已更新');
   A := 10;  ---赋值
   DELETE FROM EMP_0419 WHERE DEPTNO =A;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门数据已经删除');
   /*A=10；*/ ---判断（条件）
   A :=20;
   UPDATE EMP_0419 SET SAL=5 WHERE DEPTNO=A;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门薪资已更新');
END;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/*声明部分-标识符
标识符的命名不能太随意，主要受到以下限制：
标识符的组成元素可以为A-Z字母或0-9数字和_
不建议使用除了_之外的其他特殊字符
标识符用双引号引起后，标识符内可随意使用特殊符号，且字母会区分大小写，相应的，
在使用这些标识符时也必须加上双引号
标识符不能超过30个字符
第一个字符必须是字母 不能是SQL保留字（即不要和关键词冲突）
不要设置和字段名或表名完全相同的标识符
实际上基本所有的命名都受到以上限制*/

声明部分-数据类型
一.基础数据类型
   1.字符型
    字符型包括 VARCHAR2、CHAR、LONG 等等，用于存放字符串数据。
   VARCHAR2(n)：
/*    可变长度字符串，无默认精度，使用时需指定最大长度。在PL/SQL程序语言中，VARCHAR2(n)
    类型的数据最长可设为32767个字节，而在数据库中，VARCHAR2(n)类型的数据最长可设为4000个字节。*/
   CHAR(n)：
    /*固定长度字符串，无默认精度，使用时需指定最大长度。在PL/SQL程序语言中，CHAR(n)
    类型的数据最长可设为32767个字节，而在数据库中，CHAR(n)类型的数据最长可设为2000个字节。*/
   LONG：
/*    可变长度字符串，不需要指定精度，在PL/SQL程序语言中，LONG类型的数据长度限制在32767个字节
    ，而在数据库中，LONG类型数据的长度可到2GB。不过LONG类型一般很少被使用*/

有如下原因：
/*1）字段中根本不必存储这么长的数据，VARCHAR2(n)和char(n)类型即可满足日常需要。
2）一个表中仅能设置一个LONG类型的列
3）LONG类型字段不能作为索引列或约束列
4）LONG类型字段在某些数据库工具中处理受限。
5）LONG类型字段只有在简单查询中可以运作，*/不能进行WHERE、GROUP BY 、HAVING、ORDER BY 、DISTINCT等

2.数值型
--数值型有
 NUMBER、INT、DECIMAL、BINARY_INTEGER、PLS_INTEGER --等等，实际上除了NUMBER类型，其他数值类型都是在 NUMBER
--类型上演化得出的特殊情况，我们称之为“子类型”。
NUMBER(p,s)--中的p参数表示数值的整体长度，s参数表示数值的小数位长度，其中整体长度中不含小数点。NUMBER类型的默认
           --长度是38，即p=38。
INT/PLS_INTEGER --等表示在数值中只有整数位，没有小数位，即等同于NUMBER（p,0）

3.日期型 date timestamp
--日期型常用的就只有DATE类型，它的默认长度是7个字节，分别存放世纪、年、月、日、时、分和秒。
--除了DATE之外还有个TIMESTAMP类型，它的内容中还包括时区和毫秒。
4.布尔类型
--布尔类型只有一种——BOOLEAN类型，这种类型不用在表中，而是用在PL/SQL中的过程控制中，其内容可以是
  TRUE、FALSE、NULL
--中的一种。

二.特殊数据类型
1.%TYPE类型  表名.列名%type：可以声明一个与指定列名类型相同的数据类型
--当声明一个变量名称后便声明其具体的数据类型，而使用%TYPE类型可以在声明变量名称后再指定一个字段，
--声明该变量的数据类型与指定字段的数据类型一致。
使用%TYPE的优势：
--用户不必特意查看表中字段的字段属性，就能确保变量能够存储该字段中的数据；
--如果表中某字段的字段属性发生了改变，在PL/SQL程序中对应的变量不必做任何改动，变量的数据类型会根据
--字段的调整做出相应的变动，从而确保程序的顺利运行。
2.RECORD类型    表名.列名%type：可以声明一个与指定列名类型相同的数据类型
--RECORD类型又称记录类型，该类型的变量可以存储由多个列组成的一行数据。 因为变量中的每一列需要有明
--确的数据类型，且每个变量中的列成员是不固定的，所以在声明记录类型的变量之前，需要先定义一个记录类型，再声明变量 。
3.%ROWTYPE类型     当需要接收该表所有字段内容时可以用 定义的变量接收，否则不可以
--%ROWTYPE类型是结合了%TYPE类型与RECORD类型的特点，%TYPE类型是效仿一个字段设置数据类型，
--RECORD类型是设置一行变量的数据类型，而%ROWTYPE是效仿一行字段设置变量的数据类型。
--%ROWTYPE类型与RECORD类型一样本身没有确切的列数和属性，但是%ROWTYPE类型不需要单独定义，直接使用即可。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DECLARE
   -- 声明字符型变量
   v_varchar2    VARCHAR2(20);
   v_char        CHAR(20);
   v_long        LONG;  
   -- 声明数值型变量
   v_number      NUMBER(10,2);
   v_int         INTEGER;
   v_dec         DECIMAL(8,2);
   v_bin_int     BINARY_INTEGER;
   v_pls_int     PLS_INTEGER; 
   -- 声明日期型变量
   v_date        DATE;
   v_ts          TIMESTAMP;
   -- 声明布尔型变量
   v_bool        BOOLEAN;
   -- 声明 %TYPE 变量
   v_emp_id      employees.employee_id%TYPE;
   -- 声明 RECORD 变量
   TYPE emp_rec IS RECORD ( --emp_rec只是一个数据类型，不是数据对象 使用时需要实列化
      emp_id      employees.employee_id%TYPE,
      first_name  employees.first_name%TYPE,
      last_name   employees.last_name%TYPE,
      hire_date   employees.hire_date%TYPE
   );
   --实列record变量
   v_emp_rec     emp_rec;
   -- 声明 %ROWTYPE 变量
   v_dept_rec    departments%ROWTYPE;
BEGIN
   -- 赋值给字符型变量
   v_varchar2 := 'Hello, World!';
   v_char := 'Hello, World!';
   v_long := 'Hello, World!';
   -- 赋值给数值型变量
   v_number := 123.45;
   v_int := 123;
   v_dec := 123.45;
   v_bin_int := 123;
   v_pls_int := 123;
   -- 赋值给日期型变量
   v_date := SYSDATE;
   v_ts := SYSTIMESTAMP;
   -- 赋值给布尔型变量
   v_bool := TRUE;
   -- 使用 %TYPE 声明的变量
   SELECT employee_id INTO v_emp_id FROM employees WHERE rownum = 1;
   -- 使用 RECORD 声明的变量
   SELECT employee_id, first_name, last_name, hire_date
   INTO v_emp_rec.emp_id, v_emp_rec.first_name, v_emp_rec.last_name, v_emp_rec.hire_date
   FROM employees
   WHERE rownum = 1;
   -- 使用 %ROWTYPE 声明的变量
   SELECT * INTO v_dept_rec FROM departments WHERE department_id = 10;
   -- 输出变量值
   DBMS_OUTPUT.PUT_LINE(v_varchar2);
   DBMS_OUTPUT.PUT_LINE(v_char);
   DBMS_OUTPUT.PUT_LINE(v_long);
   
   DBMS_OUTPUT.PUT_LINE(v_number);
   DBMS_OUTPUT.PUT_LINE(v_int);
   DBMS_OUTPUT.PUT_LINE(v_dec);
   DBMS_OUTPUT.PUT_LINE(v_bin_int);
   DBMS_OUTPUT.PUT_LINE(v_pls_int);
   
   DBMS_OUTPUT.PUT_LINE(v_date);
   DBMS_OUTPUT.PUT_LINE(v_ts);
   
   DBMS_OUTPUT.PUT_LINE(v_bool);
   
   DBMS_OUTPUT.PUT_LINE(v_emp_id);
   
   DBMS_OUTPUT.PUT_LINE(v_emp_rec.emp_id || ' ' || v_emp_rec.first_name || ' ' || v_emp_rec.last_name 
                        || ' ' || v_emp_rec.hire_date);
   
   DBMS_OUTPUT.PUT_LINE(v_dept_rec.department_id || ' ' || v_dept_rec.department_name 
                        || ' ' || v_dept_rec.manager_id)
end;
---------------------变量手动赋值 &——————————————————————————————————————————————————————————
DECLARE 
  V_DEPTNO NUMBER :=&请输入部门编号;
  --弹出窗口，键盘输入
BEGIN
  DELETE FROM EMP_0419 WHERE DEPTNO=V_DEPTNO;
  DBMS_OUTPUT.PUT_LINE(V_DEPTNO||号部门已删除);
END;

====================
DECLARE 
  V_ENAME VARCHAR2(20) :=&请输入员工姓名;
  --弹出窗口，键盘输入
BEGIN 
  DELETE FROM EMP_0419 WHERE ENAME=UPPER(V_ENAME);
  ---如果不用upper（）则输入小写不会转化为大写
     ----小写不能被删除
  DBMS_OUTPUT.PUT_LINE(V_ENAME||员工已被删除);
END;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DECLARE 
  A NUMBER :=10;
  B  CONSTANT NUMBER :=20;
  C DATE;
  D DATE;
BEGIN
  C:=SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE(A);
  DBMS_OUTPUT.PUT_LINE(B);
  D:=SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE(C-D);--计算程序执行时间
END;
====================
DECLARE 
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  --SELECT ENAME,SAL,HIREDATE INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  ---ERROR   不能把不同的数据类型放到一个里
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  ---把查到的值ENAME放到V_ENAME中
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;
====================
DECLARE 
  type EMP_REC IS RECORD(
  EMP_ID  EMP.EMPNO%TYPE,
  EMP_ENAME  EMP.ENAME%TYPE
  );
  v_emp_rec emp_rec;
BEGIN
  SELECT EMPNO,ENAME INTO v_EMP_REC.EMP_ID,v_EMP_REC.EMP_ENAME FROM EMP WHERE EMPNO=7369;
  ---ERROR   不能把不同的数据类型放到一个里
  DBMS_OUTPUT.PUT_LINE(v_EMP_REC.EMP_ID||v_EMP_REC.EMP_ENAME);
END;

==========================
DECLARE 
  v_emp_rec emp%rowtype;
BEGIN
  SELECT EMPNO,ENAME INTO v_EMP_REC.empno,v_EMP_REC.ename FROM EMP WHERE EMPNO=7369;
  ---ERROR   不能把不同的数据类型放到一个里
  DBMS_OUTPUT.PUT_LINE(v_EMP_REC.ename||v_EMP_REC.ename);
END;


=========================================================================================
                              ++++++++++++EXECUTE IMMEDIATE +++++++++++++++
                              
在 Oracle 数据库中，EXECUTE IMMEDIATE 语句可以用于动态执行一个 SQL 语句或 PL/SQL 代码块。
EXECUTE IMMEDIATE 语句的语法如下：

EXECUTE IMMEDIATE dynamic_sql_statement [ INTO {define_variable[, define_variable]... | record_variable} ]
[ USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]... ];

dynamic_sql_statement   表示要执行的 SQL 语句或 PL/SQL 代码块。
EXECUTE IMMEDIATE       语句后面可以跟随一个 SELECT 语句、INSERT 语句、UPDATE 语句、DELETE 语句等任何 SQL 语句，
                        或一个 BEGIN...END 块中的 PL/SQL 代码。
/*
如果dynamic_sql_statement 是一个 SELECT 语句，则可以使用 INTO 子句将查询结果存储到一个或多个定义变量中，
或存储到一个记录变量中。例如：*/
DECLARE
  l_empno NUMBER;
  l_ename VARCHAR2(50);
BEGIN
  EXECUTE IMMEDIATE 'SELECT empno, ename FROM emp WHERE empno = :1'
    INTO l_empno, l_ename
    USING 7369;
  DBMS_OUTPUT.PUT_LINE('Empno: ' || l_empno || ', Ename: ' || l_ename);
END;
/*
如果 dynamic_sql_statement 是一个 INSERT、UPDATE 或 DELETE 语句，则不需要使用 INTO 子句，
因为这些语句不会返回结果集。例如：
*/
DECLARE
  l_deptno NUMBER := 50;
  l_dname VARCHAR2(50) := 'SALES';
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO dept (deptno, dname) VALUES (:1, :2)'
    USING l_deptno, l_dname;
  DBMS_OUTPUT.PUT_LINE('Department ' || l_deptno || ' created');
END;
/*
如果 dynamic_sql_statement 是一个 PL/SQL 代码块，则需要使用 BEGIN...END 块来封装该代码块，
并使用 EXECUTE IMMEDIATE 语句执行该代码块。例如：*/
DECLARE
  l_stmt VARCHAR2(100);
BEGIN
  l_stmt := 'BEGIN ' ||
            '  DBMS_OUTPUT.PUT_LINE(''Hello, world!''); ' ||
            'END;';
  EXECUTE IMMEDIATE l_stmt;
END;
/*
需要注意的是，使用 EXECUTE IMMEDIATE 语句需要非常谨慎，因为它可以执行任意 SQL 语句或 PL/SQL 代码，
包括具有潜在风险的语句。在实际应用中，应该使用参数化查询来防止 SQL 注入攻击，
同时使用异常处理来捕获执行过程中的错误。*/
==================================================================================================
USING 子句的语法如下：
USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...

IN --类型的绑定变量用于向动态 SQL 语句传递输入值
DECLARE
  l_empno NUMBER := 7369;
  l_ename VARCHAR2(50);
BEGIN
  EXECUTE IMMEDIATE 'SELECT ename FROM emp WHERE empno = :1'
    INTO l_ename
    USING l_empno;
  DBMS_OUTPUT.PUT_LINE('Empno: ' || l_empno || ', Ename: ' || l_ename);
END;

OUT --类型的绑定变量用于从动态 SQL 语句中获取输出值
DECLARE
  l_empno NUMBER := 7369;
  l_ename VARCHAR2(50);
BEGIN
  EXECUTE IMMEDIATE 'SELECT ename INTO :1 FROM emp WHERE empno = 7369'
    USING OUT l_ename;
  DBMS_OUTPUT.PUT_LINE('Empno: ' || l_empno || ', Ename: ' || l_ename);
END;

in out --既可以传入变量也可以接收变量，当EXECUTE IMMEDIATE后面接存储函数的时候用的较多
---定义存储函数
CREATE OR REPLACE PROCEDURE update_employee_name(
  p_employee_id IN NUMBER,
  p_employee_name IN OUT VARCHAR2
) AS
BEGIN
  UPDATE employees
  SET employee_name = p_employee_name
  WHERE employee_id = p_employee_id;
  
  SELECT employee_name INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;
END;
---IN OUT对变量的使用
DECLARE
  l_employee_id NUMBER := 1001;
  l_employee_name VARCHAR2(50) := 'John Smith';
BEGIN
  EXECUTE IMMEDIATE 'BEGIN update_employee_name(:1, :2); END;'
    USING l_employee_id, IN OUT l_employee_name;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || l_employee_name);
END;