PLSQL--(基础)

PLSQL块

DECLARE
  -- 声明部分(可选)
BEGIN
  -- 执行部分(必选)
  EXCEPTION
    -- 异常处理部分(可选)
END;

声明部分：声明程序中用到的所有变量、常量、游标，也可以写注释来对声明内容或程序整体做解释说明。

执行部分：程序正常运行，最终以关键字END结束；若程序运行过程中出现异常，则会以报错或异常处理结束。关键字BEGIN和END成对出现。

异常处理部分：关键字EXCEPTION开始，到该关键字下所有代码执行完毕结束。若程序运行有异常，异常处理部分结束则整个PL/SQL程序块结束；若程序无异常，则异常处理部分不会执行，PL/SQL以END结束。

注意

每条语句必须以英文分号结束，单行或多行书写皆可

块可以嵌套使用

多个PLSQL块之间需要用 / 才能一块执行

1 PLSQL中的执行部分

1）DBMS_OUTPUT程序包

包含一系列相关程序，如下：

• PUT_LINE() 常用

  打印一行内容，并换行

• PUT()

  输出一行内容到缓存但不打印，多次执行会再一行内缓存

• NEW_LINE

  换行 执行过该命令之后可以将缓存中的所有内容打印并换行

• 例子

  BEGIN
     DBMS_OUTPUT.PUT_LINE('HELLO ORACLE!');
     DBMS_OUTPUT.PUT_LINE(1+2+3+4+5+6);
     DBMS_OUTPUT.PUT_LINE();
     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||'备份EMP完成');
  END;
  / -- 需要有/分开不同的块，才能一起执行  *************************************
  BEGIN 
    DBMS_OUTPUT.PUT('今天');
    DBMS_OUTPUT.PUT('天气');
    DBMS_OUTPUT.PUT('真好');
    DBMS_OUTPUT.NEW_LINE;
  END;
  

2）DML操作

在PL/SQL程序中，DML操作可以直接运行

DML操作需要在程序中运行COMMIT命令才能自动提交

• 例子

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

3）DDL

在PL/SQL中，DDL不能直接操作，需要借助EXECUTE命令来实现。

EXECUTE IMMEDIATE ‘SQL语句’ ； 其中SQL语句可以是DML语句也可以是DDL语句

• 例子

  BEGIN
     -- 1）备份EMP表
     EXECUTE IMMEDIATE 'CREATE TABLE EMP_041801 AS SELECT * FROM EMP';
     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||'备份EMP完成');
     -- 2）清空EMP表
     EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
     -- 3）从备份表中恢复EMP表数据
     EXECUTE IMMEDIATE 'INSERT INTO EMP SELECT * FROM EMP_041801';
     COMMIT;
  END;
  

• 注意

PL/SQL程序运行时,ORACLE会首先检查程序的合理性，如若有语法等问题，程序不予执行。

写在EXECUTE命令中的SQL，会避开最初的合法性检查，等EXECUTE命令真正执行的时候，会检查EXECUTE命令携带的SQL的合法性

4）注释

单行注释：由两个连接字符 – 开始，后面紧跟着注释内容。换行后脱离注释范围。
多行注释：由/开头，由/结尾，两者之间都属于注释范围。注意：多行注释嵌套使用时，开头的/会变成*，结尾的/会变成*，如果是解除多层嵌套的注释，需要先解除外层注释，再解除里层注释。

2 PLSQL中的声明部分

1. 因为声明部分提供了一项非常中要的功能，就是可以声明变量。所有变量的声明都需要在声明部分进行。

2. 无论是声明变量、常量还是游标等各种声明内容，
   声明基本都是由两部分组成：名称和类型，类似于建表语句中声明每一个字段，
   每个量要有自己的名字（也称标识符），同时还要有自己的属性（含数据类型与精度）。

1）标识符

标识符组成元素可以为A-Z字母或0-9数字

不建议使用除了_之外的其他特殊字符

