Oracle12c——function（1）

最新推荐文章于 2024-08-11 09:17:34 发布

浅笑_人

最新推荐文章于 2024-08-11 09:17:34 发布

阅读量532

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/life_create/article/details/100191564

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

         ------Oracle12c  动态查询------
CREATE OR REPLACE FUNCTION F_cursor_select (p_no in varchar2)
        RETURN VARCHAR2
                     AS 
                     row_process    varchar2(60);
                     v_a           varchar2(20);
                     v_b           varchar2(20);
                     v_c           NUMBER;
                     v_d           NUMBER;
                     v_cursor       NUMBER;
                    -- p_no           varchar2(20);
BEGIN
  v_cursor := dbms_sql.open_cursor;  --打开游标
 -- p_no     :='10';
  begin
    --动态解析sql语句
    dbms_sql.parse(v_cursor,
                   'select stuid,stuname,sex,age from stuinfo  where stuid= :no',
                   dbms_sql.native);
    --绑定输入参数；
    dbms_sql.bind_variable(v_cursor,':no',p_no);
     --定义列
    --如果是char或者varchar2类型,则必须指定最后一个参数（制定字符串类型的长度，为BYTE）
    --SIMPLIFIED CHINESE_CHINA.ZHS16GBK     :   一个汉字占用两个字节
    --SIMPLIFIED CHINESE_CHINA.AL32UTF8     :   一个汉字占用三个字节
    dbms_sql.define_column(v_cursor, 1, v_a,10);
    dbms_sql.define_column(v_cursor, 2, v_b,10);
    dbms_sql.define_column(v_cursor, 3, v_c);
    dbms_sql.define_column(v_cursor, 4, v_d);
    --执行动态SQL语句
    row_process := dbms_sql.execute(v_cursor);
    LOOP
      --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1
      IF (dbms_sql.fetch_rows(v_cursor)) > 0 THEN
        --将当前行的查询结果写入上面定义的列中。
        dbms_sql.column_value(v_cursor,1,v_a);
        dbms_sql.column_value(v_cursor,2,v_b);
        dbms_sql.column_value(v_cursor,3,v_c);
        dbms_sql.column_value(v_cursor,4,v_d);
        --打印输出
        dbms_output.put_line(';'||row_process||';');
      ELSE
        EXIT;
        
      END IF;
    END LOOP;
    --关闭游标
    dbms_sql.close_cursor(v_cursor);
    commit;
    return v_a || ';' || v_b || ';' || v_c || ';' || v_d;
  end;
END F_cursor_select;

         ------Oracle12c  动态插入------
CREATE OR REPLACE FUNCTION F_cursor_insert (b_stuid in varchar2,b_stuname in varchar2,b_sex in number,b_age in number)
        RETURN VARCHAR2   AS
                     row_process    varchar2(10);
                     v_cursor       NUMBER;
                     v_sql         varchar2(200);
                   --  b_stuid       varchar2(20);
                   --  b_stuname     varchar2(20);
                   --  b_sex         number;
                   --  b_age         number;
BEGIN
  --b_stuid = '70';
  --b_stuname = '龙泣';
  --b_sex = 1;
  --b_age = 17;
  v_cursor := dbms_sql.open_cursor;
  v_sql := 'insert into stuinfo (stuid,stuname,sex,age) values
            (:p_stuid,:p_stuname,:p_sex,:p_age)';
  
  begin

      dbms_sql.parse(v_cursor,
                     v_sql,
                     dbms_sql.native);
     
      dbms_sql.bind_variable(v_cursor,':p_stuid',b_stuid);
      dbms_sql.bind_variable(v_cursor,':p_stuname',b_stuname);
      dbms_sql.bind_variable(v_cursor,':p_sex',b_sex);
      dbms_sql.bind_variable(v_cursor,':p_age',b_age);

      row_process := dbms_sql.execute(v_cursor);

      dbms_sql.close_cursor(v_cursor);
      commit;
      return  row_process;
  end;
END F_cursor_insert;

                 -------动态更新------
CREATE OR REPLACE FUNCTION F_cursor_update (b_stuid in varchar2,b_stuname in varchar2,b_age in number)
        RETURN VARCHAR2   AS
                     row_process   varchar2(10);
                     v_cursor      NUMBER;
                     v_sql         varchar2(300);
                     --b_stuid       varchar2(10);
                     --b_stuname     varchar2(10);
                     --b_age         NUMBER;
BEGIN
  --b_stuid := 10;
  --b_stuname := '10';
  --b_age := 10;
  
  v_cursor := dbms_sql.open_cursor;

  v_sql := 'update stuinfo set stuname = :v_stuname , age = :v_age where stuid = :v_stuid';

  BEGIN

    dbms_sql.parse(v_cursor,
                   v_sql,
                   dbms_sql.native);
   
    dbms_sql.bind_variable(v_cursor,':v_stuid',b_stuid);
    dbms_sql.bind_variable(v_cursor,':v_stuname',b_stuname);
    dbms_sql.bind_variable(v_cursor,':v_age',b_age);
   
    row_process := dbms_sql.execute(v_cursor);

    dbms_sql.close_cursor(v_cursor);
    commit;
    return row_process;
  end;
