SQL参考

最新推荐文章于 2005-12-07 15:08:00 发布
最新推荐文章于 2005-12-07 15:08:00 发布
阅读量1.8k 收藏
点赞数
分类专栏： oracle/sql 文章标签： sql user date null class search
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xuandhu/article/details/422249
版权

 

 

create or replace package body test_pkg is
  --- function comment 
  --#param in_number1  comment
  --#result  datatype comment
  procedure demo(in_name in varchar2, in_address in varchar2) is
    --l_name varchar2(2000);
    -- l_pos number(2);
    l_address varchar2(100);
  begin
    -- l_name := substr(in_name,1,3) || in_address ;
    -- l_pos := instr(in_name,'123');
    -- dbms_output.put_line('姓名前缀:' || l_name);
    -- dbms_output.put_line('位置:' || l_pos);
    l_address := in_address;
    dbms_output.put_line(l_address);
    dbms_output.put_line(in_name);
    if (trim(upper(in_name)) = 'OK') then
      dbms_output.put_line('ok');
    elsif (trim(upper(in_name)) = 'SORRY') then
      dbms_output.put_line('sorry');
    else
      dbms_output.put_line('other');
    end if;
  end demo;
  --- function comment 
  --#param in_number1  comment
  --#result  datatype comment
  function test return number is
  begin
    return 1;
  end test;
  --- function comment 
  --#param in_number1  comment
  --#result  datatype comment
  function test(in_number1 in number) return varchar2 is
  begin
    return to_char(in_number1);
  end test;

  procedure add_test_user(in_user_id   in s_user.user_id%type,
                          in_user_name in s_user.user_name%type,
                          in_user_psw  in s_user.user_psw%type) is
    l_search_cur helper_pkg.ref_cur;
    l_user_id    s_user.user_id%type;
    l_count      number(3);
  begin
    open l_search_cur for
      select s_user_id from s_user where user_id = in_user_id;
    fetch l_search_cur
      into l_user_id;
    --- select count(*) into l_count from s_user where s_user_Id  = in_user_Id;
    l_count := l_search_cur%rowcount; -- 重复工号的数量
    DBMS_OUTPUT.put_line(L_COUNT); --- 显示重复工号数量
    if (l_search_cur%notfound) then
      insert into s_user
        (s_user_id, user_id, user_name, user_psw)
      values
        (helper_pkg.new_id('S_USER'),
         in_user_id,
         in_user_name,
         in_user_psw);
    else
      error_pkg.handle_exception(constant_pkg.double_user_id);
    end if;
 
  end;

  ---function comment
  --# param  paramname param comment
  function get_user_psw(in_user_id in s_user.user_id%type)
    return s_user.user_psw%type is
  
    rec          s_user%rowtype;
    l_search_cur helper_pkg.ref_cur;
    l_user_psw   s_user.user_psw%type;
    l_result     s_user.user_psw%type;
   
  begin
    select * into rec from s_user where s_user_id = 1;
    dbms_output.put_line('测试数据:' || rec.user_id);
    open l_search_cur for
      select user_psw
        from s_user
       where upper(trim(user_id)) = upper(trim(in_user_id));
    fetch l_search_cur
      into l_user_psw;
    if (l_search_cur%notfound) then
      l_result := l_user_psw;
    else
      l_result := 'sorry';
    end if;
 
    for info in (select * from s_user) loop
      dbms_output.put_line('信息：' || info.user_name || ',' ||
                           info.user_id);
   
    end loop;
 
    loop
      fetch l_search_cur
        into l_user_psw;
      exit when l_search_cur%notfound;
      dbms_output.put_line(l_user_psw);
    end loop;
 
    return l_result;
  end get_user_psw;

  procedure update_user_info(in_s_user_Id in s_user.s_user_id%type,
                             in_user_name in s_user.user_name%type) is
    l_user_psw s_user.user_psw%type;
  begin
    update s_user
       set user_name = in_user_name
     where s_user_id = in_s_user_id returning user_psw into l_user_psw;
    commit;
    dbms_output.put_line(l_user_psw);
  end update_user_info;

  --------------------------------
  ---字符串解析函数,动态SQL 字段解析
  -- #param in_fied 字段名
  --#param in_pkey 传入字符串
  --#out out_result 返回的结果
  --说明  : in_field = 'pkey';
  --in_pkey := '字符1*字符2+字符3*字符4*字符5';
  --parse_string (in_field,in_pkey,out_result);
  -- out_result := '(pkey like %字符1% and pkey like%字符2% )  or (pkey like %字符3% and pkey like %字符4%  and pkey like %字符5%)'
  PROCEDURE parse_string(in_field   IN VARCHAR2,
                         in_pkey    IN VARCHAR2,
                         out_result OUT VARCHAR2) IS
    pkey           VARCHAR2(2000);
    temp           NUMBER(3);
    andsymbolcount NUMBER(3) := 0;
    orsymbolcount  NUMBER(3) := 0;
    cnt            NUMBER(4) := 0;
    step           NUMBER(3) := 1; -----循环计数器
    andsql         VARCHAR2(2000) := '';
    --orsql varchar2(2000) := '';
    symbolflag NUMBER(1);
    curpos     NUMBER(3);
    --tempflag   number(1);
    prefix  NUMBER(3) := 0;
    preflag NUMBER(1) := 2;
    v_len   NUMBER(5);
  BEGIN
    ---------去掉前后的符号
    andsql := '';
    pkey   := TRIM(in_pkey);
    pkey   := REPLACE(pkey, '　', ' ');
    temp   := instr(pkey, '*');
    IF (temp = 1) THEN
      pkey := substr(pkey, 2);
    END IF;
    temp := instr(pkey, '+');
    IF (temp = 1) THEN
      pkey := substr(pkey, 2);
    END IF;
    temp := instr(in_pkey, '*', -1);
    IF (temp = length(pkey)) THEN
      pkey := substr(pkey, 1, length(pkey) - 1);
    END IF;
    temp := instr(in_pkey, '+', -1);
    IF (temp = length(pkey)) THEN
      pkey := substr(pkey, 1, length(pkey) - 1);
    END IF;
    v_len := length(pkey);
    ------------*/+符号个数-------------------------
    andsymbolcount := get_count_flag(pkey, '*');
    orsymbolcount  := get_count_flag(pkey, '+');
    cnt            := andsymbolcount + orsymbolcount;
    IF (cnt = 0) THEN
      out_result := in_field || ' like ''%' || TRIM(pkey) || '%'' ';
      RETURN;
    END IF;
    IF (cnt = 1) THEN
      get_symbol_pos(pkey, step, preflag, prefix);
      IF (preflag = 0) THEN
        out_result := in_field || ' like ''%' ||
                      TRIM(substr(pkey, 0, prefix - 1)) || '%'' and ' ||
                      in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, v_len - prefix)) ||
                      '%''';
      ELSE
        out_result := in_field || ' like ''%' ||
                      TRIM(substr(pkey, 0, prefix - 1)) || '%'' or ' ||
                      in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, v_len - prefix)) ||
                      '%''';
      END IF;
      RETURN;
    END IF;
    temp := 0;
    WHILE (step < cnt) LOOP
      get_symbol_pos(pkey, step, preflag, prefix);
      get_symbol_pos(pkey, step + 1, symbolflag, curpos);
      IF (step > 1) THEN
        IF (preflag = 0 AND symbolflag = 0) THEN
          ---asdf*addff*asdfsf*asdf
          andsql := andsql || ' and ' || in_field || ' like ''%' ||
                    TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                    '%'' ';
        ELSIF (preflag = 0 AND symbolflag = 1) THEN
          ---asdf*addff*asdfsf+asdf
          andsql := andsql || ' and ' || in_field || ' like ''%' ||
                    TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                    '%'') ';
        ELSIF (preflag = 1 AND symbolflag = 0) THEN
          andsql := andsql || ' or  (' || in_field || ' like ''%' ||
                    TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                    '%''   ';
        ELSIF (preflag = 1 AND symbolflag = 1) THEN
          andsql := andsql || ' or ' || in_field || ' like ''%' ||
                    TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                    '%'' ';
        END IF;
      ELSE
        -------- step = 1
        IF (preflag = 1) THEN
          andsql := andsql || in_field || ' like ''%' ||
                    TRIM(substr(pkey, temp, prefix - temp - 1)) || '%''  ';
          IF (symbolflag = 1) THEN
            andsql := andsql || ' or ' || in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                      '%'' ';
          ELSE
            andsql := andsql || ' or ( ' || in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                      '%'' ';
          END IF;
        ELSIF (preflag = 0) THEN
          andsql := andsql || '(' || in_field || ' like ''%' ||
                    TRIM(substr(pkey, temp, prefix - temp - 1)) || '%'' ';
          IF (symbolflag = 0) THEN
            andsql := andsql || '  and  ' || in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                      '%'' ';
          ELSE
            andsql := andsql || ' and ' || in_field || ' like ''%' ||
                      TRIM(substr(pkey, prefix + 1, curpos - prefix - 1)) ||
                      '%'') ';
          END IF;
        END IF;
      END IF;
      step := step + 1;
    END LOOP;
    IF (preflag = 0) THEN
      IF (symbolflag = 0) THEN
        andsql := andsql || ' and ' || in_field || ' like ''%' ||
                  TRIM(substr(pkey, curpos + 1, v_len - curpos)) || '%'') ';
      ELSE
        andsql := andsql || ' or ' || in_field || ' like ''%' ||
                  TRIM(substr(pkey, curpos + 1, v_len - curpos)) || '%'' ';
      END IF;
    ELSE
      IF (symbolflag = 0) THEN
        andsql := andsql || ' and  ' || in_field || ' like ''%' ||
                  TRIM(substr(pkey, curpos + 1, v_len - curpos)) || '%'') ';
      ELSE
        andsql := andsql || ' or ' || in_field || ' like ''%' ||
                  TRIM(substr(pkey, curpos + 1, v_len - curpos)) || '%'' ';
      END IF;
    END IF;
    out_result := andsql;
 
  END;

  ------------------------------------------------
  --参数说明
  --#param in_type = 'A' 表示对整个标准体系搜索
  --#param in_type ='B' 表示对自定组织方式尽心搜索
  --#param in_type = 'C' 表示对个人收藏夹进行搜索
  ----------搜索功能
  /* PROCEDURE get_search_data(in_doc_name    IN doc_file.doc_name%TYPE,
                            in_doc_no      IN doc_file.doc_no%TYPE,
                            in_pkey        IN doc_file.pkey%TYPE,
                            in_empno       IN employee.emp_no%TYPE,
                            in_type        IN VARCHAR2,
                            in_startpos    IN NUMBER,
                            out_search_cur OUT common_pkg.ref_cur) IS
     v_empid   employee.emp_id%TYPE;
     v_isadmin NUMBER(1);
     v_dysql   VARCHAR2(2000);
     pkey      VARCHAR2(1000);
     preval    VARCHAR2(2000);
     v_name    doc_file.doc_name%TYPE;
     v_no      doc_file.doc_no%TYPE;
  BEGIN
     emp_pkg.get_emp_id(in_empno, v_empid);
     emp_pkg.isadmin(in_empno, v_isadmin);
    
     v_dysql := 'select doc_id, doc_no, doc_name, file_path,encrypt_path, doc_size, use_date, publish_unit, lastmodify_date, upload_date, file_format, NULL region, remark,';
     v_dysql := v_dysql ||
                ' doc_attrib,is_audit,confirm_date,verify_empid,use_no,use_level,english_name,pkey,gj_level from doc_file ';
     IF (upper(TRIM(in_type)) = 'A') THEN
        IF (in_startpos <> 0) THEN
           v_dysql := v_dysql ||
                      '  where doc_id in (select doc_id from doc_file where class_id in (select child_id from rs_class  start with parent_id =' ||
                      to_char(in_startpos) ||
                      ' connect by prior child_id = parent_id union select ' ||
                      to_char(in_startpos) || '  from dual  ))';
        END IF;
     ELSIF (upper(TRIM(in_type)) = 'B') THEN
        v_dysql := v_dysql ||
                   ' where  doc_id in (select doc_id from org_class_to_file where org_class_id in (select org_class_id from org_class  start with parent_id = ' ||
                   to_char(in_startpos) ||
                   ' connect by prior org_class_id = parent_id union select ' ||
                   to_char(in_startpos) || '  from dual  ))';
     ELSIF (upper(TRIM(in_type)) = 'C') THEN
        v_dysql := v_dysql ||
                   ' where doc_id in ( select class_id from myfolder  where region = ''S'' and  emp_id = ' ||
                   to_char(v_empid) || '  start with parent_id = ' ||
                   to_char(in_startpos) ||
                   ' connect  by prior  folder_id = parent_id  ' ||
                   ' union select folder_id from myfolder where region =''S'' and emp_id = ' ||
                   to_char(v_empid) || ' and folder_id = ' ||
                   to_char(in_startpos) || ' )';
     END IF;
     ---(('''||in_doc_name || '''  is null) or ('''||in_doc_name || ''' is not null and doc_name like ''%'||in_doc_name || '%''));
     IF (upper(TRIM(in_empno)) = 'EVERYONE') THEN
        ----如果是公开访问角色
        IF (in_startpos = 0) THEN
           preval := '  where   doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class where role_id = -1)) and is_validate = ''T'' and cur_state = 1 and is_audit = 1';
        ELSE
           preval := '  and   doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class where role_id = -1)) and is_validate = ''T'' and cur_state = 1 and is_audit = 1';
        END IF;
        v_dysql := v_dysql || preval;
        IF (in_pkey IS NOT NULL) THEN
           parse_string('PKEY', in_pkey, pkey);
           v_dysql := v_dysql || ' and (' || pkey || ')' ;
        END IF;
        IF (in_doc_name IS NOT NULL) THEN
           parse_string('DOC_NAME', in_doc_name, v_name);
           v_dysql := v_dysql || ' and (' || v_name || ')' ;
        END IF;
        IF (in_doc_no IS NOT NULL) THEN
           parse_string('DOC_NO', upper(in_doc_no), v_no);
           v_dysql := v_dysql || ' and (' || v_no || ')' ;
        END IF;
        OPEN out_search_cur FOR v_dysql;
        RETURN;
     END IF;
     IF (v_isadmin = 1) THEN
        ---系统管理员
        IF (upper(TRIM(in_type)) <> 'C') THEN
           IF (in_startpos = 0) THEN
              preval := '  where   is_validate = ''T'' and cur_state = 1 ';
           ELSE
              preval := '  and  is_validate = ''T'' and cur_state = 1 ';
           END IF;
        ELSE
           preval := '  and  is_validate = ''T'' and cur_state = 1 ';
        END IF;
     ELSE
        ----非系统管理员
        IF (upper(TRIM(in_type)) <> 'C') THEN
           IF (in_startpos = 0) THEN
              preval := '  where   doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class ,rs_role_user where rs_role_class.role_id = -1 or( rs_role_user.role_id = rs_role_class.role_id and rs_role_user.emp_id = ' ||
                        to_char(v_empid) ||
                        ')))  and is_validate = ''T'' and cur_state = 1 ';
           ELSE
              preval := '  and   doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class ,rs_role_user where rs_role_class.role_id = -1 or( rs_role_user.role_id = rs_role_class.role_id and rs_role_user.emp_id = ' ||
                        to_char(v_empid) ||
                        ')))  and is_validate = ''T'' and cur_state = 1 ';
           END IF;
        ELSE
           preval := '  and   doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class ,rs_role_user where rs_role_class.role_id = -1 or( rs_role_user.role_id = rs_role_class.role_id  and rs_role_user.emp_id = ' ||
                     to_char(v_empid) ||
                     ')))  and is_validate = ''T'' and cur_state = 1 ';
        END IF;
     END IF;
     v_dysql := v_dysql || preval;
     IF (in_pkey IS NOT NULL) THEN
        parse_string('PKEY', in_pkey, pkey);
        v_dysql := v_dysql || ' and (' || pkey || ')' ;
     END IF;
     IF (in_doc_name IS NOT NULL) THEN
        parse_string('DOC_NAME', in_doc_name, v_name);
        v_dysql := v_dysql || ' and (' || v_name || ')' ;
     END IF;
     IF (in_doc_no IS NOT NULL) THEN
        parse_string('DOC_NO', upper(in_doc_no), v_no);
        v_dysql := v_dysql || ' and (' || v_no || ')' ;
     END IF;
     OPEN out_search_cur FOR v_dysql;
    
  END;
 
  -------------高级搜索
  PROCEDURE get_advance_search_data(in_name          IN VARCHAR2,
                                    in_doc_no        IN VARCHAR2,
                                    in_pub           IN VARCHAR2,
                                    in_attr          IN VARCHAR2,
                                    in_desp          IN VARCHAR2,
                                    in_crtbegin_date IN VARCHAR2,
                                    in_crtend_date   IN VARCHAR2,
                                    in_pubbegin_date IN VARCHAR2,
                                    in_pubend_date   IN VARCHAR2,
                                    in_emp_no        IN employee.emp_no%TYPE,
                                    in_creator       IN VARCHAR2,
                                    in_pkey          IN VARCHAR2,
                                    in_type          IN VARCHAR2,
                                    in_startpos      IN NUMBER,
                                    out_search_cur   OUT common_pkg.ref_cur) IS
     tmp_emp_id     employee.emp_id%TYPE;
     crt_begin_date VARCHAR2(30);
     crt_end_date   VARCHAR2(30);
     pub_begin_date VARCHAR2(30);
     pub_end_date   VARCHAR2(30);
     creator_id     employee.emp_id%TYPE;
     v_isadmin      NUMBER(1);
     v_dysql        VARCHAR2(4000);
     pkey           VARCHAR2(1000);
     preval         VARCHAR2(2000);
     v_name         doc_file.doc_name%TYPE;
  BEGIN
     emp_pkg.get_emp_id(in_emp_no, tmp_emp_id);
     emp_pkg.isadmin(in_emp_no, v_isadmin);
     IF (in_creator IS NOT NULL) THEN
        emp_pkg.get_emp_id(in_creator, creator_id);
     END IF;
     ------创建日期
     IF (in_crtbegin_date IS NULL) THEN
        crt_begin_date := '1900-01-01 00:00:01';
     ELSE
        crt_begin_date := in_crtbegin_date || ' 00:00:01';
     END IF;
     IF (in_crtend_date IS NULL) THEN
        crt_end_date := '3002-01-01 23:59:59';
     ELSE
        crt_end_date := in_crtend_date || ' 23:59:59';
     END IF;
     ------实施日期
     IF (in_pubbegin_date IS NULL) THEN
        pub_begin_date := '1900-01-01 00:00:01';
     ELSE
        pub_begin_date := in_pubbegin_date || ' 00:00:01';
     END IF;
     IF (in_pubend_date IS NULL) THEN
        pub_end_date := '3002-01-01 23:59:59';
     ELSE
        pub_end_date := in_pubend_date || ' 23:59:59 ';
     END IF;
     v_dysql := 'select doc_id, doc_no, doc_name, file_path,encrypt_path, doc_size, use_date, publish_unit, lastmodify_date, upload_date, file_format, NULL region, remark,';
     v_dysql := v_dysql ||
                ' doc_attrib,is_audit,confirm_date,verify_empid,use_no,use_level,english_name,pkey,gj_level from doc_file ';
     v_dysql := v_dysql || ' where  ((''' || in_doc_no ||
                ''' is null) or (''' || in_doc_no ||
                ''' is not null and doc_no like ''%' || upper(in_doc_no) ||
                '%'')) and ';
     v_dysql := v_dysql || '((''' || in_pub || ''' is null) or (''' ||
                in_pub || ''' is not null and publish_unit like ''%' ||
                in_pub || '%'')) and';
     v_dysql := v_dysql || '((''' || in_attr || ''' is null) or (''' ||
                in_attr || ''' is not null and doc_attrib like ''%' ||
                in_attr || '%'')) and ';
     v_dysql := v_dysql || '((''' || in_desp || ''' is null) or (''' ||
                in_desp || ''' is not null and remark like ''%' || in_desp ||
                '%'')) and  ';
     v_dysql := v_dysql || '(upload_date >= to_date(''' || crt_begin_date ||
                ''',''yyyy-mm-dd HH24:mi:ss'')  AND upload_date<=to_date( ''' ||
                crt_end_date || ''',''yyyy-mm-dd HH24:mi:ss'')  ) and ';
     v_dysql := v_dysql || '( use_date>=to_date(''' || pub_begin_date ||
                ''',''yyyy-mm-dd HH24:mi:ss'')  AND use_date<=to_date(''' ||
                pub_end_date || ''',''yyyy-mm-dd HH24:mi:ss'') )';
     -----------判断创建人
     IF (in_creator IS NOT NULL) THEN
        v_dysql := v_dysql || ' and  emp_id = ' || to_char(creator_id);
     END IF;
     -------------根据类别搜索----------------------------
     IF (upper(TRIM(in_type)) = 'A') THEN
        IF (in_startpos <> 0) THEN
           v_dysql := v_dysql ||
                      '   and  doc_id in (select doc_id from doc_file where  class_id in (select child_id from rs_class  start with parent_id =' ||
                      to_char(in_startpos) ||
                      ' connect by prior child_id = parent_id union select ' ||
                      to_char(in_startpos) || '  from dual  ))';
        END IF;
     ELSIF (upper(TRIM(in_type)) = 'B') THEN
        v_dysql := v_dysql ||
                   ' and   doc_id in (select doc_id from org_class_to_file where org_class_id in (select org_class_id from org_class  start with parent_id = ' ||
                   to_char(in_startpos) ||
                   ' connect by prior org_class_id = parent_id union select ' ||
                   to_char(in_startpos) || '  from dual  ))';
     ELSIF (upper(TRIM(in_type)) = 'C') THEN
        v_dysql := v_dysql ||
                   ' and  doc_id in ( select class_id from myfolder  where region = ''S'' and  emp_id = ' ||
                   to_char(tmp_emp_id) || '  start with parent_id = ' ||
                   to_char(in_startpos) ||
                   ' connect  by prior  folder_id = parent_id  ' ||
                   ' union select folder_id from myfolder where region =''S'' and emp_id = ' ||
                   to_char(tmp_emp_id) || ' and folder_id = ' ||
                   to_char(in_startpos) || ' )';
     END IF;
     --------------------------------------------------
     IF (upper(TRIM(in_emp_no)) = 'EVERYONE') THEN
        preval := ' and doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class where role_id = -1)) and is_validate = ''T'' and cur_state = 1 and is_audit = 1';
        IF (in_pkey IS NOT NULL) THEN
           parse_string('PKEY', in_pkey, pkey);
           v_dysql := v_dysql || ' and (' || pkey || ')' ;
        END IF;
        IF (in_name IS NOT NULL) THEN
           parse_string('DOC_NAME', in_name, v_name);
           v_dysql := v_dysql || ' and (' || v_name || ')';
        END IF;
        v_dysql := v_dysql || preval;
        OPEN out_search_cur FOR v_dysql;
        RETURN;
     END IF;
     IF (v_isadmin = 1) THEN
        ---系统管理员
        preval := ' and is_validate = ''T'' and cur_state = 1 ';
     ELSE
        ----非系统管理员
        preval := ' and doc_id in (select doc_id from doc_file where class_id in (select class_id from rs_role_class ,rs_role_user where rs_role_class.role_id = -1 or ( rs_role_user.role_id = rs_role_class.role_id and rs_role_user.emp_id = ' ||
                  to_char(tmp_emp_id) ||
                  ')))  and is_validate = ''T'' and cur_state = 1';
     END IF;
     IF (in_pkey IS NOT NULL) THEN
        parse_string('PKEY', in_pkey, pkey);
        v_dysql := v_dysql || ' and ' || pkey;
     END IF;
     IF (in_name IS NOT NULL) THEN
        parse_string('DOC_NAME', in_name, v_name);
        v_dysql := v_dysql || ' and ' || v_name;
     END IF;
     v_dysql := v_dysql || preval;
     OPEN out_search_cur FOR v_dysql;
  END;*/

  FUNCTION get_count_flag(in_pkey IN VARCHAR2, in_symbol IN VARCHAR2)
    RETURN NUMBER IS
    ret  NUMBER(3) := 0;
    i    NUMBER(5);
    flag BOOLEAN;
  BEGIN
    i    := instr(in_pkey, in_symbol);
    flag := i > 0;
    WHILE (flag) LOOP
      ret  := ret + 1;
      i    := instr(in_pkey, in_symbol, i + 1);
      flag := (i > 0);
    END LOOP;
    RETURN ret;
  END;

  PROCEDURE get_symbol_pos(in_pkey   IN VARCHAR2,
                           in_order  IN NUMBER,
                           in_symbol OUT NUMBER,
                           curpos    OUT NUMBER) IS
    andpos NUMBER(3) := 0;
    orpos  NUMBER(3) := 0;
    cnt    NUMBER(3);
  BEGIN
    cnt    := 0;
    andpos := instr(in_pkey, '*');
    orpos  := instr(in_pkey, '+');
    IF (orpos = 0) THEN
      in_symbol := 0;
      curpos    := andpos;
    ELSIF (andpos = 0) THEN
      in_symbol := 1;
      curpos    := orpos;
    ELSIF (andpos > orpos) THEN
      in_symbol := 1;
      curpos    := orpos;
    ELSIF (orpos > andpos) THEN
      in_symbol := 0;
      curpos    := andpos;
    END IF;
    IF (in_order = 1) THEN
      RETURN;
    END IF;
    cnt := 1;
    WHILE (cnt < in_order) LOOP
      andpos := instr(in_pkey, '*', curpos + 1);
      orpos  := instr(in_pkey, '+', curpos + 1);
      IF (andpos > orpos AND orpos = 0) THEN
        in_symbol := 0;
        curpos    := andpos;
      ELSIF (orpos > andpos AND andpos = 0) THEN
        in_symbol := 1;
        curpos    := orpos;
      ELSIF (andpos > 0 AND orpos > 0 AND andpos > orpos) THEN
        in_symbol := 1;
        curpos    := orpos;
      ELSIF (andpos > 0 AND orpos > 0 AND andpos < orpos) THEN
        in_symbol := 0;
        curpos    := andpos;
      END IF;
      cnt := cnt + 1;
    END LOOP;
  END;

  procedure query_user_info(in_user_id     in s_user.user_id%type,
                            in_user_name   in s_user.user_name%type,
                            out_search_cur out helper_pkg.ref_cur) is
    l_dy_sql varchar2(2000);
   
  begin
    /* open out_search_cur for
    select user_name ,user_id ,user_psw from s_user
    where  ((in_user_Id is null ) or ((in_user_id is not null ) and (user_Id = in_user_Id) ) )
    and   ((in_user_name is null ) or ((in_user_name is not null ) and (user_name = in_user_name) ) ) ; */
 
    /*    l_dy_sql := 'select user_name,user_id ,user_psw from s_user where  ';
    l_dy_sql := l_dy_sql || '((''' || in_user_id || ''' is null) or ((''' ||
               in_user_id || ''' is not null) and (user_id =''' ||
               in_user_id || '''))) and ';
     l_dy_sql := l_dy_sql || '((''' || in_user_name || ''' is null) or ((''' ||
               in_user_name || ''' is not null) and (user_name =''' ||
               in_user_name || ''')))';  */
    l_dy_sql := 'select user_name,user_id ,user_psw from s_user where  ';
    l_dy_sql := l_dy_sql ||
                '((:in_user_id   is null) or ((:in_user_id   is not null) and (user_id =:in_user_id ))) and ';
    l_dy_sql := l_dy_sql ||
                '((:in_user_name  is null) or ((:in_user_name  is not null) and (user_name =:in_user_name ))) ';
    /*  -- l_dy_sql := l_dy_sql || '((''' || in_user_name || ''' is null) or ((''' ||
    in_user_name || ''' is not null) and (user_name =''' ||
    in_user_name || ''')))';  */
    /* l_dy_sql := l_dy_sql ||  '  where ((:1 is null ) or ((:1 is not null ) and (user_id = :1))) ';
    l_dy_sql := l_dy_sql ||  '  and ((:2 is null ) or ((:2 is not null ) and (user_name= :2))) ';*/
    open out_search_cur for l_dy_sql
      using in_user_id, in_user_name;
 
  end;

