--游标与存储过程
create or replace procedure testprocedure
as
name1 varchar2(50);
cursor cur_test is select * from test1;
c cur_test%rowtype;
begin
for c in cur_test loop
name1:=substr(c.name,5);
insert into test2 values(seq_test_id.nextval,c.id,name1);
end loop;
commit;
end;
--统计胜败sql
create table test1(
id integer primary key,
name varchar2(50),
result varchar2(20)
)
create sequence seq_test_id
start with 1
increment by 1
nomaxvalue
cache 10;
insert into test1 values(seq_test_id.nextval,'zhang','s');
insert into test1 values(seq_test_id.nextval,'zhang','b');
insert into test1 values(seq_test_id.nextval,'zhang','s');
insert into test1 values(seq_test_id.nextval,'li','b');
insert into test1 values(seq_test_id.nextval,'li','s');
insert into test1 values(seq_test_id.nextval,'li','b');
commit;
select * from test1;
--select name,result,count(*) as cishu from test1 group by name,result;
select name,
sum(case when result='s' then 1 end) 胜,
sum(case when result='b' then 1 end) 败 from test1 group by name;