PL/SQL基础-->1.PL/SQL

PL/SQL中PL是Procedural Language的缩写，表示过程化编程语言。PL/SQL是Oracle对标准数据库语言的扩展，是一种高性能的基于事务处理的语言，能够运行在任何Oracle环境中，支持所有数据处理命令，支持所有SQL数据类型和所有SQL函数，同时支持所有Oracle对象类型。PL/SQL块可以被命名和存储在Oracle服务器中，同时也能被其他的PL/SQL程序或者SQL命令调用，任何客户/服务器工具都能够访问PL/SQL程序，所以它具有很好的可重用性。

1 PL/SQL

 本节介绍PL/SQL块的基本结构、声明与使用常量和变量的方法、常量与变量的各种数据类型，以及PL/SQL的程序注解。

1.1 PL/SQL程序块的基本结构

PL/SQL程序块的基本结构主要包括declare部分、begin...end部分和exception部分，具体的代码结构如下：

1   [DECLARE declaration_statements ;]
2   BEGIN
3   　　executable_statements ;
4   [EXCEPTION exception_handing_statements ;]
5   END ;
6   /
  • DECLARE declaration_statements：用于声明变量。PL/SQL程序块中需要使用的变量一般都在DECLARE中声明。
  • BEGIN...END：PL/SQL程序块的主体部分。其中，还可以嵌套其他的PL/SQL程序块。
  • executable_statements：PL/SQL程序块中的可执行语句。
  • EXCEPTION exception_handing_statements：用于处理PL/SQL程序块运行过程中可能出现的任何可执行的错误。
  • /：PL/SQL程序块需要使用 正斜杠(/)结尾，才能被执行。

Note：PL/SQL 程序块中的语句都要使用分号(;)结尾，也正是因为这个原因，分号不会被Oracle的解析器解释为PL/SQL程序块的可执行部分，因此需要使用 正斜杠(/) 来结束PL/SQL程序块。

1.2 常量和变量

PL/SQL程序块中经常使用 常量和变量。常量用来声明一个不可变更的值；变量可以在程序中根据需要来存储不同的值。

定义常量和变量时，它们的 名称 必须符合Oracle标示符的规定：

  • 名称必须以字母开头；
  • 名称长度不能超过30个字符；
  • 名称中不能包含中折号(-)或者空格；
  • 不能是SQL保留字，也就是SQL中的一些缺省的关键字。

1.2.1 PL/SQL 数据类型

表中的类型建议都是大写字母，本人个人的坏习惯，喜欢使用小写字母.

类型 说明
boolean 布尔型. 取值为true|false|null
binary_integer 带符号的整数. 取值范围是-2^31~2^31
natural binary_integer的子类型, 表示非负整数
naturaln binary_integer的子类型, 表示不为null的非负整数 
positive  binary_integer的子类型, 表示正整数
positiven  binary_integer的子类型, 表示不为null的正整数 
signtype binary_integer的子类型, 取值为-1|0|1
pls_integer 带符号的整数. 取值范围是-2^31~2^31. 它与binary_integer类似,都比number类型表示的范围小,因此占用更少的内存. 但是使用pls_integer值时,如果算法发生溢出,会触发异常.
simple_integer Oracle Database 11g新增的类型. 它是binary_integer的子类型,它的取值范围与binary_integer相同,但不能存储null值.  使用simple_integer值时,如果算法发生溢出,不会触发异常,只会简单地截断结果.
string 与varchar2相同 
record  一组其他类型的组合 
ref cursor  指向一个行集的指针 

 

 

 

1.2.2 常量

声明常量时必须使用constant关键字, 并且必须在声明时就为该常量赋值, 并且在程序其他部分也不能修改该常量的值.

语法：

1 constant_name CONSTANT data_type { := | DEFAULT } value ;

说明：

  • constant_name：常量名
  • data_type：常量的数据类型
  • := ｜ default：这里的:=为赋值符号. 在初始化常量或变量时还可以使用default关键字代替
  • value：表示为常量赋的值

1.2.3 变量

变量的声明不需要使用constant关键字, 并且可以不用为它赋初始值, 因为它的值可以在程序其他部分被修改.

语法：

1 variable_name data_type [ [ NOT NULL] { :=  | DEFALUT } value ];

