1.
创建一个返回字符串的函数
create or replace function get_hello_msg
return varchar2 as
begin
return 'hello world';
end get_hello_msg;
查看函数的类型和状态
select object_name, object_type, status from user_objects where lower(object_name)='get_hello_msg';
user_source用于查询数据库中定义的函数和存储过程的代码
select name,type,line,text from user_source where lower(name)='get_hello_msg';
select get_hello_msg() from dual;
2. 创建一个获得税收的函数
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<= 60000 then
return tax_salary*30/100 - 3375;
end if;
end;
end get_tax;
函数的调用：
select get_tax(6000) tax from dual;
3：一个表中含有学生姓名字段，为了获得所有学生姓名，必须要对数据表中的数据循环处理，以获得每个学生的姓名，并将所有学生姓名
的字符串串联起来，可以创建一个函数来处理该过程。
create or replace
function get_student_string
return varchar2
as
begin
declare cursor cu_student is
select student_name from students
order by student_id;
student_nane varchar2(10);
rowString varchar2(500);
begin
open cu_student;
fetch cu_student into student_name; //将游标所指向的当前记录的数据赋值给student_name
while cu_student%found loop //用于循环处理游标所指向的记录
rowString:=rowString || student_name || ', ';
fetch cu_student into student_name;
end loop;
return substr(rowString, 1, length(rowString) - 1);
end;
end get_student_string;
4：存储过程
create or replace procedure update_students
as
begin
update students set student_age=10;
commit;
end update_students;
select object_name, object_type, status from user_objects where lower(object_name)='update_students';
select * from user_source where lower(name)='update_students';
execute update_students;
5：存储过程-in参数
create or replace
procedure update_students(in_age in number) as
begin
update student set student_age=in_age;
commit;
end update_students;
6：存储过程-out参数
create or replace
procedure update_students(in_age in number, out_age out number) as
begin
update students set student_age=in_age;
select student_age into out_age from students where student_id=1;
commit;
end update_students;7：存储过程的参数---IN OUT 参数
利用IN OUT参数来交换两个变量的值
create or replace procedure swap(in_out_param1 in out number, in_out_param2 in out number)
as
begin
declare param number;
begin
param:=in_out_param1;
in_out_param1 := in_out_param2;
in_out_param2 := param;
end;
end;
8: 以视图user_objects中的数据为例子，将其作为源表，创建新表target
create table target (object_id number, object_name varchar2(30), object_type varchar2(30), previous_name varchar2(30),
status varchar2(30));值得注意的是，列previous_name是指，所有记录按照Object_id进行升序排列，处于当前记录之前的那条记录的object_name的列值。
利用存储过程来解决这个问题。
create or replace procedure insert_objects as
begin
declare
cursor cu_objects is
select * from user_objects order by object_id;
obj user_objects%rowtype;
previous_name user_objects.object_name%type;
begin
open cu_objects;
fetch cu_objects into obj;
while cu_objects%found loop
insert into target
values
(
obj.object_id,
obj.object_name,
obj.object_type,
previous_name,
obj.status
);
previous_name := obj.object_name;
fetch cu_objects into obj;
end loop;
end;
end insert_objects;
