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