oracle基础之pl/sql基础知识

1. 块（Block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块
   块由定义部分，执行部分和异常处理部分组成。
   定义部分用于定义常量，变量，游标，异常，复杂数据类型等，Declare。
   执行部分用于实现应用模块功能，主要包括要执行的sql语句和pl/sql语句，begin。
   异常处理用于处理 块 运行时可能出现的错误，Exception。
   end是pl/sql的结束标记；declare，begin和exception后面没有分号，而end后面有。

2. pl/sql标识符命名规范
    当使用标识符定义变量，常量时每行只能定义一个标识符；标识符只能使用字母，数字
    下划线，$和#，如要使用其它字符则必须加上双引号；标识符名称必须以字符（A-Z a-z）
    开头，并且最大长度30个字符，如果以其他字符开头，必须加上双引号；不能使用oracle关键字
    ，如果要使用则必须加上双引号。

    注意：如果把一个带有单引号的字符串赋给一个变量,可以使用 q'[要赋的值]' 格式;
      s_var := q'[i'm a boy!]'; -- 把 i'm a boy! 赋给变量s_var
3. pl/sql的块划分为匿名块，命名块，子程序和触发器。
   匿名块：没有名字的pl/sql块。

  DECLARE
   v_name varchar2(10);
 BEGIN
   SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
   dbms_output.put_line('名字是:' || v_name);
 END;
3. 命名块：有名字（标示）的pl/sql，块前使用《》加以标示
        <<block1>>
 DECLARE
   v_name varchar2(10);
   v_age  number(4);
 BEGIN
   <<block2>>
   BEGIN
     SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
     dbms_output.put_line('名字是:' || v_name);
   END;
   SELECT t.age INTO v_age FROM emp t WHERE t.name = v_name;
   dbms_output.put_line('年龄:' || v_age);
 END;

 
 3.子程序：包括存储过程，函数和包
   存储过程：用于执行特定的操作，可以指定输入参数（in）或者输出参数（out），不指定默认输入。

 CREATE PROCEDURE test_update(name VARCHAR2, age NUMBER) IS
 BEGIN
   UPDATE emp t SET t.age = number WHERE t.name = name;
   COMMIT;--这里不加可以吗？
 END;

 
   函数：用于返回特定数据。函数的头部必须包含return字句，函数体内必须包含return语句返回的数据
   

     CREATE FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN VARCHAR2 IS
   v_addr VARCHAR2(100);
 BEGIN
   SELECT '中国.山东' || t.addr
     INTO v_addr
     FROM emp t
    WHERE t.name = name
      and t.age = age;
   RETURN v_addr;
 END;

 
   包：用于逻辑组合相关的存储过程和函数，由包规范和包体组成。包规范用于定义公用的常量， 变量，存储过程和函数。
       

-- 包规范知识定义了一个存储过程和函数而没有实现代码
 CREATE PACKAGE test_pkg IS
   PROCEDURE test_procedure(name VARCHAR2, age NUMBER);
   FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER;
 END;

    包体：
         

CREATE PACKAGE BODY test_pkg IS
   PROCEDURE test_procedure(name VARCHAR2, age NUMBER) IS
   BEGIN
     UPDATE emp t set t.name = name WHERE t.age = age;
   END;
   FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER IS
     v_slary NUMBER(4, 1);
   BEGIN
     SELECT t.slary
       INTO v_slary
      WHERE t.name = name
        and t.age = age;
     RETURN NUMBER v_slary;
   END;
 END;

 

   在使用包中的存储过程和函数时，必须在存储过程或者函数名之前加上包名作为前缀
   (包名.子程序名)
       
4. 触发器：隐形执行的存储过程，必须指定触发事件以及触发操作，触发事件包括insert，update和delete；而触发操作实际上是一个pl/sql块。
     

  -- 当更新dept表中的d_name列时更新emp表中的d_name列
CREATE TRIGGER test_trigger
   AFTER UPDATE OF d_name ON dept
   FOR EACH ROW
 BEGIN
   UPDATE emp t SET t.d_name = :new.d_name WHERE t.d_name = o :d_name;
 END;

 

