Oracle PL/SQL入门之案例实践

 一. 案例介绍

  某数据库有两张表,是关于某公司员工资料、薪水和部门信息的,它们分别是emp表和dept表,两张表的结

构如下:

  要求如下:

  1、按照上表结构建立相应的表,并每张表写入5组合法数据。

  2、操纵相关表,使得“技术部”的员工的薪水上涨20%。

  3、建立日志,追踪薪水变动情况。

  4、建立测试包。

  二. 案例的分析与实现

  从前面案例的介绍不难看出,要求1考察点为基本SQL语句;要求2主要考察复合查询;要求3是考察触发器

的应用;要求4的考察面相对多一些,不仅考察了包的创建,而且也考察了在PL/SQL中的测试方法。了解了这些

考察的知识点,就可以一一去解决。

  要求1:

  首先根据前面表的结构可以创建两张表:

  ——创建员工表

create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));

  ——部门表

create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

  建立了表之后就可以往表里面写数据了,这里把添加表记录的代码写入到相应的存储过程。

/*给emp表添加记录的存储过程*/
create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary

number) as
v_emp_id number:=p_emp_id;
v_emp_name varchar2(20):=p_emp_name;
v_emp_salary number:=p_emp_salary;
begin
 insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
end ins_table_emp;

/*给dept表添加记录的存储过程*/
create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number)

as
 v_dept_id number:=p_dept_id;
 v_dept_name varchar2(20):=p_dept_name;
 v_emp_id number:=p_emp_id;
begin
 insert into dept values (v_dept_id,v_dept_name,v_emp_id);
end ins_table_emp;

/*调用相应的存储过程实现记录添加*/

execute ins_table_emp(10000,'a',4000);
execute ins_table_emp(10001,'b',2300);
execute ins_table_emp(10002,'c',3500);
execute ins_table_emp(10003,'d',3500);
execute ins_table_emp(10004,'e',3500);
execute ins_table_dept(111,'张三',10000);
execute ins_table_dept(111,'张三',10001);
execute ins_table_dept(111,'张三',10002);
execute ins_table_dept(112,'赵六',10003);
execute ins_table_dept(113,'宋七',10004);

 

  要求2:

  给指定部门的员工加薪,这实际上是一个复合查询,首先需要把所有该部门的员工塞选出来,然后对这些

员工的薪水进行相应的改动。依照这一思路,代码如下:

  (需要注意的是:将要加薪的部门作为参数,这样的存储过程更有灵活性。)

create or replace procedure add_salary(p_dept_name varchar2) as
v_dept_name varchar2(20):=p_dept_name;
begin
 update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select emp.EMP_ID from

emp,dept where emp.EMP_ID=dept.EMP_ID and dept.dept_name=v_dept_nam);
end add_salary;

  要求3:
create table salary(old_s number(4), new_s number(4), differ_s number(4));
  建立日志对薪水的变动情况形成一个追踪,也就是说,如果对某个职员的薪水进行变更就应该将其相应的

变更记录全部记下来。如果对emp表的salary字段创建一个触发器,来监视对salary的更改,把每次更改进行记

录,这样就达到了要求3的目的了。

create or replace trigger print_salary_change  
before delete or insert or update on emp  --触发事件
for each row                  -- 每修改一行都需要调用此过程

declare--只有触发器的声明需要declare,过程和函数都不需要
salary_balance number;
begin
--:new 与:old分别代表该行在修改前和修改后的记录
salary_balance:=:new.EMP_salary-:old.EMP_salary;

insert into salary values(:old.EMP_salary,:new.EMP_salary,salary_balance);
end print_salary_change;

 


execute add_salary('张三');就可以看到salary表的变化。
  要求4:

  与其他语言(c/c++等)相比,PL/SQL的测试有其不同之处,归纳下来有三种方法:

  1、使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量,以此来观察程序是否存在逻辑错误。

  2、插入测试表的方法。即创建一个临时的中间表,然后把所有涉及到的中间变量的结果都作为记录插入到

中间表中,这样可以查询表中的结果来观察程序的执行情况。

  3、使用异常处理手段,对可疑的程序段使用begin … end ,然后可以在exception里进行异常捕获处理。

  这里准备使用第二种方法来建立一个测试包,PL/SQL里包的概念类似于面向对象里的类的概念,包将一组

