使用oracle function和存储过程返回单个值

1 function 返回值
function get_link_coalingid(p_id in varchar2 ) return varchar2  is    
     r_lid             varchar2(200);
   begin
     select t.link_coalingid into r_lid
      from dis_w_package_train_link_his t
     where t.pid =  p_id  and rownum <= 1  order by t.link_date desc ;   
      return r_lid;
    EXCEPTION 
    WHEN OTHERS THEN 
    RETURN NULL; 
    end;

2 执行动态sql
比如动态获取某个字段值
function getStanderByField(p_id in varchar2,fieldName in varchar2 ) return number is
    iv_sqlstr           VARCHAR2(200); 
    r_result number;
    begin
      iv_sqlstr := 'select '||fieldName||'  from v_dis_w_package_train_type 
     where  id = :1'    ;
     EXECUTE IMMEDIATE iv_sqlstr INTO r_result using p_id;
     RETURN r_result;
    end;
3 存储过程一样使用

参考
http://sishuok.com/forum/posts/list/792.html
http://blog.csdn.net/jumtre/article/details/38092067
http://blog.csdn.net/tanshi/article/details/7083922
---------------------------------------
1          EXECUTE IMMEDIATE

        oracle中DBMS_SQL package包和EXECUTE IMMEDIATE都可以用来解析并执行动态SQL语句或非运行时创建的PL/SQL块，相比较而言，EXECUTE IMMEDIATE使用较简单，能够满足较常用的需要。

1.1         语法


           EXECUTE IMMEDIATE v_sql  [BULK COLLECT INTO 或INTO 返回值变量] [INTO 入参 1,.., out 出参1,..]。

说明：
      1、v_sql为varchar2类型或clob（11g才支持），可以为DDL、DML等动态拼接的sql字符串。用在pl/sql代码中时，如果是varchar2类型，则长度不能大于32767(32K)。

      2、v_sql为DML动态语句时，执行后不会提交，需要使用commit显式提交。如果为DDL命令,执行后则会提交所有之前改变的。
      3、如果需要从动态sql返回值，则可以定义返回值变量，BULK COLLECT INTO返回多行值，此时定义的变量需是数组变量的列表或记录表类型；INTO返回单行，此时定义的变量可以使多个pl/sql变量的列表或记录类型。

      4、如果动态sql中需要绑定变量，则使用USING，通常绑定的变量为输入入参，此时变量的in可以省略；如果需要绑定输出变量（如调用过程时可能需要输出），则在变量前用out显示指明。
1.2.1 动态DDL
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
BEGIN 
  v_sql := ' create table ' || v_table || 
           ' (id varchar2(10),name varchar2(100))'; 
  EXECUTE IMMEDIATE v_sql; 
END;
1.2.2        动态DML insert
1.2.2.1       不绑定输入变量
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
BEGIN 
  --1、不绑定输入变量 
  v_sql := ' insert into ' || v_table || 
           ' values (''1'',''no_binding_in_variable'')'; 
  EXECUTE IMMEDIATE v_sql; 
  COMMIT; --dml需要显示提交 
END;
1.2.2.2       绑定输入变量
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
BEGIN 
  --1、绑定输入变量 
  v_sql := ' insert into ' || v_table || ' values (:1,:2)'; 
  EXECUTE IMMEDIATE v_sql 
    USING '2', 'binding_in_variable'; --使用using绑定输入变量 
END;
1.2.3        动态DML select
1.2.3.1       返回单行值
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
  --1、使用简单pl/sql变量v_id,v_name获得单行输出 
  v_id   VARCHAR2(10); 
  v_name VARCHAR2(100); 
  --2、使用基于test_ynamic_sql表的记录变量获得单行输出 
  TYPE test_ynamic_sql_record IS RECORD( 
    v_id   test_ynamic_sql.ID%TYPE, 
    v_name test_ynamic_sql.NAME%TYPE); 
  test_ynamic_sql_row test_ynamic_sql_record; 
BEGIN 
  --1、使用简单pl/sql变量v_id,v_name获得单行输出 
  v_sql := ' select id,name from ' || v_table || ' where id=:1 '; 
  EXECUTE IMMEDIATE v_sql 
    INTO v_id, v_name 
    USING '1'; 
  DBMS_OUTPUT.put_line('id=' || v_id || ',name=' || v_name); 
  --2、使用基于test_ynamic_sql表的记录变量获得单行输出 
  EXECUTE IMMEDIATE v_sql 
    INTO test_ynamic_sql_row 
    USING '1'; 
  DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_row.v_id || ',name=' || 
                       test_ynamic_sql_row.v_name); 
END;

