if OBJECT_ID('SF_SET_PRIMARY_KEY','P') IS NOT NULL
DROP PROC SF_SET_PRIMARY_KEY
GO
CREATE PROC SF_SET_PRIMARY_KEY @ID NVARCHAR(10)
AS
BEGIN
DECLARE @TABLE NVARCHAR(15)SET @TABLE='COMP'+@ID
if OBJECT_ID(''+@TABLE+'','PK') IS NOT NULL
EXEC('alter table '+@TABLE+'
DROP PK_'+@TABLE+' ')
END
GO
我创建了一个删除表主键的存储过程
EXEC SF_SET_PRIMARY_KEY 2010 执行存储过程
为什么删不掉呢,如何修改?
DROP PROC SF_SET_PRIMARY_KEY
GO
CREATE PROC SF_SET_PRIMARY_KEY @ID NVARCHAR(10)
AS
BEGIN
DECLARE @TABLE NVARCHAR(15)SET @TABLE='COMP'+@ID
if OBJECT_ID(''+@TABLE+'','PK') IS NOT NULL
EXEC('alter table '+@TABLE+'
DROP PK_'+@TABLE+' ')
END
GO
我创建了一个删除表主键的存储过程
EXEC SF_SET_PRIMARY_KEY 2010 执行存储过程
为什么删不掉呢,如何修改?
好像sql server 没有EXEC,而是在存储过程那里点执行
CREATE OR REPLACE PROCEDURE SF_SET_PRIMARY_KEY (P_ID NVARCHAR2)
AS
V_TABLE NVARCHAR2(15);
V_I PLS_INTEGER;
BEGIN
V_TABLE:='COMP' || P_ID;
SELECT COUNT(*) INTO V_I FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND TABLE_NAME= V_TABLE;
IF V_I > 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE DROP PRIMARY KEY PK_' || V_TABLE;
END IF;
END SF_SET_PRIMARY_KEY;
CREATE OR REPLACE PROCEDURE SF_SET_PRIMARY_KEY (P_ID NVARCHAR2)
AS
V_TABLE NVARCHAR2(15);
V_I PLS_INTEGER;
BEGIN
V_TABLE:='COMP' || P_ID;
SELECT COUNT(*) INTO V_I FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND TABLE_NAME= V_TABLE;
IF V_I > 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE DROP PRIMARY KEY PK_' || V_TABLE;
END IF;
END SF_SET_PRIMARY_KEY;