PL/SQL基础
一.入门
1.打印helloworld
DECLARE --说明部分
BEGIN
dbms_output.put_line ('Hello,world') ;
END ;
二.变量
2.引用型变量,查询并打印7835的姓名和薪水
DECLARE --定义变量保存姓名和薪水
pname emp.ename % TYPE ; psal emp.sal % TYPE ;
BEGIN
--得到员工的姓名和薪水
SELECT ename,sal
INTO pname,psal
FROM emp
WHERE
empno = 7839 ;
dbms_output.put_line (pname || '的薪水是' || psal) ;
END ;
3.记录型变量
DECLARE emp_rec emp % rowtype ;
BEGIN
SELECT
* INTO emp_rec
FROM
emp
WHERE
empno = 7839 ; dbms_output.put_line (
emp_rec.ename || '的薪水是' || emp_rec.sal
) ;
END ;
三.条件与循环
4.条件判断
DECLARE pnum NUMBER := 1 ;
BEGIN
IF pnum = 0 THEN
dbms_output.put_line (
'您输入的数字是0'
) ;
ELSIF pnum = 1 THEN
dbms_output.put_line (
'您输入的数字是1'
) ;
ELSE
dbms_output.put_line ('其它数字') ;
END IF;
END ;
5.while循环
DECLARE pnum NUMBER := 1 ;
BEGIN
LOOP
--退出循环条件
EXIT WHEN pnum > 10;
dbms_output.put_line (pnum) ;
pnum := pnum + 1;
END LOOP;
END ;
四.光标
6.光标的属性
1)%isopen 是否被打开
2)%rowcount(影响的行数)
3)%found有记录
4)%notfound没有记录
7.查询并打印员工的姓名和薪水
DECLARE
--定义光标
CURSOR cemp is SELECT ename,sal FROM emp;
pname emp.ename%TYPE;
psal emp.sal%TYPE;
BEGIN
--打开光标
OPEN cemp;
LOOP
--取出当前记录
FETCH cemp INTO pname,psal;
EXIT WHEN cemp%notfound;
dbms_output.put_line(pname||'的薪水是'||psal);
END LOOP;
CLOSE cemp;
END ;
8.给员工涨工资
DECLARE
--定义光标
CURSOR cemp IS SELECT empno,job FROM emp;
pempno emp.empno%TYPE;
pjob emp.job%TYPE;
BEGIN
--打开光标
OPEN cemp;
LOOP
--取一个员工
FETCH cemp INTO pempno,pjob;
EXIT WHEN cemp%NOTFOUND;
IF
pjob = 'PRESIDENT' THEN UPDATE emp SET sal = sal+1000 WHERE empno=pempno;
ELSIF pjob = 'MANAGER' THEN UPDATE emp SET sal = sal+800 WHERE empno=pempno;
ELSE UPDATE emp SET sal = sal+400 WHERE empno=pempno;
END IF;
END LOOP;
--关闭光标
CLOSE cemp;
--提交
COMMIT;
dbms_output.put_line('完成');
END;
9.形参和实参
DECLARE
--形参
CURSOR cemp(dno NUMBER) IS SELECT ename FROM emp WHERE deptno=dno;
pename emp.ename%TYPE;
BEGIN
--实参
OPEN cemp(10);
LOOP
FETCH cemp INTO pename;
EXIT WHEN cemp%NOTFOUND;
dbms_output.put_line(pename);
END LOOP;
CLOSE cemp;
END;
五.例外
10.被0除异常
DECLARE
pnum NUMBER;
BEGIN
pnum := 1/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN dbms_output.put_line('1:0不能做分母');
dbms_output.put_line('2:0不能做分母');
WHEN VALUE_ERROR THEN dbms_output.put_line('算术或者转换错误');
WHEN OTHERS THEN dbms_output.put_line('其它例外');
END;
11.自定义异常
DECLARE
CURSOR cemp IS SELECT ename FROM emp WHERE deptno = 50;
pename emp.ename%TYPE;
--自定义意外
no_emp_found EXCEPTION;
BEGIN
OPEN cemp;
--取第一条记录
FETCH cemp INTO pename;
IF cemp%NOTFOUND THEN
--抛出异常
RAISE no_emp_found;
END IF;
CLOSE cemp;
EXCEPTION
WHEN no_emp_found THEN dbms_output.put_line('没有找到员工');
WHEN OTHERS THEN dbms_output.put_line('其它例外');
END;
六.实例
1.统计每年入职员工的个数
DECLARE
--定义光标
CURSOR cemp IS SELECT TO_ChAR(hiredate,'yyyy') FROM emp;
phiredate VARCHAR2(4);
count80 NUMBER := 0;
count81 NUMBER := 0;
count82 NUMBER := 0;
count87 NUMBER := 0;
BEGIN
--打开光标
OPEN cemp;
LOOP
--取一个员工的入职年份
FETCH cemp INTO phiredate;
EXIT WHEN cemp%NOTFOUND;
IF phiredate = '1980' THEN count80 := count80+1;
ELSIF phiredate = '1981' THEN count81 := count81+1;
ELSIF phiredate = '1982' THEN count82 := count82+1;
ELSE count87 := count87+1;
END IF;
END LOOP;
--关闭光标
CLOSE cemp;
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line('1980:'||count80);
dbms_output.put_line('1981:'||count81);
dbms_output.put_line('1982:'||count82);
dbms_output.put_line('1987:'||count87);
END;
2.涨工资
DECLARE
--定义光标
CURSOR cemp IS SELECT empno,sal FROM emp ORDER BY sal;
pempno emp.empno%TYPE;
psal emp.sal%TYPE;
--涨工资的人数
countEmp NUMBER := 0;
--涨后的工资总额
salTotal NUMBER;
BEGIN
--得到工资的初始值
SELECT sum(sal) INTO salTotal FROM emp;
--打开光标
OPEN cemp;
IF salTotal < 70000 THEN
LOOP
--涨工资总额大于7万
EXIT WHEN salTotal > 70000;
--取一个员工
FETCH cemp INTO pempno,psal;
--notfound
EXIT WHEN cemp%NOTFOUND;
--涨工资
UPDATE emp SET sal = sal * 1.1 WHERE empno = pempno;
--人数+1
countEmp := countEmp + 1;
--涨后的工资总额
salTotal := salTotal + psal*0.1;
END LOOP;
END IF;
--关闭光标
CLOSE cemp;
COMMIT;
dbms_output.put_line('人数:'||countEmp);
dbms_output.put_line('总额度:'||salTotal);
END;
