CREATE PROCEDURE [dbo].[AddCategory]
-- Add the parameters for the function here
@CategoryName nvarchar(64),
@LogID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatID INT
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO Category (CategoryName) VALUES(@CategoryName)
SELECT @CatID = @@IDENTITY
END EXEC InsertCategoryLog @CatID, @LogID --InsertCategoryLog 为另外一个存储过程 RETURN @CatID
END
初用oracle,看了下书也没找到写法
-- Add the parameters for the function here
@CategoryName nvarchar(64),
@LogID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatID INT
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO Category (CategoryName) VALUES(@CategoryName)
SELECT @CatID = @@IDENTITY
END EXEC InsertCategoryLog @CatID, @LogID --InsertCategoryLog 为另外一个存储过程 RETURN @CatID
END
初用oracle,看了下书也没找到写法
p_categoryname VARCHAR2,
p_logid NUMBER)
AS
l_catid NUMBER
BEGIN
SELECT categoryid
INTO l_catid
FROM category
WHERE categoryname = p_categoryname;
IF l_catid IS NULL
THEN
INSERT INTO category(categoryname) VALUES(p_categoryname);
END IF; INSERTCATEGORYLOG(l_catid, p_logid);
END;
/
我觉得应该这样写,
CREATE OR REPLACE PROCEDURE addcategory(
p_categoryname VARCHAR2,
p_logid NUMBER
l_catid out number)
AS BEGIN
SELECT categoryid
INTO l_catid
FROM category
WHERE categoryname = p_categoryname;
IF l_catid IS NULL
THEN
INSERT INTO category(categoryname) VALUES(p_categoryname);
END IF; INSERTCATEGORYLOG(l_catid, p_logid);
END;