Oracle练习例子2.docx
- 文档编号:29614220
- 上传时间:2023-07-25
- 格式:DOCX
- 页数:26
- 大小:86.97KB
Oracle练习例子2.docx
《Oracle练习例子2.docx》由会员分享,可在线阅读,更多相关《Oracle练习例子2.docx(26页珍藏版)》请在冰豆网上搜索。
Oracle练习例子2
经过整理,本文练习分为下面六大块:
(大部分都有答案,笔者运可以实现,还待读者自行验证)
一.OraclePL/SQL语言基础.
二.用户管理及表空间
三.联合主键
四.函数
五.子查询
六.序列
一.OraclePL/SQL语言基础.
1.编程输出数列:
1,1,2,3,5,8,13,21,34...
并计算输出相邻两个数的商,要求最后一个数小于1000.
输出格式:
111
120.5
230.6...
.....
declare
aint:
=1;
bint:
=1;
varsumint:
=0;
dnumber(10,2);
begin
loop
varsum:
=a+b;
d:
=a/b;
dbms_output.put_line(a||b||d);
ifvarsum>=1000then
exit;
endif;
a:
=b;
b:
=varsum;
endloop;
end;
/
2.编写程序
将'WellcometoQingDao'转换成
'OADGNIQOTEMOCLLEW'.
declare
str1nvarchar2(50):
='WelcometoQingDao';
str11nvarchar2(50);
str2nvarchar2(50);
strlenint;
begin
strlen:
=length(str1);
str11:
=upper(str1);
str2:
=substr(str11,strlen,1);
loop
exitwhenstrlen=1;
strlen:
=strlen-1;
str2:
=str2||substr(str11,strlen,1);
endloop;
dbms_output.put_line(str2);
end;
/
或declare
var1varchar2(20):
='WelcometoQingDao';
var2varchar2(20);
var3varchar2(20):
='';
var4varchar2(20);
nuinteger;
numinteger;
begin
--upper()装换成大写
var2:
=upper(var1);
--length()长度
nu:
=length(var2);
dbms_output.put_line(var2);
dbms_output.put_line(nu);
num:
=nu;
whilenum>0
loop
var4:
=substr(var2,num,1);
var3:
=var3||var4;
num:
=num-1;
endloop;
dbms_output.put_line(var3);
end;
3.编写程序计算圆周率
中国古代数学家研究出了计算圆周率最简单的办法:
PI=4/1-4/3+4/5-4/7+4/9-4/11+4/13-4/15+4/17......
这个算式的结果会无限接近于圆周率的值,我国古代数学家祖冲之计算出
圆周率在3.1415926和3.1415927之间,请编程计算,要想得到这样的结果,他要经过多少次加减法运算?
18660304
计算Pi值,精确到0.0000001.
思路总结算式
加:
15913......2^n+1=1(n=0,1,2,3.......)
减:
3711.15.........4*2^n-1(n=0,1,2......)
declare
fenziconstantint:
=4;
fenmuint:
=1;
npiint:
=1;--项数
mypinumber(9,7):
=0.0;--pi
begin
mypi:
=(-1)**(npi+1)*fenzi/fenmu;
loop
--dbms_output.put_line(mypi);
exitwhenmypibetween3.1415926and3.1415927;
npi:
=npi+1;
fenmu:
=fenmu+2;
mypi:
=mypi+(-1)**(npi+1)*fenzi/fenmu;
endloop;
dbms_output.put_line(mypi);
end;
/
4.编写程序,计算根号3(3的开平方).
declare
lowfloat:
=0f;
highfloat:
=3f;
efloat:
=0.01f;
guessfloat;
e0float:
=2f;
begin
whileeo>e
loop
guess:
=(low+high)/2;
ifguess*guess>highthen
high:
=guess;
e0:
=guess*guess-high;
else
low:
=guess;
e0:
=high-guess*guess;
endif;
endloop;
dbms_output.put_line(guess);
end;
二用户管理及表空间
1.创建用户oa,登录密码hello
缺省使用表空间lyhuser,配额为10M:
--创建表空间
createtablespacelyhuser
datafile'e:
\oracle_lyh\lyhuser'size10M
/
--创建用户
createuseroaIdentifiedbyoa
defaulttablespacelyhuser
/
2.授于用户createsession,createtable系统权限。
尝试使用oa用户连结到数据库。
--授权(系统)
grantCreatesession,CreatetableTooa
/
--登录数据库
connoa/oa
/
--检查用户系统权限
select*
fromuser_sys_privs
orderbyprivilege
/
3.使用oa连结到数据库,并创建表格:
createtablet01(
idnumber(10),
nmvarchar2(12)
)
问题:
t01的数据存储在哪个表空间?
--将权限角色授给用户oa才能创建
grantresourceTooa
grantconnectTOoa
/
createtablet01(
idnumber(10),
nmvarchar(12)
)
/
存储在lyhuser表空间
4.收回权限createsession,及createtable权限。
--登录dba用户
connsysassysdba
revokecreatesession,createtablefromoa
/
撤销成功
5.创建角色r01,授予权限:
createsession,
createtable,
selectonsstore.employees
update(first_name,last_name)onstore.employees
--创建角色r01
createroler01
--创建成功
grantcreatesession,createtableTor01
/
grantselect,insertonoa.t01Tor01
/授权成功
grantselectonstore.employeesTor01
grantupdate(first_name,last_name)onstore.employeesTor01
/
6.用户oa授予角色resource
--授权
connsysassysdba/lyh
grantresourceTooa
/
7.以用户oa登录,执行以下操作:
查询store.employees中的数据;
更新store.employees中字段first_name及last_name的值
更新store.employees中字段salary的值。
--先用系统管理员身份对oa用户进行对象权限授权
connsysassysdba/lyh
grantselect,insert,updateonstore.productstooa
grantr01Tooa
/
--登录oa
--查询store.products中的数据
select*
fromstore.products
/
--first_name及last_name为列名
--要给用户oa授权角色r01,否则有错:
找不到列表或视图
updatestore.employees
setfirst_name='l',last_name='yh'
wheresalary>600000
/
观察是否操作成功及原因。
8.尝试由oa中收回对store.employees的更新权限。
应该如何做才能达到目标?
--
connoa/oa
revokeupdateonemployeesfronstore
9.测试系统权限的withadminoption的作用。
10.测试对象权限的withgrantoptioin的作用。
11.创建表空间tbs01,由两个文件(100M,30M)组成。
查询表空间的文件组成。
--
createtablespacetbs01
datafile'D:
\oracle\tbs011.mdb'size100M,'D:
\oracle\tbs012.mdb'size30M
12分配10M空间给用户oa,
将oa用户的缺省表空间改为tbs01;
13.将表空间tbs01下线
--
ALTERTABLESPACEtbs01OFFLINE
14.用户oa创建一个表格:
createtablet02(idnumber(10))
15.将表空间tbs01上线,重复14题。
--
ALTERTABLESPACEtbs01ONLINE;
16.检查用户oa的空间配额。
17.删除用户oa,依次使用以下两个语句:
dropuseroa;--观察操作结果。
dropuseroacascade;
18.删除表空间tbs01,并在其上创建表格,研究两个删除操作的异同。
droptablespacetbs01;
droptablespacetbs01includingcontents;
三.联合主键
1.创建球员表player,包含以下属性:
id标识10位整数主键
pcode编码12位字符
nm姓名12位字符
gender性别1位字符
--存放入表空间lyhuser
createtableplayer(
idintegerconstraintplayer_pkPrimarykey,
pcodevarchar2(15),
nmvarchar2(15),
genderchar
(1)
)
tablespacelyhuser
1_1获取此表列信息
-
1_2
selecttable_name,tablespace_name,temporary
fromuser_tables
wheretablespace_name='LYHUSER'
/
2.创建team表
id标识10位整数主键
nm球队名称16位字符不为空
city所属城市
--
createtableteam(
idintconstraintteam_pkprimarykey,
nmvarchar2(16)constraintteam_nnnotnull,
cityvarchar(20)
)
--查看表信息
--如果不加表空间则会存入默认的表system中
--查看表的表空间信息如下
selecttable_name,tablespace_name,temporary
fromuser_tables
wheretable_name='TEAM'
3.创建球队球员表teamplayer
要求一个球员只能属于一个球队
tid球队标识
pid球员标识
--
createtableplayerteam(
pidvarchar2(18)constraintplayerteam_pkprimarykey,
tidvarchar2(18)constraintplayerteam_nnnotnull
)
/tablespacelyhuser;
(自增题目)
4给player表增加列
nation国籍10位字符默认值为未知
4.函数
(1)单行函数
1concat(x,y)
2INITCAP
selectproduct_id,INITCAP(description)
fromproducts
whereproduct_id<4
/
3
4
selectname,instr(name,'e',1,2)
fromproducts
whereproduct_id=1
/
5
selectname,length(name)
fromproducts
/
6
7LPAD(x,width[,pad_string])
将字符串值,然后在左侧填充pad_string字符,以使总按右对齐排列字符宽度为width
selectrpad(name,20,'.'),lpad(price,8,'+')
fromproducts
whereproduct_id=4
/
8TRIM()
9REPLACE
例如,查找products表中name列,将‘Science’替换成'Physics'
SELECTname,REPLACE(name,'Science','Physics')asnewname
FROMproducts
WHEREproduct_id=1;
10SOUNDEX
SELECTlast_name
FROMcustomers
WHERESOUNDEX(last_name)=SOUNDEX('whyte');
11
SUBSTR(x,start[,length])
例如,从products表中提取name列从第2个字符开始,长度为7的字符串
SELECTname,SUBSTR(name,2,7)
FROMproducts
WHEREproduct_id<4;
(2)数字函数
1
2
3
三类型转换
To_char()
To_number()
Cast
?
?
?
?
?
?
?
?
?
?
?
?
?
习题
1.使用正则表达式函数输出字符4321helloworld为hello4321world.
2.使用正则表达式函数输出first_name中有aXXXXb形式的名子,
XXXX,为任意字符,ab也应包括在结果之中。
3.查询各部门的平均工资;
selectdepartment_id,avg(salary)
fromemployees
groupbydepartment_id
/
4.查询工资在本部门平均工资之上的员工。
selectoe.employee_id,oe.salary,oe.department_id
fromemployeesoe
whereoe.salary>(selectavg(ie.salary)fromemployeesiewhereoe.department_id=ie.department_idgroupbyie.department_id)
orderbyoe.department_id
5.查询各岗位工资的最高值、及最小值、平均值。
6.查询部门平均工资在整体平均工资水平之上的部门。
7.查询各部门的各岗位平均工资。
五.子查询更改表内容
1.创建表格emp,含字段:
ID标识,主键
FNAME名子
LNAME姓氏
SALARY薪水
--sys
createtableemp(
idintegerconstraintemp_pkPrimarykey,
fnamevarchar2(12),
lnamevarchar2(12),
salarynumber(10,2)
)
/
2.向表格emp中,插入以下数据:
101JAMESCHENNULL
102JANENULLNULL
103BILLNULL10000
---------------------
insertINTOemp(
id,fname,lname,salary)values(
101,'JAMES','CHEN',NULL
)
insertINTOemp(
id,fname,lname,salary)values(
102,'JAnE','NULL',NULL
)
insertINTOemp(
id,fname,lname,salary)values(
103,'BILL','NULL',10000
)
------------------------
3.对表格emp做如下更新:
将JAMES的薪水更新为12000,LNAME更新为WANG
将BILL的LNAME更新为LEE
将JANE的薪水更新为10000.
------------
updateemp
setsalary=12000,lname='WANG'
wherefname='JAMES'
/
-------------
updateemp
setlname='LEE'
wherefname='BILL'
/
----------
updateemp
setsalary=1000
wherefname='JAnE'
/
--------------------------------------
加薪:
将所有人的薪水更新为原薪水加1000。
--------------------------------------
updateemp
setsalary=salary+1000
/
//已更新三行
----------------------------
4.更新表格emp中薪水,数据来源为HR.employees表中的salary。
如果emp.ID与employees.employee_id相同,
则更新emp.salary值为employees.salary.
---------
给用户hr授予表权限
grantupdateonsys.emptohr;
grantselect,delete,insertonsys.emptohr;
----授权成功
-------
connhr/hr
updatesys.emp
setsys.emp.salary=(selectemployees.salaryfromemployees
wheresys.emp.id=employees.employee_id)
/-----------------------------
错误:
用hr用户的时候直接引用表不用加hr.
引用其它用户的表要加用户前缀
updatesys.emp
setsys.emp.salary=(selecthr.employees.salaryfromemployees
wheresys.emp.id=hr.employees.employee_id)
/
----------------------------------
5.更新表格emp中ID>101的行,只更新fname及lname。
更新数据来自employees,方法同4.
updatesys.emp
setsys.emp.fname=(selectemployees.frist_namefromemployees
wheresys.emp.id=employees.employee_id),
sys.emp.lname=(selectemployees.last_namefromemployees
wheresys.emp.id=employees.employee_id)
wheresys.emp.id>101
/
------------------------------
7.删除id>101的行,并检查删除的结果
--------------
deletefromsys.emp
whereid>101
-----------
select*fromsys.emp
8.删除表emp中全部数据。
----------
deletefromsys.emp
9.Createtable....select....语句,会由查询的结果中创建表,并将查询结果插入到表格中。
执行,createtablemyemp(emp_id,fname,salary)as
selectemployee_id,first_name,salary
fromemployees
whereemployee_id>180;
检查表格myemp的结构及数据。
如将查询条件更改为employee_id<0,会产生什么结果?
------------------
查到26人id181-206
employee_id<0出现错误无效字符
11.创建两个表格:
createtablee01(
idnumber(10),
fnamevarchar2(20),
lnamevarchar2(20)
);
createtablee02(
idnumber(10),
hiredatedate,
salarynumber(10)
);
将表格employees数据(employee_id,first_name,last_name,hiredate,salary),拆开存储到上面的两个表中。
------------------------
insertintoe01(id,fname,lname)
selectemployee_id,first_name,last_name
fromemployees
---------
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 练习 例子