建函数问题_如何返回父id？

2008-07-17 03:16:10 221 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【STM32】IEC60730 Class B软件安全实现 Part2：自检库实战指南本文是STM32 IEC60730 Class B软件安全实现的实战指南，详细介绍了如何将X-CUBE-CLASSB自检库集成到用户项目中。内容涵盖软件包解构、内存划分、启动与运行时自检的集成步骤、关键代码示例及调试技巧，并阐述了为最终通过安全认证所需的文档与流程准备，帮助开发者高效实现MCU功能安全。阅读详情

create table test (cid number(20),pid number(20))
insert into test values ('1','10');
insert into test values ('2','20');
insert into test values ('3','30');
insert into test values ('4','40');
insert into test values ('5','50');
insert into test values ('6','60');
commit;
------------------------------------------
这个表建成以后，如何写一个函数，返回它们的父id，例如：传递1返回10，传递1,2返回10,20，传递1,5,6返回10,50,60呢（Oracle数据库）？

...全文

火山引擎实时对话AI实战：5分钟搞定WebRTC+大模型+ASR/TTS全链路集成本文详细介绍了如何利用火山引擎的实时对话AI解决方案，快速集成WebRTC、大模型、ASR和TTS服务，构建低延迟、高保真的实时语音对话应用。通过实战代码示例，从环境准备、音频流管理到全链路串联与性能优化，为开发者提供了一套端到端的集成指南，适用于智能客服、在线教育等互动场景。阅读详情

mimanginging

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

fuyou001 2009-05-27

打赏
举报

学习

hebo2005 2008-09-16

打赏
举报



SELECT     DECODE (INSTR (in_cid, ';', 1, 1),

                              0, in_cid,

                              SUBSTR (in_cid,

                                      DECODE (ROWNUM,

                                              1, 1,

                                                INSTR (in_cid,

                                                       ';',

                                                       1,

                                                       ROWNUM - 1

                                                      )

                                              + 1

                                             ),

                                      DECODE (ROWNUM,

                                              1, INSTR (in_cid, ';', 1, 1) - 1,

                                                LENGTH (in_cid)

                                              - LENGTH (REPLACE (in_cid,

                                                                 ';',

                                                                 ''

                                                                )

                                                       )

                                              + 1, LENGTH (in_cid),

                                                INSTR (in_cid, ';', 1, ROWNUM)

                                              - 1

                                              - INSTR (in_cid,

                                                       ';',

                                                       1,

                                                       ROWNUM - 1

                                                      )

                                             )

                                     )

                             ) cid

                 FROM DUAL

           CONNECT BY ROWNUM <=

                           LENGTH (in_cid)

                         - LENGTH (REPLACE (in_cid, ';', ''))

                         + 1

oracledbalgtu 2008-07-17

打赏
举报



CREATE OR REPLACE FUNCTION GET_STR(STR_IN VARCHAR2) RETURN VARCHAR2 IS

  STR_OUT VARCHAR2(4000);

  X       VARCHAR2(5);

  TYPE REFCURSOR IS REF CURSOR;

  C_V REFCURSOR;

BEGIN

  IF (LENGTH(STR_IN) - LENGTH(REPLACE(STR_IN, ',', ''))) > 999 THEN

    RAISE_APPLICATION_ERROR(-20001, 'list too much,limited 1000!pls change it!');

  ELSE

    OPEN C_V FOR 'SELECT PID FROM TEST WHERE CID IN (' || STR_IN || ')';

    LOOP

      FETCH C_V

        INTO X;

      EXIT WHEN C_V%NOTFOUND;

      IF LENGTH(STR_OUT) > 0 THEN

        STR_OUT := STR_OUT || ',' || X;

      ELSE

        STR_OUT := X;

      END IF;

    END LOOP;

    CLOSE C_V;

  END IF;

  RETURN STR_OUT;

END;

/



SELECT GET_STR('1,2,3') FROM dual;

返回结果:

10,20,30

[Quote=引用楼主 mimanginging 的帖子:]
create table test (cid number(20),pid number(20))
insert into test values ('1','10');
insert into test values ('2','20');
insert into test values ('3','30');
insert into test values ('4','40');
insert into test values ('5','50');
insert into test values ('6','60');
commit;
------------------------------------------
这个表建成以后，如何写一个函数，返回它们的父id，例如：传递1返回…
[/Quote]

tianhuo_soft 2008-07-17

打赏
举报

Create Procedure  Search

@s varchar

as

set @s1='select pid from select pid from test where cid in ('

declare @i int

while charindex(',',@s)=0

begin

set @s1=@s1+substring(@s,1,charindex(',',@s)-1)

set @s=substring(@s,1,charindex(',',@s)-1)

end

set @s1=@s1+')'

exec(@s1)

go

hebo2005 2008-07-17

打赏
举报

不过动态SQL写起来太麻烦了,容易写错'
所以当初我写这个函数里,就想写个通用性强点的字符串拆分语句,这样写其它语句的时候,可以直接COPY
而且可以直接用在普通的SQL语句里
比如有些字符串是放在字表的字段里,这样可以直接SQL里把字段分拆

hebo2005 2008-07-17

打赏
举报

其实函数里可以用另一种更简单的方法,就是动态sql

temp_sql :='select wmsys.wm_concat (pid) from test where cid in('||in_cid||')';
execute immediate temp_sql into out_pid;

hebo2005 2008-07-17

打赏
举报

如果是9I的,就不能用wmsys.wm_concat
就要用SYS_CONNECT_BY_PATH来实现
当然你也可以用游标来一个拼接
关于SYS_CONNECT_BY_PATH 可以参考此贴用法
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048

hebo2005 2008-07-17

打赏
举报

我改了下,可以拆分任意字符串,不需要固定长度的,你去试试看
任意输入1,2,4
都能查出来

