Oracle操纵数据

1、1,表管理(1)创建表语句:CREATE TABLE table_name(field1 datatype, /field:指定列名 datatype：指定列类型field2 datatype,)(2)oracle常用数据类型char(size):定长，字母占一字节，汉字占两字节，最大2000个字符。如：create table test1(name char(32) /在name列，最多只能放32个字符，如果超过会报错，如果不够，则用空格补全。varchar2(size):变长，最大可存放4000个字符。nchar(size):定长，编码方式是unicode，汉字和字母都占一个字符，最大字符

2、2000nvarchar2(size):变长，uinicode编码，最大可存放4000个字符clob:字符型大对象，变长，最大8tbblob:变长，最大8tb，可存放声音，图片等对象说明：实际应用中很少把文件存放到数据库（效率问题），当需要考虑文件安全时才放入。number(p,s): 可存放整数和小数,变长，p表有效位，s表小数位.保存数据范围：-1E-130=number value=1e126,1=p=38,-84500 or job=MANAGER) and (ename like J%); order by(排序) 使用：排列方式有降序(desc)和升序(acs),默认是acs。如

3、select * from emp order by sal asc/desc; /没写排序方式，则默认升序排列。注：order by后面跟数字时，表示按第几个字段排序，所跟数不能超出列数。 使用列的别名排序：select ename,sal*12 年薪 from emp order by 年薪; 分页查询3.oracle表复杂查询(1)数据分组 常用函数max,min,avg,sum,count。 max取最大值，min取最小值，avg取平均值，sum取和，count统计数量。count(*)返回的是所有数据条数量，count(字段)返回有值的数据条数量。 group by.having使用

4、：按条件将数据分组如：select avg(sal) deptno from emp group by deptno,job order by deptno; /得到每个部门每个工作的平均工资并排序。select avg(sal) deptno from emp group by deptno having avg(sal)all(select sal from emp where deptno=30);any的使用：select * from emp where salany(select sal from emp where deptno=30); 多列子查询：子查询中返回多列。如：sel

5、ect * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH); from子句中使用子查询：将查询结果当做一个临时表对待。如 select t1.* from emp t1,(select avg(sal) myavg,deptno from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.salt2.myavg; /显示高于自己部门平均工资的员工信息。(3) 分页查询 各数据库分页查询语句：如演示取出数据5到12数据。mysql:

6、select * from users limit 5,8;sql server: select top 8 * from users where id not in (select top 4 id from users);oracle: select t2.* from (select t1.*,rownum rm from (select * from emp) t1 where rownum=5; /t1是第一层过滤语句得到数据的结果集，t2是第二层过滤得到前12条数据，第三层过滤后得到5到12的8条数据。 测试分页查询效率快速建表(从emp表中复制数据)：create table m

7、ytest as empno,ename from emp;自我复制(快捷形成海量数据表)：insert into mytest (empno,ename) select empno,ename from mytest;(4) 合并查询union:用于取得两个结果集的并集，会自动去掉结果集中重复行，并自动排序。union all:与union相似，但不会取消重复行，且不会排序。intersect:取两结果的交集。minus: 取两结果的差集，即并集与交集的差。(5) 使用子查询完成迁移的需求如 create table temp# as select empno,ename from emp

8、where ename like S%;(6) 使用子查询完成更新如 update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=SCOTT; /把scott的工作，工资，补助改成更smith一样4.oracle表的内连接和外连接(1)内连接：就是利用where子句对两张表形成的笛卡尔集筛选,只显示匹配的数据。如 select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;等价于 select

9、 emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;(2)外连接左外连接：左侧表完全显示，右侧表显示匹配数据。如 select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;/此时会把没人的40部门也显示出来。其他写法：select d.deptno,d.dname,e.ename from dept d deft ,emp e where d.deptno=e.deptno(+); 右外连接：右侧表

10、完全显示，左侧显示匹配数据。如select d.deptno,d.dname,e.ename from emp e right join dept d on d.deptno=e.deptno; select d.deptno,d.dname,e.ename from emp e,dept d where d.deptno(+)=e.deptno;完全外连接：完全显示两个表，没有匹配的记录置为空。如 select d.deptno,d.dname,e.ename from dept d outer join emp e on d.deptno=e.deptno;5.维护数据的完整性定义:数据完

11、整性可以使用约束,触发器,应用程序(过程,函数)三种方法来实现,约束是维护数据完整性的首选(1) 约束:用于确保数据库数据满足特定的商业规则a) not null（非空）i. 用于指定某列值不能为空b) unique（唯一）i. 指定列值不能重复，但是可以为nullc) primary key（主键）i. 用于唯一的标示表行的数据，指定列不但不能重复，而且不能为nullii. 一张表可以有多个unique,但最多只能有一个主键d) foreign key（外键）i. 用于定义主表和从表之间的关系ii. 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束iii. 要求外键列数据必

