1、基本结构：

CREATE OR REPLACE PROCEDURE 存储过程名字  
（参数1 IN NUMBER,参数2 IN NUMBER）  
AS  
变量1 INTEGER :=0;  
变量2 DATE;  
BEGIN  
END 存储过程名字  

2、无参形式的procedure：

--无参procedure  
create or replace procedure pro\_no\_param  
is  
begin  
  dbms\_output.put\_line('the procedure without params');  
end pro\_no\_param;  

--调用  
--one: 无参的procedure名字后面必须要()；  
call pro\_no\_param();  

--two:procedure名称后面可以没有();  
begin  
  pro\_no\_param();  
end;  

3、参数类型为IN的procedure：

--有参procedure 只有IN类型
create or replace procedure pro_in_param(
v_1 in number,
v_2 in varchar2,
v_3 in date
)
is
begin
dbms_output.put_line('v1: ' || v_1 || ' v2: ' || v_2 || ' v2: '|| (to_char(v_3, 'yyyy-mm-dd')));
end pro_in_param;

begin
pro_in_param(1, 'chy', sysdate);
end;

4、参数类型为OUT的procedure：

--有参procedure  只有OUT类型  
create or replace procedure pro\_out\_param(  
       v1 out number,  
       v2 out char  
)  
is  
begin  
  v1 := 2;  
  v2 := 'andyChen';  
end pro\_out\_param;  

--记得声明用于存放procedure的out值的变量  
--语句结束了一定记得结尾的 —— ;  
declare  
  v\_1 number;  
  v\_2 varchar2(200);  
begin  
  pro\_out\_param(v\_1, v\_2);  
  dbms\_output.put\_line('v1: ' || v\_1 || ' v2: ' || v\_2);  
end;  

5、参数类型同时为IN和OUT的procedure：

--同时为INOUT参数的procedure
--用同一变量接收传入的值然后将这个变量当作输出的值赋给执行时声明的变量
create or replace procedure pro_in_out_param(
in_out_param in out varchar2
)
is
begin
in_out_param := 'in_out_param and ' || in_out_param;
end pro_in_out_param;

declare
in_out_param varchar2(222) := 'detail param';
begin
pro_in_out_param(in_out_param);
dbms_output.put_line(in_out_param);
end;

CREATE TABLE user\_info  
(  
 id   VARCHAR2(4) not null primary key,  
 name VARCHAR2(15),  
 pwd  VARCHAR2(15),  
 address VARCHAR2(30)  
);  

--创建一个添加用户的stored\_procedure;  
create or replace procedure pro\_addUser(  
       n\_id user\_info.id%type,  
       n\_name user\_info.name%type,  
       n\_pwd     user\_info.pwd%TYPE,  
       n\_address user\_info.address%TYPE  
)  
as  
begin  
  --插入数据  
  insert into user\_info(id,name,pwd,address)  
  values(n\_id, n\_name, n\_pwd, n\_address);  
end pro\_addUser;  

--调用、有变量需要声明的时候才有declare、没有就直接begin  
begin  
    pro\_addUser('1', 'chy', 'admin', 'nanjin');  
    if SQL%found then  
      dbms\_output.put\_line('add successed');  
    end if;  
end;  

--根据id查询用户名和密码  
create or replace procedure pro\_getUserInfo(  
       n\_id       user\_info.id%type,  
       n\_name out user\_info.name%type,  
       n\_pwd  out user\_info.pwd%type  
)  
as  
begin  
  select user\_info.name, user\_info.pwd into n\_name, n\_pwd  
  from user\_info  
  where user\_info.id=n\_id;  
end pro\_getUserInfo;  

--调用  
declare  
    v\_id    user\_info.id%type := '1';  
    v\_name  user\_info.name%type;  
    v\_pwd   user\_info.pwd%type;  
begin  
    pro\_getUserInfo(v\_id, v\_name, v\_pwd);  
    dbms\_output.put\_line('name: ' || v\_name || ' pwd: ' || v\_pwd);  
end;  

