2024年7月11日 练习

--无参
create or replace procedure p111 
is
begin
  dbms_output.put_line('hello world!!!');
exception
  when others then
    dbms_output.put_line('11');
end;
/
begin
  p111();
end;
call p111();
--有参（in）
create or replace procedure p2(ep in number)
is
  a varchar2(30);
  b number;
begin
  select job,sal into a,b from emp
where empno=ep;
  dbms_output.put_line(a||b);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
begin
  p2(7369);
end;
call p2(7369);
-----------------------------------------------
--有参（out）
create or replace procedure p3(ep out varchar2)
is
begin
  select 'hello world' into ep from dual;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

declare
  a varchar2(30);
begin
  p3(a);
  dbms_output.put_line(a);
end;
---------------------------------------------------
--有参（in/out）
create or replace procedure p41
(ep in number,jj out emp.job%type,sl out number)
is
begin
  select job,sal into jj,sl from emp
where empno=ep;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
declare
  z number := 7369;
  a varchar2(30);
  b number;
begin
  p41(z,a,b);
  dbms_output.put_line(a||'-------'||b);
end;

---------------------------------------------
--有参（in out）
empno,sal
create or replace procedure p5(ep in out number)
is
begin
  select sal into ep from emp
where empno=ep;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

declare
  z number := 7369;
begin
  p5(z);
  dbms_output.put_line(z);
end;
-----------------------------------------------------
--有参（in out）
empno,v_emp
create or replace procedure p6
(ep in number,v_emp out emp%rowtype)
is
begin
  select * into v_emp from emp
where empno=ep;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

declare
  z number := 7369;
  v emp%rowtype;
begin
  p6(z,v);
  dbms_output.put_line(v.ename||v.sal);
end;
------------------------------------------------------
--1.创建一个存储过程，以员工号为参数，输出该员工的工资：
--有参（in）
create or replace procedure p2(eno in number)
is
  a varchar2(30);
  b number;
begin
  select sal into b from emp
where empno=eno;
  dbms_output.put_line(b);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
begin
  p2(&eno);
end;
--2.创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，
--则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250； 若属于其他部门，则增加300。
create or replace procedure upd(eno in number)
is
den number;
begin
  select deptno into den from emp where empno=eno;
  if den=10 then
    update emp set sal=sal+150 where empno=eno;
  elsif den=20 then
    update emp set sal=sal+200 where empno=eno;
  elsif den=30 then
    update emp set sal=sal+250 where empno=eno;
  else
    update emp set sal=sal+300 where empno=eno;
  end if;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
declare
   eno number:=7749;
begin
   upd(eno);
end;

--3.创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）：
create or replace procedure gznx(eno in number)
is
  a varchar2(30);
  b date;
begin
  select hiredate into b from emp
where empno=eno;
  dbms_output.put_line(b);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
begin
  gznx(&eno);
end;

--4.创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息：
create or replace procedure ygxx_4(dno in number)
is
begin

    for emp_rec in (
        select empno, ename, hiredate
        from emp
        where deptno = dno
        order by hiredate
    )
    loop
        dbms_output.put_line( emp_rec.empno || emp_rec.ename ||to_char(emp_rec.hiredate, 'yyyy-mm-dd'));
    end loop;
exception
    when no_data_found then
        dbms_output.put_line('未找到部门号 ' || dno || ' 的员工信息。');
    when others then
        dbms_output.put_line('发生其他异常: ' || sqlerrm);
end;
begin
ygxx_4(&dno);
end;

--5.创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。
--如果修改成功，则显示 “员工由……号部门调入……号部门”；如果不存在该员工，则显示 “员工号不存在，请输入正确的员工号”。
--如果不存在该部门，则显示 “该部门不存在，请输入正确的部门号”：
select * from emp
create or replace procedure ygxx_5(dno in number, eno in number)
is
    dno1 emp.deptno%type;
begin
    select count(deptno) into dno1
    from emp
    where empno = eno;
    if dno1 =0 then
        dbms_output.put_line(eno || ' 不存在，请输入正确的员工号');
        return;
    end if;
    select count(deptno) into dno1
    from dept
    where deptno = dno;
    if dno1=0 then
        dbms_output.put_line( dno||' 不存在，请输入正确的部门号');
        return;
    end if;
    select deptno into dno1
    from emp
    where empno=eno;
    update emp set deptno = dno where empno = eno;
    dbms_output.put_line('员工号 ' || eno || ' 由 ' || dno1 || ' 号部门调入 ' || dno || ' 号部门');
exception
    when no_data_found then
        dbms_output.put_line('未找到相关信息。');
    when others then
        dbms_output.put_line('发生异常: ' || sqlerrm);
end;
begin
ygxx_5(&dno,&eno);
end;
--定义一个PL/SQL块，键入两个值，计算这两个数的和与这两个数的差的比值，
--如果除数为零，将直接报错，错误代码为‘-20200’，错误信息为‘两个值相同，请重新输入！’：
create or replace procedure p_5(n in number, n1 in number)
is
    v_ number;
begin 
    if n - n1 = 0 then
        raise_application_error(-20200, '两个值相同，请重新输入！');
    else
        v_ := (n + n1) / (n - n1);
        dbms_output.put_line('计算结果：' || v_);
    end if;
exception
    when others then
        raise_application_error(-20202, '发生其他异常：' || sqlerrm);
end;
begin
    p_5(&n, &n1);
end;

--6.创建一个存储过程，以一个整数为参数，输出工资最高的前几个（参数值）员工的信息：
create or replace procedure e6(n number)
is 
  cursor mycur is select * from emp order by sal desc;
begin
  for x in mycur loop
    if mycur%rowcount<=n then
      dbms_output.put_line(x.ename||','||x.sal);
    end if;
  end loop;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end; 

begin
  e6(8);
end;