oracle根本语句

oracle基本语句

区别是和sqlserver比较

--示例一：Create?Table?命令，区别较小
Create?Table?vendor_master
(?
?vencode?varchar2(5),
?venname?varchar2(20),
?venadd1?varchar2(20),
?venadd2?varchar2(20),
?venadd3?varchar2(20)
?)
?
--示例二：Alter?Table?Modify?命令，区别较大
Alter?Table?vendor_master?Modify?(venname?varchar2(25))

--示例三：Alter?Table?Add?命令，区别较小，主要是数据类型
Alter?Table?vendor_master
??add?(tel_no?number(12),
???????tngst_no?number(12))
???????
--示例四：Drop?Column?命令：完全一样
Alter?Table?vendor_master?Drop?Column?tngst_no

--示例五：Oracle?独有
alter?Table?vendor_master?set?unused(tel_no)

--示例六：Truncate?Table命令?：完全一样
truncate?table?vendor_master

--示例八：Desc命令：完全不一样
Desc?vendor_master

--示例九：Drop?Table?命令：完全一样
drop?table?vendor_master



--示例10：Insert命令
Insert?into?vendor_master?values?('v001','John?smith','11?E?main?st','West?Avenue','alabama',1234567)

--以下这种方法只在Orace中有效，l但不推荐使用此方法
Insert?into?vendor_master?values?('&vencode','&venname','&venadd1','&venadd2','&venadd3',&telno)


--示例15：Select命令
select?*?from?vendor_master


--示例20：Update命令?：注意大小写
update?vendor_master?set?tel_no?=?987654?where?vencode='V001'??--?'v001'


--示例24：Grant?和?Revoke命令
grant?all?on?vendor_master?to?sys
revoke?all?on?vendor_master?from?sys

**********************************************************************************************
//用户


connect?system/manager@ydgl;

--删除已有的用户和表空间

--drop?tablespace?freemandatabase;
--drop?tablespace?tempfreemandatabase;

--创建表空间
create?tablespace?FreeManDataBase
datafile?'c:\FreeManDataBase.ora'
size?25M;

?

??? --查看当前用户的缺省表空间
　select username,default_tablespace from user_users;

--创建临时表空间
create?temporary?tablespace?tempFreeManDataBase
tempfile?'c:\tempFreeManDataBase.ora'
size?25M;

--创建用户
create?user?zong?identified?by?"123456"
default?tablespace?FreeManDataBase
temporary?tablespace?tempFreeManDataBase;

--赋权限
grant?connect?to?zong;
grant?resource?to?zong;
grant?dba?to?aaa;

--登录
connect?zong/123456@ydgl;

create?table?zong.ccc(bh?varchar2(10),?xm?varchar2(10),?age?number,?salary?number,?birthday?date)?
?--事务处理?
???insert?into?zong.ccc(bh,xm,age,salary,birthday)?values?('000','aaa',56,12345,to_date('1978-1-1','yyyy-mm-dd'));
???insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('004','peng',24,3456.3,to_date('1976-1-1','yyyy-mm-dd'));
???savepoint?ppp;
???insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('005','cao',21,345.3,to_date('1996-1-1','yyyy-mm-dd'));
???rollback?to?ppp;
???insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('006','li',28,31456.3,to_date('1966-1-1','yyyy-mm-dd'));
???update?zong.ccc?set?bh='008'?,salary=5000,age=33?where?bh='004';
????commit?;
--?清除数据
???truncate?table?zong.cc
???delete?from?zong.ccc?where?bh='006'


??create?table?zong.firsttable?(xm?varchar2(10),age?number(4),salary?number(7,2))
??--添加列
??alter?table?zong.firsttable?add?(kk?number(10),?birthday?date)
??--更新列类型
??alter?table?zong.firsttable?modify?(xm?number(2),?birthday?varchar2(10))
??
??--收回权限
???revoke?dba?from?zong
??--授予管理员角色
???grant?dba?to?zong?
???
???--授予对象权限
???grant?select?,update?on?firsttable?to?system
???
???--删除表
???drop?table?zong.ccc
???
???


