ORACLE变量定义及使用（另，T-SQL EXISTS的PLSQL替代写法）

1. 简单变量

declare v_cnt NUMBER(10,0) := 0;

BEGIN

  SELECT COUNT(1) INTO v_cnt FROM concept.Decoction WHERE DecoctionId = -1;

  IF v_cnt = 0 THEN

   insert into concept.Decoction( DecoctionId, Code, Name, MnemonicCode, SpellCode, WBCode, ClinicItemId, IsSpecial, IsDeleted, RowVersion, IsNeedAccount ) values

     ( -1, NULL, NULL, Null, NULL, NULL, -1, 0, 0, SYSDATE, 0 );

  END IF;

END;

 

2. 以表的字段类型定义的变量

create table DBO.TEST_TBL

  (

    ID NUMBER(10) NOT NULL PRIMARY KEY

  );

declare v_cnt CONCEPT.decoction.DecoctionId%type := 0;

BEGIN

  SELECT DecoctionId INTO v_cnt FROM concept.Decoction WHERE DecoctionId = -1;

  INSERT INTO DBO.TEST_TBL SELECT v_cnt from dual;

END;

select * from DBO.TEST_TBL;

 

3. 以表的行定义的变量

drop table DBO.TEST_TBL;

create table DBO.TEST_TBL

  (

    ID NUMBER(10) NOT NULL PRIMARY KEY

  );

declare v_row CONCEPT.decoction%rowtype;

BEGIN

  SELECT * INTO v_row FROM concept.Decoction WHERE DecoctionId = -1;

  INSERT INTO DBO.TEST_TBL SELECT v_row.DecoctionId from dual;

END;

select * from DBO.TEST_TBL;

 

4. 以记录类型定义的变量

drop table DBO.TEST_TBL;

create table DBO.TEST_TBL

  (

    ID NUMBER(10) NOT NULL PRIMARY KEY,

    NAME nvarchar2(50)

  );

 

declare  

  type v_flds is record(

    ID concept.Decoction.DecoctionId%type,

    NAME concept.Decoction.Name%type);

  v_row v_flds;

BEGIN

  SELECT DecoctionId,Name INTO v_row FROM concept.Decoction WHERE DecoctionId = 62;

  INSERT INTO DBO.TEST_TBL SELECT v_row.ID, v_row.NAME from dual;

END;

select * from DBO.TEST_TBL;

转载于:https://www.cnblogs.com/chriskwok/p/4252462.html