1.存储过程
创建格式
create or replace function test_fun
return date
is
v_date date;
begin
select sysdate into v_date
from dual;
dbms_output.put_line('我是函数');
return v_date;
end;
创建存储过程:
create or replace procedure getName(ename out varchar) is
begin
select per.real_name into ename from tms_person per
where per.person_id ='120098';
dbms_output.put_line('aaaaaa');
dbms_output.put_line('ename='||ename);
end;
在调用
declare
v_name varchar(12);
begin
getName(ename=>v_name);
dbms_output.put_line('v_name='||v_name);
end;
也可以用call getName() 调用
例子二:
create or replace procedure getName(ename out varchar,code out number) is
begin
select per.real_name,per.certi_code into ename,code from tms_person per
where per.person_id ='120098';
dbms_output.put_line('aaaaaa');
dbms_output.put_line('ename='||ename||'code='||code);
end;
调用程序
declare
v_name varchar(12);
v_code number;
begin
getName(ename=>v_name,code => v_code);
dbms_output.put_line('v_name='||v_name||'v_code='||v_code);
end;
out: 表示输出参数
in:表示输入参数
注意:ename 要与查询到返回的名称一致。
CREATE OR REPLACE
FUNCTION testFunc (num1 IN NUMBER, num2 IN NUMBER)
RETURN NUMBER
AS
num3 number;
num4 number;
num5 number;
BEGIN
num3 := num1 + num2;
num4 := num1 * num2;
num5 := num3 * num4;
RETURN num5;
END;