--创建表空间
create tablespace mytablespace1
datafile 'd:\orc1.ora'
size 100M;
create tablespace mytablespace2
datafile 'd:\orc2.ora'
size 100M;
--删除表空间
--drop tablespace mytablespace1;
--创建用户
/**
创建用户
create user 用户名
identified by 密码
[default tablespace 表空间名];
*/
create user myadmin
identified by java
default tablespace mytablespace1;
--授权
/**
grant 权限 to 用户名;
权限:dba,resouces(存储过程、视图、触发器),select|insert|update|delete
*/
grant dba to myadmin;
--回收权限
--revoke dba from myadmin;
--访问myadmin用户的表
select * from myadmin.clazz;
--oracle的数据备份
/**
只备份数据，不会备份约束
*/
create table neworders
as
select * from oe.orders;
/**
只备份表的结构，不会备份约束
*/
create table neworders1
as
select * from oe.orders where 1=2;
/*********************************************/
select * from neworders;
--oracle使用||连接字符串
/*********************************************/
select c.cust_first_name||'.'||c.cust_last_name 姓名 from oe.customers c;
/*********************************************/
--创建表
create table clazz(
cid number(9) primary key,
cname varchar2(50) not null
);
create table student(
sid number(9) primary key,
sname varchar2(50) not null,
sex varchar2(4) default '男',
address varchar2(100),
birthday date,
classid number(9) not null references clazz(cid)
);
--插入
insert into clazz values(1,'java1班');
insert into clazz values(2,'java2班');
insert into clazz values(3,'java3班');
insert into clazz values(4,'java4班');
insert into clazz values(5,'java5班');
--提交数据事务
commit;
select * from clazz;
--插入学生数据(注意日期类型的字段)
insert into student values(1,'张飞','男','北京','11-12月-1998',1);
commit;
select * from student;
/***********************************************/
select * from oe.orders order by order_id;
--获取订单表的订单金额最高的前5条记录
/**
使用rownum进行取值
*/
select a.*,rownum from
(select od.*,rownum from oe.orders od order by od.order_total desc) a
where rownum>0 and rownum<=5;
/************************************************/
--按照每页5条记录获取订单表的数据
/**
分页语法
select a.* from (select b.*,rownum r from 表 b
where rownum<=(当前页数*每页记录数)) a
where r>(当前页-1)*每页记录数
*/
/**************第1页*********************/
select a.* from (select b.*,rownum r from oe.orders b
where rownum<=5 order by b.order_id) a
where r>0;
/**************第2页*********************/
select a.* from (select b.*,rownum r from oe.orders b
where rownum<=10 order by b.order_id) a
where r>5;
/**************第3页*********************/
select a.* from (select b.*,rownum r from oe.orders b
where rownum<=15 order by b.order_id) a
where r>10;
/********************************************************/
--同义词,相当于表的别名
create public synonym myorders for oe.orders;
--访问同义词
select * from myorders;
--删除同义词
drop public synonym myorders;
--序列
/**
用于生成连续的唯一的整数
主要为表的主键字段提供键值
*/
create sequence seq_sid
increment by 5
start with 1000;
delete from myadmin.student;
commit;
select * from myadmin.student;
--使用序列的值为student表的主键赋值
/**
seq_sid.nextval:用于获取序列的下一个值
*/
insert into myadmin.student
values(seq_sid.nextval,'曹操'||seq_sid.currval,'男','西安','8-8月-1998',1);
commit;
--获取序列的下一个值
select seq_sid.nextval from dual;
--获取序列的当前值
select seq_sid.currval from dual;
--删除序列
drop sequence seq_sid;
--创建和更新视图的语法
/**
create or replace view 视图名
as
select语句;
*/
--oracle的函数库
--字符函数
/****************************************************/
--字符的截取
/*select substr('helloworld',开始位置,截取的长度) from dual;*/
select substr('helloworld',6,5) from dual;
/****************************************************/
/****************************************************/
--字符的去除字符串左右空格
/*select substr('helloworld',开始位置,截取的长度) from dual;*/
select trim(' hello world ') from dual;
/****************************************************/
/****************************************************/
--字符的左右填充
/*select substr('helloworld',开始位置,截取的长度) from dual;*/
select lpad('hello',10,'*') from dual;
select rpad('hello',10,'*') from dual;
select rpad(substr('13909876543',1,7),11,'*') from dual;
/****************************************************/
/****************************************************/
--日期函数
--获取当前日期
select sysdate from dual;
--给当前日期增加3个月
select add_months(sysdate,3) from dual;
--获取指定月份最后一天的日期
select last_day('12-2月-2020') from dual
--获取两个月份之间的差值
--当前日期与2018年的6月25日相差多少月=12.3820463709677
select months_between(sysdate,'25-6月-2018') from dual;
--对日期四舍五入
select round(sysdate,'year') from dual;
select round(sysdate,'month') from dual;
select round(sysdate,'day') from dual;
--根据星期的数值，获取所在的日期
select next_day(sysdate,7) from dual;
--截断日期
select trunc(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'day') from dual;
/****************************************************/
/****************************************************/
--转换函数
--1将数字转为字符串
select '1'||'2' from dual;
--将日期转字符串
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 to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'month') from dual;
select to_char(sysdate,'dd') from dual;--一个月的第n天
select to_char(sysdate,'day') from dual;--一个星期的星期数
/****************************************************/
/****************************************************/
--字符串转日期
select to_date('2019-12-22','yyyy-MM-dd') from dual;
--12小时制
select to_date('2019-12-22 12:21:22','yyyy-MM-dd hh:mi:ss') from dual;
--24小时制
select to_date('2019-12-22 18:21:22','yyyy-MM-dd hh24:mi:ss') from dual;
/****************************************************/
/****************************************************/
--其它函数
--获取当前登录的用户id
select uid from dual;
select user from dual;
select nvl('111','test') from dual;--相当于a==null?a:b;
/****************************************************/
--oracle面向对象
/****************************************************/
--抽象数据类型(实体类,c/c++的结构体)
/**
create or replace type address_type as object(
字段 类型,
....
);
*/
create or replace type address_type1 as object(
sheng varchar2(50),
shi varchar2(50),
jiedao varchar2(150),
zip varchar2(50)
);
--类型的使用,字段的类型使用
create table authors(
aid number(9) primary key,
aname varchar2(50) not null,
address address_type1
);
--给表插入数据
insert into authors
values(1,'武松',address_type1('山东省','淄博市','景阳冈','890009'))
commit;
--更新自定数据的值,使用自定义类型的字段时，使用表的别名访问
update authors a set
a.address.sheng='辽宁省',
a.address.shi='铁岭市',
a.address.jiedao='本山街'
where a.aid=1;
--或者
update authors a set
a.address=address_type1('辽宁省','铁岭市','本山街','880099')
where a.aid=1;
commit;
--删除自定义类型的数据(被表使用的数据类型不能删除)
drop type address_type1;
--注意：删除类型前，先删除使用了类型的表
select * from authors;
/****************************************************/
/****************************************************/
--oracle中的数组类型--收集器
/**
create or replace type 数组名称 as varray(数组长度) of 数组元素类型(长度);
*/
create or replace type namearray_type as varray(5) of varchar2(50);
--创建表，使用数组类型作为字段类型,一个客户对多个产品名称
create table myorders(
mid number(9) primary key,
cname varchar2(50) not null,
pnames namearray_type
);
--给数组插入数据
insert into myorders values(
1,'武松',
namearray_type('香皂','洗衣粉','牙刷','牙膏','洗衣液')
);
insert into myorders values(
2,'鲁智深',
namearray_type('西瓜','卤肉','炸鸡腿','圣代','冰激凌')
);
insert into myorders values(
3,'林冲',
namearray_type('苹果','葡萄','桃子','栗子','歪果仁')
);
commit;
--修改可变数组的值
update myorders m set m.pnames= namearray_type('鸡爪子','猪头肉','炸鸡腿','烤肉串','冰啤酒')
where m.mid=2;
commit;
select * from myorders;
--查看可变数组的数据
/****************************************************/
select a.mid,a.cname,b.* from myorders a,the(select mo.pnames from myorders mo where mo.mid=1) b
where a.mid=1;
--
select a.mid,a.cname,b.* from myorders a,the(select mo.pnames from myorders mo where mo.mid=2) b
where a.mid=2;
--
select a.mid,a.cname,b.* from myorders a,the(select mo.pnames from myorders mo where mo.mid=3) b
where a.mid=3;
select a.* from
the(select mo.pnames from myorders mo where mo.mid=1) a
/****************************************************/
--嵌套表
/****************************************************/
--1建立抽象类型
create or replace type stu_type as object(
sid number(9),
sname varchar2(50),
sex varchar2(4)
);
--2基于学生类型建立表类型
create or replace type stu_table_type as table of stu_type;
--创建表使用表类型
create table clazz(
cid number(9) primary key,
cname varchar2(50) not null,
stu stu_table_type
)nested table stu store as student_type_table;
--插入数据
insert into clazz
values(
1,'java1班',
stu_table_type(
stu_type(1,'张飞','男'),
stu_type(2,'马超','男'),
stu_type(3,'关羽','男'),
stu_type(4,'赵云','男'),
stu_type(5,'黄忠','男')
)
);
commit;
select * from clazz;
--查看表类型的数据
select a.* from table(select c.stu from clazz c where c.cid=1) a
/**************************************************************************************/
--pl/sql
/**********************************************/
declare--声明区
a number(9):=5;
b number(9):=10;
c number(9);
begin--代码区
c:=a+b;
dbms_output.put_line('a+b='||c);--输出
end;
/**********************************************/
--使用into关键字将表的字段值传递给变量
/**********************************************/
--编写pl/sql将订单表的订单编号、日期、客户名称和订单总金额进行输出显示
declare
oid number(9);
odate date;
cname varchar2(50);
ototal number;
cid number(9);
begin
--获取订单编号为2458的订单数据
select
od.order_id,od.order_date,od.customer_id,od.order_total
into
oid,odate,cid,ototal
from oe.orders od where od.order_id=2459;
--根据客户编号，获取客户姓名
select
cs.cust_first_name||'.'||cs.cust_last_name
into cname
from oe.customers cs where cs.customer_id=cid;
--输出显示
dbms_output.put_line('订单编号:'||oid);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单金额:'||ototal);
end;
/**********************************************/
--引用类型
/***
1、表名.列名%type：引用表的列类型做为变量的数据类型使用
2、表名%rowtype:引用表的行记录为变量的数据类型,又称为行类型
*/
/**********************************************/
--编写pl/sql将订单表的订单编号、日期、客户名称和订单总金额进行输出显示
declare
oid oe.orders.order_id%type;
odate oe.orders.order_date%type;
cname varchar2(50);
ototal oe.orders.order_total%type;
cid oe.customers.customer_id%type;
begin
--获取订单编号为2458的订单数据
select
od.order_id,od.order_date,od.customer_id,od.order_total
into
oid,odate,cid,ototal
from oe.orders od where od.order_id=2458;
--根据客户编号，获取客户姓名
select
cs.cust_first_name||'.'||cs.cust_last_name
into cname
from oe.customers cs where cs.customer_id=cid;
--输出显示
dbms_output.put_line('订单编号:'||oid);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单金额:'||ototal);
end;
/**********************************************/
--行类型的使用
select * from oe.product_information;
/**
获取商品编号为1755的商品信息
*/
/**********************************************/
declare
prows oe.product_information%rowtype;--声明行变量prows
begin
select
p.* into prows --将整行记录传递给变量prows
from oe.product_information p where p.product_id=1755;
dbms_output.put_line('产品名称:'||prows.product_name);
dbms_output.put_line('产品种类:'||prows.category_id);
dbms_output.put_line('产品价格:'||prows.list_price);
dbms_output.put_line('产品网址:'||prows.catalog_url);
end;
/**********************************************/
--条件控制if
/**
语法
if 条件 then
sql语句块
else
sql语句块
end if;
*/
/**********************************************/
--根据产品编号查询产品信息，如果存在则显示信息，否则显示查无此记录
declare
prows oe.product_information%rowtype;--声明行变量prows
num number;
begin
--获取指定编号的记录数量
select count(p.product_id) into num from oe.product_information p
where p.product_id=1756;
if num>0 then
select
p.* into prows --将整行记录传递给变量prows
from oe.product_information p where p.product_id=1756;
dbms_output.put_line('产品名称:'||prows.product_name);
dbms_output.put_line('产品种类:'||prows.category_id);
dbms_output.put_line('产品价格:'||prows.list_price);
dbms_output.put_line('产品网址:'||prows.catalog_url);
else
dbms_output.put_line('查无记录存在！');
end if;
end;
/**********************************************/
--oracle的循环语句
--loop循环
/**
语法:
loop
循环语句
exit when 退出循环的条件;
....
end loop;
*/
/**************************************************/
--使用loop循环实现1-100的累加
declare
i number:=0;
j number:=0;
begin
loop
i:=i+1;
j:=j+i;
exit when i>100;
dbms_output.put_line('i='||i);
dbms_output.put_line('j='||j);
dbms_output.put_line('----------------');
end loop;
end;
/**************************************************/
--while循环
/**
语法
while 条件表达式 loop
......
end loop;
*/
/**************************************************/
/**
使用while循环完成1-100累加
*/
declare
i number:=0;
j number:=0;
begin
while i<100 loop
i:=i+1;
dbms_output.put_line('i='||i);
j:=j+i;
dbms_output.put_line('j='||j);
dbms_output.put_line('----------------');
end loop;
end;
/**************************************************/
/**
使用for
语法:
for 变量 in 范围 loop
...
end loop;
*/
/**************************************************/
--使用for循环实现1-100累加
declare
j number:=0;
begin
for i in 1..100 loop
dbms_output.put_line('i='||i);
j:=j+i;
dbms_output.put_line('j='||j);
dbms_output.put_line('----------------');
end loop;
end;
/**************************************************/
--异常的使用
/**************************************************/
--根据产品编号查询产品信息，如果存在则显示信息，否则显示查无此记录
declare
prows oe.product_information%rowtype;--声明行变量prows
begin
select
p.* into prows --将整行记录传递给变量prows
from oe.product_information p;--where p.product_id=1756;
dbms_output.put_line('产品名称:'||prows.product_name);
dbms_output.put_line('产品种类:'||prows.category_id);
dbms_output.put_line('产品价格:'||prows.list_price);
dbms_output.put_line('产品网址:'||prows.catalog_url);
exception
when no_data_found then dbms_output.put_line('无此记录存在！');
when too_many_rows then dbms_output.put_line('查找的记录太多，无法赋值！');
end;
/**********************************************/
--游标的使用
/**************************************************/
/**
语法
declare
cursor 游标名 is select语句;
begin
open 游标名;--打开游标
....
fetch 游标 into 变量;--操作游标
...
close 游标名;
end;
*/
/*******************************************************/
--使用游标获取客户编号为101的所有订单信息
select * from oe.orders od where od.customer_id=101;
---
declare
cursor myorders_cursor is select * from oe.orders od where od.customer_id=101;
orows oe.orders%rowtype;
begin
open myorders_cursor;--打开游标
loop
fetch myorders_cursor into orows;--操作游标
exit when myorders_cursor%notfound;
dbms_output.put_line('订单编号:'||orows.order_id);
dbms_output.put_line('订单日期:'||orows.order_date);
dbms_output.put_line('订单模式:'||orows.order_mode);
dbms_output.put_line('订单金额:'||orows.order_total);
dbms_output.put_line('--------------------------');
end loop;
close myorders_cursor;--关闭游标
end;
/*******************************************************/
--使用for循环游标
declare
cursor myorders_cursor is select * from oe.orders od where od.customer_id=101;
--orows oe.orders%rowtype;
begin
for orows in myorders_cursor loop --for循环游标
dbms_output.put_line('订单编号:'||orows.order_id);
dbms_output.put_line('订单日期:'||orows.order_date);
dbms_output.put_line('订单模式:'||orows.order_mode);
dbms_output.put_line('订单金额:'||orows.order_total);
dbms_output.put_line('--------------------------');
end loop;
end;
/*******************************************************/
--oracle的存储过程和函数
--存储过程
/****
语法:
create or replace procedure 过程名(参数1 类型,参数2 out 类型,.....)
is
--局部变量的声明
变量 类型
begin
....
end;
--过程的执行
declare
变量 类型;
...
begin
过程名(传入的参数值,变量,....);
.....
end;
*/
/***************************************************************/
--编写存储过程，传递订单编号，获取订单的日期、金额和客户的名称
create or replace procedure proc_orders(oid number,odate out date,
ototal out number,cname out varchar2)
is
cid number;
begin
--获取订单信息
select
od.order_date,od.customer_id,od.order_total
into
odate,cid,ototal
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;
end;
/*************执行存储过程**************/
declare
odate date;
ototal number;
cname varchar2(50);
begin
proc_orders(2458,odate,ototal,cname);
dbms_output.put_line('订单日期:'||odate);
dbms_output.put_line('订单金额:'||ototal);
dbms_output.put_line('订单客户:'||cname);
end;
/***************************************************************/
/****使用存储过程调用游标*******************/
create or replace procedure proc_order_cursor(cid number)
is
cursor myorders_cursor is select * from oe.orders od where od.customer_id=cid;
begin
for orows in myorders_cursor loop --for循环游标
dbms_output.put_line('订单编号:'||orows.order_id);
dbms_output.put_line('订单日期:'||orows.order_date);
dbms_output.put_line('订单模式:'||orows.order_mode);
dbms_output.put_line('订单金额:'||orows.order_total);
dbms_output.put_line('--------------------------');
end loop;
end;
/*******************************************/
--执行过程
begin
proc_order_cursor(103);
end;
--函数
/****
函数语法
create or replace function 函数名(参数1 类型,....) return 返回值类型
is
局部变量 类型;
.....
begin
....
return 返回值;
end;
函数的调用
select 函数名(参数...) from dual;
或者
declare
变量 类型;
begin
变量:=函数(参数值...);
end;
*/
/********************************************/
create or replace function addx(a number,b number) return number
is
c number;
begin
c:=a+b;
return c;
end;
/********************************************/
select addx(12,2) from dual;--调用函数
/********************************************/
/***************************************************/
--编写函数，传递客户编号，获取客户的姓名
create or replace function getCname(cid number) return varchar2
is
cname varchar2(50);
begin
select
c.cust_first_name||'.'||c.cust_last_name
into
cname
from oe.customers c where c.customer_id=cid;
return cname;
end;
--调用
select getCname(101) from dual;
/***************************************************/
