游标使用:
-- Created on 2013/7/5 by ADMIN
declare
-- Local variables here
i integer;
cursor c_emp(p_deptno number) is select * from emp e where e.deptno=p_deptno; ---声明游标
emprow c_emp%rowtype;---定义变量追踪游标类型
begin
-- Test statements here
open c_emp(10);---打开游标,有参传参
loop
fetch c_emp into emprow;---讲游标取出的每行数据放入对应行变量
exit when c_emp%notfound;
dbms_output.put_line(emprow.empno||' '||emprow.empno||' '||emprow.deptno);
end loop;
close c_emp;---关闭游标
end;
存储过程:
--用存储过程实现根据传入的部门号和job,讲员工工资提高10%
create or replace procedure p_emp
--装参数(分为传入in和传出out两种类型)
(
l_deptno in number,----传入的参数,不需要写出长度,也不能赋值
l_job in varchar2,
l_result out varchar2---用来判断是否正确执行,返回值是‘0000’则正确,返回‘1111’则是错误执行
)
is
--声明变量
begin
l_result := '0000';
--业务主体语句
update emp_copy e set e.sal=e.sal*1.1 where e.deptno=l_deptno and e.job=l_job;
commit;
exception
when others then
l_result := '1111';
rollback;
end;
存储过程的调用可以在plsql工具的命令窗口或者是测试窗口,但是最重要的是在java中调用,现在在测试窗口调用测试:
declare
-- Local variables here
i integer;
l_result varchar2(20);
begin
-- Test statements here
p_emp(20,'CLERK',l_result);
dbms_output.put_line(l_result);
end;
练习:传一个员工号来给这个员工涨工资,规则:如果是10号部门,工资涨10%,20号部门涨20%,30号部门涨30%,其他员工不变,但是最高工资不能超过5000.
create or replace procedure addSal(
p_empno in number,
l_result out varchar2
)
is
l_deptno emp_copy.deptno%type;
l_sal emp_copy.sal%type;
begin
l_result := '0000';
select e.deptno into l_deptno from emp e where e.empno=p_empno;
if l_deptno=10 then
update emp_copy e2 set e2.sal=e2.sal*1.1 where e2.empno=p_empno;
elsif l_deptno=20 then
update emp_copy e2 set e2.sal=e2.sal*1.2 where e2.empno=p_empno;
elsif l_deptno=30 then
update emp_copy e2 set e2.sal=e2.sal*1.3 where e2.empno=p_empno;
end if;
select e.sal into l_sal from emp_copy e where e.empno=p_empno;
if l_sal>5000 then
update emp_copy e set e.sal=5000 where e.empno=p_empno;
end if;
commit;
exception
when others then
l_result := '1111';
rollback;
end;
关于emp复制表emp_copy的增删改存储过程:
create or replace procedure emp_copy_cdu(
l_remark in number,----0 增加,1 删除,2 修改
l_empno in number,
l_ename in varchar2,
l_job in varchar2,
l_mgr in number,
l_hiredate in varchar2,
l_sal in number,
l_comm in number,
l_deptno in number,
l_result out varchar2
)
is
begin
l_result :='0000';
if l_remark=0 then
insert into emp_copy values(l_empno,
l_ename,
l_job,
l_mgr,
to_date(l_hiredate,'yyyy-MM-dd'),
l_sal,
l_comm,
l_deptno);
elsif l_remark=1 then
delete from emp_copy e where e.empno=l_empno;
elsif l_remark=2 then
update emp_copy e set e.ename=l_ename,
e.job=l_job,
e.mgr=l_mgr,
e.hiredate=to_date(l_hiredate,'yyyy-MM-dd'),
e.sal=l_sal,
e.comm=l_comm,
e.deptno=l_deptno where e.empno = l_empno;
end if;
commit;
exception
when others then
l_result :='1111';
rollback;
end;