SQL和PLSQL

最新推荐文章于 2024-04-25 10:39:43 发布

原创最新推荐文章于 2024-04-25 10:39:43 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

93 篇文章

订阅专栏

本文通过实际案例展示了如何使用SQL进行数据查询与处理，包括纠正语法错误、查询特定信息、利用回收站恢复数据以及编写PL/SQL块进行复杂的数据分析与更新。

过滤和排序.txt

SQL>--where

SQL>--查询工资大于2500的员工信息

SQL>select *

2 formemp

3 where sal>2500;

form emp

第 2 行出现错误:

ORA-00923:未找到要求的 FROM 关键字

SQL>ed

已写入 file afiedt.buf

1 select *

2 formemp

3* where sal>2500

SQL> /

form emp

第 2 行出现错误:

ORA-00923:未找到要求的 FROM 关键字

SQL> 2

2* form emp

SQL> c/form/from

2* from emp

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7566 JONES MANAGER 7839 02-4月 -81 2975 20

SQL>--查询KING的信息

SQL>select *

2 fromemp

3 where ename='king';

未选定行

SQL>ed

已写入 file afiedt.buf

1 select *

2 fromemp

3* where ename='KING'

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL>--日期:查询17-11月-81入职的员工

SQL>select *

2 fromemp

3 where hiredate='17-11月-81';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL>select *

2 fromemp

3 where hiredate='1987-11-17';

wherehiredate='1987-11-17'

第 3 行出现错误:

ORA-01861:文字与格式字符串不匹配

SQL> --查看当前日期格式

SQL>select sysdate from dual;

SYSDATE

--------------

15-8月 -11

SQL>--查询系统设置

SQL>select * from v$nls_parameters;

SQL>col value for a30

SQL> /

PARAMETER VALUE

----------------------------------------------------------------------------------------------

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

已选择19行。

SQL>--修改日期格式

SQL>alter session set nls_date_format='yyyy-mm-dd';

会话已更改。

SQL>select *

2 fromemp

3 where hiredate='1987-11-17';

SQL>where hiredate='1981-11-17';

SP2-0734:未知的命令开头 "where hire..." - 忽略了剩余的行。

SQL>select *

2 fromemp

3 where hiredate='1981-11-17';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ---------- ----- ---------- ----------

7839 KING PRESIDENT 1981-11-17 5000 10

SQL>alter session set nls_date_formate='DD-MON-RR';

altersession set nls_date_formate='DD-MON-RR'

第 1 行出现错误:

ORA-00922:选项缺失或无效

SQL> altersession set nls_date_format='DD-MON-RR';

会话已更改。

SQL>--日期：格式问题(隐式转换、显示转换)

SQL>host cls

SQL>--between..and

SQL>--查询工资位于1000~2500的员工

SQL>select * from emp where sal>=1000 and sal<=2500;

SQL>select * from emp where sal>=1600 and sal<=2500;

SQL>select * from emp where sal between 1600 and 2500;

SQL>select * from emp where sal between 2500 and 1600;

SQL>--注意：小值在前，大值在后

SQL>--in:在集合中

SQL>--查询部门号为10 20的员工

SQL>select * from emp where deptno=10 or deptno=20;

SQL>select * from emp where deptno in(10,20);

SQL>host cls

SQL>--模糊查询

SQL>--查询姓名以S打头的员工

SQL>select *

2 fromemp

3 where ename like 'S%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7788 SCOTT ANALYST 7566 13-7月 -87 3000 20

已选择2行。

SQL>--查询姓名为四个字符的员工信息

SQL>select *

2 fromemp

3 where ename like '____';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7839 KING PRESIDENT 17-11月-81 5000 10

7902 FORD ANALYST 7566 03-12月-81 3000 20

已选择3行。

SQL>insert into emp(empno,ename) values(1234,'Tom_123');

已创建 1 行。

SQL>--查询姓名中含义下划线的员工

SQL>select *

2 fromemp

3 where ename like '%_%';

SQL>select *

2 fromemp

3 where ename like '%\_%' escape '\';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

1234 Tom_123

已选择 1 行。

SQL>select *

2 fromemp

3 where ename like '%a_%' escape 'a';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

1234 Tom_123

已选择 1 行。

SQL>host cls

SQL>--排序 order by

SQL>--按照月薪排序

SQL>select *

2 fromemp

3 order by sal;

SQL>--a命令： append

SQL> adesc;

3* order by saldesc

SQL>select *

2 fromemp

3 order by sal;

SQL>a desc;

3* order by sal desc

SQL> /

SQL>--a:两个空格

SQL>host cls

SQL>--order by后面可以跟哪些：1. 列名 2. 别名 3.序号

SQL>--按照年薪排序

SQL>select ename,sal,sal*12 年薪

2 fromemp

3 order by 年薪;

ENAME SAL 年薪

--------------- ----------

SMITH 800 9600

JAMES 950 11400

SQL>select ename,sal,sal*12 年薪

2 fromemp

3 order by 3;

ENAME SAL 年薪

--------------- ----------

SMITH 800 9600

SQL>--查询员工的信息，要求按照奖金排序

SQL>select *

2 fromemp

3 order by comm;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

SQL>delete from emp where empno=1234;

已删除 1 行。

SQL>select *

2 fromemp

3 order by comm;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

SQL>a desc;

3* order by comm desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--将空值排到最后

SQL>select *

2 fromemp

3 order by comm desc

4 nulls last;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

SQL>select *

2 fromemp

3 order by deptno,sal;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7934 MILLER CLERK 7782 23-1月 -82 1300 10

SQL>a desc;

3* order by deptno,sal desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL>ed

已写入 file afiedt.buf

1 select *

2 fromemp

3* order by deptno desc,sal desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

SQL>--升序和降序只作用于最近的一列

SQL>--order by作用于多列：先按照第一列排序；然后按照第二列排序；以此类推;

SQL>spool off

SQL>--清屏

SQL>host cls

SQL>--linux清屏: host clear

SQL>--当前用户下的表

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------------- ----------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

SQL>--emp表的结构

SQL>desc emp;

名称是否为空? 类型

------------------------------------------------- ----------------------------

EMPNO NOT NULLNUMBER(4)

ENAME VARCHAR2(10)

SQL>--查询员工所有信息

SQL>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

SQL>--设置行宽

SQL>set linesize 120

SQL>--设置列宽

SQL>col sal for 9999

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--设置页大小

SQL>set pagesize 20

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>set feedback off

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>set feedback on

SQL>--通过列名

SQL>select empno,ename,job,mgr,hiredate,sal,comm,deptno

2 fromemp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--问题：*和列名哪种性能好?

SQL>--Oracle9i之前,列名>*

SQL>host cls

SQL>--具体的列

SQL>--查询员工的编号，姓名，月薪

SQL>select empno,ename,sal form emp;

select empno,ename,salform emp

第 1 行出现错误:

ORA-00923:未找到要求的 FROM 关键字

SQL>--c命名修改

SQL> c/form/from

1* select empno,ename,sal from emp

SQL> /

EMPNO ENAME SAL

-------------------- -----

7369 SMITH 800

SQL>--通过ed命令

SQL>select empno,ename,sal form emp;

selectempno,ename,sal form emp

第 1 行出现错误:

ORA-00923:未找到要求的 FROM 关键字

SQL>ed

已写入 file afiedt.buf

1* select empno,ename,sal from emp

SQL> /

EMPNO ENAME SAL

7369 SMITH 800

SQL>--别名: 1. as 2. as可以省略 3. 跟双引号

SQL>select empno as 员工编号, ename 姓名, sal "姓名"

2 fromemp;

员工编号姓名姓名

-------------------- ----------

7369 SMITH 800

SQL>--问题: 有无双引号的区别？

SQL>host cls

SQL>--select含义算术表达式

SQL>--查询员工，编号，姓名，月薪，年薪

SQL>select empno,ename,sal 月薪, sal*12 年薪

2 fromemp;

EMPNO ENAME 月薪年薪

7369 SMITH 800 9600

SQL>--查询员工信息,编号，姓名，月薪，年薪,奖金，年收入

SQL>select empno,ename,sal 月薪, sal*12 年薪, comm 奖金, sal*12+comm 年收入

2 fromemp;

EMPNO ENAME 月薪年薪奖金年收入

7369 SMITH 800 9600

SQL>/*

SQL>sql里面，关于空值的使用:

SQL>1. 如果一个表达式中含空值，则整个表达式为空

SQL>2. null != null

SQL>*/

SQL>--查询奖金为空的员工信息

SQL>select *

2 fromemp

3 where comm =null;

未选定行

SQL>--1. 使用滤空函数 null-->0

SQL>select empno,ename,sal 月薪, sal*12 年薪, comm 奖金, sal*12+nvl(comm,0) 年收入

2 fromemp;

EMPNO ENAME 月薪年薪奖金年收入

7369 SMITH 800 9600 9600

