oracle函数

函数：类似于编程语言中的函数，可以有入参，必须有返回值。

--创建函数
create or replace function get_hello_msg 
return varchar2 as
begin
  return 'hello world!';
end get_hello_msg; 
--查看创建的函数信息
select * from user_objects where object_name='GET_HELLO_MSG'
--通过视图user_source查看函数的定义代码
select * from user_source where name='GET_HELLO_MSG';
--开启服务端调用模式
set serverout on;
--使用sql调用函数
declare msg varchar2(20);
begin
  msg:=get_hello_msg;
  dbms_output.put_line(msg);
end;
/
--使用select查询函数
select get_hello_msg from dual;--hello world
--不适用括号对函数产生的冲突 结果为 “variable”，所以在编写函数时候注意不要使用同名的变量
declare 
  msg varchar2(20);
  get_hello_msg varchar2(20);
begin
  get_hello_msg:='variable';
  msg:=get_hello_msg();
  dbms_output.put_line(msg);
end;
/
--避免冲突使用小括号
declare 
  msg varchar2(20);
begin
  msg:=get_hello_msg();
  dbms_output.put_line(msg);
end;
/
--创建函数计算员工应发工资
create or replace function get_tax(p_salary number)
return number as
begin
  declare
    tax_salary number;
  begin
      tax_salary:=p_salary-2000;
      if tax_salary<=0 then
        return 0;
      end if;
      if tax_salary<=500 then
        return tax_salary*5/100;
      end if;
      if tax_salary<=2000 then
        return tax_salary*10/100-25;
      end if;
      if tax_salary<=5000 then
        return tax_salary*15/100-125;
      end if;
      if tax_salary<=20000 then
        return tax_salary*20/100-375;
      end if;
      if tax_salary<=40000 then
        return tax_salary*25/100-1375;
      end if;
      if tax_salary<=6000 then
        return tax_salary*30/100-3375;
      end if;
    end;
end get_tax;
--调用函数get_tax
select get_tax(2200) from dual;
--创建确定性函数-->函数的确定性：如果给予的入参不变，得到的返回值也不变，那么可以使用deterministic修饰返回值,有利于数据库查询性能
create or replace function get_tax(p_salary number)
return number
deterministic as
begin
  declare
    tax_salary number;
  begin
     ...
    end;
end get_tax;

--【行转列问题】
--创建学生表students
create table students(
       student_id number not null,
       student_name varchar(10),
       student_age number,
       primary key(student_id)
);
--插入数据
insert into students(student_id,student_name,student_age) values(1,'金瑞',14);
insert into students(student_id,student_name,student_age) values(2,'钟君',15);
insert into students(student_id,student_name,student_age) values(3,'王山',14);
insert into students(student_id,student_name,student_age) values(4,'刘迪',14);
insert into students(student_id,student_name,student_age) values(5,'钟会',14);
insert into students(student_id,student_name,student_age) values(6,'张玉',14);
insert into students(student_id,student_name,student_age) values(7,'柳青',14);
insert into students(student_id,student_name,student_age) values(8,'胡东',14);
insert into students(student_id,student_name,student_age) values(9,'商乾',14);
insert into students(student_id,student_name,student_age) values(10,'周明',14);
--查看
select * from students;
--创建函数查看学生的姓名，将列转成行查看
create or replace function get_name_str
return varchar2
as
begin
  declare cursor cu_student is
     select student_name from students order by student_id;
     student_name varchar2(10);
     rowStr varchar2(500);
  begin
     open cu_student;
     fetch cu_student into student_name;
     while cu_student%found loop
       rowStr:=rowStr || student_name || ',';
       fetch cu_student into student_name;
     end loop;
     return substr(rowStr,1,length(rowStr)-1);
   end;
end get_name_str;
--查看行转列的结果
select get_name_str() from dual;
--结果: 金瑞,钟君,王山,刘迪,钟会,张玉,柳青,胡东,商乾,周明