解决方案 »
- 连接oracle10g RAC的速度很慢!
- 当Oracle数据文件或者日志文件丢失时,可采用的措施
- oracle如何填充(insert into)嵌套表(nested table)?
- 把一张表的数据查出来一部分,插到另一张相同结构表的储存过程怎么写?
- oracal 支不支持汉字别名啊???
- Oracle 行级触发器共享数据问题
- 急:为什么插入的数据排序和插入顺序不一样,如何解决问题?
- SGA达到内存的80%,真的会降低系统性能吗???
- Oracle 启动不了了,急啊。
- 怎么取消用户的创建视图的权限
- 解释“TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY PL
- oracle存储过程中临时表的使用
SQL语句就可以实现吧。
--merge into 一个sql解决,为啥用存储过程。
MERGE INTO TEST a
USING student b
ON(a.name = b.name1)
WHEN MATCHED THEN
UPDATE SET a.oid = b.id;
cursor cur is select *
from test t; cur_result test%rowtype;
v_subg3efid number;
begin
for cur_result in cur loop
select id
into v_subid
from student
where rownum = 1
and name1 = cur_result.name;
update test t
set t.oid = v_subid
where t.name1 = cur_result.name; end loop;
commit;
end df_id ;
这是最新的。 跪求指导呀·
--存储过程,存储相片
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'proc_Insertphoto' )
DROP PROCEDURE proc_Insertphoto
GOCREATE PROCEDURE proc_Insertphoto @Name varchar(20), --相片名字
@Img image , --相片
@Time datetime ,--插入时间
@Sm varchar(50),--说明
@Picid int --所属相册编号(外键)
AS
INSERT INTO photoInfo VALUES(@Name,@Img ,@Time,@Sm,@Picid)
GO
V_SUBG3EFID NUMBER;
CURSOR CUR IS(
SELECT NAME FROM TEST T);
BEGIN
FOR CUR_RESULT IN CUR LOOP
BEGIN
SELECT ID
INTO V_SUBID
FROM STUDENT
WHERE ROWNUM = 1
AND NAME1 = CUR_RESULT.NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UPDATE TEST T SET T.OID = V_SUBID WHERE T.NAME1 = CUR_RESULT.NAME;
END LOOP;
COMMIT;
END DF_ID;
--或者
CREATE OR PROCEDURE DF_ID IS
V_SUBG3EFID NUMBER;
CURSOR CUR IS(
SELECT NAME FROM TEST T);
BEGIN
FOR CUR_RESULT IN CUR LOOP
BEGIN
SELECT ID
INTO V_SUBID
FROM STUDENT
WHERE ROWNUM = 1
AND NAME1 = CUR_RESULT.NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
GO TO LABLE1;
END;
UPDATE TEST T SET T.OID = V_SUBID WHERE T.NAME1 = CUR_RESULT.NAME;
<<LABLE1>>
END LOOP;
COMMIT;
END DF_ID;