求大家给一些有关oracle存储过程的资料与例子详解。

Question

求大家给一些有关oracle存储过程的资料与例子详解。我是新手，想在pl sql developer中写一个存储过程，并能显示出结果来的sql.求大家写一个简单的，加上注解，给我指点一下。谢谢，有资料给我也行。.

Answer 1

去下个教程
http://download.csdn.net/down/1498183/kerafan

Answer 2

   1. create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as    
   2. begin    
   3.   select NAME into name_out from test where AGE = age_in;    
   4. end;    
   5.   
   6. create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is   
   7. begin   
   8.   insert into test values (UserID, UserName, UserAge);   
   9. end;   首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. create sequence TEST_SEQ    
   2. minvalue 100    
   3. maxvalue 999    
   4. start with 102    
   5. increment by 1    
   6. nocache;   语法应该是比较易懂的，最小最大值分别用 minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了 2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用 test_seq.currval访问当前的序列号。    定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. create or replace procedure InsertRecordWithSequence(UserID   out number,UserName in varchar2,UserAge  in number)    
   2. is    
   3. begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取    
   4. values(test_seq.nextval, UserName, UserAge);    
   5. /*返回PK值。注意Dual表的用法*/    
   6. select test_seq.currval into UserID from dual;       
   7. end InsertRecordWithSequence;   为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. create or replace package TestPackage is    
   2.     type mycursor is ref cursor; -- 定义游标变量    
   3.      procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数    
   4. end TestPackage;      
   5. 包体是这么定义的：    
   6. create or replace package body TestPackage is    
   7. /*过程体*/    
   8.           procedure GetRecords(ret_cursor out mycursor) as    
   9.           begin    
  10.               open ret_cursor for select * from test;    
  11.           end GetRecords;    
  12. end TestPackage;   小结：    包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用 DataReader的NextResult()方法前进到下一个游标。Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. create or replace package TestPackage is    
   2.      type mycursor is ref cursor;    
   3.      procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);    
   4.      procedure SelectRecords(ret_cursor out mycursor);    
   5.      procedure DeleteRecords(id_in in number);    
   6.      procedure InsertRecords(name_in in varchar2, age_in in number);    
   7. end TestPackage;   包体如下：Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. create or replace package body TestPackage is   
   2.     procedure UpdateRecords(id_in in number, newName in varchar2, newAge  in number) as   
   3.     begin   
   4.      update test set age = newAge, name = newName where id = id_in;   
   5.     end UpdateRecords;   
   6.   
   7.     procedure SelectRecords(ret_cursor out mycursor) as   
   8.     begin   
   9.        open ret_cursor for select * from test;   
  10.     end SelectRecords;   
  11.   
  12.     procedure DeleteRecords(id_in in number) as   
  13.     begin   
  14.        delete from test where id = id_in;   
  15.     end DeleteRecords;  
  16.  
  17.     procedure InsertRecords(name_in in varchar2, age_in in number) as   
  18.     begin   
  19.        insert into test values (test_seq.nextval, name_in, age_in);    
  20.     --test_seq是一个已建的Sequence对象，请参照前面的示例    
  21.     end InsertRecords;   
  22.     end TestPackage;   TestPackage.SelectRecords-------------------------------------------------------------------------------------------------------------------------------------------------------------oracle 存储过程的基本语法1.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)  例子：  BEGIN  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;  EXCEPTION  WHEN NO_DATA_FOUND THEN      xxxx;  END;  ...3.IF 判断  IF V_TEST=1 THEN    BEGIN       do something    END;  END IF;4.while 循环  WHILE V_TEST=1 LOOP  BEGINXXXX  END;  END LOOP;5.变量赋值  V_TEST := 123;6.用for in 使用cursor  ...  IS  CURSOR cur IS SELECT * FROM xxx;  BEGINFOR cur_result in cur LOOP  BEGIN   V_SUM :=cur_result.列名1+cur_result.列名2  END;END LOOP;  END;7.带参数的cursor  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;  OPEN C_USER(变量值);  LOOPFETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;    do something  END LOOP;  CLOSE C_USER;8.用pl/sql developer debug  连接数据库后建立一个Test WINDOW  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试-------------------------------------------------------------------------------------------------------------------------------------------------------------

