oracle PL/SQL的介绍

时间:2021-04-15 05:05:26

转自:http://blog.sina.com.cn/s/blog_4c302f060101i4o1.html

一 PL/SQL的介绍
1 PL/SQL是什么?
PL/SQL(procedural language/SQL)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变的更加强大。
(1)过程,函数,触发器是PL/SQL编写的
(2)过程,函数,触发器是在Oracle中的
(3)PL/SQL是非常强大的数据库过程化语言
(4)过程,函数可以在java程序中调用
2 学习的必要性
(1)提供应用程序的运行性能
传统的操作数据库的方法是:sql语句写到java程序中,java连接Oracle,传递给数据库,Oracle翻译SQL语句,编译成自己能识别的代码,然后才执行。
PL/SQL:直接在Oracle中写过程,由java调用过程,而此过程已经编译成可执行代码。这样节省了时间。
(2)模块化的设计思想[分页的过程,订单的过程,转账的过程。。。]
(3)减少网络传输量
传统的方法:数据库和java中的SQL语句不在同一机器上,要经过网络传输
过程:直接调用
(4)提高安全性
过程中封装了用户名、密码、表名、字段名等信息。
3 pl/sql的缺点
移植性不好  换数据库了就不能用了
4 pl/sql开发工具
(1)sqlplus开发工具  开始——运行——sqlplusw——用户名、口令
(2)pl/sql developer 开发工具  文件——新建命令窗口
5 例子:
eg1:编写一个存储过程,该过程可以向某表中添加记录——用sqlplus开发工具
(1)创建一张简单表 :create table mytest(name varchar2(30),passwd varchar2(30));
(2)创建过程:
create or replace procedure sp_pro1 is --replace:代表如果原来有sp_pro1将被替换
begin--执行部分
insert into mytest values('李叶','m123');
end;
回车 / 回车
(3)如何查看错误信息
如果编译有错,则可以用show error显示错误。如果没错,则提示过程已创建。
(4)如何调用该过程
①第一种方式:exec 过程名(参数值1,参数值2。。。);
②第二种方式:call 过程名(参数值1,参数值2。。。);
eg2:编写一个存储过程,该过程可以删除某表记录——用pl/sql developer开发工具
create or replace procedure sp_pro2 is 
begin
delete from mytest where name='韩顺平';
end;
/
exec sp_pro2;
 
二 PL/SQL的基础
    开发人员使用pl/sql编写应用程序模块时,不仅需要掌握sql语句的编写方法,还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句,从而可以编写非常有用的功能模块。比如:分页存储过程模块、订单处理存储过程模块、转账存储过程模块。。而且如果使用pl/sql编程,我们可以轻松地完成非常复杂的查询要求。
1 pl/sql可以做什么?
(1)简单分类
块(编程的基础单元) 过程(存储过程)、函数、触发器和包
(2)编写规范
①注释
单行注释 --
多行注释
②表示符号的命名规范
1)当定义变量时,建议用v_作为前缀v_sal
2)当定义常量时,建议用c_作为前缀c_rate
3)当定义游标时,建议用_cursor作为后缀emp_cursor
4)当定义例外时,建议用e_作为前缀e_error
2 pl/sql块介绍
(1)介绍
    块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能,可能只需要编写一个pl/sql块;但是如果要想完成复杂的功能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。
(2)块结构示意图
pl/sql块由三个部分构成:定义部分、执行部分、例外处理部分。
declare
begin
exception
end;
块结构和java程序的比较
java程序结构:
public static void main(String[] args)
{
int a=1;//定义部分
try{
a++;//执行部分
}
catch(Exception e){
//捕获异常
}
}
 
3 例子 
eg1 只包括执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
    dbms_output.put_line('hello');
end;
相关说明:dbms_output是Oracle所提供的包(类似java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程。
 
eg2  包括定义部分和执行部分
declare
    v_ename varchar2(5); --定义字符串变量
    v_sal number(7,2);
begin
    select ename,sal into v_ename,v_sal from emp where empno=&no;
    dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal);
end;
相关说明:(1) select ename into v_ename from... 把查出的ename赋值给v_ename
 (2)& 表示要接收从控制台输入的变量
 (3)|| 字符串连接符
 
eg3 包含定义部分、执行部分和例外处理部分
    为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能的错误进行处理,这个很有必要:
① 比如在eg2中,如果输入了不存在的雇员号,应当做例外处理
②有时出现异常,希望用另外的逻辑处理
相关说明:Oracle事先预定义了一些例外,no_data_found就是找不到数据的例外。
declare
    v_ename varchar2(5);--定义字符串变量
    v_sal number(7,2);
begin
    select ename,sal into v_ename,v_sal from emp where empno=&no;
    dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal);
--异常处理
exception
when no_data_found then 
dbms_output.put_line('朋友你的编号输入有误!');
end;
 
4 过程
    过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out),通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
 
