Oracle练习例子(2)

经过整理，本文练习分为下面六大块：

（大部分都有答案，笔者运可以实现，还待读者自行验证） 一．Oracle PL／SQL语言基础. 二．用户管理及表空间 三．联合主键 四．函数 五．子查询 六．序列

一．Oracle PL／SQL语言基础.

1.编程输出数列：1,1,2,3,5,8,13,21,34...

并计算输出相邻两个数的商，要求最后一个数小于1000. 输出格式： 1 1 1 1 2 0.5 2 3 0.6... ..... declare a int := 1; b int := 1; varsum int:=0; d number(10,2); begin loop

varsum:=a+b; d:=a/b;

dbms_output.put_line(a||b||d); if varsum >= 1000 then exit; end if; a:=b;

b:=varsum; end loop; end; /

2.编写程序

将 'Wellcome to QingDao' 转换成 'OADGNIQ OT EMOCLLEW'.

declare

str1 nvarchar2(50):='Welcome to QingDao'; str11 nvarchar2(50); str2 nvarchar2(50); strlen int; begin

strlen:=length(str1); str11:=upper(str1);

str2:=substr(str11,strlen,1); loop

exit when strlen=1; strlen:=strlen-1;

str2:=str2||substr(str11,strlen,1); end loop;

dbms_output.put_line(str2); end; /

或declare

var1 varchar2(20) :='Welcome to QingDao'; var2 varchar2(20); var3 varchar2(20):=''; var4 varchar2(20); nu integer; num integer; begin

--upper()装换成大写 var2:=upper(var1); --length()长度 nu:=length(var2);

dbms_output.put_line(var2); dbms_output.put_line(nu); num:=nu; while num>0 loop

var4:=substr(var2,num,1); var3:=var3||var4; num:=num-1; end loop;

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. 思路 总结算式

加：1 5 9 13 ...... 2^n+1=1(n=0,1,2,3.......) 减：3 7 11. 15.........4*2^n-1(n=0,1,2......) declare

fenzi constant int := 4; fenmu int := 1; npi int :=1;--项数

mypi number(9,7):=0.0;--pi begin

mypi:=(-1)**(npi+1)*fenzi/fenmu; loop

--dbms_output.put_line(mypi);

exit when mypi between 3.1415926 and 3.1415927 ; npi:=npi+1;

fenmu:=fenmu+2;

mypi:=mypi+(-1)**(npi+1)*fenzi/fenmu; end loop;

dbms_output.put_line(mypi); end; /

4.编写程序，计算根号3（3的开平方）.

declare

low float :=0f; high float :=3f; e float:=0.01f; guess float; e0 float:=2f; begin

while eo>e loop

guess:=(low+high)/2; if guess*guess>high then high :=guess;

e0:=guess*guess-high;

else

low:=guess;

e0:=high-guess*guess; end if; end loop;

dbms_output.put_line(guess); end;

二用户管理及表空间

1.创建用户oa，登录密码hello

缺省使用表空间lyhuser , 配额为10M： --创建表空间

create tablespace lyhuser

datafile 'e:\\oracle_lyh\\lyhuser' size 10M /

--创建用户

create user oa Identified by oa default tablespace lyhuser /

2.授于用户create session, create table 系统权限。 尝试使用oa用户连结到数据库。 --授权（系统）

grant Create session,Create table To oa /

--登录数据库 conn oa/oa /

--检查用户系统权限 select *

from user_sys_privs order by privilege /

3.使用oa连结到数据库，并创建表格： create table t01( id number(10), nm varchar2(12) )

问题：t01的数据存储在哪个表空间?

--将权限角色授给用户oa 才能创建 grant resource To oa

grant connect TO oa /

create table t01( id number(10), nm varchar(12) ) /

存储在 lyhuser表空间

4.收回权限create session,及create table权限。 --登录dba用户 conn sys as sysdba

revoke create session,create table from oa /

撤销成功

5.创建角色r01, 授予权限： create session, create table,

select on sstore.employees

update(first_name,last_name) on store.employees

--创建角色 r01 create role r01 --创建成功

grant create session, create table To r01 /

grant select,insert on oa.t01 To r01 /授权成功

grant select on store.employees To r01

grant update(first_name,last_name) on store.employees To r01 /

6.用户oa授予角色resource --授权

conn sys as sysdba/lyh grant resource To oa /

7.以用户oa登录，执行以下操作： 查询store.employees中的数据；

更新store.employees中字段first_name及last_name的值 更新store.employees中字段salary的值。

--先用系统管理员身份对oa用户进行对象权限授权 conn sys as sysdba/lyh

grant select,insert,update on store.products to oa grant r01 To oa /

--登录oa

--查询store.products中的数据 select *

from store.products /

--first_name及last_name为列名

--要给用户oa 授权角色r01，否则有错：找不到列表或视图 update store.employees

set first_name='l',last_name='yh' where salary>600000 /

观察是否操作成功及原因。

