Oracle存储过程练习题

1.1.创建一个过程，能向dept表中添加一个新记录。（in参数）

创建过程
create or replace procedure insert_dept
( num_dept in number, var_ename in varchar2, var_loc in varchar2 ) is
begin
insert into dept
values(num_dept,var_ename,var_loc);
commit;
end insert_dept;
/
调用
begin
insert_dept(79,'技术部','武汉');
end;
/

2.从scott.emp表中查询给定职工
（提示：使用&来输入员工编号）的职工姓名和工资。（要求：利用out模式的参数将值传给调用者。）

创建过程
create or replace procedure select_emp(
num_empno in emp.empno%type,
name out emp.ename%type,
salary out emp.sal%type) is
begin
select ename,sal into name,salary from emp where empno = num_empno;
exception
when no_data_found then
    dbms_output.put_line('该部门不存在');
end select_emp;
/
调用
declare
var_name emp.ename%type,
var_salary emp.sal%type) is
begin
select_emp(&员工编号,var_ename,var_salary);
if var_ename is not null then
dbms_output.put_line(var_ename||' '||var_salary);
end if;
end;
/

 

3.创建一个过程，在执行调用过程时，可随机输入emp表中某个雇员的姓名，
根据雇员的姓名，返回该雇员的薪水值，并输出。（out参数）。

创建过程
create or replace procedure select_emp2(
name in emp.ename%type,
salary out emp.sal%type) is
begin
select sal into salary from emp where ename = name;
exception 
when no_data_found then
salary:=0;
end select_emp2;
/
调用
declare
var_sal number(5);
begin
select_emp2('&员工姓名',var_sal);
dbms_output.put_line(var_sal);
end;
/

4.编写过程，实现交换两个变量的值的功能。并输出交换前和交换后的两个值。（in out参数）

创建过程
create or replace procedure swap(
num1 in out number,
num2 in out number)
is z number(5);
begin
z:=num1;
num1:=num2;
num2:=z;
end swap;
/
调用
declare
x number:=10;
y number:=20;
begin
dbms_output.put_line('交换前x和y的值是：'||x||'  '||y);
swap(x,y);
dbms_output.put_line('交换后x和y的值是：'||x||'  '||y);
END;
/

 

5.创建存储过程，根据员工编号删除scott.emp表中的相关记录。
（提示：由调用语句提供的员工编号来删除记录，要求员工编号可随机输入。）

创建过程
create or replace procedure delete_emp(id dept.empno%type) is
begin
Delete from dept where empno = id;
end delete_emp;
/
调用
execute delete_emp('&员工编号');

 

6. 创建存储过程：输入部门编号，
输出scott.emp表中该部门所有职工的员工编号、姓名、工作岗位。
(提示：查询结果是多行，需使用游标，需把游标的定义像变量那样在过程或函数里定义，
所以游标的定义要放在如下位置：

create or replace procedure 过程名（参数）is
  游标的定义；
begin
end;
/

创建过程
create or replace procedure selcet_curemp( 
id in emp.deptno%type) is
cursor c1 is select * from emp where deptno = id;
begin
for rec in c1 loop
dbms_output.put_line(rec.empno||' '||rec.empno||' '||rec.job);
end loop;
end;
/
执行存储过程
execute selcet_curemp(10);

 

7.编写一个过程，指定一个员工编号与一个工资增加的百分比，
使emp表中将该员工的工资（sal）增加输入的百分比。

创建过程

create or replace procedure up_sal( id in number, parsent in float) is
begin
update emp set sal = sal + sal*parsent where empno = id;
end;
/

begin
up_sal(1234,0.5);
end;
/

 

8.创建函数,从scott.emp表中查询指定员工编号的职工的工资

create or replace function select_sal(id emp.empno%type) return emp.sal%type is salary emp.sal%type;
begin
select sal into salary from emp where empno = id;
return salary;
end;
/

调用函数有以下四种方式：
方式一：使用变量接收返回值
VAR salary NUMBER;
EXEC :salary:=select_sal(7369);
PRINT salary;
方式二：在SQL语句中直接调用函数
SELECT select_sal(7369) FROM DUAL;
方式三：使用DBMS_OUTPUT调用函数
EXEC dbms_output.put_line('工资是:'|| select_sal(7369));
方式四：在匿名PL/SQL块中调用函数(推荐使用该种方式-方便好记)
begin
dbms_output.put_line('工资是:'|| select_sal(7369));
end;
/

 

9.创建函数，返回scott.emp表中指定职工的工资和姓名。
（提示：返回值是两个，可用return返回一个，另一个用out参数带回）

create or replace function select_sal_name(id in emp.empno%type,v_name out emp.ename%type) 
return emp.sal%type is salary emp.sal%type;
begin
select sal,ename into salary,v_name from emp where empno = id;
return salary;
end;
/

declare
var_name emp.ename%type;
var_sal emp.sal%type;
begin
var_sal:=select_sal_name(7369,var_name);
dbms_output.put_line(var_name||'的工资为'||var_sal);
end;
/

 

10. 创建函数，根据给定的部门编号(提示: 利用&)计算该部门所有职工的平均工资。

create or replace function avg_sal(id emp.deptno%type) return number is
avgsal number(7,2);
begin
select avg(sal) into avgsal from emp where deptno = id;
return avgsal;
end;
/

begin
dbms_output.put_line(avg_sal(&deptno));
end;
/

 

11.创建函数，将scott.emp表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数.

create or replace function fun2 return number is
 begin
 update emp set sal=sal+200 where sal<(select avg(sal)  from emp);
 return sql%rowcount;
 end;
 /

begin
dbms_output.put_line(fun2);
end; 
/

 

12.创建一个函数，仅有一个形参，它接收调用函数中传递过来的实参--部门号，
函数的返回值为该部门的一整条记录信息（注意：此处能够接收一整条记录的变量该怎么定义？）。要求在调用函数中输出该部门的部门名称与位置。

create or replace function find_dept(dept_no number) return dept%rowtype
 is
v_dept dept%rowtype;
begin
select * into v_dept from dept where deptno = dept_no;
return v_dept;
end;
/

declare
v_dept dept%rowtype;
begin
v_dept:=find_dept(30);
dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);
end;
/