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;