pl/sql(procedural language/sql)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言,还可以自定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误。
(1)过程、函数、触发器是pl/sql编写。
(2)存储过程、函数、触发器是在Oracle中的
(3)pl/sql是非常强大的数据库过程语言
(4)存储过程和函数可以再Java程序中调用。
pl/sql编程分类:
块是最小的单位,可以编写存储过程、函数、触发器、包
1、编写规范
(1)注释
单行注释 --
多行注释 /*........*/
(2)标志符号的命名规范
①定义变量时,用v_作为前缀,v_sal
②定义常量时,用c_作为前缀,c_rate
③定义游标时,用_cursor作为后缀,emp_cursor
④定义例外时,用e_作为前缀,e_error
2、块(block)
块是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能,可能只需编写一个pl/sql块,但是要实现复杂的功能,可能需要在一个pl/sql块中嵌套其他的pl/sql块。
块由三个部分构成:定义部分、执行部分、例外处理部分
declear
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分——处理运行的各种错误*/
end;
定义部分是从declare开始的,该部分可选;执行部分从begin开始,该部分必须;例外处理部分从exception开始的,该部分可选。
①定义部分和执行部分
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名:'|| v_ename ||'工资:'|| v_sal);
end;
②定义部分、执行部分和例外处理部分
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名:'|| v_ename ||'工资:'|| v_sal);
exception
when no_data_found then
dbms_output.put_lin('输入有误');
end;
3、存储过程
过程用于执行特定的操作。 当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out).通过在过程中使用输入参数,可以将数据传递到执行部分,通过使用输出参数,可以将执行部分的数据传递到应用环境。
使用create procedure来建立过程。
(1)创建存储过程
①只有执行部分
create or replace procedure pro1 is
begin
insert into mytest values('scott','tiger');
end;
/
replace表示如果有pro1就替换
查看错误信息:show error;
(2)调用存储过程
①exec 过程名(参数值1,参数值2....);
②call 过程名(参数值1,参数值2....);
eg:
create procedure pro2 (empName varchar2,newSal number) is--只是表明类型,没必要表明大小
--在存储过程中定义变量,定义在is和begin之间
update emp set sal=newSal where ename=empName;
end;
/
(3)在Java程序中调用存储过程
<span style="font-family:Microsoft YaHei;font-size:14px;"> try {
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORAL","scott","tigger");
//3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call pro(?,?)}");
//给?赋值
cs.setString(1, "SCOTT");
cs.setInt(2, 10);
//5.执行
cs.execute();
//关闭资源
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}</span>
4、函数
函数用于返回特定的数据,只返回一个值。当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。使用create function 来建立函数。
(1)建立函数
create function fun1(empName varchar2)
return number is yearSal number(7,2);--返回number,number名字是yearSal,类型是number(7,2)
begin
select sal*12+nvl(comn,0)*12 into yearSal from emp where ename=empName;
return yearSal;
end;
(2)调用函数
①在sqlplus中调用
>var income number // 定义一个变量
>call fun1("SCOTT") into :income;
②在Java程序中调用
select fun1("SCOTT") from emp;
通过rs.getInt(1)得到返回结果。
5、包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
(1)使用create package命令来创建包
eg:创建一个包package1,声明该包有一个过程update_sal,声明该包有一个函数anual_income
create package package1 is
procedure update_sal (name varchar2,newsal number); --过程声明
function anual_income(name varchar2) return number;--函数声明
end;
包规范值包含了过程和函数的声明,但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
(2)建立包体使用create package body 命令,实现包规范中的函数和过程
create or replace package body package1 is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal = newsal where ename=name;
end;
function annual_income(name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename = name;
return annual_salary;
end;
end;
(3)调用包的过程或是函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其他方案的包,还需要在包名前加方案名
exec package1.update_sal('SCOTT',120); //或是call
6、触发器
触发器是指隐含的执行的存储过程。用户不会主动调用。当定义触发器时,必须要指定出发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际上就是一个pl/sql块。
使用create trigger来建立触发器。
7、定义并使用变量
在编写pl/sql程序时,可以定义变量和常量;在pl/sql程序中包括
(1)标量类型--scalar 只能存储单个数据
①的定义标量
在编写pl/sql块时,如果要使用变量,需要在定义部分定义变量。pl/sql中定义变量和常量的语法如下:
identifier [constant] datatype [not null] [:=| default expr]
identifier:名称
constant:指定常量。需要指定它的初始值,且其值是不能改变的。
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或是常量指定初始值
default: 用于指定初始值
expr:指定初始值的pl/sql表达式,可以使文本值、其它变量、函数等。
eg:定义一个小数并给一个初始值为5.4,即v_sal是常量。
v_sal number(6,2):=5.1 // :=是pl/sql的赋值号
定义一个布尔变量,不能为空,初始值为false
v_valid boolean not null defalt false;
②使用标量
declare
c_tax_rate number(3,2):=0.03;
v_name vchar(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=v_sal * c_tax_rate;
dbms_output.put_lime(v_name || v_sal || v_tax_sal );
end;
③使用%type
场景:如上若v_name的大小超过了5个字符就会报错,它会按照数据库列来确定你定义的变量的类型和长度。
使用:标识符名 表名.列名%type;
v_name emp.ename%type; --和emp表的ename字段的大小和类型一样
(2)复合类型--composite 存放多个值的变量
①pl/sql记录
类似高级语言中的结构体(类)
declare
type emp_record _type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type); --定义了一种emp_record_type记录类型
scott_record emp_record_type; --定义了一个scott_record变量,类型是上述的定义类型
begin
select ename,sal,job into scott_record from emp where empno=7788;
dbms_output.put_line(emp_record.name);
end;
②pl/sql表
相当于高级语言中的数组。但是数组下标可以为负数,并且表元素的下标没有限制。
eg:
declare
type table_type is table of emp.ename%type
index by binary_integer; --定义了一个pl/sql表类型table_type,该类型用于存放emp.ename%type这种类型的数据数组,index by binary_integer表示下标是整数,可为负数
scott_table table_type; --定义一个scott_table变量,类型是table_type
begin
select ename into scott_table(0) from emp where empno=7788;
dbms.output.put_line(scott_table(0));
end;
③嵌套表--nested table
④varray--变长数组
(3)参照类型--reference
指用于存放数值指针的变量。通过使用参照变量,可以使应用程序共享相同的对象,从而降低占用的空间。
参照变量包括游标变量(ref cursor)和对象类型变量(ref obj_type)两种类型。
①游标变量
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open)需要指定select语句,这样一个游标就与一个select语句结合了。
eg:
declare
type emp_cursor is ref cursor; --定义游标类型
test_cursor emp_cursor; --定义游标变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp where deptno=&no; //让游标指向结果集,即把test_cursor和一个select结合。
loop --循环取出存在变量中
fetch test_cursor into v_ename,v_sal;
//退出条件,判断test_cursor是否为空
exit when test_cursor%notfound;
dbms_output.put_line(v_ename||v_sal);
end loop;
end;
(4)lob--large object