操作和属性封装在一起,不仅增强了程序的模块化,而且由于封装了更多的操作和属性而提高了执行效能。建

立一个PL/SQL需要两个步骤:首先要建立包头,类似于建立一个类的头文件,里面主要对包中的过程,函数和

变量的声明;第二部分主要是包体部分,实现前面声明的过程和函数,另外还需要对包进行初始化等工作。

  根据这一思路,建立测试包如下:
create table debugtable (d_numberOfLine number,d_description varchar2(20),d_valueOfvariable

varchar2(20));
/*包头部分*/
create or replace package debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2)
 procedure reset;
 v_numberOfLine number;
end debug;
/*包体部分*/
create or replace package body debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2) is
begin
 insert into debugtable
 values(v_numberOfLine,v_description,v_valueOfvariable);
 v_numberOfLine:=v_numberOfLine+1;
end debug;
procedure reset is
begin
 v_numberOfLine:=1;
 delete from debugtable;
end reset;
/*初始化部分*/
begin
 reset;
end debug;

PL/SQL编程 pl/sql(procedural language/sql)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入式sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误。这样使得他的功能变的更强大。缺点是移植性不好。 编写一个存储过程,向表中添加数据。 1. create table mytest (name varchar2(30),passwd varchar2(30)); 2. create or replace procedure xxc_pro1 is begin insert into mytest values ('小红','m123'); end; 3. 调用过程 exec 过程名(参数1,参数2…)或call 过程名参数1,参数2…) ① exec xxc_pro1; 或者是 ② call xxc_pro1; pl/sql可以做什么? 块:包括过程、函数、触发器、包。 编写规范: 1. 注释 --:单行注释 eg:select * from emp where empno=7788;--取得员工信息 /*……*/多行注释 2. 表示符号(变量)的命名规范: ① 当定义变量时,建议用v_作为前缀:v_ename ② 当定义常量时,建议用c_作为前缀:c_rate ③ 当定义游标时,建议用_cursor作为后缀:emp_cursor ④ 当定义例外时,建议用e_作为前缀:e_error 块(block)是pl/sql的今本程序单元,编写pl/sql程序实际上就是在编写pl/sql块;pl/sql块由三部分组成:定义部分,执行部分,例外处理部分。 declare --可选部分 /*定义部分:定义常量,变量,游标,例外,复杂数据类型*/ begin --必选部分 /*执行部分:要执行的pl/sql语句和sql语句*/ exception --可选部分 /*例外处理部分:处理运行的各种错误*/ 实例1:只包含执行部分的pl/sqlSQL> set serveroutput on --打开输出 SQL> begin 2 dbms_output.put_line('hello'); 3 end; 4 / 说明:dbms_output是oracle提供的包,该包包含一些过程,put_line就是其中之一。 实例2:包含定义部分和执行部分 SQL> declare 2 v_ename varchar2(5); 3 begin 4 select ename into v_ename from emp where empno = &no; 5 dbms_output.put_line('雇员名'||v_ename); 6 end; 7 / 说明:&:从控制台输入变量,会弹出一个对话框。 实例3.同时输出雇员名和工资 SQL> declare 2 v_ename varchar2(20); 3 v_sal number(10,2); 4 begin 5 select ename,sal into v_ename,v_sal from emp where empno=&no; 6 dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal); 7 end; 8 / 包含定义,执行,和例外处理的pl/sql块。 实例4.当输入的员工号不存在时 SQL> declare 2 v_ename varchar2(20); 3 v_sal number(10,2); 4 begin 5 select ename,sal into v_ename,v_sal from emp where empno =&no; 6 dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal); 7 exception --异常处理部分 8 when no_data_found then 9 dbms_output.put_line('请输入正确的员工号!'); 10 end; 11 / 以上为块的基础,下面来介绍块的各个组成:过程,函数,触发器,包。 过程 过程用于执行特定的操作,当执行过程的时候,可以指定输入参数(in),也可以指定输出参数(out)。通过在过程中使用输入参数,可以讲数据输入到执行部分,通过使用输出参数,可以将执行部分的数据输出到应用环境,在pl/sql中可以使用create procedure命令来创建过程。 编写一个存储过程,可以输入雇员名和新工资来改变员工工资。 --案例 create or replace procedure xxc_pro3(newname in varchar2,newsal in number) is begin update emp set sal=newsal where ename=newname; end;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值