12、须在主表的主键列存在或是为nulle) check（约束）i. 用于强制数据必须满足的条件(2) 用法举例create table goods(goodsId number primary key, -设置主键goodsName varchar2(36) not null, -商品名字不能为空unitprice number check (unitprice0), -单价要大于0category varchar2(64) check (category in (食物,日用品),)create table customer(customerId number primary key,cusNam

13、e varchar2(32) not null,email varchar2(64) unique), -电邮不能重复sex char(2) default 男 check (sex in(男,女) , -性别只能选男或女，默认是男)create table purchase(customerId number references customer(customerId), goodsId number references goods(goodsId),nums number check (nums0)(3) 表约束修改a) 修改语句：alter table 表名 modify 字段 约束

14、;b) 添加语句：alter table 表名 add constraint xxx 约束(字段)(4) 删除约束a) 语句：alter table 表名 drop constraint xxx（字段）;b) 当两张表存在主从关系时，那么删除主表的主键约束时，必须带上cascade，如 alter table 表名 drop primary key cascade;(5) 列级定义和表级定义a) 列级定义：列级定义是在定义列的同时定义约束b) 表级定义时指在定义了所有列后，再定义约束，需要注意的是：not null约束只能在列级定义c) 一般情况下使用列级定义即可，但当含复合主键时要用表级定义

15、(6) 序列(sequence):用于处理自动增长列a) 可以为表中的列自动产生值，一般用于主键或唯一列b) 由用户创建数据库对象，并可由多个用户共享c) 案例说明：创建一个序列i. create sequence myseq -创建序列名ii. start with 1 -从1开始iii. increment by 1 -每次增长1iv. maxvalue 9999/NOMAXVALUE -设置最大值9999/不设最大值v. minvalue 1 -最小值vi. cycle /NOCYCLE -循环/一直累加，不循环vii. nocache; -缓存viii. 创建后在表中使用：create

16、 table test1 (id number primary key,name varchar2(32);insert into test1 values(myseq.nextval,tang)可用myseq.currval返回当前的序列值，必须在调用了次nextval后才可用用myseq.nextval增加序列值，然后返回6.索引(1) 单列索引:基于单个别所建立的索引语法:create index index_name on table(columnname);(2) 复合索引:基于两列或是多列的索引。在同一张表上可以有多个索引，但要求列的组合必须不同。语法：create index i

17、ndex_name on table(col_name,col_name);注：在大表上建立索引才有意义在where子句或是连接条件上经常引用的列上建立索引索引的层次不要超过4层 在逻辑类型字段上，或者值就固定几种的列上也不索引(3)索引的缺点：a) 建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引b) 更新数据的时候，系统必须要又额外的时间来同时对索引更新，一维持数据和索引的一致性。7.oracle 的pl/sql编程(1)概念pl/sql是在标准sql语句基础上扩展的一种对oracle数据库进行编程的语句。可以定义常量和变量，而且可以使用条件语句和循环语句。(2)pl/sq

