Oracle_SQL学习笔记

SQL学习笔记第1页共34页SQL、PL/SQL学习笔记1．SQL并行查询altersessionenableparalleldmlexecuteimmediate'altersessionenableparalleldml';--修改会话并行DMLselect/*+parallel(a,4)*/*fromtable_nameaselect/*+parallel(a,8)*/*fromtable_nameaselect/*+parallel(a,4)parallel(b,4)parallel(c,4)*/a.*,b.*,c.*fromtable_name1a,table_name2b,table_namecinsert/*+parallel(t,4)*/intotable_nametinsert/*+parallel(t,8)*/intotable_namet/*+parallel(t,8)*/并行处理，一般为CPU的倍数如：4，8等,在执行类型SQL必须先运行:altersessionenableparalleldml2．删除表分区数据altertablemasamk.tb_mk_sc_user_montruncatepartitionmk_user_mon_'||trim(iv_month)删除指定表分区数据3．minus(差集)与intersect(交集)minus指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中,如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现;如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃。intersect指令是运用在两个SQL语句上，如果两个SQL语句的记录完全相同则显示相应记录，否则将不在结果中出现4．Orderby中的nullslastorderbyarea_code,bill_monthnullslast--nullslast将排序字段为null记录放在最后面5．nvl的几个不同函数nvl(a,1)如果a为null返回1,否则返回anvl2(a,1,0)如果a为null返回0,否则返回1nullif(a,b)如果a=b返回null,否则返回a6．怎样确保最终用户在数据库中只有N个会话(如果N为1则只有1个会话)createprofileone_sessionlimitsessions_per_userN;--创建参数文件(N为任意整数)alteruser用户profileone_session;--设置用户的参数文件altersystemsetresource_limit=true;--设置资源限定7．表的字段参照另外表的字段createtableresources(resource_namevarchar2(10)primarykey,,,,);createtableschedules(resource_namereferencesresources,….);SQL学习笔记第2页共34页8．绑定变量的使用1)sql中的绑定变量定义绑定变量：variableemplnovarchar2(10);给绑定变量赋值：execute:emplno:=‘1234567890’;sql/plus中使用绑定变量：select*fromempwhereempno=:emplno;pl/sql中使用绑定变量：executeimmediate‘insertintotvalues(:x)’usingx;游标中使用绑定变量：openc1for‘select*fromempwhereempno=:empno’usingempno;2)DDL语句中不允许使用绑定变量,如:executeimmediate‘createtableaasselct*frombwherex=:x’usingx;3)pl/sql中的批量绑定变量（forall）a)foralliin1..x.countdml;--只能有一条语句(update,insert,delete)sql%bulk_rowcount(i):用于取得在执行批量绑定操作时的第i个元素作用的行数b)bulkcollect子句：用于取得批量数据，它只适用于selectinto、fetchinto和DML返回子句语法：…BULKCOLLECTINTOcollection_name…i.select中使用bulkcollectdeclaretypeemp_table_typeistableemp%rowtypeindexbybinary_integer;emp_tableemp_table_type;beginselect*bulkcollectintoemp_tablefromempwheredeptno=&no;foriin1..emp_table.countloopdbms_output.put_line(emp_table(i).emp);endloop;foralliin1..emp.table.countupdatesalsetdeptno=emp_table(i).deptnowhereempno=emp_table(i).empno;dbms_output.put_line('第2个元素更新的行数为：'||sql%bulk_rowcount(2));end;ii.dml的返回子句中使用bulkcollectdeclaretypeename_table_typeistableofemp.ename%type;ename_tableename_table_type;begindeleteempwheredeptno=&noreturningenamebulkcollectintoename_table;foriin1..ename_table.countloopdbms_output.put_line(ename_table(i));endloop;end;c)fetchc1bulkcollectintocollect1,collect2,…[limitrows]SQL学习笔记第3页共34页9．在SQL中锁定记录锁(lock)机制用于管理对共享资料的并发访问，并提供数据完整性和一致性锁的类型：DML锁、DDL锁、内部锁和闩1)DML锁a．事务锁(TX锁)：事务发起第一个修改时会得到TX锁，直到事务提交或回滚b．DMLEnqueue锁(TM锁)：用于确保在修改表的内容时，表的结构不会改变2)DDL锁a．排他DDL锁(ExclusiveDDLLock)：这会防止其他会话得到它们自己的DDL锁或TM(DML)锁(即其他会话只能对该表执行select)。如：altertableb．共享DDL锁(ShareDDLLock)：这些锁会保护所引用对象的结构，使之不会被其他会话修改，但是允许修改数据。如在创建VIEW时，对原始表就会加共享锁，此时原始可以修改数据，但不能修改表结构c．可中断解析锁(Breakableparselocks)：这些锁允许一个对象向另外某个对象注册其依赖性3)闩(latch)：是轻量级的串行化设备，用于协调对共享数据结构、对象和文件的多用户访问；闩用于保护某些内存结构，如数据库块缓冲区缓存或共享池中的库缓存4)手动锁定和用户定义锁a．通过一条SQL语句手动地锁定数据。i.select…forupdate[nowait/wait[n]]ii.select…forupdateoftable_name--多表关联时锁定指定表的数据行iii.locktableinexclusivemodeb．通过DBMS_LOCK包创建我们自己的锁5)select…forupdate[nowait/wait[n]][skiplocked]详解select*fromresourceswhereresource_name=’abc’forupdate[nowait/wait[n]][skiplocked];nowait：立即执行，如果另有会话正在修改该记录会立即报告错误：ORA-00054:资源正忙，要求指定NOWAIT；如果不选择nowait选项则会一直处理等待状态。wait[n]：等待n秒，如果另有会话正在修改该记录会报告错误：ORA-30006:资源已被占用;执行操作时出现WAIT超时skiplocked：跳过已被别的会话锁定的记录6)settransactionreadonly(只读事务)：使会话取得特定时间点的数据，即使其它会话已经修改并提交新数据，当前会话也只能看到锁定时的数据，同时当前会话不能执行DML.7)settransactionisolationlevel{serializable|readcommitted}(顺序事务)：同只读事务，但允许执行DML语句。10．数据库与实例的关系数据库(Database)：物理操作系统文件或磁盘的集合。(数据库是磁盘上存储的数据文件集合)实例(instance)：一组Oracle后台进程/线程以及一个共享内存区，这些内存由同一个计算机上运行的统一线程/进和所共享。(实例就是一组后进程和共享内存)实例与数据库之间的关系是：数据库可以由多个实例装载和打开，而实例可以在任何时间点装载和打开一个数据库。11．Oralce数据库所包含的文件类型1)与实例相关的文件：参数文件(parameterfile)、跟踪文件(tracefile)、警告文件(alertfile)SQL学习笔记第4页共34页2)构成数据库的文件：数据文件(datafile)、临时文件(tempfile)、控制文件(controlfile)、重做日志文件(redologfile)、密码文件(passwordfile)3)Oracle10g新增文件：修改跟踪文件(changetrackingfile)、闪回日志文件(flashbacklogfile)4)其他类型文件：转储文件(DMPfile)、数据泵文件(DataPumnfile)、平面文件(flatfile)12．表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系1)表空间(tablespace)：是Oracle中的一个逻辑存储容器，位于存储层次体系的顶层，包含一个或多个数据文件2)段(segment)：占用存储空间的数据为对象，如表、索引、回滚段等；段由一个或多个区段组成3)区段(extent)：是文件中一个逻辑上连续分配的空间；区段由块组成4)块(block)：是Oracle中最小的空间分配单位；数据行、索引条目或临时排序结果就存储在块中；Oracle中常见的块大小：2K、4K、8K、16K(最大不能超过32K)5)它们之间的关系：数据库由一个或多个表空间组成，表空间由一个或多个数据文件组成，表空间包含段，段由一个或多个区段组成，区段则由连续的块组成13．名称解释1)决策支持系统(DSS)：DecisionSupportSystem2)联机事务处理(OLTP)：On-lineTransactionProcessing3)联机分析处理(OLAP)：On-LineAnalyticalProcessing也称为在线分析处理。4)ETL(Extraction-Transformation-Loading)：抽取(Extraction)、转换(Transformation)、载入(Loading)ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。ETL是数据仓库中的非常重要的一环。5)关系数据库管理系统(RDBMS)：RelationalDatabaseManagementSystem6)表的三种联接方式:nestedloop(嵌套循环连接)、sortmergejoin(排序合并连接)、hashjoin(哈希连接)7)数据查