标识符用双引号引起后，标识符可随意使用特殊字符，且字母会区分大小写，使用这些字符时也必须加上双引号

标识符必须以字母开头

不能是SQL保留字

不要设置和字段名或表名完全相同的标识符

• 例子

  DECLARE
    "A_0" NUMBER;
  BEGIN 
    "A_0" := 10;
    DBMS_OUTPUT.PUT_LINE("A_0");
    DBMS_OUTPUT.PUT_LINE("a_0"); -- 报错 必须使用"A_0" 
  END;
  

2）数据类型(标量变量：存放单个数值的变量)

• 字符型

VARCHAR2(n)：可变长度字符串，无默认精度，使用时需指定最大长度。PL/SQL中最长可设为32767个字节。数据库中最大4000

CHAR(n)：固定长度字符串，无默认精度，使用时指定最大长度。PL/SQL中最长可设为32767个字节。数据库中最大2000

LONG：可变长度字符串，不需要指定精度。PL/SQL中最长可设为32767个字节。(最长2G)

LONG RAW：可变长度字符串(最长2G)

不适用LONG的原因：

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

• 数值型

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

• 日期型

日期型常用的就只有DATE类型，它的默认长度是7个字节，分别存放世纪、年、月、日、时、分和秒。
除了DATE之外还有个TIMESTAMP类型，它的内容中还包括时区和毫秒。

• 布尔型

布尔类型只有一种——BOOLEAN类型，这种类型不用在表中，而是用在PL/SQL中的过程控制中，其内容可以是TRUE、FALSE、NULL中的一种

3）特殊的数据类型(复合变量：用复合类型来定义变量)

• %TYPE类型 (效仿字段)

使用%TYPE类型可以在声明变量名称之后，再指定一个字段，声明该变量的数据类型与指定字段的数据类型一致。

DECLARE
   V_ENAME EMP.ENAME%TYPE; -- V_ENAME 的数据类型和EMP.ENAME一样  
BEGIN
  V_ENAME := 'SMITH';
  DELETE FROM EMP WHERE ENAME = V_ENAME;
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

%type优势

1 用户不必特意查看表中字段的字段属性，就能确保变量能够存储该字段中的数据；

2 表中的字段属性发生变化 PL/SQL程序中对应的变量不必做任何改动

• RECORD类型

记录类型，该类型可以存储由多个列组成的一行数据

在声明记录类型的变量之前，需要先定义一个记录类型，再声明变量。

定义记录类型，并且指定NOT NULL约束和初始值

语法

-- 使用语法  方法  
DECLARE
 TYPE RECORD_TYPE IS RECORD  -- 定义一个记录类型  起名字
 (
      VAR_MEMBER1 DATA_TYPE,
      VAR_MEMBER2 DATA_TYPE,
      .....
      VAR_MEMBERN DATA_TYPE);
 VAR_NAME RECORD_TYPE;   --  声明一个记录类型的变量   
BEGIN 
END;

例子

DECLARE
   TYPE A IS RECORD(    --定义一个记录类型     名字是 A
        V_EMPNO NUMBER(4),
        V_ENAME VARCHAR2(100) NOT NULL :='SMITH',
        V_JOB EMP.JOB%TYPE);
   V_EMP A;   -- 声明一个变量 类型是A  A是一个记录类型  
BEGIN
  SELECT EMPNO,ENAME,JOB INTO V_EMP FROM EMP WHERE EMPNO = 7369;
  -- DBMS_OUTPUT.PUT_LINE(V_EMP);   --  报错  单列的  V_EMP是多列的  所以下面使用拼接 
  DBMS_OUTPUT.PUT_LINE(V_EMP.V_EMPNO||V_EMP.V_ENAME||V_EMP.V_JOB);
END;

• %ROWTYPE类型 (效仿表)

%ROWTYPE类型与RECORD类型一样本身没有确切的列数和属性，但是%ROWTYPE类型不需要单独定义，直接使用即可。

语法

DECLARE
  V_NAME TB_NAME%ROWTYPE; -- 效仿表结构设置变量的数据类型
