--数据定义--创建表--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'
转载于:https://www.cnblogs.com/baiyixianzi/archive/2012/08/31/plsql13.html
