Oracle里的一些基本语法.docx
《Oracle里的一些基本语法.docx》由会员分享，可在线阅读，更多相关《Oracle里的一些基本语法.docx（29页珍藏版）》请在冰豆网上搜索。
Oracle里的一些基本语法
Oracle里的一些基本语法
1.连接
---内连接
select*fromdali.test1a,dali.test2bwherea.a=b.a;
---左连接
select*fromdali.test1a,dali.test2bwherea.a=b.a（+）;
---右连接
select*fromdali.test1a,dali.test2bwherea.a（+）=b.a;
---完全连接
select*fromdali.test1a,dali.test2bwherea.a=b.a（+）
union
select*fromdali.test1a,dali.test2bwherea.a（+）=b.a;
---迪卡尔
select*fromdali.test1,dali.test2;
判断是否为空：
在SQlServer中为ISNULL（field1,0）
在Oracle中为NVL（field1,0）
一、基础与概念
1、PL/SQL不区分大小写，除非是由引号引起来的字符串。
2、PL/SQL标识符的命名规则
标识符的最大长度是30个字符，包括字母,数字,$,_,#
不可包含保留字
要以字来打头
不能和同一块中的表中的字段名一样
3、命名规范
sql*plussubstitutionvariablep_name
variablev_name
constantc_name
sql*plusglobalvariableg_name
exceptione_name
4、注销方式
--注释内容
/*……注释内容………….*/
5、只要表达式里有NULL，则整个表达式的值为NULL
BOOLEAN和NULL
这里要注意的是：
NULLANDTRUE==NULL
NULLANDFALSE==FALSE
NULLORTRUE==TRUE
NULLORFALSE==NULL
6、PL/SQLEnvironment
PL/SQLBlcok-----mon-sql-----proceduralstatementexecutor
-----sql------------sqlstatementexecutor
7、分类
procedures：
执行一个动作,做为一个pl/sql来执行，可以返回一个值
function：
计算一个值,用于嵌入到表达式中，并必须返回一个值
package：
把函数和过程逻辑的关联起来
8、pl/sqlblockstructure
header:
Containsthesubprogramname,type,andarguments.Onlyusedforsubprograms.
Declarative:
Containsthelocalidentifiersfortheblock.
Executable:
ContainstheSQLstatementsandPL/SQLcontrolstatements.
Exception:
Performsactionswhenerrorsoccur.
9、DCL和DDL都不被pl/sql支持。
DML和commit可以被支持.
10、一个嵌套块成为封闭块里的一个可执行语句，一个块可嵌套在任何允许放置可执行语句的地方，包括执行部分和异常处理部分。
11、PL/SQL表达式不能包含组函数，但一个PL/SQL块里的SQL语句可以。
12、当一个语句中有混合的数据类型时，PL/SQL可以动态转变。
如：
想把一个NUMBER值存进一个VARCHAR2的变量里，PL/SQL会动态地把NUMBER值转变为VARCHAR2类型的字符值。
13、PL/SQL表达式可以包含SQL函数。
14、嵌套块里的语句不能包含一个异常段。
15、PL/SQL中的SELECT语句必须使用INTO子句。
二、数据类型：
1）BaseScalarDatatypes•
ØVARCHAR2：
在PL/SQL中可以存储2000，在oracle8中是4000字节
ØNUMBER[（precision,scale）]：
当声明时。
默认值是null
ØDATE:
存储日期型
ØCHAR[（maximum_length）]:
如没有指定，数据类型为CHAR的列默认长度为1。
这个长度的范围是1到2000。
ØLONG:
用于长度不定，最大值为2G的字符型数据。
ØRow用来存二进制，在PL/SQL中最大长度是32767，在oracle8中是255字节
ØLONGRAW：
可以容纳rawbinarydata
ØBOOLEAN:
只能为trueorfalseornull
ØBINARY_INTEGER
ØPLS_INTEGER
2）CompositeDatatypes
ØPL/SQLTABLES:
PrimaryKey：
KEY的类型是BINARY_INTEGER
VALUE：
真正的值
声明：
TYPEtype_nameISTABLEOF
{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]
[INDEXBYBINARY_INTEGER];
identifiertype_name;
eg1:
TYPEt_nametableISTABLEOFVARCHAR
（2）
INDEXBYBINARY_INTEGER;
引用：
t_nametable
（1）:
=’jact’
eg2:
DECLARE
TYPEdept_table_typeISTABLEOFdept%ROWTYPE
INDEXBYBINARY_INTEGER;
dept_tabledept_table_type;
引用：
dept_table.row1
（1）:
=’jact’;
ØPL/SQLRECORDS
定义:
TYPEtype_nameISRECORD
（field_declaration[,field_declaration]…）;
identifiertype_name;
TYPErecord_nameISRECORD（
namevarchar（20）,
idnumber（9）
）
引用：
studentrecord_name
student.name
student.id
3）3）Lob:
用来存二进制，最大长度是4GB
CLOB:
RECIPE
BLOB:
PHOTO（可以容纳4G的二进制）
BFILE:
MOVIE把二进制存在一个扩展的文件中。
容量是4G
NCLOB
三、PL/SQLBlockStructure
DECLARE---Optional
-Variables,Constants,cursors,user-definedexceptions
BEGIN---Mandatory
-SQLstatements
-PL/SQLcontrolstatements
EXCEPTION-Optional
-Actionstoperformwhenerrorsoccur
END:
---Mandatory
Blocktype
Anonymous:
[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Procedure:
PROCEDUREnameIS
[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Function:
FUNTIONname
RETURNdatatypeIS
[DECLARE]
BEGIN
……..
RETURNvalue;
[EXCEPTION]
END;
四、变量
1、变量的赋值：
identifier[CONSTANT]datatype[NOTNULL][:
=|DEFAULTexpr];
2、变量名number（9,2）NOTNULL:
=0;//在声明一个PL/SQL参数时，可以使用赋值运算符：
=为该参数赋初值。
如果没有为参数赋初值，参数会被设置为null。
如果参数被定义NOTNULL约束，那么就必须赋初值。
3、变量的类型：
变量名number（9,2）;
变量名saray%type;//这样value的类型就和saray一样，也可以用列名取代saray
变量名parts%rowtype
//parts是表名，这是个记录类型的变量。
其内部结构和parts一样
是根据表或视图的列来定义各变量
用表名做为前缀
结构和表的结构一样
4、在PL/SQL调用sqlplus的变量，在变量前面加”：
”
:
g_monthly_sal:
=v_sal/12;
五、控制程序流
1、loop
程序段
exit[when表达式]
endloop;
可以为循环设定标签
EG：
BEGIN
<>
LOOP
v_counter:
=v_counter+1;
EXITWHENv_counter>10;
<>
LOOP
...
EXITOuter_loopWHENtotal_done='YES';
--Leavebothloops
EXITWHENinner_done='YES';
--Leaveinnerlooponly
...
ENDLOOPInner_loop;
...
ENDLOOPOuter_loop;
END;
2、while表达式loop
程序段
endloop;
3、forIin1..20loop
程序段
endloop;
4、if[not]…..then
程序段
elsif….then
程序段
else
程序段
endif;
5、message1
<>
a:
=a+1
注意：
不可以同外层跳到里层
不可以从一个IF子句跳到另一个IF子句
不可以从异常处理块跳到当前块
6、selectinto为变量赋值
selectnameintov_namewhere…
六、游标
游标的本质是SQL语句的一个工作区域，用于处理多行记录集的查询。
分类：
Implicitcursors：
由DML和PL/SQL的SELECT隐式的定义，不可以使用open,fetch和close去控制这个sql游标。
每一次只处理一行。
但是可以使用游标的属性
Explicitcursors：
由程序员定义,显式游标用于返回多于一行的查询
以下讨论的都是显式游标
执行的四个步骤：
1、声明：
定义游标的名字和结构，select中可以使用orderby
2、打开游标：
执行查询同时绑定所有涉及到的变量
执行的内容：
为select分配内存并分析select语句
绑定输入的变量
配置指针在活动集的第一行
注意：
如果查询不返回结果，不会引发PL/SQL的异常，你可以在执行fetch后测试返回的结果
如果游标内的声明包括update。
一样会执行行锁定
3、Fetch：
把当前行的值赋给变量，每个fetch都会把游标指针向下移动一行。
如果到了最后一行就会自动退出forloop
4、关闭：
释放活动的集，可以再次使用open
游标的几个属性：
SQL%ROWCOUNT受最近执行的SQL语句影响的行的数目。
（一个整数值）
SQL%FOUNDBoolean属性，如果最近的SQL语句影响了一行或多行，其值为
TRUE。
SQL%NOTFOUNDBoolean属性，如果最近的SQL语句没有影响任何行，其值为
TRUE。
SQL%ISOPEN总是为FALSE，原因是PL/SQL总是它们结束执行后立即关闭内隐游标。
例子
EG1:
常规用法
CURSORc1IS
SELECTempno,ename
FROMemp;
emp_recordc1%ROWTYPE;
BEGIN
OPENc1;
...
FETCHc1INTOemp_record;
EG2:
使用for循环实现游标
DECLARE
CURSORc1IS
SELECTempno,ename
FROMemp;
emp_recordc1%ROWTYPE;
BEGIN
FORemp_recordINc1LOOP
--implicitopenandimplicitfetchoccur
IFemp_record.empno=7839THEN
...
ENDLOOP;--implicitcloseoccurs
END;
游标FOR循环立时不需要FETCH语句的。
游标打开，在循环中每次重复提取一行，所有的行都处理后，游标会自动关闭。
EG3:
不定义游标的方式
BEGIN
FORemp_recordIN（SELECTempno,ename
FROMemp）LOOP
--implicitopenandimplicitfetchoccur
IFemp_record.empno=7839THEN
...
ENDLOOP;--implicitcloseoccurs
END;
EG4:
带变量的游标------你必须指定指定参数的数据类型，但不用指定大小
DECLARE
CURSORc1
（v_deptnoNUMBER,v_jobVARCHAR2）IS
SELECTempno,ename
FROMemp
WHEREdeptno=v_deptno
ANDjob=v_job;
BEGIN
OPENc1（10,'CLERK'）;
...
EG5:
FORUPDATE----当加上forupdate则把整个表或字段锁住了。
SELECT...FROM...
FORUPDATE[OFcolumn_reference][NOWAIT]
DECLARE
CURSORc1IS
SELECTempno,ename
FROMemp
FORUPDATENOWAIT;
NOWAIT:
返回一个oracle的错误信息如果此行给其他的会话锁住了。
EG6：
WHERECURRENTOF
用于在游标中删除和更新当前行
必须使用FORUPDATE去锁住行
使用WHERECURRENTOF去指向当前的行
DECLARE
CURSORc1IS
SELECT...
FORUPDATENOWAIT;
BEGIN
...
FORemp_recordINc1LOOP
UPDATE...
WHERECURRENTOFc1;
...
ENDLOOP;
COMMIT;
END;
七、处理异常
PredefinedException
BEGINSELECT...COMMIT;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
statement1;
statement2;
WHENTOO_MANY_ROWSTHEN
statement1;
WHENOTHERSTHEN
statement1;
statement2;
statement3;
END;
…….
Non-PredefinedException
DECLARE
e_products_invalidEXCEPTION;
PRAGMAEXCEPTION_INIT（
e_products_invalid,-2292）;
v_messageVARCHAR2（50）;
BEGIN
...
EXCEPTION
WHENe_products_invalidTHEN
:
g_message:
='Productcode
specifiedisnotvalid.';
...
END;
User-DefinedException
DECLARE
e_amount_remainingEXCEPTION;
...
BEGIN
...
RAISEe_amount_remaining;
...
EXCEPTION
WHENe_amount_remainingTHEN
:
g_message:
='Thereisstillanamount
instock.';
...
END;
RAISE_APPLICATION_ERROR
DECLARE
…….
InvalidpartEXCEPTION;
BEGIN
…….
IFSQL%NOTFOUNDTHEN
RAISEinvalidpart;
ENDIF;
EXCEPTION
WHENinvalidpartTHEN
Raise_application_error（-20003,’InvalidPartid#’||partnum）;
WHENOTHERSTHEN
Raise_application_error（-20000,errNum||errMsg）;
END
²使用EXCEPTION关键字在一个PL/SQL块的声明部分声明用户自定义异常
²使用PL/SQL命令RAISE检测用户自定义异常
²PL/SQL可使用Raise_application_error过程返回一个用户自定义错误数和消息给调用环境。
所有的用户自定义错误消息必须在-20000到-20999之间
²PL/SQL程序可以使用WHENOTHERS异常处理来处理没有特定处理的所有异常
²PL/SQL程序可以使用特殊的SQLCODE和SQLERRM函数返回最新的oracle错误号码和消息
DECLARE
v_error_codeNUMBER;
v_error_messageVARCHAR2（255）;
BEGIN
...
EXCEPTION
...
WHENOTHERSTHEN
ROLLBACK;
v_error_code:
=SQLCODE;
v_error_message:
=SQLERRM;
INSERTINTOerrorsVALUES（v_error_code,
v_error_message）;
END;
数据库联接
定义：
CREATEDATABASELINKlink_name
CONNECTTOusernameIDENTIFIEDBYpassword
USINGsqlnet_string;
使用：
表名@link_name
替代名
CREATESYNONYMsynonym_nameFORreference;
Eg：
CREATESYNONYMbackupFORbackup@esal
一、关系数据库的一些概念
1、主键的值一般不可以改变
2、外键：
指向另一个表或本表的主键或唯一键的字段。
外键的值一定要和某一主键相同，或者为空。
3、数据库对像：
表，视图，序列，索引，同义词，程序（进程，函数，sql和pl/sql数据）4、sqlcommand类别
dataretrieval数据检索:
select
datamanipulationlanguage（DML）数据操作语言:
insert,update,delete
datadefinitionlanguage（DDL）数据定义语言：
create,alter,drop,rename,truncate
transactioncontrol事务控制:
commit,rollback,savepoint
datacontrollanguage（DCL）数据控制语言：
grant,revoke
DCL和DDL命令的执行会导致一次隐式提交，之前未提交的操作（包括DML命令）都会提交写入日志文件，并在适当地时候写入数据文件。
二、SQL的语法
1）连接号：
||
2）把两个字符连接起来
eg:
selectgame_card_type_id||namefromgame_card_type;
3）selectdistinctdept_id,titlefromemp：
对多个字段的唯一
4）orderbydesc（降序）
orderbyasc（升序）
5）wherecolumnis（not）null
6）like‘_a%’_表示一个字符。
％表示多少字符
like‘%x\_y%’escape‘\’:
显示包括x_y的字符
7）wheretable1.column（+）=table2.column
placetheoperatoronthesideofthejoinwherethereisnovaluetojointo.
8）联接类型:
equijoin:
等式查询
non_equijoin:
不等式查询
self:
自己和自己建立关联
outjoin:
wherea.column=b.column（+）
可以用的操作符是：
’=’，’and’，不可以用’or’，’in’
9）COUNT函数所用的列包含空值时，空值行被忽略。
10）where后的inanyall的区别
in：
等于子查询的任何一个数
any：
与子查询的每一个值相比
只要比其中一个大（小）就可以了
all:
与子查询的所有值相比要比所有的的都大（小）
!
=ALL作用跟NOTIN一样
三、SQL*PLUS的环境（可以在glogin.sql中定义初始参数）
1）START命令用以执行一个已储存的文件,等同于@
升级会员 