为什么使用游标
在写java程序中有集合的概念,那么在pl/sq中也会用到多条记录,这时候我们就要用到游标,游标可以存储查询返回的多条数据。
语法:
CURSOR 游标名 [(参数名数据类型,参数名数据类型…,] IS SELECT语句;
例如:
cursor c1 is select ename from emp;
游标的使用步骤:
打开游标: open c1;(打开游标执行查询)
取一行游标的值: fetch c1 into job:(取一行到变量中)
关闭游标: close c1;(关闭游标释放资源)
游标的结束方式: exit when c1%notfound
注意:上面的pjob必须与emp表中的job列类型一致
定义:
pjob emp. empjob%type;
范例1:使用游标方式输出emp表中的员工编号和姓名
declare
cursor pc is
select * from emp;
pemp emp%rowtype;
begin
open pc;
loop
fetch pc into pemp;
exit when pc%notfound;
dbms_output.put_line(pemp.empno II’ ‘II pemp.ename);
end loop;
close pc;
end;
范例2:写一段PL/SQL程序,为部门号为10的员工涨工资
declare
cursor pc(dno myemp.deptno% type) is
select empno from myemp where deptno = dno;
pno myemp.empno%type;
begin
open pc(20);
loop
fetch pc into pno
exit when pc%notfound;
update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
end loop;
close pc;
●例外
异常是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。
系统定义异常
no_data_found(没有找到数据)
too_many_rows( (select.into语句匹配多个行)
zero_ divide(被零除)
value_error(算术或转换错误)
timeout_on_resource(在等待资源时发生超时)
范例1:写出被0除的异常的psq程序
declare
pnum number
begin
pnum:=1/0;
exception
when zero_divide then
dbms_ output.put_line('被0除');
when value_error then
dbms_ output.put_line('数值转换错误');
when others then
dbms_ output.put_line('其他错误');
end:
用户也可以自定义异常,在声明中来定义异常
DECLARE
My_job char(10);
v_sal emp.sal%type;
No_data exception;
cursor cl is select distinct job from emp order by job;
如果遇到异常我们要抛出 raise no_data;
范例2:查询部门编号是50的员工
declare
no_emp_found exception;
cursor pemp is
select t.ename from emp t where t.deptno = 50;
pename emp.ename% type;
begin
open pemp;
fetch pemp into pename;
if pemp%notfound then
raise no_emp_found;
end if;
close pemp;
exception
when no_emp_found then
dbms_output.put_line(’没有找到员工');
when others then
dbms_output.put_line(’其他错误’);
end:
存储过程( Stored procedure)是在大型数据库系统中,一组为了完成特定功能的SQL句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程
创建存储过程语法:
create [or replace] PROCEDURE过程名 [(参数名in/out数据类型])
AS
begin
PLSQL子程序体;
End:
或者
create [ or replace ] PROCEDURE过程名 [(参数名in/out数据类型)]
is
begin
PLSQL子程序体
End过程名;
范例1:给指定的员工涨100工资,并打印出涨前和涨后的工资
分析:我们需要使用带有参数的存储过程
create or replace procedure addSall(eno in number) is
pemp myempo%rowtype
begin
select * into pemp from myemp where empno =eno;
update myemp set sal = sal + 100 where empno =eno;
dbms_ output.put_line('涨工资前’||pemp.sal||'涨工资后’||(pemp.sa1+100));
end addSall;
调用:
begin
--Call the procedure
addSall(eno=> 7902);
commit;
end:
create or replace function函数名 (Name in type, Name out type,…) return数据类型 is 结果变量 数据类型
begin
return(结果变量);
end[函数名];
存储过程和存储函数的区别:
一般来讲,过程和函数的区别在于函数可以有一个返回值,而过程没有返回值;
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数,在过程和函数中实现返回多个值。
范例:使用存储函数来查询指定员工的年薪
create or replace function empincome(eno in emp.empno%type)
return number is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal into psal from emp t where t.empno = eno;
return psal * 12 + nvl(pcomm, 0);
end;
使用存储过程来替换上面的例子
create or replace procedure empincomep(eno in emp. empno%type,
income out number) is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
income := psal*12 + nvl(pcomm,0);
end empincomep;
调用:
declare
income number;
begin
empincomep(7369,income);
dbms_output.put_line(income);
end:
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句( insert, update, delete)在指定的表上发出时, Oracle自动地执行触发器中定义的语句序列。
1.数据确认
示例:员工涨后的工资不能少于涨前的工资
2.实施复杂的安全性检查
示例:禁止在非工作时间插入新员工
3.做审计,跟踪表上所做的数据操作等
4.数据的备份和同步
√语句级触发器
在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行。
√行级触发器( FOR EACH ROW)
触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量,识别值的状态
CREATE[ or REPLACE] TRIGGER触发器名
{BEFORE I AFTER}
{DELETE| NSERT| UPDATE [OF列名]}
ON表名
[ FOR EACH ROW [WHEN条件)]]
declare
......
PLSQL块
end 触发器名
范例:插入员工后打印一句话“一个新员工插入成功”
create or replace trigger testTrigger
after insert on person
declare
--local variables here
begin
dbms_output.put_line('一个员工被插入');
end testTrigger;
范例:不能在休息时间插入员工
create or replace trigger validInsertPerson
before insert on person
declare
weekend varchar2(10)i
begin
select to_char (sysdate, 'day') into weekend from dual;
if weekend in(‘星期一’) then
raise_application_error(-20001,’不能在非法时间插入员工’);
end if;
end validInsertPerson:
当执行插入时会报错
在触发器中触发语句与伪记录变量的值
范例:判断员工涨工资之后的工资的值一定要大于涨工资之前的工资
create or replace trigger addsal4p
before update of sal on myemp
for each row
begin
if :old.sal >= :new.sal then
raise_application_error(-20002,'涨前的工资不能大于涨后的工’);
end if;
end;
调用
update myemp t set t.sal = t.sal - 1 ;
需求:使用序列,触发器来模拟mysq中自增效果
1、建立表
create table user
(id number(6) not null,
name varchar2(30) not null primary key)
2 建立序列 SEQUENCE
create sequence user_seq:
分析:创建一个基于该表的 before insert触发器,在触发器中使用刚建的 SEQUENCE
create or replace trigger user_trigger
before insert on user
for each row
begin
select user_seq.nextval into :new. id from sys.dual;
end;
insert into user(name) values( ‘Kevin1’);
commit;
insert into user(name) values( ‘Kevin2’);
commit;
在Java项目中导入连接Oracle数据库所需要的jar包。
String driver = "Oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@10.131.68.66:1521:orcl ";
String username = "scott";
String password = "admin";
测试代码: