区别是和sqlserver比较
-- 示例一:Create Table 命令,区别较小
Create Table vendor_master
(
vencode varchar2 ( 5 ),
venname varchar2 ( 20 ),
venadd1 varchar2 ( 20 ),
venadd2 varchar2 ( 20 ),
venadd3 varchar2 ( 20 )
)
-- 示例二:Alter Table Modify 命令,区别较大
Alter Table vendor_master Modify (venname varchar2 ( 25 ))
-- 示例三:Alter Table Add 命令,区别较小,主要是数据类型
Alter Table vendor_master
add (tel_no number ( 12 ),
tngst_no number ( 12 ))
-- 示例四:Drop Column 命令:完全一样
Alter Table vendor_master Drop Column tngst_no
-- 示例五:Oracle 独有
alter Table vendor_master set unused(tel_no)
-- 示例六:Truncate Table命令 :完全一样
truncate table vendor_master
-- 示例八:Desc命令:完全不一样
Desc vendor_master
-- 示例九:Drop Table 命令:完全一样
drop table vendor_master
-- 示例10:Insert命令
Insert into vendor_master values ( ' v001 ' , ' John smith ' , ' 11 E main st ' , ' West Avenue ' , ' alabama ' , 1234567 )
-- 以下这种方法只在Orace中有效,l但不推荐使用此方法
Insert into vendor_master values ( ' &vencode ' , ' &venname ' , ' &venadd1 ' , ' &venadd2 ' , ' &venadd3 ' , & telno)
-- 示例15:Select命令
select * from vendor_master
-- 示例20:Update命令 :注意大小写
update vendor_master set tel_no = 987654 where vencode = ' V001 ' -- 'v001'
-- 示例24:Grant 和 Revoke命令
grant all on vendor_master to sys
revoke all on vendor_master from sys
**********************************************************************************************
// 用户
connect system / manager @ydgl ;
-- 删除已有的用户和表空间
-- drop tablespace freemandatabase;
-- drop tablespace tempfreemandatabase;
-- 创建表空间
create tablespace FreeManDataBase
datafile ' c:\FreeManDataBase.ora '
size 25M;
--查看当前用户的缺省表空间
select username,default_tablespace from user_users;
-- 创建临时表空间
create temporary tablespace tempFreeManDataBase
tempfile ' c:\tempFreeManDataBase.ora '
size 25M;
-- 创建用户
create user zong identified by " 123456 "
default tablespace FreeManDataBase
temporary tablespace tempFreeManDataBase;
-- 赋权限
grant connect to zong;
grant resource to zong;
grant dba to aaa;
-- 登录
connect zong / 123456 @ydgl ;
create table zong.ccc(bh varchar2 ( 10 ), xm varchar2 ( 10 ), age number , salary number , birthday date)
-- 事务处理
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
savepoint ppp;
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
rollback to ppp;
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
update zong.ccc set bh = ' 008 ' ,salary = 5000 ,age = 33 where bh = ' 004 ' ;
commit ;
-- 清除数据
truncate table zong.cc
delete from zong.ccc where bh = ' 006 '
create table zong.firsttable (xm varchar2 ( 10 ),age number ( 4 ),salary number ( 7 , 2 ))
-- 添加列
alter table zong.firsttable add (kk number ( 10 ), birthday date)
-- 更新列类型
alter table zong.firsttable modify (xm number ( 2 ), birthday varchar2 ( 10 ))
-- 收回权限
revoke dba from zong
-- 授予管理员角色
grant dba to zong
-- 授予对象权限
grant select , update on firsttable to system
-- 删除表
drop table zong.ccc
-- 集合操作
Create Table zong.YYY(xm Varchar2 ( 10 ),age Number ( 8 ));
Insert Into zong.yyy Values ( ' aaa ' , 10 )
Insert Into zong.yyy Values ( ' bbb ' , 20 )
Create Table zong.xxx(xm Varchar2 ( 10 ),age Number ( 8 ));
Insert Into zong.xxx Values ( ' aaa ' , 10 )
Insert Into zong.xxx Values ( ' ccc ' , 30 )
Select * From zong.yyy Union Select * From fei.xxx
Select * From zong.yyy Union All Select * From fei.xxx
Select * From zong.yyy Intersect Select * From fei.xxx
Select * From zong.yyy Minus Select * From fei.xxx
*********************************************
**********************************************
-- 字符串函数
select ascii ( ' A ' ) A, ascii ( ' a ' ) a, ascii ( ' 0 ' ) zero, ascii ( ' ' ) space from dual
select chr( 54740 ) zhao,chr( 65 ) chr65 from dual
select concat( ' 010- ' , ' 88888888 ' ) || ' 连接 ' 实例 from dual
select initcap( ' smith ' ) upp from dual;
select instr( ' oracle traning ' , ' ra ' , 1 , 2 ) instring from dual
select lpad(rpad( ' gao ' , 10 , ' * ' ), 17 , ' * ' ) from dual;
select ltrim ( rtrim ( ' gao qian jing ' , ' ' ), ' ' ) from dual;
select substr( ' 13088888888 ' , 3 , 8 ) from dual;
select replace ( ' he love you ' , ' he ' , ' i ' ) from dual;
-- 数学函数
select floor ( 2345.67 ) from dual;
select mod( 10 , 3 ),mod( 3 , 3 ),mod( 2 , 3 ) from dual;
select round ( 55.5 ), round ( - 55.4 ),trunc( 55.5 ),trunc( - 55.5 ) from dual;
select sign ( 123 ), sign ( - 100 ), sign ( 0 ) from dual;
-- 日期函数
select to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ), 2 ), ' yyyymm ' ) from dual;
select to_char(sysdate, ' yyyy.mm.dd ' ),to_char((sysdate) + 1 , ' yyyy.mm.dd ' ) from dual;
select last_day(sysdate) from dual;
select months_between( ' 19-12月-1999 ' , ' 19-3月-1999 ' ) mon_between from dual;
select months_between(to_date( ' 2000.05.20 ' , ' yyyy.mm.dd ' ),to_date( ' 2005.05.20 ' , ' yyyy.mm.dd ' )) mon_betw from dual;
select to_char(sysdate, ' yyyy.mm.dd hh24:mi:ss ' ) 北京时间,to_char(new_time
(sysdate, ' PDT ' , ' GMT ' ), ' yyyy.mm.dd hh24:mi:ss ' ) 埃及时间 from dual;
select next_day( ' 18-5月-2001 ' , ' 星期五 ' ) next_day from dual;
//
select round (sysdate, ' year ' ) from ccc;
select to_char(sysdate, ' dd-mm-yyyy day ' ) from dual;
select * from ccc where birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 120 ;
select * from ccc where birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 30 ;
select * from ccc where birthday > to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' );
select to_char(t.d, ' YY-MM-DD ' ) from (
select trunc(sysdate, ' MM ' ) + rownum - 1 as d
from dba_objects
where rownum < 32 ) t
where to_char(t.d, ' MM ' ) = to_char(sysdate, ' MM ' ) -- 找出当前月份的周五的日期
and trim(to_char(t.d, ' Day ' )) = ' 星期五 '
-- 类型转换函数
select to_char(sysdate, ' yyyy/mm/dd hh24:mi:ss ' ) from dual;
select to_number( ' 1999 ' ) year from dual;
-- 系统函数
select username, user_id from dba_users where user_id = uid;
select user from dual;
-- 集合函数
create table table3(xm varchar ( 8 ),sal number ( 7 , 2 ));
insert into table3 values ( ' gao ' , 1111.11 );
insert into table3 values ( ' gao ' , 1111.11 );
insert into table3 values ( ' zhu ' , 5555.55 );
-- select avg(distinct sal) from gao.table3;
-- select max(distinct sal) from scott.emp;
-- 分组函数和统计函数
select deptno, count ( * ), sum (sal) from scott.emp group by deptno;
select deptno, count ( * ), sum (sal) from scott.emp group by deptno having count ( * ) >= 5 ;
select deptno, count ( * ), sum (sal) from scott.emp having count ( * ) >= 5 group by deptno ;
select deptno,ename,sal from scott.emp order by deptno,sal desc ;
**********************************************************************************************
CREATE TABLE "ZONG"."CCC"("BH" VARCHAR2 ( 10 ), "XM" VARCHAR2 ( 10 ), "AGE" NUMBER , "SALARY" NUMBER , "BIRTHDAY" DATE)
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 001 ' , null , 33 , 4444 ,to_date( ' 1979-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
select bh 编号, nvl(xm, ' ggg ' ) as 姓名 from ccc where bh = ' 001 '
select bh 编号, nvl2(xm, ' yes ' , ' no ' ) as 姓名 from ccc
-- select bh 编号,NULLIF('bbb','aaa') from ccc
-- is null 的用法
select * from ccc where xm is null
select * from ccc where xm is NOT null
-- not in的用法
select * from ccc where birthday between to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ) and to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
select * from ccc where birthday not between to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ) and to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
-- in的用法
select * from ccc where xm in ( ' aaa ' , ' peng ' , ' cao ' )
select * from ccc where xm not in ( ' aaa ' , ' peng ' , ' cao ' )
-- like的用法和=、!=、<、>、<=、>=的用法
select * from ccc where age > 24 and age <= 56 and xm like ' %a% '
--
create table sales (xm varchar2 ( 10 ), dTime date, count number , totalmoney number ,city varchar2 ( 10 ))
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
select xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city from sales group by xm , count ,totalmoney,rollup(city) order by xm , count ,totalmoney,city
-- group分组语句
select xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city from sales group by xm , count ,totalmoney,rollup(city) having count > 2000 order by xm , count ,totalmoney,city
-- rollup函数
select xm, sum ( count ) 数量,city from sales group by xm , count ,rollup(city) order by xm , count ,city
-- 事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束(commit or rollback),Oracle自动清除临时表中数据
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR ( 20 ))
ON COMMIT DELETE ROWS;
create table permernate( a number );
insert into admin_work_area values (sysdate,sysdate, ' temperary table ' );
insert into permernate values ( 1 );
commit ;
select * from admin_work_area;
select * from permernate;
-- 会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据
drop table admin_work_area;
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR ( 20 ))
ON COMMIT PRESERVE ROWS;
create table permernate( a number );
insert into admin_work_area values (sysdate,sysdate, ' temperary table ' );
insert into permernate values ( 2 );
commit ;
select * from admin_work_area;
select * from permernate;
**********************************************************************************************
// 锁
create table sales (xm varchar2 ( 10 ), dTime date, count number , totalmoney number ,city varchar2 ( 10 ))
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
select * from sales where xm = ' 张三 ' for update of count
update sales set count = 30000 where xm = ' 张三 '
-- 另一用户登录
update ydgl.sales set count = 30000 where xm = ' 张三 '
-- 在多个用户在同一张表中放置锁时,其他用户等待上一用户的时间.
select * from sales where xm = ' 张三 ' for update of count wait 10
-- 只作查询
lock table sales in share mode
-- 能删除,更新,插入除锁定外的其他行
lock table sales in share update mode
-- 排他锁 ,在同一时间点上,只有一个用户在表中放置排他锁.
lock table sales in exclusive mode
-- 避免延迟时间,锁在用户之间的传递时间,不等待,立即提示错误信息
lock table sales in exclusive mode nowait
**********************************************************************************************
-- --创建临时表
/**/ /* create temporary tablespace mydb
tempfile 'f:\mydb.ora'
size 10m; */
-- --创建抽象数据类型
create or replace type address_ty as object
(street_no number ( 3 ),
street_name varchar2 ( 20 ),
city varchar2 ( 20 ),
state varchar2 ( 20 ));
-- --查看抽象数据类型实际值
select attr_name,length,attr_type_name
from user_type_attrs
where type_name = ' ADDRESS_TY ' ;
-- --创建应用了抽象数据类型的表
create table vend_mast
(vencode varchar2 ( 5 ),
venname varchar2 ( 15 ),
venadd address_ty,
tel_no number ( 10 ));
-- --查看表结构
desc vend_mast;
select column_name,data_type from user_tab_columns where
table_name = ' VEND_MAST ' ;
-- --插入记录
insert into vend_mast values
( ' v100 ' , ' john ' ,address_ty( 110 , ' Clinton Rd ' ,
' Rosewood ' , ' Columbia ' , 234465987 );
-- --查看记录
select a.venadd.city from vend mast a;
-- -修改记录,一定要用别名
update vend_mast a
set a.venadd.street_no = 10
where venname = ' john ' ;
-- --删除记录
delete from vend_mast a
where a.venadd.city = ' Rosewood ' ;
-- --强行删除抽象数据类型
drop type address_ty force;
-- --创建应用了抽象数据类型的表的索引
create index streetnum on vend_mast(venadd.street_no);
-- --查看索引
select owner,index_name,index_type,table_owner,table_name,table_type
from all_indexes
where owner = ' SCOTT ' ;
-- --创建不能继承的对象
create or replace type Student_typ as object
(Ssn number ,
Name varchar2 ( 30 ),
Address varchar2 ( 100 )) not final;
-- --修改是否能继承
alter type Student_typ not final;
create type t as object
(x number ,)
not instantiable member function func1 return number )
not instantiable not final;
-- --创建可变数组
create type itemcode as varray( 5 ) of varchar2 ( 5 );
create type qty_ord as varray( 5 ) of number ( 5 );
create type qty_deld as varray( 5 ) of number ( 5 );
-- --基于可变数组创建表
create table order_detail(
orderno varchar2 ( 5 ),
item_va itemcode,
qty_va qty_ord,
qtyd_va qty_deld);
-- --插入记录
insert into order_detail
values ( ' o100 ' ,itemcode( ' i100 ' , ' i101 ' , ' i102 ' , ' i103 ' , ' i104 ' ),
qty_ord( 100 , 98 , 47 , 29 , 20 ),
qty_deld( 100 , 900 , 800 , 700 , 600 ));
-- --查看整体
select * from order_detail
-- --单个
select item_va from order_detail
-- --查看可变数组内容
select * from table (
-- --select * from order_detail a where a.orderno='o100')
select a.item_va from order_detail a where a.orderno = ' o100 ' )
-- --嵌套表
-- --创建抽象数据类型即对象
create or replace type ord_ty as object (
itemcode varchar2 ( 5 ),
qty_ord number ( 5 ),
qty_deld number ( 5 ));
-- --表中包含嵌套表一定要基于一个对象创建一个新的对象作为嵌套表
create or replace type ord_nt as table of ord_ty;
-- --创建包含嵌套表的表
create table order_master(
orderno varchar2 ( 5 ),
odate date,
vencode varchar2 ( 5 ),
dets ord_nt)
nested table dets store as ord_nt_tab;
-- 嵌套表放入某个任意任名的存储空间,嵌套表的存储空间与普通表不同,分别存储在不同的空间
insert into order_master values (
' o100 ' ,to_date( ' 18-07-99 ' , ' DD-MM-YY ' ), ' v001 ' ,
ord_nt(ord_ty( ' i100 ' , 10 , 5 ),
ord_ty( ' i101 ' , 50 , 25 ),
ord_ty( ' i102 ' , 5 , 5 )));
-- --把记录插入到嵌套表中
insert into table ( select p.dets from order_master p
where p.orderno = ' o100 ' )
values ( ' i103 ' , 30 , 25 );
select t.dets from order_master t where t.orderno = ' o100 ' ;
-- --查看嵌套表中的信息
select * from table ( select t.dets from order_master t
where t.orderno = ' o100 ' );
-- --修改
update table ( select t.dets from order_master t
where t.orderno = ' o100 ' ) t
set value(t) = ord_ty( ' i103 ' , 50 , 45 )
where t.itemcode = ' i103 ' ;
-- --删除嵌套表的值
delete from table ( select t.dets from order_master t
where t.orderno = ' o100 ' ) t
where t.itemcode = ' i102 ' ;
-- --把嵌套表中已存在的记录添加到创建的表中
insert into order_master values ( ' o202 ' ,to_date( ' 2003-3-5 ' , ' YY-MM-DD ' ),
' v101 ' , cast (multiset( select * from table ( select dets from order_master
where orderno = ' o201 ' )) as ord_nt));
-- --创建对象
create type vend_ty as object(
vencode varchar2 ( 5 ),
venname varchar2 ( 20 ),
venadd1 varchar2 ( 20 ),
venadd2 varchar2 ( 20 ),
venadd3 varchar2 ( 20 ),
tel_no number ( 6 ));
drop table vend_master;
-- --创建对象表,对象中不能定义约束,在对象表中可通过关键字constraint定义
create table vend_master of vend_ty(vencode constraint vc_pk primary key );
insert into vend_master values (
vend_ty( ' v201 ' , ' John ' , ' 10 ' , ' Fezinnith ' , ' Mexico ' , 948456 ));
-- --查看地址(表中所分配的OID)
select ref(a) from vend_master a;
-- --创建一个指向抽象数据类型的表
create table ord_master(
orderno varchar2 ( 5 ),
vendet ref vend_ty); -- --数据类型为指向抽象数据类型的类型
-- --类似将查询记录插入一个表的语法插入记录
insert into ord_master( select ' o301 ' ,ref(a) from vend_master a
where vencode = ' v201 ' );
-- --查看所有记录
select * from ord_master;
select deref(a.vendet) from ord_master a;
delete from vend_master where vencode = ' v201 ' ;
-- --对象视图
create table item(
itemcode varchar2 ( 10 ),
item_on_hand number ( 10 ),
item_sold number ( 10 ));
create or replace type item_type as object
(itemcode varchar2 ( 10 ),
item_on_hand number ( 10 ),
item_sold number ( 10 ));
create view item_view of item_type with object oid -- --表名of类型名with object oid
(itemcode) as
select * from item where item_on_hand < 20 ;
insert into item values ( ' i201 ' , 10 , 5 );
-- --插入值调用函数
insert into item_view values (item_type( ' i102 ' , 15 , 50 ));
create view nt_view of ord_ty with object oid(itemcode)
as select * from table ( select d.dets
from order_master d where d.orderno = ' o201 ' );
create table itemfile(
itemcode varchar2 ( 5 ) primary key ,
itemdesc varchar2 ( 20 ),
p_category varchar2 ( 20 ),
qty_hand number ( 5 ),
re_level number ( 5 ),
max_level number ( 5 ),
itemrate number ( 9 , 2 ));
create table order_detail (
orderno varchar2 ( 5 ),
itemcode varchar2 ( 5 ),
qty_ord number ( 5 ),
qty_deld number ( 5 ), primary key (orderno,qty_ord,qty_deld),
foreign key (itemcode) references itemfile(itemcode));
create or replace type itemfile_ty as object
(itemcode varchar2 ( 5 ),
itemdesc varchar2 ( 20 ),
p_category varchar2 ( 20 ),
qty_hand number ( 5 ),
re_level number ( 5 ),max_level number ( 5 ),
itemrate number ( 9 , 12 ));
create view itemfile_ov of itemfile_ty
with object oid(itemcode)
as select * from itemfile;
select make_ref(itemfile_ov,itemcode) from itemfile;
create view order_detail_ov
as
select make_ref(itemfile_ov,itemcode) items,orderno,qty_ord,qty_deld
from order_detail;
-- --不能正确运行
select deref(a.items) from order_detail_ov a;
**********************************************************************************************
-- --创建抽象数据类型
create or replace type add_ty as object(
Street varchar2 ( 25 ),
City varchar2 ( 15 ),
State varchar2 ( 10 ),
Zip number );
-- --基于抽象数据类型创建表
create table customer(
Customer_id number ( 4 ),
person add_ty);
-- --插入记录
insert into customer values (
1001 ,add_ty( ' No.2 downhill st. ' , ' Los Angles ' , ' California ' , 700023 ));
insert into customer values (
1002 ,add_ty( ' No.120 stepahead rd. ' , ' houston ' , ' texas ' , 701024 ));
-- --查询记录
select customer_id,c.person.city from customer c
where c.person.state = ' texas ' ;
-- --删除记录
delete from customer a
where a.person.zip = 701024 ;
-- --创建可变数组
create type Phone as varray( 2 ) of Number ( 8 );
-- --使用可变数组创建表
create table Employee(
Eno number ( 4 ),
name varchar2 ( 15 ),
phone phone);
-- --插入数据
insert into Employee values (
1000 , ' George ' ,Phone( 67343344 , 3432342 ));
delete from Employee where name = ' gxj ' ;
select * from employee;
select phone from employee;
-- --创建对象
create type person_details as object(
name varchar2 ( 15 ),
age number ( 2 ),
desg varchar2 ( 15 ));
create type person_detail_table_ty as table of person_details;
create table other_info_person(
dept_name varchar2 ( 10 ),
dept_no number ( 3 ),
person_info person_detail_table_ty)
nested table person_info store as person_store_table;
-- --创建抽象数据类型
create or replace type Dept_type as object(
Deptno number ( 2 ),
Dname varchar2 ( 14 ),
Loc varchar2 ( 13 ));
-- --创建表
create table Student(
Name varchar2 ( 15 ),
Dept_detail Dept_type);
-- --插入数据
insert into Student values (
' Jessica ' ,Dept_type( 20 , ' Computer ' , ' Chicago ' ));
insert into Student values (
' Peter ' ,Dept_type( 40 , ' Electronics ' , ' California ' ));
-- --查询数据
select * from Student;
select name, a.dept_detail.Deptno from Student a
where a.Dept_detail.Loc = ' Chicago ' ;
insert into Employee values (
1002 , ' Dick ' ,Phone( 33444876 , 87876565 ));
insert into Employee values (
1003 , ' Jones ' ,Phone( 54576545 , 52457779 ));
plsql
-- 例二:创建具有LOB数据类型的表
CREATE TABLE vendor_master
(vencode varchar2 ( 5 ),
venname varchar2 ( 15 ),
venadd1 varchar2 ( 20 ),
venadd2 varchar2 ( 20 ),
venadd3 varchar2 ( 20 ),
tel_no number ( 6 ),
msg CLOB);
-- 例三:初始化LOB值
INSERT INTO vendor_master VALUES
( ' v201 ' , ' aryay ' , ' 10 ' , ' first st ' , ' mds ' , 475859 ,
' 这是我们的初始化LOB值 ' );
select * from vendor_master;
-- 例9:条件控制
select * from rs_employees
where hetongid = ' WL-090001 ' ;
DECLARE
v_department rs_employees.department % type;
BEGIN
SELECT department INTO v_department
FROM rs_employees
WHERE HeTongId = ' WL-090001 ' ;
IF v_department = ' 车间工人 ' THEN
UPDATE rs_employees
SET department = ' 不是工人 '
WHERE HeTongId = ' WL-090001 ' ;
ELSE
UPDATE rs_employees
SET department = ' 车间工人 '
WHERE HeTongId = ' WL-090001 ' ;
END IF ;
END ;
/
-- CASE语句示例(下面的写法有错)
SET SERVEROUT ON ;
DECLARE
I number : = 2 ;
BEGIN
CASE
WHEN (I = 1 ) THEN dbms_output.put_line( ' Result is 1 ' );
WHEN (I = 2 ) THEN dbms_output.put_line( ' Result is 2 ' );
END CASE ;
END ;
-- 和上面的区别是什么
SET SERVEROUT ON ;
DECLARE
I number : = 2 ;
BEGIN
CASE I(用于选择器)
WHEN 1 THEN dbms_output.put_line( ' Result is 1 ' );
WHEN 2 THEN dbms_output.put_line( ' Result is 2 ' );
END CASE ;
END ;
/
-- 例11:简单循环:在Test Window中执行
-- SET SERVEROUT ON;
DECLARE
a NUMBER : = 100 ;
BEGIN
LOOP
a : = a + 25 ;
EXIT WHEN A = 250 ;
END LOOP;
dbms_output.put_line(TO_CHAR(a));
END ;
-- 例12:While循环,此值书上有错
DECLARE
i NUMBER : = 0 ;
J NUMBER : = 0 ;
BEGIN
while i <= 100 Loop
J : = J + 1 ;
i : = i + 2 ;
end loop;
dbms_output.put_line( ' j的值是 ' || j);
END ;
-- 例13:FOR循环,结果是5050
DECLARE
i number : = 0 ;
j number : = 0 ;
BEGIN
for i in 1 .. 100
loop
j : = j + 1 ;
end loop;
dbms_output.put_line( ' j的值是 ' || j);
END ;