Oracle练习二

PL/SQL实战案例
本文提供了多个PL/SQL编程实例,包括求素数、员工薪资调整、触发器编写等,覆盖了常见业务逻辑处理和数据库操作技巧。

一、求 1-100 之间的素数

set serveroutput on;
declare
flag boolean;
begin
for i in 1..100 loop
for j in 2..i/2 loop
if mod(i,j)=0 then
flag:=false;
end if;
end loop;
if flag then
dbms_output.put_line(i);
end if;
flag:=true;
end loop;
end;
 
set serveroutput on
declare
  i number(4);
  j number(4) := 1;
  z number(4) := 0;
begin
  for i in 1..100 loop
    while j <= i/2 loop
      if mod(i,j) = 0 then 
         z := z + 1;
      end if;
      j := j + 1;
    end loop; 
    j := 1;
    if z = 1 then 
       dbms_output.put_line(i);         
    end if;  
    z := 0; 
  end loop;
end;

二、对所有员工,如果该员工职位是 MANAGER,并且在 DALLAS 工作那么就给他薪金加 15%;如果该员工职位是 CLERK,并且在 NEW YORK 工作那么就给他薪金扣除 5%;其他情况不作处理。
注意:

  • cursor语句没有写for update,就不能用WHERE CURRENT OF cursor_name子句。
  • 字段属性的值只能用单引号括起来,而且严格区分大小写。
  • 由于用FOR循环检索游标时,游标的打开、数据的检索、是否检索到数据的判断以及游标的关闭都是自动进行的,因此,可以不在声明部分定义游标,而在FOR语句中直接使用子查询。
set serveroutput on
declare
  cursor c1 is select * from emp;
  c1rec c1%rowtype;
  v_loc varchar2(20);
begin
  for c1rec in c1 loop
    select loc into v_loc from dept where deptno = c1rec.deptno;
    if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
       update emp set sal = sal * 1.15 where empno  = c1rec.empno;
    elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
       update emp set sal = sal * 0.95 where empno  = c1rec.empno;
    else
     null; 
   end if;     
  end loop;   
end;
set serveroutput on
declare
  cursor c1 is select * from emp for update of sal;
  c1rec c1%rowtype;
  v_loc varchar2(20);
begin
  for c1rec in c1 loop
    select loc into v_loc from dept where deptno = c1rec.deptno;
    if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
       update emp set sal = sal * 1.15 where current of c1;
    elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
       update emp set sal = sal * 0.95 where current of c1;
    else
     null; 
   end if;     
  end loop;   
end;

三、对所有员工,如果该员工部门是 SALES,并且工资少于 1500 那么就给他薪金加 15%;如果该员工部门是 RESEARCH,并且职位是 CLERK 那么就给他薪金增加 5%;其他情况不作处理。

set serveroutput on
declare
cursor c1 is select empno,sal,dname,job from emp e,dept d where e.deptno = d.deptno;
c1rec c1%rowtype;
begin
for c1rec in c1 loop
if c1rec.dname = 'SALES' and c1rec.sal <1500 then
update emp set sal = sal * 1.15 where empno = c1rec.empno; 
elsif c1rec.dname='RESEARCH' and c1rec.job='CLERK' then
update emp set sal = sal * 1.05 where empno = c1rec.empno; 
else
null;
end if;
end loop;
end;

四、对直接上级是'BLAKE'的所有员工,按照参加工作的时间加薪:81 年 6 月以前的加薪 10%;81 年 6 月以后的加薪 5%。

set serveroutput on
declare
  cursor c1 is select * from emp where mgr = (select empno from emp where ename='BLAKE');
  c1rec c1%rowtype;
begin
  for c1rec in c1 loop
    if c1rec.hiredate < '01-6月-81' then
       update emp set sal = sal * 1.1 where empno = c1rec.empno;
    else
       update emp set sal = sal * 1.05 where empno = c1rec.empno;     
    end if;
  end loop; 
end;

五、根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).

select deptno,ename,sal,(select count(*) + 1 from emp where deptno = a.deptno and sal > a.sal) as ord
from emp a 
order by deptno,sal desc;
set serveroutput on
declare  
  cursor cc is select * from dept;
  ccrec cc%rowtype;
  /*声明游标、(游标输入参数变量为no,数据类型为number)可选项*/
  cursor ck(no number) is select * from emp where deptno = no order by sal desc;
  ckrec ck%rowtype;
  i number;
  j number;
  v_sal number:=-1;
begin
  for ccrec in cc loop
    i := 0;
    for ckrec in ck(ccrec.deptno) loop
      i := i + 1;
      if ckrec.sal = v_sal then               
         null;
      else 
         j:=i;
      end if;
      DBMS_OUTPUT.put_line(ccrec.deptno||chr(9)||ckrec.ename||chr(9)||ckrec.sal||chr(9)||j);
      v_sal := ckrec.sal;    
    end loop;  
  end loop;
end;

六、编写一个触发器实现如下功能:
       对修改职工薪金的操作进行合法性检查:
          a) 修改后的薪金要大于修改前的薪金
          b)工资增量不能超过原工资的 10%
          c)目前没有单位的职工不能涨工资

set serveroutput on
create or replace trigger tr1 
after update of sal on emp
for each row
begin
  if :new.sal <= :old.sal then
       raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
  elsif :new.sal > :old.sal * 1.1 then
       raise_application_error(-20002,'工资增量不能超过原工资的10%');    
  elsif :old.deptno is null then
       raise_application_error(-20003,'没有单位的职工不能涨工资');           
  end if;
