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--导入数据