存储过程，触发器简单实例

1.创建2个sequence

create sequence emp_sequence
minvalue 1
maxvalue 99999
start with 1
increment by 1
;
​
create sequence dept_sequence
minvalue 1
maxvalue 999
start with 1
increment by 1
;
​
drop sequence emp_sequence;--删除sequence
drop sequence dept_sequence;
​
select dept_sequence.currval from dual;--获取当前sequence
select emp_sequence.currval from dual;
​
select emp_sequence.nextval from dual;
select dept_sequence.nextval from dual;--获取下一个sequence

2.表结构如下：

create table dept(
dept_id number(3),
dept_name varchar2(20),
constraint pk_dept primary key(dept_id)
);

create table emp(
emp_id number(5) not null,
emp_name varchar2(20),
emp_salary number(4),
dept_id number(3) not null,
constraint pk_empt primary key(emp_id,dept_id),
foreign key(dept_id) references dept(dept_id)
);
​
create table salary_change_record(--emp薪水备份表
empid number(5) not null,
old_salary number(4),
new_salary number(4),
change_date date,
constraint pk_salary_change_record primary key(empid,change_date)
);

3.存储过程

1.打印当前时间

代码：

create or replace procedure printsysdate
 is
 begin
   dbms_output.put_line(sysdate);
 end printsysdate;
​

调用：

call printsysdate();--调用

2.对emp表创建插入存储过程：emp_insert

代码：

create or replace procedure emp_insert(Ve_id number,V_name varchar2,V_salary number,Vd_id number)
is
begin
  insert into emp values(Ve_id,V_name,V_salary,Vd_id);
  dbms_output.put_line('添加了员工为：'||to_char(Ve_id)||'的信息');--打印信息
  commit;
  end;

测试:

begin 
  dept_insert(dept_sequence.nextval,'dep1');
  dept_insert(dept_sequence.nextval,'dep2');
  dept_insert(dept_sequence.nextval,'dep3');
  dept_insert(dept_sequence.nextval,'dep4');
  dept_insert(dept_sequence.nextval,'技术部');
end;

3.对dept表创建插入存储过程：dept_insert

代码：

create or replace procedure emp_insert(Ve_id number,V_name varchar2,V_salary number,Vd_id number)
is
begin
  insert into emp values(Ve_id,V_name,V_salary,Vd_id);
  dbms_output.put_line('添加了员工为：'||to_char(Ve_id)||'的信息');
  commit;
  end;

测试：

--测试
begin
  emp_insert(emp_sequence.nextval,'emp1',3000,1); 
  emp_insert(emp_sequence.nextval,'emp2',3000,1);   
  emp_insert(emp_sequence.nextval,'emp3',3000,1); 
  emp_insert(emp_sequence.nextval,'emp4',3000,1);
  emp_insert(emp_sequence.nextval,'emp5',3000,1);   
  
  emp_insert(emp_sequence.nextval,'emp1',4000,2); 
  emp_insert(emp_sequence.nextval,'emp2',4000,2);   
  emp_insert(emp_sequence.nextval,'emp3',4000,2); 
  emp_insert(emp_sequence.nextval,'emp4',4000,2);
  emp_insert(emp_sequence.nextval,'emp5',4000,2); 
  
  emp_insert(emp_sequence.nextval,'emp1',5000,3); 
  emp_insert(emp_sequence.nextval,'emp2',5000,3);   
  emp_insert(emp_sequence.nextval,'emp3',5000,3); 
  emp_insert(emp_sequence.nextval,'emp4',5000,3);
  emp_insert(emp_sequence.nextval,'emp5',5000,3); 
  
  emp_insert(emp_sequence.nextval,'emp1',6000,4); 
  emp_insert(emp_sequence.nextval,'emp2',6000,4);   
  emp_insert(emp_sequence.nextval,'emp3',6000,4); 
  emp_insert(emp_sequence.nextval,'emp4',6000,4);
  emp_insert(emp_sequence.nextval,'emp5',6000,4); 
  
  emp_insert(emp_sequence.nextval,'emp1',7000,5); 
  emp_insert(emp_sequence.nextval,'emp2',7000,5);   
  emp_insert(emp_sequence.nextval,'emp3',7000,5); 
  emp_insert(emp_sequence.nextval,'emp4',7000,5);
  emp_insert(emp_sequence.nextval,'emp5',7000,5); 
