这样试试:CREATE OR REPLACE FUNCTION gen_ordno(tabname in varchar2) RETURN NUMBER IS
num NUMBER(10);
sql_stmt VARCHAR2(1000);
begin
sql_stmt := 'SELECT COUNT(*) FROM '||tabname;
EXECUTE IMMEDIATE sql_stmt INTO num;
RETURN num;
end;
/
num NUMBER(10);
sql_stmt VARCHAR2(1000);
begin
sql_stmt := 'SELECT COUNT(*) FROM '||tabname;
EXECUTE IMMEDIATE sql_stmt INTO num;
RETURN num;
end;
/
create or replace function gen_ordno(tabname in varchar2) return
NUMBER Is
IDNUM NUMBER(10);
WHE VARCHAR2(100);
begin
delete from a;
WHE:='insert into a(a) SELECT COUNT(*) FROM '||tabname;
EXECUTE IMMEDIATE WHE into idnum;
select a into idnum from a ;
RETURN IDNUM;
end;
/可大虾,我又遇到新问题,其实我做这个是为了做个数据库触发器,在记录插入时生成记录编号,可在为明细生成编号时,我怎么在数据库中来引用INSERT INTO 进来的外键值呢,请赐教!!!
一。创建序列
CREATE SEQUENCE TEST INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
NOCACHE NOORDER二 USERADD 会员触发器CREATE OR REPLACE TRIGGER USERADD
BEFORE INSERT ON MEMBERRG
FOR EACH ROW
BEGIN
SELECT text.NEXTVAL INTO :NEW.NUM FROM DUAL;
END;
如果是这样,看看下面的代码:
SQL> create or replace trigger testnum before insert on test3 for each row
2 declare
3 i number;
4 begin
5 select count(*)+1 into i from test3;
6 :new.id :=i;
7 end;
8 /Trigger created.SQL> commit;Commit complete.SQL> select * from test3; ID DOCTIME P1 NEWZYZ ZYZ1
---------- --------- ---------- -------------------- ----------
25 01-JUL-01 2002-01-01
26 01-JUL-01 1999-01-01
41 01-JUL-01 2000-01-01
6 01-JUL-01 2002-01-01
22 01-JUL-01 2001-01-01SQL> desc test3;
Name Null? Type
----------------------------------------- -------- -------------------------- ID NOT NULL NUMBER
DOCTIME NOT NULL DATE
P1 NUMBER
NEWZYZ VARCHAR2(10)
ZYZ1 VARCHAR2(2000)SQL> insert into test3 values (1,sysdate,1,'asd','sdf');1 row created.SQL> commit;Commit complete.SQL> select * from test3; ID DOCTIME P1 NEWZYZ ZYZ1
---------- --------- ---------- -------------------- ----------
25 01-JUL-01 2002-01-01
26 01-JUL-01 1999-01-01
41 01-JUL-01 2000-01-01
6 24-SEP-02 1 asd sdf
6 01-JUL-01 2002-01-01
22 01-JUL-01 2001-01-016 rows selected.SQL> insert into test3 values (1,sysdate,2,'adfsd','dfsdf');1 row created.SQL> commit;Commit complete.SQL> select * from test3; ID DOCTIME P1 NEWZYZ ZYZ1
---------- --------- ---------- -------------------- ----------
25 01-JUL-01 2002-01-01
26 01-JUL-01 1999-01-01
41 01-JUL-01 2000-01-01
6 24-SEP-02 1 asd sdf
7 24-SEP-02 2 adfsd dfsdf
6 01-JUL-01 2002-01-01
22 01-JUL-01 2001-01-017 rows selected.
TYPE FLIGHTRCTYPE IS REF CURSOR ;--RETURN FLIGHTRESULT;
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2) RETURN FLIGHTRCTYPE;
END FLIGHTRESULT;
CREATE OR REPLACE PACKAGE BODY FLIGHTRESULT AS
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2)
--TNAME:IN OR OUT
RETURN FLIGHTRCTYPE IS
RC FLIGHTRCTYPE; --返回结果集游标
EXCSQLSTR1 VARCHAR2(3000):='';
BEGIN
EXCSQLSTR1:='SELECT * FROM TABLE WHERE COLNAME='||TNAME
OPEN RC FOR EXCSQLSTR1;
RETURN RC;
EXCEPTION --异常处理
WHEN NO_DATA_FOUND THEN
OPEN RC FOR SELECT -2 SEATS FROM DUAL;
RETURN RC; --数据没有找到
WHEN OTHERS THEN
OPEN RC FOR SELECT -3 SEATS FROM DUAL;
RETURN RC; --运行时发生意外错误
END SELECTCOMPARE;
END FLIGHTRESULT;
EXECUTE IMMEDIATE WHE INTO IDNUM;
RETURN IDNUM;
这样是可以,但是若是给从记录编号时,你的“整个表的编号”应该是个流水号,而非针对“每条主记录”来一个流水号!我是想在你的select count(*)+1 into i from test3||要加上明细表的外键(即主表限制)
而这个限制值是不固定的,它随着你INSERT的记录的外键的不同而不同
说白了就是主记录的不同,从记录的编号也随着变化,如若新增一条主记录,则从记录就从1开始。不知这样是否可以实现!!!