end;

七、编写一个 PL/SQL 程序块,对名字以"A"或"S"开始的所有雇员按他们的基本薪水的 10%加薪。

DECLARE
CURSOR c1 IS 
SELECT * FROM emp WHERE SUBSTR(ename,1,1)='A' OR SUBSTR(ename,1,1)='S' FOR UPDATE OF sal;
/*substr(字符串,截取开始位置,截取长度) 返回截取的字*/
BEGIN
FOR i IN c1
LOOP
/*NVL函数是一个空值转换函数,如果sal为空值,转换为0*/
UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;
END LOOP;
END;

八、编写一 PL/SQL,以提升两个资格最老的"职员"为"高级职员"。(工作时间越长,优先级越高)

set serveroutput on
DECLARE
CURSOR c1 IS SELECT * FROM emp WHERE job='CLERK' ORDER BY hiredate FOR UPDATE OF job;
BEGIN
FOR i IN c1
LOOP
EXIT WHEN c1%ROWCOUNT>2;
DBMS_OUTPUT.PUT_LINE(i.ename);
UPDATE emp SET job='HIGHCLERK' WHERE CURRENT OF c1;
END LOOP;
END;

九、编写一 PL/SQL,对所有雇员按他们基本薪水的 10%加薪,如果所增加的薪水大于 5000,则取消加薪。

set serveroutput on
DECLARE
CURSOR c1 IS SELECT * FROM emp for update of sal;
BEGIN
FOR i IN c1
LOOP
IF i.sal*0.1<=5000 THEN
UPDATE emp SET sal=sal+sal*0.1 where current of c1;
DBMS_OUTPUT.PUT_LINE(i.sal);
END IF;
END LOOP;
END;

十、编写一个给特殊雇员加薪 10%的过程,这之后,检查如果已经雇佣该雇员超过 60 个月,则给他额外加薪 3000.

CREATE OR REPLACE PROCEDURE Raise_Sal(no IN NUMBER) AS
vhiredate DATE;
vsal emp.sal%TYPE;
BEGIN
SELECT hiredate,sal INTO vhiredate,vsal FROM emp WHERE empno=no;
IF MONTHS_BETWEEN(SYSDATE,vhiredate)>60 THEN
vsal:=NVL(vsal,0)*1.1+3000;
ELSE
vsal:=NVL(vsal,0)*1.1;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
END;
/
/*调用存储过程*/
VARIABLE no NUMBER
BEGIN
:no:=7369;
Raise_Sal(:no);
END;
/
SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;

十一、有如下MyTable:
日期 日产
1    3.3333
2    4.2222
3    1.5555
4    9.8888
5 ………
要求用SQL语句生成如下查询
日期日产     累计日产
1    3.3333   3.3333
2    4.2222   7.5555
3    1.5555   9.0000
4    9.8888   18.8888

5………

select id,quantity,(select sum(quantity)from mytable where id<=t.id) as acount
from mytable t

十二、创建一个序列,第一次从5 循环到 10,以后再从 0 开始循环

/*创建序列*/
create sequence seq
start with 5
increment by 1
maxvalue 10
minvalue 0
cycle
nocache
/*查看已经创建的序列*/
select * from user_sequences;

十三、创建一个触发器 biu_job_emp,无论用户插入记录,还是修改 EMP 表的 job 列,都将用户指定的 job 列的值转换成大写。

CREATE OR REPLACE TRIGGER biu_job_emp
BEFORE INSERT OR UPDATE OF job ON emp
FOR EACH ROW
BEGIN
:new.job :=UPPER(:new.job);
END;

十四、创建一个函数 func_dept_name,其功能是接受职员编号后返回职员所在部门名称。

CREATE OR REPLACE FUNCTION dept_name (emp_no NUMBER)
RETURN VARCHAR2 AS
dept_no NUMBER(2);
result  dept.dname%TYPE;
BEGIN
SELECT deptno INTO dept_no FROM emp WHERE empno=emp_no;
SELECT dname INTO result FROM dept WHERE deptno = dept_no;
RETURN result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

十五、创建一个存储过程proc_dept_name,接受职员编号并检索职员姓名。将职员姓名存储在变量 empname 中,如果代码引发 VALUE_ERROR 异常,则向用户显示错误消息。

CREATE OR REPLACE PROCEDURE proc_dept_name(no IN NUMBER) AS
  empname emp.ename%TYPE;
BEGIN
  SELECT ename INTO empname FROM emp WHERE empno=no;
  DBMS_OUTPUT.PUT_LINE('职员姓名:'||empname);
  EXCEPTION
     WHEN VALUE_ERROR THEN
     DBMS_OUTPUT.PUT_LINE('要存储在变量中的值过大');  
END;
/
set serveroutput on
/*调用存储过程*/
VARIABLE no NUMBER
BEGIN
:no:=7369;
proc_dept_name(:no);
END;
/

十六、利用游标显示工资最高的前3 名雇员的名称和工资。

SET SERVEROUTPUT ON  
DECLARE  
  V_ename VARCHAR2(10);   
  V_sal NUMBER(5);   
  CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;   
BEGIN  
  OPEN emp_cursor;   
  FOR I IN 1..3 LOOP   
    FETCH emp_cursor INTO v_ename,v_sal;   
    DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);   
  END LOOP;   
  CLOSE emp_cursor;   
END;  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值