Answer 3

oracle存储过程一例By  凌云志 发表于 2007-4-18 17:01:00  最近换了一个项目组，晕，要写oracle的存储过程，幸亏写过一些db2的存储过程，尚且有些经验，不过oralce的pl/sql不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远   1. CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS   
   2. ------------------------------------------------------------------------   
   3. -- Oracle 包   
   4. ---国航支付平台VISA退款   
   5. -- 游标定义：   
   6. --   
   7. -- 存储过程定义：   
   8. -- PY_WEBREFUND_VISA_PREPARE  ： VISA退款准备   
   9. -- 最后修改人：dougq   
  10. -- 最后修改日期：2007.4.17  
  11. ------------------------------------------------------------------------   
  12.   
  13.  PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
  14.   in_serialNoStr   IN  VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
  15.   in_session_operatorid IN VARCHAR2, --业务操作员   
  16.   out_return_code     OUT VARCHAR2, --存储过程返回码   
  17.   out_visaInfoStr     OUT VARCHAR2   
  18.  );   
  19.     
  20. END PY_PCKG_REFUND2;   
  21. /   
  22.   
  23.   
  24. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS   
  25.     
  26.  PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
  27.   in_serialNoStr      IN  VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
  28.   in_session_operatorid IN VARCHAR2,--业务操作员   
  29.   out_return_code     OUT VARCHAR2, --存储过程返回码   
  30.   out_visaInfoStr     OUT VARCHAR2   
  31.  ) IS   
  32.   --变量声明   
  33.   v_serialno  VARCHAR2(20);--网上退款申请流水号   
  34.   v_refserialno VARCHAR2(20);--支付交易流水号   
  35.   v_tobankOrderNo VARCHAR2(30);--上送银行的订单号   
  36.   v_orderDate  VARCHAR2(8);--订单日期   
  37.   v_businessType VARCHAR2(10);--业务类型   
  38.   v_currType  VARCHAR2(3);--订单类型（ET－电子机票）   
  39.   v_merno   VARCHAR2(15);--商户号   
  40.   v_orderNo  VARCHAR2(20);--商户订单号   
  41.   v_orderState VARCHAR2(2);   
  42.   v_refAmount     NUMBER(15,2);--退款金额    
  43.   v_tranType  VARCHAR(2);--交易类型   
  44.   v_bank   VARCHAR2(10);--收单银行   
  45.   v_date   VARCHAR2 (8);--交易日期   
  46.       v_time   VARCHAR2 (6);--交易时间   
  47.       v_datetime  VARCHAR2 (14);--获取的系统时间   
  48.   v_index_start NUMBER;   
  49.   v_index_end  NUMBER;   
  50.   v_i    NUMBER;   
  51.  BEGIN   
  52.   -- 初始化参数   
  53.   out_visaInfoStr := '';   
  54.   v_i := 1;   
  55.   v_index_start := 1;   
  56.   v_index_end := INSTR(in_serialNoStr,'|',1,1);    
  57.   v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);   
  58.   v_datetime := TO_CHAR (SYSDATE, 'yyyymmddhh24miss');   
  59.   v_date := SUBSTR (v_datetime, 1, 8);   
  60.   v_time := SUBSTR (v_datetime, 9, 14);   
  61.   
  62.   --从退款请求表中查询定单信息（商户号、商户订单号、退款金额）   
  63.   WHILE v_index_end > 0 LOOP   
  64.    SELECT   
  65.     WEBR_MERNO,   
  66.     WEBR_ORDERNO,   
  67.     WEBR_AMOUNT,   
  68.     WEBR_SERIALNO,   
  69.     WEBR_REFUNDTYPE   
  70.    INTO   
  71.     v_merno,   
  72.     v_orderNo,   
  73.     v_refAmount,   
  74.     v_serialno,   
  75.     v_tranType   
  76.       FROM    
  77.     PY_WEB_REFUND   
  78.       WHERE    
  79.     WEBR_REFREQNO = v_refserialno;   
  80.       
  81.    --将查询到的数据组成串   
  82.    out_visaInfoStr := out_visaInfoStr || v_merno || '~' || v_orderNo || '~' || v_refAmount + '|';   
  83.      
  84.    --为下次循环做数据准备   
  85.       v_i := v_i + 1;   
  86.       v_index_start := v_index_end + 1;   
  87.       v_index_end := INSTR(in_serialNoStr,'|',1,v_i);   
  88.       IF v_index_end > 0 THEN   
  89.         v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end - 1);         
  90.       END IF;   
  91.          
  92.    --根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO   
  93.    SELECT   
  94.     WTRN_TOBANKORDERNO,   
  95.     WTRN_ORDERNO,   
  96.       WTRN_ORDERDATE,   
  97.       WTRN_BUSINESSTYPE,   
  98.     WTRN_ACCPBANK,   
  99.     WTRN_TRANCURRTYPE   
 100.    INTO   
 101.     v_tobankOrderNo,   
 102.     v_orderNo,   
 103.     v_orderDate,   
 104.     v_businessType,   
 105.     v_bank,   
 106.     v_currType   
 107.    FROM PY_WEBPAY_VIEW   
 108.     WHERE WTRN_SERIALNO = v_serialno;   
 109.        
 110.    --记录流水表(退款)   
 111.       INSERT INTO PY_WEBPAY_TRAN(   
 112.     WTRN_SERIALNO,   
 113.     WTRN_TRANTYPE,    
 114.     WTRN_ORIGSERIALNO,   
 115.     WTRN_ORDERNO,    
 116.     WTRN_ORDERDATE,    
 117.     WTRN_BUSINESSTYPE,   
 118.     WTRN_TRANCURRTYPE,   
 119.     WTRN_TRANAMOUNT,   
 120.     WTRN_ACCPBANK,    
 121.     WTRN_TRANSTATE,    
 122.     WTRN_TRANTIME,   
 123.     WTRN_TRANDATE,    
 124.     WTRN_MERNO,    
 125.     WTRN_TOBANKORDERNO   
 126.    )VALUES(   
 127.     v_refserialno, --和申请表的流水号相同，作为参数传人   
 128.     v_tranType,   
 129.     v_serialno, --原交易流水号，查询退款申请表得到   
 130.     v_orderNo,   
 131.     v_orderDate,   
 132.     v_businessType,   
 133.     v_currType,   
 134.     v_refAmount,   
 135.     v_bank,   
 136.     '1',   
 137.     v_time,   
 138.     v_date,   
 139.     v_merno,   
 140.     v_tobankOrderNo --上送银行的订单号，查询流水表得到   
 141.    );   
 142.   
 143.    --更新网上退款申请表   
 144.    UPDATE PY_WEB_REFUND   
 145.    SET    
 146.     WEBR_IFDISPOSED = '1',   
 147.     WEBR_DISPOSEDOPR = in_session_operatorid,   
 148.     WEBR_DISPOSEDDATE = v_datetime   
 149.    WHERE    
 150.     WEBR_REFREQNO = v_refserialno;   
 151.       
 152.    --更新定单表   
 153.    IF v_tranType = '2' THEN   
 154.     v_orderState := '7';   
 155.    ELSE   
 156.     v_orderState := '10';   
 157.    END IF;   
 158.     
 159.    UPDATE PY_ORDER   
 160.    SET   
 161.     ORD_ORDERSTATE = v_orderState   
 162.    WHERE   
 163.      ORD_ORDERNO = v_orderNo   
 164.     AND ORD_ORDERDATE = v_orderDate   
 165.     AND ORD_BUSINESSTYPE = v_businessType;    
 166.   END LOOP;   
 167.     
 168.   -- 异常处理   
 169.   EXCEPTION   
 170.    WHEN OTHERS THEN   
 171.    ROLLBACK;   
 172.    out_return_code := '14001';   
 173.    RETURN;    
 174.  END;   
 175.   
 176. END PY_PCKG_REFUND2;   
 177. /  

