oracle创建函数一直在执行,oracle学习_创建函数调用函数及存储过程

**

–创建方法并调用方法

**

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;