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

Xmanager工具:可以远程的调用，在本地显示一个图形化。-tnsnames.oravi $ORACLE_HOME/network/admin/tnsnames.ora在Linux中，如何图形化配置网络服务名：进入oracle用户-ànetmgr-Oracle-> SID(实例)删除重装数据库配置助手dbca -一般来说一个Oracle服务器只有一个实例Transaction Processing（事务处理）.step 10 : 选择字符集编码-事务： 举例：转账 A -> B ￥100A:update tb_balance set balance = balance-100 where id = 1001;B:update tb_balance set balance = balance+100 where id = 1002; 上述操作必须 ： 要么全部成功，要么全部失败事务是 一组DML组成的Commit ：提交到SID，一旦提交不能修改Rollback ：回滚到修改前锁： 锁一般加在数据库中的表上 行锁 表锁 字段锁-提交： commit; 结束事务、删除保存点、释放锁 rollback;删除保存点、释放锁保存点：savepoint 保存点的名字rollback to 保存点的名字 -数据库的事务隔离级别： 1、读未提交 - 最不安全的 2、可重复读 - MYSQL的事务隔离级别 3、读已提交 - Oracle的事务隔离级别 4、序列化 -> 一个一个操作 ，效率最低-事务什么是开启？SQLPLUS客户端登陆成功后，默认开启事务什么时候提交？ 手动提交 commit; 自动提交 客户端正常退出exitcommit后，一定会重新开启一个新的事务-只读事务： set transaction read only;特点：只对当前用户当前窗口有效应用：统计某个时间点一共卖票的张数-* 小技巧：dual -> sys用户创建的动态的表。select lower('ABC') from dual;Oracle的函数max min ,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-1 14:00:20','yyyy-mm-dd hh24:mi:ss');-sysdate ->返回当前时间add_months(d,n) -> 在原有日期基础上加上n个月add_months(sysdate,3); ->3个月后的今天last_day(日期) ->返回日期所在月份最后一天计算日期：select sysdate - to_date('2014-3-1 14:00:00','yyyy-mm-dd hh24:mi:ss') from dual;-视图： 虚拟的表,只读的，保存的是一个查询的结果view_test;select * from emp where deptno=20;select * from view_test;-create view 视图名称 as 查询结果表一开始没有创建视图的权限：grant create view to test; -数据库的备份和恢复：备份的几种方式：1） create table emp_bak as select * from emp;2） 导出sql文件3） 使用工具exp 前提： 只要安装Oracle的客户端，就有这个工具exp 命令如何使用？ userid - 登录到数据库的用户名密码，网络服务名 test/abc tables -> 需要备份哪些表 file -> 客户端生成的文件放在哪个位置 rows=n -只导出表结构，不导出表的数据1）举例：备份emp表exp userid=test/abc tables=(emp,dept) file=d:test1.dumpimp导入工具，恢复empimp userid=test/abc tables=emp file=d:test1.dump rows=n;-了解：数据字典动态性能视图： 需要了解用户到底创建了哪些表？ alter user scott account unlock;select * from all_tables where owner='yang' -看yang用户创建的所有的表select * from user_all_tables; -查看所有的用户的表select * from v$session ; -查看有哪些终端连入oracleselect * from dba_sys_privs where grantee='RESOURCE' select * from dba_tab_privs where grantee='TEST'select * from dba_role_privs where grantee='TEST'什么是表空间？ 在存储里面 *.dbf文件,存放表和视图、索引-数据的完整性：Student: id name classNo(fk) 1 zhangsan 10 Class: cid cname 10 class10-使用约束，维护数据的完整性约束：限定不能做什么！Oracle的约束： 非空 not null： 字段输入必须有值 唯一 unique： 字段的值不能重复 主键 primary key： not null + unique 外键 foreign key: 引用另外一个地方的一个字段 checked ： 限定只能输入固定的值添加约束：1、属性级约束（在建表的外面添加约束）（建议使用，因为这样可以方便添加注解，增加代码的可读性） 第一种分类，列的属性not null, uniquealter table tb_balance modify(字段 类型 not null);create table tb_balance( acctid number, balance number(9,2); 第二种分类，必须是oracle中的一个对象primary, foreign keyalter table 表名 add constraint 约束名 约束类型约束名定义规则： pk_XXXX fk_XXXXalter table 表名 add constraint 约束名 primary key(字段);alter table tb_balance add constraint fk_bank foreign key(bank) references tb_bank(bankid);删除约束：alter table tb_balance drop constraint 约束名;-2、表级约束(写在表里面，缺点：不利于注解键的作用)：Create table tb1(C# varchar2(20),Name varchar2(20)Primary key(C#),Foreign Key(C#) references T(T#)-索引（index）： 为了提高查询效率。创建索引后搜索机制将会自动优先调用索引。Student id name 1 zhangsan 2 lisi .此处省略一万行 10001 zhangsan 10002 zhangsan查询名字是zhangsan的学生 select * from student where name='zhangsan'没有索引：共查询10003次。-索引：name字段建立索引zhangsan 1, 10001lisi 2wangwu 3,100.共查询两次！-问题： select * from student where name like 'zhang%' -模糊查询使用不到索引，全文的遍历提高大数据量访问的效率： 1、建立索引 2、全文检索 借助第三方工具sphinx ,coreseek 3、表分区按照用户id分区 1 10 - 分区1 11 20 -分区2 4、分库，分表-索引的问题： 如果在数据量激增的情况，后面推荐使用 重新建立索引 1、创建索引：create index 索引名 on 表名（字段）2、删除索引drop index 索引名;建立索引的原则： 1、在大表上建立索引才有意义 2、在where子句或是连接条件上经常引用的列上建立索引 3、索引的层次不要超过4层create index emp_idx1 on emp (ename,job);select * from时 emp where ENAME='XXXX' AND job= 'CLERK' -能使用到索引select * from emp where job= 'CLERK' and ENAME='XXXX' ->使用不到索引也是保存在表空间上的，占用空间一般是表大小的1.2倍-PL/SQL -> SQL编程函数，存储过程，触发器函数：count,sum,lower.统计函数、数学函数、用户自定义函数等，必须有且只有一个返回值存储过程： Oracle服务端的一个代码，节约带宽，可以没有、有一个或者多个返回值。触发器： 当你操作一个表的时候，可以触发一段代码。-存储过程： 1、两种形式：临时存储过程永久存储过程临时存储过程： 1、最简单的存储过程： update tb_balance set bank = 1;declare变量列表begin 要编写的代码end;/ ：表示立即执行edit - 编辑上一条命令-create porocedure 存储过程名字 is变量列表begin 要编写的代码 end;-如何调用存储过程 exec 存储过程名 （或） call 存储过程名-show errors; ->显示错误如何让存储过程在屏幕输出一个值？set serveroutput on ;-存储过程函数触发器块： begin - end;包： 集合，包含函数，触发器，和存储过程Pak test_prodexec Pak.test_prod-如何输出到屏幕dbms_ouput.put_line(字符串)-标识符号的命名规范定义变量，建议使用v_开头：定义常量，建议使用c_开头；定义游标，建议使用_cursor为后缀；定义异常时，建议使用e_作为前缀-练习：1、 查询员工的工资，并且打印出来declare-变量的定义v_sal number(9,2);begin select sal into v_sal from emp where empno='&empno'-可以和键盘交互 dbms_output.put_line(v_sal);end;/2、 查询员工的姓名和工资，并且打印出来 (一个select后面只能有一个into) SMITH的工资是800declare v_name varchar2(20); v_sal number(9,2);begin select ename,sal into v_name, v_sal from emp where empno=&empno; dbms_output.put_line(v_name|'的工资是'|v_sal);end;/* 注意：永久存储过程不能使用键盘交互-' ' -> 自动转换， 一般来说，写SQL一定要注意类型，但是如果将number类型写成字符类型，数据库有自动转换的功能，但其他的类型错误不行。t1(id number)insert into t1 values(1);insert into t1 values( '1' );-异常和错误？ 错误：程序代码无法控制的一部分，一旦产生程序将立即终止。异常：程序运行过程中可能会遇到的问题，程序可以捕捉并且处理= 异常处理。 如：1/0 -> 除数为0 select 查询，找不到记录！是异常declare变量列表begin /执行的代码 exception /异常处理的部分 when 异常名称 then begin /处理代码。 endend;-2、 查询员工的姓名和工资，并且打印出来 (一个select后面只能有一个into) SMITH的工资是800declare v_name varchar2(20); v_sal number(9,2);begin select ename,sal into v_name, v_sal from emp where empno=&empno; dbms_output.put_line(v_name|'的工资是'|v_sal); exception when no_data_found then begin dbms_output.put_line('记录未找到'); end;end;/-异常的分类： 1、Oracle预定义 2、自定义-if 条件 then.elsif 条件2 then.else .end if;-练习： 查询一个员工的薪资，如果太少(<1000)，则显示“机密”（必须用异常）1)异常的定义ex_sal_low exception;2）如何抛出异常declare v_sal number(9,2); ex_sal_low exception;begin select sal into v_sal from emp where empno = &empno; if v_sal < 1000 then raise ex_sal_low; end if; dbms_output.put_line(v_sal); exception when ex_sal_low then begin dbms_output.put_line('薪资太少，机密'); end;end;- 如何赋值？ 1、Select ? into 变量 from. /从表中查询到的数据赋值 2、:= 赋值号 /直接进行变量间的赋值或者常量赋值 练习： 两个数字 num1:10 ,num2:20 ，计算num1+num2，打印结果declare num1 number; num2 number; rs number;begin num1 := 10; num2 := 20; rs := num1 + num2; dbms_output.put_line(rs);end;-循环 for while练习： 使用for循环打印110declarebegin for i in reverse 1.10 loop dbms_output.put_line(i); end loop;end; 使用while循环打印110declare i number :=1;begin while i<=10 loop dbms_output.put_line(i); i := i+ 1; end loop;end; -存储过程和函数的区别？ 存储过程可以没有返回值，也可以有一个或者多个返回值 函数必须有而且只有一个返回值。通过练习，了解存储过程的传参：1、传入用户的id，得到工资返回结果（使用存储过程完成）create or replace procedure getSal(v_empno in number,v_sal out number)isbegin select sal into v_sal from emp where empno = v_empno; end;/上述存储过程的调用，必须在另外一个存储过程里面declare v_empno number := 7369; v_sal number;begin getSal(v_empno,v_sal); dbms_output.put_line(v_sal);end;/drop procedure -函数： 1、传入用户的名字，得到工资返回结果（使用函数完成）create or replace function 函数名(输入参数) return 返回类型 is变量列表begin执行代码 exception 异常处理部分end;/create or replace function getSal(v_ename varchar2) return numberisv_sal number;begin select sal into v_sal from emp where ename = v_ename; return v_sal; end;/-drop function 函数名drop procedure 存储过程名