PL/SQL游标小练习 鬼知道我经历了什么…

怕是明天一早起来就看不懂自己的代码了= =
 

SELECT * FROM emp_pyb;
SELECT * FROM dept;
--   编写程序判断游标是否有数据，如果有数据，就输出数据，且输出“发现数据”；如果没有数据，就提示用户“输出没有数据”。
DECLARE
  CURSOR v_emp IS SELECT * FROM emp;--声明游标
  v_emp_line emp%ROWTYPE;
BEGIN
  OPEN v_emp;--开
  LOOP
    FETCH v_emp INTO v_emp_line;
    IF v_emp%NOTFOUND THEN--指针已经指到空了就进入IF
      DBMS_OUTPUT.NEW_LINE;
      DBMS_OUTPUT.PUT_LINE('输出没有数据');
    END IF;
  EXIT WHEN v_emp%NOTFOUND;--指针已经指到空就会退出循环
    DBMS_OUTPUT.PUT_LINE('发现数据');
    DBMS_OUTPUT.PUT_LINE('雇员名字为'||v_emp_line.ename||'，工资是：'||v_emp_line.sal);
  END LOOP;
  CLOSE v_emp;--关
END;
/
-- 	使用游标完成。显示员工名字，工资和部门号，并给部门为30的所有员工增加工资200.显示出所有员工信息（原工资和新工资）
DECLARE
  CURSOR v_mes IS SELECT ename,sal,deptno FROM emp_pyb FOR UPDATE;--定义游标，并使其可以修改
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_deptno emp.deptno%TYPE;
BEGIN
  OPEN v_mes;--开
  LOOP
    FETCH v_mes INTO v_ename,v_sal,v_deptno;--查询游标数据并且赋值给参数，游标指针往下移动一格
  EXIT WHEN v_mes%NOTFOUND;--指针已经指到空就会退出循环
    DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
    IF v_deptno=30 THEN--当指向的员工为30部门时进入if条件
       UPDATE emp_pyb SET sal=sal+200 WHERE CURRENT OF v_mes;--修改表中的数据，判断条件是CURRENT,就是当前游标指针指向的那一行
       SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;--上面修改完后，在查询一遍赋值给v_sal
       DBMS_OUTPUT.PUT_LINE(',新工资: '||v_sal);
    ELSE
       DBMS_OUTPUT.PUT_LINE(',新工资: '||v_sal||'---并没有加薪');
    END IF;
  END LOOP;
  CLOSE v_mes;--关
END;
/
--   定义一个参数游标来显示特定部门所有员工信息。（参数为部门号）
DECLARE
  CURSOR v_dept(v_deptno emp.deptno%TYPE := &deptno)
  IS
  SELECT * FROM emp WHERE deptno=v_deptno;
  v_dno emp%ROWTYPE;
BEGIN
  FOR v_dno IN v_dept LOOP
    DBMS_OUTPUT.PUT_LINE('员工编号:'||v_dno.empno||',姓名为:'||v_dno.ename||'，岗位是'||v_dno.job||',直属上司是'||
                                        v_dno.mgr||'，入职时间为:'||v_dno.hiredate||',工资为:'||v_dno.sal||'刀，奖金有:'||
                                        NVL(v_dno.comm,0)||'，所属部门为:'||v_dno.deptno);
  END LOOP;
END;
/
--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
BEGIN
  --UPDATE emp_pyb SET sal=sal*1.2 WHERE empno=7369;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('操作了数据');
  ELSE
    DBMS_OUTPUT.PUT_LINE('没操作数据');
  END IF;
  IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('没打开游标');
  ELSE
    DBMS_OUTPUT.PUT_LINE('已打开游标');
  END IF;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('DML操作没返回数据行');
  ELSE
    DBMS_OUTPUT.PUT_LINE('DML操作返回了数据行');
  END IF;
    DBMS_OUTPUT.PUT_LINE('更新了：'||NVL(SQL%ROWCOUNT,0)||'行');
