举个例子SQL> create function fun1 return varchar2 2 as 3 begin 4 return 'hello world'; 5 end ; 6 /Function created.SQL> variable v_mes varchar2(20); SQL> execute :v_mes:=fun1;PL/SQL procedure successfully completed.SQL> print v_mesV_MES -------------------------------------------------------------------------------- hello world 或者 SQL> select fun1 from dual;FUN1 -------------------------------------------------------------------------------- hello world
下面是一个内置存储过程,不防参考一下 --this example is about how to create and use procedure create or replace procedure P_UpdateRecord (v_ncol number, v_vcol varchar2) as begin update newtest set vcol = v_vcol where ncol= v_ncol; commit; end P_UpdateRecord;
-- when the procedure has been created we can use it declare n_col newtest.ncol%type; v_col newtest.vcol%type; begin select ncol,vcol into n_col,v_col from newtest where ncol = 1 ; dbms_output.put_line(n_col||' '||v_col); P_UpdateRecord(n_col,'杨红'); select vcol into v_col from newtest where ncol = 1 ; dbms_output.put_line(n_col||' '||v_col); end; /
2 as
3 begin
4 return 'hello world';
5 end ;
6 /Function created.SQL> variable v_mes varchar2(20);
SQL> execute :v_mes:=fun1;PL/SQL procedure successfully completed.SQL> print v_mesV_MES
--------------------------------------------------------------------------------
hello world
或者
SQL> select fun1 from dual;FUN1
--------------------------------------------------------------------------------
hello world
--this example is about how to create and use procedure
create or replace procedure P_UpdateRecord
(v_ncol number, v_vcol varchar2) as
begin
update newtest set vcol = v_vcol where ncol= v_ncol;
commit;
end P_UpdateRecord;
-- when the procedure has been created we can use it
declare
n_col newtest.ncol%type;
v_col newtest.vcol%type;
begin
select ncol,vcol into n_col,v_col from newtest
where ncol = 1 ;
dbms_output.put_line(n_col||' '||v_col);
P_UpdateRecord(n_col,'杨红');
select vcol into v_col from newtest
where ncol = 1 ;
dbms_output.put_line(n_col||' '||v_col);
end;
/