18、l编程优点a)提高应用程序的运行性能b)模块化的设计思想分页的过程，订单的过程，转账的过程.c)减少网络传输量d)提高安全性(3) 缺点是移植性不好(4)案例解析:创建一简单的存储过程，可以完成向某表添加一条记录create procedure pro1(in_empno number,in_ename varchar(16) is begin insert into emp(empno,ename) values(in_empno,in_ename); end;/ 调用存储过程exec pro1(3333,juan);(5) pl/sql作用pl/sql可以开发 过程、函数、包（包体）、触发

19、器。他们的基础编程单元是块(7) 编写规范a) 注释：单行注释-; 多行注释/*.*/b) 标识符号的命名规范i. 定义变量时，建议用v_作为前缀 如 v_salii. 定义常量时，建议用c_作为前缀 如 c_rateiii. 当定义游标时，建议用_cursor作为后缀 如 emp_cursoriv. 当定义例外时，建议用e_作为前缀 如 e_error(8) pl/sql块结构a) pl/sql块由三部分构成：i. declare /定义部分-定义常量、变量、游标、例外、复杂数据类型ii. begin /执行部分-要执行的pl/sql语句iii. exception /例外不理部分-处理运行

20、的各种错误iv. end; /declare和exception部分是可选的，begin是必须的b) 案例解析i. declare1. v_ename varchar2(16); -格式：变量名 变量的类型ii. begin 1. select ename into v_ename form emp where empno=&empno;-把ename值放入变量，&表示要接收从控制台输入的变量2. dbms_output.put_line(雇员名是|v_ename); -输出v_ename3. exception -异常处理语法： when 异常名称 then4. when no_data_f

21、ound then dbms_output.put_line(未找到该编号的雇员); 5. end;/注：在默认情况下，结果不输出，需要设置set serveroutput onno_data_found异常要在含select .into的语句下才可捕获块执行完就消失，所以应融入过程中案例解析2：create procedure pro2 (in_empno number) isv_ename varchar2(8);beginselect ename into v_ename from emp where empno=in_empno;dbms_output.put_line(雇员名是|v_

22、ename); -|表示把两个字符串拼接end;(9) 过程详解a) oracle过程，可以指定参数是输入的参数，还有输出的参数b) 基本语法：create procedure 过程名(变量名in 变量类型. 变量名 out 变量类型) is.c) 调用过程方法： exec 过程名(参数值) call 过程名(参数值)d) 案例解析：i. create or replace procedure pro3(in_ename in varchar2,in_new_sal in number) is -加or replace时，当方案中已存在pro3时会字段替换ii. beginiii. updat

23、e emp set sal=in_new_sal where ename=in_ename;iv. end;/e) 当出现错误时，可以输入show error来显示具体错误8.pl/sql基础知识(1) 函数：用于返回特定的数据，在头部必须包含return子句，在函数体内必须包含return语句返回的数据。(2) 案例分析：a)创建-create function 函数名(参数1.) return 数据类型 iscreate function fun1(in_v_ename varchar2)return number is-定义变量;v_sal number;begin-执行语句;selec

24、t (sal+nvl(comm,0)*13 into v_sal from emp where ename=in_v_ename;return v_sal;end;/b)调用 ：select 函数名(实际参数) from dual;(2)包：用于逻辑上组合过程和函数，便于管理，由包规范和包体两部分组成a)包创建：create or replace packege 包名 isprocedure 过程名(变量名 变量类型.);function 函数名(变量名 变量类型.) return 数据类型;end;b)实例创建-在包中声明过程和函数create or replace packege pk1

25、isprocedure pro(in_empno number, in_ename varchar2);function fun1(in_ename varchar2) return number;end;d) 包体：实现已声明的包，实例化包中的过程和函数create or replace package body pk1 isprocedure pro(in_empno number,in_ename varchar2) is-过程实现function fun1(in_v_ename varchar2) return number isv_sal number;beginselect (sa

26、l+nvl(comm,0)*13 into v_sal from emp where ename=in_v_ename;return v_sal;end;end;e) 包的过程或函数调用调用包内过程和函数时，在过程和函数前需要带包名，如果要访问其他方案的包，还需在包名前加方案名exec/call 方案名.包名.过程名(参数值.);(3) 定义并使用变量a)标量类型(scalar)定义一个变长字符串:v_ename varchar2(10);定义一个小数范围 -9999.999999.99并赋值: v_sal number(6,2):=5.4;定义一个日期类型数据：v_hiredate date

