Oracle笔记5

1、程序化结构化语言
 过程
 
2、一组连接数据库的接口
 OCI：比较底层(Oracle call Interface)
 pro*C/C++/...：c/c++嵌入SQL.
 SQLJ,JDBC(j2ee[java中的企业级开发]中数据库连接的标准接口)：java访问Oracle
  根据对不同jar包的调用可以连接到不同的数据库.
 ODBC:开放的数据库连接(open DB Connectionty),与JDBC相似；但是与操作系统有关，
  中间也需要驱动程序实现.
  
3、PL/SQL
 (1)结构化模块编程
 (2)良好的可移植性
 (3)良好的可维护性
 (4)提升系统性能; 可以保存在数据库服务器端；可以给不同的客户端调用.
 
 (5)不便于向异构 数据库移植应用程序
 
4、PL/SQL块
 声明部分，DECLARE
 执行部分,BEGIN
 异常处理,EXCEPTION
 
5、PL/SQL运行过程
 
6、开发环境
 Sqlplus utility
 GUI Develop Tools
  SQL Navigator  www.quest.com
  SQL Programmer  www.bmc.com
  
7、PL/SQL字符集
 : 赋值符号

8、标识符
 用来给对象命名
  变量，游标，类型，子程序
 (1)必须是以字母开头
 (2)后跟
 (3)最大长度为30
 
9、分界符

10、文字

11、注释

12、变量的声明
 语法：

 DBMS_OUTPUT.PUT_LINE：输出打印函数
 声明不能在begin块中，而赋值可以。
 
 set serveroutput(可简写为：serverout) on:打开输出命令

