函数:
函数必须有返回值,可以有参数
create or replace function get_hello_msg
return varchar2 as
begin
return 'hello';
end get_hello_msg;
在数据字典中查看函数的信息:
select object_name,object_type,status from user_objects where object_name = 'GET_HELLO_MSG';
查看函数定义:
select name,type,line,text from user_source where name = 'GET_HELLO_MSG';
查看函数的返回值:
set serverout on;
declare msg varchar2(20);
begin
msg:=get_hello_msg;
dbms_output.put_line(msg);
end;
调用函数:
select get_hello_msg msg from dual;
创建带参数的函数:
create or replace function get_tax(p_salary number)
return number as
begin
declare tax_salary numbe
r;
tax_salary := p_salary - 200;
return tax_salary;
end get_tax;
存储过程
过程不需要返回值,可以有参数
--创建过程,所有员工工资加1
create or replace procedure update_salary
as
begin
update employees set salary = salary+1;
commit;
end update_salary;
--在代码块中执行过程
begin
update_salary;
end;
--或者
execute update_salary;
--查看是否执行成功
select salary from employees;
--查看存储过程在数据字典中的定义
select object_name,object_type,status from user_objects where object_name = 'UPDATE_SALARY';
--查看存储过程的信息
select * from user_source where name = 'UPDATE_SALARY';
create or replace procedure update_salary
as
begin
update employees set salary = salary+1;
commit;
end update_salary;
--在代码块中执行过程
begin
update_salary;
end;
--或者
execute update_salary;
--查看是否执行成功
select salary from employees;
--查看存储过程在数据字典中的定义
select object_name,object_type,status from user_objects where object_name = 'UPDATE_SALARY';
--查看存储过程的信息
select * from user_source where name = 'UPDATE_SALARY';