下面是小弟要转的一段sql server存储过程,要转成oracle,我对oracle也不熟悉,希望大家帮忙转一下,我好参考,谢谢!CREATE PROCEDURE [dbo].[QMAP_MB_SEARCHBYID]
@ID INT,
@MAPCODE VARCHAR(8)
ASDECLARE @CONDITION NVARCHAR(210)DECLARE @SELECT_COMMAND NVARCHAR(1024)DECLARE @DBName VARCHAR(50)
SELECT @DBName=DBNAME FROM [geomap_china].dbo.MAP2DB WHERE MAPCODE=@MAPCODEDECLARE @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
@ID INT,
@MAPCODE VARCHAR(8)
ASDECLARE @CONDITION NVARCHAR(210)DECLARE @SELECT_COMMAND NVARCHAR(1024)DECLARE @DBName VARCHAR(50)
SELECT @DBName=DBNAME FROM [geomap_china].dbo.MAP2DB WHERE MAPCODE=@MAPCODEDECLARE @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
(
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;
1. 其中%TYPE是什么意思啊 ?
2.我把int型改成INTEGER 或NUMBER 也可以吧?
3.在SQL SERVER中 STR(ID) 在ORACLE中改用什么啊?