PL/SQL 数据库访问的相关技术（1）

PL/SQL


1.数据库访问的相关技术
 1)plsql:      procedural 过程化的sql 第三代的过程化的编程语言
 2)proc/c++:   使用c或c++访问oracle数据库的技术
 3)odbc/ado:   vc中访问数据库的技术
 4)oci:        oracle提供的底层的接口
 5)sql j/jdbc: java连接数据库的技术
2.PL/SQL
 1)概念
 pl/sql(procedural language/sql) 是对标准sql的扩充
 2)特点
 模块化结构化的编程
 良好的可移植性
 良好的可维护性
 提升系统性能


 不便于向异构数据库移植


3.sql语句的特点
 第四代编程语言   机器语言 汇编语言  高级语言
 只管做什么  不管怎么做
 没有过程和流程控制
 没有算法的描述能力


4.plsql扩展的内容
 1)变量和类型
 2)控制语句
 3)过程和函数
 4)对象类型
5.plsql程序的结构
 declare
   /* 声明区  声明变量 定义类型*/
   /* 多行注释 */
   -- 单行注释
 begin
   /* 执行区  执行sql和plsql语句 */
 exception
   /* 异常处理区 */
 end;
 /
6.工具
 sqlplus:命令提示符下的工具
 plsqldeveloper:可视化的工具
  
 begin
   dbms_output.put_line('Hello plsql!');
 end;
 /
  
 /* 打开输出功能 */
 set serveroutput on


7.标识符
 1)作用：给变量、类型、过程、函数等命名的
 2)案例:
 变量名 数据类型;
 变量名:=值;   :=  赋值运算符


 declare
   var_id number;
   -- var_id number:=1;
 begin
   var_id:=1;
   dbms_output.put_line(var_id);
 end;
 /
 