--集合操作
Create?Table?zong.YYY(xm?Varchar2(10),age?Number(8));
Insert?Into?zong.yyy?Values('aaa',10)
Insert?Into?zong.yyy?Values('bbb',20)

Create?Table?zong.xxx(xm?Varchar2(10),age?Number(8));
Insert?Into?zong.xxx?Values('aaa',10)
Insert?Into?zong.xxx?Values('ccc',30)

Select?*?From?zong.yyy?Union?Select?*?From?fei.xxx

Select?*?From?zong.yyy?Union?All?Select?*?From?fei.xxx

Select?*?From?zong.yyy?Intersect?Select?*?From?fei.xxx

Select?*?From?zong.yyy?Minus?Select?*?From?fei.xxx
?
?*********************************************
**********************************************
--字符串函数
??select?ascii('A')?A,ascii('a')?a,ascii('0')?zero,ascii('?')?space?from?dual
??
??select?chr(54740)?zhao,chr(65)?chr65?from?dual
??
??select?concat('010-','88888888')||'连接'?实例?from?dual
??
??select?initcap('smith')?upp?from?dual;
??
??select?instr('oracle?traning','ra',1,2)?instring?from?dual
??
??select?lpad(rpad('gao',10,'*'),17,'*')from?dual;
??
??select?ltrim(rtrim('gao?qian?jing????','?'),'?')?from?dual;
??
??select?substr('13088888888',3,8)?from?dual;
??select?replace('he?love?you','he','i')?from?dual;
??
??
?--数学函数
??select?floor(2345.67)?from?dual;
??select?mod(10,3),mod(3,3),mod(2,3)?from?dual;
??select?round(55.5),round(-55.4),trunc(55.5),trunc(-55.5)?from?dual;
??select?sign(123),sign(-100),sign(0)?from?dual;
??
??
??--日期函数
???select?to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm')?from?dual;
???select?to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')?from?dual;
???select?last_day(sysdate)?from?dual;
??
???select?months_between('19-12月-1999','19-3月-1999')?mon_between?from?dual;
???select?months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))?mon_betw?from?dual;
???
???select?to_char(sysdate,'yyyy.mm.dd?hh24:mi:ss')?北京时间,to_char(new_time
??(sysdate,'PDT','GMT'),'yyyy.mm.dd?hh24:mi:ss')?埃及时间?from?dual;
??
??select?next_day('18-5月-2001','星期五')?next_day?from?dual;
??//
??select?round(sysdate,'year')?from?ccc;
??
??select?to_char(sysdate,'dd-mm-yyyy?day')?from?dual;
??select?*?from?ccc?where?birthday-to_date('1977-11-11','yyyy-mm-dd')>120;
??select?*?from?ccc?where?birthday-to_date('1977-11-11','yyyy-mm-dd')>30;
???select?*?from?ccc?where?birthday>to_date('1977-11-11','yyyy-mm-dd');
???
???
??select?to_char(t.d,'YY-MM-DD')?from?(?
select?trunc(sysdate,?'MM')+rownum-1?as?d?
from?dba_objects?
where?rownum?<?32)?t?
where?to_char(t.d,?'MM')?=?to_char(sysdate,?'MM')?--找出当前月份的周五的日期
and?trim(to_char(t.d,?'Day'))?=?'星期五'?

--类型转换函数
select?to_char(sysdate,'yyyy/mm/dd?hh24:mi:ss')?from?dual;

select?to_number('1999')?year?from?dual;

--系统函数
select?username,user_id?from?dba_users?where?user_id=uid;
select?user?from?dual;


--集合函数
?create?table?table3(xm?varchar(8),sal?number(7,2));
?insert?into?table3?values('gao',1111.11);
?insert?into?table3?values('gao',1111.11);
?insert?into?table3?values('zhu',5555.55);
?
?--select?avg(distinct?sal)?from?gao.table3;
?--select?max(distinct?sal)?from?scott.emp;


