Oracle数据定义语言(DDL)

数据库开发技术

Oracle数据定义语言（DDL）福建工程学院本章要点创建和管理Oracle表约束Oracle数据库表的各种类型1创建和管理表

描述主要的数据库对象表和列名的命名规则创建表描述可用于定义列的数据类型修改表定义删除、更名、截断表1.1数据库对象对象 描述表 存储数据的基本单元，由行和列组成视图 来自一个或多个表的数据的子集序列 产生主关键字值索引 提高某些查询的性能的数据结构同义词 赋予对象另外的名字1.2命名规则和约定(表和列名)必须以字母开头长度为1–30个字符只能包含A–Z,a–z,0–9,_,$和#对于同一个用户不能使用和其他对象相同的名字不能是Oracle的保留字注：1.名字不区分大小写，例如：EMP、eMP或eMp指的是同一个表。

2.在不同的表中命名相同的实体采用保持一致的原则，例如：部门编号在EMP和DEPT中都使用相同的名字DEPTNO。1.3CREATETABLE语句您必须具备:CREATETABLE的权限一定的存贮空间您需要指定:表名列名、列的数据类型及列的宽度CREATETABLE[schema.]table (column

datatype[DEFAULTexpr];1.3创建表(DDL)

SQL>CREATETABLEdept1

2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));表已创建。创建表验证是否创建成功(查看表结构)

SQL>DESCRIBEdept1

NameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)1.3创建表:DEFAULT选项指定在插入数据时，列的默认值…hiredateDATEDEFAULTSYSDATE,…

合法的值可以是字面值、表达式或SQL函数非法的值是另一个列的名称或虚拟列默认值的数据类型必须和列的数据类型匹配1.4引用其他用户的表属于其他用户而不属于当前用户的表需使用用户名作为表的前缀1.5Oracle的数据类型以下是Oracle数据类型的类别：数据类型字符数值日期时间RAW/LONGRAWLOB字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB1.5Oracle的数据类型

数值数据类型可以存储整数、浮点数和实数最高精度为38位数值数据类型的声明语法：NUMBER[(p[,s])]P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位1.5Oracle的数据类型

RAW数据类型用于存储二进制数据RAW数据类型最多能存储2000字节LONGRAW数据类型用于存储可变长度的二进制数据LONGRAW数据类型最多能存储2GBLOBLOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等LOB数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能够存储大量字符数据BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中1.5Oracle的数据类型

1.5数据类型P50类型 描述VARCHAR2(size) 变长字符串(字符族):1~4000个字符CHAR(size) 固定长度字符串(字符族)):1~2000个字符NUMBER(p,s)

定点小数值,p为精度,s为标度(数字族)DATE 日期和时间值(日期族)LONG 可存放高达2GB的变长字符数据(字符族)CLOB字符大对象 可存放高达4GB的变长单字节字符数据RAWandLONGRAW 原始二进制数据BLOB二进制大对象

可存放高达4GB二进制数据BFILE 引用一个外部的二进制文件;可存放高达

4GB数据

P50表3-11.6查询数据字典列出当前用户的表

列出当前用户不同的对象类型查询当前用户的表、视图、同义词及序列SQL>SELECT table_name2 FROM user_tables;SQL>SELECT DISTINCTobject_type2 FROM user_objects;SQL>SELECT *2 FROM user_catalog;1.7用子查询创建表

--利用现有的表创建表在CREATETABLE语句的AS子句中使用子查询可以在创建表的同时插入数据所指定列的数量和类型必须和子查询中列的数量和类型相匹配由列名和默认值定义列CREATETABLEtable [column(,column...)]ASsubquery;1.7使用子查询创建表

NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30SQL>CREATETABLEdept302 AS

3 SELECT empno,ename,sal*12ANNSAL,hiredate

4 FROM emp