Answer 4

比如：我写了一个简单的：
CREATE OR REPLACE PROCEDURE skeleton (
    ID IN VARCHAR2,
    KValue OUT VARCHAR2
)
　　IS
    bb varchar2(50);
　　BEGIN
　　select ID into KValue from M_user;
　　END;
EXECUTE skeleton;
怎样才能显示出来结果值呀？

Answer 5

好吧：
drop table emp;
create table emp(A varchar2(10),B varchar2(10),C varchar2(10));
insert into emp values('1','1','1');
insert into emp values('2','2','2');
insert into emp values('3','3','3');commit;set serveroutput on--该存储过程根据 in 参数 i_A 查出表的 B列赋给out参数 o_B
create or replace procedure p_test_dym(
i_A in varchar2,
o_B out varchar2
)
as 
begin
select B into o_B from emp where emp.A = i_A; 
end;
/variable b varchar2(10);
exec p_test_dym('1',:b);
print b;create or replace procedure p_call_dym(
v_proc in varchar2,
v_param in varchar2
)
as
v_2 emp.B%TYPE;
begin
--调用刚才创建的存储过程
execute immediate 'call '||v_proc||'(:1,:2)' using in v_param,out v_2;
--打印数据
dbms_output.put_line(v_2);
end;
/--调用动态存储过程。
exec p_call_dym('p_test_dym','2');

