oracle case语句效率高,oracle的case 提交执行效率

--sql identity

CREATE TABLE dbo.Customer

(

customerid INT IDENTITY PRIMARY KEY,

firstname VARCHAR(40) NOT NULL,

lastname VARCHAR(40) NOT NULL,

statecode VARCHAR(2) NOT NULL,

totalsales money NOT NULL DEFAULT 0.00

)

--oracle中的identity

ORACLE是不能用IDENTITY的,可以用SEQUENCE

由于oracle是序列型的，所以不是在建表的时候递增的,可以用以下方法:

1、先建表：

CREATE TABLE Customer

(

customerid integer,

firstname VARCHAR(40) DEFAULT '' NOT NULL,

lastname VARCHAR(40) DEFAULT '' NOT NULL,

statecode VARCHAR(2) DEFAULT '' NOT NULL,

totalsales number(10,2) DEFAULT '' not null,--oracle money数据类型：应该用number(x,y)其中x表示有效位数，y表示小数点后的位数

PRIMARY KEY (customerid)

)

2、创建一个SEQUENCE

create sequence SEQ_Customer

minvalue 1

maxvalue 99999999

start with 21

increment by 1

cache 20;

3、在向表插数据时插入自增列

INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'Thomas', 'Jefferson', 'VA', 100.00 );

INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'lyj', 'lj', 'AB', 500.00 );

INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval , 'John', 'Adams', 'MA', 200.00);

INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval , 'Paul', 'Revere', 'MA', 300.00 );

INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'Ben', 'Franklin', 'PA', 400.00 );

commit;

4、在满足需求中增加列-case和update连用，提交执行效率

ALTER TABLE Customer ADD statedescription VARCHAR(50) DEFAULT '';

UPDATE Customer

SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'

WHEN statecode = 'VA' THEN 'Virginia'

WHEN statecode = 'PA' THEN 'Pennsylvania'

when statecode ='AB' then 'liyongjie'

ELSE NULL

END

commit;

select * from Customer;--查询以上插入的数据

SELECT COUNT(*) AS TotalCustomers,

SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers,

AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales

FROM Customer