- --代码2.1 使用书序结构计算员工薪资
- DECLARE
- v_sal1 NUMBER;
- v_sal2 NUMBER;
- v_sumsal NUMBER;
- BEGIN
- SELECT sal
- INTO v_sal1
- FROM emp
- WHERE empno = &empno1;
- SELECT sal
- INTO v_sal2
- FROM emp
- WHERE empno = &empno2;
- v_sumsal := v_sal1 + v_sal2;
- dbms_output.put_line('ID:' || &empno1 || ',' || &empno2 || ',工资之和:' ||
- v_sumsal);
- END;
- select * from emp order by sal desc;
- --代码2.2 使用分支结构为员工加薪
- DECLARE
- --定义加薪比率常量
- c_Manager CONSTANT NUMBER:=0.15;
- c_SalesMan CONSTANT NUMBER:=0.12;
- c_Clerk CONSTANT NUMBER:=0.10;
- c_SR_CONT CONSTANT NUMBER:=0.88;
- --定义工种变量
- v_Job VARCHAR(100);
- BEGIN
- --查询指定员工编码的员工信息
- SELECT job INTO v_Job FROM scott.emp WHERE empno=&empNo1;
- --执行分支判断
- IF v_Job='CLERK' THEN
- UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE empno=&empNo1;
- ELSIF v_Job='SALESMAN' THEN
- UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE empno=&empNo1;
- ELSIF v_Job='MANAGER' THEN
- UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE empno=&empNo1;
- ELSIF v_Job='SR.CONT' THEN
- UPDATE scott.emp SET SAL=SAL*(1+c_SR_CONT) WHERE empno=&empNo1;
- END IF;
- --显示完成信息
- DBMS_OUTPUT.PUT_LINE('已经为员工'||&empNo1||'成功加薪!');
- EXCEPTION
- --处理PL/SQL预定义异常
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
- END;
- --代码2.3 使用循环结构为所有员工加薪(未使用自定义函数)
- DECLARE
- --定义加薪比率常量
- c_Manager CONSTANT NUMBER:=0.15;
- c_SalesMan CONSTANT NUMBER:=0.12;
- c_Clerk CONSTANT NUMBER:=0.10;
- c_Cont CONSTANT NUMBER:=1.00;
- v_Job VARCHAR(100); --定义职位变量
- v_EmpNo VARCHAR(20); --定义员工编号变量
- v_Ename VARCHAR(60); --定义员工名称变量
- CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp FOR UPDATE;
- BEGIN
- OPEN c_Emp; --打开游标
- LOOP --循环游标
- FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename; --提取游标数据
- EXIT WHEN c_Emp%NOTFOUND; --如果无数据可提取退出游标
- IF v_Job='CLERK' THEN --如果为职员,加薪10%
- UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE CURRENT OF c_Emp;
- ELSIF v_Job='SALESMAN' THEN --如果为销售职员,加薪12%
- UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE CURRENT OF c_Emp;
- ELSIF v_Job='MANAGER' THEN --如果为经理,加薪15%
- UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE CURRENT OF c_Emp;
- ELSIF v_Job='SR.CONT' THEN --如果为高级顾问,工资翻倍!
- UPDATE scott.emp SET SAL=SAL*(1+c_Cont) WHERE CURRENT OF c_Emp;
- END IF;
- --显示完成信息
- DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!');
- END LOOP;
- CLOSE c_Emp; --关闭游标
- EXCEPTION
- WHEN NO_DATA_FOUND THEN --处理PL/SQL预定义异常
- DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
- END;
- --执行动态SQL语句
- drop table books;
- DECLARE
- v_SQLStr VARCHAR(200):=' CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL) ';
- BEGIN
- EXECUTE IMMEDIATE v_SQLStr; --执行DDL语句
- END;
- --尝试执行静态SQL语句(将发生编译错误)
- BEGIN
- CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL;
- END;
- --代码2.4 创建员工对象
- CREATE OR REPLACE TYPE Emp_obj AS OBJECT
- (
- empno NUMBER(4), --员工编号属性
- ename VARCHAR2(10), --员工名称属性
- job VARCHAR(9), --员工职别属性
- sal NUMBER(7,2), --员工薪水属性
- deptno NUMBER(2), --部门编号属性
- --加薪方法
- MEMBER PROCEDURE AddSalary(radio NUMBER)
- );
- --定义对象类型体,实现对象方法
- CREATE OR REPLACE TYPE BODY Emp_obj AS
- --实现对象方法
- MEMBER PROCEDURE AddSalary(radio NUMBER)
- IS
- BEGIN
- sal:=sal*(1+radio); --加上特定比例的薪水
- END;
- END ;
- --代码2.5 创建函数封装业务逻辑
- CREATE OR REPLACE FUNCTION GetAddSalaryRatio(p_Job VARCHAR2)
- RETURN NUMBER AS
- v_Result NUMBER(7,2);
- BEGIN
- IF p_Job='CLERK' THEN --如果为职员,加薪10%
- v_Result:=0.10;
- ELSIF p_Job='SALESMAN' THEN --如果为销售职员,加薪12%
- v_Result:=0.12;
- ELSIF p_Job='MANAGER' THEN --如果为经理,加薪15%
- v_Result:=0.15;
- ELSIF p_Job='cont' THEN --如果为顾问,工资翻倍!
- v_Result:=1;
- END IF;
- RETURN v_Result;
- END;
- --代码2.6 调用函数简化程序逻辑
- DECLARE
- v_Job VARCHAR(100); --定义职位变量
- v_EmpNo VARCHAR(20); --定义员工编号变量
- v_Ename VARCHAR(60); --定义员工名称变量
- v_Ratio NUMBER(7,2);
- CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp3 FOR UPDATE;
- BEGIN
- OPEN c_Emp; --打开游标
- LOOP --循环游标
- FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename; --提取游标数据
- EXIT WHEN c_Emp%NOTFOUND; --如果无数据可提取退出游标
- v_Ratio:=GetAddSalaryRatio(v_Job); --调用函数,得到加薪率
- UPDATE scott.emp3 SET sal2=sal2*(1+v_Ratio) WHERE CURRENT OF c_Emp;
- --显示完成信息
- DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!');
- END LOOP;
- CLOSE c_Emp; --关闭游标
- commit;
- EXCEPTION
- WHEN OTHERS THEN --处理PL/SQL预定义异常
- DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
- END;
- --代码2.7 最简单的PL/SQL块
- BEGIN
- DBMS_OUTPUT.PUT_LINE('Oracle和Mysql差别挺大,PL/SQL是对SQL的增强。');
- END;
- --代码2.8 完整的PL/SQL语句块
- DECLARE
- v_deptcount NUMBER(2);
- v_deptno NUMBER(2) := 60;
- BEGIN
- SELECT COUNT(1)
- INTO v_deptcount
- FROM dept
- WHERE deptno = v_deptno;
- IF v_deptcount = 0
- THEN
- INSERT INTO dept
- VALUES
- (v_deptno, '财务部', '深圳');
- dbms_output.put_line('成功插入部门资料');
- ELSIF v_deptcount = 1
- THEN
- dbms_output.put_line('该部门已存在!');
- END IF;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('部门资料插入失败!');
- END;
- commit;
- select * from dept;