Answer 6

前面加 
set serveroutput on,
里面就可以用dbms_output.put_line
跟java的System.out.println或者c里面的printf一样。在sqlplus里面用 exec procname(param)就可以
如果又输出参数，用print打印，看上面那个例子

Answer 7

drop table emp; 
create table emp(A varchar2(10),B varchar2(10),C varchar2(10)); 
insert into emp values('1','1','1'); 
insert into emp values('2','2','2'); 
insert into emp values('3','3','3'); commit;set serveroutput on 
create or replace procedure p_test_dym( 
i_A in varchar2, 
o_B out varchar2 
) 
as 
begin 
select B into o_B from emp where emp.A = i_A; 
end; variable b varchar2(10); 
exec p_test_dym('1',:b); 
print b; 大哥，这个在sql developer中运行说有错呀？比如variable ,好像没有这个关键字，你是在那里运行可以的？sql plus中吗？

Answer 8

plsqldev直接有调试模式，可以窗口输入参数，有窗口输出参数。不过我不常用。我都用sqlplus来做存储过程、函数等等。

Answer 9

我把刚才的代码全粘在sql plus下了。没报错，但是死掉了，没出结果。我给你粘的那段。

Answer 10

你把 / 去掉了。/在sqlplus中表示创建type/procedure/function...

Answer 11

SQL> create table emp(A varchar2(10),B varchar2(10),C varchar2(10));表已创建。SQL> insert into emp values('1','1','1');已创建 1 行。SQL> insert into emp values('2','2','2');已创建 1 行。SQL> insert into emp values('3','3','3');已创建 1 行。SQL>
SQL> commit;提交完成。SQL>
SQL> set serveroutput on
SQL> create or replace procedure p_test_dym(
  2  i_A in varchar2,
  3  o_B out varchar2
  4  )
  5  as
  6  begin
  7  select B into o_B from emp where emp.A = i_A;
  8  end;
  9  /过程已创建。SQL>
SQL> variable b varchar2(10);
SQL> exec p_test_dym('1',:b);PL/SQL 过程已成功完成。SQL> print b;B
----------------------------------------------------------------
1SQL>

Answer 12

baidu、google好多的咧，一句话讲不清啊。sorry啦。。