Oracle PL/SQL 基础

维护数据的完整性

数据库的完整性用于确保数据库数据遵从一定的商业逻辑在Oracle中。数据完整性可以使用约束，触发器，应用程序（过程，函数）三种来实现。


一 约束：
约束用于却奥数据库满足特定的商业规则，在oracle中，约束包括not null, unique,primary key, foreign key 和 check 五种
check约束

create table user (id number primary key , sal number(7,2) check(sal>=1000 and sal<=2000), sex char(2) check(sex in('男','女')))


二 索引：
单列索引是基于单个列所创建的索引
create index  index_name on table_name (columnname)
复合索引
create index  index_name on table_name (columnname,columnname2)


三 pl/sql
pl/sql 是Oracle 在标准sql上的扩展，pl/sql不仅可以嵌入sql语言还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。这样使他们的功能变得更加强大。

学习的必要性：
①提高应用程序的运行性能
②模块化的设计思想[分页的过程，订单的过程，转账的过程]
③减少网络传输量
④提高安全性
缺点：移植性不好

--编写一个简单的存储过程
create procedure aa is

begin
  insert into emp (empno, ename) values (333, '11111');
end;

在命令行中调用该存储过程exec aa
程序中调用call aa();
--编写一个带参数的存储过程将刚才插入的数据删除
CREATE OR REPLACE PROCEDURE bb(in_empno NUMBER) IS
BEGIN
    DELETE FROM emp WHERE empno=in_empno;
  END;

CALL bb(333)

--编写pl/sql块
块结构示意图
pl/sql块的结构有三部分，定义部分，执行部分，例外处理部分，如下所示：
     declare
     /--定义部分 通常定义 常量，变量，游标，例外，复杂数据类型-/
     begin
      /-执行部分，要执行plsql语句和sql语句-/
     exception
     /-例外处理部分--处理运行的各种错误-/
    end;

--开发一个只包括执行部分的hello world
--dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程 put_line就是dbms_output包的一个过程。
BEGIN
  dbms_output.put_line('Hello World');
  END;

在命令行执行之前 需要set serveroutput on;

--------------------------------------------------------------
--根据用户输入的雇员编号，显示该雇员的名字
--&表示从控制台接收输入的变量
--\\表示把两个串拼接
DECLARE
v_ename VARCHAR2(32);
BEGIN
        SELECT ename INTO v_ename FROM emp WHERE empno=&empno;
        dbms_output.put_line('查询出的雇员名称为：'||v_ename);  
 END;

 --处理一个带异常的块
  DECLARE
   v_ename VARCHAR2(32);
  BEGIN
     SELECT ename INTO v_ename FROM emp WHERE empno=&请输入员工编号;
     dbms_output.put_line('雇员的名称为'||v_ename);
     
     EXCEPTION
       
     WHEN no_data_found THEN
       dbms_output.put_line('对不起，未找到该员工');
    END;


-----------------------------------------------------------------------------
创建带输入输出参数的存储过程
过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in）,也可以指定输出参数（out）.
通过在过程中指定输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境
不写 默认为输入参数。
--编写一个过程 输入员工姓名 和工资来修改员工的工资
CREATE OR REPLACE PROCEDURE proc1(in_ename IN VARCHAR2,in_newsal IN NUMBER)
IS
BEGIN
       UPDATE emp_bak SET emp_bak.sal=in_newsal WHERE emp_bak.ename=in_ename;
  END;


--函数的概念
函数用于返回特定的数据，当建立函数时，在函数的头部必须包含return 子句，而在函数体类，必须包含return 语句返回的数据
可以使用create function 来建立函数
---------------------------------------
create function 函数名(参数1)
return 数据类型 is
定义变量
begin
 //执行部分    
end;

在sqlplus 中调用函数
var 变量名 变量类型
call 函数名(参数值...)into 变量名；
print 变量名;

在java程序中调用该函数
select 函数名(参数) from dual;
通过rst.getObject(1);获得该值

