--数据定义--创建表--drop table scores;--drop table student;Create table student(Name varchar2(10) constraint nname not null ,No varchar2(3) constraint pk_no primary key ,Sex varchar2(4)default '男' ); create table scores(id number constraint pk_ID primary key ,no varchar2(3) constraint fp_no references student(no) ,name varchar2(100),num number);--drop table scores;--drop table student;--创建视图create view view_student as select * from student;drop view view_student---创建索引create index bh on student(no,name);drop index bh;---创建约束条件----ALTER TABLE 表名 ADD(CONSTRAINT 约束名 约束类型(列名))alter table STUDENT add constraint chk check (sex='男' or sex= '女');----alter table 表名 drop(constraint) alter table student drop constraint chk ALTER TABLE scores DISABLE constraint pk_ID;----关闭 ALTER TABLE student DISABLE/enable CONSTRAINT nn_snameALTER TABLE student disABLE CONSTRAINT nname;ALTER TABLE scores DISABLE constraint fp_no;ALTER TABLE student DISABLE constraint nname; ALTER TABLE student DISABLE constraint pk_no;ALTER TABLE student DISABLE constraint nname;---添加列 ALTER TABLE products ADD description text;ALTER TABLE student ADD description varchar2(100);----删除列 ALTER TABLE products drop description text;ALTER TABLE student drop column description ;alter table yw_cyzt_wyqytj add YWFSR number;comment on column yw_cyzt_wyqytj.ywfsr is '物业费 收入';alter table yw_cyzt_wyqytj add YWJYSR number;comment on column yw_cyzt_wyqytj.YWJYSR is '物业经 营收入';alter table yw_cyzt_wyqytj add QTYWSR number;comment on column yw_cyzt_wyqytj.QTYWSR is '其他业 务收入';数据查询Select * from student数据库操纵Update insert deleteUpdate table_nameInsert into table_name values()Delete table_name 添加数据declare icount integer; sSQL varchar(100);BEGIN for icount in 1 ..90 loop sSQL:='insert into student(name,no)values('||icount ||',' || icount || ');'; dbms_output.put_line(SSQL); ---execute immediate sSQL; END LOOP;END;Sql块DECLARE V1 nchar(10); v_no varchar(3):='1'; BEGIN SELECT t.name INTO V1 FROM student t WHERE no=v_no; --SELECT t.name INTO V1 FROM student t DBMS_OUTPUT.PUT_LINE (v1); exception When TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('More than one manager'); end;自定义变量Record类型declare type t_re is record ( name1 student.name%type,name2 student.no%type,name3 student.sex%type); re t_re;begin select * into re from student where no=40; --select * into re from student where no=40; dbms_output.put_line(re.name1 || ';' || re.name2 || ';' ||re.name3); exception when too_many_rows then DBMS_OUTPUT.PUT_LINE ('More than one manager');end;索引表-- INDEX BY TABLES(不是非/聚簇索引-存在的物理表，它是一个虚拟表)declare cursor cur is select * from student; type type_arr is table of student%rowtype index by binary_integer; arr_tr type_arr; iCount integer:=1;begin for i in cur LOOP arr_tr(icount):=i; icount:=icount+1; END LOOP; for icount in 1 ..arr_tr.count loop dbms_output.put_line(arr_tr(icount).name || arr_tr(icount).no || arr_tr(icount).sex ); end loop; if arr_tr.EXISTS(2) then dbms_output.put_line('arr_tr(2)=' || arr_tr(2).name || arr_tr(2).no || arr_tr(2).sex); end if; end;游标变量显示游标declare icount integer:=0; cursor cur1 is select * from student ;---不带参数的游标 cursor cur2(v_no number) is select * from student t where to_number(t.no)>v_no;---带参数游标 type type_record is record (name student.name%type,no student.no%type,sex student.sex%type); rec_stu type_record; v_name student.name%type; v_no student.no%type; v_sex student.sex%type;begin dbms_output.put_line('不带参数的手动打开的游标' ); open cur1;-- loop exit when cur1%notfound; fetch cur1 into rec_stu; dbms_output.put_line('当前获取的值为：' || rec_stu.name ||rec_stu.no||rec_stu.sex); end loop; close cur1; dbms_output.put_line('带参数的用for打开的游标'); for i in cur2(20)loop dbms_output.put_line('当前获取的值为：' || i.name ||i.no||i.sex); end loop; end; 隐示游标declare v_no varchar2(10):=&学号; --v_no number:=&学号;---为什么在数字时能够正确判断begin delete scores t where t.no=trim(v_no); --delete scores t where t.no=v_no; if sql%notfound then---found,rowcount,isopen delete student t1 where t1.no=v_no; dbms_output.put_line(sql%rowcount); end if; end;游标修改 删除操作select * from student;declare cursor cur is select * from student for update of name nowait;begin for i in cur loop UPDATE student set name='1' where current of cur; end loop;end;--select * from student;流程控制语句条件语句---条件语句--ifdeclare v_score number(5,2):=&分数; v_Result varchar(100):='';begin if v_score>0 and v_score<60 then v_result:='未及格'; elsif v_score<80 then v_result:='中'; elsif v_score<90 then v_result:='良'; else v_result:='优'; end if; dbms_output.put_line(v_result); end; --casedeclare v_score varchar2(4):=&级别; v_Result varchar(100):='';begin case upper(trim(v_score)) when 'D' then v_result:='未及格'; when 'C' then v_result:='中'; when 'B' then v_result:='良'; when 'A' then v_result:='优'; else v_result:='未知'; end case; dbms_output.put_line(v_result); end; 循环语句 -- fordeclare i number:=0; j number:=0; icount number:=10; str varchar(20):='';begin for i in 1.. icount loop str:=''; for j in 1 .. icount-i loop str:=str ||'*'; end loop; dbms_output.put_line(str); end loop; end ;--while循环declare cursor cur is select * from student; st student%rowtype;begin open cur; dbms_output.put_line('查询的条数:' || cur%rowcount); loop exit when cur%notfound; fetch cur into st; dbms_output.put_line(st.name ||st.no ||st.sex ); end loop;close cur;end; 函数create or replace function fun_sum return number is Result number;begin select count(*) into Result from student ; return(Result);end fun_sum;-- select fun_sum from dualcreate or replace function fun_Sum_stu(v_name varCHAR)return number is sun number:=0;begin select sum(to_number(no)) into sun from student where name=v_name; return sun; end fun_Sum_stu;存储过程create or replace procedure pro_find isresult number;begin select count(*) into result from student ;end pro_find; 触发器create or replace trigger tr_out before update on student for each rowdeclare -- local variables herebegin dbms_output.put_line('修改前数据：'||:old.name || :old.no || :old.sex); dbms_output.put_line('修改后数据：'||:new.name || :new.no || :new.sex);end tr_out;update student set name='12' where no='2'