oracle sql 笔记

 

select chr(97) from dual--根据asc码获取对应的字母
select concat(sno,sname) from student--连接两个字符串
select sno||sname from student--连接两个字符串
select instr('abcd1234abcd1234','1234') from dual--获取一个字符串在另一个字符串出现的位置
select instr('abcd1234abcd1234abcd1234','1234',10,2) from dual--从第10位开始查找第2个出现的1234字符串
select length('aaaaaaaaaaa') from dual--获取一个字符串长度
select lower('ADOEdfklajEOIR') from dual--所有字符串小写
select upper('dfakLSJOLKfdasaL') from dual --所有字符串大写
select rtrim(ltrim('..,,..abce..,,..','.'),'.') from dual--去除左右的字符
select substr('abcdefghijklmn',4,5) from dual--从第4位开始截取5位长度的字符串
select replace(sname,'王','李') from student--将王替换成李
select abs(-100) from dual--取绝对值
select sysdate from dual--获取系统当前时间    sqlserver用getdate()
select last_day(to_date('2000-1-1','yyyy-mm-dd')) from dual--获取某个日期当月的最后一天
select to_char(sysdate,'yyyy') from dual
select to_date('2000-1-1','yyyy-mm-dd') from dual --将字符串转换成日期

--创建索引
create index i_score on score(sno)
--删除索引
drop index i_score

--创建视图
create or replace view v_score
as
select s.sno,s.sname,s.class,c.cno,c.cname,sc.degree from score sc
inner join student s on s.sno=sc.sno
inner join course c on c.cno=sc.cno

select * from v_score order by sno--视图并不存放数据

--case语句
select t.*,
       case sex
            when 'f' then '女'
            when 'm' then '男'
            else '不男不女'
       end 性别
from tbl_person t
--decode封装了case语句
select t.* ,decode(sex,'f','女','m','男','不男不女') 性别 from tbl_person t

--定义存储过程
create procedure SavePerson(vID number,vName varchar2,vSex char,vBirthday date,vSalary number,vClassID number)
as
  v_temp number;
begin
     if vID<=0 then
        insert into tbl_person(id,name,sex,birthday,salary,classid)
        values(s_person.nextval,vName,vSex,vBirthday,vSalary,vClassID);
     else
         update tbl_person set
                name = vName,
                sex = vSex,
                birthday = vBirthday,
                salary = vSalary,
                classid = vClassid
         where id = vid;
     end if;
end;
--调用存储过程
call SavePerson(0,'胡奇韬1','f',to_date('1988-3-7','yyyy-mm-dd'),5000.00,2)

--定义方法
create or replace function GetAge(vBirthday in date) return integer
as
  vCurrentYear number;
  vBirthYear number;
begin
     vCurrentYear := to_char(sysdate,'yyyy');--Oracle变量赋值用:=  ,比较用=
     vBirthYear := to_char(vBirthday,'yyyy');
     return vCurrentYear - vBirthYear;
end;
--调用方法
select t.*, getAge(t.birthday) age from tbl_person t

--游标使用
declare cursor Mycursor is
        select id,name,sex,birthday,classid,salary from tbl_person where classid=1;--游标的数据集
begin
        for c in Mycursor loop--每一条数据都放在c中
            if c.salary<3000 then
               update tbl_person set
                      salary = salary+1000
               where id = c.id;
            else
                update tbl_person set
                      salary = salary+500
               where id = c.id;
            end if;
        end loop;
end;

--定义触发器
create or replace trigger t_Addperson
  after insert--before insert ,after delete,after update
  on tbl_person
  for each row
begin
     insert into tbl_person2(id,name,sex,birthday,salary,classid)
     values(:new.id,:new.name,:new.sex,:new.birthday,:new.salary,:new.classid);--新增的记录放在:new临时表
end;

create or replace trigger t_DeletePerson
  after delete
  on tbl_person
  for each row
begin
     delete from tbl_person2 where id = :old.id;--删除的记录放在:old临时表
end;

delete from tbl_person where id = 129

exp utrain/utrain@orcl file=d:\backup.dmp--导出数据
imp utrain/utrain@orcl file=d:\backup.dmp full=y ignore=y--导入数据