5 实例
①请考虑编写一个过程,可以输入雇员名,新工资 可修改雇员的工资
create procedure sp_prop3(sp_Name varhcar2,newSal number) is
begin
--执行部分 根据用户名修改工资
update emp set sal=newSal where ename=sp_Name;
end;
② 如何调用过程有两种方法
exec sp_pro3('SCOTT',4768);
或者call。。。
③ 如何在java中调用一个存储过程,C C++程序都可以调用
启动eclipse——file new java project——project命名——new package 命名 com.sp——new class 命名 TestOraclePro
package com.sp;
import java.sql.*;
public class TestOraclePro{
public static void main(String[] args)
{
try{
//1.加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
//2. 得到连接
Connection ct=DriverManager.getConnection("jdbc:Oracle:thin:@127.0.0.1","SCOTT","m123");
//3 创建CallableStatement 
CallableStatement cs=ct.prepareCall("{call sp_pro3(?,?)}");
//4 给?赋值
cs.setString(1,"SMITH");
cs.setInt(2,10);
//5 执行
cs.execute();
//关闭
cs.close();
ct.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}
 
加入驱动 引入一个jar包  property——jave build path——libraries——add external jars
问题:如何使用过程返回值??
 
6 函数
函数用于返回特定的数据,当建立函数时,在函数的头部必须包含return子句,而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数,
实例:
--输入雇员的姓名,返回该雇员的年薪
create function sp_fun2(spName varchar2) return number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
调用
var abc number;
call sp_fun2('SCOTT') into:abc;
 
7 包
包用于逻辑上组合过程和函数,它是由包规范和包体组成。
① 我们可以使用create package命令来创建包:
实例
--创建一个包sp_package
--声明该包有一个过程
--声明该包有一个函数
create package sp_package is
    procedure update_sal(name varchar2,newsal number);
    function annual_income(name varchar2) return number;
end;
/ 执行 --包被创建
包的规范只包含了过程和函数的声明,但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
② 建立包体可以使用create package body命令
 
--给包 sp_package 实现包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
undate emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return number is annual_sal number;
begin 
select sal*12+nvl(comm,0)*12 into annual_sal from emp where ename=name;
return annual_sal;
end; 
end;
/ 包体被创建 
③ 如何调用包的过程或函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其它方案的包,还需要在包名前加方案名
如:call sp_package.update_sal('SCOTT',1500);
 
8 触发器
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
 
9 定义并使用变量
在编写pl/sql程序时,可以定义变量和常量;在pl/sql程序中包括:
①标量类型(scalar)
②复合类型(composite)
③参照类型(reference)
④lob(large Object)
(1)标量(scalar)——常用类型
在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下:
identifier [constant] datatype [not null] [:=|default expr]
identifier:名称
constant:指定常量。需要指定它的初始值,且其值是不能改变的
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或是常量指定初始值
default: 用于指定初始值
expr:指定初始值的pl/sql表达式,可以是文本值、其它变量、函数等
例子:
①定义一个变长字符串
v_ename varchar2(10);
②定义一个小数 范围-9999.99~9999.99
v_sal number(6,2);
③定义一个小数并给一个初始值5.4
v_sal2 number(6,2):=5.4
④定义一个日期类型的数据
v_hiredate date;
⑤定义一个布尔变量,不能为空,初始值为false
v_valid boolean not null default false;
 
使用标量
    在定义好标量之后,就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言,需要在等号前加冒号(:=)
例子
下面以输入员工号,显示员工姓名、工资、个人所得税(税率为0.03)为例,说明变量的使用,看看如何编写。
declare
c_taxrate constant number(3,2):=0.03;
v_name varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2)
begin
select ename,sal into v_name,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=sal*c_taxrate;
--输出
dbms_optput.put_line('姓名是:'||v_name||'工资:'||v_sal||'交税:'||v_tax_sal);
(2)标量(scalar)——使用%type类型
对于上面的pl/sql块有一个问题:
就是如果员工的姓名超过了5个字符的话,就会有错误,为了降低pl/sql程序的维护工作量,可以使用%type属性定义变量,这样它就会按照数据库列来确定你定义的变量的类型和长度。
标识符名 表名.列名%type;   v_name emp.ename%type;  v_name的类型和emp.ename的类型长度一致
(3)复合变量(composite)——介绍
①pl/sql记录
    类似于高级语言中的结构体,需要注意的是当引用pl/sql记录成员时,必须要加记录变量作为前缀(记录变量.记录成员)如下:
declare
--定义一个pl/sql记录类型,名字emp_record_type,类型包含3个数据分别是name,salary,title。
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--定义了一个变量,该变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name||'工资是:'||sp_record.salary);
 
②pl/sql表
   相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下表不能为负数,而pl/sql是可以为负数的,并且表元素的下表没有限制。实例如下
declare
--定义了一个pl/sql表类型,sp_table_type,该类型用于存放emp.ename%type
type sp_table_type is table of emp.ename%type
index by binary_integer;--下表为整数,可以为负
sp_table sp_table_type;--定义了一个sp_table_type类型的变量sp_table
begin  
select ename into sp_table(0) from emp where empno=7788;--如果把where去掉,则应该使用参照变量
dbms_output.put_line('员工名:'||sp_table(0)); 
end;
③嵌套表
④varray
 
(4)参照变量
    参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
参照变量——ref cursor游标变量
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open时)需要指定select语句,这样一个游标就和一个select语句结合了。
实例:
①请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资
declare
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义两个变量
v_ename emp.name%type;
v_sal emp.sal%type;
--执行
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no ;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor not found;
dbms_output.put_line('名字:'||v_ename||' 工资:'||v_sal);
end loop;
end;
②在①的基础上,如果某个员工的工资低于200元,就增加100元
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义两个变量
v_ename emp.name%type;
v_sal emp.sal%type;
--执行
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no ;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断工资是否小于200 决定是否更新
if v_sal<200 then
update emp set sal=sal+100 ;
--判断是否test_cursor为空
exit when test_cursor not found;
dbms_output.put_line('名字:'||v_ename||' 工资:'||v_sal);
end loop;
end;