在做这些例子时一些基本的语法就不写了,另外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;
待续~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~