SQL>2. is或者is not

SP2-0734:未知的命令开头 "2. is或者i..." - 忽略了剩余的行。

SQL>-- 2. is或者is not

SQL> select*

2 fromemp

3 where comm is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--查询员工的部门号

SQL>select depnto from emp;

selectdepnto from emp *

第 1 行出现错误:

ORA-00904:"DEPNTO": 标识符无效

SQL>select deptno from emp;

DEPTNO

SQL>select distince deptno from emp;

selectdistince deptno from emp

第 1 行出现错误:

ORA-00904:"DISTINCE": 标识符无效

SQL> selectdistinct deptno from emp;

DEPTNO

30 SQL>--distinct如果作用于多列?

SQL>select distinct job,deptno from emp;

JOB DEPTNO

MANAGER 20

SQL>--只有多列全部相同，才会认为是相同

SQL>host cls

SQL>--连接符: ||

SQL>--显示 Hello World

SQL>select 'hello' || ' world' from dual;

'HELLO'||'W

helloworld

已选择 1 行。

SQL>--oracle一个伪表dual:1.满足语法 2. 方便做测试

SQL>select 3+2 from dual;

3+2

已选择 1 行。

SQL>--查询员工的信息：*** 的工资是***

SQL>select ename||'的工资是'||sal

2 fromemp;

ENAME||'的工资是'||SAL

----------------------------------------------------------

SMITH的工资是800

SQL>spool off

如何手动解锁帐号和设置密码:

1. 管理员登录

*. 密码认证：sqlplus sys/password as sysdba

*. 主机认证：sqlplus / as sysdba （前提：操作系统的用户即为数据库管理员）

2. 解锁:

alter user scott account unlock;

3. 改密码:

alter user scott identified by paas123;

SQL>--大小写函数

SQL>select lower('HELLO WORLD') 转小写, upper('hEhllo WorLD') 转大写 from dual;

转小写转大写

-----------------------

helloworld HEHLLO WORLD

已选择 1 行。

SQL>--首字母大写

SQL>select initcap('hello world') 首字母大写 fromdual

2 ;

首字母大写

HelloWorld

已选择 1 行。

SQL>--显示hello word

SQL>select concat('hello',' world') from dual;

