使用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;