oracle not in一个变量,Oracle基本语法

最新推荐文章于 2023-12-15 17:41:45 发布

weixin_39814126

最新推荐文章于 2023-12-15 17:41:45 发布

阅读量430

点赞数

文章标签： oracle not in一个变量

select a.uiblockid,a.uilac_id,cast(a.subsum*1.0/b.totalsum as numeric (10, 2) ) BL from

(select sum(uinmsg_lac) subsum,uilac_id,uiblockid from

T_msg_lac group by uilac_id,uiblockid) a,

(select sum(uinmsg_block_all) totalsum,uiblockid from T_msg_lac

group by uiblockid) b

where a.uiblockid=b.uiblockid

清空回收站

PURGE recyclebin;

创建用户

CREATE USER "CEMDB" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK

GRANT "CONNECT" TO "CEMDB"

alter session set NLS_date_format='YYYY-MM-DD'; 修改数据查询时间格式

create sequence NumSeq increment by 1 start with 1 maxvalue 999; 创建序列

insert into table values(NumSeq.nextval); 表中插入序列

select NumSeq.currval from dual; 查询序列当前值;

drop sequence NumSeq ; 删除序列;

Create or replace type 类型名 as object

(

colum1 varchar(20),

colum2 varchar(30)

) 创建类型

Create table tableObject

(

Colum1 char(10),

Colum2 类型名

) 创建代类型的表

Insert into tableobject values('aaa',类型名('a','b')); 想自定义类型表中添加记录

select s.Colum1,s.Colum2.colum1,s.Colum2.Colum2 from tableObject s 查询自定义类型表记录

create or replace type person as object

(

personName varchar(10),

personSex varchar(2),

PessonBirth date

) not final; 创建可扩展的类型

create or replace Type studentType under person

(

StudentNo int,

StudentScore int

) 此类型继承了 Person类型. 也就是 studentType类型中已经有了 Person类型的三个属性.

create table student of StudentType; 创建具有StudentType的表

类型中不能保存数据.只有在创建此类型的表才可以存储数据.