27、;定义一个布尔变量，不能为空，初始为false：v_valid boolean:=false;%type的使用可以使定义的变量的类型和长度按照数据库列来确定v_ename emp.ename%type; v_sal emp.sal%type;b)复合变量(composite):用于存放多个值得变量pl/sql记录基本语法：type 自定义的记录名称 is record(变量 变量类型，变量 变量类型);记录案例解析：create or replace procedure pro3(v_empno in number) istype hsp_emp_record is record(v_enam

28、e emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);v_emp_record hsp_emp_record; -定义一上面记录类型的变量beginselect ename,sal,job into v_emp_record from emp where empno=v_empno;dbms_output.put_line(名字:|v_emp_record.v_ename | 工资:|v_emp_record.v_sal | 工作:|v_emp_record.v_job);end;pl/sql表相当于高级语言中的数组，但pl/sql表下

29、标可以为负数，并且表元素下标没有限制。c)参照变量参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型 游标变量(ref cursor)通过游标可以取得返回结果集的任何一行数据，从而提高共享的效率i 定义游标:type 自定义游标名 is ref cursor;定义已定义游标类型的变量:变量名 自定义游标名;ii 打开游标:open 游标变量 for select 语句;iii 取出当前游标指向的行:fetch 游标变量

30、 into 其它变量;iv 判断游标是否指向记录最后:游标变量%notfoundv 游标案例：create or replace procedure pro4(v_deptno number) istype hsp_emp_cursor is ref cursor;v_emp_cursor hsp_emp_cursor; -定义游标变量v_ename emp.ename%type;v_sal emp.sal%type;beginopen v_emp_cursor for select ename,sal from emp where deptno=v_deptno;loop -循环语句取出数据

31、fetch v_emp_cursor into v_ename,v-sal;exit when v_emp_cursor%notfound: -当游标到末尾时退出dbms_output.put_line(用户名:|v_ename| 薪水:|v_sal );end loop;close v_emp_cursor;-关闭游标end;9.pl/sql 的进阶(1) 控制结构a)条件分支语句：ifthen; ifthenelse; if-then-elsif-elsif-else;简单条件判断if-then案例解析：create or replace procedure pro5(in_ename v

32、archar2) isv_sal emp.sal%type;beginselect sal into v_sal from emp where ename=in_ename;if v_sal2000 thenupdate emp set sal=sal*1.1 where ename=in_ename;end if;end; 二重条件分支 if-then-elsea) 案例解析：b) create or replace procedure pro6(in_ename varchar2) isc) v_comm m%type;d) begine) select comm into v_comm

33、from emp where ename=in_ename;f) if v_comm0 theng) update emp set comm=comm+100 where ename=in_ename;h) elsei) update emp set comm=200 where ename=in_ename;j) end if;k) end; 多重条件分支：if-then-elsif-then-elsea) create or replace procedure pro7(in_empno number) isb) v_job emp.job%type;c) begind) select j

34、ob into v_job from emp where empno=in_empno;e) if v_job=PRESIDENT thenf) update emp set sal=sal+1000 where empno=in_empno;g) elsif v_job=MANAGER then -oracle中的比较是用=h) update emp set sal=sal+500 where empno=in_empno;i) elsej) update emp set sal=sal+200 where empno=in_empno;k) end if;l) end;b)循环语句 loo

35、p:pl/sql中最简单的循环语句，以loop开头，以end loop结尾，这种循环最少会被执行一次，通过exit when 条件 语句退出当前循环。案例解析：参考pro4;while循环只有条件为true时，才会执行循环语句，以while.loop开始,以end loop结束案例解析：循环添加10个用户都users表中，编号从200开始增加create table users(uno number,uname varchar2(32);create or replace procedure pro8(in_uname varchar2,in_n number) isv_uno number:

36、=200;beginwhile v_empno=200+in_n loopinsert into users values(v_uno,in_uname);end loop;end; for循环a) 基本结构：b) begini. for i in reverse 1.10 loopii. insert into users valuse(i,juan);iii. end loop;c) end;d) i是在隐含中增加，一般推荐用loop循环结构，不推荐使用for循环 goto语句a) 用于跳转到特定标号去执行语句，goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以一般可不就就不用

