Oracle总结实例.docx

1、Oracle总结实例第一章 Oracle简介 1创建用户/分配权限/回收权限 1拷贝表及数据/只拷贝表结构并把原表数据加入到新表中 2只拷贝表结构并把原表数据加入到新表中 3加入新雇员/更新薪水为10000/雇佣日期为当天或2000-10-01日 4删除该雇员 5新表与原表 5交集 5并集 5差集 7第二章 Oracle数据类型 8字符函数 8时间函数 10转换函数 11分析函数 13第三章 数据库对象的管理 14创建同义词与用户，使该用户可以访问scott用户的emp表 14创建一个序列，起始值为100，步长100，最大值1000，使用该序列向emp表插入数据 15创建带错误的/只读的/带c

2、heck约束的视图 16创建emp表中ename列上的唯一索引 17PL/SQL 17用PL/SQL过程打印特定雇员的名字和工资 17依据工资范围显示不同结果 18在pl/sql块中使用绑定变量查询特定名字的雇员的工资 19自定义异常，当余额不够时抛出并处理 19第四章 游标 20写一个PL/SQL块，使用隐式游标的属性 20用显式游标吧所有雇员的名字和薪水及雇员总数显示出来 20改上题为使用for循环游标 21写动态ref游标，输入参数显示emp/dept的名字 21第五章 子程序和程序包 22写过程，使用in、out、in out参数 22将上题改为函数 22把过程/函数放到包中 23第六

3、章 触发器 24写一个行级触发器 24写一个替代触发器的例子 25造一个触发器递归 25第一章 Oracle简介创建用户/分配权限/回收权限Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as SYS SQL create user johnny identified by johnny; User created SQL grant dba to johnny; Grant succeeded SQL revoke dba from johnny; Revoke succeededSQL grant c

4、reate session to johnny; Grant succeeded SQL conn johnny/johnny;Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as johnnySQL conn sys/sysaccp as sysdba ;Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as SYS SQL revoke create session from johnny; Revoke succ

5、eededSQL grant all on scott.emp to johnny; Grant succeeded SQL revoke all on scott.emp from johnny; Revoke succeeded拷贝表及数据/只拷贝表结构并把原表数据加入到新表中SQL create table new_Emp tablespace users as select * from scott.emp; Table created SQL select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- -

6、- - - - - - 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00

7、30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12

8、-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selected只拷贝表结构并把原表数据加入到新表中SQL create table new_Emp tablespace users as select * from scott.emp where 1=2; Table created SQL select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - SQL insert into new_Emp sel

9、ect * from scott.emp; 14 rows inserted SQL select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 19

10、81-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7

11、876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selected加入新雇员/更新薪水为10000/雇佣日期为当天或2000-10-01日SQL insert into new_Emp values(7777,johnny,MANAGER,7839,sysdate,5000,200,20)

12、; 1 row inserted SQL update new_Emp set sal=10000 where EMPNO=7777; 1 row updated SQL update new_Emp set HIREDATE=to_date(2000-10-01,yyyy-mm-dd) where EMPNO=7777; 1 row updated SQL select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800

13、.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7

14、788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1

15、300.00 10 7777 johnny MANAGER 7839 2000-10-1 10000.00 200.00 20 15 rows selected删除该雇员SQL delete new_Emp where EMPNO=7777; 1 row deleted新表与原表交集SQL select * from scott.emp intersect select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800

16、20 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 7566 JONES MANAGER 7839 1981-4-2 2975 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-5-1 2850 30 7782 CLARK MANAGER 7839 1981-6-9 2450 10 7788 SCOTT ANALYST 7566 1987-4-1

17、9 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 7876 ADAMS CLERK 7788 1987-5-23 1100 20 7900 JAMES CLERK 7698 1981-12-3 950 30 7902 FORD ANALYST 7566 1981-12-3 3000 20 7934 MILLER CLERK 7782 1982-1-23 1300 10 14 rows selected并集SQL select * from scott.emp

