**
–创建方法并调用方法
**
create or replace function f_get_name(t_empno in varchar2)
return varchar2 is
v_emp_name emp.ename%type;
begin
select e.ename into v_emp_name
from emp e where e.empno=t_empno;
return v_emp_name;
end f_get_name;
select f_get_name('7698') from dual
--根据部门号查询部门名称,使用函数 查询人员姓名及部门名称
create or replace function f_get_name_by_deptno(t_deptno in varchar2)
return varchar2 is
v_dept_name dept.dname%type;
begin
select d.dname into v_dept_name
from dept d where d.deptno=v_dept_name;
return v_dept_name;
end f_get_name_by_deptno;
select ename,f_get_name_by_deptno(deptno) from emp
select * from emp
**
–存储过程 执行DML语句用commit
**
--create or replace procedure
--存储过程的调用
--exec 过程名()
create or replace procedure proc_demo(
dept_no number default 10,sal_sum out number,emp_count out number)
is
begin
select sum(sal),count(*) into sal_sum,emp_count
from emp where deptno=dept_no;
end proc_demo;
**
–用存储过程实现decode函数的功能 --输出参数是修改的记录数
**
create or replace procedure proc_demo2(up_count out number) is
begin
up_count := 0;
declare
cursor e_job is
select empno, job from emp;
begin
for v_job in e_job loop
if v_job.job = 'CLERK' then
update emp set job = '办事员' where empno = v_job.empno;
elsif v_job.job = 'SALESMAN' then
update emp set job = '销售人员' where empno = v_job.empno;
elsif v_job.job = 'MANAGER' then
update emp set job = '经理' where empno = v_job.empno;
else
update emp set job = '其他' where empno = v_job.empno;
end if;
up_count := up_count + 1;
end loop;
commit;
end;
end proc_demo2;
select * from emp
–过程名
create table manager as select * from emp
select * from manager
--向manager表中插入职位为经理年薪大于7W的数据
--如果MANAGER表里已经存在此员工,则不插入
--如果不存在,在插入。输出参数是插入的记录数
create or replace procedure proc_demo3(
up_no out number)
is
begin
up_no:=0;
declare
cursor c_year_sal is
select empno, nvl(e.sal,0)*12+nvl(e.comm,0) as yearsal from emp;
begin
if c_year_sal.yearsal>70000 then
insert into manager select * from emp where empno = c_year_sal.empno;
else
exit;
end if;
commit;
end;
end proc_demo3;
create or replace procedure proc_demo(
dept_no number default 10,
sal_sum out number,
emp_count out number)
is
begin
select sum(sal),count(*) into sal_sum,emp_count
from emp where deptno=dept_no;
end proc_demo;
**
–无游标
**
CREATE OR REPLACE PROCEDURE add_Man2(m_num OUT NUMBER) IS
u_row_num number;
BEGIN
m_num := 0;
SELECT COUNT(1)
INTO u_row_num
FROM manager
WHERE job = '经理'
AND (nvl(sal, 0) * 12 + nvl(comm, 0)) > 70000;
IF (u_row_num < 1) THEN
--不存在
INSERT INTO manager
(empno, ename, job, sal, comm)
VALUES
(8654, '小陈', '经理', 6000, 500);
commit;
m_num := m_num + 1;
END IF;
END;
select * from manager;