5 WHERE deptno=30;表已创建。1.8ALTERTABLE语句使用ALTERTABLE语句可以：添加或删除一个列修改已存在的列为新列定义默认值ALTERTABLEtableADD (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY (columndatatype[DEFAULTexpr] [,columndatatype]...);添加新列

DEPT30

EMPNOENAME ANNSAL HIREDATE---------------- --------7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81...“在DEPT30表中添加新列JOB”

DEPT30

EMPNOENAME ANNSAL HIREDATE---------------- --------7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81...

JOB

JOB新列添加新列使用ADD子句添加新列columns.

EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQL>ALTERTABLEdept302ADD (jobVARCHAR2(9));表已更改。新列成为表的最后一列修改列可以修改列的数据类型、长度及默认值改变列的默认值仅仅影响后来插入的新记录ALTERTABLE dept30MODIFY (enameVARCHAR2(15));--原来是VARCHAR2(10)表已更改。删除一列(Oracle8i以上版本)从数据表中删除一列至少还必须剩下一列ALTERTABLE dept30DROPCOLUMNename;--一次只允许删除一列表已更改。1.9删除表表中的所有数据和结构都被删除任何与该表相关的视图或同义词依然存在，但是却是不可用的任何悬而未决的事务都将被提交所有的索引被删除.不能回滚该语句只有表的创建者或具有DROPANYTABLE权限的用户才能删除表SQL>DROPTABLEdept30;表已删除。1.10修改对象的名称要修改表、视图、序列或同义词的名称，可以使用RENAME语句您必须拥有对象的所有权.SQL>RENAMEdept1TOdepartment;表已重命名。RENAMEold_nameTOnew_name;1.11截断表TRUNCATETABLE语句：删除表中所有记录释放该表的存储空间使用TRUNCATE移除后不能回滚DELETE语句同样可以删除表中记录，但是它并不释放存储空间。SQL>TRUNCATETABLEdepartment;表被截断。1.12给表或列添加注释使用COMMENT语句，可以为表或列添加注释(不超过2000字节)注释可以通过以下字典表查询ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL>COMMENTONTABLEemp2IS'EmployeeInformation';注释已创建。2约束描述主要的约束类型建立表的同时创建约束对已存在的表添加、删除约束禁用、启用约束什么是约束阻止无效数据进入表中约束在表一级实施强制规则约束可以阻止删除和其它表具有相关性的表以下是Oracle中有效的约束类型：NOTNULLUNIQUEKeyCHECKPRIMARYKEYFOREIGNKEY值约束完整性约束约束准则给约束命名或者由Oreacle服务器产生一个形式为SYS_Cn的名称创建约束的方式:创建表的同时便创建约束表创建以后再创建约束在列或者表一级定义约束在数据字典中可以查询约束命名约束对于一个帐号，每个约束名必须是惟一的命名约束的约定格式:<tablename>_<columnname>_<constrainttype>如：emp_deptno_fk约束缩写PRIMARYKEYpkFOREIGNKEYfkUNIQUEukCHECKck或ccNOTNULLnn图普遍采用的约束的缩写表示定义约束CREATETABLE[schema.]table (column

datatype[DEFAULTexpr] [column_constraint],

… [table_constraint]);CREATETABLEemp( empnoNUMBER(4), enameVARCHAR2(10),

… deptnoNUMBER(7,2)NOTNULL,

CONSTRAINTemp_empno_pk PRIMARYKEY(EMPNO)); 定义约束列约束级

表约束级column[CONSTRAINTconstraint_name]constraint_type,column,...

[CONSTRAINTconstraint_name]constraint_type(column,...),NOTNULL约束确保该列不允许存放空值

EMP

EMPNO ENAME JOB ...COMMDEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20...NOTNULL约束(该列没有记录为NUILL)没有NOTNULL约束(任何一个记录该列可以为空值)NOTNULL约束NOTNULL约束在列一级上定义约束SQL>CREATETABLEemployee(2 empno NUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)NOTNULL);UNIQUE约束

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONUNIQUE约束

50 SALES DETROIT

60 BOSTONInsertinto不允许插入

(SALES

已经存在)允许插入UNIQUE约束可以定义在表或列一级SQL>CREATETABLEdept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname));注:Oracle服务器是通过隐含地在唯一关键字上创建唯一索引的方式实施UNIQUE约束的。PRIMARYKEY约束

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

FINANCE NEWYORK不允许(DEPTNO-20已经存在)不允许

