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;

總結

以上是生活随笔為你收集整理的oracle创建函数语句,Oracle 创建函数与存储过程语句积累的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。