--分组函数和统计函数?
?select?deptno,count(*),sum(sal)?from?scott.emp?group?by?deptno;
?select?deptno,count(*),sum(sal)?from?scott.emp?group?by?deptno?having?count(*)>=5;
?select?deptno,count(*),sum(sal)?from?scott.emp?having?count(*)>=5?group?by?deptno?;
?select?deptno,ename,sal?from?scott.emp?order?by?deptno,sal?desc;

**********************************************************************************************
????CREATE?TABLE?"ZONG"."CCC"("BH"?VARCHAR2(10),?"XM"?VARCHAR2(10),?"AGE"?NUMBER,?"SALARY"?NUMBER,?"BIRTHDAY"?DATE)?

????insert?into?zong.ccc(bh,xm,age,salary,birthday)?values?('000','aaa',56,12345,to_date('1978-1-1','yyyy-mm-dd'));
?
????insert?into?zong.ccc(bh,xm,age,salary,birthday)?values?('001',null,33,4444,to_date('1979-1-1','yyyy-mm-dd'));
???
????insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('004','peng',24,3456.3,to_date('1976-1-1','yyyy-mm-dd'));
???
????insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('005','cao',21,345.3,to_date('1996-1-1','yyyy-mm-dd'));
??
????insert?into?zong.ccc(bh,xm,age,salary,birthday)?values('006','li',28,31456.3,to_date('1966-1-1','yyyy-mm-dd'));
?
????select?bh?编号,?nvl(xm,'ggg')?as?姓名?from?ccc?where?bh='001'
?
????select?bh?编号,??nvl2(xm,'yes','no')?as?姓名??from?ccc
?
???--select?bh?编号,NULLIF('bbb','aaa')?from?ccc
???
???--is?null?的用法
???
???select?*?from?ccc??where?xm?is?null
???select?*?from?ccc??where?xm??is?NOT?null
???
???--not?in的用法
????
????select?*?from?ccc?where??birthday?between??to_date('1978-1-1','yyyy-mm-dd')?and??to_date('1996-1-1','yyyy-mm-dd')
????select?*?from?ccc?where??birthday?not??between??to_date('1978-1-1','yyyy-mm-dd')?and??to_date('1996-1-1','yyyy-mm-dd')
???
??--?in的用法
?????select?*?from?ccc?where??xm?in('aaa','peng','cao')
?????select?*?from?ccc?where??xm??not?in('aaa','peng','cao')
?????
??--like的用法和=、!=、<、>、<=、>=的用法
????
????select?*?from?ccc?where?age>24?and?age?<=56?and?xm?like?'%a%'



???
???
?????--
????create??table??sales?(xm?varchar2(10),?dTime?date,?count?number,?totalmoney?number,city?varchar2(10))
????
????insert?into?sales?values('张三',to_date('2003-01-01','yyyy-mm-dd'),1200,30000,'南昌');
????insert?into?sales?values('张三',to_date('2004-01-01','yyyy-mm-dd'),1200,30000,'南昌');
????insert?into?sales?values('张三',to_date('2003-01-01','yyyy-mm-dd'),1000,30000,'北京');
????insert?into?sales?values('张三',to_date('2004-01-01','yyyy-mm-dd'),2333,40000,'北京');
?????
????insert?into?sales?values('李四',to_date('2003-01-01','yyyy-mm-dd'),800,24567,'南昌');
????insert?into?sales?values('李四',to_date('2004-01-01','yyyy-mm-dd'),600,15000,'南昌');
????insert?into?sales?values('李四',to_date('2003-01-01','yyyy-mm-dd'),400,20000,'北京');
????insert?into?sales?values('李四',to_date('2004-01-01','yyyy-mm-dd'),1000,18000,'北京');
????
????select?xm,sum(count)?数量,sum(totalmoney)?金额?,city?from?sales?group?by?xm?,count?,totalmoney,rollup(city)?order?by?xm?,count?,totalmoney,city
????
????--group分组语句
???select?xm,sum(count)?数量,sum(totalmoney)?金额?,city?from?sales?group?by?xm?,count?,totalmoney,rollup(city)??having?count>2000?order?by?xm?,count?,totalmoney,city
????
????--rollup函数
????select?xm,?sum(count)?数量,city?from?sales?group?by?xm?,count?,rollup(city)?order?by?xm?,count?,city
????
????
????
??--事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit?or?rollback），Oracle自动清除临时表中数据
????CREATE?GLOBAL?TEMPORARY?TABLE?admin_work_area
????????(startdate?DATE,
?????????enddate?DATE,
?????????class?CHAR(20))
??????ON?COMMIT?DELETE?ROWS;
???create?table?permernate(?a?number);
???insert?into?admin_work_area?values(sysdate,sysdate,'temperary?table');
???
???insert?into?permernate?values(1);
???commit;
???select?*?from?admin_work_area;
???select??*?from?permernate;