(DEPTNO不允许为空)PRIMARYKEY约束可以定义在表或列一级SQL>CREATETABLEdept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname),6 CONSTRAINTdept_deptno_pkPRIMARYKEY(deptno));注:Oracle服务器会自动为主关键字列创建一个唯一索引。FOREIGNKEY约束

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS...PRIMARY

KEY

EMP

EMPNO ENAME JOB ...COMMDEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 30...FOREIGN

KEY

7571 FORD MANAGER ...200 97571 FORD MANAGER ...200Insertinto不允许插入

(DEPTNO-9在DEPT表中不存在)允许

FOREIGNKEY约束可以定义在表或列一级SQL>CREATETABLEemployee(2 empno NUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)NOTNULL,10 CONSTRAINTemp_deptno_fkFOREIGNKEY(deptno)11 REFERENCESdept(deptno));FOREIGNKEY约束可以定义在表或列一级SQL>CREATETABLEemployee(2 empno NUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)10constraintemp_deptno_fkreferencesdept(deptno));FOREIGNKEY约束中的关键字FOREIGNKEY在表或列一级上定义外键REFERENCES标识父表及相应的列ONDELETECASCADE如果删除父表中的记录则允许级联删除子表中相关连的记录或用ONDELETESETNULLCHECK约束定义每条记录都必须满足的条件表达式不允许使用:对伪列的引用，例如：CURRVAL,NEXTVAL,LEVEL及ROWNUM对其它记录其它值的查询可以调用SYSDATE,UID,USER及USERENV函数...,deptno NUMBER(2),CONSTRAINTemp_deptno_ckCHECK(DEPTNOBETWEEN10AND99),...添加约束添加、删除但不是修改约束启用一个已经禁用的约束使用MODIFY子句增加NOTNULL约束

ALTERTABLE tableADD[CONSTRAINTconstraint]type(column);添加约束为EMP表添加一个FOREIGNKEY约束，指定雇员的管理员编号必须是在EMP表中已经存在的雇员的雇员编号。SQL>ALTERTABLEemp2ADDCONSTRAINTemp_mgr_fk3 FOREIGNKEY(mgr)REFERENCESemp(empno);表已更改。删除约束从EMP表删除管理员约束SQL>ALTERTABLE emp2DROPCONSTRAINTemp_mgr_fk;表已更改。从DEPT表删除主键约束PRIMARYKEY，同时删除也删除了与该约束关联的EMP表DEPTNO上的外键FOREIGNKEY约束。SQL>ALTERTABLE dept2DROPPRIMARYKEYCASCADE;表已更改。禁用约束在ALTERTABLE语句中使用DISABLE子句可以使约束失效使用CASCADE选项可以使与该约束是从属关系的约束同时失效SQL>ALTERTABLE deptment2DISABLECONSTRAINT dept_deptno_pkCASCADE;表已更改。启用约束使用ENABLE子句可以激活已经禁用的约束

如果您启用唯一UNIQUE或主键PRIMARYKEY约束，唯一UNIQUE或主键PRIMARYKEY索引便自动创建SQL>ALTERTABLE deptment2ENABLECONSTRAINT dept_deptno_pk;表已更改。查询约束查询USER_CONSTRAINTS表可以检索出所有约束名称及定义CONSTRAINT_NAMECSEARCH_CONDITION-------------------------------------------------------------DEPT_DEPTNO_CKCdeptnoBETWEEN10AND99DEPT_DEPTNO_PKPDEPT_DNAME_UKUSQL>SELECTconstraint_name,constraint_type,2search_condition3FROMuser_constraints4WHEREtable_name='DEPTMENT';查询与约束相关的列CONSTRAINT_NAMECOLUMN_NAME-----------------------------------------------EMP_DEPTNO_FKDEPTNOEMP_EMPNO_PKEMPNOEMP_MGR_FKMGRSYS_C00674EMPNOSYS_C00675DEPTNOSQL>SELECT constraint_name,column_name2FROM

user_cons_columns3WHERE table_name=‘DEPTMENT';在USER_CONS_COLUMNS中查询与约束相关的列小结在Oracle中可以创建下列类型的约束：NOTNULLUNIQUEkeyCHECKPRIMARYKEYFOREIGNKEY通过数据字典表USER_CONSTRAINTS可以查询约束名称及其定义。Oracle数据库表的各种类型永久表：存储数据临时表：用在会话或事务处理期间按索引组织的表：用于具有主键值，且要频繁地查询主键值的那种表外部表：存储在数据库的“外部”3Oracle数据库表的各种类型永久表：存储数据临时表：CREATEGLOBALTEMPORARY

