ORACLE PL SQL基本语句及循环语句练习

--PL SQL 练习
-- 基本语句：定义 - 执行 
DECLARE
  a INT;
  b INT;
BEGIN
    a := 2;
    b := 3;
    DBMS_OUTPUT.PUT(a+b);
END;

-- 仅包含执行语句
BEGIN
   DBMS_OUTPUT.PUT_LINE(2+2);
END;

-- 定义时赋值
DECLARE
    a NUMBER := 2;
    b NUMBER := 2;
BEGIN
    DBMS_OUTPUT.PUT_LINE(a+b);
END; 

--定义 - 执行 - 例外处理
DECLARE
    a NUMBER := &A;
    b NUMBER := &B;
BEGIN
    DBMS_OUTPUT.PUT_LINE(a/b);
EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('除数不得为0');
END;

-- SELECT INTO 语句
DECLARE
    v_aname VARCHAR2(20) := 'edward';
BEGIN
    --查询需要用 （SELECT ... INTO 变量 ）语句
    DBMS_OUTPUT.PUT_LINE(v_aname);
    SELECT ename INTO v_aname FROM emp WHERE empno = 7788;
    DBMS_OUTPUT.PUT_LINE(v_aname);
    
    --可以正常执行增删改
    INSERT INTO new_emp (empno, ename) values (9999, 'EDWARD');
    UPDATE new_emp SET sal = 9999 WHERE empno = 9999;
    DELETE FROM new_emp WHERE empno = 9999;
END;

    --SELECT * FROM new_emp;
   
--复合类型
--记录类型 
DECLARE
    TYPE e IS RECORD(
         v_empno NUMBER(4),
         v_ename VARCHAR2(10),
         v_job VARCHAR2(9)
    );
    x e;
BEGIN
  SELECT empno, ename, job INTO x FROM new_emp WHERE empno=7788;
  
         DBMS_OUTPUT.PUT_LINE(x.v_empno);
         DBMS_OUTPUT.PUT_LINE(x.v_ename);
         DBMS_OUTPUT.PUT_LINE(x.v_job);
      
  SELECT empno, ename, job INTO x FROM new_emp WHERE empno=8888;
         SELECT empno, ename, job INTO x FROM new_emp WHERE empno=7788;
  
         DBMS_OUTPUT.put_line('---------------------------------------------------------');

         DBMS_OUTPUT.PUT_LINE(x.v_empno);
         DBMS_OUTPUT.PUT_LINE(x.v_ename);
         DBMS_OUTPUT.put_line(x.v_job);      
  
END;    

--引用类型
DECLARE
         x new_emp%ROWTYPE;
BEGIN
         SELECT * INTO x FROM new_emp WHERE empno = 7788;
         DBMS_OUTPUT.PUT_LINE(x.empno);
         DBMS_OUTPUT.PUT_LINE(x.ename);
         DBMS_OUTPUT.PUT_LINE(x.job);
         DBMS_OUTPUT.PUT_LINE(x.sal);
         DBMS_OUTPUT.PUT_LINE(x.comm);
         DBMS_OUTPUT.PUT_LINE(x.deptno);

END;

--表类型
DECLARE
         TYPE a IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
         x a;
BEGIN
         x(-0) := 'a';
         x(-1) := 'b';
         x(20) := 'c';         
         
         DBMS_OUTPUT.PUT_LINE(x(-0));
         DBMS_OUTPUT.PUT_LINE(x(-1));
         DBMS_OUTPUT.PUT_LINE(x(20));
         DBMS_OUTPUT.PUT_LINE(x.COUNT);
        
END;

--IF 条件语句
DECLARE
     a NUMBER := &A;
     b NUMBER := &B;
     c NUMBER := &C;
BEGIN
     IF a<b THEN
          a := b;
     END IF;
     IF a<c THEN
          a := c;
     END IF;
          DBMS_OUTPUT.PUT_LINE(a);
END;

--IF ELSIF ELSE 语句
DECLARE
          age NUMBER := &A; 
BEGIN
          IF        age<=0 THEN DBMS_OUTPUT.PUT_LINE('没出生呢！');
          ELSIF age<10 THEN DBMS_OUTPUT.PUT_LINE('碎娃');
          ELSIF age<20 THEN DBMS_OUTPUT.PUT_LINE('少年');
          ELSIF age<30 THEN DBMS_OUTPUT.PUT_LINE('快去奋斗吧');
          ELSIF age<100 THEN DBMS_OUTPUT.PUT_LINE('享受生活吧');
          ELSIF age<180 THEN DBMS_OUTPUT.PUT_LINE('老不死的！');
          ELSE DBMS_OUTPUT.PUT_LINE('妖精来了，快跑');
          END IF;
END;

--CASE WHEN THEN练习：
SELECT emp.*, 
        CASE
        WHEN sal<1000 THEN '屌丝'
        WHEN sal<2000 THEN '还是屌丝'
        WHEN sal<3000 THEN '平民'
        WHEN sal<4000 THEN '脱离屌丝'
        WHEN sal<5000 THEN '可以谈恋爱了'
        ELSE '在西安还能活得下去'
        END  工资等级