--会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据

?????drop?table?admin_work_area;
?????CREATE?GLOBAL?TEMPORARY?TABLE?admin_work_area
????????(startdate?DATE,
?????????enddate?DATE,
?????????class?CHAR(20))
??????ON?COMMIT?PRESERVE??ROWS;
???create?table?permernate(?a?number);
???insert?into?admin_work_area?values(sysdate,sysdate,'temperary?table');
???
???insert?into?permernate?values(2);
???commit;
???select?*?from?admin_work_area;
???select??*?from?permernate;

**********************************************************************************************
//锁

?create??table??sales?(xm?varchar2(10),?dTime?date,?count?number,?totalmoney?number,city?varchar2(10))
????
????insert?into?sales?values('张三',to_date('2003-01-01','yyyy-mm-dd'),1200,30000,'南昌');
????insert?into?sales?values('张三',to_date('2004-01-01','yyyy-mm-dd'),1200,30000,'南昌');
????insert?into?sales?values('张三',to_date('2003-01-01','yyyy-mm-dd'),1000,30000,'北京');
????insert?into?sales?values('张三',to_date('2004-01-01','yyyy-mm-dd'),2333,40000,'北京');
?????
????insert?into?sales?values('李四',to_date('2003-01-01','yyyy-mm-dd'),800,24567,'南昌');
?????insert?into?sales?values('李四',to_date('2004-01-01','yyyy-mm-dd'),600,15000,'南昌');
????insert?into?sales?values('李四',to_date('2003-01-01','yyyy-mm-dd'),400,20000,'北京');
????insert?into?sales?values('李四',to_date('2004-01-01','yyyy-mm-dd'),1000,18000,'北京');
????
????
????
????
????select?*?from?sales?where?xm='张三'?for?update?of?count
????
????update?sales?set?count=30000?where?xm='张三'
????
?????
????--另一用户登录
????update?ydgl.sales?set?count=30000?where?xm='张三'
????
????
????--在多个用户在同一张表中放置锁时,其他用户等待上一用户的时间.
????select?*?from?sales?where?xm='张三'?for?update?of?count??wait?10?
????
????
???
????
????--只作查询
????lock?table?sales?in?share??mode
????
????
????--能删除,更新,插入除锁定外的其他行
????
???lock?table?sales?in?share?update?mode
???
???
???--排他锁?,在同一时间点上,只有一个用户在表中放置排他锁.
????lock?table?sales?in?exclusive??mode
????
????--避免延迟时间,锁在用户之间的传递时间,不等待,立即提示错误信息
??????lock?table?sales?in?exclusive??mode?nowait
???**********************************************************************************************
----创建临时表??
/**//*create?temporary?tablespace?mydb
tempfile?'f:\mydb.ora'
size?10m;*/

----创建抽象数据类型
create?or?replace?type?address_ty?as?object
(street_no?number(3),
street_name?varchar2(20),
city?varchar2(20),
state?varchar2(20));

----查看抽象数据类型实际值
select?attr_name,length,attr_type_name
from?user_type_attrs
where?type_name='ADDRESS_TY';

----创建应用了抽象数据类型的表
create?table?vend_mast
(vencode?varchar2(5),
venname?varchar2(15),
venadd?address_ty,
tel_no?number(10));

----查看表结构
desc?vend_mast;

