Oracle学习笔记
一下是我学习Oracle时的基础练习，涵盖了编程中的重点知识！特别适合用来复习！
转载记得标注！！！
--查询scott用户的emp表
select * from scott.emp;
/**
--创建表空间
create tablespace 表空间名称
datafile '文件存储位置'
size xxM;
*/
create tablespace testorcl1
datafile 'D:\oracle_db\testorcl1.ora'
size 100M;
--创建用户
/**
语法
create user 用户名
identified by 密码
default tablespace 表空间名；
*/
create user testdb
identified by java
default tablespace testorcl1;
/**
给用户授权
语法
grant 角色 to 用户；
*/
--授予testdb数据库管理员权限
grant dba to testdb;
--授予testdb存储过程和函数的执行权限
grant resource to testdb;
--使用testdb身份登陆
--创建班级表
create table cloazz(
cid number(9) primary key,
cname varchar2(50) not null
);
--创建学生表,并建立与班级的主外键关联
--删除表
--drop table student;
create table student(
sid number(9) primary key,
sname varchar2(50) not null,
sex varchar2(4),
address varchar2(50),
birthday date,
classid number(9) not null references cloazz(cid)
);
--序列
/**
序列可以生产连续的整数，主要用于为表的主键值自增设置数据
--创建序列的语法
create sequences 序列名；
--创建班级表的序列
create sequence seqclazz;
--创建学生表的序列
create sequence seqstu;
--获取序列的下一个值
序列名.nextval
--获取序列的当前值
序列名.currval
*/
select * from cloazz;
select * from student;
--使用序列为班级表的逐渐字段设置属性
insert into cloazz values(seqclazz.nextval,'java'||seqclazz.currval||'班');
commit;--提交数据到数据库表
--给学生表插入数据
insert into student values(seqstu.nextval,'张飞','男','户县','09-2月-1992',1);
insert into student values(seqstu.nextval,'马超','男','西凉',sysdate,1);
commit;
--获取oe用户的订单表
select * from oe.orders order by order_id;
--获取oe用户订单表orders的前5条记录
select od.*,rownum r from oe.orders od
where rownum > 0 and rownum <= 5
order by order_id;
--分页的语法
/**
select * from
(select a.*,rownum r from 表1 a where rownum <=当前页数*每页记录数)
where r > (当前记录数 - 1)* 每页记录数；
*/
--备份表的数据
/**
create table 备份表
as
select * from 表名 [where 条件]
*/
--备份oe用户的orders表的数据,只进行数据备份，不备份约束
create table bakorders
as
select * from oe.orders;
select * from bakorders;
--只备份字段
create table bakorders1
as
select * from oe.orders where 1=2;
--获取客户的姓名,连接首姓名和尾姓名 || 代表连接
select cs.cust_first_name || '.' || cs.cust_last_name 姓名 from oe.customers cs;
--获取系统时间
select sysdate from dual;
--oracle函数
--添加月份
select add_months(sysdate,4) from dual;
--获取指定日期所在月份最后一天的日期
select last_day('12-2月-2017')from dual;
--计算两个日期之间相差的月份
select months_between(sysdate,'12-2月-2012') from dual;
--按照指定的日期格式进行四舍五入
select round(sysdate,'month') from dual;
select round(sysdate - 220,'year') from dual;
select round(sysdate,'day') from dual;
--获取星期数所在日期的下一个星期所在日期
select next_day(sysdate,'星期二') from dual;
--按照指定的日期格式截断当前指定日期
select trunc(sysdate, 'year') from dual;
select trunc(sysdate - 20, 'month') from dual;
select trunc(sysdate, 'day') from dual;
--字符函数
--截断当前字符
select substr('helloworld',0,5) from dual;
select substr('helloworld',5) from dual;
--获取指定字符在当前字符中的位置
select instr('hellworld','o') from dual;
--左右填充
select lpad('hello',10,'*') from dual;
select rpad('hello',10,'*') from dual;
--去空格或者替换当前字符串左右指定的字符
select trim(' hel lo ' ) from dual;
select trim('' from ' hello ') from dual;--无法显示
--数学函数
--四舍五入
select round(3.1415926, 3) from dual;
select round(3.1415926) from dual;
--转换函数
--1数字转换字符串
select trim(to_char(123.45,'$9999999999.99999')) from dual;
--2将日期转换为字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
--12小时制
select to_char(sysdate,'yyyy-MM-dd hh:MI:ss') from dual;
--24小时制
select to_char(sysdate,'yyyy-MM-dd hh24:MI:ss') from dual;
select '1' + '4' from dual;
--字符串转日期
select to_date('2014-12-21','yyyy-MM-dd') from dual;
--12小时制
select to_date('2014-12-21 1:03:11','yyyy-MM-dd hh:mi:ss') from dual;
--24小时制
select to_date('2014-12-21 21:03:11','yyyy-MM-dd hh24:mi:ss') from dual;
--获取用户id
select uid from dual;
--获取用户名称
select user from dual;
--其他函数
select nvl('test','hello') from dual;
select nvl('','hello') from dual;
--分组函数
/**
根据客户名称获取客户的订单数量
及其订单的总金额
*/
select * from oe.customers;
select * from oe.orders;
select
c.cust_first_name || '.' c.cust_last_name,
count(o.order_id) 订单数量,
sum(o.order_total) 订单总金额
from oe.orders o, oe.customers c
where o.customer_id=c.customer_id;
--testdb用户更新student表的第二条记录
select * from student;
update student s set s.sname='貂蝉' where s.sid=3;
commit;
--使用select获取行级锁
select * from student where sid=3 for update;
--给student表设置表级锁
lock table student in share mode;
--给student表设置排它锁
lock table student in exclusive mode;
update testdb.student s set s.sname='许褚' where s.sid=3;
select * from testdb.student;
select * from testdb.student where sid=3 for update wait 5;
commit;
lock table testdb.student in share mode;
lock table testdb.student in exclusive mode nowait;
--创建表空间
create tablespace test1
datafile 'D:\Oracle_sql\test1.ora'
--使用范围分区
create table testa1(
tid number(9) not null,
tname varchar2(50) not null
)partition by range(tid)
(
partition p1 values less than(1000) tablespace test1,
partition p2 values less than(2000) tablespace test2,
)
--按照分区表查询数据
select * from testa1 partition(p1);
select * from testa1 partition(p2);
--散列分区
create table testa2(
tid number(9) not null,
tname varchar2(50) not null
) partition by hash(tid)
(
partition ph1 tablespace test1,
partition ph2 tablespace tett2
)
--创建同义词
create synonym myorders for oe.orders;
--使用同义词访问替他用户的表
select * from myorders;
--删除同义词
drop synonym myorders;
--创建共有的同义词
create public synonym myorders for oe.orders;
--创建序列
create sequence seq_stu;
select * from student;
--给student表插入数据，主键值由序列提供
insert into student values(seq_stu.nextval,'aa'||seq_stu.currval,'男','山西',to_date('1994-8-28','yyyy-MM-dd'),1);
--创建视图
--replace 为修改视图的关键字
create or replace view vworders
as
select
od.*,c.cust_first_name||'.'||c.cust_last_name cname
from oe.orders od,oe.customers c
where od.customer_id=c.customer_id
--访问视图
select * from vworders;
--rowid
select o.*,rowid from oe.orders o;
--创建抽象类型
create or replace type myaddress as object(
addressid number(9),
city varchar2(50),
state varchar2(50),
street varchar2(50),
zip varchar2(50)
)
--创建表，指定字段的类型为自定义类型
create table student1(
stuid number(9) primary key,
sname varchar2(50),
address myaddress
)
--插入数据
insert into student1 values(
1,
'张飞',
myaddress(1001,'西安','陕西','科技四路','710060')
);
insert into student1 values(
2,
'关羽',
myaddress(1001,'太原','山西','科技四路','710060')
);
insert into student1 values(
3,
'赵云',
myaddress(1001,'安康','陕西','科技四路','710060')
);
insert into student1 values(
4,
'马超',
myaddress(1001,'西安','陕西','科技四路','710060')
);
--修改自定义类型字段的值
update student1 stu set stu.address.city='米脂',stu.sname='貂蝉'
where stuid=1;
commit;
--删除自定义类型
drop type 类型名称;
select * from student1;
--创建可变数组
/**
语法
create or replace type 类型名称 as array(长度) of 数组元素的类型
*/
--创建 商品名称数组类型
create or replace type items as array(5) of varchar2(50);
--创建购物车表
create table cart(
cid number(9) primary key,
product items
);
--插入数据
insert into cart values(
1,
items('面包','饼干','榨菜','锅巴','干果')
);
insert into cart values(
2,
items('苹果','饼干','梨子','锅巴','干果')
);
insert into cart values(
3,
items('栗子','饼干','香蕉','锅巴','方便面')
);
insert into cart values(
4,
items('枣子','饼干','火腿肠','锅巴','萝卜干')
);
commit;
select * from cart;
--查看可变数组的数据
select * from the(select product from cart c where c.cid=1);
select c.cid, p.* from cart c,table(select product from cart c where c.cid=1)p
where c.cid=1
;
--修改可变数组的数据
update cart c
set c.product=items('面包','饼干','榨菜','锅巴','芝麻酱')
where c.cid=1;
commit;
--嵌套表
--1.创建抽象数据类型
create or replace type emp_ty as object(
eid number(9),
ename varchar2(50),
sex varchar2(4),
address varchar2(50)
);
--2.创建表类型
create or replace type emp_table_type as table of emp_ty;
--3.基于表类型创建表
create table dep(
depid number(9) primary key,
depname varchar2(50),
emp emp_table_type
)nested table emp store as emp_table;
--向嵌套表中插入数据
insert into dep values(
1,
'技术部',
emp_table_type(emp_ty(1,'张飞','男','山西'),
emp_ty(2,'马超','男','山西'),
emp_ty(3,'赵云','男','山西'),
emp_ty(4,'黄忠','男','弧线')
)
);
insert into dep values(
2,
'财务部',
emp_table_type(emp_ty(1,'高峰','男','山西'),
emp_ty(2,'曹操','男','山西'),
emp_ty(3,'刘备','男','山西'),
emp_ty(4,'许褚','男','弧线')
)
);
insert into dep values(
3,
'项目部',
emp_table_type(emp_ty(1,'貂蝉','女','山西'),
emp_ty(2,'小巧','女','米脂'),
emp_ty(3,'小乔','女','锦州'),
emp_ty(4,'孙尚香','女','弧线')
)
);
commit;
select * from dep;
select * from the(select emp from dep where depid=1);
--向嵌套表中插入数据
insert into the(select emp from dep where depid=1)
values(5,'威严','女','上海');
commit;
--删除嵌套表的数据
delete from table(select emp from dep where depid=1)where eid=2;
/**
语法
declare
变量 类型[(长度)][:=值];
......
begin
sql语句块;
.......
[
exception
when
异常对象(变量) then 异常处理语句;
.......
]
end;
*/
--实例
declare--声明变量
a number(9);
b number(9);
begin
a:=10;
b:=20;
dbms_output.put_line('a='||a);
dbms_output.put_line('b='||b);
dbms_output.put_line('a+b='||(a+b));--注意（a+b）要括号
end;
--使用into关键字获取表的字段值为变量赋值
/**
编写sqlpl/sql语句
给定指定的订单编号，获取订单的日期，金额和客户的名称
*/
/****************************************/
declare
odate date;
money number;
cname varchar2(50);
cid number;
begin
--获取指定编号的订单信息
select od.order_date,order_total,customer_id
into ---注意
odate,money,cid
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=cid;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||money);
end;
/****************************************/
--使用表的列类型作为变量的数据类型
/**
表名.列名%type表示引用表的列类型
表名%rowtype--表示应用表的行类型
*/
--实例
/****************************************/
declare
odate oe.orders.order_date%type;
money oe.orders.order_total%type;
cname varchar2(50);
cid oe.customers.customer_id%type;
begin
--获取指定编号的订单信息
select od.order_date,order_total,customer_id
into ---注意
odate,money,cid
from oe.orders od where od.order_id=2459;--有异常
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=cid;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||money);
--ocrale异常
exception
when no_data_found then dbms_output.put_line('没有数据！');
end;
/****************************************/
----------------------------------------------
--行类型的使用
/**
获取指定编号的订单的所有数据和客户名称
*/
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
begin
select
od.*
into
odrows
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
end;
----------------------------------------------
--if语句
/**
语法
if 条件表达式 then
sql语句
else
处理语句
......
end if;
*/
/**
编写pl/sql
根据指定的订单编号获取信息，当订单编号不存在时，提示查无数据
否则显示信息
*/
----------------------------------------------
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
num number;
begin
--获取指定编号的订单的数量
select
count(od.order_id) into num
from oe.orders od where od.order_id=2459;
if num > 0 then
select
od.*
into
odrows
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
else
dbms_output.put_line('订单信息不存在');
end if;
end;
----------------------------------------------
--循环语句
/**
1---loop循环语法
loop
语句块
....
exit when 退出条件;
......
end loop;
*/
--使用loop循环完成1-100累加
declare
i number:=0;
j number:=0;
begin
loop
i:=i + 1;
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
exit when i >= 100;
end loop;
end;
--2--while循环
/**
语法
while 条件 loop
循环语句
.....
end loop;
*/
declare
i number:=0;
j number:=0;
begin
while i <100 loop
i:=i + 1;
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
end loop;
end;
---------------------------------------
--for循环
/**
语法
for 变量 in 范围 loop
循环语句;
......
end loop;
*/
declare
j number:=0;
begin
for i in 1.. 100 loop
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
end loop;
end;
---------------------------------------
--游标
/**
1.隐式游标，变量名称为sql，由系统确定
游标属性
%notfound --true/false 没有数据被找到
%found --true/false 有数据返回真
%rowCount --true/false 返回记录数
%isopen --true/false 游标是否打开，隐式一直是false
*/
----------------------------------------------
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
num number;
begin
--获取指定编号的订单的数量
select
count(od.order_id) into num
from oe.orders od where od.order_id=2459;
dbms_output.put_line('返回的记录数:'||sql%rowCount);
if num > 0 then
select
od.*
into
odrows
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
else
dbms_output.put_line('订单信息不存在');
end if;
end;
----------------------------------------------
--显示游标
/**
语法
declare
cursor 游标名称 is select 语句; --声名游标
......
begin
open 游标名; -- 打开游标
....
fetch 游标 into ....--操作游标
....
close 游标; -- 关闭游标
end;
*/
/**
编写pl/sql语句
使用游标完成
给定客户编号，获取客户的订单信息
*/
-----------------------------------
declare
cursor csod is select * from oe.orders
where oe.orders.customer_id=101;
odrows oe.orders%rowtype;--行变量
begin
open csod; --打开游标
loop
fetch csod into odrows;
exit when csod%notfound;
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
dbms_output.put_line('--------');
end loop;
close csod;
end;
-----------------------------------
--使用for循环操作游标
declare
cursor csod is select * from oe.orders
where oe.orders.customer_id=101;
begin
for odrows in csod loop
--输出获取信息
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
dbms_output.put_line('--------');
end loop;
end;
--创建存储过程
/**
语法
create or replace procedure 过程名(参数 [in],参数 out 类型...)
is
局部变量 类型;
.....
begin
sql语句块;
.....
end;
*/
--调用存储过程
/**
declare
...
begin
过程名(参数...);
end;
*/
---------------------------------
--创建存储过程
/**
建立存储过程
传递订单编号，返回订单的日期，金额和客户名称
*/
------------------------------------------------
create or replace procedure proc_orders(oid in number,
odate out date,
money out number,
cname out varchar2
)
is
cid number;
begin
--获取指定编号的订单信息
select
od.order_date,order_total,customer_id
into--使用into将表的字段值传递给指定变量
odate,money,cid
from oe.orders od where od.order_id=oid;
--获取客户名称
select
cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=cid;
exception
when no_data_found then dbms_output.put_line('未找到数据!');
end;
----------------------------------
create or replace procedure pro_orders(oid in number,odate out date, money out number,cname varchar2)
is
cid number;
begin
select oe.orders.order_date,oe.orders.order_total,oe.orders.customer_id
into
odate,
money,
cid
from oe.orders where oe.orders.order_id=oid;
--获取客户名称
select oe.customers.cust_first_name||'.'||oe.customers.cust_last_name
into
cname
from oe.customers where oe.customers.customer_id=cid;
exceptions
when no_data_found then dbms_output.put_line('无数据！');
end;
--select * from oe.orders where oe.orders.order_id=2458;
--执行过程
declare
adate date;
amoney number;
aname varchar2(50);
begin
--执行过程
proc_orders(2458,adate,amoney,aname);
dbms_output.put_line('姓名:'||aname);
dbms_output.put_line('金额:'||amoney);
dbms_output.put_line('日期：'||adate);
end;
--函数
/**
语法
create or replace function 函数名(参数,类型.....) return 类型
is
变量 类型;
begin
sql语句快;
.....
return 返回值;
end;
--执行函数
select 函数(参数) from dual;
--使用pl/sql执行
declare
变量 类型;
begin
变量:=函数(参数....);
.....
end;
*/
---编写函数
create or replace function addtest(a number, b number) return number
is
c number;
begin
c:=a+b;
return c;
end;
--执行函数
select addtest(12,33) from dual;
--根据客户的编号返回客户的名称
create or replace function addtest1(cid number) return varchar2
is
cname varchar2(50);
begin
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs
where cs.customer_id=cid;
return cname;
end;
--执行函数
select addtest1(193) from dual;
--根据客户的编号返回客户的名称
create or replace function getCname(cid number) return varchar2
is
cname varchar2(50);
begin
select
cs.cust_first_name||'.'||cs.cust_last_name into cname
from oe.customers cs where cs.customer_id=cid;
return cname;
end;
--执行
select getCname(101) from dual;