end test_pkg;

xuandhu
关注
专栏目录
Sql参考手册
03-31
《SQL参考手册》是数据库管理和开发领域不可或缺的资源，它为理解SQL（Structured Query Language）提供了全面而详尽的指导。SQL，结构化查询语言，是用于管理关系数据库的标准语言，广泛应用于各种数据库系统，如...
每天一个比较有文化的sql语句2
qq_35653822的博客
06-28 120
这是上一篇博客的改良版，用到了内联视图 SELECT attrId, attrCode, attrName,scopeName,sourceIdPart, sourceIdProduct,source, (case source when '01' THEN CONCAT('部件-',(select partName from pf1002...
sql参考手册chm
07-15
《SQL参考手册CHM》是针对SQL语言的一份详尽指南，特别强调了与SQL Server相关的精华内容。SQL（Structured Query Language），结构化查询语言，是用于管理关系数据库的标准编程语言，广泛应用于各类数据处理系统中...
中文版SAP-HANA数据库SQL参考手册.docx
08-19
SAP HANA数据库SQL参考手册是为SAP HANA平台上的SQL用户提供的全面指南，尤其适合SAP顾问、系统管理员和硬件合作伙伴等专业人士使用。该手册深入解析了在HANA环境中如何有效地运用SQL语言，涵盖了从基本语法到高级...
sql参考手册
04-06
这份“SQL参考手册”是一份专为新手设计的资源，旨在提供全面的基础SQL语句教程，帮助初学者快速入门并掌握基本的SQL知识。 一、SQL基础概念 SQL全称为结构化查询语言，它包括了数据查询、数据操作、数据定义和数据...
事务处理begin transtraction和比较经典的存储过程
徐虎的专栏
02-22 4668
主　　题：关于begin trans 的用法!请大家帮忙!!!小女子做了一个存储过程,是用来备份数据的.代码部分如下: select * into #test from chat_record insert into chat_record_history select * from #test delet
实现在Oracle中字段值自动增加1的方法
徐虎的专栏
04-25 3591
实现在Oracle中字段值自动增加1的方法作者：佚名文章来源：本站原创点击数： 更新时间：2004-9-23首先在数据表所在的方案中增加一个序列HEAP_sequence。CREATE SEQUENCE "fangan"."HEAP_sequence" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE NOCACH
oracle中的事务是自动开始的
徐虎的专栏
03-09 2087
由于现在一个项目要转换到oracle上，新接触oracle，以前的sybase库中这样的存储过程都要改动到oracle上，下边这个该怎么写？？谢谢： BEGIN IF @@TRANCOUNT>0   SAVE TRAN TRAN_SEQ ELSE BEGIN    BEGIN TRANSACTION TRAN_SEQ      UPDATE table_1 SET number2 =number
Oracle PL/SQL 编程手册(SQL大全)（转）
徐虎的专栏
03-07 2087
Oracle PL/SQL 编程手册(SQL大全)（转） 一、SQLPLUS 1引言 SQL命令 以下17个是作为语句开头的关键字： alterdroprevoke auditgrantrollback* commit*insertselect commentlockupdate createnoauditvalidate deleterename 这
Oracle函数
徐虎的专栏
11-10 1909
　Oracle已经内建了许多函数，不同的函数有不同的作用和用法，有的函数只能作用在一个记录行上，有的能够作用在多个记录行上，不同的函数可能处理不同的数据类型。常见的有两类，单行函数和分组函数 。 单行函数：单行函数分类函数功能示例字符函数LPAD(,[,])在字符串c1的左边添加字符串c2直到c1字符串的长度等于i。SELECT  LPAD(‘Hello!’,8,’ ’) leftpad,RPAD
项目中的oracle开发技巧
徐虎的专栏
11-07 1911
　　2.org是部门表,其中orgid是当前部门id,porgid是上级目录id,像这种有父子关系的表中做从某一部门向上或向下的查询用到了oracle的connect by语句:　　　　select * from org connect by prior orgid=porgid start with orgid=1　　　　//从部门id为1的部门寻找所有子部门　　　　select * from
数据库连接
徐虎的专栏
12-07 1837
http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp// ===============================================================================// Microsoft Data Access Application Block for .NET// ht
oracle里是否有类似于c语言的split()函数的功能？？？
徐虎的专栏
03-09 1814
create type tab_str as table of varchar2(30);/create or replace function to_table(p_str varchar2) return tab_stras  ltab tab_str := tab_str();  pos integer := 0;  ls varchar2(255) := p_str;begin  pos
sql2000列出所有表名
徐虎的专栏
04-27 1747
SELECT * frOM sysobjects WHERE xtype = U AND (Not (id) = 133575514) and status>0 ORDER BY name
用.NET调用oracle的存储过程返回记录集
徐虎的专栏
03-02 1126
用.NET调用oracle的存储过程返回记录集 文章出处：伟网动力(http://www.weiw.com)有何见解，可以来此论坛 http://www.weiw.com/bbs/ 转贴请标明出处，谢谢。最近做一个项目，为了提高数据库安全性，客户要求所有数据库操作都要放在存储过程。象一般的更新操作，返回一个值的，都比较容易实现。而要想返回记录集就比较麻烦，我在网上搜了一下，这方面的文章并不多。在此
一个示例存储过程
徐虎的专栏
03-06 1092
CREATE OR REPLACE procedure BM_ADD(   p_ID varchar, p_NAME varchar)is--v_count number;begin /*--检查输入参数是否为空if p_id is null or p_name is null thenp_out:=输入的值为空！;return;end if;--该用户名已经存在select count(*)
老虎的笔记 Oracle9i专题
徐虎的专栏
03-09 1078
Oracle9i专题Oracle不支持类似于 MySQL 中的 limit. 但你还是可以rownum来限制返回的结果集的行数. 如果你只希望返回前十行纪录，你可以这样写： SELECT * FROM table WHERE ROWNUM但是下面的语句是不对的： SELECT * FROM table WHERE ROWNUM>90 AND ROWNUM这是因为 Oracle 认为这个条件不成立，
Oracle数据库SQL参考手册
在Oracle数据库SQL参考中，你会找到一系列关键概念和技术，包括但不限于： 1. **SQL语言基础**：SQL（结构化查询语言）是用于管理关系数据库的标准语言。手册会介绍如何创建、修改和查询数据表，以及如何执行数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值