select?column_name,data_type?from?user_tab_columns?where
???????table_name='VEND_MAST';
???????
----插入记录
insert?into?vend_mast?values
('v100','john',address_ty(110,'Clinton?Rd',
'Rosewood','Columbia',234465987);

----查看记录
select?a.venadd.city?from?vend?mast?a;

---修改记录,一定要用别名
update?vend_mast?a
???????set?a.venadd.street_no=10
???????where?venname='john';
???????
----删除记录???????
delete?from?vend_mast?a?
where?a.venadd.city='Rosewood';

----强行删除抽象数据类型
drop?type?address_ty?force;

----创建应用了抽象数据类型的表的索引
create?index?streetnum?on?vend_mast(venadd.street_no);

----查看索引
select?owner,index_name,index_type,table_owner,table_name,table_type
from?all_indexes
where?owner='SCOTT';

----创建不能继承的对象
create?or?replace?type?Student_typ?as?object
(Ssn?number,
Name?varchar2(30),
Address?varchar2(100))not?final;

----修改是否能继承
alter?type?Student_typ?not?final;

create?type?t?as?object
(x?number,)
not?instantiable?member?function?func1?return?number)
not?instantiable?not?final;

----创建可变数组
create?type?itemcode?as?varray(5)?of?varchar2(5);

create?type?qty_ord?as?varray(5)?of?number(5);

create?type?qty_deld?as?varray(5)?of?number(5);

----基于可变数组创建表
create?table?order_detail(
??????orderno?varchar2(5),
??????item_va?itemcode,
??????qty_va?qty_ord,
??????qtyd_va?qty_deld);

----插入记录
insert?into?order_detail?
???????values('o100',itemcode('i100','i101','i102','i103','i104'),
??????????????qty_ord(100,98,47,29,20),
??????????????qty_deld(100,900,800,700,600));

----查看整体
select?*?from?order_detail
----单个
select?item_va?from?order_detail
----查看可变数组内容
select?*?from?table(
----select?*?from?order_detail?a?where?a.orderno='o100')
select?a.item_va?from?order_detail?a?where?a.orderno='o100')

----嵌套表
----创建抽象数据类型即对象
create?or?replace?type?ord_ty?as?object?(
???????itemcode?varchar2(5),
???????qty_ord?number(5),
???????qty_deld?number(5));

----表中包含嵌套表一定要基于一个对象创建一个新的对象作为嵌套表
create?or?replace?type?ord_nt?as?table?of?ord_ty;

----创建包含嵌套表的表
create?table?order_master(
????????orderno?varchar2(5),
????????odate?date,
????????vencode?varchar2(5),
????????dets?ord_nt)
????????nested?table?dets?store?as?ord_nt_tab;
--嵌套表放入某个任意任名的存储空间,嵌套表的存储空间与普通表不同,分别存储在不同的空间

insert?into?order_master?values(
'o100',to_date('18-07-99','DD-MM-YY'),'v001',
ord_nt(ord_ty('i100',10,5),
ord_ty('i101',50,25),
ord_ty('i102',5,5)));

----把记录插入到嵌套表中
insert?into?table(select?p.dets?from?order_master?p
where?p.orderno='o100')
values('i103',30,25);

select?t.dets?from?order_master?t?where?t.orderno='o100';

----查看嵌套表中的信息?
select?*?from?table(select?t.dets?from?order_master?t
where?t.orderno='o100');

----修改
update?table(select?t.dets?from?order_master?t?
????????????????????where?t.orderno='o100')?t
???????????????????????????set?value(t)=ord_ty('i103',50,45)
???????????????????????????????where?t.itemcode='i103';

----删除嵌套表的值
delete?from?table(select?t.dets?from?order_master?t
???????where?t.orderno='o100')?t
???????where?t.itemcode='i102';
???????
----把嵌套表中已存在的记录添加到创建的表中
insert?into?order_master?values('o202',to_date('2003-3-5','YY-MM-DD'),
'v101',cast(multiset(select?*?from?table(select?dets?from?order_master
where?orderno='o201'))?as?ord_nt));
???????
----创建对象
create?type?vend_ty?as?object(
vencode?varchar2(5),
venname?varchar2(20),
venadd1?varchar2(20),
venadd2?varchar2(20),
venadd3?varchar2(20),
tel_no?number(6));