8.尝试由oa中收回对store.employees的更新权限。 应该如何做才能达到目标？ --

conn oa/oa

revoke update on employees fron store

9.测试系统权限的with admin option的作用。

10.测试对象权限的with grant optioin的作用。

11.创建表空间tbs01,由两个文件(100M,30M)组成。 查询表空间的文件组成。 --

create tablespace tbs01

datafile 'D:\\oracle\\tbs011.mdb' size 100M ,'D:\\oracle\\tbs012.mdb' size 30M

12 分配10M空间给用户oa,

将oa用户的缺省表空间改为tbs01;

13.将表空间tbs01下线 --

ALTER TABLESPACE tbs01 OFFLINE

14.用户oa创建一个表格：

create table t02( id number(10))

15.将表空间tbs01上线，重复14题。 --

ALTER TABLESPACE tbs01 ONLINE;

16.检查用户oa的空间配额。

17.删除用户oa，依次使用以下两个语句： drop user oa; --观察操作结果。 drop user oa cascade;

18.删除表空间tbs01,并在其上创建表格, 研究两个删除操作的异同。 drop tablespace tbs01;

drop tablespace tbs01 including contents;

三．联合主键

1.创建球员表player，包含以下属性：

id 标识 10位整数 主键 pcode 编码 12位字符 nm 姓名 12位字符 gender 性别 1位字符

--存放入表空间lyhuser create table player(

id integer constraint player_pk Primary key, pcode varchar2(15), nm varchar2(15), gender char(1) )

tablespace lyhuser

1_1 获取此表 列信息 -

1_2

select table_name, tablespace_name,temporary from user_tables

where tablespace_name='LYHUSER'

/

2. 创建team表

id 标识 10位整数 主键

nm 球队名称 16 位字符 不为空 city 所属城市 --

create table team(

id int constraint team_pk primary key,

nm varchar2(16) constraint team_nn not null, city varchar(20) )

--查看表信息

--如果不加表空间 则会存入默认的表 system中 -- 查看表的表空间信息如下

select table_name, tablespace_name,temporary from user_tables

where table_name='TEAM'

3.创建球队球员表teamplayer

要求一个球员只能属于一个球队 tid 球队标识 pid 球员标识 --

create table playerteam(

pid varchar2(18) constraint playerteam_pk primary key, tid varchar2(18) constraint playerteam_nn not null )

/tablespace lyhuser；

（自增题目）

4 给player 表增加列

nation 国籍 10位字符 默认值为未知

四．函数

（1） 单行函数 1 concat(x,y)

2 INITCAP

select product_id,INITCAP(description) from products

where product_id<4 /

3

4

select name,instr(name,'e',1,2) from products

where product_id=1 /

5

select name,length(name) from products /

6

7LPAD(x, width [, pad_string])

将字符串值，然后在左侧填充pad_string字符，以使总按右对齐排列字符宽度为width select rpad(name,20,'.'),lpad(price,8,'+') from products

where product_id=4

/ 8 TRIM()

9 REPLACE

例如，查找products表中name列，将‘Science’替换成'Physics' SELECT name,REPLACE(name, 'Science', 'Physics') as newname FROM products

WHERE product_id = 1;

10 SOUNDEX

SELECT last_name FROM customers

WHERE SOUNDEX(last_name) = SOUNDEX('whyte'); 11

SUBSTR(x, start [, length])

例如，从products表中提取name列从第2个字符开始，长度为7的字符串 SELECT name,SUBSTR(name, 2, 7) FROM products

WHERE product_id < 4;

（2）数字函数 1

2

3

三 类型转换 To_char（） To_number（） Cast

？？？？？？？？？？？？？

习题

1.使用正则表达式函数输出字符4321helloworld为hello4321world.

2.使用正则表达式函数输出first_name中有aXXXXb形式的名子， XXXX，为任意字符，ab也应包括在结果之中。 3.查询各部门的平均工资；

select department_id ,avg(salary)

from employees

group by department_id /

4.查询工资在本部门平均工资之上的员工。

select oe.employee_id,oe.salary,oe.department_id from employees oe

where oe.salary>( select avg(ie.salary) from employees oe.department_id=ie.department_id group by ie.department_id) order by oe.department_id

5.查询各岗位工资的最高值、及最小值、平均值。 6.查询部门平均工资在整体平均工资水平之上的部门。 7.查询各部门的各岗位平均工资。

ie where

五．子查询更改表内容

1.创建表格emp，含字段： ID 标识，主键 FNAME 名子 LNAME 姓氏 SALARY 薪水 --sys

create table emp(

id integer constraint emp_pk Primary key, fname varchar2(12) , lname varchar2(12), salary number(10,2) ) /

2. 向表格emp中，插入以下数据： 101 JAMES CHEN NULL 102 JANE NULL NULL 103 BILL NULL 10000 --------------------- insert INTO emp(

id,fname,lname,salary)values( 101,'JAMES','CHEN',NULL )

