Oracle数据库PL/SQL学习笔记（一）

Oracel数据库的基本管理：

<span style="font-size:18px;">create tablespace test datafile 'E:\app\Administrator\oradata\orcl\test.dbf'             --注意：名称test不要带引号size 100Mautoextend on next 2M maxsize 2048Mextent management local;  --默认是本地管理，本地管理表空间与字典管理（dictionary）表空间相比大大提高了管理效率和数据库性能 alter tablespace  test rename to test2;   --改变表空间的名称 create user wanli identified by 123456 default tablespace test    ACCOUNT UNLOCK;  --解锁用户ALTER USER username ACCOUNT UNLOCK; --解锁用户grant connect,resource to wanli;  --普通用户grant  DEBUG CONNECT SESSION to wanli;  --赋予用户断点测试的权限grant dba to wanli;  --DBA管理用户</span>

sql语句的分类：
DDL--数据定义语言
    create,alter,drop,rename,truncate,comment

  delete和truncate关键字的区别：
    TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：
           二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 
            速度快，且使用的系统和事务日志资源少。


DML--数据操作语言
     select,insert,update,delete和merge  

merge关键字：

     merge是insert和update的合并。连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
          这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。


DCL--数据控制语言
     grant和revoke     授权和撤销权限
   
TCL--事务控制语言
     commit,rollback和savepoint



pl/sql不区分大小写
但是通常会对命令单纯使用大写形式，而对变量、列名和存储过程调用使用小写形式。




pl/sql支持2中类型的程序：
1.匿名块程序，支持批脚本。
2.命名块程序，提供存储编程单元。



匿名程序块结构：必须有执行部分，最简单的是null
语法：
  declare
      声明部分
  begin
      执行部分
   [exception
        异常处理部分
    ]
  end；
声明部分：包含变量定义和声明，用户定义的pl/sql类型定义，游标定义、引用游标定义
和局部函数或过程的定义。

执行部分：包含变量赋值、对象初始化、条件结构、迭代结构、嵌套的pl/sql匿名块，或者
是对局部或存储pl/sql命名块的调用。

异常部分：包含错误处理短语，该短语可以像执行部分一样使用所有项。


变量
包含标量和复合变量
标量变量：只保存一个指。
复合变量：可保存多个值。


赋值：
Oracle赋值使用  :=

nvl()函数：
  nvl(my_var,false)   如果my_var为null，则设置my_var的值为false；


基本控制结构：
检查逻辑添加和控制分支程序执行，还有一个是迭代条件直到添加满足或被命令退出。
1.条件结构
  1》  if 条件1 then
      null;
   else
      null;
    end if;

  2》多条件判断
    if-then-elsif-then-else


2.case语句
   case搜索语句：
      case  true
           when 条件1 then 
                执行1；
           when 条件2 then
                执行2
           when 条件3 then
                执行3
           else
                默认执行
       end case；


3.迭代结构
（1）
for循环包括数值for循环和游标for循环。通过使用显示的continue或exit语句分别跳过迭代或强制从循环中提早退出
for循环隐式管理其开始和结束（没有loop和end loop），不支持引用游标（refcursor）,迭代引用游标只能使用显示循环结构，如简单循环和while循环。


数值for循环：  starting_number .. ending_number  开始值和结束值必须是整数，中间是两个".."
begin
   for i in 1..10 Loop
      dbms_output.put_line('The index value is ['||i||']');
   end loop;
end;


游标for循环：
 for i in (游标名|sql语句) loop
    执行语句;
 end loop;
如果用游标名调用，就是显示游标。如果用sql语句，就是使用隐式游标。
游标for循环中索引变量不是interger数字，它是游标返回的记录结构的引用。
可以通过点号将游标索引变量和列名组合。


--隐式游标for循环(i相当于一条记录，能直接通过 i.字段名获得对应的字段值)
begin
     for i in (select * from SCOTT.EMP t) loop
        dbms_output.put_line('The name is ['||i.ename||']');
     end loop;
end; 




（2）简单循环
   是显示结构，要求用户管理循环索引和退出条件。一般与局部定义的游标和引用游标（ref cursor）一起使用。
   游标的几个特性：%found,%notfound,%isopen和%rowcount
declare
  emp_name SCOTT.EMP.ENAME%type;--根据表的指定字段的数据类型定义变量
   cursor cur is select t.ename from SCOTT.EMP t;
begin
   open cur;
   loop
      fetch cur into emp_name;
          dbms_output.put_line('The name is ['||emp_name||']'); 
       exit when cur%notfound;
   end loop;
   close cur;
end; 


(3)while循环
 要先检查循环进入条件
declare 
   emp_name SCOTT.EMP.ENAME%type;
   cursor cur is select t.ename from SCOTT.EMP t;
begin
    open cur;
    while cur%isopen loop
        fetch cur into emp_name;
        if cur%notfound then
           close cur;   --关闭游标，不能通过while循环的条件判断，自然结束循环
        end if;
         dbms_output.put_line('The name is ['||emp_name||']'); 
    end loop;
end;


PL/SQL存储编程单元：
  函数，过程，包和触发器，存储对象类型
在Oracle中，
  函数、过程、包和对象都存在同一个命名空间中。
  
  触发器存储在另一个命名空间。

函数
基本结构：
create or replace function 函数名
  （参数名  数据类型）
    return 返回结果类型
declare
   变量定义
begin
   执行sql语句
  return 结果值；
exception
   异常处理
end；


