SQLSERVER自增字段和Oracle创建自增字段-ORACLE SEQUENCE方法的对比-并且用存储过程实现参数输出

SQLSERVER自增字段和Oracle创建自增字段-ORACLE SEQUENCE方法的对比

并且用存储过程实现 带参数输出

--SQLSERVER版本的实现

CREATE TABLE [dbo].[Accounts_Permissions](     [PermissionID] [int] IDENTITY(1,1) NOT NULL, --（自增长1 从1开始）     [Description] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,     [CategoryID] [int] NULL ) ON [PRIMARY] CREATE PROCEDURE [dbo].[sp_Accounts_CreatePermission] @CategoryID int, @Description varchar(50) AS     INSERT INTO Accounts_Permissions(CategoryID,Description) VALUES(@CategoryID,@Description)     RETURN @@IDENTITY

---SQLSERVER 直接通过RETURN 返回

DECLARE @return_value int EXEC @return_value = [dbo].[sp_Accounts_CreatePermission] @CategoryID = 555, @Description = N'测试' SELECT 'Return Value' = @return_value GO

--ORACLE版本的 实现

-- 创建表 create table ACCOUNTS_PERMISSIONS ( PERMISSIONID NUMBER(4) not null, DESCRIPTION VARCHAR2(255), CATEGORYID NUMBER(4) ) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64 next 64 minextents 1 maxextents unlimited pctincrease 50 ); --创建序列 create sequence Seq_Accounts_Permissions minvalue 1 --最小值 maxvalue 9999 --最大值 start with 1 --起始于1 increment by 1 --自增长1 nocache; --不写入CACHE 这个的好处是不会因为数据库当掉二序号中断 坏处是性能没有缓存来的快 /*创建存储过程*/ create or replace procedure sp_Acc_CreatePermission(CategoryID  number,                                                     Description varchar2,                                                     outid       out number) is   id number; begin   INSERT INTO Accounts_Permissions     (PERMISSIONID, CategoryID, Description)   VALUES     (Seq_Accounts_Permissions.Nextval, CategoryID, Description);   commit;   SELECT Seq_Accounts_Permissions.currval into id FROM DUAL; ---返回当前序列的ID号   dbms_output.put_line(id);   outid := id; end;

CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值

调用

declare
  param_out number;
  categoryid  number;
  description1 varchar2(50);
begin
  categoryid :=555;  
  description1:='测试';
   sp_acc_createpermission(categoryid,
                          description1,
                           param_out);
  dbms_output.put_line(param_out);
end;

 

在ORACLE中带返回参数也可以用 函数来操作

create or replace function F_ACC_CREATEPERMISSION(CategoryID  in NUMBER,                                                   Description in VARCHAR2)   return number is   Result number; begin   INSERT INTO Accounts_Permissions     (PERMISSIONID, CategoryID, Description)   VALUES     (Seq_Accounts_Permissions.Nextval, CategoryID, Description);   COMMIT;   SELECT Seq_Accounts_Permissions.currval into Result FROM DUAL;   return(Result); end F_ACC_CREATEPERMISSION;

调用函数

begin
dbms_output.put_line(f_acc_createpermission(555,'XJY'));
end;