一、概述
pl/sql (procedural lanaguage/sql)是 oracle 在标准 sql 上的扩展 。不仅允许嵌入sql 语言,还可以定义变量和常量,允许使用条件语句和循环语句，允许使用例外处理错误。
-- 可以用来编写过程,函数,和触发器
-- 上述对象是放在数据库中的 //数据库端
-- 过程和函数可以在java程序中调用 ,触发器只能被触发,不能被调用
优点:
1.提高程序性能
2.模块化的程序设计思想
3.减少网络传输流量
4.安全性高
5.能处理较复杂的sql操作
缺点:
1.移植性不好
2.可维护性差
3.不好调试
//例子 创建存储过程
create procedure sp_adduser is //可以写成 create or replace (如果存在就替换 )
begin
insert into userinfo(userName) values ('zhangsan');
end;
如何调用?
exec 存储过程名 -> exec sp_adduser
二、pl-sql 的简单分类和编写规范
简单分类
1.过程(存诸过程)块(编程的基本单位)
2.函数
3.触发器
4.包
变量编写规范
注释 -- 或 /* */
变量 v_ // 比如 v_sal
常量 c_ // 比如 c_rate
游标 _cursor 为后缀 //比如 emp_cursor
例外 e_ //比如 e_xxxerror
块
块(block) 是pl/sql 的基本程序单元,编写pl/sql程序实际上就是编pl/sql块. 简单的功能可能只需一个块,但是如果复杂,可能在一个块中嵌套其他块。
块由三部分组成 定义部,执行部分,例外部分
如下
declear //可选,比如定义常量,变量,游标,复杂数据类型等
begin
exception //可选
end;
//例一 只包扩执行部分的pl/sql块
set serveroutput on //打开输出选项,如果是off,则不会输出,默认是off
begin
dbms_output.put_line('嘻嘻');
end;
//说明 dbms_output 是 oracle 提供的包(类似java中的类),该包中有一个过程叫 put_line
//例二 包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(50) ; --定义字符串型变量,这个变量的长度要够
begin
select ename into v_ename from emp where empno =&no; -- // &no这种写法是让用户可以手动输入值
dbms_output.put_line('员工姓名'||v_ename); - -// ||是连接字符
end;
//例三 将上例改为,连薪水也打印出来,包含例外处理
declare
v_ename varchar2(150) ;
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;
说明 : 输入了不存在的员工号,会出错,要进行例外处理
oracle 预先定义了一些例外
NO_DATA_FOUND 就是找不到数据的时候出现的例外
预定义例外: INVALID_CURSOR,ZERO_DIVIDE,VALUE_ERROR,INVALID_NUMBER .... //等共22种,都对应一个errorcod
过程
过程用于执行特定的操作,当建立过程的时候,即可指定输入参数(in) ,也可以指定输出参数 (out)
==通过输入参数,可以将数据传给过程的执行部分
==通过输出参数,可以将执行部分的搂据传给应用环境 (存储过程的调用者)
可以用 create procedure 创建过程
例子
编写过程,可以输入员工名,新工资,可以修改员工的工资
调用过程 //exec 过程名
在java程序中调用一个过程
create procedure sp_01(p_name varchar2, p_sal number) is --//这里不用指定长度
begin
update emp set sal=p_sal where ename=p_name;
end;
执行 exec sp_01('SCOTT',9090);
//在java中调用
public static void test() {
Connection conn = null;
CallableStatement stm = null;
try {
conn = DBUtil.getConn();
stm = conn.prepareCall("{call sp_01(?,?)}"); 如果是sa 登录 要写成 {scott.call sp_01(?,?)}
stm.setString(1, "SCOTT");
stm.setInt(2, 10900);
stm.execute();
System.out.println("---操作成功----");
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.close(null, stm, conn);
}
}
附: 驱动类名: oracle.jdbc.driver.OracleDriver ,连接字串 jdbc:oracle:thin:@localhost:1521:orcl
函数
函数用于返回特定的数据,当建立函数时,在函数头部必须包括 return 子句,而在函数体内必须包含return语句返回的数据。我们可以使用 create function 来建立函数。
函数一般只返回一个值
//输入员工的姓名,返回他的年薪
create function fun01(p_name varchar2)
return number is -- 在函数头部必须包括 return 子句 ,number 表示返回的是数值类型
totalsal number(8,2);
begin
select sal*12+nvl(comm,0)*12 into totalsal from emp where ename=p_name;
return totalsal;
end;
执行函数
SQL> var result number;
SQL> call fun01('SCOTT') into :result;
//在java程序中调用
public static void test2(){
Connection conn = null;
CallableStatement stm = null;
try {
conn = DBUtil.getConn();
stm=conn.prepareCall("{?=call fun01('SCOTT')}"); //fun01 是函数名,SCOTT是函数要求的参数,? 是返回值的占位符
stm.registerOutParameter(1, java.sql.Types.VARCHAR); //声要接收返回值,1 代表第一个问号 java.sql.Types.VARCHAR 代表返回值的类型
stm.execute();
String result= stm.getString(1); //取出返回值
System.out.println("年薪是:"+result);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.close(null, stm, conn);
}
}
包
包用于在逻辑上组合过程和函数,由包规范和包体组成
1) 可以使用 create package 命令来创建包
create package sp_package_01 is
procedure sq_update_sal(name varchar2,newsal number); --//只有声明,没有实现
function fun02(name varchar2) return number; --//这个函数有一个 number类型的返回值
end;
包的规范只包含了过程和函数的说明,但没有实现
2) 建立包体 create package body 命令
create package body sp_package_01 is
procedure sq_update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name; --//更新工资
end;
function fun02(name varchar2) return number is
totalsal number(8,2);
begin
select sal*12+nvl(comm,0)*12 into totalsal from emp where ename=name;
return totalsal;
end;
//显示错误信息 show errors
如何调用包里的内容,要用包名,如果是其他方案的,要有方案名.
//执行
exec sp_package_01.sq_update_sal('SCOTT',5000);
变量-之标量
在编写pl/sql 的时候,可以定义变量和常量,包括
== 标量类型(scalar)
== 复合类型(composite)
== 参照类型(reference)
== lob (large object) //大对象
标量(scalar)
1) 常用类型的标量的定义
标量主要用来放单个数据
如果要使用变量,要在定义部分定义
语法
identifier [constant] datatype [not null] [:=| default expr]
identifier:名称
constant: 指定它是常量, 需要指定初值,其值不能变
datatype: 数据类型
not null: 表示不能为空
default: 初值
expr: 指定初始值的pl/sql 表达式,可以是文本,其他变量,函数等
例子
-- 定义一个变长字符串
v_empnam varchar2(10)
-- 定义一个小数 值在 -9999.99 - 9999.99 之间
v_sal number(6,2)
-- 定义一个小数,并给初值为 3.14
v_pai number(3,2) :=3.14 // := 赋值符号
-- 定义一个日期类型的变量
v_hiredate data
-- 定义一个布尔型变量,不能为空,初值为false
v_result boolean not null default false
2)标量的使用
例子 输入员工号,显示员工工资,姓名,个人所得税(税率 0.02)
set serveroutput on ;
declare c_tax_rage number(3,2):=0.02 ; --//税率,是常量,必须给初值 v_ename varchar2(50); v_sal number(8,2); v_tax_sal number(8,2); --//应交的所得税 begin select ename ,sal into v_ename,v_sal from emp where empno=&no; v_tax_sal:=v_sal*c_tax_rage; --//所得税=工资* 税率 dbms_output.put_line('姓名'||v_ename||'税'||v_tax_sal); end ;
3) 使用 %type 类型定义标量
上例存在一个问题
如果员工的姓名超过了50 ,会报错 字符串缓冲区太小
可以使用 %type 属性定义标量,它会根据你的数据库中列的长度和类型来定义标量
标识符名称 表名 列名 %type
比如上例中 可以 v_ename emp.ename%type
变量-之复合变量
复合类型变量(composite) 好比数组
用于存放多个值
-- pl/sql 记录
-- pl/sql 表名
-- 嵌套表
-- varry //动态数组
1) pl/sql 记录
类似高级语言中的结构体 ,当引用 pl/sql 记录成员时,必须要加记录变量做为前缀(记录变量.记录名)
declare
type emp_recorder_type is record --//声明一个复合类型变量
(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
sp_record emp_recorder_type; --//定义了一个复合类型的变量,名称是sp_record,类型是 emp_recorder_type
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_lne('员工名'||sp_record.name||'工资'||sp_record.salary||'工作'||sp_record.title);
end;
2) pl/sql 表名
相当于数组 ,但它的下标可以负值,并且元素的下标没有限制
declare
type sp_table_type is table of emp.ename%type --//定义一个 sp_table_type 自定义类型,用于存放 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'; --//目前只放了一条数据,如果是多条会报错
dbms_output.put_line('员工名'||sp_table(0)); --//这个下标是负数也可以
end;
变量-之参照类型变量 (reference)
用于存放数值指针的变量,通过它可以使得应用程序共享相同的对象,降低占用空间。
有以下两种
游标类型 (ref cursor)
对象类型 (ref obj_type) //不讲
1) 参照类型 -- ref cursor 游标变量
定义游标
用的时候(open ),要指定select 语句,这样一个游标就和select语句关联了，需求 写一个pl/sql语句块,可以输入部门号,并显示该部门的所有员工姓名和工资。如果某个员工的工资低于5000,就增加10000 元。
declare
type sp_cursor is ref cursor ; --//定义一个游标类型叫 sp_cursor
v_ename emp.ename%type;
v_sal emp.sal%type;
test_cursor sp_cursor; --//定义一个游标类型的变量,名叫 test_cursor begin
open test_cursor for select ename,sal from emp where deptno=&no; --//打开游标 ,要指定一个select语句
loop fetch test_cursor into v_ename,v_sal;
exit when test_cursor % notfound;
dbms_output.put_line(v_ename||v_sal);
if v_sal<3000 then
update emp set sal=v_sal+10000 where ename=v_ename;
end if; end loop;
close test_cursor;
end if;
end;
控制结构语句
条件分支语句
if then
if then else
if then elsif else //注意,不是elseif
循环语句
loop ... end loop
while ... loop end loop
for
控制语句
goto 语句
null 语句
1)条件分支语句
== if then
编写一个过程,可以输入一个员工名,如果工资小于5000,则再扣3000
和上例相似
if v_sal<5000 then
update emp set sal=v_sal-3000 where ename=v_ename;
end if;
== if then else //二重条件分支
输入一个员工名,如果补助不是0 就在原来的基础上增加100,如果为0 就把补助改成200
create or replace procedure sp_02 (spName varchar2) is v_comm emp.comm%type;
begin
select comm into v_comm from emp where ename=spName;
if v_comm <>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=200 where ename=spName;
end if;
end;
执行: exec sp_02('SCOTT');
== 多重条件分支
if then elsif else
编写一个过程,可以输入一个员工编号,如果职位是 CLERK 给工资加 100 ,SALESMAN 给加工资 500,其他加 200
create or replace procedure sp_03(sp_no number) is
v_job emp.job%type; begin
select job into v_job from emp where empno=sp_no;
if v_job='CLERK' then
update emp set sal=sal+100 where empno=sp_no; elsif v_job='SALESMAN' then
update emp set sal=sal+500 where empno=sp_no; else
update emp set sal=sal+200 where empno=sp_no;
end if; end;