hebo2005 2008-07-17

打赏
举报

我帮你写一下

/* Formatted on 2008/07/17 15:47 (Formatter Plus v4.8.8) */

CREATE OR REPLACE FUNCTION fn_chk_pid (in_cid VARCHAR2)

   RETURN VARCHAR2

IS

   out_pid   VARCHAR2 (4000);

BEGIN

   SELECT wmsys.wm_concat (pid)

     INTO out_pid

     FROM TEST a,

          (SELECT     DECODE (INSTR (in_cid, ',', 1, 1),

                              0, in_cid,

                              SUBSTR (in_cid,

                                      DECODE (ROWNUM,

                                              1, 1,

                                                INSTR (in_cid,

                                                       ',',

                                                       1,

                                                       ROWNUM - 1

                                                      )

                                              + 1

                                             ),

                                      DECODE (ROWNUM,

                                              1, INSTR (in_cid, ',', 1, 1) - 1,

                                                LENGTH (in_cid)

                                              - LENGTH (REPLACE (in_cid,

                                                                 ',',

                                                                 ''

                                                                )

                                                       )

                                              + 1, LENGTH (in_cid),

                                                INSTR (in_cid, ',', 1, ROWNUM)

                                              - 1

                                              - INSTR (in_cid,

                                                       ',',

                                                       1,

                                                       ROWNUM - 1

                                                      )

                                             )

                                     )

                             ) cid

                 FROM DUAL

           CONNECT BY ROWNUM <=

                           LENGTH (in_cid)

                         - LENGTH (REPLACE (in_cid, ',', ''))

                         + 1) b

    WHERE a.cid = b.cid;



   RETURN out_pid;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      NULL;

   WHEN OTHERS

   THEN

      -- Consider logging the error and then re-raise

      NULL;

END fn_chk_pid;

oraclelogan 2008-07-17

打赏
举报

[Quote=引用 4 楼 hebo2005 的回复:]
其中
SQL codeSELECTTO_NUMBER
(DECODE
(LENGTH (in_promo_id),6, in_promo_id,
SUBSTR
(in_promo_id,
DECODE
(…
[/Quote]
关注之中啊。

wudileiyu 2008-07-17

打赏
举报

好长啊

hebo2005 2008-07-17

打赏
举报

其中



SELECT     TO_NUMBER

                                          (DECODE

                                              (LENGTH (in_promo_id),

                                               6, in_promo_id,

                                               SUBSTR

                                                  (in_promo_id,

                                                   DECODE

                                                         (ROWNUM,

                                                          1, 1,

                                                            INSTR

                                                                 (in_promo_id,

                                                                  ',',

                                                                  1,

                                                                  ROWNUM - 1

                                                                 )

                                                          + 1

                                                         ),

                                                   6

                                                  )

                                              )

                                          ) promo_id

                                  FROM DUAL

                            CONNECT BY ROWNUM <=

                                            LENGTH (in_promo_id)

                                          - LENGTH (REPLACE (in_promo_id,

                                                             ',',

                                                             ''

                                                            )

这段SQL是把传进来的字符串分拆
比如我传进去是100000,100001,100002
分拆后,变成一张表
promo_id
100000
100001
100002
然后再与要查的表关联

而身你这种情况,比如说传进去是1,5,6
分拆后就是
cid
1
5
6
然后再把这张表与你的目标表关联查询,查出来就是
PID
10
50
60
然后用
wmsys.wm_concat再合并

hebo2005 2008-07-17

打赏
举报

10G 的话可以参考我这个

CREATE OR REPLACE FUNCTION fn_chk_pay (in_promo_id VARCHAR2)

   RETURN VARCHAR2

IS

   out_pay       VARCHAR2 (200);

   now_pay_num   NUMBER (3);

   limit_num     NUMBER (3);

BEGIN

   SELECT COUNT (*)

     INTO now_pay_num

     FROM tcode a

    WHERE code_lgroup = 'J016'

      AND use_yn = '1'

      AND code_mgroup NOT IN ('50', '51');



   SELECT wmsys.wm_concat (value_1), COUNT (*) num

     INTO out_pay, limit_num

     FROM (SELECT DISTINCT a.value_1

                      FROM tnew_promo_d a,

                           (SELECT     TO_NUMBER

                                          (DECODE

                                              (LENGTH (in_promo_id),

                                               6, in_promo_id,

                                               SUBSTR

                                                  (in_promo_id,

                                                   DECODE

                                                         (ROWNUM,

                                                          1, 1,

                                                            INSTR

                                                                 (in_promo_id,

                                                                  ',',

                                                                  1,

                                                                  ROWNUM - 1

                                                                 )

                                                          + 1

                                                         ),

                                                   6

                                                  )

                                              )

                                          ) promo_id

                                  FROM DUAL

                            CONNECT BY ROWNUM <=

                                            LENGTH (in_promo_id)

                                          - LENGTH (REPLACE (in_promo_id,

                                                             ',',

                                                             ''

                                                            )

                                                   )

                                          + 1) b

                     WHERE a.data_type = 'B310'

                       AND a.data_gb = '10'

                       AND a.use_yn = '1'

                       AND a.promo_id = b.promo_id

                  ORDER BY 1);



  



   RETURN out_pay;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      NULL;

   WHEN OTHERS

   THEN

      -- Consider logging the error and then re-raise

      NULL;

END fn_chk_pay;

/

我这个传进去是一个字符串,不过固定是6位一个ID的,比如100000,100001,100002
传出来是固定2位一个的,比如是10,20,30

wudileiyu 2008-07-17

打赏
举报

关注

hebo2005 2008-07-17

打赏
举报

主要是要把字符串分拆

建函数问题_如何返回父id？

相关推荐