使用oracle遇到的无语的地方
1.关于rownum
select rownum as rn,distinct Salary from Employee order by Salary desc
- 这样写报错ORA-00936: missing expression然后将rownum as rn, distinct Salary换了位置就成功了,无语了,有谁知道原因能在评论区告知一下吗?
select distinct Salary ,rownum as rn from Employee order by Salary desc
2.关于rownum的特点
select distinct Salary from Employee order by Salary desc where rownum=2
这样是找不出结果的
因为oracle中rownum关键字是从第一行开始找的,必须先找到一才能找到二,直接找第二行是找不出结果的
所以应该先将行号写出来
select distinct Salary ,rownum as rn from Employee order by Salary desc
3.关于nvl的使用
nvl(Salary,null)
nvl(count(Salary),null)
我们希望当找出的Salary无记录时,返回null。但是使用上面两个语句不会达到预期。
必须使用sum()函数
nvl(sum(Salary),null)
3.关于oracle function的用法
带in参数
create or replace function get_sal(
empname in varchar2
) return number
is
Result number;
begin
select sal into Result from emp where ename=empname;
return(Result);
end;
带out参数
create or replace function get_info(
e_name varchar2,
job out varchar2
) return number
Is
Result number;
begin
select sal,job into Result,job from emp where ename=e_name;
return(Result);
end;
我自己写的带out 的function
CREATE FUNCTION getNthHighestSalary(N IN NUMBER)
RETURN NUMBER IS result NUMBER;
BEGIN
/* Write your PL/SQL query statement below */
select nvl(sum(Salary),null) as getNthHighestSalary into result
from
(
select Salary from
(
select rownum as rn ,Salary from
(
select distinct Salary from Employee order by Salary desc
)
)
where rn =N
);
RETURN (result);
END;
- 照着填就可以了
- 注意分号 ;
- 注意select nvl(sum(Salary),null) as getNthHighestSalary into result加上 into result(放在句子最后)
其他就与mysql没有区别了
4.关于给表,字段起别名
https://www.cnblogs.com/CandiceW/p/10031104.html
select Score ,rn as Rank from
报错:ORA-00955: name is already used by an existing object
于是换了一种写法
select Score ,rn "Rank" from
然后成功了
唉,太无语了,不懂不懂,求解答
4.关于ORA-00918: column ambiguously defined错误
select Score ,rn "Rank" from
(
select Score ,rownum as rn from
(select Score from Scores group by Score order by Score desc)
) a
inner join Scores on a.Score=Scores.Score
order by rn
报错ORA-00918: column ambiguously defined
是因为在一开始的 select Score ,rn “Rank” from中没有score没有指定是哪个表的Score(后面内链接的两个表都有列score)
5.关于类似mysql中类似于add_date的功能
终于发现的Oracle的一个有点了
a.RecordDate= b.RecordDate+1
类似于mysql中的
a.RecordDate= date_add(b.RecordDate, interval 1 day)
在b的日期上加一天
7.关于Oracle与MySQL区别初步总结
- oracle的表重命名不能用as ,但是MySQL允许
- Oracle即使是在不同的子查询中也不允许有相同的别名
- 取余数mysql: id%2=1 oracle: mod(id,2)=1