--这是我在SQL 2005中写的SQL:
create table #tb(id int,tname nvarchar(10))
insert into #tb
select 1,'x' union all
select 2,'y'begin
declare @i int
select @i = count(1) from #tb where id=1 and tname='a'
if @i = 0
begin
insert into #tb values (1,'a');
update #tb set tname='aa' where id=1 and tname='a'
endselect @i = count(1) from #tb where id=2 and tname='y'
if @i=0
begin
insert into #tb values (2,'y');
update #tb set tname='yy' where id=1 and tname='y'
endselect @i = count(1) from #tb where id=1 and tname='c'
if @i = 0
begin
insert into #tb values (1,'c');
update #tb set tname='cc' where id=1 and tname='c'
end
endselect * from #tb请问上面的SQL在ORACLE中应该怎么写呢?如何定义变量呢?
insert into #tb values (1,'a');
update #tb set tname='yy' where id=1 and tname='y';
end if;注意分号结尾。
如:变量名 数据类型;如: 函数例子
create or replace function ee(v_id in employee%rowtype) return varchar(15)
is
var_test varchar2(15);
begin
return var_test;
exception when others then
end
我用的Oracle11g,在PL/SQL中写代码。
insert into TEST_TB (
select 1,'x' FROM DUAL
union all
select 2,'y' FROM DUAL);declare
i number;
select count(1) into i from TEST_TB where id=1 and tname='a'
if i = 0
begin
insert into TEST_TB values (1,'a');
update TEST_TB set tname='aa' where id=1 and tname='a';
end;
end if;select count(1) into i from TEST_TB where id=2 and tname='y';
if i=0
begin
insert into TEST_TB values (2,'y');
update TEST_TB set tname='yy' where id=1 and tname='y';
end;
end if;select count(1) into from TEST_TB where id=1 and tname='c'
if i = 0
begin
insert into #tb values (1,'c');
update #tb set tname='cc' where id=1 and tname='c';
end;
end if;
end;
i number;
begin
select count(1) into i from TEST_TB where id=1 and tname='a';
if i = 0 then
begin
insert into TEST_TB values (1,'a');
update TEST_TB set tname='aa' where id=1 and tname='a';
end;
end if;select count(1) into i from TEST_TB where id=2 and tname='y';
if i=0 then
begin
insert into TEST_TB values (2,'y');
update TEST_TB set tname='yy' where id=1 and tname='y';
end;
end if;select count(1) into i from TEST_TB where id=1 and tname='c';
if i = 0 then
begin
insert into TEST_TB values (1,'c');
update TEST_TB set tname='cc' where id=1 and tname='c';
end;
end if;
end;
LZ少写了最初的一个begin
可是即使加上去还是报错了,Error Report如下:Error report:
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function package pragma procedure subtype type use <ID>
<外加雙引號的分界 ID> form current cursor
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "IF" when expecting one of the following: * & - + ; / at for mod remainder rem <指數 (**)> and or
group having intersect minus order start union where connect
|| multiset
The symbol ";" was sub
ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: * & - + / at mod remainder rem then <指數 (**)> and or ||
multiset
The symbol "then" was substituted for "BEGIN" to continue.
ORA-06550: line 13, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ; <ID> <外加雙引號的分界 ID>
The symbol ";" was substituted for "SELECT" to continue.
ORA-06550: line 15, column 5:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: * & - + / at mod remainder rem then <指數 (**)> and or ||
multiset
The symbol "then" was substituted for "BEGIN" to continue.
ORA-06550: line 21, column 22:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following: mod <ID> <外加雙引號的分界 ID> <連結變數> current
sql execute forall merge pipe
The symbol "<ID>" was substituted for "FROM" to continue.
ORA-06550: line 22, column 1:
PLS-00103: Encountered the symbol "IF" when expecting one of the following: * & - + ; / at for mod remainder rem <指數 (**)> and or
group having intersect minus order start union where connect
|| multiset
The symbol ";" was su
ORA-06550: line 23, column 5:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: * & - + / at mod remainder rem then <指數 (**)> and or ||
multiset
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
insert into TEST_TB (
select 1,'x' FROM DUAL
union all
select 2,'y' FROM DUAL);
--select * from TEST_TB
begin
declare
i number;
select count(1) into i from TEST_TB where id=1 and tname='a'
if i = 0
begin
insert into TEST_TB values (1,'a');
update TEST_TB set tname='aa' where id=1 and tname='a';
end;
end if;
endselect count(1) into i from TEST_TB where id=2 and tname='y';
if i=0
begin
insert into TEST_TB values (2,'y');
update TEST_TB set tname='yy' where id=1 and tname='y';
end;
end if;select count(1) into from TEST_TB where id=1 and tname='c'
if i = 0
begin
insert into #tb values (1,'c');
update #tb set tname='cc' where id=1 and tname='c';
end;
end if;
end;
真神奇,只是把begin拿带下面来就OK了。为什么呀?
THX,请去http://topic.csdn.net/u/20090219/13/2395780f-1b41-4e40-83a2-ad4c73e93e7d.html也回一个帖。我好一起给分。
我沒跟你講定義變量寫在begin前面嗎。