Oracle 存儲過程

Question

沒學過Oracle，想學Oracle存儲過程（本人會SQL存儲過程，有基礎，只要發個Oracle例子學習就好了）
    請高手寫個insert的Oracle存儲過程（3個以上參數），并附上.net中調用存儲過程的Function。
要求是：先判斷存不存在（資料重復），存在返回一個信息（1？）并且End，如果不存在再-->insert，insert成功也返回一個信息（2？），不成功也返回一個信息（3？）。
    另外如果可以能不能返回一個我剛剛插入的ID，因為Oracle好像不自增，不知道可不可以實現，如果不可以就算了，謝謝各位達達，先放50分探探路。

Answer 1

--1.例子：sql>select select * from test;       A        B
---------- ----------
       1        1
       2        2
       3        3
       3       10sql>create or replace procedure p_test(in_var number,out_var out sys_refcursor)
  2  as
  3  begin
  4  open out_var for select * from test where aaa=in_var;
  5  end;
  6  /Procedure created.sql>var abc refcursor
sql>exec p_test(3,:var_test)PL/SQL procedure successfully completed.sql>print :var_test       A        B
---------- ----------
       3        3
       3       10sql>--2.使用序列实现自增字段，给个简单的参考例子： create table t(pk number primary key,...); 
create sequence t_seq; 
create trigger t_trigger before insert on t for each row 
begin 
  select t_seq.nextval into :new.pk from dual; 
end; 

Answer 2

写一个供初学者参考:
SQL> create or replace procedure Pro(id_in int, name_in varchar2, v_out out int) as
  2    flag int;
  3  begin
  4    select count(1) into flag from test where id = id_in;
  5    if flag = 0 then
  6      insert into test values (id_in, name_in);
  7      commit;
  8      v_out := 2;
  9    else
 10      v_out := 1;
 11    end if;
 12  exception
 13    when others then
 14      v_out := 3;
 15  end;
 16  /Procedure created
SQL> select * from test;                                     ID NAME
--------------------------------------- ----------
                                      1 zyf
                                      2 love youSQL> 
SQL> declare
  2  v_out int;
  3  begin
  4  pro(1,'zyf',v_out);            --如果ID已存在，则返回1
  5  dbms_output.put_line(v_out);
  6  end;
  7  /1PL/SQL procedure successfully completed
SQL> declare
  2  v_out int;
  3  begin
  4  pro(3,'zyf3',v_out);            --如果ID不存在且插入成功，则返回2
  5  dbms_output.put_line(v_out);
  6  end;
  7  /2PL/SQL procedure successfully completed
SQL> SELECT * FROM TEST;                                     ID NAME
--------------------------------------- ----------
                                      1 zyf
                                      2 love you
                                      3 zyf3SQL> declare
  2  v_out int;
  3    begin
  4      pro(4,'aaaaabbbbbc',v_out);    --ID不存在,但插入的aaaaabbbbbc长度超过name定义的长度,返回3
  5      dbms_output.put_line(v_out||'  插入失败');
  6  end;
  7  /3  插入失败PL/SQL procedure successfully completedSQL> 

Answer 3

可以实现自增，建个序列写个触发器就可以了。
给你写个实例
SQL> create table  ttt (id number,name varchar2(100));Table createdSQL> create sequence seq1;Sequence createdSQL> create or replace trigger tr_insert
  2  before insert on ttt
  3  for each row
  4  begin
  5  select seq1.nextval into :new.id from dual;
  6  end;
  7  /Trigger created
SQL> insert into ttt(name) values('zhang');1 row insertedSQL> insert into ttt(name) values('wagg');1 row insertedSQL> select * from ttt;        ID NAME
---------- --------------------------------------------------------------------------------
         1 zhang
         2 wagg

Answer 4

SQL> create table  ttt (id number,name varchar2(100)); Table created SQL> create sequence seq1; Sequence created SQL> create or replace trigger tr_insert 
  2  before insert on ttt 
  3  for each row 
  4  begin 
  5  select seq1.nextval into :new.id from dual; 
  6  end; 
  7  / Trigger created 
SQL> insert into ttt(name) values('zhang'); 1 row inserted SQL> insert into ttt(name) values('wagg'); 1 row inserted SQL> select * from ttt;         ID NAME 
---------- -------------------------------------------------------------------------------- 
        1 zhang 
        2 wagg 

Answer 5

再举个返回值的例子：
SQL> create or replace procedure pro_test(n out varchar2) as
  2  begin
  3  insert into ttt(name) values(2) returning name into n;
  4  end;
  5  /Procedure createdSQL> set serveroutput on;
SQL> 
SQL> declare
  2    n varchar2(100);
  3  begin
  4    pro_test(n);
  5    dbms_output.put_line(n);
  6  end;
  7  /2PL/SQL procedure successfully completed