--包的概念
为什么需要包：
使用包可以更好的管理自己写的函数和过程
包的基本语法
先声明包
CREATE  OR REPLACE  PACKAGE sp_package IS
        
        PROCEDURE update_sal(in_ename VARCHAR2 ,new_sal NUMBER);
        FUNCTION annual_sal(in_ename VARCHAR2) RETURN NUMBER;
        
        END;
再声明包体
 CREATE PACKAGE  BODY sp_package
 IS
       PROCEDURE update_sal(in_ename IN VARCHAR2,new_sal IN NUMBER)
         IS
         BEGIN
           UPDATE emp SET emp.sal=new_sal WHERE emp.ename=in_ename;
           END;       
              
       FUNCTION annual_sal(in_ename VARCHAR2)
         RETURN NUMBER
         IS
         v_annual NUMBER ;
         BEGIN
           SELECT (sal+NVL(comm,0))*13 INTO v_annual FROM emp WHERE emp.ename=in_ename;
           RETURN v_annual;
         END;
 END;
     
--执行命令exec sp_package.update_sal('JAMES',2000);   
 
=========================================================================================
pl/sql数据类型
在编写pl/sql时可以定义变量和常量
①标量类型(scalar)

标量定义案例：
定义一个变长字符串： v_ename varchar2(10)
定义小数：val_sal number(6,2)
定义小数并赋予初始值：var_sal2 number(6,2):=5.4
定义日期：v_hiredate date;
定义布尔变量 v_valid boolean:=false

------------------------------案例-------------------------------------------------------
 CREATE OR REPLACE PROCEDURE proc1(in_empno IN NUMBER)
 IS
 --定义变量
 v_tax_rate number(3,2):=0.03;--税率
 v_sal emp.sal%type;--个人工资
 v_ename emp.ename%type; --员工姓名
 v_tax NUMBER;--个人所得税
 BEGIN
             SELECT ename,sal INTO v_ename,v_sal  FROM emp WHERE empno=in_empno;
             --计算个人所得税
             v_tax:=v_tax_rate*v_sal;
             dbms_output.put_line('员工'||v_ename||'的工资为'||v_sal||',个人所得税为'||v_tax);
   END;
 --------------------------------------------------------------------------------------------  


②复合类型(composite)
  pl/sql记录
   
   ----------------------用pl/sql记录实现
   CREATE OR  REPLACE PROCEDURE proc1(in_empno IN NUMBER)
   IS
   TYPE v_emp_record IS RECORD(
        v_ename emp.ename%TYPE,
        v_sal emp.sal%TYPE,
        v_job emp.job%TYPE
   );
   emp_record v_emp_record;
   BEGIN
        SELECT ename,sal,job INTO emp_record  FROM emp WHERE empno=in_empno;
        dbms_output.put_line('雇员名称为'||emp_record.v_ename||',工资为'||emp_record.v_sal||',职位为'||emp_record.v_job);
        
     END;
     
基本语法
pl/sql表（了解即可）
相当于高级语言中的数组，但是下标可以为负数。没有限制


③参照类型(reference)
※ 参照变量是指用于存放数值指针的变量.通过使用参照变量，可以使得应用程序共享相同对象，从而降低应用空间。
在编写pl/sql程序时可以使用游标变量和对象变量两种参照变量
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
参照变量ref-cursor的使用：

定义游标
type 自定义游标名称 is ref cursor;
变量名 自定义游标名

打开游标：
open 游标变量 of select 语句


取出当前游标指向的行：
fetch 游标变量 into 其他变量

判断游标是否指向记录最后
游标变量%notfound

