1. 数据类型
DECLARE
v_empno employee.empno%TYPE :=7788;
v_rec employee%ROWTYPE;
BEGIN
SELECT * INTO v_rec FROM employee WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE
('姓名:'||v_rec.ename||'工资:'||v_rec.sal||'工作时间:'||v_rec.hiredate);
END;
2. 显示变量v_counter的值,如果该变量小于10,则增加10并显示该变量改变后的值。
DECLARE
v_counter NUMBER := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:'||v_counter);
IF v_counter >= 10 THEN
NULL; --为了使语法变得有意义,去掉NULL会报语法错误
ELSE
v_counter := v_counter + 10;
DBMS_OUTPUT.PUT_LINE('v_counter的改变后值为:'||v_counter);
END IF;
END;
3. 预定义异常
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员号不正确');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('查询只能返回单行');
WHEN OTHERS THEN
dbms_output.put_line('错误号:'||SQLCODE||'错误描述:'||SQLERRM);
END;
4. 查询编号为7788的雇员的福利补助(comm列)。
DECLARE
v_comm employee.comm%TYPE;
e_comm_is_nullEXCEPTION; --定义异常类型变量
BEGIN
SELECTcomm INTO v_comm FROM employee WHERE empno=7788;
IFv_comm IS NULL THEN
RAISEe_comm_is_null;
ENDIF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在!错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('该雇员无补助');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
5. 使用显式游标输出每个员工的姓名和薪水。
DECLARE
nameemployee.ename%type;
salemployee.sal%type; --定义两个变量来存放ename和sal的内容
CURSOR emp_cursor IS
SELECT ename,sal
FROM employee;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO name,sal;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('第'||emp_cursor%ROWCOUNT||'个雇员:'||name||sal);
END LOOP;
CLOSE emp_cursor;
END;
6. 循环游标的用法。
--显示雇员表中所有雇员的姓名和薪水
DECLARE
CURSORemp_cursor IS
SELECT ename,sal FROM employee;
BEGIN
FORemp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE
('第'||emp_cursor%ROWCOUNT||'个雇员:'
||emp_record.ename|| emp_record.sal);
ENDLOOP;
END;
7. 多表查询更新时,更新表为锁定行所在表。
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal
FROM employee e INNER join dept d
ON e.deptno=d.deptno
FOR UPDATE OF sal;
v_emp emp_cursor%ROWTYPE;
BEGIN
IFNOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%NOTFOUND;
UPDATE employee
SET sal=sal+200
WHERE CURRENT OF emp_cursor;
END LOOP;
CLOSE emp_cursor;
END;
8. 添加员工记录。
CREATE OR REPLACE PROCEDURE add_employee(
eno NUMBER, --输入参数,雇员编号
name VARCHAR2, --输入参数,雇员名称
salary NUMBER, --输入参数,雇员薪水
job VARCHAR2 DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'
dno NUMBER --输入参数,雇员部门编号
)
IS
BEGIN
INSERT INTO employee
(empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
END;
9. sql*plus下调用存储过程
--EXEC add_employee(1111,'MARY',2000,'MANAGER',10);
--EXECadd_employee(dno=>10,name=>'MARY',salary=>2000,eno=>1112,job=>'MANAGER');
--EXECadd_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
--EXECadd_employee(1114,dno=>10,name=>'MARY',salary=>2000);
10. PL/SQL下调用存储过程
BEGIN
--按位置传递参数
add_employee(2111,'MARY',2000,'MANAGER',10);
--按名字传递参数
add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112,job=>'MANAGER');
--混合方法传递参数
add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
--默认值法
add_employee(4111,dno=>10,name=>'MARY',salary=>2000);
END;
11. 将示例8按照推荐规则修改。
CREATE OR REPLACE PROCEDURE add_employee(
eno employee.empno%type, --输入参数,雇员编号
name employee.ename%type, --输入参数,雇员名称
salary employee.sal%type, --输入参数,雇员薪水
job employee.job%type DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'
dno employee.deptno%type, --输入参数,雇员部门编号
on_Flag OUT number, --执行状态
os_Msg OUT VARCHAR2 --提示信息
)
IS
BEGIN
INSERT INTO employee (empno,ename,sal,job,deptno)VALUES(eno,name,salary,job, dno);
on_Flag:=1;
os_Msg:='添加成功';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
on_Flag:=-1;
os_Msg:='该雇员已存在。';
WHEN OTHERS THEN
on_Flag:=SQLCODE;
os_Msg:=SQLERRM;
END;
DECLARE
on_Flag NUMBER;
os_Msg VARCHAR2(100);
BEGIN
--按位置传递参数
add_employee(2111,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);
dbms_output.put_line(on_Flag||os_Msg);
END;
12. 示例1:给变量和常量声明赋值
/*
===========================================================
| 给变量和常量声明赋值
============================================================
*/
DECLARE
v_ename VARCHAR2(20);
v_rate NUMBER(7,2);
c_rate_incr CONSTANT NUMBER(7,2):=1.10;
BEGIN
--方法一:通过SELECT INTO给变量赋值
SELECT ename, sal* c_rate_incr INTO v_ename, v_rate
FROM employee
WHERE empno='7788';
--方法二:通过赋值操作符“:=”给变量赋值
v_ename:='SCOTT';
END;
13. 练习1:PL/SQL基础知识综合练习
DECLARE
v_tax_rate NUMBER(2);
v_sal_d NUMBER(5);
v_tax NUMBER(10,2);
v_sal NUMBER(10,2);
c_sal_start CONSTANT NUMBER(10):=3500;
BEGIN
SELECT sal
INTO v_sal
FROM employee
WHERE ename='KING';
v_tax:=0;
IFv_sal>=3500 THEN
v_sal:=v_sal-c_sal_start;
CASE
WHEN v_sal<=1500 THEN
v_tax_rate:=3;
v_sal_d:=0;
WHEN v_sal<=4500 THEN
v_tax_rate:=10;
v_sal_d:=105;
WHEN v_sal<=9000 THEN
v_tax_rate:=20;
v_sal_d:=555;
ELSE
v_tax_rate:=25;
v_sal_d:=1005;
END CASE;
v_tax:=v_sal*v_tax_rate/100-v_sal_d;
END IF;
dbms_output.put_line(v_tax);
END;
DECLARE
v_bonus NUMBER(10,2);
v_hiredate employee.hiredate%TYPE;
BEGIN
SELECT hiredate
INTO v_hiredate
FROM employee
WHERE ename='SCOTT';
IFsysdate-v_hiredate>=365*6 THEN
v_bonus:=2000;
ELSE
v_bonus:=1500;
END IF;
UPDATE employee SET comm=v_bonus
WHERE ename='SCOTT';
END;
DECLARE
v_grade NUMBER(2);
v_dname dept.dname%TYPE;
v_sal employee.sal%TYPE;
BEGIN
SELECT sal,dname
INTO v_sal,v_dname
FROM employee e INNER join dept d
ON e.deptno=d.deptno
WHERE ename='SCOTT';
CASE
WHEN v_sal<=3200 AND v_sal>=700 THEN
v_grade:=1;
WHEN v_sal<=4400 THEN
v_grade:=2;
WHEN v_sal<=5000 THEN
v_grade:=3;
WHEN v_sal<=7000 THEN
v_grade:=4;
WHEN v_sal<=9999 THEN
v_grade:=5;
ELSE
v_grade:=0;
END CASE;
IFv_grade=0 THEN
dbms_output.put_line('SCOTT所在部门:'||v_dname||' 薪水:'||v_sal||'工资无级别');
ELSE
dbms_output.put_line('SCOTT所在部门:'||v_dname||' 薪水:'||v_sal||'工资级别:'||v_grade);
END IF;
END;
--为员工SCOTT增加工资,每次增加100元,直到10000元停止。
DECLARE
v_hight employee.sal%TYPE:=10000;
v_sal employee.sal%TYPE;
BEGIN
SELECT sal
INTO v_sal
FROM employee
WHERE ename='SCOTT';
LOOP
EXIT WHEN v_sal+100>v_hight;
UPDATE employee
SET sal=sal+100
WHERE ename='SCOTT' ;
v_sal:=v_sal+100;
END LOOP;
END;
SELECT * FROM employee;
14. 练习2:使用预定义异常完善员工查询信息功能
DECLARE
v_empno VARCHAR2(10):=7788;
v_ename VARCHAR2(4);
BEGIN
SELECT ename INTO v_ename FROM employee WHERE empno=v_empno;
dbms_output.put_line(v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在!错误为:'||SQLCODE||SQLERRM);
WHEN VALUE_ERROR THEN
dbms_output.put_line('名称长度超过变量长度');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
15. 练习3:使用用户自定义异常完善员工调整工资业务需求
DECLARE
v_sal employee.sal%TYPE;
v_deptno employee.deptno%TYPE;
e_comm_is_nullEXCEPTION; --定义异常类型变量
BEGIN
SELECTsal,deptno INTO v_sal,v_deptno
FROM employee
WHERE empno=7788;
IF v_deptno=20 THEN
IF v_sal<10000 THEN
UPDATE employee
SET sal=10000
WHERE empno=7788;
ELSE
RAISE e_comm_is_null;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在!错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('工资不低于10000元');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
16. 练习4:使用显式游标技术完善公司业务需求
DECLARE
v_tax_rate NUMBER(2);
v_sal_d NUMBER(5);
v_tax NUMBER(10,2);
v_sal NUMBER(10,2);
v_sum NUMBER(10,2):=0;
c_sal_start CONSTANT NUMBER(10):=3500;
CURSOR emp_cursor IS
SELECT sal
FROM employee;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
v_tax:=0;
IF v_sal>=3500 THEN
v_sal:=v_sal-c_sal_start;
CASE
WHEN v_sal<=1500 THEN
v_tax_rate:=3;
v_sal_d:=0;
WHEN v_sal<=4500 THEN
v_tax_rate:=10;
v_sal_d:=105;
WHEN v_sal<=9000 THEN
v_tax_rate:=20;
v_sal_d:=555;
ELSE
v_tax_rate:=25;
v_sal_d:=1005;
END CASE;
v_tax:=v_sal*v_tax_rate/100-v_sal_d;
END IF;
v_sum:=v_sal+v_tax;
END LOOP;
CLOSE emp_cursor;
dbms_output.put_line(v_sum);
END;
DECLARE
v_bonus NUMBER(10,2);
CURSOR emp_cursor IS
SELECT hiredate
FROM employee
FOR UPDATE;
BEGIN
FOR cur1 IN emp_cursor LOOP
IF sysdate-cur1.hiredate>=365*6 THEN
v_bonus:=2000;
ELSE
v_bonus:=1500;
END IF;
UPDATE employee SET comm=v_bonus
WHERE CURRENT OF emp_cursor;
END LOOP;
END;
DECLARE
v_grade NUMBER(2);
CURSOR emp_cursor IS
SELECT ename,sal,dname
FROM employee e INNER join dept d
ON e.deptno=d.deptno
WHERE dname='SALES';
BEGIN
FOR cur1 IN emp_cursor LOOP
CASE
WHEN cur1.sal<=3200 AND cur1.sal>=700 THEN
v_grade:=1;
WHEN cur1.sal<=4400 THEN
v_grade:=2;
WHEN cur1.sal<=5000 THEN
v_grade:=3;
WHEN cur1.sal<=7000 THEN
v_grade:=4;
WHEN cur1.sal<=9999 THEN
v_grade:=5;
ELSE
v_grade:=0;
END CASE;
IF v_grade=0 THEN
dbms_output.put_line(cur1.ename||'所在部门:'||cur1.dname||'薪水:'||cur1.sal||' 工资无级别');
ELSE
dbms_output.put_line(cur1.ename||'所在部门:'||cur1.dname||'薪水:'||cur1.sal||' 工资级别:'||v_grade);
END IF;
END LOOP;
END;
17. 练习5:使用存储过程实现业务需求
CREATE OR REPLACE PROCEDURE fire_employee(
eno employee.empno%type, --输入参数,雇员编号
on_Flag OUT number, --执行状态
os_Msg OUT VARCHAR2 --提示信息
)
IS
e1EXCEPTION; --定义异常类型变量
BEGIN
DELETE
FROM employee
WHERE empno=eno;
IFSQL%NOTFOUND THEN
RAISE e1;
ELSE
on_Flag:=1;
os_Msg:='成功';
END IF;
EXCEPTION
WHEN e1 THEN
on_Flag:=-1;
os_Msg:='该雇员不存在。';
WHEN OTHERS THEN
on_Flag:=SQLCODE;
os_Msg:=SQLERRM;
END;
DECLARE
v_no employee.empno%TYPE;
on_Flag number(1); --执行状态
os_Msg VARCHAR2(200); --提示信息
BEGIN
v_no:=7788;
fire_employee(v_no,on_flag,os_Msg);
dbms_output.put_line(on_flag);
dbms_output.put_line(os_Msg);
END;
--调用get_sals存储过程,显示员工薪水
CREATE OR REPLACE PROCEDURE get_sals(
cur_salary OUT SYS_REFCURSOR,
on_Flag OUT number, --执行状态
os_Msg OUT VARCHAR2 --提示信息
)
AS
BEGIN
OPEN cur_salary FOR
SELECT empno,sal FROM employee;
on_Flag:=1;
os_Msg:='成功';
EXCEPTION
WHEN OTHERS THEN
on_Flag:=-1;
os_Msg:='其他错误,与管理员联系。';
END;
DECLARE
v_empno employee.empno%type;
v_sal employee.sal%type;
emp_salary SYS_REFCURSOR;
on_Flag number(1); --执行状态
os_Msg VARCHAR2(200); --提示信息
BEGIN
get_sals(emp_salary,on_Flag,os_Msg);
IFon_flag=1 THEN
LOOP
FETCH emp_salary INTO v_empno, v_sal;
EXIT WHEN emp_salary%notfound;
DBMS_OUTPUT.PUT_LINE(v_empno||'的薪水是' ||v_sal);
END LOOP;
ELSE
dbms_output.put_line(os_Msg);
END IF;
IFemp_salary%ISOPEN THEN
CLOSE emp_salary;
END IF;
END;