PLSQL语言的运用.docx
《PLSQL语言的运用.docx》由会员分享，可在线阅读，更多相关《PLSQL语言的运用.docx（23页珍藏版）》请在冰豆网上搜索。
PLSQL语言的运用
ØPL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写
ØPL/SQL是对SQL语言存储过程语言的扩展
PL/SQL的优点
Ø有利于客户/服务器环境应用的运行PL/SQL是对SQL语言存储过程语言的扩展
Ø适合于客户环境
ØPL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成
PL/SQL块结构和组成元素
PL/SQL块
PL/SQL程序由三个块组成，即声明部分、执行部分、
异常处理部分。
PL/SQL块结构和组成元素
PL/SQL块的结构如下：
DECLARE
/*声明部分:
在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/
BEGIN
/*执行部分:
过程及SQL语句,即程序的主要部分*/
EXCEPTION
/*执行异常部分:
错误处理*/
END;
其中执行部分是必须的。
PL/SQL块可以分成三类：
Ø匿名块：
动态构造，只能执行一次
Ø子程序：
存储在数据库中的存储过程、函数及包等。
当在数据库上建立好后可以在其它程序中调用它们
Ø触发器：
当数据库发生操作时，会触发一些事件，从而自动执行相应的程序
PL/SQL中的标识符
PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同
Ø标识符名不能超过30字符
Ø第一个字符必须为字母
Ø不分大小写
Ø不能是SQL保留字
PL/SQL中的变量类型
类型
说明
CHAR
定长字符串
VARCHAR2
可变字符串
BINARY_INTEGER
带符号整数,为整数计算优化性能
NUMBER（p,s）
数值
LONG
变长字符串
DATE
日期
BOOLEAN
布尔
ROWID
存放数据库行号
PL/SQL中的标识符
一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果
例：
DECLARE
Enamevarchar2（20）:
=’KING’;
BEGIN
DELETEFROMempWHEREename=ename;
END;
PL/SQL中声明变量
在语句块的声明部分对变量声明，声明一个变量的语法是：
variable_name[constant]type[notnull][:
=value]
其中：
variable_name为变量名
type为类型
value为变量的初值
PL/SQL中的标识符
下面是建议使用的变量命名方法
标识符
命名原则
例
程序变量
V_name
V_id
程序常量
C_constant
C_student_name
游标变量
Name_cursor
Emp_cursor
异常标识
E_name
E_too_many_rows
表类型
Name_table_type
Emp_record_type
表
Name_table
Emp
记录类型
Name_type
Emp_record
参数
P_name
P_id
PL/SQL中声明变量
例：
DECLARE
V_DescriptionVARCHAR2（50）;
V_NumberNUMBER:
=45;
V_CounterBINARY_INTEGER:
=0;
注意：
如果变量在声明时使用了NOTNULL选项则必须为变量指定初值。
如果变量在声明时使用了CONSTANT选项则必须为变量指定初值，并且该初值不能被改变。
PL/SQL中的复合类型
记录类型：
记录类型是把逻辑相关的数据作为一个单元存储起来
定义记录类型语法如下：
TYPErecord_typeISRECORD（
Field1type1[NOTNULL][:
=exp1],
Field2type2[NOTNULL][:
=exp2],
......
Fieldntypen[NOTNULL][:
=expn]）;
PL/SQL中的复合类型
%TYPE:
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE
例：
DECLARE
--用%TYPE类型定义与表相配的字段
TYPEt_RecordISRECORD（
T_noemp.empno%TYPE,
T_nameemp.ename%TYPE,
T_salemp.sal%TYPE）;
--声明接收数据的变量
v_empt_Record;
PL/SQL中的复合类型
%ROWTYPE:
返回一个记录类型,其数据类型和数据库表的数据结构相一致。
例：
DECLARE
v_empnoemp.empno%TYPE:
=&no;
recemp%ROWTYPE;
BEGIN
SELECT*INTOrecFROMempWHEREempno=v_empno;
DBMS_OUTPUT.PUT_LINE（'姓名:
'||rec.ename||'工资:
'||rec.sal||'工作时间:
'||rec.hiredate）;
END;
PL/SQL表
PL/SQL表,或者称为索引表（index-table），是可以在PL/SQL程序中引用、能够模仿数组的非永久表。
用户可以定义一个表类型，然后声明这种类型的变量。
接下来，用户就可以将记录添加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方法引用他们
表包括两个基本成分：
数据处理类型为BINARY_INTEGER主键
标量或记录数据类型的列
PL/SQL表
定义一个表：
TYPEtype_nameISTABLEOF
{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE
[INDEXBYBINARY_INTEGER];
例：
DECLARE
TYPEdept_table_typeISTABLEOF
dept%ROWTYPEINDEXBYBINARY_INTEGER;
my_dname_tabledept_table_type;
PL/SQL表
注意：
Index-by表中的元素不一定要按任何特定的顺序排序
用于Index-by表的关键字没有必要是顺序的
关键字唯一允许的类型是BINARY_INTERGER
引用表元素
例：
DECLARE
typedept_table_typeistableof
dept%ROWTYPEindexbybinary_integer;
my_dname_tabledept_table_type;
intnumber（3）;
BEGIN
int:
=-10;
my_dname_table（int）.dname:
='searching';
dbms_output.put_line（my_dname_table（int）.dname）;
int:
=0;
my_dname_table（int）.dname:
='market';
dbms_output.put_line（my_dname_table（int）.dname）;
int:
=10;
my_dname_table（int）.dname:
='finance';
dbms_output.put_line（my_dname_table（int）.dname）;
END;
PL/SQL中的可变数组
一般格式为：
TYPEtype_nameISVARRAY（maximum_size）OFelement_type
其中：
type_name是新可变长数组类型的类型名。
maximum_size是一个指定可变数组中元素最大数目的整数。
element_type是一个PL/SQL标量、记录或对象类型。
一般格式为：
DECLARE
typenumberlistisvarray（10）ofnumber（5）;
typerecordlistisvarray（5）ofdept%rowtype;
引用数组元素
例：
DECLARE
typestringsisvarray（5）ofvarchar2（10）;
--Declareavarraywithfourelement
v_liststrings:
=strings（'scott','peter','smith','tom'）;
intnumber;
BEGIN
int:
=1;dbms_output.put_line（v_list（int））;
v_list（int）:
='urman';dbms_output.put_line（v_list（int））;
int:
=3;
dbms_output.put_line（v_list（int））;
v_list（int）:
='jackson';
dbms_output.put_line（v_list（int））;
v_list.extend;
v_list（4）:
='oracle';
dbms_output.put_line（v_list（4））;
END;
表和数组属性
属性
返回类型
描述
有效范围
exists
boolean
指定元素在集合中是否存在
表、可变数组
count
number
返回集合中元素的数目
表、可变数组
limit
number
返回集合中最大元素数
可变数组
First&last
Binary_integer
返回第一个（最后一个）元素的索引
表、可变数组
Next&prior
Binary_integer
返回当前元素的下一个（前一个）元素的索引
表、可变数组
extend
N/A
向集合中添加元素
可变数组
trim
N/A
从集合的最后删除元素
可变数组
delete
N/A
从集合中删除指定元素
表
DECLARE
typestrings_tableistableofvarchar2（10）indexbybinary_integer;
stringsstrings_table;
intnumber;
BEGIN
int:
=1;
strings（int）:
='element1';
ifstrings.exists（int）then
dbms_output.put_line（strings（int））;
else
dbms_output.put_line（'nodata!
'）;
return;
endif;
strings
（2）:
='element2';
strings（3）:
='element3';
strings（4）:
='element4';
strings（5）:
='element5';
strings（6）:
='element6';
dbms_output.put_line（strings.count）;
dbms_output.put_line（strings.first）;
dbms_output.put_line（strings.last）;
dbms_output.put_line（strings.next
（2））;
dbms_output.put_line（strings.prior（4））;
strings.delete（1,3）;
--dbms_output.put_line（strings
（2））;
END;
PL/SQL运算符和表达式
运算符
意义
=
等于
<>,!
=,~=,^=
不等于
<
小于
>
大于
<=
小于等于
>=
大于等于
运算符
意义
+
加号
-
减号
*
乘号
/
除号
:
=
赋值号
||
连接符
运算符
意义
ISNULL
空值
BETWEENAND
介于两者之间
IN
在一个值列表之中
AND
并
OR
或
NOT
否
PL/SQL中的变量赋值
在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：
variable:
=expression;
variable是一个PL/SQL变量,expression是一个PL/SQL表达式.
BOOLEAN型变量赋值：
布尔值只有TRUE,FALSE及NULL三个值，其中空值在参加算数运算时，结果仍为空值。
PL/SQL中的变量赋值
DECLARE
doneBOOLEAN;
/*thefollowingstatementsarelegal:
*/
BEGIN
done:
=FALSE;
WHILENOTdoneLOOP
Null;
ENDLOOP;
END;
PL/SQL中的变量作用范围及可见性
PL/SQL的变量作用范围特点是：
Ø变量的作用范围是在所引用的程序单元（块、子程序、包）内。
即从声明变量开始到该块的结束
Ø一个变量（标识）只能在所引用的块内是可见的
Ø当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）
Ø在子块中重新定义该变量后，它的作用仅在该块内
PL/SQL中的变量作用范围及可见性
例：
DECLARE
v_NumberNUMBER（3,2）;
BEGIN
DECLARE
v_CharacterVARCHAR2（10）;
BEGIN
…………
END;
END;
PL/SQL中的注释
在PL/SQL里，可以使用两种符号来写注释：
ØPL/SQL允许用–来写注释，它的作用范围是只能在一行有效。
例：
V_SalNUMBER（12,2）;--工资变量
Ø使用/**/来加一行或多行注释。
例：
/***********************************************/
/*文件名：
department_salary.sql*/
/***********************************************/
DML语句的返回值
RETURNING列值1，列值2，……
INTO变量1，变量2，……
插入语句返回值
DECLARE
v_deptnoNUMBER;
v_dnameVARCHAR2（100）;
BEGIN
INSERTINTOdepartments
VALUES
（122,'HSW',206,1500）
RETURNINGdepartment_id,department_nameINTOv_deptno,v_dname;
dbms_output.put_line（v_deptno）;
dbms_output.put_line（v_dname）;
END;
更新语句返回值
DECLARE
v_deptnoNUMBER;
v_dnameVARCHAR2（100）;
BEGIN
UPDATEdepartmentsSETdepartment_name='GE'
WHEREdepartment_id=20
RETURNINGdepartment_id,department_nameINTOv_deptno,v_dname;
dbms_output.put_line（v_deptno）;
dbms_output.put_line（v_dname）;
END;
删除语句返回值
DECLARE
v_deptnoNUMBER;
v_dnameVARCHAR2（100）;
BEGIN
DELETEFROMdepartments
WHEREdepartment_id=190
RETURNINGdepartment_id,department_nameINTOv_deptno,v_dname;
dbms_output.put_line（v_deptno）;
dbms_output.put_line（v_dname）;
END;
DML返回多行值
DECLARE
TYPEemp_record_typeISRECORD（
v_last_nameemployees.last_name%TYPE,
v_salaryemployees.salary%TYPE）;
TYPEtable_typeISTABLEOFemp_record_type;
emp_tabletable_type;
BEGIN
UPDATEemployees
SETsalary=salary+1
WHEREsalary<=5000
RETURNINGlast_name,salaryBULKCOLLECTINTOemp_table;
FORiIN1..emp_table.countLOOP
dbms_output.put_line（emp_table（i）.v_last_name）;
dbms_output.put_line（emp_table（i）.v_salary）;
dbms_output.put_line（'------------------------------'）;
ENDLOOP;
END;
1基本输出语句
BEGIN
dbms_output.put_line（'hello,world'）;
dbms_output.put_line（127）;
dbms_output.put_line（sysdate）;系统当前时间
--dbms_output.put_line（ture）;不能直接输出true/false
END;
2变量的使用
SELECT列1，列2……..INTO变量1、变量2
DECLARE
inumber（4）:
=250;
jnumber（6）;
cvarchar（100）:
='hello,world';
dDATE:
=SYSDATE;
--eboolean:
=true;
BEGIN
j:
=1000;
dbms_output.put_line（'i='||i）;
dbms_output.put_line（'j='||j）;
dbms_output.put_line（'c='||c）;
dbms_output.put_line（'d='||to_char（d,'YYYY-MM-DD'））;
--dbms_output.put_line（'e='||e）;
END;
CONSTANT常量的使用
declare//只做声明
c_aCONSTANTNUMBER（4）:
=100;
v_bnumber（4）notnull:
=101;使用notnull修饰变量时，变量必须初始化；
begin
--c_a:
=101;常量不能边
dbms_output.put_line（c_a）;
dbms_output.put_line（v_b）;
null；不能为空
end;
更新100员工，工资增加1并在控制台打印输出
declare
v_empidbinary_integer:
=100;
v_moneynumber（10）:
=1;
begin
updateemployeessetsalary=salary+v_moneywhereemployee_id=v_empid;
commit;
dbms_output.put_line（v_empid||','||v_money）;
end;
查询某个员工的编号的姓名，工资，入职日期，部门编号
declare
v_empidbinary_integer:
=101;
v_namevarchar（50）;
v_salarynumber（8,2）;
v_hiredateDATE;
v_deptidbinary_integer;
begin
selectemployee_id,last_name,salary,hire_date,department_id
intov_empid,v_name,v_salary,v_hiredate,v_deptid
fromemployees
whereemployee_id=v_empid;
dbms_output.put_line（v_empid||','||v_name||','||v_salary||','||to_char（v_hiredate,'yyyY/mm/DD'）||','||v_deptid）;
end;
记录类型
declare
typeemp_record_typeisrecord（
v_empidbinary_integer:
=101,
v_namevarchar（50）,
v_salarynumber（8,2）,
v_hiredateDATE,
v_deptidbinary_integer
）;
eemp_record_type;
begin
selectemployee_id,last_name,salary,hire_date,department_id
intoe
fromemployees
whereemployee_id=e.v_empid;
dbms_output.put_line（e.v_empid||','||e.v_name||','||e.v_salary||','||to_char（e.v_hiredate,'yyyY/mm/DD'）||','||e.v_deptid）;
end;
参照引用类型
未知的变量类型可以参照已知的变量类型，或者参照数据库表中的列的类型语法%type
declare
inumber（4）;
ji%type:
=100;
ke
升级会员 