SQL编程(三)

在做这些例子时一些基本的语法就不写了，另外oracle10g有许多函数，这里也不举例了，用的时候可以去查看，大家可以看看‘精通oracle10g编程’这本书。

本章的一些例子

--ALL
SELECT ename FROM emp WHERE sal >ALL
(SELECT DISTINCT sal FROM salary);
--IN
SELECT city,street FROM address WHERE city IN('沈阳','厦门');
--多列子查询
SELECT ename FROM emp WHERE (sal,deptno) =(SELECT sal,deptno FROM emp WHERE ename = "fei");
--相关子查询
SELECT ename FROM emp WHERE EXISTS
                      (SELECT deptno FROM dept WHERE deptno ='20');
--DDL中子查询
CREATE TABLE /* VIEW */emps(id,name,age) AS
SELECT empno,ename FROM emp ;
--UNION 并集
SELECT ename FROM emp WHERE sal >10000
UNION --ALL 不去重
SELECT deptno FROM dept WHERE dname ='fei';
--INTERSECT 交集
SELECT ename FROM emp WHERE sal >10000
INTERSECT
SELECT deptno FROM dept WHERE dname ='fei'
--MINUS 差集 存在第一个条件而不存在第二个条件
SELECT ename FROM emp WHERE sal >10000
MINUS
SELECT deptno FROM dept WHERE dname ='fei'

--CASE
SELECT ename,CASE WHEN sal>3000 THEN 3 WHEN sal >2000 THEN 2 ELSE 1 END grade FROM emp WHERE  deptno ='s1';
--查看历史记录
SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp(
'2010-09-25 08:25:34','YYYY-MM-DD HH24:MI:SS')
WHERE deptno='10';
--获取特定SCN数据
exec dbms_flashback.disable;
--WITh 等同于创建一个视图
WITH summ AS (SELECT dname,sum(sal) AS dept_total FROM emp,dept WHERE emp.deptno = dept.dno GROUP BY dname)
SELECT dname ,dept_total FROM summ WHERE dept_total >(SELECT sum(dept_total)*1/3 FROM sum);
--SQL游标 隐含游标
--SQL%ISOPEN 该游标在执行ddl语句之前打开，执行之后关闭
--SQL%NOTFOUND 判定语句是否执行成功
DECLARE
v_deptno emp.deptno%TYPE:=&no;
BEGIN
   UPDATE emp SET sal = sal*2
   WHERE deptno = v_deptno;
   IF SQL%FOUND THEN
   dbms_output.put_line('语句执行成功');
   ELSE
   dbms_output.put_line(语句执行失败);
   END IF;
END
--SQL%FOUND 与SQL%NOTFOUND相反
--SQL%ROWCOUNT SQL语句所作用的总行数
DECLARE
  v_deptno emp.deptno%TYPE:=&no;
BEGIN
  UPDATE emp SET sal = sal *2
  WHERE deptno = v_deptno;
  dbms_output.put_line('修改了||SQL%ROWCOUNT行');
END;
   待续~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~