当前位置: 代码迷 >> DB2 >> db2中有select into吗?小弟我想将几个值同时插入到几个变量
  详细解决方案

db2中有select into吗?小弟我想将几个值同时插入到几个变量

热度:10000   发布时间:2013-02-26 00:00:00.0
db2中有select into吗?我想将几个值同时插入到几个变量?
db2中有select into吗?我想将几个值同时插入到几个变量?比如:
 SELECT
  t.HM, substr(t.zh,7,3),to_char(t.KHRQ,'yyyymmdd'),t.ZHLB
  INTO :NEW.ACCNAME,:NEW.BRNO,:NEW.KHRQ,:NEW.ZHXZ
  FROM FHDGCKFHZ t
  WHERE t.ZH=:NEW.ACCNO;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  :NEW.BRNO := substr(:NEW.ACCNO,7,3);
  NULL;
  WHEN OTHERS THEN
  RAISE;

------解决方案--------------------------------------------------------
有啊。其语法例子为

Select Into
A SELECT-INTO statement is used in an application program to retrieve a single row. If
more than one row matches, an error is returned. The statement text is the same as any ordinary
query, except that there is an INTO section (listing the output variables) between the
SELECT list and the FROM section.

Example
SQL code
SELECT name,salaryINTO :name-var,:salary-varFROM staffWHERE id = :id-var
------解决方案--------------------------------------------------------
select column into 变量 from tbname
------解决方案--------------------------------------------------------
可以啊
select f1,f2 into v1,v2 from tt where ....
------解决方案--------------------------------------------------------
or
set V_SELL_ID = (SELECT COALESCE(RECEIVE_ID,-1) FROM IS_ORDER WHERE ROW_ID=IN_ROW_ID)
------解决方案--------------------------------------------------------
if exists(SELECT t.HM, substr(t.zh,7,3),replace(char(t.KHRQ),'-',''),t.ZHLB 
INTO :v_table_accname,:v_table_brno,:v_table_khrq,:v_table_zhxz 
FROM BRAS.FHDGCKFHZ t 
WHERE t.ZH=new.ACCNO) then
当然不行,INTO是给变量赋值,
你要判断变量是否有值,在IF THEN 中判断

------解决方案--------------------------------------------------------
你这个执行报什么错啊?
------解决方案--------------------------------------------------------
这样:
if exists(SELECT * FROM BRAS.FHDGCKFHZ t WHERE t.ZH=new.ACCNO) then 
set v_table_accname=(SELECT t.HM FROM BRAS.FHDGCKFHZ t WHERE t.ZH=new.ACCNO);
有无问题
------解决方案--------------------------------------------------------
把冒号去掉试一下
  相关解决方案