8.变量和数据类型
 8.1 数据类型
  1）标量类型
     number     binary_integer  数字类型
     varchar2
     date
     boolean  布尔
  2）复合类型
     record
     table
  3）引用类型(参考类型)
     ref
  4）大类型  (路径)
     BLOB    0-4g
     CLOB    0-4g
 8.2案例：使用binary_integer和boolean类型定义变量
   declare
     var_id binary_integer:=1;
     var_flag boolean;
   begin
     var_flag:=true;
     if var_flag then
       dbms_output.put_line(var_id);
     end if;
   end;
   /     
  
 8.3 变量的修饰
  constant 数据类型 not null 
  
  declare
    -- var_id constant number:=1;
    -- var_name varchar2(20) not null:='Jerry';
    var_n  number;
  begin
    -- var_id:=2;
    -- dbms_output.put_line(var_id);
    -- var_name:='Tom';
    -- dbms_output.put_line(var_name);
    dbms_output.put_line(var_n);
  end;
  /
  constant:
  1）constant修饰的变量必须初始化
  PLS-00322: declaration of a constant 'VAR_ID' must 
  contain an initialization assignment
  2）constant修饰的变量不允许重新赋值
  PLS-00363: expression 'VAR_ID' cannot be used as 
  an assignment target
  
  not null:
  1)not null修饰的变量必须初始化
  PLS-00218: a variable declared NOT NULL must have 
  an initialization assignment


  plsql中任何类型的变量，没有赋值时值都是null


 8.4 定义两个变量，类型分别和s_emp表中id和first_name相同
     把id=1的员工的id和first_name放到两个变量中并输出
   declare
     var_id number(7);
     var_name varchar2(25);
   begin
     var_id:=1;
     var_name:='Carmen';
     dbms_output.put_line(var_id||':'||var_name);
   end;
   /
   
   表名.字段%type:获取表中字段的数据类型
   使用select语句可以直接给变量赋值
   declare
     var_id s_emp.id%type;
     var_name s_emp.first_name%type;
   begin
     -- var_id:=1;
     -- var_name:='Carmen';
     select id,first_name into var_id,var_name 
           from s_emp where id=1;
     dbms_output.put_line(var_id||':'||var_name);
   end;
   /


  8.5 record类型
  1）record类型的定义
    type 类型名 is record(
      字段 类型,
      ...
      字段 类型
    );
  2）定义一个record类型，字段和s_emp表的
    id,first_name,salary对应。声明一个record类型的变量
    保存id=2的员工的id,first_name和salary
    declare
      /* 定义一个record类型 */
      type emp_record is record(
        id number(7),
        name varchar2(25),
        sal number(11,2)
      );
      /* 使用定义的record类型声明变量 */
      var_emp emp_record;
    begin
      select id,first_name,salary into var_emp
         from s_emp where id=2;
      dbms_output.put_line(var_emp.id||':'||
                        var_emp.name||':'||var_emp.sal);
    end;
    /


  3)如何获取表中的一整行数据？
    表名%rowtype
    declare
      /* 使用表名%rowtype声明变量 */
      var_emp s_emp%rowtype;
    begin
      select * into var_emp
         from s_emp where id=2;
      dbms_output.put_line(var_emp.id||':'||
                          var_emp.first_name||':'
                        ||var_emp.salary||':'
                        ||var_emp.dept_id);
    end;
    /


  4)实际查询的字段的数量少于record类型的字段数量时
    declare
      /* 定义一个record类型 */
      type emp_record is record(
        id number(7),
        name varchar2(25),
        sal number(11,2)
      );
      /* 使用定义的record类型声明变量 */
      var_emp emp_record;
    begin
      select id,first_name into var_emp.id,var_emp.name
         from s_emp where id=2;
      dbms_output.put_line(var_emp.id||':'||
                        var_emp.name);
    end;
    /


  8.6 table类型类似于C语言的数组
   1）table类型的定义语法
    type 类型名 is table of 元素的数据类型
    index by binary_integer;
   2）定义一个table类型，使用table类型声明变量来
    保存多个数字
   declare
     /* 定义table类型 */
     type numstype is table of number
     index by binary_integer;
     /* 使用定义的类型声明变量 */
     var_nums numstype;
   begin
     /* 赋值 */
     var_nums(1):=100;
     var_nums(5):=200;
     var_nums(2):=300;
     /* 输出 */
     dbms_output.put_line(var_nums(1));
     dbms_output.put_line(var_nums(5));
     dbms_output.put_line(var_nums(2));
   end;
   /
   
   3)下标连续时
   declare
     /* 定义table类型 */
     type numstype is table of number
     index by binary_integer;
     /* 使用定义的类型声明变量 */
     var_nums numstype;
     /* 声明变量表示下标 */
     var_ind binary_integer;
   begin
     /* 赋值 */
     var_nums(1):=100;
     var_nums(2):=200;
     var_nums(3):=300;
     /* 输出 */
     var_ind:=1;
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_ind+1; -- plsql中没有++
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_ind+1;
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_ind+1;
   end;
   /


   4)下标不连续时  
    first():获取第一个元素的下标
    next(n):获取下标为n的元素的下一个元素的下标
    last():获取最后一个元素的下标
    
    declare
     /* 定义table类型 */
     type numstype is table of number
     index by binary_integer;
     /* 使用定义的类型声明变量 */
     var_nums numstype;
     /* 声明变量表示下标 */
     var_ind binary_integer;
   begin
     /* 赋值 */
     var_nums(1):=100;
     var_nums(7):=200;
     var_nums(3):=300;
     /* 输出 */
     var_ind:=var_nums.first();
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_nums.next(var_ind);
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_nums.next(var_ind);
     dbms_output.put_line(var_nums(var_ind));
     var_ind:=var_nums.next(var_ind);
   end;
   /


   5)思考：使用table类型保存s_emp表中id=1,3,7的三个员工的信息，
     并遍历输出(id,first_name,salary)
   table的元素类型使用record或者表名%rowtype
   使用select语句给元素赋值
   declare
     /* 定义table类型 */
     type empstable is table of s_emp%rowtype
     index by binary_integer;
     /* 声明变量 */
     var_emps empstable;
     /* 声明变量表示下标 */
     var_ind binary_integer;
   begin
     select * into var_emps(1) from s_emp where id=1;
     select * into var_emps(3) from s_emp where id=3;
     select * into var_emps(7) from s_emp where id=7;
     var_ind:=var_emps.first();
     dbms_output.put_line(var_emps(var_ind).id||':'||
                         var_emps(var_ind).first_name||
                    ':'||var_emps(var_ind).salary);
     var_ind:=var_emps.next(var_ind);
     dbms_output.put_line(var_emps(var_ind).id||':'||
                         var_emps(var_ind).first_name||
                    ':'||var_emps(var_ind).salary);
     var_ind:=var_emps.next(var_ind);
     dbms_output.put_line(var_emps(var_ind).id||':'||
                         var_emps(var_ind).first_name||
                    ':'||var_emps(var_ind).salary);
     var_ind:=var_emps.next(var_ind);
   end;
   /
   
 8.7 变量的作用域和可见性
  <<标签名>>
  <<a>>
  declare
     var_n number:=100;-- 全局变量
  begin
     declare
       var_m number:=1000;-- 局部变量
       var_n number:=200;
     begin
       dbms_output.put_line(var_m);     
       dbms_output.put_line(var_n);
       /* 局部可以访问全局变量 */
       /* 当全局变量和局部变量重名时，
          可以使用标签名.变量名的方式在局部访问全局变量 */
       dbms_output.put_line(a.var_n);
     end;
       /* 全局不能访问局部变量 */
       -- dbms_output.put_line(var_m);
     dbms_output.put_line(var_n);
  end;
  /


