函数:类似于编程语言中的函数,可以有入参,必须有返回值。
--创建函数
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;
--结果: 金瑞,钟君,王山,刘迪,钟会,张玉,柳青,胡东,商乾,周明