1.2.3.2       返回多行值
1.2.3.2.1      使用记录表获取
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
  --1、使用基于test_ynamic_sql表的记录变量获得多行输出 
  TYPE test_ynamic_sql_record IS RECORD( 
    id   test_ynamic_sql.ID%TYPE, 
    NAME test_ynamic_sql.NAME%TYPE); 
  TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql_record INDEX BY BINARY_INTEGER; 
  /*可以用以下方式定义记录表*/ 
  --TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql%ROWTYPE INDEX BY BINARY_INTEGER; 
  test_ynamic_sql_multi_row test_ynamic_sql_table_type; 
BEGIN 
  --1、使用基于test_ynamic_sql表的记录变量获得多行输出 
  v_sql := ' select id,name from ' || v_table; 
  EXECUTE IMMEDIATE v_sql BULK COLLECT 
    INTO test_ynamic_sql_multi_row; 
  FOR m IN 1 .. test_ynamic_sql_multi_row.COUNT LOOP 
    DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row(m) 
                         .id || ',name=' || test_ynamic_sql_multi_row(m).NAME); 
  END  LOOP; 
END; 

1.2.3.2.2      使用多个嵌套表获取
DECLARE 
  v_sql   VARCHAR2(1000); 
  v_table VARCHAR2(30) := 'test_ynamic_sql'; 
  --1、使用基于多个嵌套表获取多行输出 
  TYPE test_ynamic_sql_id_type IS TABLE OF test_ynamic_sql.ID%TYPE INDEX BY BINARY_INTEGER; 
  TYPE test_ynamic_sql_name_type IS TABLE OF test_ynamic_sql.NAME%TYPE INDEX BY BINARY_INTEGER; 
  test_ynamic_sql_multi_row_id   test_ynamic_sql_id_type; 
  test_ynamic_sql_multi_row_name test_ynamic_sql_name_type; 
BEGIN 
  --1、使用基于多个嵌套表获取多行输出 
  v_sql := ' select id,name from ' || v_table; 
  EXECUTE IMMEDIATE v_sql BULK COLLECT 
    INTO test_ynamic_sql_multi_row_id, test_ynamic_sql_multi_row_name; 
  FOR m IN 1 .. test_ynamic_sql_multi_row_id.COUNT LOOP 
    DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row_id(m) || 
                         ',name=' || test_ynamic_sql_multi_row_name(m)); 
  END LOOP; 
END; 

1.2.4        动态调用函数
1.2.4.1       使用select 获取返回值
DECLARE 
  v_sql  VARCHAR2(1000); 
  v_name VARCHAR2(100); 
BEGIN 
  --1、先创建测试函数 
  v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2) RETURN VARCHAR2 IS 
             v_name VARCHAR2(100); 
               BEGIN 
               SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id; 
             RETURN v_name; 
            END ; '; 
  EXECUTE IMMEDIATE v_sql; 
  --2、  使用select 获取返回值 
  v_sql := ' select f_test_ynamic_sql(:1) from dual'; 
  EXECUTE IMMEDIATE v_sql 
    INTO v_name 
    USING '1'; 
  DBMS_OUTPUT.put_line(' NAME = ' || v_name); 
END; 

1.2.4.2       使用begin .. end绑定函数输出变量
DECLARE 
  v_sql    VARCHAR2(1000); 
  v_name_o VARCHAR2(100); 
BEGIN 
  --1、先创建测试函数 
  v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) RETURN VARCHAR2 IS 
             v_name VARCHAR2(100); 
               BEGIN 
               SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id; 
               v_name_o:=v_name; 
             RETURN v_name; 
            END ; '; 
  EXECUTE IMMEDIATE v_sql; 
  --2、使用begin .. end绑定函数输出变量 
  v_sql := ' declare v_name varchar2(100);  
           begin  v_name:=f_test_ynamic_sql(:1,:2); end;'; 
  EXECUTE IMMEDIATE v_sql 
    USING '1', OUT v_name_o; 
  DBMS_OUTPUT.put_line('name_o=' || v_name_o); --using中的输出变量需要显示说明 
END; 

1.2.5        动态调用过程
DECLARE 
  v_sql    VARCHAR2(1000); 
  v_name_o VARCHAR2(100); 
BEGIN 
  --1、先创建测试过程 
  v_sql := ' CREATE OR REPLACE procedure p_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) IS 
               BEGIN 
               SELECT NAME INTO v_name_o FROM test_ynamic_sql WHERE id = v_id; 
            END ; '; 
  EXECUTE IMMEDIATE v_sql; 
  --2、使用begin .. end绑定过程输出变量 
  v_sql := ' begin  p_test_ynamic_sql(:1,:2); end;'; 
  EXECUTE IMMEDIATE v_sql 
    USING '1', OUT v_name_o; --using中的输出变量需要显示说明 
  DBMS_OUTPUT.put_line('name_o=' || v_name_o); 
END;