--1、编写一个函数,输入员工编号将每位员工工作了多少年零多少月零多少天输出来:如
-- 年份:XXXX 月份:XX 天数:XX
create or replace procedure proc_workofyear(eno number)
is
cursor curs_workofyear is select ename ,
floor(months_between(sysdate,hiredate)/12) as wyear,
mod(floor(months_between(sysdate,hiredate)),12) as wmonth,
mod(floor(sysdate-hiredate),30) as wday
FROM EMP where empno=eno;
begin
for i in curs_workofyear loop
dbms_output.put_line(i.ename||':工作了'||i.wyear||'年'||i.wmonth||'月'||i.wday||'天。');
end loop;
end;
declare
v_empno number(10):=&empno;
begin
proc_workofyear(v_empno);
end;
--2、编写一个存储过程,输入某位员工的编号,如果某个员工的工资低于200 元,就添加100 元,否则添加50。
create or replace procedure proc_emp(eno number)
is
cursor curs_emp is select sal from emp where empno=eno for update;
begin
for i in curs_emp
loop
if i.sal>200 then
update emp set sal=sal+100 where current of curs_emp;
else
update emp set sal=sal+50 where current of curs_emp;
end if;
end loop;
end;
declare
v_empno emp.empno%type :=&empno;
begin
proc_emp(v_empno);
end;
--3、编写一个存储过程,可以输入部门号,并显示该部门所有员工姓名和工
create or replace procedure proc_dept(dno in number,ename out varchar2,sal out varchar2)
is
cursor curs_dept is select ename,sal into ename,sal from emp where deptno=dno;
begin
for i in curs_dept loop
dbms_output.put_line('员工姓名:'||i.ename||' 工资:'||i.sal);
end loop;
end;
declare
v_deptno emp.deptno%type :=&deptno;
v_ename varchar2(10);
v_sal emp.sal%type;
begin
proc_dept(v_deptno,v_ename,v_sal);
end;
-- 年份:XXXX 月份:XX 天数:XX
create or replace procedure proc_workofyear(eno number)
is
cursor curs_workofyear is select ename ,
floor(months_between(sysdate,hiredate)/12) as wyear,
mod(floor(months_between(sysdate,hiredate)),12) as wmonth,
mod(floor(sysdate-hiredate),30) as wday
FROM EMP where empno=eno;
begin
for i in curs_workofyear loop
dbms_output.put_line(i.ename||':工作了'||i.wyear||'年'||i.wmonth||'月'||i.wday||'天。');
end loop;
end;
declare
v_empno number(10):=&empno;
begin
proc_workofyear(v_empno);
end;
--2、编写一个存储过程,输入某位员工的编号,如果某个员工的工资低于200 元,就添加100 元,否则添加50。
create or replace procedure proc_emp(eno number)
is
cursor curs_emp is select sal from emp where empno=eno for update;
begin
for i in curs_emp
loop
if i.sal>200 then
update emp set sal=sal+100 where current of curs_emp;
else
update emp set sal=sal+50 where current of curs_emp;
end if;
end loop;
end;
declare
v_empno emp.empno%type :=&empno;
begin
proc_emp(v_empno);
end;
--3、编写一个存储过程,可以输入部门号,并显示该部门所有员工姓名和工
create or replace procedure proc_dept(dno in number,ename out varchar2,sal out varchar2)
is
cursor curs_dept is select ename,sal into ename,sal from emp where deptno=dno;
begin
for i in curs_dept loop
dbms_output.put_line('员工姓名:'||i.ename||' 工资:'||i.sal);
end loop;
end;
declare
v_deptno emp.deptno%type :=&deptno;
v_ename varchar2(10);
v_sal emp.sal%type;
begin
proc_dept(v_deptno,v_ename,v_sal);
end;