整理自网络
1.使用OUT类型的参数返回存储过程的结果
创建带输出参数的存储过程
create or replace procedure emp_count_out(ptotal out number) is
begin
select count(*) into ptotal from my_emp;
end emp_count_out;
备注:输出参数为ptotal
创建另一个存储过程,并调用存储过程emp_count_out
create or replace procedure emp_list is
v_empcount number; --定义变量
BEGIN
emp_count_out(v_empcount); --调用带有输出参数的存储过程,并将输出参数的值传递给变量v_empcount
dbms_output.put_line('人数:'||v_empcount);
end emp_list;
执行存储过程
set serveroutput on
call emp_list();
执行结果
人数:14
Call completed.
2.带输入参数的存储过程
编写给雇员增加工资的存储过程CHANGE_SALARY,通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
创建带输入参数的存储过程
create or replace procedure change_salary(p_empno number default 7788, p_paise number default 10) is
v_ename dongxiaobing.my_emp.ename%type;
v_sal dongxiaobing.my_emp.sal%type;
begin
select ename,sal into v_ename,v_sal from dongxiaobing.my_emp where empno=p_empno;
update dongxiaobing.my_emp set sal=v_sal+p_paise where empno=p_empno;
dbms_output.put_line('员工:'||v_ename||'工资改为'||to_char(v_sal+p_paise));
commit;
exception
when others then
dbms_output.put_line('发生错误,修改失败!');
rollback;
end change_salary;
执行存储过程
call change_salary();
员工:SCOTT工资改为3030
Call completed.
执行结果
call change_salary();
员工:SCOTT工资改为3030 --参数默认值
Call completed.
或者
call change_salary(7369,100);
员工:SMITH工资改为1100
Call completed.
练习:创建插入雇员信息的存储过程,将雇员编号等作为输入参数
create or replace procedure insert_emp(
v_empno my_emp.empno%type, --用my_emp表字段类型类定义变量
v_ename my_emp.ename%type,
v_job my_emp.job%type,
v_mgr my_emp.mgr%type,
v_hiredate my_emp.hiredate%type,
v_sal my_emp.sal%type,
v_comm my_emp.comm%type,
v_deptno my_emp.deptno%type
) is
begin
insert into my_emp values(v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
commit;
end insert_emp;
执行存储过程
call insert_emp(8001,'yang','SALESMAN',7698,to_date('1982-7-8','yyyy-mm-dd'),2000,300,30);
Call completed.
3.带输入输出参数的存储过程
使用IN OUT类型的参数,给电话号码增加区码。
create or replace procedure add_region(p_phone_number in out varchar2) is --定义输入输出参数
begin
p_phone_number:='0334-'||p_phone_number; --给参数赋值
end add_region;
调用存储过程add_region
create or replace procedure add_region_call is
v_phone_number varchar2(15);
begin
v_phone_number:='23233981';
add_region(v_phone_number);
dbms_output.put_line('电话号码为:'||v_phone_number);
end add_region_call;
执行存储过程,返回结果
SQL> call add_region_call();
电话号码为:0334-23233981
Call completed.
4.
如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%;
如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%;其他情况不作处理
使用sql实现
update my_emp set sal=sal*1.15 where empno in
(select empno from my_emp a,scott.dept b where a.deptno=b. deptno and a.job='MANAGER' and b.loc='DALLAS');
update my_emp set sal=sal*0.95 where empno in
(select empno from my_emp a,scott.dept b where a.deptno=b. deptno and a.job='CLERK' and b.loc='NEW YORK');
使用存储过程实现
create or replace procedure update_sarlary is
cursor a_cursor is select * from my_emp;
b_cursor a_cursor%rowtype;
v_loc scott.dept.loc%type;
begin
for b_cursor in a_cursor loop
select loc into v_loc from scott.dept where deptno=b_cursor.deptno;
if b_cursor.job='MANAGER' and v_loc='DALLAS' then
update my_emp set sal=sal*1.15 where empno=b_cursor.empno;
elsif b_cursor.job='CLERK' and v_loc='NEW YORK' then
update my_emp set sal=sal*0.95 where empno=b_cursor.empno;
else
null;
commit;
end if;
end loop;
end update_sarlary;
5.根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).
使用sql实现
select deptno,empno,ename,sal,rank()over(partition by deptno order by sal) rn from scott.emp;
备注:rank()为跳跃排序,如果有两个第二名,接下来就是第四名
使用存储过程实现
create or replace procedure paixu is
cursor a_cursor is select deptno,empno,ename,sal,rank()over(partition by deptno order by sal) rn from scott.emp;
b_cursor a_cursor%rowtype;
begin
for b_cursor in a_cursor loop
dbms_output.put_line(b_cursor.deptno||','||b_cursor.empno||','||b_cursor.ename||','||b_cursor.sal||','||b_cursor.rn);
end loop;
end paixu;
6.
实现批量修改
名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪
使用sql实现
update my_emp set sal=sal*1.1 where empno in (select empno from my_emp where ename like 'A%' or ename like 'S%');
使用存储过程实现
以下是两种存储过程方法
create or replace procedure update_salary_3 is
cursor test1_cursor is select * from my_emp where ename like 'A%' or ename like 'S%';
test2_cursor test1_cursor%rowtype;
begin
for test2_cursor in test1_cursor loop
update my_emp set sal=sal*1.1 where empno=test2_cursor.empno;
commit;
end loop;
end update_salary_3;
##################################
create or replace procedure update_salary_3 is
cursor test1_cursor is select * from my_emp;
test2_cursor test1_cursor%rowtype;
begin
for test2_cursor in test1_cursor loop
if test2_cursor.ename like 'A%' or test2_cursor.ename like 'S%' then
update my_emp set sal=sal*1.1 where empno=test2_cursor.empno;
end if;
commit;
end loop;
end update_salary_3;
7.对直接上级是'BLAKE'的所有员工,按照参加工作的时间加薪:
81年6月以前的加薪10%
81年6月以后的加薪5%
使用sql实现
update my_emp set sal=sal*1.1 where hiredate<to_date('1981/06/01','yyyy-mm-dd') and
empno in (select empno from my_emp where mgr=(select empno from my_emp where ename='BLAKE'));
update my_emp set sal=sal*1.05 where hiredate>to_date('1981/06/01','yyyy-mm-dd') and
empno in (select empno from my_emp where mgr=(select empno from my_emp where ename='BLAKE'));
commit;
使用存储过程实现
create or replace procedure update_salary_2 is
cursor a_cursor is select * from my_emp where mgr=(select empno from my_emp where ename='BLAKE');
b_cursor a_cursor%rowtype;
begin
for b_cursor in a_cursor loop
if b_cursor.hiredate
update my_emp set sal=sal*1.1 where empno=b_cursor.empno;
elsif
b_cursor.hiredate>to_date('1981/06/01','yyyy-mm-dd') then
update my_emp set sal=sal*1.05 where empno=b_cursor.empno;
end if;
commit;
end loop;
end update_salary_2;
8.对所有职位为'saleman'增加佣金500
sql实现
update my_emp set comm=comm+500 where empno in (select empno from my_emp where job='SALESMAN');
存储过程实现
create or replace procedure update_salary_4 is
cursor a_cursor(pjob my_emp.job%type) is --定义带参数的游标
select * from my_emp where job=pjob for update of comm; --锁定符合条件的行
b_cursor a_cursor%rowtype;
v_comm my_emp.comm%type; --定义变量
begin
for b_cursor in a_cursor('SALESMAN') loop --传递值给游标
v_comm:=b_cursor.comm+500; --变量赋值
update my_emp set comm=v_comm WHERE CURRENT OF a_cursor; ---从当前游标修改
end loop;
end update_salary_4;
9.提升两个资格最老的"职员"为"高级职员"(工作时间越长,优先级越高)
sql实现
update my_emp set job='HIGHCLERK' where empno in (select empno from
(select * from my_emp where job='CLERK' order by hiredate)
where rownum<3);
存储过程实现
create or replace procedure update_job_1 is
cursor a_cursor is
select * from
(select * from my_emp where job='CLERK' order by hiredate)
where rownum<3;
b_cursor a_cursor%rowtype;
begin
for b_cursor in a_cursor loop
update my_emp set job='HIGHCLERK' where empno in b_cursor.empno;
commit;
end loop;
end update_job_1;
10.对所有雇员按他们基本薪水的10%加薪,如果所增加的薪水大于5000,则取消加薪(并输出工资情况)
create or replace procedure update_salary_5 is
cursor a_cursor is select * from my_emp for update of sal;
b_cursor a_cursor%rowtype;
v_newsal my_emp.sal%type;
begin
for b_cursor in a_cursor loop
if b_cursor.sal>=5000 then
v_newsal:=b_cursor.sal;
dbms_output.put_line(b_cursor.ename||':'||'保持原来的工资:'||v_newsal);
else
v_newsal:=b_cursor.sal*1.1;
dbms_output.put_line(b_cursor.ename||':'||'工资更新为:'||v_newsal);
end if;
update my_emp set sal=v_newsal where current of a_cursor;
end loop;
commit;
end update_salary_5;
11.显示emp的第四条记录
使用sql实现
select * from
(select rownum rn,e.* from my_emp e where rownum<3)
where rn>=2;
使用存储过程实现
create or replace procedure select_record is
cursor a_cursor is select * from my_emp;
b_cursor a_cursor%rowtype;
begin
for b_cursor in a_cursor loop
if a_cursor%rowcount=4 then
Dbms_output.put_line(b_cursor.empno||','||b_cursor.ename
||','||b_cursor.job||','||b_cursor.mgr||','||b_cursor.hiredate
||','||b_cursor.sal||','||b_cursor.comm||','||b_cursor.deptno);
end if;
end loop;
end select_record;
12.求1-100之间的素数
create or replace procedure test is
pag boolean:=true;
begin
for i in 1..100 loop
for j in 2..i-1 loop
if mod(i,j)=0 then
pag:=false;
end if;
end loop;
if pag then
dbms_output.put_line(i);
end if;
pag:=true;
end loop;
end test;
13.给雇员加薪10%的过程,这之后,检查如果已经雇佣该雇员超过60个月,则给他额外加薪3000.
create or replace procedure update_salary_6 is
cursor a_cursor is select * from my_emp for update of sal;
b_cursor a_cursor%rowtype;
v_newsal my_emp.sal%type;
begin
for b_cursor in a_cursor loop
if months_between(sysdate,b_cursor.hiredate)>60 then
v_newsal:=b_cursor.sal*1.1+3000;
else
v_newsal:=b_cursor.sal*1.1;
end if;
update my_emp set sal=v_newsal where current of a_cursor;
end loop;
commit;
end update_salary_6;
14.一个函数以检查所指定雇员的薪水是否有效范围内。
不同职位的薪水范围为:
Designation Raise
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above
如果薪水在此范围内,则显示消息"Salary is OK",否则,更新薪水为该范围内的最低值
使用函数实现
create or replace procedure update_salary_7(p_empno in my_emp.empno%type, ss out varchar2) is
v_name my_emp.ename%type;
v_job my_emp.job%type;
v_sal my_emp.sal%type;
begin
select ename,job,sal into v_name,v_job,v_sal from my_emp where empno=p_empno;
if v_job='CLERK' then
if v_sal>=1500 and v_sal<=2500 then
ss:='Salary is OK';
else
v_sal:=1500;
ss:='sal is:'||to_char(v_sal);
end if;
elsif v_job='SALESMAN' then
if v_sal>=2501 and v_sal<=3000 then
ss:='Salary is OK';
else
v_sal:=2501;
ss:='sal is:'||to_char(v_sal);
end if;
elsif v_job='ANALYST' then
if v_sal>=3500 and v_sal<=4500 then
ss:='Salary is OK';
else
v_sal:=3500;
ss:='sal is:'||to_char(v_sal);
end if;
elsif v_sal>=4501 then
ss:='Salary is OK';
else
v_sal:=4501;
ss:='sal is:'||to_char(v_sal);
end if;
update my_emp set sal=v_sal where empno=p_empno;
end update_salary_7;
执行函数
BEGIN
DBMS_OUTPUT.PUT_LINE(salary_test(7499));
END;
返回结果
Salary is OK
15.输入员工编号,返回员工姓名
使用函数实现
create or replace function get_emp_ename(f_empno in my_emp.empno%type) return varchar2 is
v_ename varchar2(20);
begin
select ename into v_ename from my_emp where empno=f_empno;
return(v_ename);
exception
when no_data_found then
dbms_output.put_line('雇员编号不存在!');
when too_many_rows then
dbms_output.put_line('有重复的雇员编号!');
when others then
dbms_output.put_line('编号输入错误!');
end get_emp_ename;
执行
begin
dbms_output.put_line('员工姓名为:'||get_emp_ename(7369));
end;
结果
员工姓名为:SMITH
#####################################################
使用带输出参数的存储过程实现
create or replace procedure update_salary_7(p_empno in my_emp.empno%type,name out my_emp.ename%type) is
begin
select ename into name from my_emp where empno=p_empno;
dbms_output.put_line('雇员姓名:'||name);
exception
when no_data_found then
dbms_output.put_line('雇员编号不存在!');
when too_many_rows then
dbms_output.put_line('有重复的雇员编号!');
when others then
dbms_output.put_line('编号输入错误!');
end update_salary_7;
执行
declare
name my_emp.ename%type;
begin
empinfo(7369,name);
end;
结果
雇员姓名:SMITH
16.(华为笔试题)
用户资料表:serv( serv_id number(10), pro_id number(10), user_type varchar2(30), terminal_name varchar2(30) )
其中,serv_id 为用户标识,是serv表的主键,prod_id为产品标识;user_type为用户类型;terminal_name为终端类型
终端类型临时表:terminal(serv_id number(10),terminal_name varchar2(30))
初始化的情况下,serv表的serv_id,prod_id,user_type字段是已知的,terminal_name字段是空的,现在主要根据prod_id,user_type字段的值来更新terminal_name字段
更新条件为:
当条件满足“prod_id = 1 and user_type='A'“时,terminal_name更新为‘固话’
当条件满足”prod_id = 1 and user_type='B'“时,terminal_name更新为‘小灵通’
当条件满足”prod_id = 2“时,terminal_name更新为‘宽带’
当条件满足”user_type=‘C’“时,terminal_name更新为‘CDMA’
以上条件均不满足时,terminal_name更新为 -1;
create table serv(
serv_id number(10),
pro_id number(10) not null,
user_type varchar2(30) not null,
terminal_name varchar2(30),
constraints serv_id_pk primary key(serv_id));
insert into serv values(0001,1,'A',null);
insert into serv values(0002,1,'B',null);
insert into serv values(0003,1,'C',null);
insert into serv values(0004,2,'A',null);
insert into serv values(0005,2,'B',null);
insert into serv values(0006,2,'C',null);
insert into serv values(0007,3,'A',null);
insert into serv values(0008,3,'B',null);
insert into serv values(0009,3,'C',null);
create table terminal(
serv_id number(10) not null,
terminal_name varchar2(30)
);
insert into terminal values(0001,null);
insert into terminal values(0002,null);
insert into terminal values(0003,null);
insert into terminal values(0004,null);
insert into terminal values(0005,null);
insert into terminal values(0006,null);
insert into terminal values(0007,null);
insert into terminal values(0008,null);
insert into terminal values(0009,null);
create or replace procedure update_terminal is
cursor a_cursor is select * from serv_test for update of terminal_name;
b_cursor a_cursor%rowtype;
v_terminal_name serv_test.terminal_name%type;
begin
open a_cursor;
loop
fetch a_cursor into b_cursor;
exit when a_cursor%notfound;
case
when b_cursor.pro_id=1 and b_cursor.user_type='A' then
v_terminal_name:='固话';
when b_cursor.pro_id=1 and b_cursor.user_type='B' then
v_terminal_name:='小灵通';
when b_cursor.pro_id=2 then
v_terminal_name:='宽带';
when b_cursor.user_type='C' then
v_terminal_name:='CDMA';
else
v_terminal_name:='-1';
end case;
update serv_test set terminal_name=v_terminal_name where current of a_cursor;
end loop;
close a_cursor;
update terminal_test tt set terminal_name=
(select terminal_name from serv_test st where tt.serv_id=st.serv_id)
where exists
(select 1 from serv_test st where st.serv_id=tt.serv_id);
end update_terminal;