TABLE按索引组织的表：CREATETABLE

tablename…ORGANIZAITIONINDEX外部表：CREATETABLE

tablename…ORGANIZAITIONEXTERNAL演讲完毕，谢谢观看！OracleERP主要模块InvoicesARENGBOMWIPBillsand

RoutingsShipmentsECOSWork

OrdersGLAPPlanning(MRP)POMaterialRequirementsEnterprise

DemandVendorPOInterplantPOReceiptsWorkOrdersCustomersJournalEntriesJournalEntriesJournalEntriesVendorInvoicesVendorPaymentsTransportation

InformationBillsItemsOEINVInternalPaymentsCorporateJournalEntriesShipmentsOrdersFAMassAdditionJournalEntriesCOSTCOSTCOSTReceipts财务配销HRPayroll制造人力资源财务系统模块:Oracle总帐管理（GL）Oracle应付帐管理（AP）Oracle固定资产管理（FA）Oracle应收帐管理（AR）Oracle现金管理（CE）Oracle项目会计（PA）Oracle财务分析（OFA）分销系统模块:Oracle库存管理（INV）Oracle采购管理（PUR）Oracle销售定单管理（OE）Oracle销售&市场管理（SM）Oracle销售补偿管理（SC）Oracle售后服务管理（SR）Oracle销售分析（OSA）OracleERP模块清单制造系统模块:Oracle计划管理（MPS/MRP）Oracle能力计划管理（CAP）Oracle制造数据管理（BOM）Oracle车间生产管理（WIP）Oracle成本管理（CST）Oracle项目制造（PJM)Oracle流程制造(OPM)Oracle质量管理（QM)其他系统模块:Oracle设备管理（EM）Oracle人事管理（HR）Oracle薪金管理（PAYROLL）Oracle系统管理（SYSTEMADMIN）Oracle预警（ALT）Oracle多维数据分析/商业智能系统 （OLAP/BIS）Oracle桌面集成（ADI）OracleERP模块清单Engineering工程资料管理系统BillofMaterials物料清单及制程管理系统CostManagement成本管理系统OrderEntry订单管理系统MasterScheduling/MRP生产排程管理系统与物料需求计划系统WorkInProcess生产管理系统Inventory库存管理系统Purchasing采购管理系统Personnel人事管理系统Receivable应收账款管理系统Payable应付账款管理系统Assets固定资产管理系统GeneralLedger

总帐会计系统Capacity产能规划系统OracleApplications59Oracle行业模型:离散制造供应链企业后台管理客户关系管理计划寻源

制造

市场销售支持企业管理返回项目到盈利财务管理到财务报告概念到发布联系到解决合同到续签预测到计划采购到付款需求到制造(离散)订单到收款库存到履约问题到预防机会到订单Oracle主要业务流程DesigntoReleaseForecasttoPlanPlantoScheduleScheduletobuildProcuretoPayOrdertoCashInventorytoFulfillmentCostQuality61概念到发布返回设计到发布预测到计划计划到排程排程到生产(离散)概念到获利一级业务流程62预测到计划返回供应商协同到供应商承诺客户协同到达成一致预测预测到计划计划到排程排程到生产(离散)请求到接收–直接设计到发布一级业务流程63采购到支付返回分析到协议预测到计划申请到接收–直接申请到接收–间接供应商退货到贷项通知单供应商退货到补货验收到接收接收到资产供应商发票到付款补货信号费用报告到入帐一级业务流程64需求到制造(离散)返回一级业务流程返回计划到排程排程到生产(离散)检验到处理设计到发布按订单配置到发运按订单装配到发运一级业务流程65订单到收款返回订单到直发信贷审批到客户拣货到订单到发运客户退货到退款客户退货到退换客户发票-现金定制到订单到发运装配到订单到发运

