oracle函數學習筆記

SQL> CREATE OR REPLACE FUNCTION circle_area (
  2   p_radius IN NUMBER
  3  ) RETURN NUMBER AS
  4   v_pi NUMBER := 3.1415926;
  5   v_area NUMBER;
  6  BEGIN
  7   v_area := v_pi*POWER(p_radius,2);
  8   RETURN v_area;
  9  END;
 10  /


已建立函數.


SQL> SELECT circle_area(2)
  2  FROM dual;


CIRCLE_AREA(2)
--------------
    12.5663704


SQL> CREATE OR REPLACE FUNCTION average_product_price (
  2   p_product_type_id INTEGER
  3  ) RETURN NUMBER AS
  4   v_average_product_price NUMBER;
  5  BEGIN
  6   SELECT AVG(price)
  7   INTO v_average_product_price
  8   FROM products
  9   WHERE product_type_id = p_product_type_id;
 10   RETURN v_average_product_price;
 11  END average_product_price;
 12  /


已建立函數.


SQL> SELECT average_product_price(1)
  2  FROM dual;


AVERAGE_PRODUCT_PRICE(1)
------------------------
                 22.4825


SQL> DROP FUNCTION circle_area;