130823创建函数

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    7  begin  8    v_area := v_pi * power(p_radius, 2);  9    return v_area; 10   11  end circle_area; 12  / Function created SQL> select circle_area(2) from dual; CIRCLE_AREA(2)--------------    12.5663704

SQL> create or replace function average_product_price(  2  p_product_type_id in integer  3  ) return number as  4  v_average_product_price number;  5    6  begin  7    select avg(price)  8    into v_average_product_price  9    from products 10    where product_type_id = p_product_type_id; 11    return v_average_product_price; 12  end average_product_price; 13  / Warning: Function created with compilation errors SQL> show error;Errors for FUNCTION CALVIN.AVERAGE_PRODUCT_PRICE: LINE/COL ERROR-------- -----------------------------------------------9/8      PL/SQL: ORA-00942: table or view does not exist7/3      PL/SQL: SQL Statement ignored SQL> SQL> create or replace function average_product_price(  2  p_product_type_id in integer  3  ) return number as  4  v_average_product_price number;  5    6  begin  7    select avg(price)  8    into v_average_product_price  9    from store.products 10    where product_type_id = p_product_type_id; 11    return v_average_product_price; 12  end average_product_price; 13  / Function created SQL> select calvin.average_product_price(1) from dual; CALVIN.AVERAGE_PRODUCT_PRICE(1------------------------------                       22.4825 

这里需要注意，查询函数使用user_procedures视图，系统中不存在users_functions视图；

SQL> select * from user_procedures; OBJECT_NAME                                                                      PROCEDURE_NAME                  OBJECT_ID SUBPROGRAM_ID OVERLOAD                                 OBJECT_TYPE         AGGREGATE PIPELINED IMPLTYPEOWNER                  IMPLTYPENAME                   PARALLEL INTERFACE DETERMINISTIC AUTHID-------------------------------------------------------------------------------- ------------------------------ ---------- ------------- ---------------------------------------- ------------------- --------- --------- ------------------------------ ------------------------------ -------- --------- ------------- ------------AVERAGE_PRODUCT_PRICE                                                                                                75103             1                                          FUNCTION            NO        NO                                                                      NO       NO        NO            DEFINERUPDATE_PRODUCT_PRICE                                                                                                 75101             1                                          PROCEDURE           NO        NO                                                                      NO       NO        NO            DEFINERCIRCLE_AREA                                                                                                          75102             1                                          FUNCTION            NO        NO                                                                      NO       NO        NO            DEFINER 

SQL> drop function circle_area; Function dropped