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;