欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

第二阶段自己做的领导要交的函数 存储过程功能 有大用


1,创建表语句

create  sequence my_emp_seq nocache;

create  sequence my_dept_seq nocache;


create  table my_emp(

emp_id integer,

emp_name varchar2(20),

dept_id integer

);


create  table my_dept(

dept_id integer,

dept_name varchar2(20)

);



2,函数与存储过程--函数调用存储过程

存储过程

create or replace procedure dml_my_emp_proc (emp_id_p out my_emp.emp_id%type,

emp_name_p in my_emp.emp_name%type, dept_id_p in my_dept.dept_id%type)

 as

  

 begin

   select emp_id into emp_id_p from my_emp where emp_name=emp_name_p;

   if emp_id_p is not null then

      dbms_output.put_line('库中有此员工,名字为' || emp_name_p);

   end if;

   exception

     when no_data_found then

      emp_id_p :=my_emp_seq.nextval;

      insert into my_emp (emp_id,emp_name,dept_id)values(emp_id_p,emp_name_p,dept_id_p);

      dbms_output.put_line('新增员工,id为' || to_char(emp_id_p) || ',名字为' || emp_name_p);

 end;





函数


create or replace function dml_my_dept_func (emp_name_p in out my_emp.emp_name%type,

dept_name_p in out my_dept.dept_name%type, dept_id_p out my_dept.dept_id%type)

 return my_emp.emp_id%type

 as

 emp_id_pf my_emp.emp_id%type;

 begin

   select dept_id into dept_id_p from my_dept where dept_name=dept_name_p;

   if dept_id_p is not null then

     dml_my_emp_proc(emp_id_pf,emp_name_p,dept_id_p);

     dbms_output.put_line('库中有此部门,名称为'||dept_name_p);

     return emp_id_pf;

    end if;

   exception

     when no_data_found then

       dept_id_p :=my_dept_seq.nextval;

       insert into my_dept(dept_id,dept_name)values(dept_id_p,dept_name_p);

        dbms_output.put_line('新增部门,id为' || to_char(dept_id_p) || ',名称为' || dept_name_p);

        dml_my_emp_proc(emp_id_pf,emp_name_p,dept_id_p);

    return emp_id_pf;

 end;



3,调用语句块-目的是插入用户和部门

set serveroutput on;


declare

emp_id my_emp.emp_id%type;

dept_id my_dept.dept_id%type;

emp_name my_emp.emp_name%type;

dept_name my_dept.dept_name%type;

begin

  emp_name :='钱三';

  dept_name :='市场部';

  dbms_output.put_line('员工id是' || DML_MY_DEPT_FUNC(emp_name,dept_name,dept_id));

  dbms_output.put_line('部门id是' || to_char(dept_id));

  end;

/




1)  修改表语句和插入语句

alter table my_emp add  salary number;

alter table my_emp add constraint emp_id_pk primary key(emp_id);

alter table my_dept add constraint dept_id_pk primary key (dept_id);


truncate table my_emp;

truncate table my_dept;


insert into my_dept (dept_id,dept_name) values(1,'资讯部');

insert into my_dept (dept_id,dept_name) values(2,'市场部');

insert into my_dept (dept_id,dept_name) values(3,'工程部');

insert into my_dept (dept_id,dept_name) values(4,'产品部');



insert into my_emp (emp_id,emp_name,dept_id,salary) values(1,'张三',1,3000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(2,'李四',1,3000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(3,'王五',2,2000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(4,'赵六',2,2000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(5,'钱七',2,3000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(6,'孙八',3,3000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(7,'周九',3,4000);

insert into my_emp (emp_id,emp_name,dept_id,salary) values(8,'郑十',4,5000);


2)存储过程与函数-存储过程调用函数



存储过程

create or replace procedure dml_my_dept_proc2 (dept_name_p in my_dept.dept_name%type,

salary_p in number)

 as

 dept_id_pf my_dept.dept_id%type;

 max_salary_pf my_emp.salary%type;

 my_emp_pf my_emp%rowtype;

 my_emp_cur sys_refcursor;


 begin

   select dept_id into dept_id_pf from my_dept where dept_name=dept_name_p;

   max_salary_pf := dml_my_emp_func2(dept_id_pf,salary_p);

   if max_salary_pf = 0 then

     return;   

   end if;

   dbms_output.put_line('最大薪水为' || to_char(max_salary_pf) || ',包含的员工有如下' );

   open my_emp_cur for select * from my_emp where salary=max_salary_pf and dept_id=dept_id_pf;

   loop

     fetch my_emp_cur into my_emp_pf;

     exit when my_emp_cur%notfound;

     dbms_output.put_line(my_emp_pf.emp_name);

   end loop;

   close my_emp_cur;

   exception

     when no_data_found then

       dbms_output.put_line('没有此部门');

 end;






函数

create or replace function dml_my_emp_func2(dept_id_p in my_emp.emp_id%type,

                                            salary_p  in my_emp.salary%type)

  return my_emp.salary%type

  as

  cursor my_emp_cur is select * from my_emp where dept_id = dept_id_p;

  my_emp_pf     my_emp%rowtype;

  max_salary_pf number;


begin

  open my_emp_cur;

  fetch my_emp_cur into my_emp_pf;

  if my_emp_cur%rowcount = 0 then

    dbms_output.put_line('此部门无员工');

    return 0;

  end if;

  close my_emp_cur;

  for my_emp_pf in my_emp_cur loop

    update my_emp set salary=nvl(salary,0)+salary_p where emp_id=my_emp_pf.emp_id;

  end loop; 

  

  select max(salary) into max_salary_pf from my_emp where dept_id = dept_id_p;

  return max_salary_pf;

end;




3),调用语句块-目的是给部门的人涨薪水-得到涨后工资最高的人

set serveroutput on;


declare

   dept_name my_dept.dept_name%type;

   salary_add my_emp.salary%type;

  begin

  dept_name :='资讯部';

  salary_add := 111;

  dml_my_dept_proc2(dept_name,salary_add);

  end;



普通分类: