Oracle 存储过程和函数 Oracle 存储过程和函数函数一般是工具性的东西。存储过程一般是一些操作。一、Oracle 自定义函数语法：Create function 函数名称 return 返回值类型 as Begin ... End 函数名称;示例：统计t_book记录数create or replace function getbookCount return number as begin declare bookNum number; begin select count(*) into bookNum from t_book; return bookNum; end; end getbookCount;函数调用set serveroutput on begin dbms_output.put_line('t_book表有：' || getbookCount() || '条记录'); end;输出结果：带参数的函数示例：create or replace function getBookSum(table_name varchar2) return number as begin declare sumCount number; count_sql varchar2(200); begin count_sql:='select count(*) from ' || table_name; execute immediate count_sql into sumCount; return sumCount; end; end getBookSum;带参函数调用set serveroutput on begin dbms_output.put_line('表有：' || getBookSum('t_booktype') || '条记录'); end;输出结果：二、Oracle 存储过程语法：Create procedure 存储过程名称 as Begin ... End 存储过程名称； In 只进不出 Out 只出不进 In out 可进可出参数In 只进不出示例：向t_book中添加数据。create or replace procedure addBook(bookname in varchar2, booktypeId in number,price in number) as begin declare maxId number; begin select max(id) into maxId from t_book; insert into t_book(id,bookname,type_id,price) values(maxId+1, bookname,booktypeId,price); commit;--自动提交 end; end addBook;存储过程调用execute 存储过程名称(参数1，参数2.。。)；示例1：execute addBook('代码简洁之道',2,66);查询t_book结果：示例2：如果存在就不添加，没有记录才添加。create or replace procedure addCheckBook(boookn in varchar2, booktypeId in number,price in number) as begin declare maxId number; n number; begin select count(*) into n from t_book where bookname=boookn; if (n>0) then return; end if; select max(id) into maxId from t_book; insert into t_book(id,bookname,type_id,price) values(maxId+1, boookn,booktypeId,price); commit; end; end addCheckBook;调用存储过程execute addCheckBook('代码简洁之道2',2,66);此时已经存在，不在向t_book中添加记录。Out 只出不进示例1：返回添加记录前和添加记录后的总条数create or replace procedure addCheckBookCount(boookn in varchar2, booktypeId in number,price in number, befern out number, aftern out number) as begin declare maxId number; n number; begin select count(*) into befern from t_book; select count(*) into n from t_book where bookname=boookn; if (n>0) then return; end if; select max(id) into maxId from t_book; insert into t_book(id,bookname,type_id,price) values(maxId+1, boookn,booktypeId,price); select count(*) into aftern from t_book; commit; end; end addCheckBookCount;执行存储过程：execute用于执行单据。这里调用存储过程，需要单独调用：declare befern number; aftern number; begin addCheckBookCount('代码简洁之道3',2,66,befern,aftern); dbms_output.put_line('新增前条数：' || befern || '新增后条数：' ||aftern); end;输出结果：三、存储过程Debug1、打开调试，右键 Add debug information2、test调试， Test，然后进入调试界面。3、调试界面，输入参数4、开始调试5、查看参数鼠标放到对应参数上，即可显示当前值。四、程序包引入的目的，是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。语法：Create or replace package 包名 as 变量名称1 数据类型1； 变量名称2 数据类型2； ... ... Function 函数名称1(参数列表) return 数据类型1； Function 函数名称2(参数列表) return 数据类型2； ... ... Procedure 存储过程名称1(参数列表)； Procedure 存储过程名称2(参数列表)； ... ... End 包名；

Oracle 触发器 Oracle 触发器具备某些条件，由数据库自动执行的一些DML 操作行为。一、语句触发器语句触发器针对整个表，作用整个表操作。一般用于权限判断之类的。常用行触发器。语法结构Create trigger 触发器名称 Before/after 触发动作 On 作用对象触发器谓词：INSERTING、UPDATING、DELETING示例1：向t_book插入数据之前判断，用户是否有权限执行查询操作。创建语句触发器：create trigger tr_book before insert on t_book begin if user!='hello' then raise_application_error(-20001,'权限不足'); end if; end;执行插入数据：insert into t_book values(6,'ooo',2,68,'男');提示结果：修改触发器：create or replace trigger tr_book before insert on t_book begin if user!='SCOTT' then raise_application_error(-20001,'权限不足'); end if; end;重新执行插入数据即可插入。insert into t_book values(6,'ooo',2,68,'男');查询结果：注意：多个可以用or连接示例：create or replace trigger tr_book before insert or update or delete on t_book begin if user!='hello' then raise_application_error(-20001,'权限不足'); end if; end; before insert or update or delete：插入、修改、删除之前判断是否有权限。进行插入、修改、删除操作:insert into t_book values(7,'occcc',2,88,'男'); delete from t_book where id=1; update t_book set price=89 where id=1;此时都提示:示例2：在对t_book增删改之后记录日志到t_book_log表中。编写行触发器：create or replace trigger tr_book_log after insert or update or delete on t_book begin if inserting then insert into t_book_log values(user, 'insert', sysdate); else if updating then insert into t_book_log values(user, 'upate', sysdate); else if deleting then insert into t_book_log values(user, 'delete', sysdate); end if; end if; end if; end;分别执行增删改操作：insert into t_book values(8,'866',2,88,'男'); delete from t_book where id=8; update t_book set price=88 where id=1;此时t_book_log表中已经有记录了。select * from t_book_log;查询结果：注意：判断增删改用的不是insert、delte、update，而是inserting、updating、deleting。二、行触发器行触发器针对行记录。一般用于记录操作日志。语法结构Create trigger 触发器名称 Before/after 触发动作 For each row On 作用对象只是语句触发器多了一个For each row示例1：当修改t_book增加一条数据时，t_booktype对应的类型名称就改为t_book中新增的书名作为类型名。为插入数据前t_book\t_booktype表数据：编写行触发器：create or replace trigger tr_bookandtype after insert on t_book for each row begin update t_booktype set booktypename=:new.bookname where id=:new.type_id; end;注意：触发器内置变量:old 表示旧行记录。:new 表示新插入行记录。执行插入操作：insert into t_book values(8,'新增sql秘籍',2,55,'男');重新查看2表数据：可以看到，新增数据行8后，对应的typeid=2的类型名称改为书名了。示例2：当删除t_book一条数据后，将t_booktype中对应类型名称，改为被删除t_book记录中书名。create or replace trigger tr_bookdeltype after delete on t_book for each row begin update t_booktype set booktypename=:old.bookname where id=:old.type_id; end;注意：记录被删除了，所以可以用内置对象old.字段，获取删除记录的字段信息。t_book、t_booktype表原始数据：删除操作：delete from t_book where id=7;查看删除后t_book、t_booktype表数据：t_book中id=7记录已被删除。t_booktype中typename=www了。注意：一般行触发器用的较多。三、触发器禁用和开启禁用触发器alter trigger 触发器名称 disable启用触发器alter trigger 触发器名称 enable四、删除触发器drop trigger 触发器名称;五、修改触发器修改和其它修改视图、触发器、存储过程一样。create or replace只需要加上or replace

Oracle 游标 一、游标使用游标，我们可以对具体操作数据，比如查询的结果，对行，列数据进行更加细致的处理。以及对其他DML操作进行判断等操作；类似一个数据集合。1、语法declare cursor 游标名称 is 查询的sql;示例：declare cursor cu_book is select bookname,price from t_book;注意：游标默认是关闭的。2、打开右边open 游标名；示例：open cu_book;3、关闭游标close 游标名;示例：close cu_book;4、游标循环fetch 游标名 into 临时值1,临时值2...;循环将游标中的字段保存到临时值中。5、判断游标是否还有记录游标名%found二、示例set serverout on; --定义游标哦 declare cursor cu_book is select bookname,price from t_book; b_name varchar(20); b_price number; begin open cu_book; --打开游标 fetch cu_book into b_name,b_price; --循环游标取值 while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑 dbms_output.put_line('书名：' || b_name || '价格' || b_price); fetch cu_book into b_name,b_price; end loop; close cu_book; end;输出结果：三、注意事项1、定义的游标默认是关闭的。2、while条件注意跳出。3、注意关闭游标。4、loop要有对应的end loop四、显示游标1、显示游标上面定义的临时变量的类型必须和游标里面查询表的字段类型一样，如果表的字段类型改了，定义的临时变量就会报错，类型不一样。因此就出现了显示游标。定义： 临时变量名 表明.字段名%type;上面一中示例改进成显示游标：set serverout on; --定义游标哦 declare cursor cu_book is select bookname,price from t_book; b_name t_book.bookname%type; b_price t_book.price%type; begin open cu_book; --打开游标 fetch cu_book into b_name,b_price; --循环游标取值 while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑 dbms_output.put_line('书名：' || b_name || '价格' || b_price); fetch cu_book into b_name,b_price; end loop; close cu_book; end;输出结果：2、字段类型表示表中字段数据类型。%type3、行类型表示表中一行数据类型。%rowtype示例：set serverout on; --定义游标哦 declare cursor cu_book is select * from t_book; book t_book%rowtype; -- 行类型 begin open cu_book; --打开游标 fetch cu_book into book; --循环游标取值 while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑 dbms_output.put_line('书名：' || book.bookname || '价格' || book.price); fetch cu_book into book; end loop; close cu_book; end;注意：1、游标数据变成select * 查询全部了。2、fetch循环游标时，是将游标里面记录复制到定义的book行类型变量里面了。3、取值时，是用行类型变量.字段名取值。输出结果：五、隐式游标不用显示定义游标，但是显示的功能更强大。1、DML 执行过程中为真，结束后为假SQL%ISOPEN 布尔型DML 执行过程中为真，结束后为假示例：begin if sql%isopen then dbms_output.put_line('游标打开的'); else dbms_output.put_line('游标未打开'); end if; end;输出结果：2、DML 语句成功执行的数据行数SQL%ROWCOUNT 整型代表DML 语句成功执行的数据行数示例：declare b_count number; begin select count(*) into b_count from t_book; dbms_output.put_line('游标捕获行数：'|| sql%rowcount); end;输出结果：只成功执行了一次DML查询操作。示例2：begin update t_book set bookname='xxxx' where id=11111; if sql%rowcount=1 then dbms_output.put_line('执行修改成功'); else dbms_output.put_line('未执行成功'); end if; end;输出结果：未成功，因为没有id=11111的记录。修改id=1，重新执行；begin update t_book set bookname='xxxx' where id=1; if sql%rowcount=1 then dbms_output.put_line('执行修改成功'); else dbms_output.put_line('未执行成功'); end if; end;修改成功，因为有id=1的记录，成功执行修改了的。3、插入、删除、更新或单行查询操作成功SQL%FOUND 布尔型值为TRUE 代表插入、删除、更新或单行查询操作成功4、与SQL%FOUND 属性返回值相反SQL%NOTFOUND 布尔型与SQL%FOUND 属性返回值相反六、动态游标1、定义强类型动态游标declare type 类名 is ref cursor return 表明%rowtype;2、定义一个动态游标对象游标对象 类名;示例：查询表中是否有"jvm并发"的书，如果有就输出改记录信息，没有则查询全部记录，并输出。--定义一个booktype类似一个类 declare type booktype is ref cursor return t_book%rowtype; --定义一个动态游标对象 cu_book booktype; --返回行数 b_count number; --定义一个行类型变量 b_rowdata t_book%rowtype; begin select count(*) into b_count from t_book where bookname='jvm并发1'; --如果没有查到JVM并发这本书，就查询全部记录,保存到动态游标中。 if b_count=0 then open cu_book for select * from t_book; --注意打开游标 else open cu_book for select * from t_book where bookname='jvm并发'; end if; --循环游标将每条记录，保存到行变量中。 因为cu_book动态游标里面可能一条数据或全部的记录。 fetch cu_book into b_rowdata; --判断游标是否还有数据，有就循环输出行变量中字段数据 while cu_book%found loop dbms_output.put_line('书名：'||b_rowdata.bookname || '价格：' || b_rowdata.price); --还有数据就循环将游标数据保存到行变量中 fetch cu_book into b_rowdata; end loop; close cu_book; end;当bookname='jvm并发1'时，不存在该记录，因此查询全部记录信息。输出结果：当bookname='jvm并发'时，存在该记录，因此输出记录信息。3、定义弱类型动态游标弱类型比强类型强大。弱类型动态游标定义，只是少了返回类型的定义，没有了返回值。declare type 游标类型 is ref cursor;需求：查询t_book中是否有书名为“jvm并发”的记录，有就输出t_book所有记录数据，没有则输出t_booktype表所有记录数据。原始表数据：代码：--定义弱类型动态游标 declare type notknowtype is ref cursor; --定义一个动态游标对象 notknow notknowtype; --用于统计是否存在记录 b_count number; cu_book t_book%rowtype; cu_booktype t_Booktype%Rowtype; begin select count(*) into b_count from t_book where bookname='jvm并发'; if b_count >0 then --不存在 open notknow for select * from t_book; --查询所有t_book数据到游标 --将游标对象循环赋值到t_book行数据中 fetch notknow into cu_book; while notknow%found --如果游标对象中有数据，则循环输出全部记录信息 loop dbms_output.put_line('书ID:' || cu_book.id || '书名：' || cu_book.bookname || '价格：' || cu_book.price); fetch notknow into cu_book;--继续循环一下条数据 end loop; close notknow; else open notknow for select * from t_booktype; fetch notknow into cu_booktype; while notknow%found loop dbms_output.put_line('t_booktype id:' || cu_booktype.id || 't_booktype name:' || cu_booktype.booktypename); fetch notknow into cu_booktype; end loop; close notknow; end if; end;输出结果：书ID:3书名：jvm并发价格：66 书ID:4书名：c++价格：99 书ID:5书名：C#价格：51 书ID:1书名：xxxx价格：55 书ID:2书名：go秘籍价格：78修改条件中书名为不存在书名，重新执行：--定义弱类型动态游标 declare type notknowtype is ref cursor; --定义一个动态游标对象 notknow notknowtype; --用于统计是否存在记录 b_count number; cu_book t_book%rowtype; cu_booktype t_Booktype%Rowtype; begin select count(*) into b_count from t_book where bookname='jvm并发111'; if b_count >0 then --不存在 open notknow for select * from t_book; --查询所有t_book数据到游标 --将游标对象循环赋值到t_book行数据中 fetch notknow into cu_book; while notknow%found --如果游标对象中有数据，则循环输出全部记录信息 loop dbms_output.put_line('书ID:' || cu_book.id || '书名：' || cu_book.bookname || '价格：' || cu_book.price); fetch notknow into cu_book;--继续循环一下条数据 end loop; close notknow; else open notknow for select * from t_booktype; fetch notknow into cu_booktype; while notknow%found loop dbms_output.put_line('t_booktype id:' || cu_booktype.id || 't_booktype name:' || cu_booktype.booktypename); fetch notknow into cu_booktype; end loop; close notknow; end if; end;输出结果：t_booktype id:2t_booktype name:go t_booktype id:1t_booktype name:java history注意:成对的结束，最后的分号，关闭游标。七、查看游标1、查看系统游标数(最大游标数)select value from v$parameter where name = 'open_cursors';2、查看当前打开的游标数目select count(*) from v$open_cursor;3、查看游标使用情况select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs from v$open_cursor o, v$session s where user_name = '用户名' and o.sid = s.sid group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;4、修改Oracle最大游标数alter system set open_cursors=2000 scope=both;5、各用户的打开游标总数SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;6、查找数据库各用户各个终端的缓存游标数SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM ( SELECT A.VALUE, S.MACHINE, S.USERNAME FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'session cursor cache count' ) AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;7、查找数据库各用户各个终端的打开游标数SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM ( SELECT A.VALUE, S.MACHINE, S.USERNAME FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'opened cursors current' ) AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;

Oracle 权限、视图、约束 Oracle 权限、视图、约束一、数据库权限1、查看用户所属角色select * from user_role_privs;2、查询角色权限CONNECT：仅连接权限select * from dba_sys_privs where grantee='CONNECT'查看RESOURCE权限：select * from dba_sys_privs where grantee='RESOURCE'DBA角色权限查看select * from dba_sys_privs where grantee='DBA'3、用户授权例如：授予scoot拥有dba的权限grant dba to scott;二、视图1、创建视图Create view 视图名称as 查询语句 create view v_emp1 as select ename,job from emp;2、修改视图create or replace view 始图名称 as 新的查询数据； create or replace view vemp as select empno,ename,job from EMP t;注意与创建视图的不同：多了一个or replace3、删除视图drop view 视图名称； drop view vemp2;4、使用视图select * from 视图； select * from vemp;就当作是一张虚拟的表使用。5、修改视图数据注意：修改视图数据，实际是修改实际表的数据，不是视图的数据。例如修改上面视图数据：update vemp set ename='xxxx' where job='CLERK' and empno='7369'修改后：最后实际修改的是，视图中查询数据表的实际数据。上面视图实际修改的是EMP表的数据：select * from EMP;注意：修改视图数据时，实际是修改实际表的数据，不是视图的数据。6、视图添加数据注意：向视图数据添加数据，实际是向实际表中添加数据和普通表添加数据一样，视图只是在向实际的表添加数据 insert into vemp values('1111','bbbbb','teacher');查看添加视图数据，实际是向实际表中添加了数据。select * from vemp;查看实际表中数据：select * from EMP;7、删除视图中数据和普通删除表数据一样，实际是删除的实际表中数据。delete from vemp where empno=1111总结：对视图中数据的CRUD实际是对真实表的数据操作，实际开发中一般都会将视图设置为只读，防止误操作。8、只读视图Create view 视图名称as 查询语句 with read only;当向视图添加、删除、修改数据时，都会提示“无法对只读视图执行dml操作”。三、约束1、主键约束（Primary）用来标识唯一一条记录的。非空，和其它表的外键有关系，其它表的外键关联该表的主键，起到约束作用。一般用作条件查询、条件修改、条件删除等操作。id设置为主键。查询一条数据：insert into t_bookType values(1,'java history');查看数据：select * from t_bookType;查看建外键sql语句：查看sql：这里就能看到建表、建主键的sql语句了：2、外键约束(Foreign)起到约束作用。新建表：创建外键：注意：另一张表t_bookType的主键id不能为空，否则建外键时会报错id不能为空。查看建外键sql语句和上面一样：约束作用：查看t_bookType数据：向t_book添加数据时：添加一条不存在的主键数据时，就会提示“违反完整约束条件”因此起到了，约束添加的外键只能是另一张表里面存在的主键。3、唯一性约束(Unique)约束某些字段值作为唯一标识，不能重复添加相同数据。添加方式查看sql脚本同上：没加约束前，可以添加相同bookTypeName的记录，加了约束后会提示报错"违反唯一约束条件"4、检查约束（针对所有记录，上面1、2、3都是正对一条记录）给某个字段添加，写入值的条件。t_book增加价格price字段：查询t_book:如果以后向每次添加数据时，检查price价格返回是否在50-100之间，就可以使用检查约束。添加检查约束sql：添加数据价格在范围内不报错，超出范围会提示报错“范围检查约束条件”5、默认值约束其实就是添加数据时，某些字段没有设置时，给字段设置默认值。t_book增加作者性别字段：添加一条数据：insert into T_BOOK(id,BOOKNAME,TYPE_ID,price) values(3,'jvm并发',1,66);新加数据sex性别为空，不符合实际逻辑，怎么会没有性别，因此想设置默认值。添加默认值约束：重新添加一条数据：此时就有默认值了。删除上面添加没有sex性别的记录，重新加添；insert into T_BOOK(id,BOOKNAME,TYPE_ID,price) values(3,'jvm并发',1,66);现在就有值了。添加默认值约束SQL：6、非空约束设置字段值不能为空勾选：允许为空，不勾选：不允许为空。查询一条件数据，价格不添加：添加数据值，为添加price的值，由于设置了非空约束，不能为空，所以报错“无法将null插入”

Oracle常用函数 一、Oracle 表空间一个数据库可以有多个表空间，一个表空间里可以有多个表。表空间就是存多个表的物理空间；可以指定表空间的大小位置等。创建表空间create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;自动扩展大小create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;设置最大空间create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;更改用户默认表空间alter database default tablespace ts1;表空间改名alter tablespace ts1 rename to tss1;删除表空间drop tablespace ts2 including contents and datafiles;二：虚拟表dual 表Dual 表是sys 用户下的一张虚表；提供一些运算和日期操作时候用到；例如，查询系统日期时间select sysdate from dual;三、序列序列作为数据库里的对象，主要作用是生成唯一的主键值；创建序列create sequence 序列名称； 重要属性： 序列名称.currval 当前值nextval 下一个值 指定初始值： start with xx序列属性Minvalue maxvalue 最大值和最小值默认最小值1 最大值10 的27 次方； Increment by 属性序列增长步长默认值1 Cycle 设置循环； （了解即可，不使用）； Cache 缓存设置； 一次获取序列的多个连续值，默认20 ，放到内存中，方便下次快速获取；四、CRUD1、表的CRUD创建表create table 表名 ( 字段名1 字段类型 默认值 是否为空 ， 字段名2 字段类型 默认值 是否为空， 字段名3 字段类型 默认值 是否为空， … );示例：create table T_BOOK_LOG ( operusers VARCHAR2(10) not null, optertype VARCHAR2(10), optertime DATE )修改表名rename 旧表名 to 新表名；示例：rename t_book to t_books;删除表删除所有数据，会删除表结构drop table 表名；示例：drop table T_BOOKs;修改表添加新字段alter table 表名 add(字段名 字段类型 默认值 是否为空);示例：alter table t_book add(booknum number(6) default 0 null);字段添加注释comment on column 表明.字段 is '注释内容';示例：comment on column t_book.booknum is '书本数量';修改字段类型属性alter table 表名 modify (字段名 字段类型 默认值 是否为空);示例：alter table t_book modify (booknum varchar2(20) default 'hello');修改字段名alter table 表名 rename column 列名 to 新列名;示例：alter table t_book rename column booknum to remark;删除字段alter table 表名 drop column 字段名；示例：alter table t_book drop column remark;2、数据的CRUDinsert给指定列插入数据：insert into dept(deptno,dname) values(50,'xx'); 插入全部列数据：insert into dept(deptno,dname,loc) values(60,'xx','lll'); 简写insert into dept values(70,'xxx','llll');update更新指定数据：update dept set dname='司法部' where deptno=50; update dept set dname='司法部' ,loc='china' where deptno=50;delete删除指定数据：delete from dept where deptno=70; 删除指定条件的数据：delete from dept where deptno>40;truncatetruncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。truncate table 表名；查询所有select * from emp;指定字段查询select ename,sal from emp;where 条件查询select * from emp where sal>=800; select * from emp where sal>=1500 and job='SALESMAN';Distinct去重复记录Group by分组查询select job,count(ename) as num from EMP t group by job;Having 过滤分组select job,count(ename) as num from EMP t group by job having count(ename)>=2;Order by 排序select * from emp order by sal desc;子查询查询出基本工资大于平均工资的员工select * from emp where sal>(select avg(sal) from emp)union并集（去重复）select * from t_user1 union select * from t_user2;union all并集（不去重）select * from t_user1 union all select * from t_user2;intersect交集select * from t_user1 intersect select * from t_user2;minus差集select * from t_user1 minus select * from t_user2;inner内连接select * from emp t,dept d where t.deptno=d.deptno;和这个一样，inner join效率更高select * from emp e inner join dept d on e.deptno=d.deptno;left join左外连接select * from emp e left join dept d on e.deptno=d.deptno;right join右外连接select * from emp e right join dept d on e.deptno=d.deptno;五、数据类型1、字符串类型及函数字符类型分3 种，char(n) 、varchar(n)、varchar2(n) ；char(n)：固定长度字符串，假如长度不足n，右边空格补齐；varchar(n)：可变长度字符串，假如长度不足n，右边不会补齐；varchar2(n)：可变长度字符串，Oracle 官方推荐使用，向后兼容性好；char(n) VS varchar2(n)： char(n)查询效率相对较高，varchar2(n)存储空间相对较小；字符串常用方法lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user3; rpad() 向右补全字符串：select rpad(stuno,6,'0') from t_user3; lower() 返回字符串小写：select lower(userName) from t_user3; upper() 返回字符串大写：select upper(userName) from t_user3; initcap() 单词首字符大写：select initcap(userName) from t_user3; length() 返回字符串长度：select length(password) from t_user3; substr() 截取字符串：select substr(userName,1,2) from t_user3; instr() 获取字符串出现的位置：select instr(password,'23',2,2) from t_user3; ltrim() 删除左侧空格:select ltrim(userName) from t_user3; rtrim() 删除右侧空格:select rtrim(userName) from t_user3; trim() 删除两侧空格:select trim(userName) from t_user3; concat() 串联字符串：select concat(userName,password) from t_user3; reverse() 反转字符串：select reverse(userName) from t_user3;2、数值类型number：是oracle 中的数据类型；number(precision,scale)；Precision，scale 均可选；Precision 代表精度，sacle 代表小数位的位数；Precision 范围[1,38] scale 范围[-84,127]举例： 12345.678 Precision 是8 scale 是3；数值类型常用方法abs() 求绝对值；select abs(n1) from t_number where id=1; round() 四舍五入：select round(n1,2) from t_number where id=1; ceil() 向上取整：select ceil(n1) from t_number where id=2; floor 向下取整：select floor(n1) from t_number where id=2; Mod()取模：select mod(5,3) from dual; Sign()正负性：select sign(n1) from t_number where id=1; Sqrt() 求平方根：select sqrt(9) from dual; Power()求乘方：select power(2,3) from dual; Trunc()截取：select trunc(123.456,2) from dual; To_char() 格式化数值：常见的字符匹配有0、9、，、$、FM、L、CTo_char() 格式化数值To_char() 格式化数值：常见的字符匹配有0、9、，、$、FM、L、C select to_char(123.45,'0000.000') from dual; select to_char(123.45,'9999.999') from dual; select to_char(123123,'99,999,999.99') from dual; select to_char(123123.3,'FM99,999,999.99') from dual; select to_char(123123.3,'$99,999,999.99') from dual; select to_char(123123.3,'L99,999,999.99') from dual; select to_char(123123.3,'99,999,999.99C') from dual;3、日期类型Date 和timestamp（时间戳）Date ：包含信息century（世纪信息） year 年month 月day 日hour 小时minute 分钟second 秒Timestamp ：一般用于日期时间要求非常精确的情况，精确到毫秒级；insert into t_date values(1,sysdate,systimestamp);日期类型常用方法select sysdate from dual; select systimestamp from dual; Add_months 添加月份select add_months(d1,2) from t_date where id=1; Last_day 返回指定日期月份的最后一天select last_day(d1) from t_date where id=1; update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1; update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1; Months_between 返回两个日期的相差月数select months_between(d1,d3) from t_date where id=1; next_day 返回特定日期之后的一周内的日期：select next_day(d1,2) from t_date where id=1;Trunc 截取日期select trunc(d1,'YYYY') from t_date where id=1; select trunc(d1,'MM') from t_date where id=1; select trunc(d1,'DD') from t_date where id=1; select trunc(d1,'HH') from t_date where id=1; select trunc(d1,'MI') from t_date where id=1;Extract 返回日期的某个域select extract(year from sysdate) from dual; select extract(month from sysdate) from dual; select extract(day from sysdate) from dual; select extract(Hour from systimestamp) from dual; select extract(minute from systimestamp) from dual; select extract(second from systimestamp) from dual;To_char 将日期转换成字符串select to_char(d1,'YYYY-MM-DD') from t_date where id=1; select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;4、其他常用处理函数常用的聚合函数Max 求最大值select max(sal) from emp ;Min 求最小值select min(sal) from emp ;Avg 求平均值select avg(sal) from emp ;Sum 求和select sum(sal) from emp ;Count 统计记录数select count(ename) from emp ;Nvl 空值处理select ename,nvl(sal,0) from emp;rownum 分页select * from (select a.*,rownum rn from (select * from emp) A where rownum<=10) where rn>5;Oracle 中的运算select 2+1 from dual; select 2-1 from dual; select 2*1 from dual; select 2/1 from dual;Between and条件判断式Between and 范围查询：select * from emp where sal between 900 and 1500; select * from emp where sal>=900 and sal<=1500;In 集合范围select ename,hiredate from emp where ename in (select distinct ename from bonus)Like 模糊查询select * from emp where ename like '%M%' select * from emp where ename like 'M%' select * from emp where ename like '_M%'