020.PL-SQL编程—变量

我 的 个 人 主 页：👉👉 失心疯的个人主页 👈👈
入 门 教 程 推 荐 ：👉👉 Python零基础入门教程合集 👈👈
虚 拟 环 境 搭 建 ：👉👉 Python项目虚拟环境(超详细讲解) 👈👈
PyQt5 系 列 教 程：👉👉 Python GUI(PyQt5)文章合集 👈👈
Oracle数据库教程：👉👉 Oracle数据库文章合集 👈👈
优 质 资 源 下 载 ：👉👉 资源下载合集 👈👈
优 质 教 程 推 荐：👉👉 Python爬虫从入门到入狱系列 合集👈👈

变量

• 在编写PL/SQL程序时，可以定义变量和常量
• 常用变量类型
  1. 标量类型（scalar） —— 一般变量
  2. 复合类型（composite） —— 字典、数组
  3. 参照类型（reference）
  4. lob（large object）

---

---

标量（scalar）

• 在编写PL/SQL块时，如果要使用变量，需要在定义部分定义变量。
• 定义变量和常量的语法：

  identifier [constant] datatype [not null] [:= default expr]
  

• 参数说明

  参数	说明
  identifier	名称
  constant	指定常量。需要指定其初始值，且初始值不能改变
  datatype	数据类型
  not null	指定变量值不能为null
  :=	给变量或常量指定初始值
  default	用于指定初始值
  expr	指定初始值的PL/SQL表达式。可是文本值、其他变量、函数等

• 示例

1. 标量定义

   • 定义一个变长字符串

     v_ename varchar2(10);
     

   • 定义一个小数，范围-9999.99~9999.99

     v_sal number(6,2);
     

   • 定义一个小数并给一个初始值为5.4

     v_sal number(6,2):=5.4
     

   • 定义一个日期类型的数据

     v_hiredate date;
     

   • 定义一个布尔变量，不能为空，初始值为False

     v_valid boolean not null default False;
     

2. 标量使用

   • 在定义好变量后，就可以使用这些变量。这里需要说明的是PL/SQL块为变量赋值不同于其他的编程语言，需要在等号前加冒号（:=）
   • 案例：

     -- 输入员工工号，显示员工姓名、工资、个人所得税（税率为0.03）
     
     declare
       --定义常量：税率
       c_tax_rate number(3,2):=0.03;
       --定义变量：姓名、工资、所得税
       v_ename varchar2(5);
       v_sal number(7,2);
       v_tax_sal number(7,2);
     begin
       --执行部分，查询姓名和工资
       select ename,sal into v_ename,v_sal from emp where empno=&no;
       -- 计算所得税
       v_tax_sal:=v_sal * c_tax_rate;
       -- 输出结果
       dbms_output.put_line('员工姓名：'||v_ename||'    工资待遇：'||v_sal||'    所 得 税：'||v_tax_sal);
     end;
     

     • 

     • 

3. 标量：使用%type类型

   • 对于上面的PL/SQL块有一个问题：

   • 如果员工的姓名超过了5字符，就会有错误。为了降低PL/SQL程序的维护工作量，可以使用%type属性定义变量。这样，该变量会根据数据库列来确定你定义的变量的类型和长度（类似于VBA中的变体变量）

   • 

   • %type案例

     declare
       --定义常量：税率
       c_tax_rate number(3,2):=0.03;
       --定义变量：姓名、工资、所得税
       v_ename emp.ename%type;
       v_sal number(7,2);
       v_tax_sal number(7,2);
     begin
       --执行部分，查询姓名和工资
       select ename,sal into v_ename,v_sal from emp where empno=&no;
       -- 计算所得税
       v_tax_sal:=v_sal * c_tax_rate;
       -- 输出结果
       dbms_output.put_line('员工姓名：'||v_ename||'    工资待遇：'||v_sal||'    所 得 税：'||v_tax_sal);
     end;
     

     • 

     • 

---

复合变量（composite）

• 用于存放多个值的变量，主要包括这几种(类似于VBA中的数组？)
  • PL/SQL记录 —— 字典
  • PL/SQL表 —— 一维数组
  • 嵌套表 nested table
  • 变长数组 varray