因为函数返回的是sql数据类型，所以可以左右pl/sql赋值中的右操作数，也可以直接从sql语句中调用、
如：
  select greatest(12,34) from dual;  --在sql中调用greatest函数


declare
   var2 number(10);
begin 
   var2 := greatest(11,10,50,69);  --在
   dbms_output.put_line('The name is ['||var2||']'); 
end;


在命令窗口，可以使用call  函数  给会话级绑定变量赋值
SQL> variable var3 varchar2(30);
SQL>  call join_strings('hello','word') into :var3;
SQL>  select :var3 from dual;




--注意，所有的参数都要声明后再使用
create or replace function getName(userid number) return varchar2 is
  name_value varchar2(30);
  id number(10);
begin
  id := userid;
  select t.name  into name_value from tb_user t where t.userid = id;
  return(name_value);
end getName;


存储过程：
不能作为右操作数，也不能用于sql语句。
语法：
  create procedure 存储过程名
     [参数名   in|out类型   数据类型]  is
  begin
     执行sql语句
    [异常处理]
  end；

事务控制和异常捕捉：
begin
   savepoint new_member;--设置回滚点
   
    insert into tb_user t values(51,'wanli',22,'123');
    insert into tb_user t values(51,'wanli',22,'123');    
    dbms_output.put_line('both succeeded');
    
    commit;  --提交
    
    exception
       when others then   --when others 能够截取所有的异常，一般放在异常处理的最后。
          rollback to new_member;  --回滚到设置的回滚点
          dbms_output.put_line(Sqlerrm);--输出错误信息
end;


触发器：
正因为触发器实在某个时刻运行，因此不能在触发器中使用sql dcl语句：
 savepoint,rollback,或commit。


Oracle中的特殊字符：
:=            赋值
:变量名       指定标识符为会话级变量
&变量名       指定标识符为替换变量（可接受输入值）
=             比较运算符


日期格式的赋值：
字符串格式化为时间，最好使用to_date();
不支持隐式转化。即不能直接给  字符串格式的字符给    日期类型的变量。


relative_date := '01-jun-07';


date_1 := to_date('20140912','yyyy-mm-dd');


date_2 := cast('01-jun-07','mon-dd-yy') XXXXX  ----经验证，已不能使用。


extract()内置函数可以从date值中获取数值型年月日。


复合数据类型：
主要有两种复合数据类型：记录和集合。


记录，也称结构体，包含相关元素的集合。相当于java的bean，或者类。
集合，事物集，相当于java的集合。


定义记录的语法：
 type 记录名 is record
   (变量1   数据类型1，
    变量2   数据类型2，
  )
例：
declare
 type demo_record_type is record(
   id number default 1,
   value varchar2(10) := 'one'
 );
  demo demo_record_type;
begin
  dbms_output.put_line('['||demo.id||']['||demo.value||']');
end;  


集合：
  是数组和列表。
1.varray数据类型   --类似java中的数组

--自定义数组（varray）
declare
   type number_varray is varray(10) of number;   --类型number_varray是包含10个数字的number类型的varray数组
   list number_varray := number_varray(1,2,3,4,5,6,7,8,null,null);    --变量list的类型是number_varray，并初始化
begin
  for i in 1..list.limit loop            --limit返回最大尺寸
     dbms_output.put('['||list(i)||']');
  end loop;
  dbms_output.new_line;
end;

--嵌套表数据类型（索引列表或java类），list集合，  特点：有序，密集填充 ，大小不固定
declare
   type number_table is table of number;    --类型是table，包含的是number，不用声明长度    
   list number_table := number_table(1,90,3,4,5,6,7,8);
begin
    list.delete(2);    --删除第二个元素，但不删除已分配的空间
     dbms_output.put_line(list.count);
     dbms_output.put_line(list.limit);  --table没有limit属性，只有varray数组才有limit属性
    for i in 1..list.count loop     
       if list.exists(i) then
           dbms_output.put('['||list(i)||']');
       end if;
    end loop;
    dbms_output.new_line;  
end;




--联合数组数据类型   （类似set集合）               特点：无序 稀疏，大小不固定
--和嵌套表定义非常相似，主要不同点：指定了如何进行索引  index by Pls_Integer 或者 index by varchar2(10)
declare
  type number_table is table of number index by Pls_Integer;
  list number_table;
begin
   for i in 1..8 Loop
      dbms_output.put_line('The index value is ['||i||']');
     list(i):= i;
   end loop;
    dbms_output.put_line(list.count);
   list.delete(2);   --和varray和嵌套表中不同，删除元素时也会删除已分配的空间
    dbms_output.put_line(list.count);
    for i in 1..list.count loop      --由于count变小，所有最后一个值8查不到
      if list.exists(i) then       
       dbms_output.put('['||list(i)||']');
      end if;
    end loop;
    dbms_output.new_line;
end;



游标
类型：隐式游标和显示游标

显示游标：在声明块中定义的游标，都属于显示游标。
隐式游标：任何执行块或异常块中的DML语句都是隐式游标。（包括insert,update,delete）

--引用游标
--弱类型的引用游标没有返回类型，强类型的引用游标有返回类型
--主要作用：  实现在程序间传递结果集的功能，在同一过程中使用引用游标没有意义。
declare
 type weakly_typed is ref cursor;
 quick weakly_typed;
 v_a1 varchar2(30);
 v_b1 number(10);
begin
   open quick for
     select t.name,t.age from tb_user t;
/*    loop 
       fetch quick into v_a1,v_b1;
        dbms_output.put_line(v_a1||'今年'||v_b1||'岁');
       exit when quick%notfound;
    end loop;*/
end;