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 閱讀(1613) 評論(0)  編輯  收藏  所屬分類: Database 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
					

          導航
          

					
					
					

          常用鏈接
          


          留言簿(44)
          


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



          搜索
          



          最新評論
	
          


					

				
			
          			
			
	

          






          




    







          
        
	




主站蜘蛛池模板：
株洲县|
巴里|
九寨沟县|
且末县|
称多县|
兰考县|
无锡市|
浏阳市|
巴林右旗|
巴马|
灵璧县|
浠水县|
永顺县|
安岳县|
伊宁县|
威信县|
梓潼县|
松阳县|
海安县|
高平市|
明水县|
浦北县|
阜宁县|
泾源县|
惠州市|
宿迁市|
合阳县|
涟水县|
黄平县|
柳林县|
武鸣县|
南陵县|
澄迈县|
汉阴县|
静安区|
漳州市|
七台河市|
柘荣县|
龙南县|
金华市|
高安市|