37、b) 案例解析：i number:=1; beginloopdbms_output.put_line(输出i=|i);if i=12 thengoto end_loop; -goto用于跳到标号end if;i:=i+1;if i=10 thengoto start_loop;end if;end loop;end; -输出结果是112(2)编写分页过程a) 无返回值的存储过程参考前面;b) 有返回值的存储过程(非列表) 案例解析：编写一个过程，可以输入雇员的编号，返回雇员的姓名create or replace procedure pro9(in_empno in number,out_en

38、ame out varchar2) isbeginselect ename into out_ename from emp where empno=in_empno;end;在java程序中调用c) 有返回结果的存储过程(列表/集合) 一个包i. create or replace package pack1 isii. type my_cursor is ref cursor; -定义一个游标数据类型iii. end; 建立存储过程i. create or replace procedure pro10(in_deptno in number, out_result out pack1.my

39、_cursor) isii. beginiii. open out_result for select * from emp where deptno=in_deptno;iv. -为了在java程序可使用，在此还不能关闭游标v. end; 在java程序中调用d) 分页存储过程案例解析：输入表名、每示显示数、当前页，返回返回果集。 建立包i. create or replace package pack2 isii. type my_cursor is ref cursor;iii. end; 编写过程i. create or replace procedure Pro11(in_table

40、 in varchar2,in_pagesize in number,in_pagenow in number,out_result out pack2.my_cursor,out_rows out number,out_pagecount out number) isii. v_sql varchar2(2000);iii. v_start number;iv. v_end number;v. beginvi. v_start:=in_pagesize*(in_pagenow-1)+1;vii. v_end :=in_pagesize*in_pagenow;viii. v_sql:=sele

41、ct t2.* from (select t1.*,rownum rn from (select * from |in_tanble|) t1 where rownum=|v_start;ix. open out_result for v_sql;-打开游标，让游标指向结果集x. select count(*) into out_rows from emp;xi. if mod(out_rows,in_pagesize)=0 thenxii. out_pagecount:=out_rows/in_pagesize;xiii. elsexiv. out_pagecount:=out_rows/i

42、n_pagesize+1;xv. end if;xvi. end;(3) 视图视图是oracle的一种数据对象，主要用处是简化操作，提高安全，满足不同用户的查询需求，视图不是一个真正存在的物理表，它是根据别的表，动态生成。a) 创建视图create view 视图名 as select语句with read only;当带了with read only时，就只能对该表进行查询操作b) 实例解析：create view empview as select empno,ename,job from emp;注:没带with read only可对视图进行任何操作，且在视图的操作将更新到原表c) 在

43、多表查询时可简化操作create view myview as select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;创建view后可当表用进行查询d) 视图与表的区别i 表需要占用磁盘空间，视图不需要ii 视图不能添加索引iii 使用视图可简化复杂查询vi 视图有利于提高安全性(4) 触发器a) 触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert，update，delete语句，触发操作实际

44、就是一个pl/sql块。b) 触发器的分类：dml触发器(增删改)，ddl触发器(create.drop.)，系统触发器(与系统相关的触发器，如登录，退出，启动数据库，关闭数据库.)c) 基本语法：createor replace trigger trigger_namebefore|afterinsert|delete|updateof column,column.on schema. table_namefor each row -表示一个行级触发器when condition 条件begin trigger_body; end;d) 案例解析：在emp表添加一条数据，并提示你添加了一条数

45、据 create or replace trigger tri1 afterinsert on scott.empbegindbms_output.put_line(添加了一条数据);end;案例解析2：禁止在休息日改变员工信息，通过before触发器实现create or replace trigger tri2 beforeinsert or update or delete on scott.empbeginif to_char(sysdate,day) in (星期六,星期日) then-阻止删除操作并提示错误信息，参数1是错误号，2是提示信息RAISE_APPLICATION_ERR

46、OR (-20001, 对不起，休息日不能修改员工信息);end if;end;f) 使用条件谓词：inserting,updating,deletingcreate or replace trigger tri3 beforeinsert or update or delete on scott.empbegincasewhen inserting thenraise_application_error(-20002,禁止插入);when updating thenraise_application_error(-20002,禁止修改);when deleting thenraise_application_error(-200