<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


        



    blogjava's web log
    

    blogjava's web log

    
...

    


    



  
    
			
    
				
					
	
    
		
    
			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;

    
				--
				創建臨時表空間
				
						
    
						
				    
				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;
    
		
    
			posted on 2006-04-17 21:13 record java and net 閱讀(1610) 評論(0)  編輯  收藏  所屬分類: Database 
		
    
	
    
	
	


	


    
	    
    




    








    

				
			
    
			
    
				
					
					

    導航
    

					
					
					

    常用鏈接
    


    留言簿(44)
    


		
    新聞檔案
    
		
				
			
	
		
    2.動態語言
    
		
				
			
	
		
    3.工具箱
    
		
				
			
	
		
    9.文檔教程
    
		
				
			
	
		
    友情鏈接
    
		
				
			
	



    搜索
    



    最新評論
	
    


					

				
			
    			
			
	

    






    




    







    
        
	



主站蜘蛛池模板：
女人18毛片水最多免费观看|
日韩亚洲国产高清免费视频|
日韩成人免费视频|
足恋玩丝袜脚视频免费网站|
成年网站免费视频A在线双飞|
女人被弄到高潮的免费视频|
亚洲国产精品日韩专区AV|
亚洲熟妇无码八AV在线播放|
亚洲精品成人久久|
亚洲午夜无码毛片av久久京东热|
337P日本欧洲亚洲大胆精品|
a毛片免费全部在线播放**|
222www免费视频|
永久免费看mv网站入口|
在线精品亚洲一区二区三区|
久久久亚洲AV波多野结衣|
亚洲成_人网站图片|
国产vA免费精品高清在线观看|
8090在线观看免费观看|
成人性生活免费视频|
国产亚洲精品无码拍拍拍色欲|
久久久亚洲裙底偷窥综合|
国产成人精品亚洲|
99久久免费观看|
四虎成人精品在永久免费|
久久亚洲精品无码AV红樱桃|
国产午夜亚洲精品不卡|
午夜无码A级毛片免费视频|
免费涩涩在线视频网|
亚洲av无码成h人动漫无遮挡|
国产成人亚洲综合网站不卡|
久久免费香蕉视频|
成年人免费网站在线观看|
亚洲va久久久噜噜噜久久狠狠|
亚洲人成电影网站色www|
永久免费av无码入口国语片|
日本成人免费在线|
久久久久亚洲Av无码专|
日本一区二区三区免费高清在线|
国产精品成人免费福利|
国产亚洲精久久久久久无码AV
|