在《.Net 程序员学用 Oracle 系列 (5):三大数据类型》一文中详细地讲述了 Oracle 的基本数据类型,文中还提到,除基本数据类型之外,Oracle 还在语法上支持一些非固有数值类型。
事实上,Oracle 在语法上支持的数据类型远不止于此,Oracle 还支持一些复杂而强大的数据类型。如属性类型
和
- %TYPE
,记录类型 RECORD,集合类型 VARRAY 和 TABLE 等。本节将会介绍实用的属性类型和灵活的记录类型。
- %ROWTYPE
属性类型是一种可以直接引用数据库中列的数据类型来描述变量类型的类型。Oracle 提供了两种属性类型,分别是
和
- %TYPE
,下文将逐一说明。
- %ROWTYPE
%TYPE:该属性允许在声明中引用数据库中的列或先前声明的变量的数据类型,而不是硬编码类型名称。在声明常量、变量和参数时,都可以使用
属性作为数据类型说明符。如果引用的类型被更改,则声明也将自动更新,这点有利于后期代码维护。
- %TYPE
示例:
- DECLARE
- v_staff_name t_staff.staff_name%TYPE;
- BEGIN
- SELECT t.staff_name INTO v_staff_name FROM demo.t_staff t WHERE t.staff_id=5;
- DBMS_OUTPUT.PUT_LINE(v_staff_name);
- END;
%ROWTYPE:该属性可以表示数据库中表或游标的行的记录类型。使用
声明的变量可以存储从表中选择或从游标或游标变量获取的整行数据,且变量记录中的字段和每行中的相应列具有相同的名称和数据类型。
- %ROWTYPE
示例:
- DECLARE
- v_staff t_staff%ROWTYPE;
- BEGIN
- SELECT t.* INTO v_staff FROM demo.t_staff t WHERE t.staff_id=5;
- DBMS_OUTPUT.PUT_LINE(v_staff.staff_name);
- END;
想要更多的了解属性类型可参考:《Database PL/SQL User's Guide and Reference: %TYPE Attribute》和《Database PL/SQL User's Guide and Reference: %ROWTYPE Attribute》。
记录类型是由单行多列标量构成的复合结构。可以看做是一种用户自定义的数据类型,提供了将一个或多个标量封装成一个对象进行操作的能力。在使用记录数据类型的变量时,需要在声明部分先定义记录的成员变量,然后在执行部分引用该记录变量本身或其中的成员。但不可以对记录做整体性的比较运算,如判断记录类型的变量是否为 NULL。
示例:
- DECLARE
- TYPE staff_type IS RECORD(
- staff_name VARCHAR2(50),
- gender VARCHAR2(2)
- );
- v_staff staff_type;
- BEGIN
- SELECT t.staff_name,DECODE(t.gender,1,'男',0,'女','两性') INTO v_staff.staff_name,v_staff.gender
- FROM demo.t_staff t WHERE t.staff_id=5;
- DBMS_OUTPUT.PUT_LINE(v_staff.staff_name||'|'||v_staff.gender);
- END;
记录类型和
属性在用途上比较相似,区别在于前者是自定义结构,而后者为表结构,前者比较灵活,而后者比较方便。
- %ROWTYPE
一般计算机编程语言中都有变量的概念,PL/SQL 也不例外,变量用于存储计算结果和表示可变状态,本节将着重介绍在 PL/SQL 中变量如何定义及赋值。另外,PL/SQL 中也有常量,只是极少有人使用,有兴趣的读者可以参考《Oracle Database PL/SQL Language Reference: Constant》。
在 PL/SQL 中定义变量的可选类型非常多,包括 Oracle 中常见的三大类基本数据类型,以及 Oracle 在语法上支持的诸多非固有数据类型。如整形 (INT/INTEGER)、布尔类型(BOOLEAN) 等 Oracle 本身并不支持,却在 PL/SQL 中可用的数据类型。
在 PL/SQL 中定义变量与 C# 中定义变量本质上并无区别,不同的是 PL/SQL 中的变量得集中定义,变量定义区域得用
关键字开头,且每行只能定义一个变量。如果是 SQL*Plus 环境则必须用
- DECLARE
开头。
- VAR[IABLE]
语法:
- variable_name datatype[[NOT NULL] {: =|DEFAULT
- }
- expression];
如果使用了 NOT NULL 则必须给变量赋初始值。另外,在命名变量的时候还需要遵守以下命名规则:
给 PL/SQL 变量赋值的写法与给 C# 字段赋值写法基本一样,既可以在定义变量时就赋给它一个初始值,也可以在使用之前给它赋值,如果不赋值也会有默认值。唯一的区别就是,C# 中类型不同默认值也不同,而 PL/SQL 中所有类型的默认值都一样,都是 NULL。
在 PL/SQL 中还有一点比较怪的就是,可能所有编程语言的赋值操作符都是
,而 PL/SQL 中的赋值操作符却偏偏是
- =
。而且 Oracle 相关的 API 中参数写法也与其它大多数数据库不同。
- :=
示例一(普通 PL/SQL 环境):
- DECLARE
- v1 NUMBER;
- v2 NUMBER(5,2);
- v3 NUMBER := 50.20;
- v4 NUMBER(4) := 1998;
- v5 VARCHAR2(4) DEFAULT 'A';
- v6 DATE NOT NULL := fn_now;
- BEGIN
- v1 := 100;
- v2 := 99.99;
- v5 := 'A5';
- v6 := SYSDATE;
- DBMS_OUTPUT.PUT_LINE(v1||'|'||v2||'|'||v3||'|'||v4||'|'||v5||'|'||v6);
- END;
示例二(SQL*Plus 环境):
- VARIABLE v1 NUMBER
- BEGIN
- :v1 := 12;
- DBMS_OUTPUT.PUT_LINE(:v1);
- END;
- /
和普通编程语言一样,PL/SQL 中也有常见的三大控制结构以及顺序控制语句——GOTO。本节将重点讲述被广泛接受的三大控制结构,至于不受待见的 GOTO 语句,有兴趣的读者可以参考《Oracle Database PL/SQL User's Guide and Reference: Using the GOTO Statement》。
顺序结构是面向过程编程中最基本、最简单、最常用的程序控制结构。顺序结构用于表示若干个依次执行的处理步骤,表现形式就是线性结构,一个方向走下去、不拐弯。使用时只要按照解决问题的顺序写出相应的语句就行,它的执行顺序是自上而下、依次执行。
PL/SQL 中提供了两种选择结构,分别是
结构和
- IF
结构。其中
- CASE
结构有三种变体,
- IF
结构有两种变体。下文将逐一讲述各个选择语句:
- CASE
IF 结构变体一:
语法:
- IF condition THEN
- {...statements to execute when condition is TRUE...}
- END IF;
示例:
- BEGIN
- IF 1>0 THEN
- DBMS_OUTPUT.PUT_LINE('executed');
- END IF;
- END;
IF 结构变体二:
语法:
- IF condition THEN
- {...statements to execute when condition is TRUE...}
- ELSE
- {...statements to execute when condition is FALSE...}
- END IF;
示例:
- BEGIN
- IF 1>2 THEN
- DBMS_OUTPUT.PUT_LINE('The result is true');
- ELSE
- DBMS_OUTPUT.PUT_LINE('The result is false');
- END IF;
- END;
IF 结构变体三:
语法:
- IF condition1 THEN
- {...statements to execute when condition1 is TRUE...}
- ELSIF condition2 THEN
- {...statements to execute when condition2 is TRUE...}
- [ELSE
- {...statements to execute when both condition1 and condition2 are FALSE...}]
- END IF;
示例:
- BEGIN
- IF 1>2 THEN
- DBMS_OUTPUT.PUT_LINE('1>2 branch');
- ELSIF 1<2 THEN
- DBMS_OUTPUT.PUT_LINE('1<2 branch');
- ELSE
- DBMS_OUTPUT.PUT_LINE('1=2 branch');
- END IF;
- END;
注意:IF 结构变体三中有个巨坑,就是 IF 和 ELSE 之间的分支写法,不是 ELSE IF 也不是 ELSEIF 而是 ELSIF。尽管你写成 ELSE IF 编辑器也有智能提示,但当你执行的时候就会报
的错,而且这个错误的描述正常人基本没可能看懂。
- ORA-06550
CASE 结构变体一
示例:
- DECLARE
- v_grade VARCHAR2(1);
- BEGIN
- v_grade:='B';
- CASE v_grade
- WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('甲');
- WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('乙');
- WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('丙');
- ELSE DBMS_OUTPUT.PUT_LINE('丁');
- END CASE;
- END;
CASE 结构变体二
示例:
- DECLARE
- v_score NUMBER(3);
- BEGIN
- v_score:=78;
- CASE
- WHEN v_score>=80 THEN DBMS_OUTPUT.PUT_LINE('优');
- WHEN v_score>=70 THEN DBMS_OUTPUT.PUT_LINE('良');
- WHEN v_score>=60 THEN DBMS_OUTPUT.PUT_LINE('中');
- ELSE DBMS_OUTPUT.PUT_LINE('差');
- END CASE;
- END;
与多分支的 IF 语句相比,CASE 语句更可读、更高效,所以当程序分支较多时,应尽可能的使用 CASE 而不是 IF。CASE 语句的 ELSE 子句是可选的。但如果省略 ELSE 字句,PL/SQL 将为 CASE 语句添加以下隐式的 ELSE 子句:
- ELSE RAISE CASE_NOT_FOUND;
换句话说,如果你省略了 ELSE 子句,且 CASE 语句与 WHEN 子句不匹配,PL/SQL 就会引发预定义的异常
。
- CASE_NOT_FOUND
PL/SQL 中提供了三种循环结构,分别是
、
- LOOP
和
- WHILE LOOP
。另外 PL/SQL 还提供了
- FOR LOOP
语句用于退出当前循环。下文将逐一讲述各个循环语句:
- EXIT
LOOP 循环
语法:
- LOOP
- {...statements...}
- EXIT [ WHEN boolean_condition ];
- END LOOP;
示例一:
- DECLARE
- v_counter BINARY_INTEGER := 0;
- BEGIN
- LOOP
- v_counter := v_counter + 1;
- DBMS_OUTPUT.PUT_LINE(v_counter); -- 输出结果:1、2、3、4、5、6、7、8、9
- IF v_counter >= 9 THEN
- EXIT;
- END IF;
- -- 上面的 IF 语句块还可以由"EXIT WHEN v_counter >= 9;"代替
- END LOOP;
- END;
示例二(嵌套循环):
- DECLARE
- i BINARY_INTEGER := 0;
- j BINARY_INTEGER := 0;
- BEGIN
- LOOP
- i := i + 1;
- j := 0;
- LOOP
- j := j + 1;
- DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
- EXIT WHEN j >= 3;
- END LOOP;
- EXIT WHEN i >= 4;
- END LOOP;
- END;
示例三(标记循环):
- DECLARE
- i BINARY_INTEGER := 0;
- j BINARY_INTEGER := 0;
- BEGIN
- <<outer_loop>>
- LOOP
- i := i + 1;
- j := 0;
- <<inner_loop>>
- LOOP
- j := j + 1;
- DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
- EXIT inner_loop WHEN j >= 3;
- EXIT outer_loop WHEN i >= 4;
- END LOOP inner_loop;
- END LOOP outer_loop;
- END;
WHILE LOOP 循环
语法:
- WHILE condition
- LOOP
- {...statements...}
- END LOOP;
示例:
- DECLARE
- v_score NUMBER(3) := 0;
- BEGIN
- WHILE v_score < 60 LOOP
- v_score := v_score + 10;
- DBMS_OUTPUT.PUT_LINE(v_score); -- 输出结果:10、20、30、40、50、60
- END LOOP;
- DBMS_OUTPUT.PUT_LINE('over');
- END;
FOR LOOP 循环
语法:
- FOR loop_counter IN [REVERSE] lowest_number..highest_number
- LOOP
- {...statements...}
- END LOOP;
示例一(正向循环):
- BEGIN
- FOR i IN 3..7 LOOP
- DBMS_OUTPUT.PUT_LINE(i); -- 输出结果:3、4、5、6、7
- END LOOP;
- END;
示例二(反向循环):
- BEGIN
- FOR i IN REVERSE 3..7 LOOP
- DBMS_OUTPUT.PUT_LINE(i); -- 输出结果:7、6、5、4、3
- END LOOP;
- END;
注意:FOR LOOP 循环中的计数器(变量)可以被读取,但不能被修改。另外,在 LOOP 循环的示例中用到的 EXIT 和循环标记,同样可用于 WHILE LOOP 循环和 FOR LOOP 循环中。
本文主要讲述了 PL/SQL 中的变量和结构两个基本的编程元素,以及属性类型和记录类型。本人觉得对于常年使用 Oracle 的朋友来说,这几个知识点应该是必须要掌握的。
来源: http://www.cnblogs.com/hanzongze/p/Oracle-plsql-1.html