9.控制语句
  9.1 分支语句
  1）C语言中的写法
     if(a>b){
        //操作
     }


     if(a>b){
     }
     else{
     }


     if(a>b){
     }
     else if(b>c){
     }
     ...
     else{
     }
  2）plsql中的写法  条件可以用()括起来，一般不写
     if a>b then
       -- 操作
     end if;


     if a>b then
     else
     end if;


     if a>b then
     elsif b>c then
     ...
     else
     end if;
  
  3）定义三个变量并赋值，输出最大值
   -- 方法一
   declare
     var_a number:=12;
     var_b number:=6;
     var_c number:=20;
   begin
     if var_a > var_b then
        if var_c > var_a then
           dbms_output.put_line(var_c);
        else
           dbms_output.put_line(var_a);
        end if;
     else -- var_a<=var_b
        if var_c > var_b then
           dbms_output.put_line(var_c);
        else
           dbms_output.put_line(var_b);
        end if;
     end if;
   end;
   /


   -- 方法二
   declare
     var_a number:=12;
     var_b number:=6;
     var_c number:=20;
   begin
     if var_a < var_b then
         var_a:=var_b;
     end if;
     if var_a < var_c then
         var_a:=var_c;
     end if;
     dbms_output.put_line(var_a);
   end;
   /


   -- 方法三
   declare
     var_a number;
     var_b number;
     var_c number;
     var_max number;
   begin
     var_a:=&var_a;
     var_b:=&var_b;
     var_c:=&var_c;
     var_max:=var_a;
     if var_b > var_max then
         var_max:=var_b;
     end if;
     if var_c > var_max then
         var_max:=var_c;
     end if;
     dbms_output.put_line(var_max);
   end;
   /


  9.2 循环语句
  9.2.1 简单循环
    1) 语法
        loop
          -- 操作
       end loop;
    2）退出循环的方式
       if 循环结束的条件 then 
           exit;
       end if;


       exit when 循环退出的条件;
    3)使用简单循环输出1..10
      declare
        var_i number:=1;
      begin
        loop
          dbms_output.put_line(var_i);
          if var_i=10 then
             exit;
          end if;
          var_i:=var_i + 1;
        end loop;
      end;
      /


   9.2.2 while循环
    1）语法：
     while 条件 loop
      -- 操作
     end loop;
    2）使用while循环输出1..10
     declare
       var_i number:=1;
     begin
       while var_i<=10 loop
           dbms_output.put_line(var_i);
           var_i:=var_i + 1;
       end loop;
     end;
     /
     /* 退出循环的两种方式在三种循环中都可以使用 */
     declare
       var_i number:=1;
     begin
       while var_i<=10 loop
           dbms_output.put_line(var_i);
           exit when var_i=5;
           var_i:=var_i + 1;
       end loop;
     end;
     /


   9.2.3 for循环  智能循环
    1)语法
     for 变量 in 区间 loop
        -- 循环的操作
     end loop;
    2）使用for输出1..10
     /* 循环变量不用声明 */
     begin
       for var_i in 1..10 loop
         dbms_output.put_line(var_i);
       end loop;
     end;
     /
    
    3)使用for循环输出10到1 -- 使用reverse关键字
     begin
       for var_i in reverse 1..10 loop
         dbms_output.put_line(var_i);
       end loop;
     end;
     /


    4）for循环中的循环变量不允许被赋值
     begin
       for var_i in 1..10 loop
         dbms_output.put_line(var_i);
         if var_i=5 then
           --var_i:=11;
         end if;
       end loop;
     end;
     /


  9.3 goto语句
   <<标签名>>
     -- 语句(必须语句)  NULL;(空语句)
   goto 标签名;




   for ...  loop
     for ... loop
       goto 标签名;
     end loop;
   end loop;
   <<标签名>>
     NULL;
  
   <<outer>>
   for ...  loop
     for ... loop
       if 退出条件 then
          exit outer;
       end if;
     end loop;
   end loop;    
   