13、数据类型
 (1)标量型
 (2)组合型
 (3)参考型
 (4) LOB(Large Object)
 
 %type:
 
 declare
  --v_name varchar(50)
  v_name s_emp.first_name%type;  --type属性的用法
  v_salary s_emp.salary%type;
  v_deptid s_emp.dept_id%type;
 begin
  select first_name, salary, dept_id  --顺序一致
  into vname
  from s_emp
  where id = 5;
  DBMS_OUTPUT.PUT_LINE('id:5 name:' || v_name);
  DBMS_OUTPUT.PUT_LINE('salary:' || v_name);
  DBMS_OUTPUT.PUT_LINE('dept_id:' || v_name);
 end;
 
 record类型：
  declare
   type emp_info is record(
    v_name s_emp.first_name%type,  --type属性的用法
    v_salary s_emp.salary%type,
    v_deptid s_emp.dept_id%type
   );
   v_emp emp_info;
   v_emp2 emp_info;
   
   type emp_info2 is record(
    v_name s_emp.first_name%type,  --type属性的用法
    v_salary s_emp.salary%type,
    v_deptid s_emp.dept_id%type
   );
   v_emp3 emp_info2; 
   --此时不能写：v_emp3 := v_emp;(会报错：different type) 
   
  begin
   select first_name, salary, dept_id
   into v_emp
   from s_emp
   where id = 5;
   DBMS_OUTPUT.PUT_LINE('id:5 name:' || v_emp.v_name);
   DBMS_OUTPUT.PUT_LINE('salary:' || v_emp.v_salary);
   DBMS_OUTPUT.PUT_LINE('dept_id:' || v_emp.v_dept_id);
   v_emp2 := v_emp;
   DBMS_OUTPUT.PUT_LINE('id:5 name:' || v_emp2.v_name);
   DBMS_OUTPUT.PUT_LINE('salary:' || v_emp2.v_salary);
   DBMS_OUTPUT.PUT_LINE('dept_id:' || v_emp2.v_dept_id);
 end;
 
 %ROWTYPE
  代表某张表的整行记录
   declare
        v_emp s_emp%rowtype;
   begin
        select *
        into v_emp
        from s_emp
        where id = 5;
        DBMS_OUTPUT.PUT_LINE('first_name:' || v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('salary:' || v_emp.salary);
        DBMS_OUTPUT.PUT_LINE('dept_id:' || v_emp.dept_id);
   end;

 table类型：
  declare
        type emp_table is table of s_emp%rowtype index by binary_integer;
        v_emp emp_table;
  begin
        select *
        into v_emp(5)
        from s_emp
        where id = 5;
        select *
        into v_emp(10)
        from s_emp
        where id = 10;
        DBMS_OUTPUT.PUT_LINE('id:5' || v_emp(5).first_name || ' ' ||
        v_emp(5).salary || ' ' || v_emp(5).dept_id);
        DBMS_OUTPUT.PUT_LINE('id:10' || v_emp(10).first_name || ' '||
        v_emp(10).salary || ' ' || v_emp(10).dept_id);
  end;

14、变量的作用域与可见性
 （1）内嵌过程可以改变外部变量的值；内嵌过程声明的变量不能在外部使用。
  <<outer>>
  declare
         v_ssn number;
  begin
         v_ssn := 10;
         DBMS_OUTPUT.PUT_LINE('v_ssn :' || v_ssn);
       
         declare
          v_ssn varchar2(20) := 'hello.workd!';
      begin
          DBMS_OUTPUT.PUT_LINE('inner v_ssn:' || v_ssn);
             DBMS_OUTPUT.PUT_LINE('access outer v_ssn:' || outer.v_ssn);
          end;
         DBMS_OUTPUT.PUT_LINE('v_ssn:' || v_ssn);
  end;

15、PL/SQL控制语句
 (1)条件语句
  declare
         v_salary number;
         --v_emp s_emp%rowtype;  (注意可以使用这样的声明变量)
  begin
         select salary
         into v_salary
         from s_emp
         where id = 5;
         if v_salary < 1000 then
                 v_salary := v_salary * (1 + 0.2);
         elsif v_salary < 2000 then
                 v_salary := v_salary * (1 + 0.15);
         else v_salary := v_salary * (1 + 0.1);
         end if;
         DBMS_OUTPUT.PUT_LINE('id:5 salary:' || v_salary);
  end;

 (2)循环语句
  while循环：
   利用table类型;
    declare
     type v_table is table of s_emp%rowtype index by binary_integer;
     v_row v_table;
     v_id s_emp.id%type := 1;
    begin
     while v_id < 6 loop
      select *
      into v_row(v_id)
      from s_emp
      where id = v_id;
      dbms_output.put_line(...)   --要打印的字段
      v_id = v_id + 1;
     end loop;
    end;
    
   利用rowtype类型；
   declare
         v_row s_emp%rowtype;
         v_id s_emp.id%type := 1;
   begin
          while v_id < 6 loop
                 select *
                 into v_row
                 from s_emp
                 where id = v_id;
                 dbms_output.put_line('id:' || v_id ||' first_name:'
                  || v_row.first_name || ' ' || 'salary:' ||
                  v_row.salary ||' ' || 'dept_id:' || v_row.dept_id);
                 v_id := v_id + 1;
          end loop;
   end;

  
  for循环：
   for loop_counter in [reverse] low_bound..high_bound loop
    ...
   end loop;
   reverse:反向执行；先执行满足条件的高级的；
   
   loop_counter不需要在declare中声明
   
   declare
        v_row s_emp%rowtype;
   begin
        for v_id in 1..4 loop
                select *
                into v_row
                from s_emp
                where id = v_id;
                dbms_output.put_line('id:' || v_id ||' first_name:'
                 || v_row.first_name|| ' ' || 'salary:' || v_row.salary
                 ||' ' || 'dept_id:' || v_row.dept_id);
                v_id := v_id + 1;
         end loop;
  end;

   
 (3)goto 语句
 
 (4)null 语句

16、SQL in PL/SQL1
 (1)Data Manipulation Language(DML)
  select ,insert, delete, set transaction, explain plan
 
 (2)Data Definition Language(DDL)
  drop, create, alter, grant, revoke
 
 (3)Transaction Control
  commit, rollback, savepoint
 
 (4)Session Control
  alert session, set role
  
 注意：只有DML(包含查询) SQL和事务控制语句可以直接在PL/SQL中使用
   使用Oracle内置的DBMS_SQL包，可以使用动态SQL语句
   动态SQL语句是在运行时生成一个SQL的串，将该串提交给DBMS_SQL
    包来执行(create_table.sql)
    
17、游标(cursor)
 作用：用来提取多行数据集
 使用：
  (1)声明
  (2)打开
  (3)取数据
  (4)关闭
 游标的属性： 
 游标的FETCH循环
  declare
         cursor mycursor is select * from s_emp where id < 5;
         v_emp s_emp%rowtype;
  begin
         open mycursor;
          loop
           fetch mycursor into v_emp;
              EXIT WHEN mycursor%NOTFOUND;    -- 关键
              dbms_output.put_line('id:' || v_emp.id || ' '
               || 'first_name:' || v_emp.first_name || ' '
                  || 'salary:' || v_emp.salary);
          end loop;
         close mycursor;
  end;