首页 > 经验记录 > PL/SQL游标小练习 鬼知道我经历了什么…

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;
/

 

           


EA PLAYER &

历史记录 [ 注意:部分数据仅限于当前浏览器 ]清空

      00:00/00:00