end;

4.查看员工数量：EMP_COUNT

代码：

CREATE OR REPLACE PROCEDURE EMP_COUNT   
is  
V_TOTAL NUMBER(10);   
BEGIN  
 SELECT COUNT(*) INTO V_TOTAL FROM EMP;   
 DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);   
END; 

测试：

begin
   EMP_COUNT();--调用
   end;

5.查看全部员工信息存储过程：EMP_COUNT

代码：

create or replace procedure showemp 
is 
Cursor emp_mycursor is select emp_id,emp_name,emp_salary,dept_id from emp;
begin 
       dbms_output.put_line('员工号 '||'名字 '||' 薪水 '||'部门');
       For emp_cursor in emp_mycursor Loop
         dbms_output.put_line('  '||emp_cursor.emp_id||'    '||emp_cursor.emp_name||'    '||emp_cursor.emp_salary||'    '||emp_cursor.dept_id);
         end loop;
end;

测试

begin 
  showemp;   
  end;

6.删除员工信息:emp_delete

代码

create or replace procedure emp_delete(Ve_id number)
is
begin
  delete from emp where emp_id=Ve_id;
  dbms_output.put_line('删除了员工为：'||Ve_id||'的信息');
  commit;
  end;

测试

 begin
    emp_delete(7);
    showemp;   --调用显示信息的存储过程
    end;
  select * from emp;--使用sql

7.修改员工信息：emp_update

代码：

create or replace procedure emp_update(Ve_id number,V_salary number)
is
begin
  update emp set emp_salary=V_salary where emp_id=Ve_id;
  dbms_output.put_line('修改了员工为：'||to_char(Ve_id)||'的信息');
  commit;
  end;

测试

  begin
  emp_update(12,8000);
  end;

8.按照部门号增加员工薪资：addsalarybydept_id

代码：

create or replace procedure addsalarybydept_id(Vd_id number)
  is
  begin
    update emp set emp_salary = emp_salary*(1+1.2) where dept_id = Vd_id;
    commit;
    dbms_output.put_line('修改了员工部门为：'||to_char(Vd_id)||'的信息！');
    end;

测试：

begin 
      addsalarybydept_id(2);
      showemp();
      end;
  --select * from emp;

9.通过emp_id查询emp表信息：emp_selectbyid

代码：

create or replace procedure emp_selectbyid(Ve_id number)
 is
 Cursor emp_cursor is select emp_id,emp_name,emp_salary,dept_id from emp where emp_id=Ve_id;
 begin
   dbms_output.put_line('员工号 '||'名字 '||' 薪水 '||'部门');
   For mycursor in  emp_cursor loop
     dbms_output.put_line('  '||mycursor.emp_id||'    '||mycursor.emp_name||'    '||mycursor.emp_salary||'    '||mycursor.emp_id);
         end loop;     
 end;

测试

begin
    emp_selectbyid(10);
    end;

10.对表salary_change_record进行插入：salary_change_record_insert

create or replace procedure salary_change_record_insert(V_id number,V_old_salary number,V_new_salary number,V_change_date date)
is
begin 
  insert into salary_change_record values(V_id,V_old_salary,V_new_salary,V_change_date);
  commit;
end;

11.对表salary_change_record进行查询：salary_change_record_select

create or replace procedure salary_change_record_select
is
       cursor mycursor is select empid,old_salary,new_salary,change_date from salary_change_record where 1=1;
begin
  dbms_output.put_line('        '||'备份表 ');
  dbms_output.put_line('员工号 '||'原薪水 '||' 薪水 '||'修改时间');
  for select_cursor in mycursor loop
    dbms_output.put_line('  '||select_cursor.empid||'    '||select_cursor.old_salary||'    '||select_cursor.new_salary||'    '||to_char(select_cursor.change_date,'yyyy/mm/dd hh24:mi:ss'));
         end loop;
end;

4.触发器

1.创建一个对emp表修改的触发器：change_record

create or replace trigger change_record
after update on emp
for each row
  begin
    insert into salary_change_record values(:new.emp_id,:old.emp_salary,:new.emp_salary,sysdate);
    end;
​

测试触发器：

begin
  addsalarybydept_id(2); --对部门2薪资修改
  salary_change_record_select;--查看修改信息
  end;