create or replace function func_name(dept_id number,salary number)
return number
is
//函数使用过程中需要声明的变量,记录类型,cursor
begin
//函数体的执行
exception
//处理函数执行过程中的异常
end;
//hello world
create or replace function hello_world
return varchar2
is
begin
return 'hello world';
end;
调用函数
begin
dbms_output.put_line(hello_world);
或者
select hello_world from dual;
带有参数的
create or replace function hello_world(v_logo varchar2)
return varchar2
is
begin
return 'hello world'|| v_logo;
end;
调用
select hello_world('王晓庆') from dual;
或者
begin
dbms_output.put_line(hello_world('王晓庆'));
end;
//创建一个存储函数,返回当前的系统时间
create or replace function get_sysdate
return date
is
v_date date;
begin
v_date :=sysdate;
return v_date;
end;
//定义带参数的函数,二个数相加
create or replace function add(v_num1 number,v_num2 number)
return number
is
v_sum number(10);
begin
v_sum:=v_num1+v_num2;
return v_sum;
end;
//定义一个函数:获取给定的部门的工资总和,要求:部门号定义为参数,工资总额定义为返回值
create or replace function get_sal (dept_id number)
return number
is
v_sumsal number(10):=0;
cursor salary_cursor is select salary from employees where department_id=dept_id;
begin
for c in salary_cursor loop
v_sumsal:=v_sumsal+c.salary;
end loop;
return v_sumsal;
end;
//返回二个值
create or replace function get_sal(dept_id number,total_count out number)
retuen number
is
v_sumsal number (10):=0;
cursor salary_cursor is select salary from employees where department_id=dept_id;
begin
total_count:=0;
for c in salary_cursor loop
v_sumsal:=v_sumsal+c.salary;
total_count:=total_count+1;
end loop;
return v_sumsal;
end;
//执行
declare
v_total number(3):=0;
begin
dbms_output.put_line(sum_sal(80,v_total));
dbms_outp.put_line(v_total);
end;
//定义一个存储过程:获取给部门的工资总和通过out参数,要求:部门和工资总额定义为参数
create or replace procedure get_sal2(dept_id number,sumsal out number)
is
cursor salary_cursor is select salary from employees where department_id=dept_id;
begin
sumsal:=0;
for c in salary_cursor loop
sumsal:=sumsal+c.salary;
end loop;
dbms_output.put_line(sumsal);
end;
//调用
declare
v_sal number(10):=0;
begin
get_sal(80,v_sal);
end;
//
create or replace procedure add_sal (dept_id number,temp_sal out number)
is
cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id=dept_id;
v_i number(4,2):=0;
begin
temp_sal:=0;
for c in sal_cursor loop
if to_char(c.hire_date,'yyyy')<'1995' then v_i:=0.05;
elsif to_char(c.hire_date,'yyyy')<'1998' then v_i:=0.03;
else v_i:=0.01;
end if;
update employees set salary=salary*(1+v_i) where employee_id=c.employee_id