www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
OCP les_7-4 笔记-技术论坛||Oracle高手之路—Orasql.Com!
            OCP les_7-4 笔记
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2011-05-30 10:53:49
为什么要使用 pl/sql 便于维护(模块化) 提高数据安全性和完整性(通过程序操作数据) 提高性能(编译好的) 简化代码(反复调用) 块的结构 declare 声明 变量部分,可以没有 begin 逻辑执行部分,到end结束,必须有 exception 错误处理,可以没有 end ; 案例1:输出hello world 设置变量serveroutput 不指定默认值为不输出 ,就只指定可以在屏幕上输出显示,默认是不显示 set serveroutput on 查看show serveroutput begin dbms_output.put_line('hello world'); end; / 每句话以分号结束,最后加上 / -- 使用变量 变量用来存储数据 操作存储的数据 可以重复应用 使维护工作简单 语法: identified datatype [not null] [:= | default expr] 案例2使用变量,一定使用declare开头 declare v_1 date; v_2 number(2) not null :=10; v_3 varchar2(10) :='abc'; v_4 constraint number :=100; v_5 boolean not null :=true; not null一定要给初始值 constraint 一定要给值 a:= 为赋值 = 逻辑判断符号,判断是否相等 --- %type 取某 一个列的数据类型赋予给当前变量 声明一个变量和某列的数据类型相同 v_name scoot.emp.ename%type 注释 /* */ 多行注释 --行注释 给变量赋值 select into 从表中查询数据的结果赋予给变量,一次只能给以个,多个要做循环运算 pl/sql 块中操作数据库中的表.select into 将表中的数据放入到变量 案例:取表中的数据 declare v1 scott.emp.ename%type ; v2 scott.emp.sal%type ; begin select ename ,sal into v1,v2 from scott.emp where empno=7900; dbms_output.put_line(v1); dbms_output.put_line(v2); end; / 注意:一定要有 into 一次只能操作一行,操作多行得用循环 要求编写一个块,可以输出smith员工的员工编号和工资 set serveroutput on declare v1 emp.empno%type ; v2 emp.sal%type ; begin select empno ,sal into v1, v2 from emp where ename ='SMITH'; dbms_output.put_line(v1); dbms_output.put_line(v2); end ; declare 变量 begin 执行操作 end ; DML语句与SQL相同 案例 掌握在 pl/sql 块中使用 if 语句进行分支操作 if -then -end if if -then -else -end if if-then -elsif --enf if if 条件1 then 语句1 elsif 条件2 then 语句2 else 语句3 end if ; 分支就是树的结构,条件就是分支的选择,我们只能走到一个支干上,即使每个条件都符合,我们也只 能操作一个支干的语句。 案例 declare v1 date:=to_date('2011-1-1','yyyy-mm-dd'); begin if months_between (sysdate,v1) >5 then dbms_output.put_line('true'); else dbms_output.put_line('false'); end if ; end ; / if 条件1 then 语句1 elsif 条件2 then 语句2 else 都不满足则执行3 end if 触发器 trigger 机关 如何查看触发器 : user_triggers 触发器类型: 行级触发器 for each row 影响的每一行都会执行触发器 语句级触发器 默认,一句话才执行一次触发器 何时触发 dml 语句 before 在条件运行前执行触发器 after 在条件运行后执行触发器 instead of 替代触发,作用在视图 实验用表 create table d as select * from dept; create table e as select * from emp; 案例1 创建触发器 create or replace trigger t_update after delete or update of deptno on d for each row begin if (updating and :old.deptno != :new.deptno) then update e set deptno=:new.deptno where deptno =:old.deptno; elsif deleting then delete from e where deptno =:old.deptno ; end if ; end ; / 解释 :old 表示原来的数据 --验证触发器 select trigger_name ,status from user_triggers; --修改触发器状态 --禁用某个触发器 alter trigger t_update disable; 启用 alter trigger t_update enable; --禁用表上的所有触发器 alter table d disalbe all triggers; alter table d enable all triggers; --删除触发器 drop trigger t_update ; 验证触发器的功能 update d set deptno=50 where deptno=30; delete from d where deptno=50; 案例2 禁止修改e表的sal列 create or replace trigger e_update before update of sal on e begin if updating then rais_application_error(20001,'no'); end if ; end; / ???rollback 案例3 什么是 :old :new 保存老值和新的值 CONNECT SCOTT/TIGER DROP TABLE T1; CREATE TABLE T1 AS SELECT SAL OLD_VALUE,SAL NEW_VALUE FROM EMP WHERE 0=9; CREATE OR REPLACE TRIGGER TRG1 BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW BEGIN INSERT INTO T1 VALUES(:OLD.SAL,:NEW.SAL); END; / SELECT * FROM T1; update emp set sal=sal+1; commit; select * from t1; 案例4 建立一个登录的审计触发器 create table login_table (user_id varchar2(15), log_date date, action varchar2(15) ); --on schema只记录当前用户行为 CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO login_table(user_id, log_date, action) VALUES (USER, SYSDATE, 'Logging on'); END; / create or replace trigger logoff_trig before logoff on schema begin insert into login_table (user_id ,log_date,action) values(USER,SYSDATE,'Loging off'); end ; / ---函数:就是可以带返回值的命名块 函数是有名称的 pl/sql 块 函数有返回值 在表达式中调用函数 存储在服务器端 in 输入变量 out 输出变量 in out 输入输出变量 --根据用户输入的员工号,查出该员工的工资 create or replace function get_sal (v_id emp.empno%type) return number as v_salary emp.sal%type :=0; begin select sal into v_salary from emp where empno=v_id; return (v_salary); end ; / 验证对象 select object_name ,object_type from user_objects 查看原程序 select text from user_source where name ='GET_SAL'; 调用函数 select get_sal(7839) from dual; 删除函数 drop function get_sal; --- 编写存储过程 存储过程 存储在服务器端 编译好的 可以在程序中调用 完成一定功能 可以没有返回值,也可以有多个返回值 编写导入型的存储过程(in) 案例:指定员工编号加工资 create or replace procedure p1 (v_id in emp.empno%type) as begin update e set sal=sal+1 where empno=v_id ; commit; end p1; / 验证源程序 select text from user_source where name='P1' select object_name ,object_type,status from user_objects where object_type='PROCEDURE' 执行存储过程 execute p1(7839) 或者 begin p1(7900); p1(7902); end ; / ----编写包 package 将功能相近的函数或存储过程组织在一起 便于管理 包内的函数可以重名,提高程序的通用性 减少对象的名称占用问题 一个包内函数使用,整个包都调入内存 包内一个程序失效,整个包重新编译 由包头和包体组成 包头 不能加密 描述了包内的函数,存储过程的参数 可以独立存在 包体 可以加密 函数的实现 不能独立存在 建立包头 create or replace package mypk as /is function fsal(no number) return number; procedure psal(v_no number); end ; / 建立包体 create or replace package body mypk as function fsal(no number) return number is v_salary number :=0; begin select sal into v_salary from emp where empno=no; return v_salary; end ; procedure psal (v_no in number) as begin update emp set sal=sal+10 where empno=v_no ; commit; end ; end mypk; ;

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|