END;
/
--2,使用游标和loop循环来显示所有部门的名称
DECLARE
    CURSOR v_dept IS SELECT dname FROM dept;
    v_dname dept.dname%TYPE;
BEGIN
    OPEN v_dept;
    LOOP
      FETCH v_dept INTO v_dname;
    EXIT WHEN v_dept%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_dname);
    END LOOP;
    CLOSE v_dept;
END;
/
--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
DECLARE
    CURSOR v_dept IS SELECT loc FROM dept;
    v_loc dept.loc%TYPE;
BEGIN
  OPEN v_dept;
  LOOP
       FETCH v_dept INTO v_loc;
  EXIT WHEN v_dept%FOUND = false;
       DBMS_OUTPUT.PUT_LINE(v_loc);
  END LOOP;
  CLOSE v_dept;
END;
/
--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
DECLARE
    CURSOR v_emp(v_deptno emp.deptno%TYPE :=&inputdeptno)
                    IS
    SELECT * FROM emp_pyb WHERE deptno=v_deptno;
    v_demp emp%ROWTYPE;
BEGIN
  FOR v_demp IN v_emp LOOP
    DBMS_OUTPUT.PUT_LINE('员工编号:'||v_demp.empno||',姓名为:'||v_demp.ename||'，岗位是'||v_demp.job||',直属上司是'||
                                        v_demp.mgr||'，入职时间为:'||v_demp.hiredate||',工资为:'||v_demp.sal||'刀，奖金有:'||
                                        NVL(v_demp.comm,0)||'，所属部门为:'||v_demp.deptno);
  END LOOP;
END;
/
--5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
DECLARE
    CURSOR v_emp(v_job emp.job%TYPE :='CLERK')
                    IS
    SELECT * FROM emp_pyb WHERE job=v_job;
    v_demp emp%ROWTYPE;
BEGIN
  FOR v_demp IN v_emp LOOP
    DBMS_OUTPUT.PUT_LINE('员工编号:'||v_demp.empno||',姓名为:'||v_demp.ename||'，岗位是'||v_demp.job||',直属上司是'||
                                        v_demp.mgr||'，入职时间为:'||v_demp.hiredate||',工资为:'||v_demp.sal||'刀，奖金有:'||
                                        NVL(v_demp.comm,0)||'，所属部门为:'||v_demp.deptno);
  END LOOP;
END;
/
--6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来
--和负一题（没序号的第二题）一样的，就不写了
--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
DECLARE
  --定义一个可以被修改的游标，查询的就直接是A OR S开头的
  CURSOR v_emp IS SELECT * FROM emp_pyb WHERE ename LIKE 'A%' OR ename LIKE 'S%' FOR UPDATE;
BEGIN
  --遍历就是了，然后直接改
  FOR i IN v_emp LOOP
    UPDATE emp_pyb SET sal=sal*1.1 WHERE CURRENT OF v_emp;
  END LOOP;
END;
/
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
DECLARE
  CURSOR v_emp IS SELECT * FROM emp_pyb WHERE job='SALESMAN' FOR UPDATE;
BEGIN
  FOR i IN v_emp LOOP
    UPDATE emp_pyb SET sal=sal+500 WHERE CURRENT OF v_emp;
  END LOOP;
END;
/
--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
DECLARE
  CURSOR v_emp IS
         SELECT e.* FROM emp_pyb e WHERE rownum<3 ORDER BY hiredate
  FOR UPDATE;
BEGIN
  FOR i IN v_emp LOOP
    UPDATE emp_pyb SET job='MANAGER' WHERE CURRENT OF v_emp;
  END LOOP;
END;
/
--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，如果增加的薪水大于300就取消加薪,并将更新前后的数据输出出来
DECLARE
  CURSOR v_mes IS SELECT ename,sal,deptno FROM emp_pyb FOR UPDATE;--定义游标，并使其可以修改
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_deptno emp.deptno%TYPE;
  v_if NUMBER;--加了多少薪
