给你一个相似的例子
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 Database 9i/10g/11g编程艺术深入数据库体系结构中文版2’
- oracl如何查看那个用户对某张表进行的操作,在日志文件怎么查找出这种信息
- 数据库备份
- 我安装了Oracle 11g,但是没找到图形界面EMC,11g里没有吗?
- 学习ORACLE
- oracle 触发器的问题
- 请教一个SQL语句,一个表的列数据怎么转换为字段名出来?
- 新手问一个关于sql插入、修改、删除的问题
- 请问SGA 中SQL共享池中的运行数据库所需的临时数据与数据缓冲区中的数据库对象数据有什么区别
- 神秘的oracle?
- 如何跟踪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应该改成我的表名吧
第一次写存储过程不好意思