insert INTO emp(

id,fname,lname,salary)values( 102,'JAnE','NULL',NULL )

insert INTO emp(

id,fname,lname,salary)values(

103,'BILL','NULL',10000 )

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

3. 对表格emp做如下更新：

将JAMES的薪水更新为12000, LNAME更新为WANG 将BILL的LNAME更新为LEE 将JANE的薪水更新为10000. ------------ update emp

set salary=12000,lname='WANG' where fname='JAMES' /

------------- update emp set lname='LEE' where fname='BILL' /

---------- update emp set salary=1000

where fname='JAnE' /

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

加薪：将所有人的薪水更新为原薪水加1000。 -------------------------------------- update emp

set salary=salary+1000 /

//已更新三行

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

4.更新表格emp中薪水，数据来源为HR.employees表中的salary。 如果emp.ID与employees.employee_id相同， 则更新emp.salary值为employees.salary. ---------

给用户hr授予表权限

grant update on sys.emp to hr;

grant select,delete,insert on sys.emp to hr； ----授权成功 ------- conn hr/hr

update sys.emp

set sys.emp.salary= (select employees.salary from employees where sys.emp.id=employees.employee_id)

/-----------------------------

错误： 用hr用户的时候 直接引用表 不用加hr. 引用其它用户的表 要加用户前缀 update sys.emp

set sys.emp.salary= (select hr.employees.salary from employees where sys.emp.id=hr.employees.employee_id) /

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

5.更新表格emp中ID>101的行，只更新fname及lname。 更新数据来自employees，方法同4. update sys.emp

set sys.emp.fname= (select employees.frist_name from employees where sys.emp.id=employees.employee_id),

sys.emp.lname= (select employees.last_name from employees where sys.emp.id=employees.employee_id) where sys.emp.id>101 /

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

7.删除id>101的行，并检查删除的结果 --------------

delete from sys.emp where id>101 -----------

select* from sys.emp

8.删除表emp中全部数据。 ----------

delete from sys.emp

9.Create table .... select ....语句，会由查询的结果中创建表，并将查询结果插入到表格中。 执行，create table myemp(emp_id,fname,salary) as select employee_id,first_name,salary from employees

where employee_id > 180; 检查表格 myemp的结构及数据。

如将查询条件更改为employee_id < 0，会产生什么结果？ ------------------

查到26人 id 181-206

employee_id < 0 出现错误 无效字符

11. 创建两个表格： create table e01( id number(10),

fname varchar2(20),

lname varchar2(20) );

create table e02( id number(10), hiredate date, salary number(10) );

将表格employees数据（employee_id,first_name,last_name,hiredate,salary），拆开存储到上面的两个表中。

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

insert into e01(id,fname,lname)

select employee_id,first_name,last_name from employees ---------

insert into e02(id,hiredate,salary) select employee_id,hire_date,salary from employees /

12.insert first与insert all异同是什么？

六．序列

1.创建序列：

序列seq_even的步长为2，最小值为1。 序列seq_odd的步长为2，最小值为2。 2.创建表格并使用序列。 表格 students(

id number(10), nm varchar2(12) );

向表格插入全班同学的数据，

每行数据的ID使用序列seq_even产生的值。

3.以HR用户建立会话，使用seq_odd.nextval产生序列。 打开新的窗口，以HR用户建立会话， 再次使用seq_odd.nexval产生新的序列。

分别在两个会话中交替使用seq_odd.nextval产生序列整数。

观察产生的序列数，会得出什么结论？

在两个会话中分别使用seq_odd.currval，观察获得的结果。

4.将seq_odd更改为最小值为1000会有什么结果？

5.更改seq_even的步长更改为1，并测试。

6.删除序列seq_odd及seq_even.

7.使用create table myemp as select * from employees; 创建一个新的表格myemp，数据来自employees 8.如果应用程序中经常有这样的查询: select

first_name,last_name,salary from myemp

where employee_id = xxxx;

应当创建什么样的索引，以提高查询速度？写出创建索引的语句。

9.如果应用程序中经常有如下的查询：

select first_name,last_name,department_id,salary from employees

where salary > xxxxx and department_id = XXX ;

应创建什么样的索引，写出创建索个的语句。

10.查询表格employees上定义的索引。

11.创建视图vshipping,视图的基表为myemp , 包含以下字段： employee_id, first_name,last_name,salary

只包含部门shipping(department_id=50)的职员。 允许更新，但不允许更改后的数据不在视图中。

12.将视图vshipping中的salary集休加薪1000元。 观察myemp中的数据变化。

13. 删除视图vshipping中的数据，观察myemp中数据的变化。

14.在myemp表格基础上，创建视图vdept,

该视图为各部门高于本部门平均薪水的人员信息，含字段: first_name(重命名为fname) last_name(重命名为lname) salary

department_id(重命名为dept_id)

测试该视图是否有更新能力，为什么？

本文来源：https://www.bwwdw.com/article/5w65.html