oracle创建存储过程,函数基本语法

基本语法

、Oracle创建存储过程

1、基本语法 create or replace procedure update_emp_sal

(Name in out type,

Name in out type, ...

) is

begin

end

update_emp_sal;

2

、写一个简单的例子修改emp表的ename字段

create or replace procedure update_emp

(

v_empno varchar2,

v_ename varchar2

) is

begin

update emp set ename=v_ename where empno=v_empno;

end update_emp;

调用方法如下：

SQL>exec update_emp('7935','test');

2、有返回值的存储过程

就写一个简单的返回empno=7935的sal值

create or replace procedure emp_out_sal

(

v_empno in varchar2,

v_sal out number

) is

vsal number(7,2);

begin

基本语法

select sal into vsal from emp where empno=v_empno;

v_sal:=vsal;

end;

调用有返回值的过程

SQL> var vsal number

SQL> exec emp_out_sal('7935',:vsal);

PL/SQL procedure successfully completed

vsal

---------

700

SQL> var vsal number

SQL> call emp_out_sal('7935',:vsal);

Method called

vsal

---------

700

二、Oracle创建函数（function）

1、基本语法规则如下：

create or replace function (Name in type, Name in type, ...) return number is

Result number;

begin

return (Result);

end ;

2、写一个简单的查询例子查询出empno=7935的sal值

create or replace function ret_emp_sal(v_ename varchar2)

return number

is

基本语法

v_sal number(7,2);

begin

select nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);

return v_sal; end

;

调用此函数：

SQL> var vsla number

SQL> call ret_emp_sal('7935') into :vsal;

Method called

vsal

---------

700

三、Oracle创建包

包用于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。包规范用于定义公用的常量、变量、过程和函数，创建包规范可以使用CREATE PACKAGE命令，创建包体可以使用CREATE PACKAGE BODY

1、创建包规范

create package emp_pkg is

procedure emp_update_ename(v_empno varchar2,v_ename varchar2); function emp_get_sal(v_empno varchar2) return number;

end;

2、创建包体

create or replace package body emp_pkg

is

procedure emp_update_ename

(

v_empno varchar2,

v_ename varchar2

)

is

vename varchar2(32);

begin

update emp set ename=v_ename where empno=v_empno;

基本语法

commit;

select ename into vename from emp where empno=v_empno;

dbms_output.put_line('雇员名称：'||vename);

end;

function emp_get_sal

(

v_empno varchar2

)

return number is

vsal number(7,2);

begin

select sal into vsal from emp where empno=v_empno;

return

vsal;

end;

end;

在此提示，在没有创建包规范就创建包体，会失败，要使用包，必须先创建包规范，然后在创建包体 当要调用包的过程和函数时，在过程和函数的名称前加上包名作为前缀（包名.子程序名称），而如果要访问其他方案的包时需要在包的名称前加上方案的名称

（方案名称.包名.子程序名称）

实例：

SQL> var vsla number

SQL> call emp_pkg.emp_get_sal('7935') into :vsal;

Method called

vsal

---------

700

SQL> exec emp_pkg.emp_update_ename('7935','helong');

雇员名称：helong

SQL> call emp_pkg.emp_update_ename('7935','helong');

基本语法

Method called

SQL> select * from dual;

DUMMY

-----

X

雇员名称：helong