我定义了一了function
create or replace function getvalue(p_name varchar2) return int
is
begin
return 1;
end; 然后用select调用
select getvalue('pname') from employee发现返回的记录行数会随from后的表的行数有关系,
如果employee表中有五行则返回五行1,其实我只是想
返回一个值.这是为什么呢?
create or replace function getvalue(p_name varchar2) return int
is
begin
return 1;
end; 然后用select调用
select getvalue('pname') from employee发现返回的记录行数会随from后的表的行数有关系,
如果employee表中有五行则返回五行1,其实我只是想
返回一个值.这是为什么呢?
---
一条记录:select getvalue('pname') from dual其实楼主上面的语句返回多条记录很正常,如果'pname'换成你表中的字段,那么每条记录通过函数计算后都会产生一个值,当然就会返回多条记录了。
--删除重复记录的行
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
--这样也可以删除啊
DELETE FROM employee E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM employee X WHERE X.EMP_NO = E.EMP_NO); 下面是例子:
SQL> truncate table test;Table truncatedSQL> select * from test;AA
--SQL> insert into test values('1');1 row insertedSQL>
SQL> insert into test values('2');1 row insertedSQL> insert into test values('3');1 row insertedSQL> insert into test values('1');1 row insertedSQL> insert into test values('4');1 row insertedSQL> insert into test values('5');1 row insertedSQL> insert into test values('3');1 row insertedSQL> insert into test values('3');1 row insertedSQL> commit;Commit completeSQL> select * from test;AA
--
1
2
3
1
4
5
3
38 rows selectedSQL>
SQL> delete from test where rowid!=
2 (select max(rowid) from test b where test.aa=b.aa);3 rows deletedSQL> select * from test;AA
--
2
1
4
5
3
--另外一种方法也可以的你试试
SQL> DELETE FROM test E
2 WHERE E.ROWID > (SELECT MIN(X.ROWID)
3 FROM test X
4 WHERE X.aa = E.aa);3 rows deletedSQL> select from test;select from testORA-00936: 缺少表达式SQL> select * from test;AA
--
1
2
3
4
5SQL> rollback;Rollback completeSQL> select * from test;AA
--
1
2
3
1
4
5
3
38 rows selected