一下是我學習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;
