Oracle11G-SQL开发指南-11-PL/SQL编程

1.  块结构
    [DECLARE declaration_statements ]
    BEGIN
        executable_statements
    [EXCEPTION
        exception_handling_statements
    ]
    END;
   例1：
   DECLARE
     V_WIDTH  INTEGER;
     V_HEIGHT INTEGER := 3;
     V_ARE    INTEGER := 6;
   BEGIN
     V_WIDTH := V_ARE / V_HEIGHT;
     DBMS_OUTPUT.PUT_LINE('v_width=' || V_WIDTH);
   EXCEPTION
     WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE('报错了：Division by zero');
   END;


2.  变量和类型
    变量在DECLARE块中声明；
    声明时要同时包含名称和类型；
    v_id INTEGER;
    v_char VARCHAR2(50);
    v_type table_name.colmnt_name%TYPE;


3.  条件逻辑
    IF、THEN、ELSE、ELSEIF、ENDIF;


4.  循环
    a> 简单循环
        LOOP
            statements
        END LOOP;
结束循环要使用EXIT或EXIT WHEN语句
结束当前循环进行下一次循环可以使用CONTINUE或CONTINUE WHEN语句
    b> WHILE循环
        WHILE condition LOOP
   statements
END LOOP;


    c> FOR循环
        FOR loop_variable IN [REVERSE] lower_bound.. upper_bound LOOP
   statements
END LOOP;


5.  游标cursor
    a> 声明变量，用于存储记录的列值；
    b> 声明游标；
    c> 打开游标；
    d> 一次从游标中取出一个值来，并将列值存储在第1步声明的变量中，进行自定义的操作处理；
    e> 关闭游标；


6.  异常  exception 块中的内容
    EXCEPTION 
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('出现异常：Division by zero');
        ROLLBACK;
        RETURN;


7.  过程 : 一组SQL和pl/sql语句
    a> 创建过程
       CREATE [ OR REPLACE ] PROCEDURE pocedure_name
       [(parameter_name [IN | OUT | IN OUT ] type [, ...])]
       {IS | AS }
       BEGIN
         procedure_body
       END pocedure_name ;
    b> 调用过程:
       call pocedure_name (parameter_name => value);
    c> 获取过程信息 user_procedures 
    d> 删除过程  drop PROCEDURE pocedure_name ;
    e> 查看报错 show errors


8.  函数，与过程相似，就是多一个返回值
    a> 创建函数
       CREATE [ OR REPLACE ] FUNCTION function_name
       [(parameter_name [IN | OUT | IN OUT ] type [, ...])]
       RETURN type
       {IS | AS }
       BEGIN
         function_body
       END function_name ;
    b> 调用函数:与内置函数类似
    c> 获取函数信息 user_procedures 
    d> 删除过程  drop FUNCTION function_name ;


9.  包，把函数和过程组织在一起
    包由两部分组成：规范specification和包体body
    创建包规范：
    CREATE [OR REPLACE ] PACKAGE package_name
    {IS | AS}
        package_specification
    END package_name ;
    创建包体：
    CREATE [OR REPLACE ] PACKAGE BODY package_name
    {IS | AS}
        package_body
    END package_name;


10. 触发器
    CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | ALTER | INSTEAD OF | FOR } trigger_event
    ON table_name
    [FOR EACH ROW]
    [{FORWARD | REVERSE} CROSSEDITION ]
    [{FOLLOWS | PRECEDES} schema.other_trigger]
    [{ENABLE | DISABLE}]
    [WHEN trigger_condition ]
    BEGIN
        trigger_body;
    END trigger_name ;


创建时的is与as区别
是ORACLE为了方便而设置的同义词
在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。


-------------------------------------------------------
-- 块  声明块，执行块，异常块 
  DECLARE
     V_WIDTH  INTEGER;
     V_HEIGHT INTEGER := 3;
     V_ARE    INTEGER := 6;
   BEGIN
     V_WIDTH := V_ARE / V_HEIGHT;
     DBMS_OUTPUT.PUT_LINE('v_width=' || V_WIDTH);
   EXCEPTION
     WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE('报错了：Division by zero');
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('出现异常：Division by zero');
   END;


-------------------------------------------------------
 --简单循环 ，IF，EXIT WHEN
   DECLARE
      V_COUNT INTEGER := 0;
    BEGIN
      LOOP
        V_COUNT := V_COUNT + 1;
        IF V_COUNT = 3 THEN
          CONTINUE;
        END IF;
        DBMS_OUTPUT.PUT_LINE('当前值为：' || v_count);
        EXIT WHEN V_COUNT = 5;
      END LOOP;
    END;
-------------------------------------------------------
--简单循环，CONTINUE WHEN，EXIT WHEN
    DECLARE
      V_COUNT INTEGER := 0;
    BEGIN
      LOOP
        V_COUNT := V_COUNT + 1;
        CONTINUE WHEN v_count = 3 ;
        DBMS_OUTPUT.PUT_LINE('当前值为：' || v_count);
        EXIT WHEN V_COUNT = 5;
      END LOOP;
    END;
-------------------------------------------------------   
-- while 循环
    DECLARE
      V_COUNT INTEGER := 0;
    BEGIN
      WHILE V_COUNT < 6 LOOP
        V_COUNT := V_COUNT + 1;
        DBMS_OUTPUT.PUT_LINE('当前值为：' || V_COUNT);
      END LOOP;
    END;
-------------------------------------------------------
--for循环


    DECLARE
      V_COUNT INTEGER := 0;
    BEGIN
      FOR V_COUNT IN 1.. 6 LOOP      
        DBMS_OUTPUT.PUT_LINE('当前值为：' || V_COUNT);
      END LOOP;
    END;


-------------------------------------------------------
-- 游标示例1 完整过程
 
    DECLARE
      -- 1：声明变量
      V_PRODUCT_ID PRODUCTS.PRODUCT_ID%TYPE;
      V_NAME       PRODUCTS.NAME%TYPE;
      V_PRICE      PRODUCTS.PRICE%TYPE;
      -- 2：声明游标
      CURSOR V_PRODUCT_CURSOR IS
        SELECT PRODUCT_ID, NAME, PRICE FROM PRODUCTS ORDER BY PRODUCT_ID;
    
    BEGIN
      -- 3： 打开游标
      OPEN V_PRODUCT_CURSOR;
    
      LOOP
      -- 4： 从游标中取数据
        FETCH V_PRODUCT_CURSOR
          INTO V_PRODUCT_ID, V_NAME, V_PRICE;
        EXIT WHEN V_PRODUCT_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('id值为：' || V_PRODUCT_ID || 'name=：' || V_NAME);
      END LOOP;
      -- 5.关闭游标
      CLOSE V_PRODUCT_CURSOR;
    END;

-------------------------------------------------------
-- 游标示例2 ： 结合for循环 不用显示打开和关闭游标
 
    DECLARE
      -- 1：声明变量
      -- 2：声明游标
      CURSOR V_PRODUCT_CURSOR IS
        SELECT PRODUCT_ID, NAME, PRICE FROM PRODUCTS ORDER BY PRODUCT_ID;
    
    BEGIN
      -- 3： 打开游标    
      FOR v_product IN V_PRODUCT_CURSOR LOOP
      -- 4： 从游标中取数据
        DBMS_OUTPUT.PUT_LINE('id值为：' ||v_product.PRODUCT_ID || 'name=：' || v_product.NAME);
      END LOOP;
      -- 5.关闭游标
    END;
 


-------------------------------------------------------
-- 游标示例3 ：ref cursor游标 结合open-for语句 灵活使用游标
-- 动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
    
    DECLARE
      -- 1：声明变量
      -- 2：声明游标
      TYPE T_PRODUCT_CURSOR IS REF CURSOR RETURN PRODUCTS%ROWTYPE;
      V_PRODUCT_CURSOR T_PRODUCT_CURSOR;
      V_PRODUCT        PRODUCTS%ROWTYPE;
    
    BEGIN
      -- 3： 打开游标    
      OPEN V_PRODUCT_CURSOR FOR
        SELECT * FROM PRODUCTS WHERE PRODUCT_ID < 5;
      LOOP
        FETCH V_PRODUCT_CURSOR INTO V_PRODUCT;
        EXIT WHEN V_PRODUCT_CURSOR%NOTFOUND;
        -- 4： 从游标中取数据
        DBMS_OUTPUT.PUT_LINE('id值为：'|| V_PRODUCT.PRODUCT_ID || 'name=：'||V_PRODUCT.NAME);
      END LOOP;
      -- 5.关闭游标
      CLOSE V_PRODUCT_CURSOR;
    END;


-------------------------------------------------------
-- 游标示例4  无约束游标


    DECLARE
      TYPE T_CURSOR IS REF CURSOR;  --先定义动态游标
      V_CURSOR   T_CURSOR;
      V_PRODUCT  PRODUCTS%ROWTYPE;
      V_CUSTOMER CUSTOMERS%ROWTYPE;
      
    BEGIN
      OPEN V_CURSOR FOR
        SELECT * FROM PRODUCTS WHERE PRODUCT_ID < 5 ;
      LOOP
        FETCH V_CURSOR  INTO V_PRODUCT;   -- 第一次动态使用
        EXIT WHEN V_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('id值为：' || V_PRODUCT.PRODUCT_ID || 'name=：' || V_PRODUCT.NAME);
      END LOOP;


      OPEN V_CURSOR FOR
        SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID <3 ;
      LOOP
        FETCH V_CURSOR INTO V_CUSTOMER;  -- 第二次动态使用
        EXIT WHEN V_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('id值为：' || V_CUSTOMER.customer_ID || 'name=：' || V_CUSTOMER.first_name);
      END LOOP;
      EXCEPTION 
        WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('出现异常：Division by zero');
      
      CLOSE V_CURSOR;
    END;
        
-------------------------------------------------------
-- 过程


CREATE OR REPLACE PROCEDURE update_product_price(
  p_product_id IN products.product_id%TYPE,
  p_factor     IN NUMBER
) AS
  v_product_count INTEGER;
BEGIN
  -- count the number of products with the
  -- supplied product_id (will be 1 if the product exists)
  SELECT COUNT(*)
  INTO v_product_count
  FROM products
  WHERE product_id = p_product_id;


  -- if the product exists (v_product_count = 1) then
  -- update that product's price
  IF v_product_count = 1 THEN
    UPDATE products
    SET price = price * p_factor
    WHERE product_id = p_product_id;
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END update_product_price;

-------------------------------------------------------
-- 函数1
CREATE OR REPLACE FUNCTION circle_area (
  p_radius IN NUMBER
) RETURN NUMBER AS
  v_pi   NUMBER := 3.1415926;
  v_area NUMBER;
BEGIN
  -- circle area is pi multiplied by the radius squared
  v_area := v_pi * POWER(p_radius, 2);
  RETURN v_area;
END circle_area;


-- 函数2
CREATE OR REPLACE FUNCTION average_product_price (
  p_product_type_id IN INTEGER
) RETURN NUMBER AS
  v_average_product_price NUMBER;
BEGIN
  SELECT AVG(price)
  INTO v_average_product_price
  FROM products
  WHERE product_type_id = p_product_type_id;
  RETURN v_average_product_price;
END average_product_price;


-------------------------------------------------------
-- 包
CREATE OR REPLACE PACKAGE product_package AS
  TYPE t_ref_cursor IS REF CURSOR;
  FUNCTION get_products_ref_cursor RETURN t_ref_cursor;
  PROCEDURE update_product_price (
    p_product_id IN products.product_id%TYPE,
    p_factor     IN NUMBER
  );
END product_package;


-------------------------------------------------------
-- 触发器
CREATE OR REPLACE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW 
when (new.price < old.price * 0.75)
BEGIN
  dbms_output.put_line('product_id = ' || :old.product_id);
  dbms_output.put_line('Old price = ' || :old.price);
  dbms_output.put_line('New price = ' || :new.price);
  dbms_output.put_line('The price reduction is more than 25%');


  -- insert row into the product_price_audit table
  INSERT INTO product_price_audit (
    product_id, old_price, new_price
  ) VALUES (
    :old.product_id, :old.price, :new.price
  );
END before_product_price_update;