--游标(光标) 一般用于多行数据
语法:cursor 游标名称 (参数名 参数类型 ..) is select语句
--操作游标
open 游标名; --打开游标
loop
fetch 游标名 into 记录型变量; --取出一条记录,放入记录型变量中
exit when 游标名%notfound; --当游标取空,退出
(具体操作)
end loop;
close 游标名; --关闭游标
--1、用游标方式输出emp表中的员工编号和姓名
declare
cursor empList is select * from emp;
pemp emp%rowtype;
begin
open empList;
loop
fetch empList into pemp;
exit when empList%notfound;
dbms_output.put_line(pemp.empno||'-'||pemp.ename);
end loop;
close empList;
end;
--2、写一段PL/SQL程序,为部门号=10的员工涨工资
declare
cursor addSal is select * from emp where deptno=10;
pemp emp%rowtype;
begin
open addSal;
loop
fetch addSal into pemp;
exit when addSal%notfound;
update emp set sal = sal +100 where empno = pemp.empno;
commit;
end loop;
close addSal;
end;
--3、写一段PL/SQL程序,某部门号的员工涨工资
declare
cursor addSal (dpn number) is select * from emp where deptno=dpn;
pemp emp%rowtype;
begin
open addSal (20); --此时指定实参
loop
fetch addSal into pemp;
exit when addSal%notfound;
update emp set sal = sal +1 where empno = pemp.empno;
commit;
end loop;
close addSal;
end;
--exception 例外 (异常)
declare
begin
[exception]
end;
--系统定义的异常
no_data_found --没有找到数据
too_many_rows --select..into语句匹配多个行
zero_divide --被零除
value_error --算数或转换错误
timeout_on_resource --等待资源超时
declare
pnum number;
begin
--pnum:=1/0;
pnum:='数字类型赋值字符串';
exception
when zero_divide then
dbms_output.put_line('被零除异常');
when value_error then
dbms_output.put_line('算数或转换错误');
when others then
dbms_output.put_line('其他异常');
end;
--自定义的异常
--1、查询部门编号是50 的员工,如果没有员工,则提示'没有找到员工'
declare
cursor dept_emp is select * from emp where deptno = 50;
pemp emp%rowtype;
no_found_emp exception; --声明异常
begin
open dept_emp;
fetch dept_emp into pemp;
if dept_emp%notfound then
raise no_found_emp; --抛出异常
end if;
close dept_emp;
exception
when no_found_emp then --捕获异常
dbms_output.put_line('没有找到员工');
end;
--存储过程:把pl/sql提前封装(编译)好,放在服务端,以便于再次调用
语法:
create [or replace] procedure 名称[(参数名 in/out 参数类型)]
as|is
begin
PLSQL程序
end[存储过程名称];
--1、给指定的员工涨100工资,并打印出涨前和涨后的工资
create procedure addSal(epn in number) --创建存储过程
as
be_sal number;
af_sal number;
begin
select sal into be_sal from emp where empno=epn;
update emp set sal =sal+100 where empno=epn;
commit;
select sal into af_sal from emp where empno=epn;
dbms_output.put_line(be_sal||'-'||af_sal);
end;
--调用存储过程
--第一种
call addSal(7369);
--第二种:在pl/sql调用
declare
begin
addSal(7369);
end;
--2、使用存储过程来查询指定员工的年薪
create procedure yearSal(epn in number,total out number)
as
psal number;
pcomm number;
begin
select sal,comm into psal,pcomm from emp where empno=epn;
total:=psal*12+nvl(pcomm,0);
end;
--调用存储过程
declare
total number;
begin
yearSal(7369,total);
dbms_output.put_line(total);
end;
--3、查询指定[部门]的员工
create procedure empList (dpn in number,cur_emp out sys_refcursor) --游标型变量
as
begin
open cur_emp for select * from emp where deptno=dpn;--给游标型变量放入多行结果集
end;
declare --调用
cur_emp sys_refcursor;
pemp emp%rowtype;
begin
empList(10,cur_emp); --调用存储过程
loop
fetch cur_emp into pemp;
exit when cur_emp%notfound;
dbms_output.put_line(pemp.empno||'-'||pemp.ename);
end loop;
close cur_emp; --关闭游标
end;
--存储函数
语法:
create [or replace] function 函数名(Name in type,Name out type ..) return 数据类型
as|is
结果变量 数据类型;
begin
PLSQL程序;
return (结果变量);
end[函数名];
--1、使用存储函数来查询指定员工的年薪
create or replace function f_getYearSal(epn in number) return number
as
psal number;
pcomm number;
begin
select sal,comm into psal,pcomm from emp where empno=epn;
return psal*12+nvl(pcomm,0);
end;
declare --调用存储函数
total number;
begin
total:=f_getYearSal(7369);
dbms_output.put_line(total);
end;
--函数和过程的区别
存储函数有返回值,但是过程和函数都可以使用out类型的参数返回一个或者多个值
--用java调用存储过程
|-先用java查询emp
|-调用存储过程(yearsal)
|-调用(empList)
--触发器
语法:
create [or replace] trigger 触发器名称
before|after
insert|delete|update [of 列名]
on 表名
[for each row]
declare
begin
end;
--例如:
create table person(
id number(10),
name varchar2(20)
)
--插入员工后打印一句话'一个新员工插入成功'
create trigger addPerson
after
insert
on person
for each row
declare
begin
dbms_output.put_line('一个新员工插入成功');
end;
insert into person values(1,'张三'); --测试
--不能在指定星期几增加新员工(此时是星期五)
--报错
raise_application_error(-20001,'不能再今天插入新员工') -- -20001~20999
create trigger noAdd
before
insert
on person
declare
week varchar2(20);
begin
select to_char(sysdate,'day') into week from dual;
if trim(week)='friday' then
raise_application_error(-20001,'不能在星期五增加员工');
end if;
end;
insert into person values(1,'张三'); --测试
--emp表中,判断员工涨工资的工资值一定大于涨工资之前的工资
只有在for each row出现时可以使用 :old :new
:old--伪记录型变量 某个操作之前的数据
:new--伪记录型变量 某个操作之后的数据
create or replace trigger addSal
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20002,'涨工资的工资值一定大于涨工资之前的工资');
end if;
end;
update emp set sal = sal-1 where empno=7369;--测试
update emp set sal = sal+1 where empno=7369;--测试
--实现主键自增列
--建表
create table test_user(
id number(6) primary key,
name varchar2(20) not null
)
--创建序列
create sequence seq_user;
--创建触发器
create trigger seq_pk
before
insert
on test_user
for each row
declare
begin
select seq_user.nextval into :new.id from dual;
--将自增主键 赋值给即将插入数据的主键
end;
insert into test_user(name) values('李四');
待续……