FROM emp;

    /*
      查询员工的名字以及入职了多少年，并按入职年份划分出级别：
        入职1年以内的 是 职场菜鸟
        入职5年以内的 是 职场老手
        入职10年以内的 是 职场油条
        其它都是 元老*/

SELECT  emp.*, 
CASE
        WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM hiredate) <=10 THEN '职场菜鸟'
        WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM hiredate) <=25 THEN '职场老手'
        WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM hiredate) <=30 THEN '职场油条'
        ELSE '元老'
END 工龄
FROM emp;

--利用loop循环，计算 1+2+3+4+5+....+100 的和
DECLARE
     i NUMBER := 1;
     s NUMBER := 0;
BEGIN
     LOOP
              s := s + i;
              i := i+1;
              EXIT WHEN i>100;
     END LOOP;
              DBMS_OUTPUT.PUT_LINE(s);
END;

-- 1 - 2 + 3 - 4 ... -100
DECLARE
     i NUMBER := 1;
     s NUMBER := 1;       
BEGIN
     LOOP
              i := i+1;
              IF mod(i, 2)=0     THEN  s := s - i;
              ELSE  s := s + i;
              END IF;
              EXIT WHEN i=100;
     END LOOP;
              DBMS_OUTPUT.PUT_LINE(s);         
END;

/*从键盘输入一个数字，利用for in 循环，输出一个该数字的所有约数。
比如 12的约数是： 1 2 3 4 6 12
比如 20的约数是： 1 2 4 5 10 20*/

DECLARE
           i NUMBER := &I;
BEGIN
           DBMS_OUTPUT.PUT_LINE(i || '的约数有：1');   
           FOR j IN 1..i LOOP
                   IF mod(i, j)=0 THEN DBMS_OUTPUT.PUT_LINE('，' || j);   
                   END IF;
           END LOOP;        
END;

-- --------------------------------------------------------------------------
--S = 2 + 22 + 222 + 2222 + 22222 ..  个数由用户自己设定
           --Lpad 方法.
DECLARE
           i  NUMBER := &A;
           j VARCHAR2(100) := '2';
           k VARCHAR2(100) := 'S=2';
BEGIN
           FOR z IN 2..i LOOP
                 j := lpad(j, i, 2);
                 k := k || '+' || j;
           END LOOP;
           DBMS_OUTPUT.PUT_LINE(k);
END;
          --数学规律方法
DECLARE
           i NUMBER := &A;
           j NUMBER := 2;
           k VARCHAR2(100) := 'S=2';
BEGIN
           FOR z IN 2..i LOOP
                 j := j*10 + 2;
                 k := k || '+'||j;
           END LOOP;
                  DBMS_OUTPUT.PUT_LINE(k);
END;
--1. 练习
-- table 和 %rowtype 的配合
DECLARE
     TYPE t_a IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     a1 t_a;    
BEGIN
     SELECT * BULK COLLECT INTO a1 FROM emp;
     DBMS_OUTPUT.PUT_LINE(a1(1).empno || '，'||a1(1).ename);
     DBMS_OUTPUT.PUT_LINE(a1(2).empno || '，'||a1(2).ename);
END;

-- table 和 record 的配合
DECLARE
     TYPE r_a IS RECORD (
          v_empno NUMBER,
          v_ename VARCHAR2(10)
     );
     TYPE t_a IS TABLE OF R_A INDEX BY BINARY_INTEGER;
     a2 t_a;
BEGIN
     SELECT empno, ename BULK COLLECT INTO a2 FROM emp;
     DBMS_OUTPUT.PUT_LINE(a2(1).v_empno || '，'||a2(1).v_ename);
     DBMS_OUTPUT.PUT_LINE(a2(2).v_empno || '，'||a2(2).v_ename);
END;

--2. 练习DML语句的返回值!
DECLARE
     v_empno NUMBER;
     v_ename VARCHAR2(10);
BEGIN
     INSERT INTO new_emp (empno, ename) VALUES (9876, 'Tomie') RETURNING empno, ename INTO v_empno, v_ename;
     UPDATE new_emp SET ename = 'Tommie' WHERE empno = 9876 RETURNING empno, ename INTO v_empno, v_ename;
     DELETE FROM new_emp WHERE empno = 9876 RETURNING empno, ename INTO v_empno, v_ename;
     DBMS_OUTPUT.PUT_LINE(v_empno||'，'||v_ename);
END;

--3. 输出1到100之间的所有质数.
DECLARE
   i NUMBER;                                                    --计数器
BEGIN
   FOR j IN 2..100 LOOP                                   --从 2 开始一直到 100循环
         i := 0;                                                        --每次循环开始，将计数器重置
         FOR k IN 2..(j-1) LOOP                           
             IF (mod(j, k) = 0) THEN i := i+1;       --发现一个约数，计数器 +1
             END IF;
          END LOOP;   
               IF i=0 THEN DBMS_OUTPUT.PUT_LINE(j);
               END IF;
   END LOOP;
END;