一、PL/SQL中的数据类型
1、字符串类型
在 PL/SQL 中的字符串类型所你所想的一样,是一种常见的数据类型定义,它允许在其中有字母和数字。像名字、代码、描述、序列号等都可以包括在字符串中。
char中存储着固定长度的字符串代码,char最大长度为32767个字节。
语法:char(max_length)
子类型:character
varchar2则存储着长度可变的字符串,varchar2 所允许的最大长度为 32767 个字节。
语法:varchar2(max_length)
子类型:varchar,string
long 也可以存储变长的字符串,最大长度为 32760 个字节,long 是典型的用以存储长文本。
2、数值数据类型
number用以存储在 ORACLE 数据库中的任何类型的数值 。
语法:number(max_length)
使用下边的语法可以指定数据类型的精度:number(precision,scale)
子类型:dec、decimal、double precision、integer、int、numeric、real、smallint、float
pls_integer定义的整数是可以带有符号的,例如:负数。
3、二进制数据类型
二进制数据类型可以以二进制形式来存储数据。这种数据类型包括raw 和 longraw。
4、日期数据类型
date是在 oracle 中可以有效存储的数据类型,如果你将某一列定义为date类型,你就不能指定它的长度,date 数据类型的长度是默认的,oracle日期数据类型:01- OCT-97。
5、逻辑数据类型
逻辑数据类型:true、false 和 NULL。与 date相类似,boolen 在作为列或变量的类型在定义时也不需要参数。
6、rowid
rowid是存在于oracle数据库的每一个表中的预定义列。rowid以二进制格式存储和确定表中的每一列,索引就是使用rowid指向数据的。
二、PL/SQL块结构
PL/SQL 是一种块结构语言,也就是说 PL/SQL 的程序可以分成逻辑块来写。在一个块的内部可以有像数据操作或查询之类的过程。
declare部分包括了定义的变量和其它的对象,如常量和指针。//可选的
procdure部分包括条件语句和 SQL 语句,块可以对它进行控制。 //强制的
exception告诉了 PL/SQL 如何处理指定的错误并按用户的定义进行处理。//可选的
块是 PL/SQL 代码的逻辑单元,包括至少一个 procdure 部分和可以选择的 declare 以及 exception 部分。
PL/SQL块的基本结构:
begin --可选的,意味着块的开始
declare --可选的,变量定义
begin --强制性,是指程序段的开始
exception --可选的,是指除部分的开始
end --强制,是指程序段的结束
end --可选的,是指块的结束
三、注释
PL/SQL单行注释:-- This is a one-line comment. 使用--进行注释。
多行注释:/* This is a multiple-line comment.*/ 使用/**/方式进行注释。
PL/SQL不支持数据字典语言(DDL)命令,用户可以使用 PL/SQL 数据,不能维护数据结构。
四、declare部分
1、变量声明
--声明变量
declare
owner char(10);
tablename char(30);
bytes number(10);
tody date
--初始化变量
declare
customer char(30);
fiscal_year number(2):='97';
2、常量的定义
定义常量的方法与定义变量相同,但是常量的数值是静态的,他们不能改变。在上个例子中 fiscal_year 可能是常量。
3、指针的定义
指针是 PL/SQL 中的另一种类型的变量。指针型变量则指向了查询结果中的某一行数据。在查询的结果有多个时,需要在每个记录之间进行翻阅,利用指针进行返回每行的数据。
declare
cursor employee_cursor is
select * from employees;
4、%TYPE属性
%TYPE 可以返回表中给定列的变量属性。
declare
cursor employee_cursor is
select emp_id,emp_name from employees;
id_num employee.emp_id%TYPE;--id_num和emp_id的数据类型相同
name employee.emp_name%TYPE;--name和emp_name的数据类型相同
5、%ROWTYPE
当定义的变量和指针关联,可以使用%ROWTYPE声明变量和指针所指向的行类型相同。
declare
cursor employee_cursor is
select emp_id,emp_name from employees;
employee_record employee_cursor%ROWTYPE;
--定义变量employee_record和employee_cursor所在行的数据类型相同
6、ROWCOUNT
PL/SQL 中%ROWCOUNT 属性可以保证在特定的 SQL 语句块中的游标行数。
declare
cursor employee_cursor is
select emp_id,emp_name from employees;
records_processed:= employee_cursor%ROWCOUNT;
--records_processed 将会返回 PL/SQL 语句所访问的 employee_cursor 的行数
五、Procdure
1、begin.....end
begin表示过程开始,end表示语句块的结束。
begin
open a cursor;
condition1;
statement1;
condition2;
statement2;
close the cursor;
end
2、指针控制命令
如何对定义过的指针进行访问?
2.1 declare
2.2 open 打开指针
begin
open employee_cursor;
statement1;
statement2;
end
2.3 FETCH 从指针中获取变量的值
declare
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
begin
open employee_cursor;
loop
fetch employee_cursor into employee_record;
end loop;
close employee_cursor;
end
--将一个指针所指的行的数据赋给了一个名字叫employee_record的变量,使用 LOOP 循环来移动指针,块实际没有做任何工作
declare
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
begin
open employee_cursor;
loop
fetch employee_cursor into employee_record;
end loop;
close employee_cursor;
end
--将当前的指针所指的行的对应数据填入变量id_num和name
2.4 close
在结束指针使用时,应当使用close将指针关闭。在关闭指针以后,查询的结果集就不存在了。
3、条件语句
3.1 if....then
--对两种情况分别处理
IF condition1 THEN
statement1;
ELSE
statement2;
END IF;
--判断的条件多余两个
IF condition1 THEN
statement1;
ELSIF condition2 THEN
statement2;
ELSE
statement3;
END IF;
3.2 loops循环
3.3.1 loop
loop本身是一个无限的循环,经常在指针中使用,若终止这种循环,必须指定在什么时候退出。
--当指针处于最后一行时退出
BEGIN
open employee_cursor;
LOOP
FETCH employee_cursor into employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
statement1;
END LOOP;
close employee_cursor;
END;
3.3.2 while-loop
while-loop循环只有满足while条件才能执行特定的语句,不满足的时候,会退出循环。
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
open payment_cursor;
WHILE payment < total_due LOOP
FETCH payment_cursor into cust_id, payment, total_due;
EXIT WHEN payment_cursor%NOTFOUND;
insert into underpay_table
values (cust_id, 'STILL OWES');
END LOOP;
close payment_cursor;
END;
3.3.3 for-loop
当指针所指定的行的数值不满足for循环的内容,将会退出循环。
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
open payment_cursor;
for pay_rec IN payment_cursor LOOP
IF pay_rec.payment < pay_rec.total_due THEN
insert into underpay_table
values (pay_rec.cust_id, 'STILL OWES');
END IF;
END LOOP;
close payment_cursor;
END;
4、EXCEPTION
4.1 激活exception异常
可以使用raise语句进行激活异常。在日常生活中,当数据库出现错误时,是被自动激活的。
BEGIN
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
Statement;
END;
END;
4.2 异常处理
在捕获异常以后,对异常进行处理,需要在错误的状态继续运行或是终止运行。
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
WHEN OTHERS THEN
statement3;
5、将输入返回给用户
PL/SQL 在它的语法部分中并没有提供直接的方式来显示输出,但是它可以让你来用一个对该语句块服务的包,这个包是由 DBMS_OUTPUT 来调用的。
EXCEPTION
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('ERROR: DIVISOR IS ZERO. SEE YOUR DBA.');
五、事务处理
事务控制命令:COMMIT、ROLLBACK、AVEPOINT。这些命令可以让程序员在在事务向数据库中进行写操作时加以控制。PL/SQL 可以自动地执行事务控制命令来代替对大型事务的不断监控。
六、存储过程、包和触发机制
使用 PL/SQL,可以创建存储对象来代替日复一日的输入单调和枯燥的代码。存储过程是一些可以执行一些特定类型的存储工作的代码块,相关的过程可以组合和存储在一起。这称为包。触发机制是一种在其它的事务中使用的数据库对象。
存储过程:
PROCEDURE procedure_name IS
variable1 datatype;
...
BEGIN
statement1;
...
EXCEPTION
when ...
END procedure_name;
包:
CREATE PACKAGE package_name AS
PROCEDURE procedure1 (global_variable1 datatype, ...);
PROCEDURE procedure2 (global_variable1 datatype, ...);
END package_name;
CREATE PACKAGE BODY package_name AS
PROCEDURE procedure1 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure1;
PROCEDURE procedure2 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure2;
END package_name;
触发机制:
CREATE TRIGGER trigger_name
AFTER UPDATE OF column ON table_name
FOR EACH ROW
BEGIN
statement1;
...
END;