drop?table?vend_master;

----创建对象表,对象中不能定义约束，在对象表中可通过关键字constraint定义
create?table?vend_master?of?vend_ty(vencode?constraint?vc_pk?primary?key);

insert?into?vend_master?values(
vend_ty('v201','John','10','Fezinnith','Mexico',948456));

----查看地址（表中所分配的OID）
select?ref(a)?from?vend_master?a;

----创建一个指向抽象数据类型的表
create?table?ord_master(
orderno?varchar2(5),
vendet?ref?vend_ty);----数据类型为指向抽象数据类型的类型

----类似将查询记录插入一个表的语法插入记录
insert?into?ord_master(select'o301',ref(a)from?vend_master?a
where?vencode='v201');

----查看所有记录
select?*?from?ord_master;

select?deref(a.vendet)?from?ord_master?a;

delete?from?vend_master?where?vencode='v201';


----对象视图
create?table?item(
??????itemcode?varchar2(10),
??????item_on_hand?number(10),
??????item_sold?number(10));

create?or?replace?type?item_type?as?object
??????(itemcode?varchar2(10),
??????item_on_hand?number(10),
??????item_sold?number(10));

create?view?item_view?of?item_type?with?object?oid----表名of类型名with?object?oid
(itemcode)?as
select?*?from?item?where?item_on_hand<20;

insert?into?item?values('i201',10,5);
----插入值调用函数
insert?into?item_view?values(item_type('i102',15,50));

create?view?nt_view?of?ord_ty?with?object?oid(itemcode)
as?select?*?from?table(select?d.dets
from?order_master?d?where?d.orderno='o201');

create?table?itemfile(
????????itemcode?varchar2(5)?primary?key,
????????itemdesc?varchar2(20),
????????p_category?varchar2(20),
????????qty_hand?number(5),
????????re_level?number(5),
????????max_level?number(5),
????????itemrate?number(9,2));

create?table?order_detail?(
????????orderno?varchar2(5),
????????itemcode?varchar2(5),
????????qty_ord?number(5),
????????qty_deld?number(5),primary?key(orderno,qty_ord,qty_deld),
????????foreign?key(itemcode)?references?itemfile(itemcode));

create?or?replace?type?itemfile_ty?as?object
????????(itemcode?varchar2(5),
????????itemdesc?varchar2(20),
????????p_category?varchar2(20),
????????qty_hand?number(5),
????????re_level?number(5),max_level?number(5),
????????itemrate?number(9,12));

create?view?itemfile_ov?of?itemfile_ty
???????with?object?oid(itemcode)
???????as?select?*?from?itemfile;

select?make_ref(itemfile_ov,itemcode)?from?itemfile;

create?view?order_detail_ov?
as?
???????select?make_ref(itemfile_ov,itemcode)?items,orderno,qty_ord,qty_deld?
??????????????from?order_detail;

----不能正确运行
select?deref(a.items)?from?order_detail_ov?a;

**********************************************************************************************



----创建抽象数据类型
create?or?replace?type?add_ty?as?object(
Street?varchar2(25),
City?varchar2(15),
State?varchar2(10),
Zip?number);

----基于抽象数据类型创建表
create?table?customer(
Customer_id?number(4),
person?add_ty);

----插入记录
insert?into?customer?values(
1001,add_ty('No.2?downhill?st.','Los?Angles','California',700023));

insert?into?customer?values(
1002,add_ty('No.120?stepahead?rd.','houston','texas',701024));

----查询记录
select?customer_id,c.person.city?from?customer?c
where?c.person.state='texas';

----删除记录
delete?from?customer?a
where?a.person.zip=701024;

----创建可变数组
create?type?Phone?as?varray(2)?of?Number(8);

----使用可变数组创建表
create?table?Employee(
Eno?number(4),
name?varchar2(15),
phone?phone);