///
--使用pl/sql编写一个过程，可以输入部门号，并显示当前部门所有员工姓名以及他的工资
CREATE OR REPLACE PROCEDURE proc1(v_in_deptno IN NUMBER)
IS
--定义游标变量类型
TYPE type_emp_cursor IS REF CURSOR;
--定义游标变量
v_emp_cursor type_emp_cursor;
--定义员工姓名和工资变量
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
  --打开游标
  OPEN v_emp_cursor FOR SELECT ename ,sal FROM emp WHERE deptno=v_in_deptno;
  --取出游标指向的每行数据用于循环
  LOOP
   
    --将游标指向的值填充到变量中,并向下移动
    FETCH v_emp_cursor INTO v_ename,v_sal;
       --当游标执行完后退出循环
    EXIT WHEN v_emp_cursor%NOTFOUND;
    dbms_output.put_line('员工'||v_ename||',工资为'||v_sal);
 
    END LOOP;
    --关闭游标
    CLOSE v_emp_cursor;
  END;

//
--在上面功能上增加 如果员工的工资小于1000则增加100；

CREATE OR REPLACE PROCEDURE PROC1(IN_DEPTNO IN NUMBER) IS
  TYPE TYPE_EMP_CURSOR IS REF CURSOR;
  V_EMP_CURSOR TYPE_EMP_CURSOR;
  V_ENAME      EMP.ENAME%TYPE;
  V_EMPNO      EMP.EMPNO%TYPE;
  V_SAL        EMP.SAL%TYPE;
BEGIN
  OPEN V_EMP_CURSOR FOR
    SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = IN_DEPTNO;
  LOOP
    FETCH V_EMP_CURSOR
      INTO V_EMPNO, V_ENAME, V_SAL;
    EXIT WHEN V_EMP_CURSOR%NOTFOUND;
    IF V_SAL <= 1000 THEN
      UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = V_EMPNO;
    END IF;
  END LOOP;
  CLOSE V_EMP_CURSOR;
END;

④lob(large object)

/

//循环控制语言
案例：用户输入用户名和个数n，从1 开始添加，如果添加到n个则退出循环
CREATE OR REPLACE PROCEDURE PROC1(IN_ENAME IN VARCHAR2, N IN NUMBER) IS
  V_EMPNO EMP.EMPNO%TYPE := 1;//声明变量默认值为1
BEGIN
  LOOP
    INSERT INTO EMP (EMPNO, ENAME) VALUES (V_EMPNO, IN_ENAME);
    EXIT WHEN V_EMPNO = N;
    V_EMPNO := V_EMPNO + 1;
  END LOOP;
END;
///
使用while循环

CREATE OR REPLACE PROCEDURE PROC1(IN_ENAME IN VARCHAR2, N IN NUMBER) IS
  V_EMPNO EMP.EMPNO%TYPE := 1;
BEGIN
 WHILE V_EMPNO <= N LOOP
    INSERT INTO EMP (EMPNO, ENAME) VALUES (V_EMPNO, IN_ENAME);
    V_EMPNO := V_EMPNO + 1;
  END LOOP;
END;
///
for循环
for i in reverse 1..10 loop
end loop;不推荐使用for循环

--编写一个过程，可以输入员工编号返回员工姓名
CREATE OR REPLACE PROCEDURE proc1(in_empno IN NUMBER ,out_ename OUT VARCHAR2)
IS BEGIN
   SELECT ename INTO out_ename FROM emp WHERE empno=in_empno;
END;
///
------------------------------------Oracle分页存储过程---------------------------------
CREATE OR REPLACE PACKAGE pack1 IS

TYPE my_cursor IS REF CURSOR ;END;

CREATE OR REPLACE PROCEDURE PAGEING(IN_TABLENAME IN VARCHAR2,
                                    IN_PAGESIZE  IN NUMBER,
                                    IN_PAGENOW   IN NUMBER,
                                    OUT_RESULT   OUT PACK1.MY_CURSOR) IS
  V_SQL   VARCHAR2(2000);
  V_START NUMBER;
  V_END   NUMBER;

BEGIN
  V_START := IN_PAGESIZE * (IN_PAGENOW - 1) + 1;
  V_END   := IN_PAGESIZE * IN_PAGENOW;
  V_SQL   := 'select t2.* from(select t1.*,rownum rn from (select * from ' ||
             IN_TABLENAME || ')t1 where rownum<=' || V_END ||
             ')t2 where rn>='||V_START;
 OPEN OUT_RESULT FOR V_SQL;
