开发笔记_Oracle学习笔记 _14_PL/SQL(变量/常量 & 记录 & 参照变量)

时间:2021-07-30 21:40:00

Oracle学习 第14天

            —— PL/SQL 编程(变量/常量 & 记录 & 参照变量)

变量和常量

                PL/SQL 编程中,变量和常量分以下几种类型:

                        ①   标量类型(SCALAR)

                        ②   复合类型(COMPOSITE)

                        ③   参照类型(REFERENCE)

                        ④   LOB(LARGE OBJECT)

        定义变量/常量的基本语法

identifier [CONSTANT] datatype [NOT NULL] [:= default | expr]

        -- identifier:   名称
        -- constant:     如果是常量则需要该关键字,且必须执行默认值,其值不可改变
        -- datatype:    数据类型
        -- not null:    指定变量/常量是否为空
        -- :=      :     赋值符号
        -- default :    默认值
        -- expr    :    默认值的表达式

        1、标量类型

                        varchar2(n)                变长字符串
                        number(x, y)              小数类型
                        date                           日期类型
                        boolean                     布尔类型(默认false)
            ★ 注意:  
                        PL/SQL中的等于号为 = 。  需要注意区分的是,该符号在许多编程语言中是赋值符号
                        PL/SQL中的赋值符号为  := 。      例:v_test boolean := false; 
                        %type     可以自动获取表中字段的类型。
                                       常用于定义变量时动态获取表中字段类型,更加灵活。
                                       使用方法:表名.字段名%type(详见下例)
            标量示例
                        定义过程,在EMP表中根据员工编号查询员工的姓名、月薪、和年薪 = 月薪* 12
SQL> create or replace procedure pro_test(v_in_empno in number) is
  2  --定义常量Month = 12
  3  c_month constant number not null := 12;
  4  --定义变量Name、Sal_month、Sal_year
  5  v_name emp.ename%type;
  6  v_salmonth emp.sal%type;
  7  --定义年薪
  8  v_salyear number(10,2);
  9  --开始执行
 10  begin
 11  select ename, sal into v_name, v_salmonth from emp where empno = v_in_empno; --注意参数顺序对应
 12  --计算年薪
 13  v_salyear := v_salmonth * c_month;
 14  dbms_output.put_line('员工号为:' || v_in_empno || '姓名:' || v_name || '月薪:' || v_salmonth || '年薪:' || v_salyear);
 15  end;
 16  /

Procedure created


SQL> set serveroutput on;
SQL> SELECT * FROM emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected


SQL> exec pro_test(7934);

员工号为:7934姓名:MILLER月薪:1300年薪:15600

PL/SQL procedure successfully completed

        2、复合类型

                        复合变量是用于存放多个值的变量。常用以下两种:

                        PL/SQL记录:
                                PL/SQL 记录类似于其他编程语言中的结构体或者实体类。
                                当引用PL/SQL记录成员时,必须加记录变量作为前缀,即   记录变量.记录成员
                                
                                基本语法:
-- 定义PL/SQL记录
TYPE 记录名 IS RECORD(
    变量名  变量类型,
    变量名  变量类型
);

-- 使用PL/SQL记录
变量名 记录名;

            记录示例

                        定义过程,使用记录在EMP表中根据员工编号查询员工的姓名、月薪、和工作

SQL> create or replace procedure pro_test(v_in_name in number) is
  2  --定义记录 其中包括 姓名、月薪、工作
  3  type emp_record is record(
  4  v_ename emp.ename%type,
  5  v_sal emp.sal%type,
  6  v_job emp.job%type
  7  );
  8  --定义类型为自定义记录的变量
  9  v_emprecord emp_record;
 10  --开始执行
 11  begin
 12  select ename, sal, job into v_emprecord from emp where empno = v_in_name;
 13  dbms_output.put_line('姓名:' || v_emprecord.v_ename || '月薪:' || v_emprecord.v_sal || '工作:' || v_emprecord.v_job);
 14  end;
 15  /

Procedure created


SQL> exec pro_test(7934);

姓名:MILLER月薪:1300工作:CLERK

PL/SQL procedure successfully completed


                        PL/SQL表:

                                PL/SQL 表类似于其他编程语言中的数组。但其下标没有非负的限制。

                                基本语法:

-- 声明 PL/SQL 表
DECLARE
TYPE PL/SQL表类型 IS TABLE OF 表元素类型
INDEX BY BINARY_INTEGER;

-- 定义类型为 PL/SQL表类型 的变量
变量名  PL/SQL表类型;

BEGIN
-- 执行体;
END;
            表示例
                        声明PL/SQL表,根据员工编号获取员工姓名。
SQL> declare
  2  type sp_table_type is table of emp.ename%type
  3  index by binary_integer;
  4  sp_table sp_table_type;
  5  begin
  6  select ename into sp_table(-1) from emp where empno='7934';
  7  dbms_output.put_line('姓名' || sp_table(-1));
  8  end;
  9  
 10  /

姓名MILLER

PL/SQL procedure successfully completed

                        如上,sp_table 即是一个 emp.ename%type 类型的表,可以存放该类型的多个数据。同样,也可以修改查询条件,当返回多条记录(多个员工姓名时),循环存储到 sp_table 表中,存储获取的过程只需要循环下标即可。

        3、参照类型

                        参照变量是指用于存放数值指针的变量。
                        通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。

                        常用的参照变量主要有以下两种:

                    游标变量(CURSOR):
                                游标,就类似与指针,指向一个返回的结果集中的某一行。
                                通过游标,我们可以取得返回的结果集中的任何一行数据。从而提交共享效率,降低占用空间。
                                
                                基本语法:
-- 定义游标
TYPE 游标名 IS REF CURSOR;
变量名 游标名;

-- 打开游标
OPEN 游标变量 FOR select语句;

-- 取出当前游标指向的行
FETCH 游标变量 INTO 其他变量;

-- 判断游标是否指向记录最后
游标变量%NOTFOUND

            游标示例:

                        在EMP表中,传入部门编号,显示该部门所有员工的姓名与月薪,并判断月薪若低于2000,就增加100元奖金。

SQL> SELECT * FROM emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected


SQL> 
SQL> CREATE OR REPLACE PROCEDURE pro_test(v_in_deptno NUMBER) IS
  2  -- 定义游标类型
  3  TYPE test_emp_cursor IS REF CURSOR;
  4  -- 定义游标变量
  5  v_emp_cursor test_emp_cursor;
  6  -- 定义接收结果的变量
  7  v_ename emp.ename%TYPE;
  8  v_sal emp.sal%TYPE;
  9  v_empno emp.empno%TYPE;
 10  -- 执行
 11  BEGIN
 12  -- 打开游标
 13  OPEN v_emp_cursor FOR SELECT empno, ename, sal FROM emp WHERE deptno = v_in_deptno;
 14  -- 取出游标所指向的数据(循环)
 15  LOOP
 16    -- 将游标指向的行的对应数据赋给定义的三个变量,并指向下一行
 17    FETCH v_emp_cursor INTO v_empno, v_ename, v_sal;
 18  
 19    -- 指定退出循环的条件(游标指向结果集末尾)
 20    -- ★ 一定要在游标取完所有记录并指向最后一行的下一行(即空行)时判断,为空不输出直接退出。
 21    -- 放在游标下移之前或输出语句之后,都会导致最后一条记录重复输出
 22    EXIT WHEN v_emp_cursor%NOTFOUND;
 23  
 24    -- 输出姓名、月薪。如果薪水小于2000,则加100
 25    IF v_sal < 2000 THEN
 26      UPDATE emp SET sal = sal + 100 WHERE empno = v_empno;
 27    END IF;
 28    dbms_output.put_line('姓名:' || v_ename || '月薪:' || v_sal);
 29  END LOOP;
 30  -- 循环结束。关闭游标
 31  CLOSE v_emp_cursor;
 32  END;
 33  /

Procedure created


SQL> exec pro_test(10);

姓名:CLARK月薪:2450
姓名:KING月薪:5000
姓名:MILLER月薪:1300

PL/SQL procedure successfully completed


SQL> SELECT * FROM emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1400.00               10

14 rows selected

 

        4、LOB类型

                Oracle 中 LOB 类型有时候也有很多类型。

                LOB 类型大多数情况下被用来存储一些二进制文件或者字节字符流。

                一般不是很常用。以后遇到了再补充。