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 类型大多数情况下被用来存储一些二进制文件或者字节字符流。
一般不是很常用。以后遇到了再补充。