END F_cursor_update;

                    ------动态删除------
CREATE OR REPLACE FUNCTION f_cursor_delete (b_stuid in varchar2)
        RETURN VARCHAR2   AS
                     row_process    varchar2(10);
                     v_cursor       NUMBER;
                     v_sql         varchar2(200);
BEGIN
  
  v_cursor := dbms_sql.open_cursor;

  v_sql := 'delete from stuinfo where stuid = :v_stuid';

  begin

  dbms_sql.parse(v_cursor,
                 v_sql,
                 dbms_sql.native);
 
  dbms_sql.bind_variable(v_cursor,':v_stuid',b_stuid);
  row_process := dbms_sql.execute(v_cursor);

 
  dbms_sql.close_cursor(v_cursor);
 
  return row_process;
  end;
END f_cursor_delete;

--------------游标--------return t_table---------------------
create or replace function f_return_table1 (sqlstr IN VARCHAR2) return t_table
       IS
    --创建一个类型变量cur，它引用游标
    --TYPE cur：定义类型变量
    --is ref cursor：相当于数据类型，不过是引用游标的数据类型。
    TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标
    users ref_cursor_type;   --定义游标类型
    --user obj_table; --定义变量类型， 这里的users是表明
    vSql VARCHAR2(255); --sql文本
    o_tb t_table := t_table();
    v_id varchar(20);
    v_cd_name varchar(50);
i number := 0;
BEGIN
  vSql := sqlstr; --要查询的sql字符串，可拼接起来
  OPEN users FOR  vSql;  --打开游标
  LOOP
    FETCH users INTO v_id,v_cd_name; --循环遍历users列表给user，user为临时对象
    exit when users%notfound; 

 o_tb.extend;
    i := i + 1;
    o_tb(i) := obj_table (v_cd_name,v_id);
    --dbms_output.put_line(o_tb);
  end loop;

  CLOSE users;
 
  return o_tb;
  end;
--select * from table(c##zhaojian.f_return_table('select stuid,stuname from stuinfo'));  查询结果


-----------------------------------------------------------------------
CREATE OR REPLACE TYPE "T_TABLE"
  is table of obj_table
------------------------------------------------------------------------
create or replace type OBJ_TABLE as object
(
   id VARCHAR2(20),
   name VARCHAR2(1000)
)
------------------------------------------------------------------------

--------------游标-----------管道------------return t_table-----------------
create or replace function f_return_table2_guandao (sqlstr IN VARCHAR2) return t_table
   pipelined  as
    --创建一个类型变量cur，它引用游标
    --TYPE cur：定义类型变量
    --is ref cursor：相当于数据类型，不过是引用游标的数据类型。
    TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标
    users ref_cursor_type;   --定义游标类型
    --user obj_table; --定义变量类型， 这里的users是表明
    vSql VARCHAR2(255); --sql文本
    --o_tb t_table := t_table();
    v_id varchar(20);
    v_cd_name varchar(50);
    --i number := 0;
BEGIN
  vSql := sqlstr; --要查询的sql字符串，可拼接起来
  OPEN users FOR  vSql;  --打开游标
  LOOP
    FETCH users INTO v_id,v_cd_name; --循环遍历users列表给user，user为临时对象
     
    exit when users%notfound; 
    
    pipe row(obj_table (v_id,v_cd_name));
  end loop;

  CLOSE users;
 
  return;
  end;
--select * from table(c##zhaojian.f_return_table('select stuid,stuname from stuinfo'));  查询结果
-----------------------------------------------------------------------
CREATE OR REPLACE TYPE "T_TABLE"
  is table of obj_table
-----------------------------------------------------------------------
create or replace type OBJ_TABLE as object
(
   id VARCHAR2(20),
   name VARCHAR2(1000)
)
------------------------------------------------------------------------

-----------------------管道-----------------查询数据------------
create or replace function f_return_guandao (pIdIn NVARCHAR2  )
   return T_TABLE pipelined --管道
as
  v OBJ_TABLE;
begin
  for thisrow in (
    SELECT  stuid,stuname FROM stuinfo
    start with stuid=pIdIn connect by stuname=prior stuid)loop
    v:=OBJ_TABLE(thisrow.stuid,thisrow.stuname);
    pipe row(v);
  end loop;
  return;
end;
--select * from table(F_Test(10));    查询结果

--------------本地动态sql------------相当于游标进行查询--------
create or replace function F_LOCAL_EXECUTE
  return varchar2 is
  L_VLAUES varchar2(200);
  V_TIME number;
  V_STATEMENT varchar2(200);
begin
      V_TIME := 1;
            V_STATEMENT :=
                  'SELECT MAX(stuid) FROM stuinfo  M WHERE M.sex = :1';

            EXECUTE IMMEDIATE V_STATEMENT
                         INTO L_VLAUES
                        USING V_TIME;
  return L_VLAUES;
end F_LOCAL_EXECUTE;