END;

CREATE OR REPLACE PACKAGE mypackage
IS TYPE my_cursor IS REF CURSOR; END;
CREATE OR REPLACE PROCEDURE PAGEING(IN_TABLENAME  IN VARCHAR2,
                                    IN_PAGESIZE   IN NUMBER,
                                    IN_PAGENOW    IN NUMBER,
                                    OUT_RESULT    OUT MYPACKAGE.MY_CURSOR,
                                    OUT_ROWSCOUNT OUT NUMBER,
                                    OUT_PAGECOUNT OUT NUMBER) IS
  V_SQL   VARCHAR2(2000);
  V_START NUMBER;
  V_END   NUMBER;
BEGIN
  V_START := IN_PAGESIZE * (IN_PAGENOW - 1) + 1;
  V_END   := IN_PAGESIZE * IN_PAGENOW;
  V_SQL   := 'SELECT T2.* FROM (SELECT T1.*, ROWNUM RN FROM (SELECT * FROM ' ||
             IN_TABLENAME || ')T1 WHERE ROWNUM <=' || V_END ||')T2 WHERE RN >=' || V_START;
  OPEN OUT_RESULT FOR V_SQL;
  SELECT COUNT(*) INTO OUT_ROWSCOUNT FROM EMP;
  IF MOD(OUT_ROWSCOUNT, IN_PAGESIZE) = 0 THEN
    OUT_PAGECOUNT := OUT_ROWSCOUNT / IN_PAGESIZE;
  ELSE
    OUT_PAGECOUNT := OUT_ROWSCOUNT / IN_PAGESIZE + 1;
  END IF;
END;
//
Oracle视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图中带有一系列名称和行的数据，但是视图并不在数据库中
以存储的数据值集形式存在。行和列数据来自由定义视图的查询与偶引用的表，并且在引用时动态的生成

语法：create view 视图名称 as select ...from emp [with read only]

CREATE  OR REPLACE VIEW empview AS SELECT empno,ename FROM emp WITH READ ONLY;

视图与表的区别：
表需要占用磁盘空间，视图不需要
视图不能添加索引
使用视图可以简化复杂查询
视图有利于提高安全性
//
Oracle触发器
很多关系型数据库中都会提供一种技术，可以再用户进行某种操作的时候，自动的进行另一操作。这种技术叫做触发器技术

触发器是指存放到数据库中，被隐含执行的存储过程，可以支持dml触发器，还基于支持系统事件（启动数据库，关闭数据库，登陆）
和ddl操作建立触发器
触发器的分类：
dml触发器
ddl触发器
系统触发器（与系统相关的触发器，比如登陆，退出，关闭打开数据库）。

--当向emp表中添加一条数据时显示一句话：增加了一条数据
--当向emp表中添加一条数据时显示一句话：增加了一条数据
CREATE OR REPLACE TRIGGER tg1
AFTER INSERT ON scott.emp
[FOR EACH ROW]--行级触发器
BEGIN
      dbms_output.put_line('增加了一条数据');
  END;

/
 
  --禁止员工在休息日修改表的数据
  CREATE OR REPLACE TRIGGER TG1
    BEFORE INSERT OR UPDATE OR DELETE ON EMP
  BEGIN
    IF TO_CHAR(SYSDATE, 'day') IN ('星期六', '星期日') THEN
      RAISE_APPLICATION_ERROR(-20001, '不能在休息日操作数据');
    END IF;
  END;
/
使用条件谓词
CREATE OR REPLACE TRIGGER TG1
  BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
  CASE
    WHEN INSERTING THEN
      RAISE_APPLICATION_ERROR(-20001, '请勿添加数据');
    WHEN DELETING THEN
      RAISE_APPLICATION_ERROR(-20002, '请勿删除数据');
    WHEN UPDATING THEN
      RAISE_APPLICATION_ERROR(-20003, '请勿修改数据');
  END CASE;