说明：

  • variable_name：变量名
  • NOT NULL ：表示可以对变量定义非空约束。如果使用了这个选项，那么就必须为变量赋予非空的初始值，并且也不允许在程序其他部分将其值修改为NULL

 1.2.4 PL/SQL中使用 常量和变量 实例

 1 SET SERVEROUTPUT ON
 2 DECLARE
 3     emp_number CONSTANT NUMBER(4) := 7900 ;
 4     emp_name VARCHAR2(10) ;
 5     emp_job VARCHAR2(9) ;
 6     emp_sal NUMBER(7, 2) ;
 7 BEGIN
 8     SELECT ename, job, sal
 9     INTO emp_name, emp_job, emp_sal
10     FROM emp WHERE empno=emp_number ;
11     DBMS_OUTPUT.PUT_LINE('员工编号: ' || emp_number) ;
12     DBMS_OUTPUT.PUT_LINE('员工姓名: ' || emp_name) ;
13     DBMS_OUTPUT.PUT_LINE('员工职位: ' || emp_job) ;
14     DBMS_OUTPUT.PUT_LINE('员工工资: ' || emp_sal) ;
15 END ;
16 /
17 -----------------下面是结果---------------------
18 PL/SQL 过程已成功执行
19 
20 员工编号: 7900
21 员工姓名: JACY
22 员工职位: CLERK
23 员工工资: 9500

说明：

  • 使用constant关键字定义了emp_number常量，并赋值为7900
  • 定义了3个变量：emp_name, emp_job, emp_sal
  • 使用select...into语句为emp_name, emp_job, emp_sal 赋于empno=7900员工的值
  • 最后调用DBMS_OUTPUT.PUT_LINE系统过程输出常量和变量的值
  • 要想在SQL*Plus中显示DBMS_OUTPUT.PUT_LINE过程的输出内容，需要使用set serveroutput on命令打开服务器输出
  • Oracle中可以使用双竖线(||)来连接两个字符串

1.3 %TYPE类型 和 %ROWTYPE类型

在PL/SQL中，除了可以使用SQL数据类型，以及PL/SQL中特定的数据类型以外，还可以在生命变量时使用%TYPE和%ROWTYPE类型。
1.3.1 %TYPE类型
%TYPE类型用于隐式地将变量的数据类型指定为对应的数据类型。针对表中的某一列。
使用%TYPE定义变量的形式如下：

1 variable_name table_name.column_name%TYPE [ [ NOT NULL ] { := | DEFALUT } value ] ;

1.3.2 %ROWTYPE类型
%ROWTYPE类型定义的变量可以存储表中的一行数据。针对表中的某一行。

1 variable_name table_name%ROWTYPE ;

示例：

 1 SQL>set serveroutput on ;
 2 SQL>declare
 3 emp_number CONSTANT emp.empno%TYPE := 7900 ;
 4 one_emp emp%ROWTYPE ;
 5 begin
 6 select * into one_emp
 7 from emp where empno=emp_number ;
 8 DBMS_OUTPUT.PUT_LINE('查询员工的编号为：' || emp_number) ;
 9 DBMS_OUTPUT.PUT_LINE('该员工的姓名为：' || one_emp.ename) ;
10 DBMS_OUTPUT.PUT_LINE('该员工的职位为：' || one_emp.job) ;
11 DBMS_OUTPUT.PUT_LINE('该员工的工资为：' || one_emp.sal) ;
12 end ;
13 SQL>/
14 ----------------运行结果-------------------
15 查询员工的编号为：7900
16 该员工的姓名为：JAMES
17 该员工的职位为：CLERK
18 该员工的工资为：950

上述示例中使用%ROWTYPE类型定义了一个变量one_emp,其类型为emp表中的一行，向该变量赋予一行数据后，使用one_emp.ename的形式读取该行数据中的ename列值。

1.4 PL/SQL记录类型和表类型 （C语言中的结构体）
PL/SQL 记录类型和标类型都是用户自定义的复合数据类型，其中记录类型可以存储多个字段值，类似于表中的一行；标类型则可以存储多行数据。
1.4.1 记录类型
记录类型与数据库中表的行结构非常相似，使用记录类型定义的变量可以存储由一个或多个字段组成的一行数据。
创建记录类型需要使用TYPE语句，语法如下：

1 TYPE record_name IS RECORD (
2 field_name data_type [ [ NOT NULL ] { := | DEFAULT } value ]
3 [, ...]
4 ) ;

说明：

  • record_name：创建的记录类型名称。
  • IS RECORD：表示创建的是记录类型（区别于后面的表类型）。
  • field_name：记录类型中的字段名。

