oracle学习笔记（基本语句）

sys/sysdba

一    基本操作

1   建表

create  table   stu5     ----建表语句

(

s#  varchar(20) not  null,

sname   varchar(20)  ,

addr     varchar(100)

)

--

create table    stu6   as   select   * from  stu5   ----根据stu5生成stu6表

2           修改表名

alter  table     stu5   rename    to  stu10;

alter   table   stu10    rename  to  stu5;

3           添加字段

alter  table    stu5   add addr3    varchar(100);   ---添加列

4           修改字段名

alter   table  stu5   rename  column   addr   to   addr2 ;--addr->addr2  修改字段名；

alter  table   stu5    rename     column     addr2 to  addr ---又把名称改回来了

5            修改字段类型

alter table    stu5  modify     addr    varchar2(100)  ---addr属性改为了varchar2(100)

6           插入

insert into  stu5(s#,sname)   values('s20140102','张三') 

alter table  stu5    add     birth       date;--添加日期型字段

insert    into    stu5(s#,birth)  values('s20140104',to_date('2015-11-20','yyyy-mm-dd'))--插入日期型数值

insert  into  stu5(s#,birth)  values('s20141105',to_date('2014-12-1 18:55:22','yyyy-mm-ddhh24:mi:ss'));

--插入日期型数值  带时分秒

7  修改

update    stu5   set     sname='李伟'  where       s#='s20140102'  ---修改某字段值

-----------------------

select  * from   stu4

create   view     v_stu4  ---创建视图

(学号,姓名,年龄,性别)

  as

select   s#,sname,sage,ssex  from     stu4

where    s#>10110   

select   * from    v_stu4

dropview   v_stu4   ---删除视图

create   view     v_stu4

(学号,姓名,年龄,性别)

  as

select   s#,sname,sage,ssex  from     stu4

where    s#>100110

 select   * from    v_stu4    --视图 s#>100110的记录

二  条件语句

 1     if    

declare

 var_name1 varchar2( 50);

 var_name2  varchar2(50);

 begin

   var_name1:='east';

   var_name2:='western';

   if length(var_name1) <length(var_name2)  then

     dbms_output.put_line('字符串'||var_name1||'的长度比'||'字符串'||var_name2||'的长度小');

    endif;

 end;   ----t   字符串east的长度比字符串western的长度小

2    if …….. else  

  declare

  age   int:=55;

  begin  

    if  age>56  then

      dbms_output.put_line('您可以退休了');

     else

       dbms_output.put_line('现在还不可以退休');

    endif;

  end;  ---t    现在还不可以退休

 3 if …….. elsif………elsif……else            

 

  declare

     month  int :=10;

     begin  ---begin开始..........end  结束

       ifmonth>=1andmonth<=3 then

       dbms_output.put_line('现在是春季'); ---带分号

       elsif month>=4andmonth<=6 then

       dbms_output.put_line('现在是夏季'); ---带分号

       elsifmonth >=7  and month<=9 then

       dbms_output.put_line('现在是秋季') ; 

       elsif month >=10 and  month <=12 then

       dbms_output.put_line ('现在是冬季') ;  ---带分号

       else  ---else前面有then，本身没有then

         dbms_output.put_line ('对不起，输入错误！');

         endif;  ---带分号

     end;

4    case

       declare  

       season  int:=3;

       begin

         case season

           when1 then

             dbms_output.put_line ('现在是第'||season||'季度包含1,2,3月份');

           when2 then

              dbms_output.put_line('现在是第'||season||'季度包含4,5,6月份');

           when   3  then

             dbms_output.put_line('现在是第'||season||'季度包含7,8,9月份');

           when   4 then

              dbms_output.put_line('现在是第'||season||'季度包含10,11,12月份');

         end  case;

        end;  ----t  现在是第3季度包含7,8,9月份

   ----------

       declare  

       season  int:=2;

       info  varchar2(50);

       begin

         case season

           when1 then

              info:='现在是第'||season||'季度包含1,2,3月份';

           when2 then

               info:='现在是第'||season||'季度包含4,5,6月份';

           when   3  then

              info:='现在是第'||season||'季度包含7,8,9月份';

           when   4 then

              info:='现在是第'||season||'季度包含10,11,12月份';

         end  case;

         dbms_output.put_line(info);

        end;    ----t  现在是第2季度包含4,5,6月份    sys/sysdba

   ------------------

三  循环语句

1   when 

declare

  i int:=0;

 sums  int:=0;

 begin

    loop

      i:=i+1;

      sums:=sums+i;

    exit  when    i=100;

    endloop;

    dbms_output.put_line('前100个自然数的和是：'||sums);

 end;   ----t    前100个自然数的和是：5050 

2   while

 declare

   i int:=0;

   s int:=0;

   begin

     while i<100 loop

       i:=i+1;  -----这句一定别少了！！！！！！

       s:=s+i;

      endloop;

  dbms_output.put_line('前100个自然数的和是：'||s);

  end;  ---t  前100个自然数的和是：5050

  -----------

3   for

  declare

   s int:=0;

   begin

     for i in  reverse  0..10 loop

       ifmod(i,2)=0 then

         s:=s+i;

       end   if;

      end loop;

      dbms_output.put_line('前10个自然数中的偶数和是：'||s);

  end;  ---前10个自然数中的偶数和是：30

------10  8  6 4  2 

   --------------------  

      declare

   s int:=0;

   begin

     for i in  0..10  loop

       ifmod(i,2)=0 then

         s:=s+i;

       end   if;

      end loop;

      dbms_output.put_line('前10个自然数中的偶数和是：'||s);

  end;      ---t    前10个自然数中的偶数和是：30

--- 2  4 6  8   10

   --------------------------

四  存储过程

1    不带参数

   create   or  replace   procedure   proc13 is

   begin

   insertinto stu2 values( '200110','黄山',to_date('1995-5-8','yyyy-mm-dd'),'男');

   commit;

   dbms_output.put_line ('插入新纪录成功！');

   end proc13;   ------t   有分号 

   select * from stu2

   begin

     proc13;   插入新纪录成功！

    end;

--

-----------

  ---查询学生总数

  createor replace  procedure  cout_stu5

  as

  zongshu  number(10);

  begin

    select  count(*) into   zongshu  from   stu5;

  dbms_output.put_line('学生总数是：'||zongshu);

  end;

  --

  begin

    cout_stu5;

   end; ---t   学生总数是：11

2    带输入参数

 create   or replace   procedure   proc2_insr

 ( var_s#  in  varchar2,

    var_sname in    varchar2,

    var_addr in  varchar2

 )  as --is    也行   ；

 begin

   insert into  stu5(s#,sname,addr) values( var_s#,var_sname,var_addr);

   commit;

   dbms_output.put_line ('插入新纪录成功！');   ---括号中有单引号

   end;  ---定义完成

  begin

     proc2_insr('s20140109','张峰','河西大街');  

  end;  -----t

  select  * from stu5

 

  --------

3   带输入  输出参数  

  ---修改成绩并输出

  create  or replace   procedure   stu_yuwen_change

(  var_s#  in  varchar2,   ------全局变量不能有长度   不设置输入输出类型  则默认是in

   yuwen_raisein  number

)

   as

   var_sname   varchar2(10);  ------局部变量可以有长度 且不需设置in out

   var_yuwen   number(10,3);  ------不需要加圆括号

   begin

     select sname,yuwen   into   var_sname,var_yuwen from  stu   where   s#=var_s#;

     update    stu   set   yuwen=yuwen+yuwen_raise  where     s#=var_s#;

    dbms_output.put_line(var_sname||'同学现在的语文成绩是：'||to_char(var_yuwen+yuwen_raise));  --to_char(yuwen)则错误

   end  stu_yuwen_change; --------没有commit也会修改成功      ----输出函数中的变量必须是局部变量

   ----定义是    end   存储过程名

  ----

  begin  

    stu_yuwen_change('100101',5); ---第一次执行是  +5

    end;  -------t   赵华同学现在的语文成绩是：110

    ----调用是     end;  不需要带存储过程名   

5                            函数

1    无参数

create or   replace  function   stu5_cout

return number

as

cout   number;

begin

  select count(*) into   cout   from   stu5;

 return cout;

 end  stu5_cout;

 --

 declare  i  number;

 begin

   i:=stu5_cout();

  dbms_output.put_line('学生总数是:'||to_char(i));

  end; ---学生总数是:11

     

2                            带参数

create  or  replace   function     get_avg_sal

(

num_depno  varchar   ----这个变量后面不能加分号      

)

return  number

is

num_avg_pay number ;

begin

  select   avg(sal)into num_avg_pay  from  emp3  where   depno=num_depno;

  return (round(num_avg_pay,2));

  exception

    when   no_data_found   then

      dbms_output.put_line('该部门编号不存在！');

      return (0);

  end;

  ---

  select   * from emp3

  declare

      avg_pay number;

      begin

        avg_pay:=get_avg_sal('102');

       dbms_output.put_line('平均工资是：'||avg_pay); --不是dbms_out.

       --print  '平均工资是：'||avg_pay;

       end;

6                            触发器

 

create or   replace   trigger    tri_dept2

beforeinsert   or    update or   delete

on  lyzh.dept2

declare    var_tag  varchar2(10);

begin

  if  inserting   then

    var_tag:='插入';

  elsif   updating   then

    var_tag:='修改';

  elsif deleting then

    var_tag:='删除';

    endif;

    insert  into    lyzh.dept2_log  values(var_tag,sysdate);

   end    tri_dept2;   ----t

  

   insert into  lyzh.dept2   values('31','产品服务部','奥体新街')

    delete   from    lyzh.dept2   where depno='23'

 

       select  * from   lyzh.dept2 ----5r

 

 

  select  * from   lyzh.dept2_log