sql server 存储过程 转 oracle

[火星的你]

下面是小弟要转的一段sql server存储过程，要转成oracle,我对oracle也不熟悉，希望大家帮忙转一下，我好参考，谢谢！

CREATE PROCEDURE [dbo].[QMAP_MB_SEARCHBYID]
@ID INT,
@MAPCODE VARCHAR(8)
AS

DECLARE @CONDITION NVARCHAR(210)

DECLARE @SELECT_COMMAND NVARCHAR(1024)

DECLARE @DBName VARCHAR(50)
SELECT @DBName=DBNAME FROM [geomap_china].dbo.MAP2DB WHERE MAPCODE=@MAPCODE

DECLARE @POITBL VARCHAR(64)
SET @SELECT_COMMAND = N'SELECT @TBL=DEV_TBL FROM ['+@DBName+'].dbo.DEV_GRP WHERE DEV_TBL LIKE ''P%'''
EXECUTE SP_EXECUTESQL @SELECT_COMMAND,N'@TBL VARCHAR(64) OUTPUT',@POITBL OUTPUT

SET @SELECT_COMMAND = N'SELECT RTRIM(NAME) NAME, RTRIM(ADDRESS) ADDRESS,ISNULL(RTRIM(TELEPHONE),'''') TELEPHONE,RTRIM(PY) PY,ISNULL(ZIPCODE,'''') ZIPCODE FROM ['+@DBName+'].dbo.'+@POITBL+
' WHERE ID='+LTRIM(STR(@ID))-- + ' FOR XML AUTO'
EXECUTE SP_EXECUTESQL @SELECT_COMMAND

[火星的你]

哪位大侠帮帮忙啊？帮忙转一下上面的存储过程。

[lee24]

CREATE OR REPLACE PROCEDURE DBO.QMAP_MB_SEARCHBYID
(
V_ID INT,
V_MAPCODE VARCHAR2
)
IS
V_CONDITION VARCHAR2(210);
V_SELECT_COMMAND VARCHAR2(1024);
V_DBNAME VARCHAR2(50);
V_POITBL VARCHAR2(64);
V_TBL dbo.DEV_GRP%TYPE;
BEGIN
SELECT DBNAME
INTO V_DBNAME
FROM DBO.MAP2DB
WHERE MAPCODE = V_MAPCODE;

V_SELECT_COMMAND := 'SELECT DEV_TBL INTO V_TBL FROM ' || V_DBNAME || '.dbo.DEV_GRP WHERE DEV_TBL LIKE ''P%''';
EXECUTE IMMEDIATE V_SELECT_COMMAND;
...
END DBO.QMAP_MB_SEARCHBYID;

[火星的你]

谢谢，不过V_TBL dbo.DEV_GRP%TYPE;
1. 其中%TYPE是什么意思啊 ？
2.我把int型改成INTEGER 或NUMBER 也可以吧？
3.在SQL SERVER中 STR(ID) 在ORACLE中改用什么啊？

[火星的你]

对了，存储过程中dbo.这不用去掉吗？