10.plsql中如何使用sql语句
  1)select语句:配合into使用
     select 字段 into 变量 from 表名 where 条件;
     select id,first_name into var_id,var_name from s_emp
            where id=1;
  2)dml(insert  delete update)
    tcl(commit  rollback  savepoint)
    可以直接在plsql中使用
  3)ddl(create  drop  alter)
    不能直接在plsql中使用  必须使用动态sql


11.动态sql
 11.1 概念
   把一个保存了sql语句的字符串，当成真正的sql语句执行
 11.2 案例
   /* 错误：ddl语句不能直接在plsql中使用 */
   begin
     create table testsql_zsm_00(id number);
   end;


   /* 函数可以在plsql中使用 */
   declare
     sqlstr varchar2(200);
   begin
     sqlstr:='create table testsql_zsm_00(id number)';
     sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
     sqlstr:=sqlstr||',name varchar2(20))';
     --dbms_output.put_line(sqlstr);
     execute immediate sqlstr;
   end;
   /


   11.3 dml的动态sql
    1)直接使用dml和tcl语句
    begin
      insert into testsql_zsm_00 values(1,'test1');
      commit;
    end;
    /


    2）常规的字符串拼接
    declare
      sqlstr varchar2(100);
    begin
      sqlstr:='insert into testsql_zsm_00 values(2,''test2'')';
      execute immediate sqlstr;
      commit;
    end;
    /
    3)带变量的字符串的拼接
    declare
      sqlstr varchar2(100);
      var_id number:=3;
      var_name varchar2(20):='test3';
    begin
      sqlstr:='insert into testsql_zsm_00 values(';
      sqlstr:=sqlstr||var_id||','''||var_name||''')';
      -- dbms_output.put_line(sqlstr);
      execute immediate sqlstr;
      commit;
    end;
    /


    4)使用占位符 配合using 解决带变量的字符串的拼接
     占位符:     :标识符
     
     declare
      sqlstr varchar2(100);
      var_id number:=4;
      var_name varchar2(20):='test4';
     begin
      sqlstr:='insert into testsql_zsm_00 values(:b0,:b1)';   
      execute immediate sqlstr using var_id,var_name;
      commit;
     end;
     /


----------------------------------------------------------
总结：
1.变量和数据类型
2.控制语句
3.在plsql中使用sql语句
  




练习：
1.定义record类型，声明record类型变量 保存s_dept表中id=41
 的部门的信息
2.（选作）
 用table类型变量保存s_region表中id为1，2，3信息，
 并遍历输出（可以使用循环输出）
3.删除testsql_XXX_NN表中id=3的数据