PLSQL学习

ORACLE31.TOP-N分析查找EMP表中工资最高的前三名员工。增加一个伪列，在WHERE里面对伪列进行设置。先需要在FROM里面讲需要显示的列找出来，并且排好顺序，然后增加伪列，然后对增加的伪列进行筛选。SELECTROWNUM,,,....FROM(SELECT)WHEREROWNUM=N1.索引的创建规则：索引能很大程度提高系统的额查询效率。但是所以的创建和维护需要花费时间，还要占用内存。什么时候需要建立索引：当表的数据量很大的时候，但是检索的数据量占比比较小。索引建在什么样的列上：索引一般建在经常需要查询的，需要排序的，或者经常需要与其他表连接的列上面。2.唯一索引：卫衣的索引不允许存在同样索引键值的两个记录行的存在。当为数据库表定义主键的时候数据库自动的在该主键上创建唯一索引。3.索引的使用：索引是不需要调用的，由系统决定。如果需要强制调用索引，在FROM语句中可以用WITH(INDEX(索引名))来调用。4.UNION和UNIONALL：UNION和UNIONALL是将查询结果连接起来，区别是UNION会消除查询结果的重复行，而UNIONALL是不消除重复行的。5.INTERSECT：将两个查询做交集。6.(FULL/RIGHT/LEFT)OUTERJOIN和INNERJOIN：FULL/RIGHT/LEFT自动默认为的就是OUTER形式的连接OUTER:左外连接：左边表的全部记录和右边表的匹配的记录。右外连接：右边表的全部记录和左边表的匹配记录。INNER:产生的是两个结果集的交集。SELECT*FROMEMPFULLOUTERJOINENP_HISONEMP.EMPNO=EMP_HIS.EMPNO。CROSSJOIN:直接就是做的笛卡尔积。一般很少这样使用。7.在BEGIN...END中使用SELECT是必须需要INTO的，单独的SELECT报错。8.创建一个带参数的存储过程：CREATEORREPLACEPROCEDUREfind_proc(enoINNUMBER,enaOUTVARCHAR)ASBEGINSELECTENAMEINTOenaFROMEMPWHEREWMPNO=eno;END;一、PLSQL的使用手册1.数据库的连接：登录之后，点击左上方的钥匙即可切换数据库的连接。2.运行方式：我们想运行一段SQL代码，直接可以选中。如果不选中则运行所有的代码。3.执行快捷键：F84.左下角的windowlist选择显示打开的窗口中的任意一个。5.自动添加和删除注释：，点击这两个就可以添加或者是删除注释。6.通过SELECT查询结果直接更改数据：SQL语句如下:SELECT*FROMEMPFORUPDATE;操作步骤如下：1首先通过SQL语句查询到数据结果。2点击查询结果表上面的。3提交更改或者回滚：。注意：一旦使用FORUPDATE进行表的更改，未Commit或者RollBack时网线断了，则表会被数据库锁住，别人无法使用该表，必须通知DBA进行数据库的解锁。7.点击结果表字段可以对结果表进行排序。8.将查询结果导入Excel表：选中数据，右键CopyToExcel。9.查看表结构：SQL窗口中按下Ctrl鼠标指向表名，查看表结构。点击右下角的：ViewSQL可以查看表的SQL定义语句。10.新建包：包名一般为UG_开头，存储过程一般为UP_开头。11.设置自动RollBack：当SQL执行无法停止，或则出现死机时，强制关闭时进行回滚操作，保证数据的完整性和安全性。12.美化SQL语句：选中SQL语句，点击上方PLSQLBeautifier。13.设置KeyWord全部大写。二、PLSQL:遇到的错误及原因1.无法验证（）违反主键：是因为建表后添加主键的时候主键的数据项存在相同的主键，违反了逐渐的唯一性。三、将查询结果插入到一张表中的三种情况：1.当被插入的表不存在的时候：SELECT*INTOT1FROMT2;2.当被插入的表已经存在的话：INSERTINTOT2SELECT*FROMT1WHERE条件3.跨数据库插入，将A数据库表A.T查到的数据插入B数据库表B.T。SELECT*INTOB.TFROMA.TWHERE条件四、查询数据库中的重复行，并且删除重复的行。1.当主键ID相同时1查询主键ID相同的行SELECTIDFROMEMPWHEREIDIN(SELECTIDFROMEMPGROUPBYIDCOUNT(ID)1)2删除主键ID重复的行DELETEFROMEMPWHEREEMPNOIN(SELECTEMPNOFROMEMPGROUPBYEMPNOHAVINGCOUNT(EMPNO)1)ANDROWIDNOTIN(SELECTMIN(ROWID)FROMEMPGROUPBYEMPNOHAVINGCOUNT(*)1)五、ROWNUM和ROWID的区别。ROWNUM和ROWID都是伪列，但是ROWNUM是基于查询结果使用，随着SQL语句的不同值也不同，但是ROWID是表中每一条数据的存在就存在一个ROWID。1ROWNUM主要用来作为TOP-N分析。筛选排序出来的列。2ROWID用来作为行的标记。现在需要删除主键的重复行。PL/SQL复杂编程1.数组类型数组类型声明和记录一样：TYPEV_NAMESISVARRAY(N)OFVARCHAR2;V_nameV_NAMES;不能直接对数组元素赋值，必须先用构造函数赋初值。V_name:=V_NAMES(值列);在构造函数赋初值之后，可以进行普通的赋值操作。V_name(1):=’中国’;2.异常处理三种：系统定义错误，系统未定义错误，用户自定义错误。用户自动以错误：...IFcursor%NOTFOUNDTHENRAISE错误名;...EXCEPTIONWHEN错误名THEN错误处理方法;END;3.函数的三种参数传递三种参数传递：位置表示法，名称表示法，组合传递。可以统称为传址法、传值法。位置表示法：按照形参的顺序给出实参，注重顺序。名称表示法：形参=实参，注重名称对应，不注重次序。组合传递：名称和位置表示法结合，但是位置表示法必须放在名称表示法的前面。4.数据库优化1访问表的方式：两种方式，全表扫描或者是通过ROWID来访问表。。ROWID访问表的效率更高。2表名顺序的效率：ORACLE从最右边开始访问表，所以讲数据量最少得表作为基本表将会很大程度的提高效率。表19876条记录。表12条记录.FROM表1，表2将会比FROM表2，表1的访问效率高很多。3WHERE中连接顺序：能过滤掉最大量信息的条件写在WHERE的最右边，表之间的连接一般写在其他连接条件之前。4避免使用SELECT*。5.包的使用包的定义：包定义里面的元素全部为公有元素。CREATEORREPLACEPACKAGEtest_packageIS...（变量，常量，函数，存储过程）函数需要指明返回值的类型ENDtest_package;包体的定义：包主题之中可以申明包的私有元素。CREATEORREPLACEPACKAGEBODYtest_packageISBEGIN...(函数，存储过程的实现)ENDtest_package;6.考虑项1.Oracle本来是没有int类型，为了与其他数据库兼容，int是number的子集。int相当于number（22）；2.判断是否存在记录，不存在则插入。SELECTCOUNT(*)INTOXFROMEMPWHEREEMPNO=7369IFX=NULLTHENINSERTINTOEMPVALUES(值列表);ENDIF;END;如果是直接的SELECTEMPNO会因为没有发现数据而不执行下面的代码，我们用聚合函数可以直接计算0或者NULL从而不影响下面的程序运行。3.7.多表查询1.2.8.主键PRIMARY和唯一索引UNIQUEINDEX的区别：、1.主键不能为空值，但是唯一索引有些值可以为空。2.主键只能建一个，唯一索引在一张表上可以建多个。3.当一个表关联外键时，只能关联表的主键。9.