----插入数据
insert?into?Employee?values(
1000,'George',Phone(67343344,3432342));

delete?from?Employee?where?name='gxj';

select?*?from?employee;

select?phone?from?employee;

----创建对象
create?type?person_details?as?object(
name?varchar2(15),
age?number(2),
desg?varchar2(15));


create?type?person_detail_table_ty?as?table?of?person_details;

create?table?other_info_person(
dept_name?varchar2(10),
dept_no?number(3),
person_info?person_detail_table_ty)
nested?table?person_info?store?as?person_store_table;


----创建抽象数据类型
create?or?replace?type?Dept_type?as?object(
Deptno?number(2),
Dname?varchar2(14),
Loc?varchar2(13));

----创建表
create?table?Student(
Name?varchar2(15),
Dept_detail?Dept_type);

----插入数据
insert?into?Student?values(
'Jessica',Dept_type(20,'Computer','Chicago'));

insert?into?Student?values(
'Peter',Dept_type(40,'Electronics','California'));

----查询数据
select?*?from?Student;

select?name,?a.dept_detail.Deptno?from?Student?a?
where?a.Dept_detail.Loc='Chicago';



insert?into?Employee?values(
1002,'Dick',Phone(33444876,87876565));
insert?into?Employee?values(
1003,'Jones',Phone(54576545,52457779));
??plsql
--例二：创建具有LOB数据类型的表
CREATE?TABLE?vendor_master
?(vencode????varchar2(5),
??venname????varchar2(15),
??venadd1????varchar2(20),
??venadd2????varchar2(20),
??venadd3????varchar2(20),
??tel_no?????number(6),
??msg????????CLOB);
??
--例三：初始化LOB值
INSERT?INTO?vendor_master?VALUES?
?('v201','aryay','10','first?st','mds',475859,
??'这是我们的初始化LOB值');
??
select?*?from?vendor_master;


--例9：条件控制
select?*?from?rs_employees
??where?hetongid='WL-090001';

DECLARE
??v_department???rs_employees.department%type;
BEGIN
??SELECT?department?INTO?v_department
????FROM?rs_employees
????WHERE?HeTongId='WL-090001';
????
??IF?v_department?=?'车间工人'?THEN
????UPDATE?rs_employees?
??????SET?department='不是工人'
??????WHERE?HeTongId='WL-090001';
??ELSE
????UPDATE?rs_employees?
??????SET?department='车间工人'
??????WHERE?HeTongId='WL-090001';
??END?IF;
END;
/

--CASE语句示例（下面的写法有错）
SET?SERVEROUT?ON;
DECLARE
??I?number:=2;
BEGIN
??CASE?
????WHEN?(I=1)?THEN?dbms_output.put_line('Result?is?1');
????WHEN?(I=2)?THEN?dbms_output.put_line('Result?is?2');
??END?CASE;
END;



--和上面的区别是什么
SET?SERVEROUT?ON;
DECLARE
??I?number:=2;
BEGIN
??CASE?I(用于选择器)
????WHEN?1?THEN?dbms_output.put_line('Result?is?1');
????WHEN?2?THEN?dbms_output.put_line('Result?is?2');
??END?CASE;
END;
/

--例11：简单循环：在Test?Window中执行
--SET?SERVEROUT?ON;
DECLARE
??a????NUMBER?:=?100;
BEGIN
??LOOP
????a?:=?a+25;
????EXIT?WHEN?A=250;
??END?LOOP;
??dbms_output.put_line(TO_CHAR(a));
END;

--例12：While循环，此值书上有错
DECLARE
??i????NUMBER?:=0;
??J????NUMBER?:=0;
BEGIN
?while?i<=100?Loop
???J?:=?J+1;
???i?:=?i+2;
?end?loop;
?dbms_output.put_line('j的值是'||j);
END;
?
--例13：FOR循环，结果是5050
DECLARE
??i??number?:=0;
??j??number?:=0;
BEGIN
??for?i?in?1..100
??loop
????j?:=?j?+?1;
??end?loop;
??dbms_output.put_line('j的值是'||j);
END;