Note：
一般来说，表是一个实体集，表中的每一行都表示一个实体，因此对记录类型的最好理解方式是将它看成是一个实体，其字段表示该实体的属性。
示例：

 1 SQL>set serveroutput on;
 2 SQL>declare
 3 TYPE emp_type IS RECORD (
 4 myempno NUMBER(4) ;
 5 myename VARCHAR2(10);
 6 myjob VARCHAR2(9);
 7 mysal VARCHAR2(7,2);
 8 );
 9 one_emp emp_type ;
10 begin
11 select empno,ename,job,sal
12 into one_emp
13 from emp where empno=7900 ;
14 DBMS_OUTPUT.PUT_LINE('查询员工的编号为：' || one_emp.myempno) ;
15 DBMS_OUTPUT.PUT_LINE('该员工的姓名为：' || one_emp.myename) ;
16 DBMS_OUTPUT.PUT_LINE('该员工的职位为：' || one_emp.myjob) ;
17 DBMS_OUTPUT.PUT_LINE('该员工的工资为：' || one_emp.mysal) ;
18 end ;
19 SQL>/
20 ----------------运行结果-------------------
21 查询员工的编号为：7900
22 该员工的姓名为：JAMES
23 该员工的职位为：CLERK
24 该员工的工资为：950

1.4.2 表类型
使用记录类型变量只能保存一行数据，这限制了SELECT语句的返回行数，如果SELECT语句返回多行就会报错。而Oracle提供了另外一种自定义类型，也就是表类型，它是对记录类型的扩展，允许处理多行数据，类似于表。
语法：

1 TYPE table_name IS TABLE OF data_type [ NOT NULL ] 
2 INDEX BY BINARY_INTEGER ;

说明：

  • table_name：创建的表类型名称。
  • IS TABLE ：表示创建类型是表类型
  • data_type：可以是任何合法的PL/SQL数据类型。
  • INDEX BY BINARY_INTEGER：指定系统创建一个主键索引，之后用于引用表类型变量中的特定行。

示例：

 1 SQL>set serveroutput on ;
 2 SQL>declare
 3 TYPE my_emp IS TABLE OF emp%ROWTYPE
 4 INDEX BY BINARY_INTEGER ;
 5 new_emp my_emp ;
 6 begin
 7 new_emp(1).empno := 6800 ;
 8 new_emp(1).ename := 'TRACY' ;
 9 new_emp(1).job := 'CLERK';
10 new_emp(1).sal := 2500 ;
11 new_emp(2).empno := 6900 ;
12 new_emp(2).ename := 'LUCY' ;
13 new_emp(2).job := 'MANAGER';
14 new_emp(2).sal := 4000 ;
15 DBMS_OUTPUT.PUT_LINE(new_emp(1).empno || ',' ||
16 new_emp(1).ename || ',' ||
17 new_emp(1).job || ',' ||
18 new_emp(1).sal ) ;
19 DBMS_OUTPUT.PUT_LINE(new_emp(2).empno || ',' ||
20 new_emp(2).ename || ',' ||
21 new_emp(2).job || ',' ||
22 new_emp(2).sal ) ;
23 end ;
24 SQL>/
25 --------------运行结果-----------------    
26 6800,TRACY,CLERK,2500
27 6900,LUCY,MANAGER,4000

从上述示例中可以发现，通过表类型变量存取值时使用的是索引值，如new_emp(1)和new_emp(2)，分别表示该表类型变量new_emp中的第一行数据于第二行数据。
如果要删除表类型变量中的记录，可以使用DELETE方法，如下：

variable_name DELETE [ (index_number) ] ;

其中，variable_name表示变量名，index_number表示索引值，如果不指定索引值，则表示删除变量中的所有记录。
对表类型变量进行操作时，除了可以使用DELETE方法之外，还可以使用如下方法：

  • CONUT:返回表类型变量中的记录数.
  • FIRST:返回表类型变量中的第一行索引.
  • LAST:返回表类型变量中的最后一行索引.
  • NEXT:返回表类型变量中的下一行索引.

1.5 PL/SQL程序注解
-- : 可以添加单行注解.范围是从--符号开始,到该行的行末.
/* ... */ : 添加一行或多行注解。这种方式可以运用在可执行代码中间，系统只将字符对之间的文本内容作为注解。

posted @ 2017-12-03 22:07  樊辰木风  阅读(304)  评论(0)    收藏  举报