plsql 基础语法

--数据定义--创建表--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