Oracle存储过程开发学习
1.rowtype的使用
create or replace procedure PD_ROWTYPE is
v_emp_rec emp%rowtype;
begin
select * into v_emp_rec from emp where empno=7839;
dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal);
end PD_ROWTYPE;
2.判断用户从键盘输入的数字
accept num prompt'请输入一个数字';
declare
pnum number :=#
begin
if pnum=0 then dbms_output.put_line('您输入的数字是0');
elsif pnum=1 then dbms_output.put_line('您输入的数字是1');
elsif pnum=1 then dbms_output.put_line('您输入的数字是2');
else dbms_output.put_line('其他数字');
end if;
end;
3.使用while循环打印数字的1~10
declare
pnum number:=1;
begin
while pnum<=10 loop
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
4.使用loop循环打印
declare
pnum number:=1;
begin
loop
exit when pnum>10;
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
5. 使用for循环打印1~10
declare
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
6.-查询并打印员工的姓名和薪水
--光标的属性
--%found %notfound
declare
cursor cemp is select ename,sal from emp;
--为光标定义对应的变量
pename emp.ename%type;
psal emp.sal%type;
begin
-- Test statements here
open cemp;
loop
--取一条记录
fetch cemp into pename,psal;
--思考:1.循环什么时候退出?2.fetch不一定能取到记录
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
--打印
end loop;
close cemp;
end;
7.给员工涨工资,总裁1000,经理800,其他400
declare
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
-- Test statements here
open cemp;
loop
--取出一个员工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断员工的职位
if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+500 where empno=pempno;
end if;
end loop;
close cemp;
--事物的提交
commit;
dbms_output.put_line('涨工资完成');
end;
8.光标
1)光标的属性
%found %notfound
%isopen 判断光标是否打开
%rowcount 影响的行数,不是总行数,到目前取走的行数
2)光标的限制:默认情况下,oracle数据库只允许在同一个会话中,打开300个光标
修改光标数的限制:
alter system set open_cursor=400 scope=both;
9.统计每年的入职员工数
declare
cursor cemp is select to_char(hiredate,'yyyy') from emp;
phiredate varchar2(4);
--每年入职的员工人数
count80 number:=0;
count81 number:=0;
count82 number:=0;
count87 number:=0;
begin
open cemp;
loop
--取出一个员工的入职年份
fetch cemp into phiredate;
exit when cemp%notfound;
--判断入职年份
if phiredate = '1980' then count80:=count80+1;
elsif phiredate = '1981' then count81:=count81+1;
elsif phiredate = '1982' then count82:=count82+1;
else count87:=count87+1;
end if;
end loop;
close cemp;
--输出结构
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line('1980:'||count80);
dbms_output.put_line('1981:'||count81);
dbms_output.put_line('1982:'||count82);
dbms_output.put_line('1987:'||count87);
end;
10、员工涨工资问题(有bug待完善)
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--涨工资的人数
countEmp number:=0;
--涨后的工资总额
salTotal number;
begin
--得到工资总额的初始值
select sum(sal) into salTotal from emp;
--打开光标
open cemp;
loop
--1工资总额>5w
exit when salTotal>50000;
--取一个员工涨工资
fetch cemp into pempno,psal;
--2%notfound
exit when cemp%notfound;
--涨工资
update emp set sal*1.1 empno=pempno;
countEmp:=countEmp+1;
--涨后的工资总额=涨钱的工资总额+sal*0.1
salTotal:=salTotal+psal*0.1;
end loop;
--关闭光标
close cemp;
commit;
dbms_output.put_line('人数:'||countEmp||'涨后的工资总额:'||salTotal);
end;
11、对比2种存储过程的写法,该存储过程是用来为emp1表中对于不同层次工资进行相应的增长,2中写法都能达到同样的效果。
第一种写法:
declare
cursor csr_update is select * from emp1;
empinfo csr_update%rowtype;
saleinfo emp1.sal%type;
empnoinfo emp1.empno%type;
begin
open csr_update;
loop
fetch csr_update into empinfo;
exit when csr_update%notfound;
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
else
saleInfo:=empInfo.sal;
END IF;
empnoinfo:=empInfo.empno;
update emp1 set sal=saleinfo where empno=empnoinfo;
end loop;
close csr_update;
end;
第二种写法:
declare
cursor
csr_Update is select * from emp1 for update OF SAL;
empInfo csr_Update%rowtype;
saleInfo emp1.SAL%TYPE;
begin
FOR empInfo IN csr_Update LOOP
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
else
saleInfo:=empInfo.sal;
END IF;
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
END LOOP;
END;
12、对每位员工的薪水进行判断,如果该员工薪水高于其所在部门的平均薪水,则将其薪水减50元,输出更新前后的薪水,员工姓名,所在部门编号。
declare
cursor
csr_Update is select * from emp1 for update OF SAL;
empInfo csr_Update%rowtype;
saleInfo emp1.SAL%TYPE;
begin
FOR empInfo IN csr_Update LOOP
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
else
saleInfo:=empInfo.sal;
END IF;
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
END LOOP;
END;
13.放入存储过程中嵌套
create or replace procedure proc_tmp_dept (out_cnt out number) as begin declare cursor cur_dept is select deptno from dept; v_count number; --此begin不能少,不写此begin与对应的end(即for loop代码块不用begin end括起来,会报编译错误) begin for dept_rec in cur_dept loop insert into temp_dept(deptno) values(dept_rec.deptno); v_count:=1000000; out_cnt:=v_count; end loop; end; end;