oracle存储过程知识点.docx

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（