PL/SQL复习九 存储过程

无参数的存储过程：

create or replace procedure out_time

is

begin

  dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));

end;

/

调用： exec out_time;

--------------------------------------------------------------------------------------------------

带有in参数的过程：

create or replace procedure add_employee

(

  eno in number,

  name in varchar2,--注意，定义参数的时候不能指定大小

  sal in number

)

is

begin

  insert into emp(empno,ename,sal) values(eno,name,sal);

end;

/

调用：exec add_employee(5569,'lixin',5589);

--------------------------------------------------------------------------------------------------

带有out 参数的过程：

create or replace procedure query_employee

(

  eno in number,

  name out varchar2,

  salary out number

)

is

begin

  select ename, sal into name, salary from emp where empno = eno;

end;

/

调用：

SQL> var name varchar2(20);

SQL> var salary number;

SQL> exec query_employee(7788,:name,:salary);

 

PL/SQL procedure successfully completed

name

---------

SCOTT

salary

---------

3000

--------------------------------------------------------------------------------------------------

 

带有in out参数的过程：

create or replace procedure pro_in_out

(

  num1 in out number,

  num2 in out number

)

is 

  v1 number;

  v2 number;

begin

  v1 := num1 / num2;

  v2 := mod(num1,num2);

  num1 := v1;

  num2 := v2;

end;

/

调用：

SQL> var n1 number;

SQL> var n2 number;

SQL> exec :n1 := 100;

SQL> exec :n2 := 30;

SQL> exec pro_in_out(:n1, :n2); --不能直接传值,必须传变量,因为是in out类型参数

 

PL/SQL procedure successfully completed

n1

---------

3.33333333333333

n2

---------

10

 

传递参数时可以根据位置传递，也可以根据名称传递：

exec add_employee(123,name=>'lixin',sal=>88564);

 

查看过程源码：

select text from user_source where name = 'ADD_EMPLOYEE';

 

删除过程：

drop procedure add_employee;

 

-----------------------------------------------------------------------------------------------------

 

java程序调用存储过程：