BEGIN 
END;

例子

DECLARE
  V_EMP EMP%ROWTYPE;  -- 效仿EMP表设置变量的数据类型
BEGIN
  SELECT * INTO V_EMP FROM WHERE EMPNO = 7369;
  DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||V_EMP.ENAME||V_EMP.JOB||V_EMP.SAL);
END;

参照变量：存放数值指针的变量，比如游标变量和对象变量。

4）定义常量和变量

变量与常量都必须在声明部分完成声明，而后在执行部分中，变量与常量都只能被使用，但不能被定义。

1.定义变量
变量是指其值在程序运行过程中可以改变的数据存储结构，必须元素有变量名称和数据类型，可选项有初始值。
2.定义常量
常量是指其值在程序运行过程中不可改变的数据存储结构，必须元素有常量名、关键词constant、数据类型和常量值。

语法

DECLARE
  VAR_NAME DATA_TYPE  := 'VALUE';   -- 定义一个变量并赋予初始值  可以没有
  CON_NAME CONSTANT DATA_TYPE := 'VALUE';  -- 定义一个常量并赋予常量值 必须有值
BEGIN 
END;

5）变量赋值

1 通过 := 赋值，该方法可用在PL/SQL块中的任何部分，另外还可以设置&符号手动获取变量值 (如果是字符型的话加上引号，数值型的话可以不用加引号)

-- 方法1
DECLARE
  V_DEPTNO NUMBER :=&请指定部门编号; -- 数值型
  V_ENAME VARCHAR2(20) := '&请指定员工姓名'; -- 字符型
BEGIN
  DELETE FROM EMP WHERE DEPTNO = V_DEPTNO;
  DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'号部门已删除');
  DELETE FROM EMP WHERE ENAME = UPPER(V_ENAME);
  DBMS_OUTPUT.PUT_LINE('员工'||V_ENAME||'已删除');
END;

2 通过SELECT VALUE INTO VAR_NAME的方法赋值，即将数据查出后赋予到某变量

-- 方法2
DECLARE
   A VARCHAR2(10);
BEGIN
  SELECT ENAME INTO A FROM EMP WHERE EMPNO = 7566;
  DBMS_OUTPUT.PUT_LINE(A);
END;

3 EXECUTE IMMEDIATE V_SQL INTO VAR_NAME; 查出之后赋值

-- 方法3 
DECLARE
   A VARCHAR2(10);
   V_SQL VARCHAR2(2000);
BEGIN
  --EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO = 7566' INTO A;
  V_SQL := 'SELECT ENAME FROM EMP WHERE EMPNO = 7566';
  EXECUTE IMMEDIATE V_SQL INTO A;
  DBMS_OUTPUT.PUT_LINE(A);
END;

例子

DECLARE
  V_ENAME VARCHAR2(10)  := 'SMITH';
  V_SAL   NUMBER        := 1000;
  V_SQL VARCHAR2(1000);
  C_DEPTNO CONSTANT NUMBER := 10;
BEGIN
  SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = V_ENAME;
  DBMS_OUTPUT.PUT_LINE(V_ENAME||'的工资是'||V_SAL);
  V_ENAME := 'SCOTT';
  V_SQL := 'SELECT SAL FROM EMP WHERE ENAME = '''||V_ENAME||''''; -- ''表示一个单引号 ' 单引号'
  EXECUTE IMMEDIATE V_SQL INTO V_SAL;
  DBMS_OUTPUT.PUT_LINE(V_ENAME||'的工资是'||V_SAL);
  SELECT SUM(SAL) INTO V_SAL FROM EMP WHERE DEPTNO = C_DEPTNO;
  DBMS_OUTPUT.PUT_LINE(C_DEPTNO||'部门的总工资是'||V_SAL);
END;

总结

无论是怎样的赋值方法，变量是单列变量还是多列变量，每次赋值都只能给变量赋予一行数据！
如果一次性将多行数据或0行数据赋予给变量将会导致程序报错！