oracle高级语法(事物、函数、存储过程、触发器、异常)

Xmanager工具:可以远程的调用，在本地显示一个图形化。。。--------------------------tnsnames.oravi$ORACLE_HOME/network/admin/tnsnames.ora在Linux中，如何图形化配置网络服务名：进入oracle用户-----------netmgr-----------------------------------------------------Oracle-->SID(实例)删除重装数据库配置助手dbca--》一般来说一个Oracle服务器只有一个实例TransactionProcessing（事务处理）...step10:选择字符集编码----------------------------------------------------------------------------------------------------------------------------------------------------事务：举例：转账A-->B￥100A:updatetb_balancesetbalance=balance-100whereid=1001;B:updatetb_balancesetbalance=balance+100whereid=1002;上述操作必须：要么全部成功，要么全部失败事务是一组DML组成的Commit：提交到SID，一旦提交不能修改Rollback：回滚到修改前锁：锁一般加在数据库中的表上行锁表锁字段锁-------------------------------------提交：commit;结束事务、删除保存点、释放锁rollback;删除保存点、释放锁保存点：savepoint保存点的名字rollbackto保存点的名字---------------------------------------------数据库的事务隔离级别：1、读未提交--》最不安全的2、可重复读--》MYSQL的事务隔离级别3、读已提交--》Oracle的事务隔离级别4、序列化-->一个一个操作，效率最低-----------------------------------------事务什么是开启？SQLPLUS客户端登陆成功后，默认开启事务什么时候提交？手动提交commit;自动提交客户端正常退出exitcommit后，一定会重新开启一个新的事务----------------------------只读事务：settransactionreadonly;特点：只对当前用户当前窗口有效应用：统计某个时间点一共卖票的张数------------------------------**小技巧：dual-->sys用户创建的动态的表。。。selectlower('ABC')fromdual;Oracle的函数maxmin,avg,count,sum-->统计函数，where条件后面不能跟统计函数。Oracle的函数：必须有输入，肯定有返回值字符串处理：lower(字符串)-->转换成小写，返回类型字符串upper(字符串)-->转换成大写返回类型字符串length(字符串);-->字符串的长度，返回类型数字substr(char,m,n)--》截取字符串，返回类型字符串Oracle的字符串，下标从1开始------------------------Oracle数学函数round(n,[m])-->四舍五入取m位小数，返回数字类型

trunc(n,[m])-->截取指定位数小数，保留m位

mod(m,n)--->取模

floor(n)--->不大于n的最大整数

ceil(n)--》不小于n的最小整数power(m,n)-->m的n次方abs(n)-->n的绝对值----------------------------------------------------------------------格式化输出to_char(待转换的数据,'格式')日期的处理：to_date函数(将要成的字符串，‘格式’)to_date('1990-1-114:00:20','yyyy-mm-ddhh24:mi:ss');------------------------------------------------------------------------------sysdate-->返回当前时间add_months(d,n)-->在原有日期基础上加上n个月add_months(sysdate,3);-->3个月后的今天last_day(日期)-->返回日期所在月份最后一天计算日期：selectsysdate-to_date('2014-3-114:00:00','yyyy-mm-ddhh24:mi:ss')fromdual;-----------------视图：虚拟的表,只读的，保存的是一个查询的结果view_test;select*fromempwheredeptno=20;select*fromview_test;----------------------createview视图名称as查询结果表一开始没有创建视图的权限：grantcreateviewtotest;--------------------------------数据库的备份和恢复：备份的几种方式：1）createtableemp_bakasselect*fromemp;2）导出sql文件3）使用工具exp前提：只要安装Oracle的客户端，就有这个工具exp命令如何使用？userid--》登录到数据库的用户名密码，网络服务名test/123456@abctables-->需要备份哪些表file-->客户端生成的文件放在哪个位置rows=n