insert into student values('a','b',to_date(1980-1-1','YYYY-MM-DD'),1001,89)); 向此关系类型的表中添加数据.

select * from student 对具有此类型的表进行查询.

update student a set a.personsex='女' where personname='a'; 更新此类型的表中的数据.

insert into student select studenttype('a','b',to_date(1980-1-1','YYYY-MM-DD'),1001,89)) from student where personName='a'; 创建抽象类型的面向对象表

updata student set studenttype('a','b',to_date(1980-1-1','YYYY-MM-DD'),1001,89)) where provid='10001'; 对表中的整个对象进行更新.

创建代方法的对象类型

create or replace type studenttype as object

(

stuid varchar(10),

stuName varchar(10),

HomAddress Address,

Member funcation getAddress Return Address,

Member procedure setAddress(newAddress Address)

);

create or replace type body studenttype as

Member function getAddress return Address is

begin

Return HomeAddress;

end;

Member procedure setAddress(newAddress Address) is

begin

HomeAddress:=newAddress;

end;

一创建带有可变数组的表

---格式 create or replace type 基类型名 as object (字段清单)

--实例

create or replace type MingXiType as object

(

goodsid varchar(15),

InCount int,

ProviderID varchar(10)

)

B.建立嵌套项类型的可变数组

格式:Create or replace 可变数组类型名 as Varray(最大行数) of 可变数组的基类型;

实例: Create Or Replace Type arrMingXiType as varray(100) of MingXiType;

C.创建一个主表

Create table inStockOrder

(

OrderID varchar(15) Not Null Primary Key,

InDate Date,

OperatorId varchar(10),

Mingxi arrMingXiType

);

2.操作可变数组

A.插入数据

Insert into InStockorder

Values('20020807001',To_data(2002-08-07','YYYY-MM-DD'),'002',

aarrMingXiType(MingXiType('S001',100,'100009),

MingXiType('S001',100,'100009)))

b.查询数据

使用普通的select不能显示Varray中的数据.要使用带有游标的PL/SQL块来操纵Varray.

但可以使用Table()函数查询集合列

select * from Table(select t.Mingxt from instockorder t where t.orderid='20020807001')

c.修改数据

实例:把编号为20020807001的入库单的货物编号为S001的入库数量改为200

Update instockorder

set MingXi=arrMingXiType(MingXiType('S001',200,'10009'),MingXiType('T002',400,'10003')) where orderID='200208070001'

注意: 不能更新VArray中的单个元素,必须更新整个的Varray.

创建嵌套表

A 创建嵌套表基类型

格式: Create or replace type 嵌套表基类型名 as object

实例: Create or replace type MingXiType As Object

(

GoodsId varchar(15),

InCount int,

ProviderID varchar(10)

) not final;

B.创建嵌套表类型

格式: Create Type 嵌套表类型名 As Table of 类型名;

实例: Create Or Type nestMingXiType As Tabele of MingXiType;

C.创建主表,其中一个列是嵌套表的类型的

格式: Create Table 表名

(

字段1 类型及长度.....字段N 类型及长度,

嵌套类型字段嵌套表类型名

)

Nested Table 嵌套类型字段名 Store As 真正存储的嵌套表;

实例:(创建入库表)

Create Table InStockTable

(

OrderID varchar(13) not null Primary Key,

InDate Date,

Operator varchar(12),

MingXi nestMingXiType

) Nested Table MingXi Store As MingxiTable;

2.操纵嵌套表

A.向嵌套中插入记录

格式: Insert Into 主表名[(字段列表)]

Values(主表字段1值,.....主表字段N的值,

嵌套表类型名(嵌套表类型的基类型名(属性1的值,....属性N的值)

)

);

实例: Insert Into InStockTable

Values('200208060001',To_Date('2002-08-06','YYYY-MM-DD'),'3001',

nestMingXitableType(MingXiType('J0001',200,'1001'),

MingXiType('S0001',1000,'1002'),

MingXiType('T0005',500,'1003')

)

B.更新嵌套表中的数据

Update Table(select t.mingxi from instocktable t where orderid='200208060001') mx

set mx.incount=500

where mx.goodsid='j0001';

c.删除嵌套中的数据

delete from

table (select t.mingxi from instocktable t where orderid='200208060001') t

where t.goodsid='10001'

创建对象表

Create Table ObjectTable1 of MingxiType;

插入对象记录

Insert into objectTable1 values('3002',3000,'s001');

查询对象表中的OID

select ref(a) from objecttable1 a;

1.Ref 函数使用

--创建科室类型

Create or replace Type officeType As Object

(

ID varchar(10),

TypeName varchar(10)

);

--创建对应的对象表-- 科室表

Create table office of officeType;

--向对象表中插入记录

Insert into office Values('0001','财务科');

Insert into office Values('0002','人士科');

Insert into office Values('0003','伙食科');

Insert into office Values('0004','后勤科');

--使用Ref函数查看行对象的OID值以及表中的科室编号,名次

格式:select Ref(表别名) From 对象表对象表的别名;

实例:

select Ref(f) ,ID,TypeName from Office f; -- 将表的别名作为输入

结果: 对象的OID编号, 类型名

2.Ref类型

Ref类型的使用:通过REf和DEref运算符,可以将OID用于创建外键关系.

--创建具有外键的关系表--人事表

Create Table Worker

( WorkerID varchar(10) primary key,

WorkerName varchar(10),

Workeroffice ref officeType Scope Is Office, --引用officetype外键.关联的是OID值

phone varchar(16)

);

--向表中插入数据,此表将从上面创建的对象表中引用数据

Insert into worker select 'C001','张小明',Ref(0),'010-1234567' from office 0

where ID='001'

3.使用Deref查看OID指向的行中的数据

格式: select Deref(表别名.引用类型列名) from 表名表的别名

实例: select workerID,workerName,DeRef(w.workerOffice),phone from worker w

where workerID='C001'

4.Value函数;

--使用value()返回表中的对象

select Value(0) from office 0;

5.对象试图

Create view officeview of officetype with object oid(id)

as select * from office;

6.创建对象试图

A.创建基于关系表父表的对象类型.

create or replace type depttype as object

(

deptno number(2),

dName varchar2(14),

loc varchar(13)

);

--创建基于关系表的视图(dept)

create view deptview of depttype with object oid(deptno) as select * from dept;

B.创建引用视图(类似于关系创建一个从表)

Create view emp_view as select make_ref(deptview,deptno) deptOid,empno,ename from emp;

--对象视图实际上是将关系包装成对象表,通过构造关系表每条记录OID来实现类似于关系表 --主外键约束.

三.动态游标

declare

type refEmpcur is ref cursor;

empcur refempcur

Emprow emp%rowtype;

flag int:=0;

begin

flage:=&flage;

if flag=0 then

Open Empcur for select * from emp where sal>500 and sal<1000;

elseif flag=1 then

open Empcur for select * from emp where sal>=1000;

else

open Empcur for select * from emp;

end if;

/* 由于For循环会自动的打开游标,所以REF游标不能使用

for Emprow int Empcur loop

DBMS_output.put_line('empno'||emprow.empno);

end loop; */

loop

Fetch empcur into emprow;

DBMS_output.put_line('empno'||emprow.empno);

wxit when empcur%notfound;

end loop;

end;

过程的使用

A.结构

--说明部分

Create or replace procedure 过程名

/* 声明部分*/

begin

/*声明部分*/

exception

/*异常处理部分*/

end [过程名];

//注:(过程是有名的程序块,as代替了Declare声明关键词)

B.格式

Create or replace procedure 过程名

[(参数1 in|out|in out 类型,参数2 in|out| int out 类型,.....参数N in|out|in out 类型)]

{is|as}

过程体

四.程序包

A.包说明及主体

格式:Create [or Replace] package 包名 IS|AS

Pragma restrict_references(函数名,WNDS[,WNPS][,RNDS][,RNDS])

end [包名];

B.格式:

Create [or replace] package body 包名 IS|AS

/*包体名一定要已经定义的包名同*/

end [包体名];

C.包调用

包名.类型名;

包名.函数名[参数表];

包名..过程名[参数表];

显示绑定到引用游标变量上的值----包调用

set Autoprint on

1.variable tempCursor;

2.exec Studentpackage.returnstudent(:tempCur);

D.数据字典

User Objects ,User_source

F.包的修改和删除

Drop Package [body] 包名;

Drop Package Body StudetPackage;

包的实例:

包的声明部分

Create or replace package Studentpackage

type curRefstudent is ref Cursor return student%rowtype;

Procedure selectstudent(findID in strudent.stuid%type)

Procedure Insertstudent(Newstudent in student%type);

Procedure Updatestudent(Newstrdeng in student%rowtype);

procedure deletestudent(DelID in student.stuid%Type);

Procedure Returnstudent(inOutstu in out Currefstudeng);

function ReturnRecordCount return Number;

end strudentpackage;

创建包的主体部分实例:

Create or replace Package body studentPackage as

procedure selectstudent(findID in student.stuid%type) as

Cursor findcur is select * from student where stuid=findid;

begin

for s in findcur loop

DBMS_output.put_line(s.stuid||' '||s.stuName||' '||s.sex);

end loop;

Exception

when No_date_found then

DBMS_output.put_Line('没有查到ID为'||findid||'的记录!');

when others then

DBMS_OutPut.put_Line('查询过程中发生意外情况');

end selectstudent;

Procedure Insertstudent(newstudent in student%RowType) AS

iRec Integer;

Not_Exits_student Exception;

begin

select count(*) into iRec from Student where stuid=newstudent.stuid;

if iRec>0 then

Raise not_exists_studeng;

else

Insert into Studeng valuew(newStudeng.stuid,newstudeng.stuName,newstudeng.sex);

commit;

end if;

Exception

when Not_Exists_Studeng Then

DBMS_Output.put_line('要插入的编号为:'||NewStudent.stuid||'的记录已经存在');\

when others then

DBMS_OUtput.put_Line('插入记录操纵过程中出现错误');

end InsertStudent;

............................... --中间其他存储过程的实现省略

//引用游标的使用.

Procedure Returnstudent(inOutStu in out curRefStudent) as

begin

Open inOutStu For select * from student;

end Returnstudent;

end studentPackage ;

程序包的调用

1.调用Studentpackage中的InsertStudent过程

Declare

newStu Student%RowType;

begin

Newstu.stuid:='1001';

newstu.stuName:='马大哈';

Studentpackage.insertstudent(newstu);

end;

Declare

newStu Student%RowType;

begin

newstu.stuid:='1001';

newstu.stuname:='李连杰';

newstu.sex:='男';

Studentpackage.Updatestudent(newstu);

Exception

when Dup_Val_On_Index then

DBMS_OUTPUT.put_Line('唯一约束被破坏');

when Others then

DBMS_OUTPUT.PUT_Line('更新过程出现错误');

end;

begin

StudentPackage.DeleteStudent('888');

end;

begin

DBMS_OUTPUT.PUT_line(StudentPackage.ReturnRecordCount);

end;

5.调用引用游标

set Autoprint on

Variable tempcur refcursor;

exec Studentpackage.returnstudent(:tempcur);

函数级别的使用

Create table test(a int);

Create or replace package Mypack

procedure UpdateTable(s Int);

Pragma restrict_references(Updatetable,Wnds);

end Mypack;

Create or replace package body Mypack

procedure Updatetable(s Int) is

begin

Update test set a=s;

end;

触发器的使用:

1.触发器具有三个部分

(1)触发事件

(2)可选的触发器约束条件;

(3)触发器动作

2.可以创建对应如下语句所触发的触发器;

(1)DML语句(Insert Delete UPdate);

(2)DDL语句(create Alter Drop)

(3)数据库操纵(serverError logon logoff Startup Shutdown)

3.可创建触发器的对象

(1) 数据库表

(2)数据库视图

(3)用户模式

(4)数据库实例

4.触发器类型

(1) DML触发器

(2)系统触发器

(3)替代(Instead of) 触发器

5.执行DML语句的顺序

(1)执行Before语句级触发器(如果有);

(2)对于受语句影响的每一行,执行DML语句;

(3)执行After语句级触发器(如果有)

6.两个特殊值 :New 新值 :Old旧值

7.触发器谓词

(1) Inserting

(2) Updating

(3) Deleting

二,创建DML触发器

Create [or Replace] Trigger [模式.]触发器名

before | After Insert| Delete |Update Of 列名

on 表名

[For Each Row]

when

pl/sql 块

1.使用Before触发器(new)

Create or replace Trigger tg_student

before insert on Student

for each row

when (new.sex='f')

(一)PL/SQL索引表

一PL/SQL表

1.定义表类型

Type 表类型名 Is Table of 列类型 | 变量数据类型[非空] index By Binary_Integer;

注:

A 表类型名: 是表类型定义的名称(像记录类型一样)

B列类型:可以是char,Date,Number等任何标量的数据类型

C变量数据类型: 可以是%Type或%Rowtype,Record等类型

实例:

Type stuNametableType is Table of varchar(10) index By Binary_Integer;

Type StudentTableType is Table of Student%RowType Index By Binary_Integer;

2.声明PL/SQL变量

PL/SQL变量 PL/SQL类型

Stunametable stuNameTableType;

3.访问plsql变量/删除plsql变量元素

变量名(索引下标)

Declare

Type stuNametableType is TAble of varhcar(10) Index By Binary_Integer;

stuNametable stuNameTableType;

begin

stunametable(2):='10';

DBMS_Output.put_line('stunametable'||stunametable(2));

stunametable(6):='10';

DBMS_Output.put_line('stunametable'||stunametable(6));

stunametable.delete(6);

DBMS_Output.put_line('stunametable'||stunametable(6));

end;

二.记录类型

第一步声明记录类型

1.格式

Type 记录类型名 is Record

(字段1 类型| 变量%Type| 表. 字段名%Type|表%RowType [Not Null[:=表达式1],

字段n 类型| 变量%Type|表. 字段名%Type|表%RowType[Not null[:=表达式n]);

第二步声明记录类型变量

变量记录类型

赋值方法

变量名.列名

Declare

Type stuRecordType is Record

( ID Student.StuID%Type,

Name Student.StuName%Type,

Sex Student.Sex%Type);

stuRec StuRecordType; --定义记录类型变量

Begin

Select StuID,StuName,Sex Into StuRec From Student

where StuID='1001';

DBMS_Output.put_line(stuRec.ID||''||stuRec.Name||''||stuRec.Sex);

Exception

when No_Data_Found then

DBMS_OutPut.put_Line('没有数据查询到!);

end;

三.嵌套表与可变数组(集合类型)

1.声明类型 Type Table_Type is table of Type

2.定义变量及初始化 Var_Table Table_Type:=Table_type(1,3,2);

3.实例

Declare

type myTabletype is table of int;

vartable mytabletype :=mytabletype(1,2,3,4.5,6,7,8,9);

begin

/*vartable(1):10;*/

for i in 1..9 loop

DBMS_Output.Put_line('ss'||vartable(i));

end loop;

end;

4.嵌套表与PL/sql表的区别

四.集合方法

五.批量绑定

当集合参与数据存取时,为提高效率而采用的方法

Bulk collect 子句

注 Bulk collect 可以用于select Into 子句中

也可以用于游标Fetch Bulk collect int 子句中

六,成员函数和方法

Accessof 和multator

注意member关键字的用法.

七.定义对象类型

1.定义对象类型的说明部分

Create or replace type Persontype as object

(

Id Int,

Name varchar(20),

Member function GetId Return int,

Member procedure setID(pid int),

Member function GetName return varchar,

Member procedure SetName(Pname vharchar)

) not final;

2.实现对象的主体部分

Crate or replace type body Persontype as

member function getid return int

begin

Return id;

end getid;

member procedure setid(pid int) as

begin

id:=Pid;

end setid;

member function getName return varchar as

begin

return Name;

end Getname;

member procedure setName(Pname varchar) is

begin

Name:=Pname;

end setName;

end;

3.对象的使用

Declare

p persontype;

begin

p:=Persontype(1001,'tom');

DBMS_output.put_line(p.id);

DBMS_output.put_line(p.getid);

DbMS_output.put_line(p.getName);

p.setID(2001);

p.setName('Mike');

end;

4.定义对象表

Create table persontable of persontype;

5.向对象表中插入数据

Insert into persontable values(2001,'Mike');

insert into persontable values(persontype(2002,'peter'));

6.查询对象表中的数据

select ref(o) from persontable o

select value(o) from persontable o;

select p.getid(),p.getname() from persontable p where p.id=2001

weixin_39814126

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle not in一个变量,Oracle基本语法

select a.uiblockid,a.uilac_id,cast(a.subsum*1.0/b.totalsum as numeric (10, 2) ) BL from(select sum(uinmsg_lac) subsum,uilac_id,uiblockid fromT_msg_lac group by uilac_id,uiblockid) a,(select sum(uinmsg...
复制链接

扫一扫