如题 怎么将Sqlserver中的存储过程转换为Oracle啊 我上网搜了 自己改了还是不行 我有例子 请高手指点一下啊。
ALTER PROCEDURE dbo.Get4City
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 4 * FROM HQXH_M_CITY order by CITY_ID desc
END //这是一个例子
ALTER PROCEDURE dbo.InsertAssNews
@ASS_TITLE varchar(MAX),
@ASS_CONTENT varchar(MAX),
@ASS_DATE datetime
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
INSERT INTO HQXH.dbo.HQXH_M_AFFICHE
(AFFICHE_TITLE
,AFFICHE_CONTENT
,AFFICHE_DATE)
VALUES
(@ASS_TITLE,
@ASS_CONTENT,
@ASS_DATE)
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION;
RETURN
END COMMIT TRANSACTION //这个也是一个 这2个都改不好 求高手指教啊!!
ALTER PROCEDURE dbo.Get4City
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 4 * FROM HQXH_M_CITY order by CITY_ID desc
END //这是一个例子
ALTER PROCEDURE dbo.InsertAssNews
@ASS_TITLE varchar(MAX),
@ASS_CONTENT varchar(MAX),
@ASS_DATE datetime
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
INSERT INTO HQXH.dbo.HQXH_M_AFFICHE
(AFFICHE_TITLE
,AFFICHE_CONTENT
,AFFICHE_DATE)
VALUES
(@ASS_TITLE,
@ASS_CONTENT,
@ASS_DATE)
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION;
RETURN
END COMMIT TRANSACTION //这个也是一个 这2个都改不好 求高手指教啊!!
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 4 * FROM HQXH_M_CITY order by CITY_ID desc --改为cursor返回数据集,top 改为rownum <=4的方式.
END //这是一个例子
ALTER PROCEDURE dbo.InsertAssNews @ASS_TITLE varchar(MAX), -- oracle的声明方式为:ASS_TITLE varchar2(100)这样的形式
@ASS_CONTENT varchar(MAX),
@ASS_DATE datetime -- datetime -->date
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- 去掉.
-- Insert statements for procedure here
INSERT INTO HQXH.dbo.HQXH_M_AFFICHE
(AFFICHE_TITLE
,AFFICHE_CONTENT
,AFFICHE_DATE)
VALUES
(@ASS_TITLE, -- 去掉@符号
@ASS_CONTENT,
@ASS_DATE)
IF @@error <> 0 -- exception when .. then ...
BEGIN
ROLLBACK TRANSACTION; -- ROLLBACK;RETURN
END COMMIT TRANSACTION -- commit;
CREATE OR REPLACE PROCEDURE Get4OUT
AS
BEGIN
SELECT * FROM HQXH_M_OUT where rownum<=4 order by OUT_ID desc;
END Get4OUT;第二个@符号去掉怎么写 我不会了 要不也不能问啊...
表值函数,刚好还没有写。呵呵。
练练手。
--第一个存储过程,因为oracle不支持在存储过程中返回一个表作为结果集。
--所以第一个存储过程似乎没什么意义,SQL server 会为存储过程的计划缓存,下次执行的时候,就会直接调用计划,而节约了分析时间。
--但是oracle似乎是会为每个查询都做类似的处理,不用放在存储过程里面了。ALTER PROCEDURE Get4City
AS
BEGIN
insert into table_one
--在oracle存储过程里面,如果一个select语句返回的值不是一行的话,这是个错误。
--并且返回值也必须是插入到表里,或者 通过(select A into B from tableA ;)赋值给一个变量。
select * from(SELECT * FROM HQXH_M_CITY order by CITY_ID desc )A where rownum <=4 ;
END Get4City;--第二个create or replace PROCEDURE InsertAssNews
(
ASS_TITLE in varchar2,
ASS_CONTENT in varchar2,
ASS_DATE in date
)
AS
BEGIN INSERT INTO HQXH.dbo.HQXH_M_AFFICHE
(AFFICHE_TITLE ,AFFICHE_CONTENT ,AFFICHE_DATE)
VALUES
(ASS_TITLE, ASS_CONTENT, ASS_DATE)
;
commit;
exception others then
rollback;
end InsertAssNews;
第二个create or replace PROCEDURE InsertAssNews(
ASS_TITLE in varchar2, --可以定义为HQXH_M_AFFICHE.AFFICHE_TITLE%TYPE
ASS_CONTENT in varchar2,
ASS_DATE in date
)
as
begin
begin
insert into HQXH.dbo.HQXH_M_AFFICHE(AFFICHE_TITLE ,AFFICHE_CONTENT ,AFFICHE_DATE)
values(ASS_TITLE, ASS_CONTENT, ASS_DATE);
commit;
exception others then
rollback; --其实这里要不要rollback都无所谓了,就执行了一条语句
end;
end;