18、 union select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 7566 JONES MANAGER 7839 1981-4-2 2975 20 7654 MARTIN SALESMAN 7698 1981-9-28

19、1250 1400 30 7698 BLAKE MANAGER 7839 1981-5-1 2850 30 7777 johnny MANAGER 7839 2010-3-5 17 5000 200 20 7782 CLARK MANAGER 7839 1981-6-9 2450 10 7788 SCOTT ANALYST 7566 1987-4-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 7876 ADAMS CLERK 7788 1987-5-2

20、3 1100 20 7900 JAMES CLERK 7698 1981-12-3 950 30 7902 FORD ANALYST 7566 1981-12-3 3000 20 7934 MILLER CLERK 7782 1982-1-23 1300 10 8888 yellow MANAGER 7839 2001-10-11 5000 200 20 16 rows selected所有allSQL select * from scott.emp union all select * from new_Emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM D

21、EPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 7566 JONES MANAGER 7839 1981-4-2 2975 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-5-1 2850 30 7782 CLARK MANA

22、GER 7839 1981-6-9 2450 10 7788 SCOTT ANALYST 7566 1987-4-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 7876 ADAMS CLERK 7788 1987-5-23 1100 20 7900 JAMES CLERK 7698 1981-12-3 950 30 7902 FORD ANALYST 7566 1981-12-3 3000 20 7934 MILLER CLERK 7782 1982-

23、1-23 1300 10 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 7566 JONES MANAGER 7839 1981-4-2 2975 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-5-1 2850 30 EMPNO ENAME JOB MGR HIR

24、EDATE SAL COMM DEPTNO- - - - - - - - 7782 CLARK MANAGER 7839 1981-6-9 2450 10 7788 SCOTT ANALYST 7566 1987-4-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 7876 ADAMS CLERK 7788 1987-5-23 1100 20 7900 JAMES CLERK 7698 1981-12-3 950 30 7902 FORD ANALYST

25、 7566 1981-12-3 3000 20 7934 MILLER CLERK 7782 1982-1-23 1300 10 7777 johnny MANAGER 7839 2010-3-5 17 5000 200 20 8888 yellow MANAGER 7839 2001-10-11 5000 200 20 30 rows selected差集SQL select * from new_Emp minus select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 777

26、7 johnny MANAGER 7839 2010-3-5 17 5000 200 20 8888 yellow MANAGER 7839 2001-10-11 5000 200 20CommitSQL commit; Commit completeSave point/rollbackSQL savepoint s2; Savepoint createdSQL rollback to savepoint s2; Rollback complete第二章 Oracle数据类型字符函数将字符串的第一个字母转换为大写select initcap(hello) from dual;INITCAP(

27、HELLO)-Hello 去除字符串左边的指定字符SQL select Ltrim(aaahellobbb,aaa) from dual; LTRIM(AAAHELLOBBB,AAA)-Hellobbb去除字符串右边的指定字符SQL select rtrim(aaahellobbb,b) from dual; RTRIM(AAAHELLOBBB,B)-Aaahello替换字符串中的字符为指定字符SQL select translate(aaahellobbb,b,c) from dual; TRANSLATE(AAAHELLOBBB,B,C-Aaahelloccc替换字符串中的字符串为指定字符

28、串SQL select replace(abchelloabc,abc,*) from dual; REPLACE(ABCHELLOABC,ABC,*-*hello*找出指定字符串在原字符串中的位置SQL select instr(abchelloabc,abc) from dual; INSTR(ABCHELLOABC,ABC)- 1从索引为2的位置开始找出指定字符串在原字符串中的位置SQL select instr(abchelloabc,abc,2) from dual; INSTR(ABCHELLOABC,ABC)- 9从索引为2的位置开始找出指定字符串在原字符串中的第2个出现位置SQL select instr(abchelloabchelloabc,abc,2,2) from dual; INSTR(ABCHE