BEGIN
  OPEN v_mes;--开
  LOOP
    FETCH v_mes INTO v_ename,v_sal,v_deptno;
  EXIT WHEN v_mes%NOTFOUND;
    DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
    SELECT sal*1.2-sal INTO v_if FROM emp_pyb WHERE ename=v_ename;--查询一下加了多少薪水，保存到v_if
    IF v_if>300 THEN
       DBMS_OUTPUT.PUT_LINE(',新工资: '||v_sal||'---增加的薪水大于300  取消加薪');
    ELSE
       UPDATE emp_pyb SET sal=sal*1.2 WHERE CURRENT OF v_mes;
       SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;
       DBMS_OUTPUT.PUT_LINE(',新工资: '||v_sal);
    END IF;
  END LOOP;
  CLOSE v_mes;--关
END;
/
--11:将每位员工工作了多少年零多少月零多少天输出出来
--近似
  --CEIL(n)函数：取大于等于数值n的最小整数
  --FLOOR(n)函数：取小于等于数值n的最大整数
  --truc的用法
--不会做，看不懂
--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以现有的sal为标准
DECLARE
  CURSOR v_emp(v_deptno emp_pyb.deptno%TYPE := &dno)
  IS SELECT ename,sal,deptno FROM emp_pyb WHERE deptno=v_deptno FOR UPDATE;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_deptno emp.deptno%TYPE;
BEGIN
  OPEN v_emp;
  LOOP
    FETCH v_emp INTO v_ename,v_sal,v_deptno;
    IF v_ename IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('无此部门 或此部门无人');
      EXIT;
    END IF;
    CASE v_deptno
    WHEN 10 THEN
      DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
      UPDATE emp_pyb SET sal=sal*1.05 WHERE ename=v_ename;
      SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;
      DBMS_OUTPUT.PUT_LINE('加薪后工资(10部门加薪5%):'||v_sal);
    WHEN 20 THEN
      DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
      UPDATE emp_pyb SET sal=sal*1.1 WHERE ename=v_ename;
      SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;
      DBMS_OUTPUT.PUT_LINE(',加薪后工资(20部门加薪10%):'||v_sal);
    WHEN 30 THEN
      DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
      UPDATE emp_pyb SET sal=sal*1.15 WHERE ename=v_ename;
      SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;
      DBMS_OUTPUT.PUT_LINE(',加薪后工资(30部门加薪15%):'||v_sal);
    WHEN 40 THEN
      DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
      UPDATE emp_pyb SET sal=sal*1.2 WHERE ename=v_ename;
      SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;
      DBMS_OUTPUT.PUT_LINE(',加薪后工资(40部门加薪20%):'||v_sal);
    END CASE;--case结束
  EXIT WHEN v_emp%NOTFOUND;
  END LOOP;
  CLOSE v_emp;
END;
/
--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
DECLARE
  CURSOR v_mes IS SELECT ename,sal,deptno FROM emp_pyb FOR UPDATE;--定义游标，并使其可以修改
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_deptno emp.deptno%TYPE;
  v_avg NUMBER;
BEGIN
  OPEN v_mes;--开
  LOOP
    FETCH v_mes INTO v_ename,v_sal,v_deptno;
  EXIT WHEN v_mes%NOTFOUND;
    DBMS_OUTPUT.PUT('员工名字：'||v_ename||',员工工资：'||v_sal||',部门编号：'||v_deptno);
    SELECT AVG(sal) INTO v_avg FROM emp_pyb WHERE deptno=v_deptno;--查询出遍历到的员工部门平均工资
    IF v_sal>v_avg THEN
       UPDATE emp_pyb SET sal=sal-50 WHERE ename=v_ename;
       SELECT sal INTO v_sal FROM emp_pyb WHERE ename=v_ename;--修改完数据后再查一遍工资保存起来
       DBMS_OUTPUT.PUT_LINE('--比平均工资高，扣50: '||v_sal);
    ELSE
       DBMS_OUTPUT.PUT_LINE('--比平均工资低，工资保持为: '||v_sal||'');
    END IF;
  END LOOP;
  CLOSE v_mes;--关
END;
/