oracle存储过程知识点.docx
《oracle存储过程知识点.docx》由会员分享，可在线阅读，更多相关《oracle存储过程知识点.docx（22页珍藏版）》请在冰豆网上搜索。
oracle存储过程知识点
（1）SEQNAME.NEXTVAL里面的值如何读出来？
可以直接在insertintotestvalues（SEQNAME.NEXTVAL）是可以用这样：
SELECTtmp#_seq.NEXTVAL
INTOid_temp
FROMDUAL;然后可以用id_temp
（2）PLS-00103:
出现符号">"在需要下列之一时：
代码如下：
IF（sum>0）
THEN
begin
INSERTINTOemesp.tp_sn_PRoduction_log
VALUES（r_serial_number,,id_temp）;
EXIT;
end;
一直报sum>0这是个很郁闷的问题因为变量用了sum所以不行，后改为i_sum>0
（3）Oracle语法
1.Oracle应用编辑方法概览
答：
1）Pro*C/C++/...:
C语言和数据库打交道的方法，比OCI更常用;
2）ODBC
3）OCI:
C语言和数据库打交道的方法，和ProC很相似，更底层，很少用;
4）SQLJ:
很新的一种用java访问Oracle数据库的方法，会的人不多;
5）JDBC
6）PL/SQL:
存储在数据内运行,其他方法为在数据库外对数据库访问;
2.PL/SQL
答：
1）PL/SQL（Proceduallanguage/SQL）是在标准SQL的基础上增加了过程化处理的语言;
2）Oracle客户端工具访问Oracle服务器的操作语言;
3）Oracle对SQL的扩充;
4.PL/SQL的优缺点
答：
优点：
1）结构化模块化编程，不是面向对象;
2）良好的可移植性（不管Oracle运行在何种操作系统）;
3）良好的可维护性（编译通过后存储在数据库里）;
4）提升系统性能;
第二章
PL/SQL程序结构
1.PL/SQL块
答：
1）申明部分,DECLARE（不可少）;2）执行部分,BEGIN...END;
3）异常处理，EXCEPTION（可以没有）;
2.PL/SQL开发环境
答：
可以运用任何纯文本的编辑器编辑，例如：
VI；toad很好用
3.PL/SQL字符集
答：
PL/SQL对大小写不敏感
4.标识符命名规则
答：
1）字母开头;
2）后跟任意的非空格字符、数字、货币符号、下划线、或#;
3）最大长度为30个字符（八个字符左右最合适）;
5.变量声明
答：
语法
Var_nametype[CONSTANT][NOTNULL][:
=value];
注：
1）申明时可以有默认值也可以没有;
2）如有[CONSTANT][NOTNULL],变量一定要有一个初始值;
3）赋值语句为“:
=”;
4）变量可以认为是数据库里一个字段;
5）规定没有初始化的变量为NULL;
第三章
1.数据类型
答：
1）标量型：
数字型、字符型、布尔型、日期型;
2）组合型：
RECORD（常用）、TABLE（常用）、VARRAY（较少用）
3）参考型：
REFCURSOR（游标）、REFobject_type
4）LOB（LargeObject）
2.%TYPE
答：
变量具有与数据库的表中某一字段相同的类型
例：
v_FirstNamestudengts.first_name%TYPE;
3.RECORD类型
答：
TYPErecord_nameISRECORD（/*其中TYPE，IS，RECORD为关键字，record_name为变量名称*/
field1type[NOTNULL][:
=expr1],/*每个等价的成员间用逗号分隔*/
field2type[NOTNULL][:
=expr2],/*如果一个字段限定NOTNULL，那么它必须拥有一个初始值*/
.../*所有没有初始化的字段都会初始为NULL
fieldntype[NOTNULL][:
=exprn]）;
4.%ROWTYPE
答：
返回一个基于数据库定义的类型
DECLARE
v_StuRecStudent%ROWTYPE;/*Student为表的名字*/
注：
与3中定一个record相比，一步就完成，而3中定义分二步：
a.所有的成员变量都要申明;b.实例化变量;
5.TABLE类型
答：
TYPEtabletypeISTABLEOFtypeINDEXBYBINARY_INTEGER;
例：
DECLARE
TYPEt_StuTableISTABLEOFStudent%ROWTYPEINDEXBYBINARY_INTERGER;
v_Studentt_StuTable;
BEGIN
SELECT*INTOv_Student（100）FROMStudentWHEREid=1001;
END;
注：
1）行的数目的限制由BINARY_INTEGER的范围决定;
6.变量的作用域和可见性
答：
1）执行块里可以嵌入执行块;
2）里层执行块的变量对外层不可见;
3）里层执行块对外层执行块变量的修改会影响外层块变量的值;
第四章
1.条件语句
答：
IFboolean_expression1THEN
...
ELSIFboolean_expression2THEN/*注意是ELSIF，而不是ELSEIF*/
.../*ELSE语句不是必须的，但ENDIF;是必须的*/
ELSE...
ENDIF;
2.循环语句
答：
1）Loop
..
IFboolean_exprTHEN/**/
EXIT;/*EXITWHENboolean_expr*/
ENDIF;/**/
ENDLOOP;
2）WHILEboolean_exprLOOP
...
ENDLOOP;
3）FORloop_counterIN[REVERSE]low_blound..high_boundLOOP
...
ENDLOOP;
注：
a.加上REVERSE表示递减，从结束边界到起始边界，递减步长为一;
b.low_blound起始边界;high_bound结束边界;
3.GOTO语句
答：
GOTOlabel_name;
1）只能由内部块跳往外部块;
2）设置标签：
<>
3）示例：
LOOP
...
IFD%ROWCOUNT=50THEN
GOTOl_close;
ENDIF;
...
ENDLOOP;
<>;
...
4.NULL语句
答：
在语句块中加空语句，用于补充语句的完整性。
示例：
IFboolean_exprTHEN
...
ELSE
NULL;
ENDIF;
5.SQLinPL/SQL
答：
1）只有DMLSQL可以直接在PL/SQL中使用;
第五章
1.游标（CURSOR）
答：
1）作用：
用于提取多行数据集;
2）声明：
a.普通申明：
DELCARECURSORCURSOR_NAMEISselect_statement/*CURSOR的内容必须是一条查询语句*/
b.带参数申明：
DELCARECURSORc_stu（p_idstudent.ID%TYPE）SELECT*FROMstudentWHEREID=p_id;
3）打开游标：
OPENCursor_name;/*相当于执行select语句，且把执行结果存入CURSOR;
4）从游标中取数：
a.FETCHcursor_nameINTOvar1,var2,...;/*变量的数量、类型、顺序要和Table中字段一致;*/
b.FETCHcursor_nameINTOrecord_var;
注：
将值从CURSOR取出放入变量中，每FETCH一次取一条记录;
5）关闭游标:
CLOSECursor_name;
注：
a.游标使用后应该关闭;
b.关闭后的游标不能FETCH和再次CLOSE;
c.关闭游标相当于将内存中CURSOR的内容清空;
2.游标的属性
答：
1）%FOUND:
是否有值;
2）%NOTFOUND:
是否没有值;
3）%ISOPEN:
是否是打开状态;
4）%ROWCOUNT:
CURSOR当前的记录号;
3.游标的FETCH循环
答：
1）LOOP
FETCHcursorINTO...
EXITWHENcursor%NOTFOUND;/*当cursor中没记录后退出*/
ENDLOOP;
2）WHILEcursor%FOUNDLOOP
FETCHcursorINTO...
ENDLOOP;
3）FORvarINcursorLOOP
FETCHcursorINTO...
ENDLOOP;
第六章
1.异常
答：
DECLARE
...
e_TooManyStudentsEXCEPTION;/*申明异常*/
...
BEGIN
...
RAISEe_TooManyStudents;/*触发异常*/
...
EXCEPTION
WHENe_TooManyStudentsTHEN/*触发异常*/
...
WHENOTHERSTHEN/*处理所有其他异常*/
...
END;
2004-9-8星期三阴
PL/SQL数据库编程（下）
1.存储过程（PROCEDURE）
答：
创建过程：
CREATE[ORREPLACE]PROCEDUREproc_name
[（arg_name[{IN|OUT|INOUT}]TYPE,
arg_name[{IN|OUT|INOUT}]TYPE）]
{IS|AS}
procedure_body
1）IN:
表示该参数不能被赋值（只能位于等号右边）;
2）OUT:
表示该参数只能被赋值（只能位于等号左边）;
3）INOUT:
表示该类型既能被赋值也能传值;
2.存储过程例子
答：
CREATEORREPLACEPROCEDUREModeTest（
p_InParmINNUMBER,
p_OutParmOUTNUMBER,
p_InOutParmINOUTNUMBER）
IS
v_LocalVarNUMBER;/*声明部分*/
BEGIN
v_LocalVar:
=p_InParm;/*执行部分*/
p_OutParm:
=7;
p_InOutParm:
=7;
...
EXCEPTION
.../*异常处理部分*/
ENDModeTest;
3.调用PROCEDURE的例子
答：
1）匿名块可以调;
2）其他PROCDEURE可以调用;
例：
DECLARE
v_var1NUMBER;
BEGIN
ModeTest（12,v_var1,10）;
END;
注：
此时v_var1等于7
4.指定实参的模式
答：
1）位置标示法：
调用时添入所有参数，实参与形参按顺序一一对应;
2）名字标示法：
调用时给出形参名字，并给出实参
ModeTest（p_InParm=>12,p_OutParm=>v_var1,p_Inout=>10）;
注：
a.两种方法可以混用;
b.混用时第一个参数必须通过位置来指定。
5.函数（Function）与过程（Procedure）的区别
答：
1）过程调用本身是一个PL/SQL语句（可以在命令行中通过exec语句直接调用）;
2）函数调用是表达式的一部分;
6.函数的声明
答：
CREATE[ORREPLACE]PROCEDUREproc_name
[（arg_name[{IN|OUT|INOUT}]TYPE,
arg_name[{IN|OUT|INOUT}]TYPE）]
RETURNTYPE
{IS|AS}
procedure_body
注：
1）没有返回语句的函数将是一个错误;
7.删除过程与函数
答：
DROPPROCEDUREproc_name;
DROPFUNCTIONfunc_name;
第八章
1.包
答：
1）包是可以将相关对象存储在一起的PL/SQL的结构;
2）包只能存储在数据库中，不能是本地的;
3）包是一个带有名字的声明;
4）相当于一个PL/SQL块的声明部分;
5）在块的声明部分出现的任何东西都能出现在包中;
6）包中可以包含过程、函数、游标与变量;
7）可以从其他PL/SQL块中引用包，包提供了可用于PL/SQL的全局变量。
8）包有包头和包主体，如包头中没有任何函数与过程，则包主体可以不需要。
2.包头
答：
1）包头包含了有关包的内容的信息，包头不含任何过程的代码。
2）语法：
CREATE[ORREPLACE]PACKAGEpack_name{IS|AS}
procedure_specification|function_specification|variable_declaration|type_definition|exception_declaration|cursor_declaration
ENDpack_name;
3）示例：
CREATEORREPLACEPACKAGEpak_testAS
PROCEDURERemoveStudent（p_StuIDINstudents.id%TYPE）;
TYPEt_StuIDTableISTABLEOFstudents.id%TYPEINDEXBYBINARY_INTEGER;
ENDpak_test;
3.包主体
答：
1）包主体是可选的，如包头中没有任何函数与过程，则包主体可以不需要。
2）包主体与包头存放在不同的数据字典中。
3）如包头编译不成功，包主体无法正确编译。
4）包主体包含了所有在包头中声明的所有过程与函数的代码。
5）示例：
CREATEORREPLACEPACKAGEBODYpak_testAS
PROCEDURERemoveStudent（p_StuIDINstudents.id%TYPE）IS
BEGIN
...
ENDRemoveStudent;
TYPEt_StuIDTableISTABLEOFstudents.id%TYPEINDEXBYBINARY_INTEGER;
ENDpak_test;
4.包的作用域
答：
1）在包外调用包中过程（需加包名）：
pak_test.AddStudent（100010,'CS',101）;
2）在包主体中可以直接使用包头中声明的对象和过程（不需加包名）;
5.包中子程序的重载
答：
1）同一个包中的过程与函数都可以重载;
2）相同的过程或函数名字，但参数不同;
6.包的初始化
答：
1）包存放在数据库中;
2）在第一次被调用的时候，包从数据库中调入内存并被初始化;
3）包中定义的所有变量都被分配内存;
4）每个会话都将拥有自己的包内变量的副本。
第九章
1.触发器
答：
1）触发器与过程/函数的相同点
a.都是带有名字的执行块;
b.都有声明、执行体和异常部分;
2）触发器与过程/函数的不同点
a.触发器必须存储在数据库中;
b.触发器自动执行;
2.创建触发器
答：
1）语法：
CREATE[ORREPLACE]TRIGGERtrigger_name
{BEFORE|AFTER}triggering_eventONtable_reference
[FOREACHROW[WHENtrigger_condition]]
trigger_body;
2）范例：
CREATEORREPLACETRIGGERUpdateMajorStatsAFTERINSERTORDELETEORUPDATEONstudents
DECLARE
CURSORc_StatisticsIS
SELECT*FROMstudentsGROUPBYmajor;
BEGIN
...
ENDUp;
3.触发器
答：
1）三个语句（INSERT/UPDATE/DELETE）;
2）二种类型（之前/之后）;
3）二种级别（row-level/statement-level）;
所以一共有3X2X2=12
4.触发器的限制
答：
1）不应该使用事务控制语句;
2）不能声明任何LONG或LONGRAW变量;
3）可以访问的表有限。
5.触发器的主体可以访问的表
答：
1）不可以读取或修改任何变化表（被DML语句正在修改的表）;
2）不可以读取或修改限制表（带有约束的表）的主键、唯一值、外键列。
（4）Java开发中使用Oracle的ORA-01000
很多朋友在Java开发中，使用Oracle数据库的时候，经常会碰到有ORA-01000:
maximumopencursorsexceeded.的错误。
实际上，这个错误的原因，主要还是代码问题引起的。
ora-01000:
maximumopencursorsexceeded.
表示已经达到一个进程打开的最大游标数。
这样的错误很容易出现在Java代码中的主要原因是：
Java代码在执行conn.createStatement（）和conn.prepareStatement（）的时候，实际上都是相当与在数据库中打开了一个cursor。
尤其是，如果你的createStatement和prepareStatement是在一个循环里面的话，就会非常容易出现这个问题。
因为游标一直在不停的打开，而且没有关闭。
一般来说，我们在写Java代码的时候，createStatement和prepareStatement都应该要放在循环外面，而且使用了这些Statment后，及时关闭。
最好是在执行了一次executeQuery、executeUpdate等之后，如果不需要使用结果集（ResultSet）的数据，就马上将Statment关闭。
对于出现ORA-01000错误这种情况，单纯的加大open_cursors并不是好办法，那只是治标不治本。
实际上，代码中的隐患并没有解除。
而且，绝大部分情况下，open_cursors只需要设置一个比较小的值，就足够使用了，除非有非常特别的要求。
（5）在storeprocedure中执行DDL语句
一是：
executeimmediate'update'||table_chan||'set'||column_changed||'='''||v_trans_name||'''whereempid='''||v_empid||'''';
二是：
TheDBMS_SQLpackagecanbeusedtoexecuteDDLstatementsdirectlyfromPL/SQL.
这是一个创建一个表的过程的例子。
该过程有两个参数：
表名和字段及其类型的列表。
CREATEORREPLACEPROCEDUREddlproc（tablenamevarchar2,colsvarchar2）AS
cursor1INTEGER;
BEGIN
cursor1:
=dbms_sql.open_cursor;
dbms_sql.parse（cursor1,'CREATETABLE'||tablename||'（'||cols||'）',dbms_sql.v7）;
dbms_sql.close_cursor（cursor1）;
end;
/
2如何找数据库表的主键字段的名称?
SQL>SELECT*FROMuser_constraints
WHERECONSTRAINT_TYPE='P'andtable_name='TABLE_NAME';
3如何查询数据库有多少表?
SQL>select*fromall_tables;
4使用sql统配符
通配符描述示例%包含零个或更多字符的任意字符串。
WHEREtitleLIKE'%computer%'将查找处于书名任意位置的包含单词computer的所有书名。
_（下划线）任何单个字符。
WHEREau_fnameLIKE'_ean'将查找以ean结尾的所有4个字母的名字（Dean、Sean等）。
[]指定范围（[a-f]）或集合（[abcdef]）中的任何单个字符。
WHEREau_lnameLIKE'[C-P]arsen'将查找以arsen结尾且以介于C与P之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen等。
[^]不属于指定范围（[a-f]）或集合（[abcdef]）的任何单个字符。
WHEREau_lnameLIKE'de[^l]%'将查找以de开始且其后的字母不为l的所有作者的姓氏。
5使普通用户有查看v$session的权限
GRANTSELECT
ON"SYS"."V_$OPEN_CURSOR"TO"SFISM4";
GRANTSELECT
ON"SYS"."V_$SESSION"TO"SFISM4";
常用函数
distinct
去掉重复的
minus相减
在第一个表但不在第二个表
SELECT*FROMFOOTBALLMINUSSELECT*FROMSOFTBALL;
intersect相交
INTERSECT返回两个表中共有的行。
SELECT*FROMFOOTBAL；
UNIONALL与UNION一样对表进行了合并但是它不去掉重复的记录。
汇总函数
count
selectcount（*）fromtest;
SUM
SUM就如同它的本意一样它返回某一列的所有数值的和。
SELECTSUM（SINGLES）TOTAL_SINGLESFROMTEST;
SUM只能处理数字如果它的处理目标不是数字你将会收到如下信息
输入/输出
SQL>SELECTSUM（
升级会员 