销售线索到订单订单到发运订单到直接采购发运收入到过帐订单到客户发票一级业务流程66库存到履约返回申请到接收-直接排程到生产(离散)检验到处理产品标准成本到库存再估价订单到收款接收到库存准确度补货信号到补货子分类帐到过帐仓库布局到库存估价计划到排程一级业务流程客户订单CustomerPO销售预测Forecast主需求计划MDS主生产计划MPS物料需求

计划MRP请购作业PR采购作业PO

收料作业

Receiving工单开立WIP生产发料WIPIssue生产控制生产入库WIPComplete出货作业Shipping应付账款应收账款发票作业成本作业Oracle整合企业日常作业流程可承诺量查核ATP内部领用捡货单杂项发料/退货MiscIssue/Return发料/出货Issue入库异动ReceiptTransaction实际盘点PhysicalCounting标签与序列号列印TagGeneration盘点过账交货预测与再订购管理ForecastandReorder存货管制规则-Min-max-ReorderPoint-Sub-inventoryreplenishment交货/预定安排库存评估/报表定义作业规定总帐更新定义作业流程组织/仓库定义SetupOrganization/Subinventory检验Inspect登帐运送Deliver退货Return供应商Supplier内部Internal成本Cost状态Status目录Catalog分类Category模板Template供应商Supplier内部Internal订单处理自动请购余额更新调整库存平衡表循环盘点CycleCounting-自动排程-手动排程OracleInventory(库存管理系统)库存控制InventoryControl物料管理MaterialManagement收料Receipt联接操作制程料品定义Item定义资源Resource选用零配件/料品成本累计成品FinishGood定义资源使用量ResourceRate新产品流程建立替代制程AlternateRouting定义制程Routing材料需求ItemRequirement新产品开发建立替代性物料清单AlternateBOM工单排程量产日排程实施设计变更单OracleBillofMaterials(物料清单及制程管理系统)设计变更单ECO半成品Assembly建立物料清单BOM产品规格-型号、选项产生工单(或量产计划)完成品入库原物料耗用报表-工单成本-期间成本-成本差异-成本衡量-物料耗用-资源耗用组立装配Assembly(选项)外包反溯扣帐Backflash工单发放Release发料Issue装配完成工单完成WIPComplete制造资源耗用Resource-人工-机器设备-制造费用送货/交货外包交货外包OutsideProcessing安排生产制程物料清单展开计划工单手开工单销售订单OracleWorkInProcess(生产管理系统)预测资料ForecastOracleSO客户订单其它系统客户订单需求报表&查询主需求计划MDS需求排程更新MDSUpdate详细产能计划物料需求计划MRP工单WIP请购PRBOM计划工单计划采购单现有库存量OnhandOraclePlanning(MPS/MRP管理系统)主生产计划MPS交易资讯Transaction定义配件成本报告采购收料POReceive定义成本计算规则成本累计CostRollup成本更新CostUpdate库存值计算采购料品材料材料管理费用用料清单制程

成本元件-工作项目/活动Project/Active-料品Item-批次Lot-资源单位Uom-总价半成品库存资源成本采购退货POReturn公司间移转OrganizationTransfer移动平均成本更新杂项收发MiscellaneousReceipt/Issue选择模拟规则模拟冻结成本Frozen冻结成本OracleCostManagement(成本管理系统)请购单处理收货

(料)Receipt检验Inspect异动处理-收货-运送-调拨退货Return应付账款对帐应计作业-暂记项目-凭证-月结报价单收集请求报价RFQ(选择性项目)自动产生订购单发放Release定义企业流程收料控制-核准及安全管制-交货来源-采购预算管制-异动处理方法内部转移InterOrgTransfer发票处理应付账款成立APInvoice总帐科目更新核准Approve供应商挑选供应商交货允收AcceptOraclePurchasing(采购管理系统)MRP需求存货状况OnhandQty在制品(委外)OutProcess手开请购ManualPR开放界面Interface列印/EDI核准Approve各种采购单-标准式Standard-计划式Planned-合并式Blanket-合约式Contract判退Reject选配件产品订单输入OrderEnter发票开立Invoice出货管理Shipping退货管理RMA退货审核Approve货品退回Return订单完成重排程Re-schedule保留Reservation库存余额更新未交订单管理定义各项流程次序定义流程元件定义企业运作流程Workflow-弹定规则订单处理流程-授信额度检查-退货管理