Oracle 札记
Oracle 笔记
1. select + xxx + from + xxx //查询语句 xxx 表示一个表
Select * from + xxx // * 表示一个列表中所有的内容
类： 1）// select * from country
2）// select vLastName, vFirstName from externalcandidate
“--”表示单行注释
“/* ... */ ”表示多行注释
2. desc + xxx // 查看表的结构 recruit
desc externalcandidate
3. select cCountryCode "编号",cCountry "国家" from country; //把标题名字换了
4. select distinct cCity from externalcandidate
加了个distinct，表示没有重复的了，重复显示一个。如果是两列的话有两个都是重复的话，就保留一个
select distinct cCity,CState from college
5.运算符
and
or
not
|| 连接两个字符串，把两列的数据合到一起去了
如：select vLastName || vFirstName from externalcandidate
//把两列的数据合起来的标题名字换了
select vLastName || vFirstName "name" from externalcandidate
//把两列的数据合起来之间加一个点
select vLastName || '.' ||vFirstName "name" from externalcandidate
//把两列的数据合起来之间加一个字符串
select vLastName || 'von' ||vFirstName "name" from externalcandidate
6. //把列出的数据乘上5的结果是多少
select nAnnualsalary * 5 from Annualsalary
//where nAnnualsalary > 40000 加入的条件语句放在后面 表示大于40000的把它列出来
select nAnnualsalary from Annualsalary where nAnnualsalary > 40000
//通过条件查找特定的对象
select vLastName, vFirstName from externalcandidate where vLastName = 'King' ;
// 表示两者之间
select nAnnualsalary from Annualsalary where nAnnualsalary > 30000 and nAnnualsalary < 40000;
//表示两者之间 包括两者的界限 30000和40000
//where 。。。 between 。。。 and 。。。;
select nAnnualsalary from Annualsalary where nAnnualSalary between 30000 and 40000;
//查询具体个对象 where +与前面的对象一样+in ('。。。', '。。。' ,'。。。')
select vFirstName from externalcandidate where vFirstName in ('Carol', 'Catherine' ,'David')
//查找比较模糊的对象 where 。。。 like '。。。%'
select vFirstName from externalcandidate where vFirstName like 'Da%' ;
select vFirstName from externalcandidate where vFirstName like '%in%' ;
select vFirstName from externalcandidate where vFirstName like 'L_nda' ;//这里的下划线表示一个字符
7.作业
1. select vLastName || vFirstName "名字" ,cCity ,nTestScore from externalcandidate where cCity = 'New York City' and nTestScore > 60;
2. select cZip"邮编" ,cState || cCity || vAddress"地址", vLastName || vFirstName"姓名 ", nTestScore from externalcandidate where nTestScore > 60;
3. select distinct cEmployeeCode ,nMonthlySalary ,nReferralBonus from monthlysalary where (nMonthlySalary * 12 + nReferralBonus) > 30000;
///////////////////////////
//
1. where 。。。 is null;当什么为空的时候
select vFirstName,VlastName, cPhone from externalcandidate where vEmailid is null;
2. where .... is not null 当什么不为空的时候
select vFirstName,dInterviewDate from externalcandidate where dInterviewDate is not null
3.order by 。。。（asc） 把数据按照。。。排序（升序排列）
如：
select vFirstName,VlastName, vEmailid from externalcandidate where vEmailid is not null order by vLastName asc;
//
order by 。。。desc 把数据按照。。。排序（降序排列）
select vFirstName,VlastName, vEmailid from externalcandidate where vEmailid is not null order by vLastName desc;
//
select vFirstName,VlastName, nTestScore from externalcandidate where nTestScore >=60 order by nTestScore desc;
//排两次
select * from annualsalary order by nAnnualsalary ,nYear ;？？？？？？
//toy的表需要换recruit
select cOrderNo, cToyId ,nToyCost from OrderDetail order by nToyCost desc;
select vFristName, vLastName ,vAddress from recipient where vToyName like 'B%';
select nShippingCharges from orders where dorderdate = '20-5月-99'
select vToyDescription, vToyName from toys where nToyRate between 50 and 120;
8.生产一个亚元表 dual
select * from dual
//select 'abc' from dual //如果没订定列的名字，则为字符的少一位表示
//函数有：Upper ，Lower ，initCap ，ltrim ，rtrim
Upper 转换成大写
Lower 转换成小写
select upper('abc') from dual
select upper(vFirstName),vFirstName from employee //前面大写，后面一个是原本的样子
select lower(vFirstName),vFirstName from employee
initCap 把结果每个词的首字母大写
//select initCap('acdfg')from dual;
select initCap('acdfg class')from dual;//两个字符的第一个字母大写
select initCap('AVDSGGS class')from dual; //保留首字母大写其他都小写
concat 连接俩个字符串，只能是两个。
select concat('good','morning')from dual;
substr 提取字符下标之间的字符 第一个数字表示下标是从几位开始的,第二个数字表示几位数。
select substr('sfdf',2,3)from dual;//第二位开始，以后的三个字符
select substr('abcdefg',2,4)from dual;
instr 查找对应的字符在字符串中的位置 下标是从1开始的，第一次出现的位子
select instr('abcdefg','d')from dual;
select instr('abcdefg','h')from dual;
select instr('abcdefg','cde')from dual;
length 字符串长度
select length('abcdefg')from dual;
1 .select length (vFirstName||vFirstName) from employee
2. select length (vFirstName||vFirstName) from employee
Lpad 从左边填充字符。第二位表示指定第一位的字符的个数，第三位表示要加入的字符，如果第二位的位数小于实际第一位给出的位数，则显示的就是第一位上的前几位字符。
select lpad('abcdef',8,'$') from dual;
select lpad('abcdef',12,'12') from dual;// 填充顺着填
select lpad('abcdef',4,'12345') from dual;//后面的没有起到作用，因为第二位要求的位数少。
rpad从右边填充字符。同上
select rpad('abcdef',4,'12345') from dual;
ltrim // 从左边去掉对应字符。只看第一个字母，必须是从第一个字母开始删除。如果要删除的两个字符是间隔的，那就删除第一个就好。
select ltrim('abcdef','ab') from dual;
select ltrim('abcdef','ac') from dual; //c没有用
select ltrim('abcdef','bb') from dual; // 不能删除
rtrim从右边去掉对应字符，同上不过删除字符从后面向前删除
select rtrim('abcdef','ef') from dual;
select rtrim('abcdef','bef') from dual;
select rtrim('abcdef','bec') from dual;//只看要删除的字符的最后面的第一个字母
select upper(cName), substr(vAddress,1,10) from contractrecruiter
abs(-5) 取绝对值
select abs(-5) from dual
ceil(3.14) 取整数值为4 取大的
select ceil(3.14) from dual
floor(3.14) 取整数值为3 取小
select floor(3.14) from dual
power(2, 4) 取数的次方的 2的4次方
select power(2, 4) from dual
mod(11, 4) 取模的
select mod(11, 4) from dual
round(3.1415926, 2) 取小数的位数 四舍五入 。正数表示小数点后保留几位。为负数表示小数点向前几位，用零替换。
select round(3.1415926, 2) from dual
select round(123.1415926,-2) from dual //值为100
select round(163.1415926,-2) from dual //值为200
trunc(163.1415926,2) 取小数的位数 直接去掉 其他和上面一样
select trunc(163.1415926,2) from dual
sqrt(64) 开平方
select sqrt(64) from dual
//列子
select vFirstName from externalcandidate where ceil(sqrt(nTestScore)*10)>75
select dInterviewDate from externalcandidate where dInterviewDate = '21-8月-98'
//日期
//sysdate 当前日期
select sysdate from dual;
sysdate+3 算天的
select sysdate+3 from dual;
// greatest 比较两个日期，那个日期比较大
select greatest(sysdate , '8-8月-08') from dual;
// least比较那个日期比较小
select least(sysdate , '8-8月-08') from dual;
// months_between
months_between(sysdate,'8-8月-8') //比较两个日期之间相差多少个月，得出来是小数 。前面减后面的值
select months_between(sysdate,'8-8月-8') from dual;
//add_ months 增加几个月后的日期
select add_ months(sysdate,+7) from dual;
//next_day/算下个星期几是什么日期
next_day(sysdate,'星期二') //算下个星期几是什么日期
select next_day(sysdate,'星期二') from dual;
select next_day(sysdate,3) from dual; //也可以这样表示 ，1表示星期日 2表示星期一
select months_between(sysdate,dInterviewDate) from externalcandidate
//类型转换
// desc employee
long
clob
raw（）
long raw 2G
blob 4G二进制数据
bflie
//用”yy”表示显示几位数，‘y’如果是一位就显示一位，
day 表示星期几的意思；ddd表示一年的第几天；d 表示星期几，用数字表示的；hh24/mi/ss 表示24小时制的时间；am.pm表示上午，下午。
1. to_char(sysdate, 'yyyy-mm-dd') //转换成字符串形式 // 2008-08-07
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate) from dual;
to_char(sysdate, 'yy-month-dd') /转换成字符串形式 08-8月 -07
select to_char(sysdate, 'yy-month-dd') from dual;
to_char(sysdate, 'yy-month-dd day') 08-8月 -07 星期四
select to_char(sysdate, 'yy-month-dd day') from dual;
select to_char(sysdate, 'yy-month-dd day ddd') from dual;// 08-8月 -07 星期四 220天（一年的第220天）
select to_char(sysdate, 'yy-month-dd d day ddd') from dual;// 08-8月 -07 5 星期四 220
//显示时间 12小时制的
select to_char(sysdate, 'yy-month-dd d day ddd hh/mi/ss') from dual; // 08-8月 -07
5（表示星期几的意思用数字表示的） 星期四 220 03/22/50
//显示时间 24小时制的
select to_char(sysdate, 'yy-month-dd d day ddd hh24/mi/ss') from dual; // 08-8月 -07 5 星期四 220 15/23/39
select to_char(sysdate, 'yy-month-dd d day ddd pm hh24/mi/ss') from dual;
//08-8月 -07 5 星期四 220 下午 15/24/59
//转换成特定字符
//今年是2008年08月07日下午 03点30分27秒
select to_char(sysdate, '"今年是"yyyy"年"mm"月"dd"日"am hh"点"mi"分"ss"秒"') from dual;
// to_date
select to_date('20080808','yyyymmdd') from dual; //08-8月 -08
select to_date('20080808','yyyymmdd') - sysdate from dual; .351493056 算多少天
select to_date('200808081945','yyyymmddhh24mi') - sysdate from dual;算多少天
select (to_date('200808081945','yyyymmddhh24mi') - sysdate )* 24 from dual; //算多少小时
// round 四舍五入的第一天
select round(sysdate, 'month') from dual; 01-8月 -08 //找到与他最近的日期 45度向前向后 ？？？？
select round(sysdate, 'year') from dual;
// trunc 表示当前的月或年等所在的第一天的日期
select trunc(sysdate, 'year') from dual; 01-1月 -08 //所在这一年的第一天
select trunc(sysdate, 'month') from dual; 所在这一月的第一天
// to_char 把数值转换成字符串
select to_char(3.14) from dual; //把数值转换成字符串
//to_number 把字符串转换成数值
select to_number('3.14') from dual; //把字符串转换成数值
//
select to_char(3.14, '0.0000') from dual; //得到的结果3.1400
select to_char(3.14, '9.9999') from dual; //得到的结果3.1400
select to_char(3.14, '99.9999') from dual; 3.1400
select to_char(3.14, '09.9999') from dual; 03.1400 前面是0的话，则结果要 用0补齐
select to_char(3.14, '$09.9999') from dual; $03.1400
elect to_char(3.14, 'L09.9999') from dual; RMB03.1400
select to_char(3.14, 'L000,009.9999') from dual; RMB000,003.1400
select months_between( dJoiningDate,dBirthDate )from employee
select cEmployeecode ,to_char(nannualsalary*0.03,'L999,999.99' )from annualsalary
select vFirstName||vLastName,length(vEmailId) from ExternalCandidate;
nvl 将空置用字符替换掉
// nvl(vEmailId,'@') 如果没有值，就自己设置一个字符表示
select nvl(vEmailId,'@') from ExternalCandidate;
coalesce(vEmailId,vFirstname) 可以多个参数，先查找第一列，如果有值就显示，如果没有，就查第二列，有再显示。只显示一个。两个都没有则不显示。
select coalesce(vEmailId,vFirstname) from ExternalCandidate;
select coalesce(vEmailId,cphone)"联系方式" from ExternalCandidate;
///////////////////////
case col switch(col)
when 'a' then ..... case 'a': ......break
when 'b' then .... case 'b': ......break
else ....... case 'c': ......break
end default ......
l
例子：
select vFirstname ,vLastname ,
case cstate
when 'califotnia' then 'send a letter'
else 'send a card'
end
from employee
//2.
select vFirstname ,vLastname ,
case cstate
when 'Califotnia' then 'send a letter'
when 'New York' then 'call'
else 'send a card'
end
from employee;
//等值联接
//如果从两个表里取东西时，要指名是那张表里的要用 xxx.（点）xxx
select vFirstName, vLastName, nAnnualsalary from annualsalary, employee where annualsalary.cemployeeCode = employee.cemployeeCode;
//可以用别名的，annualsalary a, employee e
//1表示
select vFirstName, vLastName, nAnnualsalary from annualsalary a, employee e where a.cemployeeCode = e.cemployeeCode;
//2表示
select vFirstName, vLastName, nAnnualsalary
from annualsalary a, employee e
where a.cemployeeCode = e.cemployeeCode;
//3表示 join 。。。。on。。。。
select vFirstName, vLastName, nAnnualsalary
from annualsalary a join employee e
on a.cemployeeCode = e.cemployeeCode;
//
select vFirstName, vLastName, vDepartmentName
from Department a join employee e
on a.cDepartmentCode = e.cDepartmentCode;
//指定特定的人 ，并且排序
select vFirstName, vLastName, vDepartmentName
from Department a join employee e
on a.cDepartmentCode = e.cDepartmentCode
and vFirstName = 'Helen'
order by vDepartmentName;
//
select vLastName||'.'||vFirstName, cCountry ,cName
from ExternalCandidate e , Country c ,ContractRecruiter r
where e.cCountryCode = c.cCountryCode
and e.cContractRecruiterCode = r.cContractRecruiterCode;
//查表
select * from user_tables;
select table_name from user_tables;
select * from all_tables; //查数据库里面所有表
select vFirstName,nRating from externalcandidate
select * from rating
//非等值连接 得到的值 ，在表中是一个范围的话就这样表示（上面分开显示的情况）
select vFirstName, cCandidateRating
from externalcandidate e, rating r
where e.nRating between r.nlowerrating and r.nhigherrating;
//
select vFirstName, vDepartmentName
from employee e, department d
where e.cDepartmentCode = d.cDepartmentCode;
@//等值 自然连接 natural join 这里适合一个相同的列
select vFirstName, vDepartmentName
from employee natural join department;
//有多个相同的列时 用join 。。。using (相同列)
select vFirstName, vDepartmentName
from employee join department
using (cDepartmentCode);
//交叉连接 cross join
select vFirstName, vDepartmentName
from employee cross join department;
//练习
//1
select vFirstName, cNewspapername
from externalcandidate e, Newspaper n,NewsAd a
where e.cNewsAdNo = a.cNewsAdNo
and a.cNewspaperCode = n.cNewspaperCode;
//2显示两者都有的
select vfirstname ,cname
from externalcandidate e ,recruitmentagencies r
where e.cagencycode = r.cagencycode
//左外部连接 select ……from ……. Left outer join…….on…….
显示左边的所有内容，则右边是两者共有的部分
select vfirstname ,cname
from externalcandidate e left outer join recruitmentagencies r
on e.cagencycode = r.cagencycode
//右外部连接 select ……from ……. Right outer join…….on…….
显示右边的所有内容，则右边是两者共有的部分
select vfirstname ,cname
from externalcandidate e right outer join recruitmentagencies r
on e.cagencycode = r.cagencycode
//还可以用（+）表示
（+） 跟在谁后面，谁就显示两者共有的部分。
select vfirstname ,cname
from externalcandidate e ,recruitmentagencies r
where e.cagencycode = r.cagencycode(+);
//完整外部连接 select ……from ……. Full outer join…….on…….
两者全部显示
select vfirstname ,cname
from externalcandidate e full outer join recruitmentagencies r
on e.cagencycode = r.cagencycode;
练习
select x.vfirstname "应聘者",y.vfirstname"接待人"
from externalcandidate x,employee y
where x.cinterviewer = y.cemployeecode(+);
2.左外部连接
select x.vfirstname "应聘者",y.vfirstname"接待人"
from externalcandidate x left outer join employee y
on x.cinterviewer = y.cemployeecode;
//自联接
select x.vfirstname "员工",y.vfirstname"主管"
from employee x, employee y
where x.csupervisorcode = y.cemployeecode;
课本练习4课
1. select initcap(vToyName) from Toys ;
//多行函数
// avg （） 平均值
select avg(ntestscore) from externalcandidate
//
select avg(ntestscore) from externalcandidate
where ntestscore >= 70;
//sum （） 求和的
select sum(ntestscore) from externalcandidate
// max （） 最大值
select max(ntestscore) from externalcandidate
// min （） 最小值
select min(ntestscore) from externalcandidate
// count（） 返回查询的行数。 有效个行数多少，如果有人没值的话的，就当不存在。
select count(ntestscore) from externalcandidate
select count(vEmailId) from externalcandidate
// count(*) 表示列出所有的行数，包括没有值的
select count(*) from externalcandidate
//
//执行函数前，先执行去掉重复的
select count(distinct cState) from externalcandidate;
//
select max(npercentageCharge)"Maximum hiring charge",
min(npercentageCharge)"Minimum hiring charge",
avg(npercentageCharge)"averge hiring charge"
from recruitmentAgencies
where cCity = 'Alexandria';
//stddev（） 返回一组值的标准偏差
select stddev (nmonthlysalary) from monthlysalary
// variance（） 返回一组值的方差
select variance (nmonthlysalary) from monthlysalary
// group by 把相同的组合到一起去 ，后面显示的内容必须是前面显示的内容，都写出来。先执行group by 的内容
//group by 按后面跟的第一个对象来组合
select vFirstName ,cDepartmentCode
from employee
group by cDepartmentCode, vFirstName;
//与上面的区别
select vFirstName ,cDepartmentCode
from employee
order by cDepartmentCode, vFirstName;
//
select cDepartmentCode --,count(cDepartmentCode)
from employee
order by cDepartmentCode;
//先分组后算函数（是分组后的每个小组里面再分别统计行数）
select cDepartmentCode ,count(cDepartmentCode)
from employee
group by cDepartmentCode;
//
select cState, count(cState)
from externalcandidate
group by cState; ？？？？
//
select cState, avg(ntestscore)
from externalcandidate
group by cState;
Having 在分组后再执行having子句 用来组合函数，用having
？？
//每个州人数超过2的州的挑出来
select cState
from externalcandidate
group by cState
having count(cstate) > 2;
//1
select cCity,count(vQualification)
from externalcandidate
group by cCity,vQualification
having vQualification='MBA';
//2
select cCity,count(cCity)
from externalcandidate
where vQualification='MBA'
group by cCity,vQualification;//先执行where 这个条件
// 3
select cCity,count(vQualification)
from externalcandidate
where vQualification='MBA'
group by cCity,vQualification;
//
select vDepartmentName, count(cemployeecode)
from department natural join employee
group by vDepartmentName
//子查询
//子句
/*select cCity
from employee
where vfirstname = 'Catherine' and vlastname = 'Roberts';
*/
//主句
/*select vfirstname ,vlastname
from employee
where ccity = 'Norton'
*/
//组合上面的两个
select vfirstname ,vlastname
from employee
where cCity =
(select cCity
from employee
where vfirstname = 'Catherine' and vlastname = 'Roberts');
//单行子查询
select vfirstname ,vlastname
from externalcandidate
where ntestscore >
(select avg(ntestscore)
from externalcandidate);
//从
with result as
(select vfirstname ,ntestscore from externalcandidate where ntestscore > 80)
select avg(ntestscore) from result
//多行子查询 in，any，all， not in
in
select vfirstname ,vlastname ,ntestscore
from externalcandidate
where ntestscore in
(select ntestscore
from externalcandidate
where ntestscore > 90);
//any
select vfirstname ,vlastname ,ntestscore
from externalcandidate
where ntestscore >any
(select ntestscore
from externalcandidate
where ntestscore > 90);
//1
select vlastname,vfirstname
from employee
where ccurrentposition =
(select cdepartment where )
//2
select vlastname,vfirstname
from employee natural join department
where vdepartmentname = 'Sales';
//
select vfirstname,cphone
from externalcandidate
where vfirstname in
(select vfirstname
from employee
where djoiningdate < '1-6月-98');
//其他子查询
select c.ccollegecode, ccollegename from college c
where 5 <= (select count(*) from externalcandidate where ccollegecode = c.ccollegecode);
select ccollegecode, ccollegename from college;
select count(*) from externalcandidate e, college c where e.ccollegecode = c.ccollegecode;
//创建与管理
//创建表create table
create table student
(
vname varchar2(4),
nage number(3,0),
csex char(2),
nscore number(4,1)
);
//
create table teacher
(
vname varchar2(4) not null,
nage number(3,0)
);
//查询创建的表
select * from user_tables;
// alter table 修改表，add 加入列
alter table teacher
add cphone char(8)
// colname 列名
alter table teacher
add colname char(3) not null;
//新增加的列可以设置 默认值（default）
alter table department
add col char(3) default 'abc' not null;
//删除表drop table
drop table student;
// drop column删除表中的列
alter table department
drop column col;
// modify 修改表列中的内容
alter table department
modify col char(6);
//
char类型，只能增加长度；有一种特例那就是当char那一列都是为空的时候，才可以修改该类型。Varchar类型则不是了。
//
alter table department
modify col char(15) default'0001';
//这里的default 是指从表中已有的行的后面再增加新的行时，default的默认值才开始起作用。
//插入行insert into …….. values('0011','name','head1','locl');
insert into department values('0011','name','head1','locl');
//
insert into department values('0013',null,'head4','loc4');
select * from department;
//插入行 ，指定列名插入值
insert into department (cdepartmentcode,vdepartmentname) values('0012','name');
select * from department;
//
/*create table student
(
code char(4),
name varchar(4)
)*/
/*
insert into student (code,name) values('001','nm1');
select *from student;
*/
select *from department;
insert into department (cdepartmentcode,vdepartmenthead) select code, name from student; //指定列名，通过查询其他表，将里面的值添加到指定的行中去。
select *from department;
//更新(修改)所对应的列的值update 。。。。。。set 。。。。where。。。。
select * from department;
update department
set
vlocation = 'aisa'
where cdepartmentcode = '0013'; //不加条件，则一列所有的数据都被改掉了
select * from department;
//select * from department;
update department
set
vlocation = 'aisa',
vdepartmenthead = 'ads'
where cdepartmentcode = '0013';
select * from department;
//例子
Insert Into department(cdepertmentcode,vdepartmentname)values('0025','develepment');
update employee
set
cdepartmentcode = '0025'
where cdepartment = '0005';
//删除一行数据
delete student
where code = '001';
//合并语句
merge into teacher t
using student s
on (t.code = s.code)
when matched then
update set
t.name = s.name
when not matched then
insert (t.code,t.name)
values(s.code,s.name);
//主键约束
create table student
(
code char(4),
name varchar(10),
constraint sc_pk primary key(code) //主键创建再列上
)
//
insert into student values(null,'abc');//主键约束不能为空
insert into student values('0001','abc');
//主键创建再表上
create table student
(
code char(4) constraint sc_pk primary key,
name varchar(10),
)
//
create table student
(
code char(4) primary key,
name varchar2(10)
);
//查询已有主键
select * from user_constraints
//创建外键
create table teacher
(
code char(4),
name varchar2(10),
constraint teacher_code_pk primary key (code)
);
create table student
(
code char(4),
name varchar2(10),
teacher char(4),
constraint student_code_pk primary key (code),
constraint student_teacher_fk foreign key (teacher) references teacher(code)—创建外键
);
insert into student values('0002','dfc','0002');
insert into student values('0003','dfc','0002');
//创建唯一约束
create table student
(
code char(4),
name varchar2(10),
teacher char(4),
constraint student_code_uk unique (code)—创建唯一约束
);
//检查约束
create table student
(
code char(4),
name varchar2(10),
score number(4,1),
constraint score_ck check (score between 0 and 100) --创建检查约束
);
insert into student values('0001','aaa',89);
insert into student values('0001','aaa',101);--违反约束
//非空约束
create table student
(
code char(4),
name varchar2(10) not null –非空约束
);
////
insert into student values('0001',null)
//
create table student
(
code char(4),
name varchar2(10) not null,
constraints sn_uk unique(name)—非空及唯一约束
);
//
/*
create table teacher
(
code char(4),
name varchar2(10)
);
*/
// add 添加主键
alter table teacher
add constraint teacher_pk primary key (code);//添加主键
//// drop 删除主键
alter table teacher
drop constraint teacher_pk;//删除主键
// disable 禁用主键
cascade-- 强制性禁用
alter table student
disable constraint student_code_pk;--禁用主键，如果要禁用的主键还有其他类型的建在上面作用的话，主键不能禁用。如要禁用需要用强制性禁用，则应加cascade。
//alter table student
disable constraint student_code_pk cascade;-- 强制性禁用
// enable 恢复主键
alter table student
enable constraint student_code_pk;--恢复主键，这里必须把表里数据改写正确才可以恢复。
//使用事务
显示事务
commit;//提交语句
update employee
set
vfirstname = 'dsf'
where cemployeecode = '000004';
commit;//提交语句
select cemployeecode ,vfirstname from employee
where cemployeecode = '000004';
//隐式事务 正常提交事务的时候也会自动提交的
show autocommit 查看自动提交语句是否打开的
set autocommit on 打开自动提交语句。
set autocommit off 关闭自动提交语句。
//
//回复修改
Rollback 撤销上次的提交时候的位置
Savepoint 保存点
//例子
update employee
set
vfirstname = 'ccccc'
where cemployeecode = '000005';
savepoint step2; //设置保存点
update employee
set
vfirstname = 'ddddd'
where cemployeecode = '000004';
savepoint step3;
insert into employee(cemployeecode,vfirstname) values('000026','3310');
savepoint step4;
delete employee where cemployeecode = '000025';
rollback to step4; //撤销到step4这个点，以后的都被撤销掉了，前面的还有。
select cemployeecode,vfirstname from employee;
//上锁
用两个oracle的工具时，一个工具在用一个数据时，另一个工具就不能对这个数据进行修改，这个过程就时上锁。
//创建视图
// create view。。。as 创建视图
create view employee_view
as select vfirstname, vlastname, cphone from employee;
//查看是否以创建了
select * from user_views;
//查询表的数据
select * from employee_view;
//显示视图的结构
desc employee_view;
//修改创建的视图，实际上就是修改他的原地址的数据信息
update employee_view
set vlastname = '2222'
where vfirstname = 'ccccc';
select * from employee_view;
select * from employe;--查询原数据
//
create view emp_dept_view
as select vfirstname, vlastname,vdepartmentname
from employee natural join department;
select *from emp_dept_view;
// force 表示强制的创建 不管用到的基表是不是存在的，都创建视图。
create force view name_view
as select * from name;--不存在的基表
select * from user_views;
// or replace 表示没有视图就创建视图，如果有就修改现有的视图。
create or replace view name_view
as select * from employee;
select * from user_views;
// with check option;
create view ex_view
as select * from externalcandidate where ntestscore > 85
with check option;
//
update ex_view
set ntestscore = 60 --这里条件要在条件范围里才行
where ccandidatecode = '000010';
// drop view 删除视图
drop view ex_view;
// group by 用了这个不能修改视图数据
create or replace view employee_view
as select cstate,count(cstate)"count" from employee group by cstate;
update employee_view
set cstate ='abc'
where cstate = 'Georgia';
//
create or replace view employee_view
as select rownum"row",count(cstate)"count" from employee group by rownum;
//不能修改视图数据
create or replace view employee_view
as select vfirstname||vlastname"fl" from employee ;
//
insert into employee_view values('abcd');
//Top-n分析法
select rownum,vfirstname,vlastname
from(select vfirstname,vlastname from externalcandidate order by ntestscore desc)
where rownum <=3;--分数最高的3个人
//
//创建序列
create sequence emp_seq
increment by 1 -- 指定两个数之间的间隔，这里是1
start with 20 --指定序列初始值
maxvalue 30 --最大序列值
--minvalue --最小序列值
--cycle --表示循环
--cache 10 –预存在内存里的序列
//查询创建的序列
select * from user_sequences
//检查序列是否创建好
select sequence_name ,max_value, min_value,increment_by,last_number from user_sequences;
返回用户定义的序列列表和它的最大值，最小值，增量，和终值
//
select emp_seq.nextval from dual;--每生成下一个序列的数值
select emp_sep.currval from dual;--查看当前值的序列的数值
insert into employee (cemployeecode, vfirstname) values(emp_sep.nextval,'abc');
insert into employee (cemployeecode, vfirstname) values(to_char(emp_sep.nextval,'000 00'),'abc');
//修改最大值
alter sequence emp_sep
maxvalue 35
cycle
//不能修改初始值
alter sequence emp_sep
start with 10;
// 删除序列
drop sequence emp_sep;
//创建索引
create index emp_name_index
on employee (vfirstname);
//查询用户的索引
select * from user_ind_columns;
//创建同义词
create synonym ec
for externalcandidate;
//
select * from ec;
drop synonym ec；
//创建用户
create user student
identified by abc;
//设置连接数据库 权限
grant create session
to student;
//设置创建表 ， 创建视图，创建序列 权限
grant create table ,create view ,create sequence
to student;
//把查询teacher的权限给student
grant select on teacher to student;
//
insert into sys.teacher value('0001',sg)
//修改密码
alter user student identified by cde
//用户修改密码 ？？？？？？
//删除权限
drop user student
//撤销权限
revoke create table from student1;
//撤销权限 ？？？？？
revoke alter on teacher from student1;
//
grant alter on teacher to student with grant option;
//让student 拥有dba的角色（管理员）
grant dba to student
//PL/SQL
declare--声明部分（变量，常量）
begin --块（可执行部分）
exception(可选的)
end;
//显示输出内容
set serveroutput on
//用c_ 表示常量；用v_ 表示变量; %type 表示与前面一样的类型；
Constant 表示常量，它的值不能再改变了
declare
c_numOfEmployees constant number(4):=123;--只是一行的数据
v_name employee.vfirstname%type not null:='abc';
begin
dbms_output.put_line('abcdefg');--输出语句
end;
//
declare
c_numOfEmployees constant number(4):=123;
v_name employee.vfirstname%type not null:='abc';
begin
dbms_output.put_line(v_name);--可以是变量
end;
// select。。。into。。。where。。。表示查询数据
declare
v_firstname employee.vfirstname%type; --只是一行的数据
v_lastname employee.vlastname%type;
begin
select vfirstname,vlastname -- 这里的数据类型与下面是对应的
into v_firstname,v_lastname
from employee
where cemployeecode='000005';
dbms_output.put_line(v_firstname || v_lastname);
end;
//
declare
begin
insert into employee(cemployeecode,vfirstname)
values('000029','jack');
end;
//从一个表中查询出指定一行的数据，把它的结果插入到另一个表中
declare
v_vfirstname employee.vfirstname%type;
v_vlastname employee.vlastname%type;
v_phone employee.cphone%type;
v_birthdate employee.dbirthdate%type;
begin
select vfirstname,vlastname,cphone,dbirthdate
into v_vfirstname,v_vlastname,v_phone,v_birthdate
from externalcandidate
where vfirstname= 'Nancy'and vlastname = 'King';
insert into employee(cemployeecode,vfirstname,vlastname,cphone,dbirthdate)
values( '000032' , v_vfirstname,v_vlastname,v_phone,v_birthdate );
dbms_output.put_line('000035'||v_firstname||v_lastname||c_phone||v_birthdate);
end;
///查找是否插入了
select cemployeecode,vfirstname,vlastname,cphone,dbirthdate from employee;
//判断语句
declare
begin
if....then
..
elsif....then
....
else
....
end if;
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate
where ccandidatecode ='000002';
if v_score>90
then dbms_output.put_line('good');
elsif v_score>80
then dbms_output.put_line('average');
else dbms_output.put_line('bad');
end if;
end;
//循环语句
//循环语句1
declare
begin
loop --循环开始的地方
。。。
exit when
。。。
end loop; --循环结束的地方
end;
//例子
declare
v_score number(3):=0;
begin
loop --循环开始的地方
v_score:=v_score+1;
--exit when v_score>5;
dbms_output.put_line(v_score);
exit when v_score>5;--当条件不成立，退出循环。一定非要放在这里，可以放在输出前面。
end loop;--循环结束的地方
end;
//循环语句2
declare
begin
for i in 1..10
loop
。。。
end loop;
end;
//
declare
v_score number(3):=0;
begin
for i in 1..10 --输出从1到10
loop
dbms_output.put_line(i);
end loop;
end;
// //循环语句3 reverse
declare
v_score number(3):=0;
begin
for i in reverse -2..10 --倒着输出
loop
dbms_output.put_line(i);
end loop;
end;
//循环语句4
declare
...
begin
while 。。。 --当条件成立，继续循环，否则，跳出循环
loop
.。。。...
end loop;
end;
//
declare
v_score number(3):=0;
begin
while v_score < 5
loop
v_score:=v_score+1;
dbms_output.put_line(v_score);
end loop;
end;
//组合的用法
declare
v_score number(3):=0;
begin
while v_score < 5
loop
v_score:=v_score+1;
dbms_output.put_line(v_score);
exit when v_score =3; -- 循环到3就跳出循环
end loop;
end;
//1. cursor 声明游标
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is --声明游标和select关联起来
select vfirstname,vlastname -- 注这里没有into下面fetch时才用了
from employee;
begin
open result;
fetch result into v_firstname,v_lastname;--取出游标里的数据, 每调用一次，他就指到下一句语句，如果是单独一句语句，那就指的是当前。
dbms_output.put_line(v_firstname || v_lastname);
fetch result into v_firstname,v_lastname;
dbms_output.put_line(v_firstname || v_lastname);
close result;
end;
//2.
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop --循环取出数据，这里取到最后没有下一行时，就重复取最后一行到报错。
fetch result into v_firstname,v_lastname;
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
end;
//3.
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname
from employee;
begin
for row1 in result -- 取出一行里数据的每一条值
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname);
end loop;
end;
//4.
declare
v_score number(3):=0;
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
--cursor result is
--select vfirstname,vlastname from employee;
begin
--open result;
for row1 in (select vfirstname,vlastname from employee) --这里直接把查询语句代替了游标
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname);
end loop;
--close result;
end;
//5.
declare
begin
for row1 in (select vfirstname,vlastname,csex,ntestscore from externalcandidate where ntestscore >75)
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname ||' ' || row1.ntestscore);
end loop;
end;
//
declare
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
dbms_output.put_line(result%rowcount);--取得当前行号
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
fetch result into v_firstname,v_lastname;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
fetch result into v_firstname,v_lastname;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop
fetch result into v_firstname,v_lastname;
exit when result%notfound;--%notfound表示是否还有值,如果没有数据就表示true
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop
fetch result into v_firstname,v_lastname;
exit when result%notfound;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
if result%isopen then --表示游标有没有关掉
dbms_output.put_line('open');
else
dbms_output.put_line('close');
end if;
end;
//异常
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate
where ccandidatecode = '000103';
dbms_output.put_line(v_score);
exception --异常处理
when no_data_found then --no_data_found 不能找到
dbms_output.put_line('no data found');
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate;
--where ccandidatecode = '000103';
dbms_output.put_line(v_score);
exception --异常处理
when no_data_found then --no_data_found 不能找到数据
dbms_output.put_line('no data found');
when too_many_rows then --no_many_rows 行太多
dbms_output.put_line('too_many_rows');
when others then -- --其他所有的都可以处理
dbms_output.put_line('sth is wrong');
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
insert into employee(vfirstname)
values('abc');
exception --异常处理
when others then
dbms_output.put_line('sth is wrong');
end;
//
declare
v_score externalcandidate.ntestscore%type;
null_in_pk exception;
pragma exception_init(null_in_pk,-01400);
begin
insert into employee(vfirstname)
values('abc');
exception --异常处理
when null_in_pk then
dbms_output.put_line('null can not be insert into pk');
end;
//创建一个存储过程
Create （or replace） procedure name2department
(
name in varchar2 -- in 表示输入参数
)
is --is下面相当于声明部分
v_code employee.cdepartmentcode%type;
v_department department.vdepartmentname%type;
begin
select cdepartmentcode --先通过名字查处部门的代号，
into v_code
from employee
where vfirstname = name;
select vdepartmentname --再通过部门的代号查处部门的名字
into v_department
from department
where cdepartmentcode = v_code;
dbms_output.put_line(v_department);
end;
//通过上面 查询部门的名字
exec name2department('Angela');
//
create (or replace) procedure name2department
(
name in varchar2,
depart out varchar2 --out 放入值
)
is
v_code employee.cdepartmentcode%type;
v_department department.vdepartmentname%type;
begin
select cdepartmentcode
into v_code
from employee
where vfirstname = name;
select vdepartmentname
into v_department
from department
where cdepartmentcode = v_code;
depart:=v_department;
end;
//接上面
create or replace procedure name2depar
(
name in varchar2
)
is
v_department department.vdepartmentname%type;
begin
name2department(name,v_department);
dbms_output.put_line(v_department);
end;
//？？？？？？？
exec name2depart('Angela');
//
触发器 需要在Oracle Enterprise Manager Console 中的方案(通过管理员登录)中的触发器。
// 触发器主体：
begin
dbms_output.put_line('abcsd');
end;
//
update employee
set vfirstname='sdsf'
where cemployeecode = '000030';
//触发器主体：
begin
dbms_output.put_line(:old.vfirstname);
end;
| Oracle 的代码表示及事例 |
1. select + xxx + from + xxx //查询语句 xxx 表示一个表
Select * from + xxx // * 表示一个列表中所有的内容
类： 1）// select * from country
2）// select vLastName, vFirstName from externalcandidate
“--”表示单行注释
“/* ... */ ”表示多行注释
2. desc + xxx // 查看表的结构 recruit
desc externalcandidate
3. select cCountryCode "编号",cCountry "国家" from country; //把标题名字换了
4. select distinct cCity from externalcandidate
加了个distinct，表示没有重复的了，重复显示一个。如果是两列的话有两个都是重复的话，就保留一个
select distinct cCity,CState from college
5.运算符
and
or
not
|| 连接两个字符串，把两列的数据合到一起去了
如：select vLastName || vFirstName from externalcandidate
//把两列的数据合起来的标题名字换了
select vLastName || vFirstName "name" from externalcandidate
//把两列的数据合起来之间加一个点
select vLastName || '.' ||vFirstName "name" from externalcandidate
//把两列的数据合起来之间加一个字符串
select vLastName || 'von' ||vFirstName "name" from externalcandidate
6. //把列出的数据乘上5的结果是多少
select nAnnualsalary * 5 from Annualsalary
//where nAnnualsalary > 40000 加入的条件语句放在后面 表示大于40000的把它列出来
select nAnnualsalary from Annualsalary where nAnnualsalary > 40000
//通过条件查找特定的对象
select vLastName, vFirstName from externalcandidate where vLastName = 'King' ;
// 表示两者之间
select nAnnualsalary from Annualsalary where nAnnualsalary > 30000 and nAnnualsalary < 40000;
//表示两者之间 包括两者的界限 30000和40000
//where 。。。 between 。。。 and 。。。;
select nAnnualsalary from Annualsalary where nAnnualSalary between 30000 and 40000;
//查询具体个对象 where +与前面的对象一样+in ('。。。', '。。。' ,'。。。')
select vFirstName from externalcandidate where vFirstName in ('Carol', 'Catherine' ,'David')
//查找比较模糊的对象 where 。。。 like '。。。%'
select vFirstName from externalcandidate where vFirstName like 'Da%' ;
select vFirstName from externalcandidate where vFirstName like '%in%' ;
select vFirstName from externalcandidate where vFirstName like 'L_nda' ;//这里的下划线表示一个字符
7.作业
1. select vLastName || vFirstName "名字" ,cCity ,nTestScore from externalcandidate where cCity = 'New York City' and nTestScore > 60;
2. select cZip"邮编" ,cState || cCity || vAddress"地址", vLastName || vFirstName"姓名 ", nTestScore from externalcandidate where nTestScore > 60;
3. select distinct cEmployeeCode ,nMonthlySalary ,nReferralBonus from monthlysalary where (nMonthlySalary * 12 + nReferralBonus) > 30000;
///////////////////////////
//
1. where 。。。 is null;当什么为空的时候
select vFirstName,VlastName, cPhone from externalcandidate where vEmailid is null;
2. where .... is not null 当什么不为空的时候
select vFirstName,dInterviewDate from externalcandidate where dInterviewDate is not null
3.order by 。。。（asc） 把数据按照。。。排序（升序排列）
如：
select vFirstName,VlastName, vEmailid from externalcandidate where vEmailid is not null order by vLastName asc;
//
order by 。。。desc 把数据按照。。。排序（降序排列）
select vFirstName,VlastName, vEmailid from externalcandidate where vEmailid is not null order by vLastName desc;
//
select vFirstName,VlastName, nTestScore from externalcandidate where nTestScore >=60 order by nTestScore desc;
//排两次
select * from annualsalary order by nAnnualsalary ,nYear ;？？？？？？
//toy的表需要换recruit
select cOrderNo, cToyId ,nToyCost from OrderDetail order by nToyCost desc;
select vFristName, vLastName ,vAddress from recipient where vToyName like 'B%';
select nShippingCharges from orders where dorderdate = '20-5月-99'
select vToyDescription, vToyName from toys where nToyRate between 50 and 120;
8.生产一个亚元表 dual
select * from dual
//select 'abc' from dual //如果没订定列的名字，则为字符的少一位表示
//函数有：Upper ，Lower ，initCap ，ltrim ，rtrim
Upper 转换成大写
Lower 转换成小写
select upper('abc') from dual
select upper(vFirstName),vFirstName from employee //前面大写，后面一个是原本的样子
select lower(vFirstName),vFirstName from employee
initCap 把结果每个词的首字母大写
//select initCap('acdfg')from dual;
select initCap('acdfg class')from dual;//两个字符的第一个字母大写
select initCap('AVDSGGS class')from dual; //保留首字母大写其他都小写
concat 连接俩个字符串，只能是两个。
select concat('good','morning')from dual;
substr 提取字符下标之间的字符 第一个数字表示下标是从几位开始的,第二个数字表示几位数。
select substr('sfdf',2,3)from dual;//第二位开始，以后的三个字符
select substr('abcdefg',2,4)from dual;
instr 查找对应的字符在字符串中的位置 下标是从1开始的，第一次出现的位子
select instr('abcdefg','d')from dual;
select instr('abcdefg','h')from dual;
select instr('abcdefg','cde')from dual;
length 字符串长度
select length('abcdefg')from dual;
1 .select length (vFirstName||vFirstName) from employee
2. select length (vFirstName||vFirstName) from employee
Lpad 从左边填充字符。第二位表示指定第一位的字符的个数，第三位表示要加入的字符，如果第二位的位数小于实际第一位给出的位数，则显示的就是第一位上的前几位字符。
select lpad('abcdef',8,'$') from dual;
select lpad('abcdef',12,'12') from dual;// 填充顺着填
select lpad('abcdef',4,'12345') from dual;//后面的没有起到作用，因为第二位要求的位数少。
rpad从右边填充字符。同上
select rpad('abcdef',4,'12345') from dual;
ltrim // 从左边去掉对应字符。只看第一个字母，必须是从第一个字母开始删除。如果要删除的两个字符是间隔的，那就删除第一个就好。
select ltrim('abcdef','ab') from dual;
select ltrim('abcdef','ac') from dual; //c没有用
select ltrim('abcdef','bb') from dual; // 不能删除
rtrim从右边去掉对应字符，同上不过删除字符从后面向前删除
select rtrim('abcdef','ef') from dual;
select rtrim('abcdef','bef') from dual;
select rtrim('abcdef','bec') from dual;//只看要删除的字符的最后面的第一个字母
select upper(cName), substr(vAddress,1,10) from contractrecruiter
abs(-5) 取绝对值
select abs(-5) from dual
ceil(3.14) 取整数值为4 取大的
select ceil(3.14) from dual
floor(3.14) 取整数值为3 取小
select floor(3.14) from dual
power(2, 4) 取数的次方的 2的4次方
select power(2, 4) from dual
mod(11, 4) 取模的
select mod(11, 4) from dual
round(3.1415926, 2) 取小数的位数 四舍五入 。正数表示小数点后保留几位。为负数表示小数点向前几位，用零替换。
select round(3.1415926, 2) from dual
select round(123.1415926,-2) from dual //值为100
select round(163.1415926,-2) from dual //值为200
trunc(163.1415926,2) 取小数的位数 直接去掉 其他和上面一样
select trunc(163.1415926,2) from dual
sqrt(64) 开平方
select sqrt(64) from dual
//列子
select vFirstName from externalcandidate where ceil(sqrt(nTestScore)*10)>75
select dInterviewDate from externalcandidate where dInterviewDate = '21-8月-98'
//日期
//sysdate 当前日期
select sysdate from dual;
sysdate+3 算天的
select sysdate+3 from dual;
// greatest 比较两个日期，那个日期比较大
select greatest(sysdate , '8-8月-08') from dual;
// least比较那个日期比较小
select least(sysdate , '8-8月-08') from dual;
// months_between
months_between(sysdate,'8-8月-8') //比较两个日期之间相差多少个月，得出来是小数 。前面减后面的值
select months_between(sysdate,'8-8月-8') from dual;
//add_ months 增加几个月后的日期
select add_ months(sysdate,+7) from dual;
//next_day/算下个星期几是什么日期
next_day(sysdate,'星期二') //算下个星期几是什么日期
select next_day(sysdate,'星期二') from dual;
select next_day(sysdate,3) from dual; //也可以这样表示 ，1表示星期日 2表示星期一
select months_between(sysdate,dInterviewDate) from externalcandidate
//类型转换
// desc employee
long
clob
raw（）
long raw 2G
blob 4G二进制数据
bflie
//用”yy”表示显示几位数，‘y’如果是一位就显示一位，
day 表示星期几的意思；ddd表示一年的第几天；d 表示星期几，用数字表示的；hh24/mi/ss 表示24小时制的时间；am.pm表示上午，下午。
1. to_char(sysdate, 'yyyy-mm-dd') //转换成字符串形式 // 2008-08-07
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate) from dual;
to_char(sysdate, 'yy-month-dd') /转换成字符串形式 08-8月 -07
select to_char(sysdate, 'yy-month-dd') from dual;
to_char(sysdate, 'yy-month-dd day') 08-8月 -07 星期四
select to_char(sysdate, 'yy-month-dd day') from dual;
select to_char(sysdate, 'yy-month-dd day ddd') from dual;// 08-8月 -07 星期四 220天（一年的第220天）
select to_char(sysdate, 'yy-month-dd d day ddd') from dual;// 08-8月 -07 5 星期四 220
//显示时间 12小时制的
select to_char(sysdate, 'yy-month-dd d day ddd hh/mi/ss') from dual; // 08-8月 -07
5（表示星期几的意思用数字表示的） 星期四 220 03/22/50
//显示时间 24小时制的
select to_char(sysdate, 'yy-month-dd d day ddd hh24/mi/ss') from dual; // 08-8月 -07 5 星期四 220 15/23/39
select to_char(sysdate, 'yy-month-dd d day ddd pm hh24/mi/ss') from dual;
//08-8月 -07 5 星期四 220 下午 15/24/59
//转换成特定字符
//今年是2008年08月07日下午 03点30分27秒
select to_char(sysdate, '"今年是"yyyy"年"mm"月"dd"日"am hh"点"mi"分"ss"秒"') from dual;
// to_date
select to_date('20080808','yyyymmdd') from dual; //08-8月 -08
select to_date('20080808','yyyymmdd') - sysdate from dual; .351493056 算多少天
select to_date('200808081945','yyyymmddhh24mi') - sysdate from dual;算多少天
select (to_date('200808081945','yyyymmddhh24mi') - sysdate )* 24 from dual; //算多少小时
// round 四舍五入的第一天
select round(sysdate, 'month') from dual; 01-8月 -08 //找到与他最近的日期 45度向前向后 ？？？？
select round(sysdate, 'year') from dual;
// trunc 表示当前的月或年等所在的第一天的日期
select trunc(sysdate, 'year') from dual; 01-1月 -08 //所在这一年的第一天
select trunc(sysdate, 'month') from dual; 所在这一月的第一天
// to_char 把数值转换成字符串
select to_char(3.14) from dual; //把数值转换成字符串
//to_number 把字符串转换成数值
select to_number('3.14') from dual; //把字符串转换成数值
//
select to_char(3.14, '0.0000') from dual; //得到的结果3.1400
select to_char(3.14, '9.9999') from dual; //得到的结果3.1400
select to_char(3.14, '99.9999') from dual; 3.1400
select to_char(3.14, '09.9999') from dual; 03.1400 前面是0的话，则结果要 用0补齐
select to_char(3.14, '$09.9999') from dual; $03.1400
elect to_char(3.14, 'L09.9999') from dual; RMB03.1400
select to_char(3.14, 'L000,009.9999') from dual; RMB000,003.1400
select months_between( dJoiningDate,dBirthDate )from employee
select cEmployeecode ,to_char(nannualsalary*0.03,'L999,999.99' )from annualsalary
select vFirstName||vLastName,length(vEmailId) from ExternalCandidate;
nvl 将空置用字符替换掉
// nvl(vEmailId,'@') 如果没有值，就自己设置一个字符表示
select nvl(vEmailId,'@') from ExternalCandidate;
coalesce(vEmailId,vFirstname) 可以多个参数，先查找第一列，如果有值就显示，如果没有，就查第二列，有再显示。只显示一个。两个都没有则不显示。
select coalesce(vEmailId,vFirstname) from ExternalCandidate;
select coalesce(vEmailId,cphone)"联系方式" from ExternalCandidate;
///////////////////////
case col switch(col)
when 'a' then ..... case 'a': ......break
when 'b' then .... case 'b': ......break
else ....... case 'c': ......break
end default ......
l
例子：
select vFirstname ,vLastname ,
case cstate
when 'califotnia' then 'send a letter'
else 'send a card'
end
from employee
//2.
select vFirstname ,vLastname ,
case cstate
when 'Califotnia' then 'send a letter'
when 'New York' then 'call'
else 'send a card'
end
from employee;
//等值联接
//如果从两个表里取东西时，要指名是那张表里的要用 xxx.（点）xxx
select vFirstName, vLastName, nAnnualsalary from annualsalary, employee where annualsalary.cemployeeCode = employee.cemployeeCode;
//可以用别名的，annualsalary a, employee e
//1表示
select vFirstName, vLastName, nAnnualsalary from annualsalary a, employee e where a.cemployeeCode = e.cemployeeCode;
//2表示
select vFirstName, vLastName, nAnnualsalary
from annualsalary a, employee e
where a.cemployeeCode = e.cemployeeCode;
//3表示 join 。。。。on。。。。
select vFirstName, vLastName, nAnnualsalary
from annualsalary a join employee e
on a.cemployeeCode = e.cemployeeCode;
//
select vFirstName, vLastName, vDepartmentName
from Department a join employee e
on a.cDepartmentCode = e.cDepartmentCode;
//指定特定的人 ，并且排序
select vFirstName, vLastName, vDepartmentName
from Department a join employee e
on a.cDepartmentCode = e.cDepartmentCode
and vFirstName = 'Helen'
order by vDepartmentName;
//
select vLastName||'.'||vFirstName, cCountry ,cName
from ExternalCandidate e , Country c ,ContractRecruiter r
where e.cCountryCode = c.cCountryCode
and e.cContractRecruiterCode = r.cContractRecruiterCode;
//查表
select * from user_tables;
select table_name from user_tables;
select * from all_tables; //查数据库里面所有表
select vFirstName,nRating from externalcandidate
select * from rating
//非等值连接 得到的值 ，在表中是一个范围的话就这样表示（上面分开显示的情况）
select vFirstName, cCandidateRating
from externalcandidate e, rating r
where e.nRating between r.nlowerrating and r.nhigherrating;
//
select vFirstName, vDepartmentName
from employee e, department d
where e.cDepartmentCode = d.cDepartmentCode;
@//等值 自然连接 natural join 这里适合一个相同的列
select vFirstName, vDepartmentName
from employee natural join department;
//有多个相同的列时 用join 。。。using (相同列)
select vFirstName, vDepartmentName
from employee join department
using (cDepartmentCode);
//交叉连接 cross join
select vFirstName, vDepartmentName
from employee cross join department;
//练习
//1
select vFirstName, cNewspapername
from externalcandidate e, Newspaper n,NewsAd a
where e.cNewsAdNo = a.cNewsAdNo
and a.cNewspaperCode = n.cNewspaperCode;
//2显示两者都有的
select vfirstname ,cname
from externalcandidate e ,recruitmentagencies r
where e.cagencycode = r.cagencycode
//左外部连接 select ……from ……. Left outer join…….on…….
显示左边的所有内容，则右边是两者共有的部分
select vfirstname ,cname
from externalcandidate e left outer join recruitmentagencies r
on e.cagencycode = r.cagencycode
//右外部连接 select ……from ……. Right outer join…….on…….
显示右边的所有内容，则右边是两者共有的部分
select vfirstname ,cname
from externalcandidate e right outer join recruitmentagencies r
on e.cagencycode = r.cagencycode
//还可以用（+）表示
（+） 跟在谁后面，谁就显示两者共有的部分。
select vfirstname ,cname
from externalcandidate e ,recruitmentagencies r
where e.cagencycode = r.cagencycode(+);
//完整外部连接 select ……from ……. Full outer join…….on…….
两者全部显示
select vfirstname ,cname
from externalcandidate e full outer join recruitmentagencies r
on e.cagencycode = r.cagencycode;
练习
select x.vfirstname "应聘者",y.vfirstname"接待人"
from externalcandidate x,employee y
where x.cinterviewer = y.cemployeecode(+);
2.左外部连接
select x.vfirstname "应聘者",y.vfirstname"接待人"
from externalcandidate x left outer join employee y
on x.cinterviewer = y.cemployeecode;
//自联接
select x.vfirstname "员工",y.vfirstname"主管"
from employee x, employee y
where x.csupervisorcode = y.cemployeecode;
课本练习4课
1. select initcap(vToyName) from Toys ;
//多行函数
// avg （） 平均值
select avg(ntestscore) from externalcandidate
//
select avg(ntestscore) from externalcandidate
where ntestscore >= 70;
//sum （） 求和的
select sum(ntestscore) from externalcandidate
// max （） 最大值
select max(ntestscore) from externalcandidate
// min （） 最小值
select min(ntestscore) from externalcandidate
// count（） 返回查询的行数。 有效个行数多少，如果有人没值的话的，就当不存在。
select count(ntestscore) from externalcandidate
select count(vEmailId) from externalcandidate
// count(*) 表示列出所有的行数，包括没有值的
select count(*) from externalcandidate
//
//执行函数前，先执行去掉重复的
select count(distinct cState) from externalcandidate;
//
select max(npercentageCharge)"Maximum hiring charge",
min(npercentageCharge)"Minimum hiring charge",
avg(npercentageCharge)"averge hiring charge"
from recruitmentAgencies
where cCity = 'Alexandria';
//stddev（） 返回一组值的标准偏差
select stddev (nmonthlysalary) from monthlysalary
// variance（） 返回一组值的方差
select variance (nmonthlysalary) from monthlysalary
// group by 把相同的组合到一起去 ，后面显示的内容必须是前面显示的内容，都写出来。先执行group by 的内容
//group by 按后面跟的第一个对象来组合
select vFirstName ,cDepartmentCode
from employee
group by cDepartmentCode, vFirstName;
//与上面的区别
select vFirstName ,cDepartmentCode
from employee
order by cDepartmentCode, vFirstName;
//
select cDepartmentCode --,count(cDepartmentCode)
from employee
order by cDepartmentCode;
//先分组后算函数（是分组后的每个小组里面再分别统计行数）
select cDepartmentCode ,count(cDepartmentCode)
from employee
group by cDepartmentCode;
//
select cState, count(cState)
from externalcandidate
group by cState; ？？？？
//
select cState, avg(ntestscore)
from externalcandidate
group by cState;
Having 在分组后再执行having子句 用来组合函数，用having
？？
//每个州人数超过2的州的挑出来
select cState
from externalcandidate
group by cState
having count(cstate) > 2;
//1
select cCity,count(vQualification)
from externalcandidate
group by cCity,vQualification
having vQualification='MBA';
//2
select cCity,count(cCity)
from externalcandidate
where vQualification='MBA'
group by cCity,vQualification;//先执行where 这个条件
// 3
select cCity,count(vQualification)
from externalcandidate
where vQualification='MBA'
group by cCity,vQualification;
//
select vDepartmentName, count(cemployeecode)
from department natural join employee
group by vDepartmentName
//子查询
//子句
/*select cCity
from employee
where vfirstname = 'Catherine' and vlastname = 'Roberts';
*/
//主句
/*select vfirstname ,vlastname
from employee
where ccity = 'Norton'
*/
//组合上面的两个
select vfirstname ,vlastname
from employee
where cCity =
(select cCity
from employee
where vfirstname = 'Catherine' and vlastname = 'Roberts');
//单行子查询
select vfirstname ,vlastname
from externalcandidate
where ntestscore >
(select avg(ntestscore)
from externalcandidate);
//从
with result as
(select vfirstname ,ntestscore from externalcandidate where ntestscore > 80)
select avg(ntestscore) from result
//多行子查询 in，any，all， not in
in
select vfirstname ,vlastname ,ntestscore
from externalcandidate
where ntestscore in
(select ntestscore
from externalcandidate
where ntestscore > 90);
//any
select vfirstname ,vlastname ,ntestscore
from externalcandidate
where ntestscore >any
(select ntestscore
from externalcandidate
where ntestscore > 90);
//1
select vlastname,vfirstname
from employee
where ccurrentposition =
(select cdepartment where )
//2
select vlastname,vfirstname
from employee natural join department
where vdepartmentname = 'Sales';
//
select vfirstname,cphone
from externalcandidate
where vfirstname in
(select vfirstname
from employee
where djoiningdate < '1-6月-98');
//其他子查询
select c.ccollegecode, ccollegename from college c
where 5 <= (select count(*) from externalcandidate where ccollegecode = c.ccollegecode);
select ccollegecode, ccollegename from college;
select count(*) from externalcandidate e, college c where e.ccollegecode = c.ccollegecode;
//创建与管理
//创建表create table
create table student
(
vname varchar2(4),
nage number(3,0),
csex char(2),
nscore number(4,1)
);
//
create table teacher
(
vname varchar2(4) not null,
nage number(3,0)
);
//查询创建的表
select * from user_tables;
// alter table 修改表，add 加入列
alter table teacher
add cphone char(8)
// colname 列名
alter table teacher
add colname char(3) not null;
//新增加的列可以设置 默认值（default）
alter table department
add col char(3) default 'abc' not null;
//删除表drop table
drop table student;
// drop column删除表中的列
alter table department
drop column col;
// modify 修改表列中的内容
alter table department
modify col char(6);
//
char类型，只能增加长度；有一种特例那就是当char那一列都是为空的时候，才可以修改该类型。Varchar类型则不是了。
//
alter table department
modify col char(15) default'0001';
//这里的default 是指从表中已有的行的后面再增加新的行时，default的默认值才开始起作用。
//插入行insert into …….. values('0011','name','head1','locl');
insert into department values('0011','name','head1','locl');
//
insert into department values('0013',null,'head4','loc4');
select * from department;
//插入行 ，指定列名插入值
insert into department (cdepartmentcode,vdepartmentname) values('0012','name');
select * from department;
//
/*create table student
(
code char(4),
name varchar(4)
)*/
/*
insert into student (code,name) values('001','nm1');
select *from student;
*/
select *from department;
insert into department (cdepartmentcode,vdepartmenthead) select code, name from student; //指定列名，通过查询其他表，将里面的值添加到指定的行中去。
select *from department;
//更新(修改)所对应的列的值update 。。。。。。set 。。。。where。。。。
select * from department;
update department
set
vlocation = 'aisa'
where cdepartmentcode = '0013'; //不加条件，则一列所有的数据都被改掉了
select * from department;
//select * from department;
update department
set
vlocation = 'aisa',
vdepartmenthead = 'ads'
where cdepartmentcode = '0013';
select * from department;
//例子
Insert Into department(cdepertmentcode,vdepartmentname)values('0025','develepment');
update employee
set
cdepartmentcode = '0025'
where cdepartment = '0005';
//删除一行数据
delete student
where code = '001';
//合并语句
merge into teacher t
using student s
on (t.code = s.code)
when matched then
update set
t.name = s.name
when not matched then
insert (t.code,t.name)
values(s.code,s.name);
//主键约束
create table student
(
code char(4),
name varchar(10),
constraint sc_pk primary key(code) //主键创建再列上
)
//
insert into student values(null,'abc');//主键约束不能为空
insert into student values('0001','abc');
//主键创建再表上
create table student
(
code char(4) constraint sc_pk primary key,
name varchar(10),
)
//
create table student
(
code char(4) primary key,
name varchar2(10)
);
//查询已有主键
select * from user_constraints
//创建外键
create table teacher
(
code char(4),
name varchar2(10),
constraint teacher_code_pk primary key (code)
);
create table student
(
code char(4),
name varchar2(10),
teacher char(4),
constraint student_code_pk primary key (code),
constraint student_teacher_fk foreign key (teacher) references teacher(code)—创建外键
);
insert into student values('0002','dfc','0002');
insert into student values('0003','dfc','0002');
//创建唯一约束
create table student
(
code char(4),
name varchar2(10),
teacher char(4),
constraint student_code_uk unique (code)—创建唯一约束
);
//检查约束
create table student
(
code char(4),
name varchar2(10),
score number(4,1),
constraint score_ck check (score between 0 and 100) --创建检查约束
);
insert into student values('0001','aaa',89);
insert into student values('0001','aaa',101);--违反约束
//非空约束
create table student
(
code char(4),
name varchar2(10) not null –非空约束
);
////
insert into student values('0001',null)
//
create table student
(
code char(4),
name varchar2(10) not null,
constraints sn_uk unique(name)—非空及唯一约束
);
//
/*
create table teacher
(
code char(4),
name varchar2(10)
);
*/
// add 添加主键
alter table teacher
add constraint teacher_pk primary key (code);//添加主键
//// drop 删除主键
alter table teacher
drop constraint teacher_pk;//删除主键
// disable 禁用主键
cascade-- 强制性禁用
alter table student
disable constraint student_code_pk;--禁用主键，如果要禁用的主键还有其他类型的建在上面作用的话，主键不能禁用。如要禁用需要用强制性禁用，则应加cascade。
//alter table student
disable constraint student_code_pk cascade;-- 强制性禁用
// enable 恢复主键
alter table student
enable constraint student_code_pk;--恢复主键，这里必须把表里数据改写正确才可以恢复。
//使用事务
显示事务
commit;//提交语句
update employee
set
vfirstname = 'dsf'
where cemployeecode = '000004';
commit;//提交语句
select cemployeecode ,vfirstname from employee
where cemployeecode = '000004';
//隐式事务 正常提交事务的时候也会自动提交的
show autocommit 查看自动提交语句是否打开的
set autocommit on 打开自动提交语句。
set autocommit off 关闭自动提交语句。
//
//回复修改
Rollback 撤销上次的提交时候的位置
Savepoint 保存点
//例子
update employee
set
vfirstname = 'ccccc'
where cemployeecode = '000005';
savepoint step2; //设置保存点
update employee
set
vfirstname = 'ddddd'
where cemployeecode = '000004';
savepoint step3;
insert into employee(cemployeecode,vfirstname) values('000026','3310');
savepoint step4;
delete employee where cemployeecode = '000025';
rollback to step4; //撤销到step4这个点，以后的都被撤销掉了，前面的还有。
select cemployeecode,vfirstname from employee;
//上锁
用两个oracle的工具时，一个工具在用一个数据时，另一个工具就不能对这个数据进行修改，这个过程就时上锁。
//创建视图
// create view。。。as 创建视图
create view employee_view
as select vfirstname, vlastname, cphone from employee;
//查看是否以创建了
select * from user_views;
//查询表的数据
select * from employee_view;
//显示视图的结构
desc employee_view;
//修改创建的视图，实际上就是修改他的原地址的数据信息
update employee_view
set vlastname = '2222'
where vfirstname = 'ccccc';
select * from employee_view;
select * from employe;--查询原数据
//
create view emp_dept_view
as select vfirstname, vlastname,vdepartmentname
from employee natural join department;
select *from emp_dept_view;
// force 表示强制的创建 不管用到的基表是不是存在的，都创建视图。
create force view name_view
as select * from name;--不存在的基表
select * from user_views;
// or replace 表示没有视图就创建视图，如果有就修改现有的视图。
create or replace view name_view
as select * from employee;
select * from user_views;
// with check option;
create view ex_view
as select * from externalcandidate where ntestscore > 85
with check option;
//
update ex_view
set ntestscore = 60 --这里条件要在条件范围里才行
where ccandidatecode = '000010';
// drop view 删除视图
drop view ex_view;
// group by 用了这个不能修改视图数据
create or replace view employee_view
as select cstate,count(cstate)"count" from employee group by cstate;
update employee_view
set cstate ='abc'
where cstate = 'Georgia';
//
create or replace view employee_view
as select rownum"row",count(cstate)"count" from employee group by rownum;
//不能修改视图数据
create or replace view employee_view
as select vfirstname||vlastname"fl" from employee ;
//
insert into employee_view values('abcd');
//Top-n分析法
select rownum,vfirstname,vlastname
from(select vfirstname,vlastname from externalcandidate order by ntestscore desc)
where rownum <=3;--分数最高的3个人
//
//创建序列
create sequence emp_seq
increment by 1 -- 指定两个数之间的间隔，这里是1
start with 20 --指定序列初始值
maxvalue 30 --最大序列值
--minvalue --最小序列值
--cycle --表示循环
--cache 10 –预存在内存里的序列
//查询创建的序列
select * from user_sequences
//检查序列是否创建好
select sequence_name ,max_value, min_value,increment_by,last_number from user_sequences;
返回用户定义的序列列表和它的最大值，最小值，增量，和终值
//
select emp_seq.nextval from dual;--每生成下一个序列的数值
select emp_sep.currval from dual;--查看当前值的序列的数值
insert into employee (cemployeecode, vfirstname) values(emp_sep.nextval,'abc');
insert into employee (cemployeecode, vfirstname) values(to_char(emp_sep.nextval,'000 00'),'abc');
//修改最大值
alter sequence emp_sep
maxvalue 35
cycle
//不能修改初始值
alter sequence emp_sep
start with 10;
// 删除序列
drop sequence emp_sep;
//创建索引
create index emp_name_index
on employee (vfirstname);
//查询用户的索引
select * from user_ind_columns;
//创建同义词
create synonym ec
for externalcandidate;
//
select * from ec;
drop synonym ec；
//创建用户
create user student
identified by abc;
//设置连接数据库 权限
grant create session
to student;
//设置创建表 ， 创建视图，创建序列 权限
grant create table ,create view ,create sequence
to student;
//把查询teacher的权限给student
grant select on teacher to student;
//
insert into sys.teacher value('0001',sg)
//修改密码
alter user student identified by cde
//用户修改密码 ？？？？？？
//删除权限
drop user student
//撤销权限
revoke create table from student1;
//撤销权限 ？？？？？
revoke alter on teacher from student1;
//
grant alter on teacher to student with grant option;
//让student 拥有dba的角色（管理员）
grant dba to student
//PL/SQL
declare--声明部分（变量，常量）
begin --块（可执行部分）
exception(可选的)
end;
//显示输出内容
set serveroutput on
//用c_ 表示常量；用v_ 表示变量; %type 表示与前面一样的类型；
Constant 表示常量，它的值不能再改变了
declare
c_numOfEmployees constant number(4):=123;--只是一行的数据
v_name employee.vfirstname%type not null:='abc';
begin
dbms_output.put_line('abcdefg');--输出语句
end;
//
declare
c_numOfEmployees constant number(4):=123;
v_name employee.vfirstname%type not null:='abc';
begin
dbms_output.put_line(v_name);--可以是变量
end;
// select。。。into。。。where。。。表示查询数据
declare
v_firstname employee.vfirstname%type; --只是一行的数据
v_lastname employee.vlastname%type;
begin
select vfirstname,vlastname -- 这里的数据类型与下面是对应的
into v_firstname,v_lastname
from employee
where cemployeecode='000005';
dbms_output.put_line(v_firstname || v_lastname);
end;
//
declare
begin
insert into employee(cemployeecode,vfirstname)
values('000029','jack');
end;
//从一个表中查询出指定一行的数据，把它的结果插入到另一个表中
declare
v_vfirstname employee.vfirstname%type;
v_vlastname employee.vlastname%type;
v_phone employee.cphone%type;
v_birthdate employee.dbirthdate%type;
begin
select vfirstname,vlastname,cphone,dbirthdate
into v_vfirstname,v_vlastname,v_phone,v_birthdate
from externalcandidate
where vfirstname= 'Nancy'and vlastname = 'King';
insert into employee(cemployeecode,vfirstname,vlastname,cphone,dbirthdate)
values( '000032' , v_vfirstname,v_vlastname,v_phone,v_birthdate );
dbms_output.put_line('000035'||v_firstname||v_lastname||c_phone||v_birthdate);
end;
///查找是否插入了
select cemployeecode,vfirstname,vlastname,cphone,dbirthdate from employee;
//判断语句
declare
begin
if....then
..
elsif....then
....
else
....
end if;
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate
where ccandidatecode ='000002';
if v_score>90
then dbms_output.put_line('good');
elsif v_score>80
then dbms_output.put_line('average');
else dbms_output.put_line('bad');
end if;
end;
//循环语句
//循环语句1
declare
begin
loop --循环开始的地方
。。。
exit when
。。。
end loop; --循环结束的地方
end;
//例子
declare
v_score number(3):=0;
begin
loop --循环开始的地方
v_score:=v_score+1;
--exit when v_score>5;
dbms_output.put_line(v_score);
exit when v_score>5;--当条件不成立，退出循环。一定非要放在这里，可以放在输出前面。
end loop;--循环结束的地方
end;
//循环语句2
declare
begin
for i in 1..10
loop
。。。
end loop;
end;
//
declare
v_score number(3):=0;
begin
for i in 1..10 --输出从1到10
loop
dbms_output.put_line(i);
end loop;
end;
// //循环语句3 reverse
declare
v_score number(3):=0;
begin
for i in reverse -2..10 --倒着输出
loop
dbms_output.put_line(i);
end loop;
end;
//循环语句4
declare
...
begin
while 。。。 --当条件成立，继续循环，否则，跳出循环
loop
.。。。...
end loop;
end;
//
declare
v_score number(3):=0;
begin
while v_score < 5
loop
v_score:=v_score+1;
dbms_output.put_line(v_score);
end loop;
end;
//组合的用法
declare
v_score number(3):=0;
begin
while v_score < 5
loop
v_score:=v_score+1;
dbms_output.put_line(v_score);
exit when v_score =3; -- 循环到3就跳出循环
end loop;
end;
//1. cursor 声明游标
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is --声明游标和select关联起来
select vfirstname,vlastname -- 注这里没有into下面fetch时才用了
from employee;
begin
open result;
fetch result into v_firstname,v_lastname;--取出游标里的数据, 每调用一次，他就指到下一句语句，如果是单独一句语句，那就指的是当前。
dbms_output.put_line(v_firstname || v_lastname);
fetch result into v_firstname,v_lastname;
dbms_output.put_line(v_firstname || v_lastname);
close result;
end;
//2.
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop --循环取出数据，这里取到最后没有下一行时，就重复取最后一行到报错。
fetch result into v_firstname,v_lastname;
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
end;
//3.
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname
from employee;
begin
for row1 in result -- 取出一行里数据的每一条值
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname);
end loop;
end;
//4.
declare
v_score number(3):=0;
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
--cursor result is
--select vfirstname,vlastname from employee;
begin
--open result;
for row1 in (select vfirstname,vlastname from employee) --这里直接把查询语句代替了游标
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname);
end loop;
--close result;
end;
//5.
declare
begin
for row1 in (select vfirstname,vlastname,csex,ntestscore from externalcandidate where ntestscore >75)
loop
dbms_output.put_line(row1.vfirstname || row1.vlastname ||' ' || row1.ntestscore);
end loop;
end;
//
declare
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
dbms_output.put_line(result%rowcount);--取得当前行号
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
fetch result into v_firstname,v_lastname;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
fetch result into v_firstname,v_lastname;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop
fetch result into v_firstname,v_lastname;
exit when result%notfound;--%notfound表示是否还有值,如果没有数据就表示true
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
end;
//
declare
v_firstname employee.vfirstname%type;
v_lastname employee.vlastname%type;
cursor result is
select vfirstname,vlastname from employee;
begin
open result;
loop
fetch result into v_firstname,v_lastname;
exit when result%notfound;
dbms_output.put(result%rowcount);
dbms_output.put_line(v_firstname || v_lastname);
end loop;
close result;
if result%isopen then --表示游标有没有关掉
dbms_output.put_line('open');
else
dbms_output.put_line('close');
end if;
end;
//异常
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate
where ccandidatecode = '000103';
dbms_output.put_line(v_score);
exception --异常处理
when no_data_found then --no_data_found 不能找到
dbms_output.put_line('no data found');
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
select ntestscore
into v_score
from externalcandidate;
--where ccandidatecode = '000103';
dbms_output.put_line(v_score);
exception --异常处理
when no_data_found then --no_data_found 不能找到数据
dbms_output.put_line('no data found');
when too_many_rows then --no_many_rows 行太多
dbms_output.put_line('too_many_rows');
when others then -- --其他所有的都可以处理
dbms_output.put_line('sth is wrong');
end;
//
declare
v_score externalcandidate.ntestscore%type;
begin
insert into employee(vfirstname)
values('abc');
exception --异常处理
when others then
dbms_output.put_line('sth is wrong');
end;
//
declare
v_score externalcandidate.ntestscore%type;
null_in_pk exception;
pragma exception_init(null_in_pk,-01400);
begin
insert into employee(vfirstname)
values('abc');
exception --异常处理
when null_in_pk then
dbms_output.put_line('null can not be insert into pk');
end;
//创建一个存储过程
Create （or replace） procedure name2department
(
name in varchar2 -- in 表示输入参数
)
is --is下面相当于声明部分
v_code employee.cdepartmentcode%type;
v_department department.vdepartmentname%type;
begin
select cdepartmentcode --先通过名字查处部门的代号，
into v_code
from employee
where vfirstname = name;
select vdepartmentname --再通过部门的代号查处部门的名字
into v_department
from department
where cdepartmentcode = v_code;
dbms_output.put_line(v_department);
end;
//通过上面 查询部门的名字
exec name2department('Angela');
//
create (or replace) procedure name2department
(
name in varchar2,
depart out varchar2 --out 放入值
)
is
v_code employee.cdepartmentcode%type;
v_department department.vdepartmentname%type;
begin
select cdepartmentcode
into v_code
from employee
where vfirstname = name;
select vdepartmentname
into v_department
from department
where cdepartmentcode = v_code;
depart:=v_department;
end;
//接上面
create or replace procedure name2depar
(
name in varchar2
)
is
v_department department.vdepartmentname%type;
begin
name2department(name,v_department);
dbms_output.put_line(v_department);
end;
//？？？？？？？
exec name2depart('Angela');
//
触发器 需要在Oracle Enterprise Manager Console 中的方案(通过管理员登录)中的触发器。
// 触发器主体：
begin
dbms_output.put_line('abcsd');
end;
//
update employee
set vfirstname='sdsf'
where cemployeecode = '000030';
//触发器主体：
begin
dbms_output.put_line(:old.vfirstname);
end;