SQL> create table t_test_return 2 (id number(20), 3 name varchar2(100));
Table created
SQL> SQL> create sequence seq_test_return 2 minvalue 1 3 maxvalue 999999999999999999999999999 4 start with 1 5 increment by 1;
Sequence created
SQL> SQL> create or replace trigger tri_test_return 2 before insert on t_test_return 3 for each row 4 declare 5 -- local variables here 6 begin 7 select seq_test_return.nextval into :new.id from dual; 8 end tri_test_return; 9 /
Trigger created
SQL> insert into t_test_return(name)values('a');
1 row inserted
SQL> select * from t_test_return;
ID NAME --------------------- -------------------------------------------------------------------------------- 1 a
SQL> SQL> create or replace procedure proc_pass_col_name 2 3 as 4 v_id number(20); 5 begin 6 insert into t_test_return(name)values('b') returning id into v_id; 7 dbms_output.put_line(v_id); 8 end; 9 /
Procedure created
SQL> set serveroutput on SQL> exec proc_pass_col_name;
2
PL/SQL procedure successfully completed
SQL>
SQL> select * from t_test_return;
ID NAME --------------------- -------------------------------------------------------------------------------- 1 a 2 b
id用序列来自动增长,最后返回就返回你序列所取的nextval
这个系统的功能是 用户在一个页面提交题目,问题后,后台插入数据库中,并返回一个数字,有一个页面是给用户使用这个数字来查找自己的问题,我这里将主键ID作为这个数字返回给用户但发现在底层DAO中不怎么会写返回ID的方法= = 所以想到了用存储过程,要是有人能教下怎么写这个存储过程最好了
2 (id number(20),
3 name varchar2(100));
Table created
SQL>
SQL> create sequence seq_test_return
2 minvalue 1
3 maxvalue 999999999999999999999999999
4 start with 1
5 increment by 1;
Sequence created
SQL>
SQL> create or replace trigger tri_test_return
2 before insert on t_test_return
3 for each row
4 declare
5 -- local variables here
6 begin
7 select seq_test_return.nextval into :new.id from dual;
8 end tri_test_return;
9 /
Trigger created
SQL> insert into t_test_return(name)values('a');
1 row inserted
SQL> select * from t_test_return;
ID NAME
--------------------- --------------------------------------------------------------------------------
1 a
SQL>
SQL> create or replace procedure proc_pass_col_name
2
3 as
4 v_id number(20);
5 begin
6 insert into t_test_return(name)values('b') returning id into v_id;
7 dbms_output.put_line(v_id);
8 end;
9 /
Procedure created
SQL> set serveroutput on
SQL> exec proc_pass_col_name;
2
PL/SQL procedure successfully completed
SQL>
ID NAME
--------------------- --------------------------------------------------------------------------------
1 a
2 b
SQL>