END;

使用:old或：new

CREATE OR REPLACE TRIGGER TG1
  BEFORE UPDATE ON SCOTT.EMP
  FOR EACH ROW
BEGIN
  IF :NEW.SAL<:OLD.SAL THEN
    RAISE_APPLICATION_ERROR(-20005, '不能低于之前的工资');
  ELSE
    DBMS_OUTPUT.PUT_LINE('原来的工资为' || :OLD.SAL || ',新工资为' || ：NEW.SAL);
  END IF;
END;  
///
 CREATE OR REPLACE TRIGGER T4
  BEFORE DELETE ON
  SCOTT.EMP
  FOR EACH ROW
    BEGIN
           INSERT INTO emp_bak(ID,NAME) VALUES(:old.empno,:old.ename);
      END;

//
--编写触发器，修改员工的工资。控制员工的新工资不能低于旧工资，也不能高于旧工资的20%
CREATE  OR REPLACE TRIGGER T1
BEFORE UPDATE ON SCOTT.EMP
FOR EACH ROW
  BEGIN
         IF(:NEW.SAL<:OLD.SAL OR :NEW.SAL*1.2>:OLD.SAL)THEN
               RAISE_APPLICATION_ERROR(-20002,'工资修改错误');
         END IF;
    END;
//
CREATE OR REPLACE TRIGGER T1
BEFORE INSERT ON
SCOTT.EMP
FOR EACH ROW
    BEGIN
         IF(:NEW.SAL<0)THEN
               RAISE_APPLICATION_ERROR(-20002,'添加的工资不能小于0');
         END IF;  
     END;
//
    --创建触发器阻止小于18岁的用户添加进去
     CREATE OR REPLACE TRIGGER T1
     BEFORE INSERT ON SCOTT.EMP
     FOR EACH ROW
       BEGIN
              IF  add_months(:new.birthday,18*12)>SYSDATE THEN
                  RAISE_APPLICATION_ERROR(-20002,'对不起，您还未满十八岁');
         END;
/
数据库的备份和导入导出
1.导出自己的表
 exp userid=scott/tiger@orcl tables=(emp,dept) file=d:\e1.dmp
-------------------------------------------------------------------------------------------
C:\Users\Administrator>exp userid=scott/tiger@orcl tables=(emp,dept) file=f:/scott.dmp;

Export: Release 11.1.0.6.0 - Production on 星期六 2月 2 21:22:31 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                             EMP导出了          14 行
. . 正在导出表                            DEPT导出了           4 行
成功终止导出, 没有出现警告。

导出其他用户的表
如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限，比如system用户就可以导出任何方案
 exp userid=system/manager@myorcl owner=(system,scott) file=d:\system.dmp
/
导出数据库
 导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限
 增量备份（好处是第一次备份后，第二次备份就快很多了）
 exp userid=system/manager@myorcl full=y inctype=complete file=d:\all.dmp

 导出表的结构
 exp userid=scott/tiger@accp tables=(emp) file=d:\e3.dmp  rows=n
 使用直接导出方式
 exp userid=scott/tiger@accp tables=(emp) file=d:\e4.dmp  direct=y
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。
 这时需要数据库的字符集要与客户端字符集完全一致，否则会报错...
///
 n导入表
 1. 导入自己的表
 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp
2. 导入表到其它用户
 要求该用户具有dba的权限，或是imp_full_database
imp userid=system/tiger@myorcl tables=(emp) file=d:\xx.dmp touser=scott
3. 导入表的结构
 只导入表的结构而不导入数据
 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp  rows=n
4. 导入数据
 如果对象（如比表）已经存在可以只导入表的数据
 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp  ignore=y
n导入方案
 导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba的权限，或者imp_full_database
1．导入自身的方案
 imp userid=scott/tiger file=d:\xxx.dmp
2．导入其它方案
 要求该用户具有dba的权限
 imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott
n导入数据库
 在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：
 imp userid=system/manager full=y file=d:\xxx.dmp
//