Oracle???????????????????
???????????? ???????[ 2016/5/12 11:34:34 ] ????????Oracle ?????
??????????????????????洢?????????MS SQL??Oracle?????????????????????????????????????????????????????????????Oracle?????????????????????Oracle??????н??Oracle?????????????Oracle??????MS SQL????????????????????????????????????????Oralce??MSSQL????????ò?????CRUD???????????????????????????Oracle?????????????????е??????
????????????????????????????????????Account???????????η???AccountID?? AccountName??????AccountID???????????????в???????????????????????д????????????????????????????
?????????MS SQL??
??????????????Account????????????????nvarchar(50)?????????????????????£?
????if object_id(N'Account'??N'U') is not null
????drop table Account
????create table Account
????(
????AccountID nvarchar(50) primary key not null??
????AccountName nvarchar(50)
????)
???????????????????????????????в???????
????if not exists (select * from Account where AccountID = '1')
????insert into Account(AccountID??AccountName) values('1'??'Sam Xiao')
????else
????update Account set AccountName = 'Ф??' where AccountID = '1'
?????????????????SQL????д????????????????????????Oracle?У?????????????д??????????Щ?鷳??????????????Oracle???????????????????????
??????????Oracle??
????????????????????????????ж??????????????????MS SQL??OBJECT_ID(‘?????’??’????????’)???Ь??????????????Oracle??????????????????????????Oracle????д????????ж?????????????????????????????????MS SQL?????????? Select Name From SysObjects Where XType=’U’???????????????????Oracle??????select * from user_tables??????????????ж????????????????????????????????????????????
????declare num number;
????begin
????select count(1) into num from user_tables where table_name='ACCOUNT';
????if num > 0 then
????dbms_output.put_line('????!');
????execute immediate 'drop table ACCOUNT ';
????end if;
????execute immediate 'create table Account
????(
????AccountID nvarchar2(50) primary key??
????AccountName nvarchar2(50)
????)';
????dbms_output.put_line('?????????!');
????end;
??????MS SQL???????????????????????Щ???????????????????????
???????????????????????????????????Oracle?У???????????????????????????в????????????2????????????д????????????????????????????????????????Oracle?е?exists()???????ж?????????????????????????MS SQL??????????????????????????????????????????ж???????????????ɡ????????????????????Oracle??????????????????????????????????????????????
????1??????α? SQL%NOTFOUND SQL%FOUND
????SQL%NOTFOUND ??SQL?е????????α????????????????????????????????????????????????false???????????????????????????
????begin
????update account set AccountName = '???-a' where AccountID = '5';
????IF SQL%NOTFOUND THEN
????insert into account(AccountID??AccountName) values('5'??'???-b');
????END IF;
????end;
?????????ID??????????????????????????????????д??????????????SQL%NOTFOUND????false??????????????????SQL%NOTFOUND????true????????в??????
????2?????? DUP_VAL_ON_INDEX
??????Oracle?????????????????exception???д???
????begin
????insert into account(AccountID??AccountName) values('6'??'???-b');
????exception
????when DUP_VAL_ON_INDEX then begin
????update account set AccountName = '???-b' where AccountID = '6';
????end;
????end;
???????????в????????????????????????????????????????????????????????????????????????????????
????3???????? dual
????dual???????????????????select????????oracle???dual??????????????????
????declare t_count number;
????begin
????select count(*) into t_count from dual where exists(select 1 from account where AccountID='11');
????if t_count 1 then
????dbms_output.put_line('???');
????insert into account(AccountID??AccountName) values('11'??'???-11');
????else
????dbms_output.put_line('???');
????update account set AccountName = '???-11' where AccountID = '11';
????end if;
????end;
?????????????????t_count????dual????????t_count?????????С??1??????????????????в????????????????????????????
????4??no_data_found??
????????????????????????????????????????????????????????£?
????declare t_cols number;
????begin
????select AccountName into t_cols from account where AccountID = '8';
????exception
????when no_data_found then begin
????--dbms_output.put_line('???');
????insert into account(AccountID??AccountName) values('8'??'???-8');
????end;
????when others then
????begin
????--dbms_output.put_line('???');
????update account set AccountName = '???-8' where AccountID = '8';
????end;
????end;
????5??merge??
?????????????merge??????
????MERGE INTO table_name alias1
????USING (table|view|sub_query) alias2
????ON (join condition)
????WHEN MATCHED THEN
????UPDATE table_name SET col1 = col_val1
????WHEN NOT MATCHED THEN
????INSERT (column_list) VALUES (column_values);
????????merge?????????????????????????????????????????????????
????merge into Account t1
????using (select '3' AccountID??'Ф???' AccountName from dual) t2
????on (t1.AccountID = t2.AccountID)
????when matched then
????update set t1.AccountName = t2.AccountName
????when not matched then
????insert values (t2.AccountID?? t2.AccountName);
????commit;
??????????????????????????????????????????С??????????????Oracle??ü?????????????MS SQL???????÷???????к??????????????????????????
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11