5. pl/sql数据类型：标量（Scalar）类型，复合（Composit）类型，参照（Reference）类型和LOB（Large Object）类型。

        标量（Scalar）类型：只能存放单个数值的变量；定义标量时必须指定数据类型。
        varchar2(n) : 定义可变长字符串，最大值32767字节；定义变量时必须指定长度。varchar2定义 列时最大长度是4000字节。
        char(n) : 定义变量时如果没有指的那个长度默认为1， 定义列数据时最大长度是2000字节。
        boolean ：布尔类型值为true，false和null。表列不能使用该数据类型
        binary_integer:定义整数-2147483647到2147483647
        binary_float 和 binary_double 是oracle10新增，分别用来定义单精度浮点数和双精度浮点数
        binary_float 应带有后缀f ,binary_double应带后缀d
 
    定义标量变量：

 

 v_name varchar2(10);
 v_age number(10,4);--整数部分是6位
 v_sex constant vachar(10):='woman';
 v_date Date;
 v_flag boolean not null default false;

   --如果在定义变量时没有指定初始值那么变量的初始值为null。

 

 

  %TYPE：当使用%type定义变量时,它会按照数据库列或者其他变量来确定新变量的类型和长度。
  

  DECLARE
   v_name   emp.name%TYPE;
   v_t_name v_name%TYPE;
   v_age CONSTANT NUMBER(2) := 11;
 BEGIN
   SELECT name, age INTO v_name, v_age FROM emp WHERE emp.id = '45';
   v_t_name := v_name;
   dbms_output.put_line('名字是：' || v_t_name);
 END;

 

 

 复合（Composit）类型:
    复合变量用来存放多个值的变量。pl/sql复合数据类型包括记录，表，嵌套表以及数组（varry）；
  记录：首先在定义部分定义记录类型和记录变量，然后再执行部分引用该记录变量

 DECLARE
   TYPE test_type_emp IS RECORD(
     v_name emp.name%TYPE,
     v_age  emp.age%Type);
   v_r_emp test_type_emp; -- 定义一个record变量test_type_emp
 BEGIN
   SELECT name, age INTO v_r_emp FROM emp WHERE emp.id = '45';
   dbms_output.put_line('名字是：' || v_r_emp.v_name);
 END;

 

 

    嵌套表（Nested Table）：类似高级语言的数组，只是嵌套表的个数没有限制。嵌套表可以作为
表列的数据类型。pl/sql表不能作为表列的数据类型。

 

 --创建嵌套表
CREATE OR REPLACE TYPE test_emp_type AS OBJECT
 (
   name varchar2(10),
   age  number
 ) ;

 

CREATE OR REPLACE TYPE emp_type IS TABLE OF test_emp_type;

 

 

 

       创建嵌套表后，就可以在表列或对象属性中将其作为用户自定义类型来引用。
       值得注意的是当使用嵌套表作为表列时必须专门为其制定专门的存储表。
        

CREATE TABLE hoom(
    hname varchar2(100);
    vemp emp_array
 )NESTED TABLE STORE AS vemp

 

       数组：类似嵌套表，varray的元素是有限制的。当使用varray数组时要先建立一个varray。当建立了varry类型之后，可以在表列或对象属性中将其作为用户自定义数据类型。

     

CREATE OR REPLACE TYPE test_emp_type AS OBJECT
 (
   name varchar2(10),
   age  number
 )
 ;

CREATE OR REPLACE TYPE emp_array IS VARRY(10) OF test_emp_type;
        
CREATE TABLE hoom(
    hname varchar2(100);
    vemp emp_array
 )

         
 参照变量： 用于存放数值指针的变量，使用参照变量，可以使应用程序共享相同对象包括游标（Cursor）和对象类型变量（obj_type）
       

 DECLARE
   TYPE c1 IS REF CURSOR;
   emp_cursor c1;
   v_name     emp.name%TYPE;
   v_age      emp.age%TYPE;
 BEGIN
   OPEN emp_cursor FOR
     SELECT name, age FROM emp WHERE emp.name = 'a';
   LOOP
     FETCH emp_cursor
       INTO v_name, v_age;
     EXIT WHEN emp_cursor%notfound;
     dbms_output.put_line('名字是：' || v_name);
   END LOOP;
   CLOSE emp_cursor;
 END;