首页 > 代码库 > ORACLE变量定义及使用（另，T-SQL EXISTS的PLSQL替代写法）
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;
ORACLE变量定义及使用（另，T-SQL EXISTS的PLSQL替代写法）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。