Oracle练习5

1. 创建一个过程，能向dept表中添加一个新记录。（in参数）P134,7-5
   创建过程
   create or replace procedure p1(
   vdno in number,
   vdname in varchar2,
   vloc in varchar2) is
   begin
   insert into dept values(vdno,vdname,vloc);
   commit;
   dbms_output.put_line(‘insert successfully’);
   end p1;
   /
   调用过程
   begin
   p1(vdno=>79,vdname=>‘Market Extends’,vloc=>‘HongK’);
   end;
   /

2. 从scott.emp表中查询给定职工（提示：使用&来输入员工编号）的职工姓名和工资。（要求：利用out模式的参数将值传给调用者。）
   创建过程
   create or replace procedure p1(
   veno in out emp.empno%type,
   vename out emp.ename%type,
   vsal out emp.sal%type) is
   begin
   veno :=&Empno;
   select ename,sal into vename,vsal from emp
   where empno=veno;
   end p1;
   /
   调用过程
   declare
   veno emp.empno%type;
   vename emp.ename%type;
   vsal emp.sal%type;
   begin
   p1(veno,vename,vsal);
   dbms_output.put_line(‘Ename:’||vename||’ Sal: '||vsal);
   end;
   /

3. 创建一个过程，在执行调用过程时，可随机输入emp表中某个雇员的姓名，根据雇员的姓名，返回该雇员的薪水值，并输出。（out参数）。
   创建过程
   create or replace procedure p1(
   veno in emp.empno%type,
   vename out emp.ename%type,
   vsal out emp.sal%type) is
   begin
   select ename,sal into vename,vsal from emp
   where empno=veno;
   end p1;
   /
   调用过程
   declare
   veno emp.empno%type:=&Empno;/在调用时输入；/
   vename emp.ename%type;
   vsal emp.sal%type;
   begin

p1(veno,vename,vsal);
dbms_output.put_line(‘Ename:’||vename||’ Sal: '||vsal);
end;
/

4. 编写过程，实现交换两个变量的值的功能。并输出交换前和交换后的两个值。（in out参数）
   创建过程
   create or replace procedure p1(
   veno1 in out emp.empno%type,
   veno2 in out emp.empno%type) is
   temp emp.empno%type:=0;
   begin
   veno1 :=&Empno1;
   veno2 :=&Empno2;
   dbms_output.put_line(‘Veno1:’||veno1||’ Veno2:’||veno2);
   temp :=veno1;
   veno1 :=veno2;
   veno2:=temp;
   end p1;
   /
   调用过程
   declare
   veno1 emp.empno%type;
   veno2 emp.empno%type;

begin

p1(veno1,veno2);
dbms_output.put_line(‘Veno1:’||veno1||’ Veno2:’||veno2);
end;
/
5. 创建存储过程，根据员工编号删除scott.emp表中的相关记录。（提示：由调用语句提供的员工编号来删除记录，要求员工编号可随机输入。）
创建过程
create or replace procedure p1(
veno in emp.empno%type
) is

begin
delete from emp
where empno=veno;
dbms_output.put_line(‘Delete successfully’);
end p1;
/

调用过程
declare
veno emp.empno%type;
begin
veno :=&Empno;
p1(veno);
end;
/
6. 创建存储过程：输入部门编号，输出scott.emp表中该部门所有职工的员工编号、姓名、工作岗位。（提示：查询结果是多行，需使用游标，需把游标的定义像变量那样在过程或函数里定义，所以游标的定义要放在如下位置：
创建过程
create or replace procedure p1(vdeptno in emp.empno%type) is
cursor c1 is
select * from emp
where deptno=vdeptno;

emp2 emp%rowtype;

begin
open c1;
fetch c1 into emp2;/不要into错位置/
while c1%found loop
dbms_output.put_line(‘雇员信息:’);
dbms_output.put_line(‘Empno:’||emp2.empno||’ Name:’||emp2.ename||’ Job:’||emp2.job);
fetch c1 into emp2;
end loop;
close c1;
end;
/
调用过程
declare
vdeptno emp.empno%type;
begin
vdeptno :=&Empno;
p1(vdeptno);
end;
/

7. 编写一个过程，指定一个员工编号与一个工资增加的百分比，使emp表中将该员工的工资（sal）增加输入的百分比。
   创建过程
   create or replace procedure p1 is
   vempno emp.empno%type:=7369;
   percent number:=0.4;
   vtemprow emp%rowtype;/begin下面不可以再定义临时需要用的变量了/
   begin
   update emp set sal=sal*(1+percent) where empno=vempno;

select * into vtemprow from emp where empno=vempno;
dbms_output.put_line(‘Empno:’||vempno||’ Sal:’||vtemprow.sal||’ Percent:0’||percent);
end;
/
调用过程
declare

begin
p1;
end;
/
8. 创建函数，从scott.emp表中查询指定员工编号的职工的工资。
创建函数
create or replace function f1(vempno number) return number is
vsal number;

begin
select sal into vsal from emp where empno=vempno;
dbms_output.put_line(‘Sal:’||vsal);
return vsal;
end;
/
调用函数
declare
vempno number;
begin
vempno:=f1(7369);
end;
/
9. 创建函数，返回scott.emp表中指定职工的工资和姓名。（提示：返回值是两个，可用return返回一个，另一个用out参数带回）
创建函数
create or replace function f2(vempno in number,vsal out number) return varchar2 is

vename emp.ename%type;
begin

select ename,sal into vename,vsal from emp where empno=vempno;
return vename;
end;
/
调用函数
declare
vname emp.ename%type;
vsal emp.sal%type;
begin
vname:=f2(7369,vsal);
dbms_output.put_line(vname||’:’||vsal);
end;
/
10. 创建函数，根据给定的部门编号(提示: 利用&)计算该部门所有职工的平均工资。
创建函数
create or replace function f1(vdeptno number) return number is

avgsal number;

begin
select avg(sal) into avgsal from emp where deptno=vdeptno;

return avgsal;
end;
/
调用函数
declare
vsal emp.sal%type;
vdeptno number:=&Deptno;
begin
vsal:=f1(vdeptno);
dbms_output.put_line(‘Avg Sal:’||vsal);
end;
/
11. 创建函数，将scott.emp表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数。
创建函数
create or replace function f1 return number is /若无参数，f1就不带括号/
begin
update emp set sal=sal+200 where sal<(select avg(sal) from emp);

return sql%rowcount;
end;
/
调用函数
declare
vcount number;
begin
vcount:=f1;
dbms_output.put_line(‘修改工资人数:’||vcount);
end;
/
12. 创建一个函数，仅有一个形参，它接收调用函数中传递过来的实参–部门号，函数的返回值为该部门的一整条记录信息（注意：此处能够接收一整条记录的变量该怎么定义？）。要求在调用函数中输出该部门的部门名称与位置。
创建函数 /为我视为不可思议的写法/
create or replace function f1(vdeptno number) return dept%rowtype is
rowtemp dept%rowtype;
begin
select * into rowtemp from dept where deptno=vdeptno;
return rowtemp;
end;
/
调用函数
declare
rowtemp dept%rowtype;
begin
rowtemp:=f1(&Deptno);
dbms_output.put_line(‘Dname:’||rowtemp.dname||’ Loca:’||rowtemp.loc);
end;
/