-- 打印九九乘法表  
create or replace procedure pro\_multiplication\_table  
is  
       i  integer;  
       j  integer;  
begin  
       for i in 1..9 loop  
         for j in 1..9 loop  
           if i>=j then  
             DBMS\_output.put(To\_Char(j)||'\*'||to\_char(i)||'='||to\_char(i\*j)||'　　 ');  
           end if;  
         end loop;  
         DBMS\_output.put\_line('');  
       end loop;  
end  pro\_multiplication\_table;        

--调用  
call pro\_multiplication\_table();  

--使用自定义游标、根据工作and薪水查询员工姓名  
create or replace procedure pro\_getName(  
       n\_sal        emp.sal%type,  
       n\_ename  out emp.ename%type,  
       n\_job in out emp.job%type  
)  
is  
       n\_count number;  
       cursor cur is select ename from emp where emp.sal > n\_sal and emp.job=n\_job;  
       n\_row  cur%rowtype;  
begin  
       select count(\*) into n\_count from emp where emp.sal > n\_sal and emp.job=n\_job;  
       if n\_count > 1 then  
         for n\_row in cur loop  
           DBMS\_output.put\_line('职工姓名为:'||n\_row.ename||'    工作为:'||n\_job);  
         end loop;  
       else  
           DBMS\_output.put\_line('未查到符合条件的记录！');  
       end if;  
end  pro\_getName;        

-- 调用  
declare  
   v\_sal   emp.sal%type := 2000;  
   v\_job   emp.job%type :='MANAGER';  
   v\_ename emp.ename%type;  
begin  
   pro\_getName(v\_sal, v\_ename, v\_job);  
end;     

--ref cursor的使用  
--创建存放弱引用和强引用的cursor的包  
create or replace package refcursor\_pkg  
as  
type weak\_ref\_cursor is ref cursor;  
type strong\_ref\_cursor is ref cursor return emp%rowtype;  
end refcursor\_pkg;  

--将弱引用的cursor作为结果返回  
create or replace procedure test(  
       p\_deptno in number,  
       p\_cursor out refcursor\_pkg.weak\_ref\_cursor  
)  
is begin  
       open p\_cursor for select \* from emp where deptno=p\_deptno;  
end test;  

/\*\*或者不用包直接使用下面这种定义  
create or replace procedure test\_1(  
       p\_deptno IN number,  
       p\_cursor OUT SYS\_REFCURSOR  
)  
is  
begin  
  open p\_cursor FOR select \*from emp where  deptno = p\_deptno;  
end test\_1;  
\*/  

declare  
    v\_deptno number := 20;  
    v\_cursor refcursor\_pkg.weak\_ref\_cursor;  
    r\_emp emp%rowtype;  
begin  
    test(v\_deptno, v\_cursor);  
    loop  
      fetch v\_cursor into r\_emp;  
      exit when v\_cursor%notfound;  
      dbms\_output.put\_line('empno: ' || r\_emp.empno || ' ename: ' || r\_emp.ename || ' job: ' || r\_emp.job);  
    end loop;  
    close v\_cursor;  
end;  
/\*\*  

//java中使用ref cursor  

public void method() throws SQLException{  
  Connection conn = getConnection();  
  CallableStatement cstmt = null;  
  ResultSet rs = null;  
  int deptno = 10;  
  Object temp;  
  try{  
      cstmt = conn.prepareCall("begin  test(?,?); end;");  
      cstmt.setInt(1, deptno);  
      cstmt.registerOutParameter(2, OracleTypes.CURSOR);  
      cstmt.execute();  
      rs = (ResultSet) cstmt.getObject(2);  
      ResultSetMetaData rsm = rs.getMetaData();  
      int columnCount = rsm.getColumnCount();  
      while (rs.next()){  
         for (int j=0;j< columnCount;j++){  
            temp = rs.getObject(j+1);  
         }  
      }  
  } finally {  
      if (!rs==null){  
        rs.close();  
      }  
      if (!stmt==null){  
        stmt.close();  
      }  
      if (!conn==null){  
        conn.close();  
      }  
  }  
}  
\*/