CONCAT('HEL

helloworld

已选择 1 行。

SQL>--substr: 求子串

SQL>select substr('Hello World',3) from dual;

SUBSTR('H

lloWorld

已选择 1 行。

SQL>select substr('Hello World',3,5) from dual;

SUBST

lloW

已选择 1 行。

SQL>host cls

SQL>--length:字符数; lengthb:字节数

SQL>select length('hello world') 字符数, lengthb('hello world') 字节数 from dual;

字符数字节数

11 11

已选择 1 行。

SQL>select lenght('中国') 字符数, lengthb('中国') 字节数 from dual;

selectlenght('中国') 字符数, lengthb('中国') 字节数 from dual

第 1 行出现错误:

ORA-00904:"LENGHT": 标识符无效

SQL>select length('中国') 字符数, lengthb('中国') 字节数 from dual;

字符数字节数

2 4

已选择 1 行。

SQL>host cls

SQL>--instr: 求子串的位置

SQL>select instr('hello world','ll') from dual;

INSTR('HELLOWORLD','LL')

已选择 1 行。

SQL>select instr('hello world','abc') from dual;

INSTR('HELLOWORLD','ABC')

已选择 1 行。

SQL>--左右填充: abcd --> 10位, 并且用*填充

SQL>select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;

左填充右填充

******abcdabcd******

已选择 1 行。

SQL>--trim：去掉字符串前后的指定的字符

SQL>select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR

elloWorld

已选择 1 行。

SQL>--替换:

SQL>select replace('Hello World','l','*') from dual;

REPLACE('HE

He**oWor*d

已选择 1 行。

SQL>select ename from emp;

ENAME

SMITH

SQL>host cls

SQL>--数字函数

SQL>select round(45.926,2) 四舍五入, trunc(45.926,2) 截断, mod(1600,300) 求于 from dual;

四舍五入截断求于

45.93 45.92 100

已选择 1 行。

SQL>--日期函数

SQL>select sysdate from dual;

SYSDATE

15-8月 -11

已选择 1 行。

SQL>host cls

SQL>--昨天，今天，明天

SQL> selectsysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;

昨天今天明天

14-8月 -11 15-8月 -11 16-8月 -11

已选择 1 行。

SQL>--计算员工的工龄

SQL>select ename, sysdate-hiredate 天 from emp;

ENAME 天

SMITH 11198.6066

SQL>--日期能相加吗?

SQL>select ename, sysdate+hiredate from emp;

selectename, sysdate+hiredate from emp

第 1 行出现错误:

ORA-00975:不允许日期 + 日期

SQL>host cls

SQL>--计算员工的工龄，分别以天，月，年方式显示

SQL>select ename, sysdate-hiredate 天, (sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月

2 fromemp;

ENAME 天星期月

SMITH 11198.6088 1599.80126 373.28696

SQL>select ename,(sysdate-hiredate)/30 月, months_between(sysdate,hiredate) 方式二 from emp;

ENAME 月方式二

SMITH 373.287032 367.955193

SQL>--add_months: 在指定的时间上加上月数

SQL>--123月后

SQL>select add_months(sysdate,123) from dual;

ADD_MONTHS(SYS

15-11月-21

已选择 1 行。

SQL>--本月的最后一天

SQL>select last_day(sysdate) from dual;

LAST_DAY(SYSDA

31-8月 -11

已选择 1 行。

SQL>host cls

SQL>--next_day:从某个时间算起，下一个出现该星期几的日期

SQL>select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA

22-8月 -11

已选择 1 行。

SQL>host cls

SQL>--对日期进行四舍五入

SQL>select round(sysdate,'month'), round(sysdate,'year') from dual;

ROUND(SYSDATE,ROUND(SYSDATE,

01-8月 -11 01-1月 -12

已选择 1 行。

SQL>--对日期进行截断

SQL>select trunc(sysdate,'month'),trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,TRUNC(SYSDATE,

01-8月 -11 01-1月 -11

已选择 1 行。

SQL>select sysdate from dual;

SYSDATE

15-8月 -11

已选择 1 行。

SQL>--RR:表示年，有问题吗？

SQL>--千年虫问题

SQL>host cls

SQL>--显示当前系统时间

SQL>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

2011-08-1514:58:35

已选择 1 行。

SQL>--今天星期几

SQL>select to_char(sysdate,'day') from dual;

TO_CHAR(S

星期一

已选择 1 行。

SQL>--格式化显示日期: 今天是2011-08-15,现在时间14:58:35

SQL>select to_char(sysdate,'"今天是"yyyy-mm-dd "现在时间"hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'"今天是"YYYY-MM-

今天是2011-08-15 现在时间15:01:03

已选择 1 行。

SQL>host cls

SQL> select* from v$nls_parameters;

PARAMETER VALUE

NLS_LANGUAGE SIMPLIFIED CHINESE

SQL>host cs

SQL>--查询员工的工资，要求有千位符，货币代码，两位小数

SQL>select ename,to_char(sal,'L9,999.99') from emp;

ENAME TO_CHAR(SAL,'L9,999

SMITH ￥800.00

SQL>select to_number('123') from dual;

TO_NUMBER('123')

123

已选择 1 行。

SQL>host cls

SQL>select *

2 fromemp

3 where hiredate=to_date('1981-12-17','yyyy-mm-dd');

未选定行

SQL>select *

2 fromemp

3 where hiredate=to_date('1981-11-17','yyyy-mm-dd');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-11月-81 5000 10

已选择 1 行。

SQL>--通用函数

SQL>--滤空函数: nvl(a,b):当a为空时，返回b，反则返回a

SQL>-- nvl2(a,b,c):当a为空时，返回c；否则返回b;

SQL>select ename,sal,sal*12 年薪, comm 奖金, sal*12+nvl2(comm,comm,0) 年收入 from emp;

ENAME SAL 年薪奖金年收入

SMITH 800 9600 9600

SQL>host cls

SQL>--nullif(a,b):当a=b时，返回null；不等时，返回a

SQL>select nullif('abc','abc') from dual;

NUL

已选择 1 行。

SQL>select nullif('abc','abcd') from dual;

NUL

---

abc

已选择 1 行。

SQL>host cls

SQL>--COALESCE: 找到蚕食；列表中，第一个不为空的值

SQL>select ename,sal,comm,COALESCE(comm,sal) 第一个不为空 from emp;

ENAME SAL COMM 第一个不为空

SMITH 800 800

SQL>host cls

SQL>--背景:根据不同的工种涨工资

SQL>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--总裁涨1000，经理800，其他员工600

SQL>--使用case表达式

SQL>select ename,job,sal 涨前工资, case job when 'PRESIDENT' then sal+100

2 when'MANAGER' then sal+800

3 elsesal+400

4 end 涨后工资

5 fromemp;

ENAME JOB 涨前工资涨后工资

SMITH CLERK 800 1200

SQL>select ename,job,sal 涨前工资, decode(job,'PRESIDENT',sal+1000,

2 'MANAGER', sal+800,

3 sal+400) 涨后工资

4 fromemp;

ENAME JOB 涨前工资涨后工资

SMITH CLERK 800 1200

SQL>spool off

SQL>/*

SQL>Oracle中支持的连接类型：

SQL>1. 等值连接

SQL>2. 不等值连接

SQL>3. 外连接

SQL>4. 自连接

SQL>*/

SQL>--等值连接

SQL>--查询员工的信息,要求显示员工编号，姓名，月薪和部门名称

SQL>select e.empno,e.ename,e.sal,d.dname

2 fromemp e,dept d

3 where e.deptno=d.deptno;

EMPNO ENAME SAL DNAME

7369 SMITH 800 RESEARCH

SQL>--不等值连接

SQL>--查询员工信息，要求显示工资级别

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

DEPT TABLE

SQL>select * from salgrade;

GRADE LOSAL HISAL

1 700 1200

SQL>select e.ename,e.sal,s.grade

2 fromemp e,salgrade s

3 where e.sal between s.losal and s.hisal

4 ;

ENAME SAL GRADE

SMITH 800 1

SQL>--按部门查询员工人数

SQL>--要求，显示部门编号，部门名称，人数

SQL>select d.deptno,d.dname,count(e.empno)

2 fromemp e, dept d

3 where e.deptno=d.deptno;

selectd.deptno,d.dname,count(e.empno)

第 1 行出现错误:

ORA-00937:不是单组分组函数

SQL>select d.deptno,d.dname,count(e.empno)

2 fromemp e, dept d

3 where e.deptno=d.deptno

4 group by d.deptno,d.dname;

DEPTNO DNAME COUNT(E.EMPNO)

10 ACCOUNTING 3

SQL>--这个结果对吗？

SQL>select * from dept;

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

SQL>--外连接：当等值连接条件不成立时，任然希望某些记录出现的结果中

SQL>-- 左外连接，和右外连接

SQL>--右外连接：条件不成立时，等号右边的任然显示,写法:where e.deptno(+)=d.deptno

SQL>--左外连接：条件不成立时，，等号左边的任然显示，写法:where e.deptno =d.deptno(+)

SQL>select d.deptno,d.dname,count(e.empno)

2 fromemp e, dept d

3 where e.deptno(+)=d.deptno

4 group by d.deptno,d.dname;

DEPTNO DNAME COUNT(E.EMPNO)

10 ACCOUNTING 3

SQL>host cls

SQL>--查询员工信息，要求显示：***的老板是***

SQL>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--自连接：通过表的别名，将同一张表视为不同表，然后再利用等值、不等值、外连接进行操作

SQL>select e.ename||'的老板是'||b.ename

2 fromemp e,emp b

3 where e.mgr=b.empno;

E.ENAME||'的老板是'||B.ENAME

FORD的老板是JONES

SQL>--问题：自连接存在什么问题？

SQL>--性能问题

SQL>--自连接只适用于小表

SQL>--为了解决自连接的性能问题，Oracle提出了层次查询

SQL>--伪列：level

SQL>--层次查询：同一张表的前后两次查询，进行连接操作

SQL>select level,empno,ename,mgr

2 fromemp

3 connect by prior empno=mgr

4 start with empno=7839

5 order by level;

LEVEL EMPNO ENAME MGR

1 7839 KING

SQL>--打开sql执行的时间开关

SQL>

SQL>set timing on

SQL> select e.ename||'的老板是'||b.ename

2 from emp e,emp b

3 where e.mgr=b.empno;

E.ENAME||'的老板是'||B.ENAME

FORD的老板是JONES

已用时间: 00: 00: 00.01

SQL>select level,empno,ename,mgr

2 fromemp

3 connect by prior empno=mgr

4 start with empno=7839

5 order by level;

LEVEL EMPNO ENAME MGR

1 7839 KING

已用时间: 00: 00: 00.39

SQL>set timing off

SQL>spool off

SQL>--sum函数

SQL>--求员工工资的总和

SQL>select sum(sal) from emp;

SUM(SAL)

29025

已选择 1 行。

SQL>--count函数:求个数

SQL>--求员工的人数

SQL>select count(*) from emp;

COUNT(*)

已选择 1 行。

SQL>--求平均工资

SQL>select sum(sal)/count(*) from emp;

SUM(SAL)/COUNT(*)

2073.21429

已选择 1 行。

SQL>select avg(sal) from emp;

AVG(SAL)

2073.21429

已选择 1 行。

SQL>host cls

SQL>--求奖金的总额

SQL>select sum(comm) from emp;

SUM(COMM)

2200

已选择 1 行。

SQL>--求平均奖金

SQL>select sum(comm)/count(*) from emp;

SUM(COMM)/COUNT(*)

157.142857

已选择 1 行。

SQL>select avg(comm) from emp;

AVG(COMM)

550

已选择 1 行。

SQL>--组函数会自动滤空

SQL>select sum(comm)/count(comm) from emp;

SUM(COMM)/COUNT(COMM)

550

已选择 1 行。

SQL>select count(comm) from emp;

COUNT(COMM)

已选择 1 行。

SQL> select sum(comm)/count(nvl(comm,0)) from emp;

SUM(COMM)/COUNT(NVL(COMM,0))

157.142857

已选择 1 行。

SQL>select count(nvl(comm,0)) from emp;

COUNT(NVL(COMM,0))

已选择 1 行。

SQL>--屏蔽组函数的滤空功能：嵌套滤空函数

SQL>host cls

SQL>--求最高工资和最低公司

SQL>select max(sal) 最高工资, min(sal) 最低工资 from emp;

最高工资最低工资

5000 800

已选择 1 行。

SQL>host cls

SQL>--求各个部门的平均工资

SQL>select deptno,avg(sal)

2 fromemp

3 group by deptno;

DEPTNO AVG(SAL)

30 1566.66667

20 2175

10 2916.66667

已选择3行。

SQL>host cls

SQL>--按部门，不同的工种求平均工资

SQL>select deptno,job,avg(sal)

2 fromemp

3 group by deptno,job;

DEPTNO JOB AVG(SAL)

20 CLERK 950

SQL>select deptno,job,avg(sal)

2 fromemp

3 group by deptno,job

4 order by 1;

DEPTNO JOB AVG(SAL)

30 SALESMAN 1400

SQL>select deptno,job,avg(sal)

2 fromemp

3 group by deptno

4 order by 1;

selectdeptno,job,avg(sal)

第 1 行出现错误:

ORA-00979:不是 GROUP BY 表达式

SQL>host cls

SQL>--having:对分组后的数据，再进行过滤

SQL>select deptno,max(sal)

2 fromemp

3 group by deptno

4 having max(sal)>2000;

DEPTNO MAX(SAL)

10 5000

已选择3行。

SQL>select deptno,max(sal)

2 fromemp

3 group by deptno

4 having max(sal)>3000;

DEPTNO MAX(SAL)

10 5000

已选择 1 行。

SQL>host cls

SQL>--group by 的增强

SQL>/*

SQL>select deptno,job,sum(sal) from emp group by deptno,job

SQL> +

SQL>select deptno,sum(sal) from emp group by deptno

SQL> +

SQL>select sum(sal) from emp

SQL>

SQL>group by的增强：

SQL>group by rollup(a,b)

SQL> =

SQL>group by a,b

SQL> +

SQL>group by a

SQL> +

SQL>group by null

SQL>

SQL>select deptno,job,sum(sal)

SQL>from emp

SQL>group by rollup(depnto,job);

SQL>*/

SQL>select deptno,job,sum(sal)

2 fromemp

3 group by rollup(depnto,job);

group byrollup(depnto,job)

第 3 行出现错误:

ORA-00904:"DEPNTO": 标识符无效

SQL>host cls

SQL>select deptno,job,sum(sal)

2 fromemp

3 group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

10 CLERK 1300

SQL>break on deptno skip 2

SQL> /

DEPTNO JOB SUM(SAL)

10 CLERK 1300

MANAGER 2450

PRESIDENT 5000

8750

20 CLERK 1900

ANALYST 6000

MANAGER 2975

10875

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

DEPTNO JOB SUM(SAL)

------------------- ----------

29025

已选择13行。

SQL>break on null

SQL> /

DEPTNO JOB SUM(SAL)

10 CLERK 1300

已选择13行。

SQL>spool off

SQL>--查询10，20部门的员工信息

SQL>--第一种

SQL>select *

2 fromemp

3 where deptno=10 or deptno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--第二种

SQL>select*

2 ;

第 2 行出现错误:

ORA-00923:未找到要求的 FROM 关键字

SQL>select *

2 fromemp

3 where deptno in(10,20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

已选择8行。

SQL>host cls

SQL>--第三种，集合运算

SQL>select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

SQL>select * from emp where deptno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>select * from emp where depnto=10

2 union

3 select * from emp where deptno=20;

select *from emp where depnto=10

第 1 行出现错误:

ORA-00904:"DEPNTO": 标识符无效

SQL>ed

已写入 file afiedt.buf

1 select * from emp where deptno=10

2 union

3* select * from emp where deptno=20

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>host cls

SQL>--使用union完成group by rollup

SQL>select deptno,job,sum(sal) from emp group by deptno,job

2 union

3 select deptno,sum(sal) from emp group by deptno

4 union

5 select sum(sal) from emp;

selectdeptno,sum(sal) from emp group by deptno

第 3 行出现错误:

ORA-01789:查询块具有不正确的结果列数

SQL> /*

SQL> 注意事项：

SQL>1. 参与运算的各个集合必须列数相同，并且类型一致

SQL>2. 最终结果的表头采用第一个select的表头

SQL>*/

SQL>select deptno,job,sum(sal) from emp group by deptno,job

2 union

3 select deptno,to_char(null),sum(sal) from emp group by deptno

4 union

5 select to_number(null),to_char(null),sum(sal) from emp;

DEPTNO JOB SUM(SAL)

10 CLERK 1300

SQL>break on deptno skip 2

SQL> /

DEPTNO JOB SUM(SAL)

10 CLERK 1300

MANAGER 2450

PRESIDENT 5000

8750

20 ANALYST 6000

CLERK 1900

MANAGER 2975

10875

DEPTNO JOB SUM(SAL)

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

29025

已选择13行。

SQL>--rollup和集合运算，哪个好？

SQL>set timing on

SQL>select deptno,job,sum(sal)

2 fromemp

3 group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

10 CLERK 1300

MANAGER 2450

PRESIDENT 5000

8750

20 CLERK 1900

ANALYST 6000

MANAGER 2975

10875

DEPTNO JOB SUM(SAL)

------------------- ----------

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

29025

已选择13行。

已用时间: 00: 00: 00.18

SQL>select deptno,job,sum(sal) from emp group by deptno,job

2 union

3 select deptno,to_char(null),sum(sal) from emp group by deptno

4 union

5 select to_number(null),to_char(null),sum(sal) from emp;

DEPTNO JOB SUM(SAL)

10 CLERK 1300

20 ANALYST 6000

CLERK 1900

MANAGER 2975

10875

DEPTNO JOB SUM(SAL)

------------------- ----------

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

29025

已选择13行。

已用时间: 00: 00: 00.50

SQL>--集合运算只适用于小表

SQL>set timing off

SQL> breakon null

SQL>host cls

SQL>--交集:

SQL>--举例：查询员工信息，工资位于1000~2000和1500~2500的范围上

SQL>select * from emp where sal between 1000 and 2000

2 intersect

3 select * from emp where sal between 1500 and 2500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

SQL>--差集：

SQL>--举例：查询员工信息，工资位于1000~2000上，但不属于1500~2500的范围上

SQL>select * from emp where sal between 1000 and 2000

2 minus

3 select * from emp where sal between 1500 and 2500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

SQL>--第一题: rownum伪列,行号

SQL>select rownum,ename,sal from emp;

ROWNUM ENAME SAL

1 SMITH 800

SQL>--rownum的注意事项:

SQL>/*

SQL>1. 行号一旦生成，就不变

SQL>2. 行号只能使用小于号，不能使用大于号

SQL>*/

SQL>select rownum,ename,sal from emp order by sal desc;

ROWNUM ENAME SAL

9 KING 5000

SQL>select *

2 fromemp

3 where rownum<=3

4 order by sal desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

SQL>select *

2 fromemp

3 where rownum>=5 and rownum<=8;

未选定行

SQL>host cls

SQL>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>host cls

SQL>--第一题

SQL>select rownum,e.empno,e.ename,e.sal

2 from(select * from emp order by sal desc) e

3 where rownum<=3;

ROWNUM EMPNO ENAME SAL

1 7839 KING 5000

SQL>--将from后的子查询视为新表

SQL>select *

2 from (select rownum r,e1.*

3 from (select * from emp order by sal) e1

4 where rownum <=8

5 )

6 where r >=5;

R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

SQL>host cls

SQL>--第二题：

SQL>select empno,ename,sal,avgsal

2 fromemp e,(select deptno,avg(sal) avgsal from emp group by deptno) d

3 where e.sal>d.avgsal and e.deptno=d.deptno

4 ;

EMPNO ENAME SAL AVGSAL

7698 BLAKE 2850 1566.66667

SQL>spool off

SQL>select hiredate from emp;

HIREDATE

17-12月-80

SQL>select to_char(hiredate,'yyyy') from emp;

TO_C

1980

SQL>select count(*)"Total",sum(decode(to_char(hiredate,'yyyy'),'1980',1,0))"1980",

2 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0))"1981",

3 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",

4 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"

5 fromemp;

Total 1980 1981 1982 1987

14 1 10 1 2

SQL>spool off

SQL>--背景：查询工资比SCOTT高的员工信息

SQL>--1.求出SCOTT的工资

SQL>select sal

2 fromemp

3 where ename='SCOTT';

SAL

3000

SQL>--2.求出比3000高的员工

SQL>select *

2 fromemp

3 where sal>3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-11月-81 5000

SQL>set linesize 120

SQL>col sal for 9999

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-11月-81 5000 10

SQL>--子查询适用的场景：不能一次求解

SQL>--使用子查询求解

SQL>select *

2 fromemp

3 where sal > (select sal

4 from emp

5 where ename='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-11月-81 5000 10

SQL>/*

SQL> 子查询的注意事项:

SQL>1. 将子查询放入括号中

SQL>2. 尽量将子查询往右放

SQL>3. 在主查询的select, from, where后面均可使用子查询

SQL>4. 在主查询中的group by后面不能使用子查询

SQL>5. 主查询和子查询可以不是一张表，只要子查询返回的结果，主查询可以使用，即可

SQL>6. 在from使用子查询，最常使用的.

SQL>7. 一般，在子查询中不使用order by;但在top-n分析中，除外

SQL>*/

SQL>--3.在主查询的select, from, where后面均可使用子查询

SQL>select (select ename from emp where empno=7839),job

2 fromemp;

(SELECTENAJOB

KING CLERK

(SELECTENA JOB

KING CLERK

SQL>--查询部门号为10的员工信息 select * from emp where deptno=10;

SQL>--姓名和薪水

SQL>

SQL>select *

2 from(select ename,sal from emp where deptno=10);

ENAME SAL

CLARK 2450

SQL>select e.ename,e.sal

2 from(select ename,sal from emp where deptno=10) e

3 ;

ENAME SAL

CLARK 2450

SQL> --5.主查询和子查询可以不是一张表，只要子查询返回的结果，主查询可以使用，即可

SQL>--查询部门名称为ACCOUTING的员工

SQL>select *

2 fromemp

3 where deptno=( select deptno from dept where dname='ACCOUNTING');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

SQL>host cls

SQL>--在子查询中使用组函数

SQL>--查询工资最低的员工

SQL>select *

2 fromemp

3 where sal=(select min(sal) from emp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>select *

2 fromemp

3 where sal= (select min(sal) fromemp group by deptno);

wheresal= (select min(sal) from emp group by deptno) *

第 3 行出现错误:

ORA-01427:单行子查询返回多个行

SQL>host cls

SQL>--子查询中空值

SQL>--查询不是经理的员工

SQL>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL>select *

2 fromemp

3 where empno not in (select mgr from emp);

未选定行

SQL>--如果子查询中含义空值，则整个子查询为空

SQL>--对子查询实现滤空，通过where条件

SQL>select *

2 fromemp

3 where empno not in (select mgr from emp where mgr is not null);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7900 JAMES CLERK 7698 03-12月-81 950 30

已选择8行。

SQL>host cls

SQL>--多行子查询

SQL>--in: 查询部门名称为ACCOUNTING和SALES的员工信息

SQL>select * from dept;

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

SQL>select *

2 fromemp

3 where deptno in (select deptno from dept where dname='ACCOUNTING' ordname='SALES');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

SQL>--any:和子查询返回的任意一个值比较

SQL>--查询工资比10号部门任意一个低的员工信息

SQL>select *

2 fromemp

3 where sal < any (select sal from emp where deptno=10);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>select *

2 fromemp

3 where sal < (select min(sal) from emp where deptno=10);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>select *

2 fromemp

3 where sal < (select max(sal) from emp where deptno=10);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--查询工资比10号部门任意一个低的员工信息:即小于最大值

SQL>host cls

SQL>--all:和子查询返回的所有值比较

SQL>--查询工资比10号部门都低员工

SQL>select *

2 fromemp

3 where sal < all (select sal from emp where deptno=10);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>select *

2 fromemp

3 where sal < (select min(sal) from emp where deptno=10);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--查询工资比10号部门都低员工:即小于子查询的最小值

SQL>spool off

SQL>--Oracle中的事务

SQL>host cls

SQL>/*

SQL>Oracle中，事务的起始标志：

SQL> DML语句为起始标志

SQL> 事务自动开启

SQL> 结束标志: 提交: 1. 显式提交: commit

SQL> 2. 隐式提交: DDL自动提交前面的DML语句

SQL> 回滚：1. 显式回滚: rollback

SQL> 2. 隐式回滚: 系统掉电，宕机，非正常退出

SQL>*/

SQL>host cls

SQL>--存储点

SQL>select * from emp10;

SQL>select * from emp20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>rollbck;

SP2-0042:未知命令 "rollbck" - 其余行忽略。

SQL>rollback;

SQL>--插入数据

SQL>insert into emp20(empno,ename) values(1234,'Tom_123');

SQL>set feedback on

SQL>--定义一个存储点

SQL>select * from emp20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>savepoint a;

保存点已创建。

SQL>--更新操作

SQL>update emp20 set sal=2000;

已更新6行。

SQL>select * from emp20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 2000 20

已选择6行。

SQL>--回退到a

SQL>rollback to a;

回退已完成。

SQL>select * from emp20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12月-80 800 20

SQL>--提交

SQL>commit;

提交完成。

SQL>host cls

SQL>--JDBC中如何使用存储点?

SQL>/*

SQL>Connection conn = ......

SQL>conn.setAutoCommit(false);

SQL>Statement st = conn.createStatement();

SQL>st.excute(sql1);

SQL>st(sql2);

SQL>Savepoint sp = conn.setSavePoint();

SQL>sql3

SQL>sql4

SQL>conn.rollback(sp);

SQL>conn.commit;

SQL>*/

SQL>spool off

SQL>/*

SQL> 表的操作:

SQL>1. create table

SQL>2. alter table(添加新列，修改列，删除列)

SQL>3. drop table

SQL>*/

SQL>--4. 约束

SQL>show user;

USER 为 "SCOTT"

SQL>host cls

SQL>/*

SQL> 创建表：

SQL>1. create table权限

SQL>2. 在users表空间上，必须有相应的配额

SQL>*/

SQL>create table testtable

2 (tidnumber,

3 tname varchar2(20),

4 birthday date default sysdate);

表已创建。

SQL>insert into testtable(tid,tname) values(1,'aa);

ERROR:

ORA-01756:引号内的字符串没有正确结束

SQL>insert into testtable(tid,tname) values(1,'aa');

已创建 1 行。

SQL>select * from testtable;

TID TNAME BIRTHDAY

1 aa 16-8月 -11

已选择 1 行。

SQL>host cls

SQL>show user

USER 为 "SCOTT"

SQL>select * from hr.employees;

EMPLOYEE_IDFIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE

JOB_ID SALARY COMMISSION_PCT MANAGER_IDDEPARTMENT_ID

198 Donald OConnell DOCONNEL 650.507.9833 21-6月 -99

SH_CLERK 2600 124 50

已选择107行。

SQL>host cls

SQL>select * from hr.employees;

select *from hr.employees

第 1 行出现错误:

ORA-00942:表或视图不存在

SQL>host cls

SQL>--rowid:行地址, Oracle维护的,代表硬盘上的一个位置，在该位置上，存了该条记录

SQL>select rowid,empno,ename, sal from emp;

ROWID EMPNO ENAME SAL

AAANW0AAEAAAAAfAAA 7369 SMITH 800 SQL>host cls

SQL>--使用子查询创建表

SQL>--建立一个新表，包含员工的编号，姓名，月薪，年薪，年收入

SQL>create table empincome

2 as

3 select empno,ename,sal,sal*12 annlsal,sal*12+nvl(comm,0) totalIncome

4 fromemp;

表已创建。

SQL> select* from empincome;

EMPNO ENAME SAL ANNLSAL TOTALINCOME

7369 SMITH 800 9600 9600

SQL>--修改表

SQL>desc empincome;

名称是否为空? 类型

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

SAL NUMBER(7,2)

ANNLSAL NUMBER

TOTALINCOME NUMBER

SQL>--添加照片列

SQL>alter table empincome add column image blob;

altertable empincome add column image blob

第 1 行出现错误:

ORA-00904:: 标识符无效

SQL>alter table empincome addimage blob;

altertable empincome addimage blob

第 1 行出现错误:

ORA-01735:无效的 ALTER TABLE 选项

SQL>alter table empincome add image blob;

表已更改。

SQL>desc empincome;

名称是否为空? 类型

EMPNO NUMBER(4)

SQL>--修改列

SQL>alter table empincome modify enamevarchar(20);

表已更改。

SQL>desc empincome;

名称是否为空? 类型

EMPNO NUMBER(4)

SQL>--删除列

SQL>alter table empincome drop column image;

表已更改。

SQL>desc empincome;

名称是否为空? 类型

EMPNO NUMBER(4)

ENAME VARCHAR2(20)

SAL NUMBER(7,2)

ANNLSAL NUMBER

TOTALINCOME NUMBER

SQL>host cls

SQL>--删除表

SQL>--Oracle的回收站

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

DEPT TABLE

SQL>drop table EMPINCOME;

表已删除。

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

DEPT TABLE

已选择9行。

SQL>--查看回收站

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

EMPINCOME BIN$Tp9LGYxbTpeSeiaZCUl5uA==$0TABLE 2011-08-16:15:41:31

SQL>--闪回(flashback)

SQL>--彻底删除

SQL>drop table testdelete purge;

表已删除。

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

EMPINCOME BIN$Tp9LGYxbTpeSeiaZCUl5uA==$0TABLE 2011-08-16:15:41:31

SQL>--清空回收站

SQL>purge recyclebin;

回收站已清空。

SQL> show recyclebin;

SQL>host cls

SQL>create table myperson

2 (pid varchar2(18) constraint myperson_PK primary key,

3 pname varchar(10) constraint mypserson_name not null,

4 email varchar(20) constraintmyperson_eamil unique,

5 gender varchar(4) constraint myperson_gender check(gender in('男','女')),

6 deptno number(4) constraint myperson_fk refereneces dept(deptno));

deptno number(4) constraint myperson_fk referenecesdept(deptno))

第 6 行出现错误:

ORA-02253:此处不允许约束条件说明

SQL>create table myperson

2 (pid varchar2(18) constraint myperson_PK primary key,

3 pname varchar(10) constraint mypserson_name not null,

4 email varchar(20) constraintmyperson_eamil unique,

5 gender varchar(4) constraint myperson_gender check(gender in('男','女')),

6 deptno number(4) constraint myperson_fk references dept(deptno));

表已创建。

SQL>insert into myperson values('a1234','Tom','tom@126.com','男',10);

已创建 1 行。

SQL>insert into myperson values('a1234','Tom','tom@126.com','饭',10);

insertinto myperson values('a1234','Tom','tom@126.com','饭',10)

第 1 行出现错误:

ORA-02290:违反检查约束条件 (SCOTT.MYPERSON_GENDER)

SQL>spool off

PL/SQL

setserveroutput on

declare

--变量申明

begin

--程序体

dbms_output.put_line('Hello World');

end;

if语句：判断用户键盘输入的数字

setserveroutput on

--接收键盘输入

acceptnum prompt '请输入一个数字:';

declare

--通过&取输入的值

pnum number := &num;

begin

if pnum = 0 then dbms_output.put_line('您输入的是0');

end if;

if pnum = 1 then dbms_output.put_line('您输入的是1');

end if;

end;

被0除

catch(***Exceptionex){

...

}

setserveroutput on

declare

--定义数值变量，用于保存1/0结果

pnum number;

begin

--赋值

pnum := 1/0;

exception

when zero_divide then dbms_output.put_line('被0除');

end;

带参数的光标: 查询并打印10号部门的员工的姓名和薪水

selectename,sal from emp where deptno=?

setserveroutput on

declare

cursor cemp10(pdno number) is selectename,sal from emp where deptno=pdno;

pename emp.ename%type;

psal emp.sal%type;

begin

open cemp10(20);

loop

fetch cemp10 into pename,psal;

exit when cemp10%notfound;

dbms_output.put_line(pename||'的工资是'||psal);

end loop;

close cemp10;

end;

给员工涨工资，按照工种涨：总裁涨1000 经理800 其他人员400

可能的sql:

selectempjob,empno from emp;

updateemp set sal=sal+? where empno=?

setserveroutput on

declare

--光标保存员工的编号和工作

cursor cemp is select empno,empjob from emp;

--对应的变量

pempno emp.empno%type;

pempjob emp.empjob%type;

begin

open cemp;

LOOP

--取出一个员工

fetch cemp into pempno,pempjob;

--退出条件

exit when cemp%notfound;

--判断

if pempjob = 'PRESIDENT' then update empset sal=sal+1000 where empno=pempno;

elsif pempjob= 'MANAGER' then update empset sal=sal+800 where empno=pempno;

else update emp set sal=sal+400 whereempno=pempno;

end if;

END LOOP ;

close cemp;

--提交

commit;

dbms_output.put_line('完成');

end;

使用游标查询员工姓名和工资，并打印

可能的sql：

selectename,sal from emp

setserveroutput on

declare

--定义光标

cursor cemp is select ename,sal from emp;

--定义光标对应的变量

pename emp.ename%type;

psal emp.sal%type;

begin

--打开光标

open cemp;

--通过循环取出光标中的值

loop

--取值

fetch cemp into pename,psal;

--退出条件

exit when cemp%notfound;

--取到值

dbms_output.put_line(pename||'的薪水是'||psal);

end loop;

--关闭光标

close cemp;

end;

实例1：统计每年入职的员工个数。

selectcount(*) "Total", sum(decode(to_char(hiredate,'yyyy'),'1980',1,0))"1980"

from emp;

可能的sql:

selectto_char(hiredate,'yyyy') from emp;

setserveroutput on

declare

--定义光标保存员工的入职年份

cursor cemp is selectto_char(hiredate,'yyyy') from emp;

phiredate varchar2(4);

--计数器

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

begin

--打开光标

open cemp;

loop

--取值

fetch cemp into phiredate;

--退出条件

exit when cemp%notfound;

if phiredate = '1980' then count80 :=count80 +1;

elsif phiredate = '1981' thencount81:=count81+1;

elsif phiredate = '1982' then count82:=count82+1;

else count87 := count87 + 1;

end if;

end loop;

--关闭

close cemp;

--输出

dbms_output.put_line('总数' ||(count80+count81+count82+count87));

dbms_output.put_line('80年'|| count80);

dbms_output.put_line('81年'|| count81);

dbms_output.put_line('82年'|| count82);

dbms_output.put_line('87年'|| count87);

end;

实例2：

为员工长工资。从最低工资调起每人长10％，但工资总额不能超过6万元,

请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

可能用到的sql:

selectempno from emp order by sal;

涨工资:update emp set sal=sal*1.1 where empno=?

工资总额： select sum(sal) from emp;

setserveroutput on

declare

--查询员工，按照工资升序排列

cursor cemp is select empno from emp order bysal;

pempno emp.empno%type;--记录员工的编号

--变量保存工资的总额

psalTotal number;

--计数器保存涨工资的人数

empcount number :=0;

begin

--初始化工资的总额

select sum(sal) into psalTotal from emp;

--涨工资

open cemp;

loop

--第一个退出条件

exit when psalTotal > 60000;

--取出一个员工涨工资

fetch cemp into pempno;

--第二个退出条件

exit when cemp%notfound;

--涨工资

update emp set sal=sal*1.1 where empno=pempno;

--人数加一

empcount := empcount + 1;

--重新计算工资总额

select sum(sal) into psalTotal fromemp;

end loop;

close cemp;

--提交

commit;

--输出结果

dbms_output.put_line('工资总额:' || psaltotal);

dbms_output.put_line('涨工资的人数:' || empcount);

dbms_output.put_line('-----------------------');

end;

实例3:

用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)

统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)

可能的sql：

查询部门: select distinct deptno from dept;

取出该部门的员工: select sal from emp where deptno=?

计算该部门的工资总额: select sum(sal) from emp where deptno=?

setserveroutput on

declare

--查询部门

cursor cdept is select distinct deptno fromdept;

pdeptno dept.deptno%type;

--查询该部门中的员工

cursor cemp(pdno number) is select sal fromemp where deptno=pdno;

psal emp.sal%type;

--计数器

count1 number;-->6000

count2 number;--3000~6000

count3 number;--<3000

--工资总额

psalTotal number;

begin

--获取部门

open cdept;

loop

--取一个部门

fetch cdept into pdeptno;

--退出

exit when cdept%notfound;

--初始化计数器

count1 := 0;count2 := 0;count3 := 0;

--得到该部门的工资总额

select sum(sal) into psalTotal from empwhere deptno=pdeptno;

--得到该部门的员工的工资

open cemp(pdeptno);

loop

--取一个员工的工资

fetch cemp into psal;

exit when cemp%notfound;

--判断

if psal >= 6000 then count1 := count1+1;

elsif psal < 6000 and psal >=3000then count2:=count2+1;

else count3:=count3+1;

end if;

end loop;

close cemp;

--保存结果

insert into msg1values(pdeptno,count3,count2,count1,psalTotal);

commit;

end loop;

close cdept;

dbms_output.put_line('完成');

end;

打印1~10

setserveroutput on

declare

pnum number :=1;

begin

loop

--退出条件

exit when pnum > 10;

--打印

dbms_output.put_line(pnum);

pnum := pnum + 1;

end loop;

end;

引用型变量和记录型变量

1. 引用型变量: 查询7839员工姓名和薪水 select ename, sal from emp where empno=7839;

2. 记录型变量: 查询7839员工所有信息 select * from emp where empno=7839;

setserveroutput on

declare

--申明引用型变量

pename emp.ename%type; --记录姓名

psal emp.sal%type;--记录薪水

--记录型变量

emprec emp%rowtype;

begin

--执行查询

--select ename, sal into pename,psal from empwhere empno=7839;

select * into emprec from emp whereempno=7839;

--打印

--dbms_output.put_line(pename||'的薪水是'||psal);

dbms_output.put_line(emprec.ename||'的薪水是'||emprec.sal);

end;

自定义例外

setserveroutput on

declare

cursor cemp(pdno number) is select ename fromemp where deptno=pdno;

--变量

pename emp.ename%type;

--例外

no_emp_data exception;

begin

open cemp(70);

--取值

fetch cemp into pename;

if cemp%notfound then raise no_emp_data;

end if;

close cemp;

exception

when no_emp_data then dbms_output.put_line('没有70号部门的员工');

end;

示例1：限制非工作时间向数据库emp插入数据(before insert)

非工作时间: 星期四,星期六，星期日, x 9：00前 18：00后

x not between 9 and 18; x<9 or x>18

create orreplace trigger securityEmp

beforeinsert

on emp

declare

begin

if to_char(sysdate,'day') in ('星期四','星期六'，'星期日') or

to_number(to_char(sysdate,'hh')) notbetween 9 and 18 then

raise_application_error(-20001,'不能在非工作时间插入员工数据');

end if;

end;

场景二：涨工资（涨后工资不能比涨前的少）

示例2：确认数据（检查emp表中sal 的修改值不低于原值）

create orreplace trigger checksal

beforeupdate

on emp

for eachrow

declare

begin

if :old.sal > :new.sal then

raise_application_error(-20002,'涨工资后的值不能小于涨前的值,涨前为:'||:old.sal||' 涨后为:'||:new.sal);

end if;

end;

存储过程示例：为指定的职工在原工资的基础上长10%的工资，并打印涨工资前和涨工资后的工资

create orreplace procedure raiseSalary(eno in number)

--变量说明:记录该员工的工资

psal emp.sal%type;

begin

--得到该员工的工资

select sal into psal from emp whereempno=eno;

--给涨工资

update emp set sal=sal*1.1 where empno=eno;

--打印

dbms_output.put_line(eno||'涨前的工资是'||psal||' 涨后的工资是' || (psal*1.1));

end;

存储函数：查询某员工的年收入

create orreplace function queryEmpIncome(eno in number)

returnnumber

--变量说明

psal emp.sal%type;

pcomm emp.comm%type;

begin

--查询该员工的月薪和奖金

select sal,comm into psal,pcomm from empwhere empno=eno;

--返回年收入

return psal*12+nvl(pcomm,0);

end;

带out参数的存储过程：查询某员工的月薪，年薪和年收入

问题：返回10号部门员工的所有信息

create orreplace procedure queryempincomeinfo(eno in number,psal out number,

pannlsal out number,

ptotalIncome out number)

begin

--通过查询得到这些值

select sal,sal*12,sal*12+nvl(comm,0) intopsal,pannlsal,ptotalincome from emp where empno=eno;

end;

最简单的存储过程:打印hello world

create orreplace procedure printHello

--变量说明

begin

dbms_output.put_line('Hello World');

end;

用PLSQL语言编写一个程序。按系(系名）分段统计（成绩小于60分，60-85分, 85分以上）

“大学物理”课程各分数段的学生人数，及各系学生的平均成绩。

可能的sql:

得到系的信息： select dno,dname from dep;

得到该系选修了某门课的学生成绩：

selectgrade from sc where sno in (select sno from student where dno=?)

and cno = (select cno from course where cname=?)

得到该系选修了某门课的学生平均成绩

selectavg(grade) from sc where sno in (select sno from student where dno=?)

and cno = (select cno from course where cname=?)

setserveroutput on

declare

--得到系

cursor cdep is select dno,dname from dep;

pdno dep.dno%type;

pdname dep.dname%type;

--得到该系选修了某门课的学生成绩

cursor cgrade(pdepno number,pcoursenamevarchar2) is

select grade from sc where sno in(select sno from student where dno=pdepno)

and cno = (select cno from course wherecname=pcoursename);

pgrade sc.grade%type;

--三个计数器

count1 number;-- <60

count2 number;--60~85

count3 number;-->85

--每个系的平均成绩

pavggrade number;

--课程名称

pcname varchar2(10);

begin

--大学物理

pcname := '大学物理';

--打开系

open cdep;

loop

fetch cdep into pdno,pdname;

--退出条件

exit when cdep%notfound;

--计数器清零

count1:=0;count2:=0;count3:=0;

--得到该系大学物理的学生成绩

open cgrade(pdno,pcname);

loop

fetch cgrade into pgrade;

exit when cgrade%notfound;

--判断

if pgrade < 60 then count1:=count1+1;

elsif pgrade>=60 and pgrade<85then count2:=count2+1;

else count3:=count3+1;

end if;

end loop;

close cgrade;

--统计平均成绩

select avg(grade) into pavggrade from scwhere sno in (select sno from student where dno=pdno)

and cno = (select cno fromcourse where cname=pcname);

--保存结果

insert into msgvalues(pcname,pdname,count1,count2,count3,pavggrade);

commit;

end loop;

close cdep;

dbms_output.put_line('完成');

end;

SQL>/*

SQL>1. 错误删除数据，并且提交

SQL>2. 错误删除了一张表drop table

SQL>3. 如何获取表的历史记录

SQL>4. 错误地执行了一个事务，并且提交

SQL>*/

SQL>--切换到管理员

SQL>conn / as sysdba

已连接。

SQL>show parameters undo;

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>--将900秒改为1200秒

SQL>alter system set undo_retention=1200 scope=both;

系统已更改。

SQL>show parameters undo;

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 1200

undo_tablespace string UNDOTBS1

SQL>/*

SQL>scope的取值:1. memory 2. spfile 3. both

SQL>*/

SQL>--授予scott用闪回表的权限

SQL>grant flashback any table to scott;

授权成功。

SQL>conn scott/tiger

已连接。

SQL>host cls

SQL>--scn:系统改变号 system change number

SQL>--通过时间获取SCN

SQL>select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

3995473

SQL>--闪回表

SQL>create table flashback_table

2 (fidnumber,

3 fname varchar(20));

表已创建。

SQL>insert into flashback_table values(1,'Tom');

已创建 1 行。

SQL>commit;

提交完成。

SQL>insert into flashback_table values(2,'Mary');

已创建 1 行。

SQL>commit;

提交完成。

SQL>insert into flashback_table values(3,'Mike');

已创建 1 行。

SQL>commit;

提交完成。

SQL>host cls

SQL>select * from flashback_table;

FID FNAME

1 Tom

2 Mary

3 Mike

SQL>--记录当前的SCN

SQL>select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

3995555

SQL>delete from flashback_table where fid=2;

已删除 1 行。

SQL>commit;

提交完成。

SQL>select * from flashback_table;

FID FNAME

1 Tom

3 Mike

SQL>--执行闪回

SQL>flashback table flashback_table to scn 3995555;

flashbacktable flashback_table to scn 3995555

第 1 行出现错误:

ORA-08189:因为未启用行移动功能, 不能闪回表

SQL>select rowid, fid,fname from flashback_table;

ROWID FID FNAME

AAANY0AAEAAAAI0AAA 1 Tom

SQL>--开启表的行移动功能

SQL>alter table flashback_table enable row movement;

表已更改。

SQL>flashback table flashback_table to scn 3995555;

闪回完成。

SQL>select * from flashback_table;

FID FNAME

1 Tom

2 Mary

3 Mike

SQL>/*

SQL> 注意：

SQL>1. 需要flashback any table的权限

SQL>2. 开启行移动功能

SQL>3. 如何获取回退的scn/时间?

SQL>*/

SQL>host cls

SQL>--oracle回收站

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

MSG1 TABLE

SYS_TEMP_FBT TABLE

已选择9行。

SQL>drop table msg1;

表已删除。

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MSG1 BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE 2011-08-19:10:34:00

SQL>show user;

USER 为 "SCOTT"

SQL>conn / as sysdba

已连接。

SQL>--管理员没有回收站

SQL>show user;

USER 为 "SYS"

SQL>create table mytable

2 (midnumber, mname varchar(20));

表已创建。

SQL>drop table mytable;

表已删除。

SQL>show recyclebin;

SQL>--回收站只对普通用户有效

SQL>conn scott/tiger

已连接。

SQL>host cls

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MSG1 BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE 2011-08-19:10:34:00

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

SYS_TEMP_FBT TABLE

FLASHBACK_TABLE TABLE

BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE

DEPT TABLE

SQL>drop table TESTSEQ purge;

表已删除。

SQL>--purge参数：彻底删除

SQL> show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MSG1 BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE 2011-08-19:10:34:00

SQL>--清空回收站:purge recyclebin;

SQL>host cls

SQL> show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MSG1 BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE 2011-08-19:10:34:00

SQL>--执行闪回删除

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

SYS_TEMP_FBT TABLE

FLASHBACK_TABLE TABLE

BIN$VzIQRj4rSnC/kXNQ3p9rGg==$0TABLE

SQL>flashback table msg1 to before drop;

闪回完成。

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

MSG1 TABLE

SYS_TEMP_FBT TABLE

已选择8行。

SQL>select * from msg1;

DEPTNO EMP_NUM1 EMP_NUM2 EMP_NUM3 SUM_SAL

30 3 2 1 21538

SQL>drop table msg1;

表已删除。

SQL>host cls

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MSG1 BIN$L7lJstduQeS4nblsvF+8tw==$0TABLE 2011-08-19:10:39:09

SQL>--通过回收站中的名字闪回删除

SQL>flashback table BIN$L7lJstduQeS4nblsvF+8tw==$0 to before drop;

flashbacktable BIN$L7lJstduQeS4nblsvF+8tw==$0 to before drop

第 1 行出现错误:

ORA-00905:缺失关键字

SQL>flashback table "BIN$L7lJstduQeS4nblsvF+8tw==$0" to before drop;

闪回完成。

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

MSG1 TABLE

SYS_TEMP_FBT TABLE

SQL>host cls

SQL>--闪回重名的对象

SQL>create table test

2 (tidnumber,

3 tname varchar(20));

表已创建。

SQL>drop table test;

表已删除。

SQL> create table test

2 (tid number,

3 tname varchar(20));

表已创建。

SQL>drop table test;

表已删除。

SQL>host cls

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$1wr66/qVT1eU/+mppG1Row==$0 TABLE 2011-08-19:10:42:38

TEST BIN$pEMjzBhlQV+Qw5sG0JfSBA==$0TABLE 2011-08-19:10:42:24

SQL>--执行闪回删除

SQL>flashback table test to before drop;

闪回完成。

SQL>show recyclebin;

ORIGINALNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$pEMjzBhlQV+Qw5sG0JfSBA==$0TABLE 2011-08-19:10:42:24

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

SQL>flashback table test to before drop;

flashbacktable test to before drop

第 1 行出现错误:

ORA-38312:原始名称已被现有对象使用

SQL>flashback table test to before drop rename to test123;

闪回完成。

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

MSG1 TABLE

SQL>host cls

SQL>--闪回版本查询

SQL>create table versions_table

2 (vidnumber,

3 vnamevarchar(20));

表已创建。

SQL>insert into versions_table values(1,'Tom');

已创建 1 行。

SQL>commit;

提交完成。

SQL>insert into versions_table values(2,'Mary');

已创建 1 行。

SQL>commit;

提交完成。

SQL>insert into versions_table values(3,'Mike');

已创建 1 行。

SQL>commit;

提交完成。

SQL>update versions_table set vname='Mary123' where vid=2;

已更新 1 行。

SQL>commit;

提交完成。

SQL>select * from versions_table;

VID VNAME

1 Tom

2 Mary123

3 Mike

SQL>--问题：如何获取表的历史记录?

SQL>host cls

SQL>/*

SQL>versions 伪表：

SQL>1. versions_operation: DML操作

SQL>2. versions_starttime: DML的起始时间

SQL>3. versions_endtime:DML的结束时间

SQL>4. versions_xid: DML的事务编号

SQL>*/

SQL>select vid,vname,versions_operation,versions_starttime,versions_endtime

2 fromversions_tables

3 versions between timestamp minvalue and maxvalue

4 order by vid,versions_starttime;

fromversions_tables*

第 2 行出现错误:

ORA-00942:表或视图不存在

SQL>ed

已写入 file afiedt.buf

1 select vid,vname,versions_operation,versions_starttime,versions_endtime

2 fromversions_table

3 versions between timestamp minvalue and maxvalue

4* order by vid,versions_starttime

SQL> /

VID VNAME V

VERSIONS_STARTTIME

VERSIONS_ENDTIME

1 Tom I

19-8月 -11 10.47.58 上午

2 Mary I

19-8月 -11 10.48.13 上午

19-8月 -11 10.48.55 上午

SQL>set linesize 150

SQL>col VERSIONS_STARTTIME for a30

SQL>col VERSIONS_ENDTIME for a30

SQL> /

VID VNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME

1 Tom I 19-8月 -11 10.47.58 上午

SQL>/*

SQL> 通过闪回版本查询：

SQL>1. 获取所有历史记录

SQL>2.如何过去闪回表的时间或者SCN

SQL>*/

SQL>host cls

SQL>--闪回事务查询

SQL>desc flashback_transaction_query

名称是否为空? 类型

XID RAW(8)

SQL>conn / as sysdba

已连接。

SQL>grant select any transaction to scott;

授权成功。

SQL>conn scott/tiger

已连接。

SQL>host cls

SQL>--举例

SQL>create table transaction_table

2 (tidnumber,

3 tname varchar(20));

表已创建。

SQL>--第一个事务

SQL>insert into transaction_table values(1,'Tom');

已创建 1 行。

SQL>insert into transaction_table values(2,'Mary');

已创建 1 行。

SQL>insert into transaction_table values(3,'Mike');

已创建 1 行。

SQL>commit;

提交完成。

SQL>--第二个事务

SQL>select * from transaction_table;

TID TNAME

1 Tom

2 Mary

3 Mike

SQL>delete from transaction_table where tid=2;

已删除 1 行。

SQL>update transaction_table set tname='Mike123' where tid=3;

已更新 1 行。

SQL>commit;

提交完成。

SQL>--通过闪回版本查询获取事务id

SQL>selecttid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid

2 fromtransaction_table

3 versions between timestamp minvalue and maxvalue

4 order by versions_xid;

TID TNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID

2 Mary D 19-8月 -11 11.17.12 上午 03000C0055040000

SQL>--通过闪回事务查询获取undo_sql

SQL>select operation,row_id,undo_sql

2 fromflashback_transaction_query

3 where xid='03000C0055040000';

OPERATION ROW_ID

---------------------------------------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE AAANY6AAEAAAAJNAAC

update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mike'where ROWID = 'AAANY6AAEAAAAJNAAC';

DELETE AAANY6AAEAAAAJNAAB

insertinto"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('2','Mary');

BEGIN

SQL>update "SCOTT"."TRANSACTION_TABLE" set "TNAME" ='Mike' where ROWID = 'AAANY6AAEAAAAJNAAC';

已更新 1 行。

SQL>insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('2','Mary');

已创建 1 行。

SQL>commit;

提交完成。

SQL>select * from TRANSACTION_TABLE;

TID TNAME

1 Tom

2 Mary

3 Mike

SQL>spool off

SQL>--授权

SQL>conn / as sysdba

已连接。

SQL>grant create database link to scott;

授权成功。

SQL>conn scott/tiger

已连接。

SQL>--创建数据库链路

SQL>create database link l2 connect to scott identified by tiger using'remoteorcl';

数据库链接已创建。

SQL>--查询员工信息，要求显示员工的姓名，工资和部门号

SQL>--假设：dept在北京，emp在上海

SQL>select ename,sal,dname

2 fromdept, emp@l2

3 where dept.deptno=emp.deptno;

ENAME SALDNAME

SMITH 800RESEARCH

SQL>conn / as sysdba

已连接。

SQL> grantcreate synonym to scott;

授权成功。

SQL>conn scott/tiger

已连接。

SQL>--使用同义词简化远端表

SQL>create synonym remoteemp for emp@l2;

同义词已创建。

SQL>select ename,sal,dname

2 fromdept,remoteemp

3 where dept.deptno=remoteemp.deptno;

ENAME SAL DNAME

SMITH 800RESEARCH

SQL>host cls

SQL>--应用场景3：使用触发器实现数据的同步

SQL>create or replace trigger syncemp

2 after update

3 onemp

4 foreach row

5 declare

6 begin

8 update remoteemp set sal=:new.sal where empno=:new.empno;

10 end;

11 /

触发器已创建

SQL>select sal from emp where empno=7839;

SAL

10648

SQL>update emp set sal=4321 where empno=7839;

updateemp set sal=4321 where empno=7839*

第 1 行出现错误:

ORA-20002:涨工资后的值不能小于涨前的值,涨前为:10648 涨后为:4321

ORA-06512:在 "SCOTT.CHECKSAL", line4

ORA-04088:触发器 'SCOTT.CHECKSAL' 执行过程中出错

SQL>update emp set sal=sal+1 where empno=7839;

已更新 1 行。

SQL>commit;

提交完成。

SQL>spool off

SQL>host cls

SQL>conn / as sysdba

已连接。

SQL>--创建一个用户

SQL>create user myit1122 identified by password;

用户已创建。

SQL>--解锁

SQL>alter user myit1122 account unlock;

用户已更改。

SQL>--改密码

SQL>alter user myit1122 identified by abcd;

用户已更改。

SQL>host cls

SQL>--权限

SQL>conn hr/hr

已连接。

SQL>conn / as sysdba

已连接。

SQL>drop user jeff cascade;

用户已删除。

SQL>drop user emi cascade;

用户已删除。

SQL>host cls

SQL>--权限的级联

SQL>show user;

USER 为 "SYS"

SQL>--创建jeff emi

SQL>create user jeff identified by password;

用户已创建。

SQL>--授予登录

SQL>grant create session to jeff;

授权成功。

SQL>create user emi identified by password;

用户已创建。

SQL>grant create session to emi;

授权成功。

SQL>--授予jeff创建表的权限

SQL>grant create table to jeff with admin option;

授权成功。

SQL>--分配空间users

SQL>alter user jeff quota unlimited on users;

用户已更改。

SQL>--切换到jeff上

SQL>

SQL>conn jeff/password

已连接。

SQL>--授予emi创建表的权限

SQL>grant create table to emi;

授权成功。

SQL>conn / as sysdba

已连接。

SQL>alter user emi quota unlimited on users;

用户已更改。

SQL>conn emi/password

已连接。

SQL>show user

USER 为 "EMI"

SQL>create table bb(bb number);

表已创建。

SQL>--切换到管理员上，撤销create table

SQL>conn / as sysdba

已连接。

SQL>revoke create table from jeff;

撤销成功。

SQL>conn jeff/password

已连接。

SQL>create table ccc(cc number);

createtable ccc(cc number)

第 1 行出现错误:

ORA-01031:权限不足

SQL>conn emi/password

已连接。

SQL> create table ccc(cc number);

表已创建。

SQL>host cls

SQL>-- 演示grant option

SQL>conn scott/tiger

已连接。

SQL>--select on emp-->jeff

SQL>grant select on emp to jeff with grant option;

授权成功。

SQL>conn jeff/password

已连接。

SQL>select count(*) from scott.emp;

COUNT(*)

SQL> --jeff:select on scott.emp --> emi

SQL>grant select on scott.emp to emi;

授权成功。

SQL>conn emi/password

已连接。

SQL>select count(*) from scott.emp;

COUNT(*)

SQL>--回到scott上

SQL>conn scott/tiger

已连接。

SQL>revoke select on emp from jeff;

撤销成功。

SQL>conn jeff/password

已连接。

SQL>select count(*) from scott.emp;

selectcount(*) from scott.emp

第 1 行出现错误:

ORA-00942:表或视图不存在

SQL>conn emi/password

已连接。

SQL>select count(*) from scott.emp;

selectcount(*) from scott.emp

第 1 行出现错误:

ORA-00942:表或视图不存在

SQL>host cls

SQL>--角色

SQL>conn / as sysdba

已连接。

SQL>create user myuser123 identified by password;

createuser myuser123 identified by password

第 1 行出现错误:

ORA-01920:用户名 'MYUSER123' 与另外一个用户名或角色名发生冲突

SQL>create user myuser1234 identified by password;

用户已创建。

SQL>grant connect,resource to myuser1234;

授权成功。

SQL>host cls

SQL>--自定义角色

SQL>create role myrole1234;

createrole myrole1234

第 1 行出现错误:

ORA-01921:角色名 'MYROLE1234' 与另一个用户名或角色名发生冲突

SQL>create role myrole123;

createrole myrole123 *

第 1 行出现错误:

ORA-01921:角色名 'MYROLE123' 与另一个用户名或角色名发生冲突

SQL>create role myrole321;

角色已创建。

SQL>grant create sessiont,create table to myrole321;

grantcreate sessiont,create table to myrole321

第 1 行出现错误:

ORA-00990:权限缺失或无效

SQL>grant create session,create table to myrole321;

授权成功。

SQL>spool off