sql4

------------------------ ------------------- 
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