给你一个相似的例子
PROCEDURE save_acc_instant_stk(p_product IN NUMBER,
p_store IN INTEGER,
p_account IN NUMBER,
p_quantity IN INTEGER) IS
BEGIN
MERGE INTO t_store_acc_instant_stock d
USING (SELECT p_product AS product_no,
p_store AS store_no,
p_account AS account_no,
p_quantity AS quantity
FROM dual) s
ON (d.product_no = s.product_no AND d.store_no = s.store_no AND d.account_no = s.account_no)
WHEN MATCHED THEN
UPDATE SET d.quantity = d.quantity + s.quantity
WHEN NOT MATCHED THEN
INSERT
(d.product_no, d.store_no, d.account_no, d.quantity)
VALUES
(s.product_no, s.store_no, s.account_no, s.quantity);
END save_acc_instant_stk;
PROCEDURE save_acc_instant_stk(p_product IN NUMBER,
p_store IN INTEGER,
p_account IN NUMBER,
p_quantity IN INTEGER) IS
BEGIN
MERGE INTO t_store_acc_instant_stock d
USING (SELECT p_product AS product_no,
p_store AS store_no,
p_account AS account_no,
p_quantity AS quantity
FROM dual) s
ON (d.product_no = s.product_no AND d.store_no = s.store_no AND d.account_no = s.account_no)
WHEN MATCHED THEN
UPDATE SET d.quantity = d.quantity + s.quantity
WHEN NOT MATCHED THEN
INSERT
(d.product_no, d.store_no, d.account_no, d.quantity)
VALUES
(s.product_no, s.store_no, s.account_no, s.quantity);
END save_acc_instant_stk;
解决方案 »
- Oracle ODI ODI-1228 ORA-12154错误
- PL/SQL查询结果(远超过excel表6万),如何导出为DBF格式?
- 咨询:通过一个循环向ORALCE写数据时提示:“ORA-12571:TNS:包写入程序失败”,如何解决?
- 求高手改造一条sql语句 在线等
- ODP.net部署问题?
- 按某一字段统计另一字段的值如何实现??
- 存储过程重编译问题
- 怎么写树状结构SQL查询?
- oracle9i可以直接生成HTML各式的Report,oracle有没有提供程序接口
- ADOStoreProc控件调用procedure的错误?c++builder高手请进
- 如何跟踪sql的后台执行情况,急!在线等待!
- 求教高手:求一条Sql语句!!
在oracle中当然不能用拉
(p_CARID IN NUMBER,p_CAR_CLASS IN NUMBER,p_CAR_MARK IN NUMBER)
IS
BEGIN
MERGE INTO CAR_INFO d
USING (SELECT p_CARID AS CARID,
p_CAR_CLASS AS CAR_CLASS ,
p_CAR_MARK AS CAR_MARK
FROM dual) s
ON (d.CARID= s.CARIDAND )
WHEN MATCHED THEN
UPDATE SET d.CARID=12,d.CAR_CLASS=13,d.CAR_MARK=14
WHEN NOT MATCHED THEN
INSERT
(d.CARID, d.CAR_CLASS, d.CAR_MARK)
VALUES
(s.CARID, s.CAR_CLASS, s.CAR_MARK);
END CARINFO;
--你测试一下
rCnt number;
SELECT count(1) into rCnt FROM CAR_INFO WHERE CARID=@CARID
if( rCnt >0) then
...
else
...
end if;
EXECUTE CAR_INFO 10,20,30,50这句还是説无效的SQL语句
CAR_INFO 10,20,30,50;
end;--应该这样执行吧
直接写名字就好
用doer_ljy(可战) ( ) 的方法SELECT count(1) into rCnt FROM CAR_INFO WHERE CARID=@CARID --oracle中不用@
FROM dual
dual应该改成我的表名吧
第一次写存储过程不好意思