1. PL/SQL记录

   • 类似于高级语言中的结构体。需要注意的是，当引用PL/SQL记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）
   • PL/SQL记录实例

     -- 使用pl/sql编写一个块，输入员工编号，显示员工姓名、工资和上级领导
     --pl/sql记录实例
     declare
     --定义一个pl/sql记录类型emp_record_type，包含三个元素（name、salary、title），并指定三个元素的类型与数据库三个字段的类型一致
     type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
     --定义变量sp_record，并指定类型为emp_record_type
     sp_record emp_record_type; --类似于v_name varchar2(10);
     begin
       select ename,sal,job into sp_record from emp where empno=&on;
       dbms_output.put_line('员工姓名：'||sp_record.name||' 员工工资：'||sp_record.salary||' 上级领导：'||sp_record.title);
     end;
     

     • 

     • 

2. PL/SQL表

   • 相当于高级语言中的数组。但是需要注意的是在高级语言中数组的下标不能为负数，而PL/SQL是可以为负数的，并且表元素的下标没有限制

   • PL/SQL表实例

     -- 使用pl/sql编写一个块，输入员工编号，显示员工姓名
     --pl/sql表实例
     declare
     -- 定义一个pl/sql表类型emp_table_type，指定该类型存放emp.name相同类型的数据
     -- index by binary_integer指定该类型的下标是整数（正数、负数、0都可以）
     type emp_table_type is table of emp.ename%type index by binary_integer;
     -- 定义一个变量sp_table,指定其类型为emp_table_type
     sp_table emp_table_type;
     begin
       select ename into sp_table(0) from emp where empno=&on;
       dbms_output.put_line('员工姓名：'||sp_table(0));
     end;
     

   • 

   • 

3. 嵌套表 nested table

4. 变长数组 varray

---

参照变量

• 参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型
• 分类
  • 游标变量（ref cursor）
  • 对象类型变量（ref obj_type）

1. 游标变量（ref cursor）

   • 使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时(open时)需要指定select语句，这样一个游标就与一个select语句结合了

   • 游标变量实例

     1. 使用pl/sql编写一个块，可以输入部门号，显示该部门所有员工的姓名及工资

        -- 使用pl/sql编写一个块，可以输入部门号，显示该部门所有员工的姓名及工资
        declare
        -- 定义游标类型 emp_cursor_type
        type emp_cursor_type is ref cursor;
        -- 定义一个变量sp_cursor，指定类型为emp_cursor_type
        sp_cursor emp_cursor_type;
        
        -- 定义两个变量，用于接收循环取值
        v_ename emp.ename%type;
        v_sal emp.sal%type;
        begin
          -- 把sp_cursor和一个select语句相结合
          -- 打开游标类型变量sp_cursor，使其指向select语句的查询结果
          open sp_cursor for select ename,sal from emp where deptno=&no;
          -- 循环取值
          loop
            -- 取游标sp_cursor指向的值，存入变量中
            fetch sp_cursor into v_ename,v_sal;
            -- 判断游标sp_cursor未查找到值，则退出循环
            exit when sp_cursor%notfound;
            -- 打印结果
            dbms_output.put_line('员工姓名：'||v_ename||'，员工工资：'||v_sal);    
          end loop;
        end;
        

     • 

     • 

     • 

     2. 在实例1的基础上，如果员工工资低于1500，则增加500元

        declare
        -- 定义游标类型 emp_cursor_type
        type emp_cursor_type is ref cursor;
        -- 定义一个变量sp_cursor，指定类型为emp_cursor_type
        sp_cursor emp_cursor_type;
        
        -- 定义两个变量，用于接收循环取值
        v_ename emp.ename%type;
        v_sal emp.sal%type;
        begin
          -- 把sp_cursor和一个select语句相结合
          -- 打开游标类型变量sp_cursor，使其指向select语句的查询结果
          open sp_cursor for select ename,sal from emp where deptno=&no;
          -- 循环取值
          loop
            -- 取游标sp_cursor指向的值，存入变量中
            fetch sp_cursor into v_ename,v_sal;
        
            -- 判断游标sp_cursor未查找到值，则退出循环
            exit when sp_cursor%notfound;
            -- 判断工资是否低于1500，低于则增加500
            if v_sal < 1500 then
              v_sal:=v_sal + 500;
            end if;
            -- 打印结果
            dbms_output.put_line('员工姓名：'||v_ename||'，员工工资：'||v_sal);    
          end loop;
        end;