--》只导出表结构，不导出表的数据1）举例：备份emp表expuserid=test/123456@abctables=(emp,dept)file=d:\test\1.dumpimp导入工具，恢复empimpuserid=test/123456@abctables=empfile=d:\test\1.dumprows=n;-------------------------------------------了解：数据字典动态性能视图：需要了解用户到底创建了哪些表？？alteruserscottaccountunlock;select*fromall_tableswhereowner='yang';----看yang用户创建的所有的表select*fromuser_all_tables;----------查看所有的用户的表select*fromv$session;--------------查看有哪些终端连入oracleselect*fromdba_sys_privswheregrantee='RESOURCE';select*fromdba_tab_privswheregrantee='TEST';select*fromdba_role_privswheregrantee='TEST';什么是表空间？在存储里面****.dbf文件,存放表和视图、索引-----------------------------------------------数据的完整性：Student:idnameclassNo(fk)1zhangsan10Class:cidcname10class10-------------------------使用约束，维护数据的完整性约束：限定不能做什么！！！Oracle的约束：非空notnull：字段输入必须有值唯一unique：字段的值不能重复主键primarykey：notnull+unique外键foreignkey:引用另外一个地方的一个字段checked：限定只能输入固定的值添加约束：1、属性级约束（在建表的外面添加约束）（建议使用，因为这样可以方便添加注解，增加代码的可读性）第一种分类，列的属性notnull,uniquealtertabletb_balancemodify(字段类型notnull);createtabletb_balance(acctidnumber,balancenumber(9,2));第二种分类，必须是oracle中的一个对象primary,foreignkeyaltertable表名addconstraint约束名约束类型约束名定义规则：pk_XXXXfk_XXXXaltertable表名addconstraint约束名primarykey(字段);altertabletb_balanceaddconstraintfk_bankforeignkey(bank)referencestb_bank(bankid);删除约束：altertabletb_balancedropconstraint约束名;----------------------------------------------2、表级约束(写在表里面，缺点：不利于注解键的作用)：Createtabletb1(C#varchar2(20),Namevarchar2(20)Primarykey(C#),ForeignKey(C#)referencesT(T#))-----------------------------------------------------------索引（index）：为了提高查询效率。创建索引后搜索机制将会自动优先调用索引。Studentidname1zhangsan2lisi....此处省略一万行10001zhangsan10002zhangsan查询名字是zhangsan的学生select*fromstudentwherename='zhangsan'没有索引：共查询10003次。。。-----------------------索引：name字段建立索引zhangsan1,10001lisi2wangwu3,100....共查询两次！！！-----------------------------------问题：select*fromstudentwherenamelike'zhang%'--》模糊查询使用不到索引，全文的遍历提高大数据量访问的效率：1、建立索引2、全文检索借助第三方工具sphinx,coreseek3、表分区按照用户id分区1~10--》分区111~20--》分区24、分库，分表------------------------------------------索引的问题：如果在数据量激增的情况，后面推荐使用重新建立索引1、创建索引：createindex索引名on表名（字段）2、删除索引dropindex索引名;建立索引的原则：1、在大表上建立索引才有意义

2、在where子句或是连接条件上经常引用的列上建立索引

3、索引的层次不要超过4层createindexemp_idx1onemp(ename,job);select*from时empwhereENAME='XXXX'ANDjob='CLERK'--》能使用到索引select*fromempwherejob='CLERK'andENAME='XXXX'-->使用不到索引也是保存在表空间上的，占用空间一般是表大小的1.2倍----------------------------------------------------PL/SQL-->SQL编程函数，存储过程，触发器函数：count,sum,lower...统计函数、数学函数、用户自定义函数等，必须有且只有一个返回值存储过程：Oracle服务端的一个代码，节约带宽，可以没有、有一个或者多个返回值。触发器：当你操作一个表的时候，可以触发一段代码。---------------------存储过程：1、两种形式：临时存储过程永久存储过程临时存储过程：1、最简单的存储过程：updatetb_balancesetbank=1;declare变量列表begin要编写的代码end;/：表示立即执行edit--》编辑上一条命令-------------------createporocedure存储过程名字is变量列表begin要编写的代码end;--------------------------如何调用存储过程exec存储过程名（或）call存储过程名-------------------------showerrors;-->显示错误如何让存储过程在屏幕输出一个值？setserveroutputon;----------------------------存储过程函数触发器块：begin---end;包：集合，包含函数，触发器，和存储过程Paktest_prodexecPak.test_prod-----------------------------------如何输出到屏幕dbms_ouput.put_line(字符串)---------------------------------------------标识符号的命名规范定义变量，建议使用v_开头：

定义常量，建议使用c_开头；定义游标，建议使用_cursor为后缀；定义异常时，建议使用e_作为前缀--------------------------------------练习：1、查询员工的工资，并且打印出来declare--变量的定义v_salnumber(9,2);beginselectsalintov_salfromempwhereempno='&empno';--可以和键盘交互dbms_output.put_line(v_sal);end;/2、查询员工的姓名和工资，并且打印出来(一个select后面只能有一个into)SMITH的工资是800declarev_namevarchar2(20);v_salnumber(9,2);beginselectename,salintov_name,v_salfromempwhereempno=&empno;dbms_output.put_line(v_name||'的工资是'||v_sal);end;/**注意：永久存储过程不能使用键盘交互--------------------------------''-->自动转换，一般来说，写SQL一定要注意类型，但是如果将number类型写成字符类型，数据库有自动转换的功能，但其他的类型错误不行。t1(idnumber)insertintot1values(1);insertintot1values('1');----------------------------------异常和错误？？错误：程序代码无法控制的一部分，一旦产生程序将立即终止。异常：程序运行过程中可能会遇到的问题，程序可以捕捉并且处理==异常处理。如：1/0-->除数为0select查询，找不到记录！！！是异常declare变量列表begin//执行的代码exception//异常处理的部分when异常名称thenbegin//处理代码。。endend;----------------------------2、查询员工的姓名和工资，并且打印出来(一个select后面只能有一个into)SMITH的工资是800declarev_namevarchar2(20);v_salnumber(9,2);beginselectename,salintov_name,v_salfromempwhereempno=&empno;dbms_output.put_line(v_name||'的工资是'||v_sal);exceptionwhenno_data_foundthenbegindbms_output.put_line('记录未找到');end;end;/----------------------异常的分类：1、Oracle预定义2、自定义-------------------if条件then.....elsif条件2then.....else.....endif;---------------------练习：查询一个员工的薪资，如果太少(<1000)，则显示“机密”（必须用异常）1)异常的定义ex_sal_lowexception;2）如何抛出异常declarev_salnumber(9,2);ex_sal_lowexception;beginselectsalintov_salfromempwhereempno=&empno;ifv_sal<1000thenraiseex_sal_low;endif;dbms_output.put_line(v_sal);exceptionwhenex_sal_lowthenbegindbms_output.put_line('薪资太少，机密');end;end;-----------------------------------------如何赋值？？1、Select???into变量from....//从表中查询到的数据赋值2、:=赋值号//直接进行变量间的赋值或者常量赋值练习：两个数字num1:10,num2:20，计算num1+num2，打印结果declarenum1number;num2number;rsnumber;beginnum1:=10;num2:=20;rs:=num1+num2;dbms_output.put_line(rs);end;--------------------循环forwhile练习：使用for循环打印1~10declarebeginforiin[reverse]1..10loopdbms_output.put_line(i);endloop;end;使用while循环打印1~10declareinumber:=1;beginwhilei<=10loopdbms_output.p