delete from abc_zspmxx where zsxm_dm='03' and zspm_dm in('1111','1115','1116')
update abc_zspmxx set xh='3' where zsxm_dm='03' and zspm_dm='1112'
update abc_zspmxx set xh='4' where zsxm_dm='03' and zspm_dm='1113'
update abc_zspmxx set xh='5' where zsxm_dm='03' and zspm_dm='1114'
update abc_zspmxx set xh='8' where zsxm_dm='03' and zspm_dm='1108'
INSERT INTO ABC_ZSPMXX VALUES ('03','1117','aaaaa',1,null,'元','004',null,1,null,1,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1118','bbbbb',3,null,'元','004',null,3,null,2,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1119','ccccc',5,null,'元','004',null,5,null,6,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1120','ddddd',4,null,'元','004',null,4,null,7,'01')
我现在要执行上面的这九条语句,能不能用一个sql脚本,或者用一个或多个更加优化的sql写出来
不要用存储过程和用JAVA代码写。在线等!!!
update abc_zspmxx set xh='3' where zsxm_dm='03' and zspm_dm='1112'
update abc_zspmxx set xh='4' where zsxm_dm='03' and zspm_dm='1113'
update abc_zspmxx set xh='5' where zsxm_dm='03' and zspm_dm='1114'
update abc_zspmxx set xh='8' where zsxm_dm='03' and zspm_dm='1108'
INSERT INTO ABC_ZSPMXX VALUES ('03','1117','aaaaa',1,null,'元','004',null,1,null,1,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1118','bbbbb',3,null,'元','004',null,3,null,2,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1119','ccccc',5,null,'元','004',null,5,null,6,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1120','ddddd',4,null,'元','004',null,4,null,7,'01')
我现在要执行上面的这九条语句,能不能用一个sql脚本,或者用一个或多个更加优化的sql写出来
不要用存储过程和用JAVA代码写。在线等!!!
delete from abc_zspmxx where zsxm_dm='03' and zspm_dm in('1111','1115','1116')
update abc_zspmxx set xh='3' where zsxm_dm='03' and zspm_dm='1112'
update abc_zspmxx set xh='4' where zsxm_dm='03' and zspm_dm='1113'
update abc_zspmxx set xh='5' where zsxm_dm='03' and zspm_dm='1114'
update abc_zspmxx set xh='8' where zsxm_dm='03' and zspm_dm='1108'
INSERT INTO ABC_ZSPMXX VALUES ('03','1117','aaaaa',1,null,'元','004',null,1,null,1,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1118','bbbbb',3,null,'元','004',null,3,null,2,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1119','ccccc',5,null,'元','004',null,5,null,6,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1120','ddddd',4,null,'元','004',null,4,null,7,'01')
我现在要执行上面的这九条语句,能不能用一个sql脚本,或者用一个或多个更加优化的sql写出来,让它一次执行。
不要用存储过程和用JAVA代码写。在线等!!!
2.将上面的语句(每句以半角分号结束)粘贴上去;
3.保存文件为test.sql;
4.打开sqlplus或pl/sql,如:
d:@test.sql --即可运行。
update abc_zspmxx set xh='3' where zsxm_dm='03' and zspm_dm='1112'
update abc_zspmxx set xh='4' where zsxm_dm='03' and zspm_dm='1113'
update abc_zspmxx set xh='5' where zsxm_dm='03' and zspm_dm='1114'
update abc_zspmxx set xh='8' where zsxm_dm='03' and zspm_dm='1108'
INSERT INTO ABC_ZSPMXX VALUES ('03','1117','aaaaa',1,null,'元','004',null,1,null,1,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1118','bbbbb',3,null,'元','004',null,3,null,2,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1119','ccccc',5,null,'元','004',null,5,null,6,'01')
INSERT INTO ABC_ZSPMXX VALUES ('03','1120','ddddd',4,null,'元','004',null,4,null,7,'01')
保存为D:\test.sql 再在D:\的根目录下创建个test.bat并保存下sqlplus scott/tiger @D:\test.sql语句 然后双击test.bat就可以了
update abc_zspmxx set xh='3' where zsxm_dm='03' and zspm_dm='1112'
update abc_zspmxx set xh='4' where zsxm_dm='03' and zspm_dm='1113'
update abc_zspmxx set xh='5' where zsxm_dm='03' and zspm_dm='1114'
update abc_zspmxx set xh='8' where zsxm_dm='03' and zspm_dm='1108'
UPDATE ABC_ZSPMXX
SET XH = DECODE(ZSPM_DM,
'1112',
'3',
'1113',
'4',
'1114',
'5',
'1108',
'8')
WHERE ZSXM_DM = '03'
AND ZSPM_DM IN ('1112', '1113', '1114', '1108');
update abc_zspmxx
set xh = decode(zspm_dm,
'11112',
'3',
'1113',
'4',
'1114',
'5',
'1108',
'8',
null)
where zspm_dm = '03';
怎么运行d:\@test.sql
提示
SQL> d:\@test.sql
2 /d:\@test.sqlORA-00900: invalid SQL statement
[sql>@d:\test.sql
呵呵