Oracle全能资料.doc

一. 使用PL/SQL 1. 属性类型: 1.列类型: %TYPE varable tableName.columnName%TYPE; 2.行类型: %ROWTYPE varable tableName%ROWTYPE; 3.使用行类型: varable.列名;2. 控制结构: 1.条件控制结构: a) if condition then exp_body end if; b) if condition then exp_body else exp_body end if; c) if condition then exp_body elsif condition then exp_body else exp_body end if;3. case 语句: case 同 select 时 when 后面不能加; 1. sql中:a) select 别名=case when 条件 then 值 when 条件 then 值 else 值 end from 表 b) select case 列名 when 值 then 值 when 值 then 值 else 值 end from 表 2. oracle中: a) select case 列名 when 值 then 值 when 值 then 值 else 值 end case from 表明 b) select case when 列名=值 then 值 when 列名=值 then 值 else 值 end case from 表4. 循环: 1) Loop condition End loop 2) While condition Loop exp_body End loop 3) For varable in REVERSE value1.value2 ( varable 变量不许声明 , . 是范围连接符 ) Loop exp_body End Loop5. 退出条件: exit; exit when condition ;/ 用来结束循环6. 顺序控制: 必须要指定条到那里去 , 将无条件地将控制权转到标签指定的语句, 标签是用双括号括起来的标识符且必须是唯一的名称(只能从 if , case , loop 语句块中跳出来) if 12 then goto quit ; else goto updation; end if; -标签 exit;-标签指定的语句 -标签 select * from dual; -标签指定的语句7. 动态SQL : 只能用于处理返回单行或没有返回的SQL 语句, 要处理返回多行的动态SQL请使用ref游标 的 open . for 语句 (需要放在块中执行) sql:= select * from dept where deptno = :deptno; / :deptno 代表一个占位符 Execute Immediate sql into 行类型变量 using 参数值8. 异常处理: 1)自定义异常: a)定义异常:(位 declare和 begin之间) DECLARE varable EXCEPTION; b)显示引发异常(为 begin 和 end 之间): RAISE varable; 2)处理预定义异常:(位 begin 和 end 之间) EXCEPTION when Too_Many_ROWS then DBMS_OUTPUT.PUT_LINE(返回多行); when others then DBMS_OUTPUT.PUT_LINE(全部处理); 3) 引发应用程序错误: RAISE_APPLICATION_ERROR(error_number, error_message);二.游标: 1. 游标的特点: 1)一次只能访问一条记录 2)不能通过游标来直接访问数据(间操性) 3)必须先把游标中的值保存到变量中去再来间接使用变量中(间操性) 2.隐式游标通过SQL 来访问它的属性 3. 属性: %FOUND - 只有DML语句影响一行或多行时返回 true %NOTFOUND - 与 FOUND 相反 %RowCount - 返回DML语句影响的行数 %ISOPEN - 来判断游标是否打开 4. 将列或行中的数据保存到 列类型或 行类型中去:(有且必须返回一行记录) select 列名/ * into 列类型变量/ 行类型变量 from 表明 5.显示游标和隐式游标的区别: a)显示游标需要进行声明, 打开, 提取, 关闭. 隐式游标不需要! b)显示游标一次可以返回多条记录集, 隐式有且只能返回一条 6.显示游标的使用的步筹: 1) 声明游标(Declare) 2) 打开游标(Begin) 3) 提取游标(Fetch) 4) 关闭游标(Close) 7. ref 游标使用的步寿: 1. 声明ref cursor 类型 2. 声明ref cursor 类型所定义的游标 8. 用于处理单行结果的游标- 隐式游标 9. 显示和隐式游标的最大区别是: %ISOPEN10. 显示游标在哪个部分中声明- Declare11. 隐式游标: PL/SQL 为所有SQL数据操纵语句(包括:返回的select) 隐式声明游标12. 访问隐式游标的属性SQL%ATTRIBUTETYPE二.显示游标:(行集多行或零行) 1).声明游标 - 2).打开游标 - 3).从游标中获取记录 - 4).关闭游标 a) 声明游标: set serveroutput on declare cursor cursor_Name is select * from Table_Name; /声明. row Table_Name%ROWTYPE; /定义行类型 begin open cursor_Name; loop fetch cursor_Name into row; /提取数据 exit when cursor_Name%NotFound; exp_body end loop; close cursor_Name; / 关闭游标 end;三.循环游标(无需打开和关闭) set serveroutput on declare cursor cursor_Name is select * from Table_Name; begin for cur in cursor_Name loop if cur.columnName =值 then exp_body end if; end loop; end; 四.ref 游标: (没有for 循环游标的用法) a) 声明 ref cursor 类型 Type ref_cursor_Name is ref cursor; b) 声明 ref cursor 类型的游标变量 cur ref_cursor_Name; c)示例: set serveroutput on declare Type ref_cursor_Name is ref cursor; 声明 ref cursor 类型 cur ref_cursor_Name; 声明 ref cursor 类型的游标变量 row Table_Name%rowType; 声明行类型 begin open cur for select * from Table_Name where column_Name condition ; loopfetch cur into row; exit when cur%NotFound; if row%columnName = 值 then exp_body end if; end loop; end;五. 使用显示游标删除或更新: cursor cursor_Name is select * from Table_Name for update of column_Name /*在执行Delete和Update 时使用 where cursor of 子句指定游标的当前行, 在声明游标时, update 命令中使用的列也必须出现for update of 子句中, select 语句必须只包括一个表， 而且delete 和 update 语句只有在打开 游标并提取特定行之后才能使用； */ Update Table_Name set column_Name = 值 where cursor of cursor_Name 示例: set serverout on declare new_price number; cursor cur is select toyprice from toys where toyprice :old.columnName (来访问表中的值) insert - :new.columnName (来访问表中的值) :old.columnName (来访问原表中的值) update - :new.columnName (来访问新表中的值) 否则会报错- new 和 old 引用不允许在表层触发器 中错误 5.区别: after触发器, before触发器, instead of触发器的异同:名称触发器执行是在表数据插入之前还是之后触发器类型原理before触发器之前1.行级2.语句Insert数据先复制New-再复制表(New中数据可以更改再保存)after触发器之后1.行级2.语句先保存到表中-再执行触发器Insert 数据-先复制表-再复制-New(New中数据不能更改)instead of 触发器之前只能是行级别(默认为行级别触发器)只适合视图必须手动去更新,删除,插入触发器表中的数据(该表是键保留表) 6.触发器的分类: 1)before触发器: 1.行级: 语法: create or replace trigger 触发器名称 before insert or update of 列名 on 表明 for each row - 是行级触发器的标志 when ( condition ) begin end 特点: 每一行触发一次且执行一次 2.语句: 语法: create or replace trigger 触发器名称 before insert or update of 列名 on 表明 when ( condition ) begin end 特点: 只执行一次 2) after触发器:1.行级: 语法: create or replace trigger 触发器名称 after insert or update of 列名 on 表明 for each row - 是行级触发器的标志 when ( condition ) begin end 特点: 每一行触发一次且执行一次 2.语句: 语法: create or replace trigger 触发器名称 after insert or update of 列名 on 表明 when ( condition ) begin end 特点: 只执行一次 3)instead of 触发器(只能是行级触发器, 只能是针对视图): 行级(默认就是): 语法: create or replace trigger 触发器名 instead of instead or update or delete on 表明 referencing new as n -为new 表指定别名 for each row declare begin end 特点: 每一行触发一次且执行一次 1.只有视图才有该类型触发器且只能是行级的, 2.必须手动对触发器表中进行insert ,update ,delete 3.该类型触发器默认为性级触发器 4.视图表必须是键保留表(键保留表: 视图包含了一个表的主键,该主键也是视图的主键,这个表叫做键保留表) 5.没有语句触发器 6.视图只能更改一个表(且必须是键保留表) 4)模式触发器: 触发器事件: Create ,alter, drop , grant , revoke , truncate 等DDL语句 当在表,视图,过程,函数,索引, 程序包,序列,同义词等模式对象 执行上操作 语法: create or replace trigger 触发器名 before|after drop on schema when (trigger_conditon ) . 5)数据库级触发器 触发事件: 当数据库启动,关闭,服务器错误,登陆和注销 语法:create or replace trigger 触发器名 after|before startup o database begin . end 删除触发器: drop trigger 触发器名 启动触发器: alter trigger 触发器名 enable 禁用触发器: alter trigger 触发器名 disable 触发器中可以执行事务回滚: rollback 系统程序包: 输出一行后不自动换行: DBMS_OUTPUT.PUT(文本) 输出一行后自动换行: DBMS_OUTPUT.PUT_LINE(文本) 换一行: DBMS_OUTPUT.NEW_LINE 产生随机数: DBMS_RANDOM 触发器 - user_triggers 过