------------------------ -------------------
2003-03-17 19:15:59.688 2003-03-17 19:15:59
如果只想获得1/100秒(hsecs),还可以利用dbms_utility.get_time
[Q]如果存在就更新,不存在就插入可以用一个语句实现吗
[A]9i已经支持了,是Merge,但是只支持select子查询,
如果是单条数据记录,可以写作select …… from dual的子查询。
语法为:
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
VALUES (cu.course_name, cu.period,
cu.course_hours);
[Q]怎么实现左联,右联与外联
[A]在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
sql4
最新推荐文章于 2022-04-21 09:33:05 发布