Oracle索引、控制语句

create tablespace table1
datafile 'D:\table1.dbf'
size 50m
autoextend on 
next 50m maxsize 2048m;

create user student identified by student default tablespace table1;
grant connect,resource to student;
grant create view to student;

create table stuinfo
(
stuid int primary key,
stuname varchar(10) not null,
age int check(age>0 and age<100),
sex varchar(10) default '男',
createTime date default sysdate
)

create table stuscore
(
       scoreid int primary key,  
       stuid int references stuinfo(stuid) not null,  
       subject varchar(10) not null,  
       score float check(score>=0 and score<=100)     
)

select * from user_tables;
select * from user_tablespaces;


insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'11111',18);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'22222',16);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'33333',26);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'44444',32);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'55555',15);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'66666',16);
select * from stuinfo;
delete from stuinfo;

select * from user_sequences;

insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,1,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,2,'jsp',76);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,3,'jsp',56);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,4,'jsp',93);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,5,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,6,'jsp',75);
select * from stuscore;
delete from stuscore;


create view view1 
as
select row_number() over(partition by stuScore.Subject order by stuScore.Score desc) as myindex,
stuInfo.Stuid,stuInfo.Stuname,stuScore.Subject,stuscore.score from stuInfo inner join stuScore on stuInfo.Stuid = stuScore.Stuid;

select * from view1;


declare
         maxnumber constant int:=1000;
         i int :=1;
         myname varchar(10);
     begin
         for i in 1..maxnumber loop
          --insert into stuInfo (stuid,stuname,age) values (SQ_aaa.nextval,'1111'+SQ_aaa.CURRVAL,30);
          --select stuname into myname from stuInfo where stuId = 200000;
          select stuname into myname from stuinfo where stuName = '201111';
         end loop;
     dbms_output.put_line(' 成功录入数据！ ');
     commit;
     end; 
     
select count(*) from stuinfo;

select * from stuinfo;
select * from user_indexes;

select * from stuinfo where stuid=200000;
select * from stuinfo where stuname='201111';

create index index_stuname on stuinfo(stuname);
create index index_stuscore on stuscore(subject,score);

select * from stuscore where subject='' and score=11;


declare 
       stuname varchar(10) :='student1';--声明并初始化一个字符串变量
      -- score number(7,3);   -- int float varchar() char
       myscore stuscore.score %type;
begin
     myscore :=11.11;
     Dbms_Output.put_line(myscore);---字符串变量输出
end;

--------条件控制语句---------
declare
        myscore float;
begin
     myscore:=11.11;
     if myscore<22.22 then Dbms_Output.put_line('厉害了，Word哥！');
     else Dbms_Output.put_line(',,,,,,,,,,,');
     end if;
end;

-----------if分支判断-----------
declare 
       myscore float;
begin
     myscore:=11.11;
     if myscore<22.22 then Dbms_Output.put_line('厉害了，Word哥！');
     elsif myscore <33.33 then Dbms_Output.put_line('22222222222');
     else Dbms_Output.put_line(',,,,,,,,,,,,,,,');
     end if;
end;

----------case分支判断-----------
declare
       myscore stuscore.score%type;
begin
     select score into myscore from stuscore where stuid=2;
     case
     when myscore<60
     then Dbms_Output.put_line('低等成绩');
     when myscore>=60 and myscore<=80
     then Dbms_Output.put_line('中等成绩');
     else Dbms_Output.put_line('优秀成绩');
     end case;
end;

---------循环控制语句----------
------Loop语句------
declare
       cou int :=10;
       result int :=0;
begin
     Dbms_Output.put_line('循环开始');
Loop
    exit when cou>20;
    result :=result+cou;
    Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
    cou:=cou+1;
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;
-----For循环-----
declare 
       result int :=0;
begin 
     Dbms_Output.put_line('循环开始。。。。。。');
     for cou in 10..20
     loop
       result :=result+cou;
       Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;
-----While循环-----
declare
       cou int :=10;
       result int :=0;
begin
     Dbms_Output.put_line('循环开始.......');
     while cou <20
     loop
         result :=result+cou;
         Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
         cou :=cou+1;
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;