ORACLEPLSQL存储过程.docx

1、ORACLEPLSQL存储过程作者:梁宏林(1)SEQNAME.NEXTVAL里面的值如何读出来？可以直接在insert into test values(SEQNAME.NEXTVAL) 是可以用 这样： SELECT tmp#_seq.NEXTVAL INTO id_temp FROM DUAL; 然后可以用id_temp(2)PLS-00103: 出现符号 在需要下列之一时：代码如下： IF (sum0) THEN begin INSERT INTO emesp.tp_sn_production_log VALUES (r_serial_number, , id_temp); EXIT;

2、 end; 一直报sum0 这是个很郁闷的问题 因为变量用了sum 所以不行，后改为i_sum0(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(Procedual language/SQL)是在标

3、准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开发环境答：可以运用任何纯文本的编辑器编辑，

4、例如：VI ；toad很好用3. PL/SQL字符集答：PL/SQL对大小写不敏感4. 标识符命名规则答：1) 字母开头; 2) 后跟任意的非空格字符、数字、货币符号、下划线、或# ; 3) 最大长度为30个字符(八个字符左右最合适);5. 变量声明答：语法 Var_name type CONSTANTNOT NULL:=value; 注：1) 申明时可以有默认值也可以没有; 2) 如有CONSTANTNOT NULL, 变量一定要有一个初始值; 3) 赋值语句为“:=”; 4) 变量可以认为是数据库里一个字段; 5) 规定没有初始化的变量为NULL;第三章1. 数据类型答：1) 标量型：数字

5、型、字符型、布尔型、日期型; 2) 组合型：RECORD(常用)、TABLE(常用)、VARRAY(较少用) 3) 参考型：REF CURSOR(游标)、REF object_type 4) LOB(Large Object)2. %TYPE答：变量具有与数据库的表中某一字段相同的类型 例：v_FirstName studengts.first_name%TYPE;3. RECORD类型答：TYPE record_name IS RECORD( /*其中TYPE，IS，RECORD为关键字，record_name为变量名称*/field1 type NOT NULL:=expr1, /*每个等

6、价的成员间用逗号分隔*/field2 type NOT NULL:=expr2, /*如果一个字段限定NOT NULL，那么它必须拥有一个初始值*/ . /*所有没有初始化的字段都会初始为NULLfieldn type NOT NULL:=exprn);4. %ROWTYPE答：返回一个基于数据库定义的类型 DECLARE v_StuRec Student%ROWTYPE; /*Student为表的名字*/ 注：与3中定一个record相比，一步就完成，而3中定义分二步：a. 所有的成员变量都要申明; b. 实例化变量;5. TABLE类型答：TYPE tabletype IS TABLE O

7、F type INDEX BY BINARY_INTEGER; 例：DECLARE TYPE t_StuTable IS TABLE OF Student%ROWTYPE INDEX BY BINARY_INTERGER; v_Student t_StuTable;BEGIN SELECT * INTO v_Student(100) FROM Student WHERE id = 1001; END; 注：1) 行的数目的限制由BINARY_INTEGER的范围决定;6. 变量的作用域和可见性答：1) 执行块里可以嵌入执行块; 2) 里层执行块的变量对外层不可见; 3) 里层执行块对外层执行块

8、变量的修改会影响外层块变量的值;第四章1. 条件语句答：IF boolean_expression1 THEN . ELSIF boolean_expression2 THEN /*注意是ELSIF，而不是ELSEIF*/ . /*ELSE语句不是必须的，但END IF;是必须的*/ ELSE . END IF;2. 循环语句答：1) Loop . IF boolean_expr THEN /* */ EXIT; /* EXIT WHEN boolean_expr */ END IF; /* */ END LOOP; 2) WHILE boolean_expr LOOP . END LOOP;

9、 3) FOR loop_counter IN REVERSE low_blound.high_bound LOOP . END LOOP; 注：a. 加上REVERSE 表示递减，从结束边界到起始边界，递减步长为一; b. low_blound 起始边界; high_bound 结束边界;3. GOTO语句答：GOTO label_name; 1) 只能由内部块跳往外部块; 2) 设置标签： 3) 示例： LOOP . IF D%ROWCOUNT = 50 THEN GOTO l_close; END IF; . END LOOP; ; .4. NULL语句答：在语句块中加空语句，用于补充语

10、句的完整性。示例： IF boolean_expr THEN . ELSE NULL; END IF;5. SQL in PL/SQL答：1) 只有DML SQL可以直接在PL/SQL中使用;第五章1. 游标(CURSOR)答：1) 作用：用于提取多行数据集; 2) 声明：a. 普通申明：DELCARE CURSOR CURSOR_NAME IS select_statement /* CURSOR的内容必须是一条查询语句*/ b. 带参数申明：DELCARE CURSOR c_stu(p_id student.ID%TYPE) SELECT * FROM student WHERE ID =

11、 p_id; 3) 打开游标：OPEN Cursor_name; /*相当于执行select语句，且把执行结果存入CURSOR; 4) 从游标中取数：a. FETCH cursor_name INTO var1, var2, .; /*变量的数量、类型、顺序要和Table中字段一致;*/ b. FETCH cursor_name INTO record_var; 注：将值从CURSOR取出放入变量中，每FETCH一次取一条记录; 5) 关闭游标: CLOSE Cursor_name; 注：a. 游标使用后应该关闭; b. 关闭后的游标不能FETCH和再次CLOSE; c. 关闭游标相当于将内存

12、中CURSOR的内容清空;2. 游标的属性答：1) %FOUND: 是否有值; 2) %NOTFOUND: 是否没有值; 3) %ISOPEN: 是否是打开状态; 4) %ROWCOUNT: CURSOR当前的记录号;3. 游标的FETCH循环答：1) LOOP FETCH cursor INTO . EXIT WHEN cursor%NOTFOUND; /*当cursor中没记录后退出*/ END LOOP; 2) WHILE cursor%FOUND LOOP FETCH cursor INTO . END LOOP; 3) FOR var IN cursor LOOP FETCH cur

13、sor INTO. END LOOP; 第六章1. 异常答：DECLARE . e_TooManyStudents EXCEPTION; /* 申明异常 */ . BEGIN . RAISE e_TooManyStudents; /* 触发异常 */ . EXCEPTION WHEN e_TooManyStudents THEN /* 触发异常 */ . WHEN OTHERS THEN /* 处理所有其他异常 */ . END;2004-9-8 星期三 阴PL/SQL数据库编程(下)1. 存储过程(PROCEDURE)答：创建过程： CREATE OR REPLACE PROCEDURE p

14、roc_name (arg_nameIN|OUT|IN OUTTYPE, arg_nameIN|OUT|IN OUTTYPE) IS|AS procedure_body 1) IN: 表示该参数不能被赋值(只能位于等号右边); 2) OUT:表示该参数只能被赋值(只能位于等号左边); 3) IN OUT: 表示该类型既能被赋值也能传值;2. 存储过程例子答：CREATE OR REPLACE PROCEDURE ModeTest( p_InParm IN NUMBER, p_OutParm OUT NUMBER, p_InOutParm IN OUT NUMBER) IS v_LocalVar

15、 NUMBER; /* 声明部分 */ BEGIN v_LocalVar:=p_InParm; /* 执行部分 */ p_OutParm:=7; p_InOutParm:=7; . EXCEPTION . /* 异常处理部分 */ END ModeTest; 3. 调用PROCEDURE的例子答：1) 匿名块可以调; 2) 其他PROCDEURE可以调用; 例： DECLARE v_var1 NUMBER; BEGIN ModeTest(12, v_var1, 10); END; 注：此时v_var1等于74. 指定实参的模式答：1) 位置标示法：调用时添入所有参数，实参与形参按顺序一一对应;

16、 2) 名字标示法：调用时给出形参名字，并给出实参 ModeTest(p_InParm=12, p_OutParm=v_var1, p_Inout=10); 注：a. 两种方法可以混用; b. 混用时第一个参数必须通过位置来指定。5. 函数(Function)与过程(Procedure)的区别答：1) 过程调用本身是一个PL/SQL语句(可以在命令行中通过exec语句直接调用); 2) 函数调用是表达式的一部分;6. 函数的声明答：CREATE OR REPLACE PROCEDURE proc_name (arg_nameIN|OUT|IN OUTTYPE, arg_nameIN|OUT|I

17、N OUTTYPE) RETURN TYPE IS|AS procedure_body 注：1) 没有返回语句的函数将是一个错误;7. 删除过程与函数答：DROP PROCEDURE proc_name; DROP FUNCTION func_name;第八章1. 包答：1) 包是可以将相关对象存储在一起的PL/SQL的结构; 2) 包只能存储在数据库中，不能是本地的; 3) 包是一个带有名字的声明; 4) 相当于一个PL/SQL块的声明部分; 5) 在块的声明部分出现的任何东西都能出现在包中; 6) 包中可以包含过程、函数、游标与变量; 7) 可以从其他PL/SQL块中引用包，包提供了可用于

18、PL/SQL的全局变量。 8) 包有包头和包主体，如包头中没有任何函数与过程，则包主体可以不需要。2. 包头答：1) 包头包含了有关包的内容的信息，包头不含任何过程的代码。 2) 语法： CREATE OR REPLACE PACKAGE pack_name IS|AS procedure_specification|function_specification|variable_declaration|type_definition|exception_declaration|cursor_declaration END pack_name; 3) 示例： CREATE OR REPLACE

19、 PACKAGE pak_test AS PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE); TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER; END pak_test;3. 包主体答：1) 包主体是可选的，如包头中没有任何函数与过程，则包主体可以不需要。 2) 包主体与包头存放在不同的数据字典中。 3) 如包头编译不成功，包主体无法正确编译。 4) 包主体包含了所有在包头中声明的所有过程与函数的代码。 5) 示例： CREATE OR REPL

20、ACE PACKAGE BODY pak_test AS PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE) IS BEGIN . END RemoveStudent; TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER; END pak_test;4. 包的作用域答：1) 在包外调用包中过程(需加包名)：pak_test.AddStudent(100010, CS, 101); 2) 在包主体中可以直接使用包头中声明的对象和过程(不需加包名);5.

21、包中子程序的重载答：1) 同一个包中的过程与函数都可以重载; 2) 相同的过程或函数名字，但参数不同;6. 包的初始化答：1) 包存放在数据库中; 2) 在第一次被调用的时候，包从数据库中调入内存并被初始化; 3) 包中定义的所有变量都被分配内存; 4) 每个会话都将拥有自己的包内变量的副本。第九章1. 触发器答：1) 触发器与过程/函数的相同点 a. 都是带有名字的执行块; b. 都有声明、执行体和异常部分; 2) 触发器与过程/函数的不同点 a. 触发器必须存储在数据库中; b. 触发器自动执行;2. 创建触发器答：1) 语法： CREATE OR REPLACE TRIGGER trig

22、ger_name BEFORE|AFTER triggering_event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body; 2) 范例： CREATE OR REPLACE TRIGGER UpdateMajorStats AFTER INSERT OR DELETE OR UPDATE ON students DECLARE CURSOR c_Statistics IS SELECT * FROM students GROUP BY major; BEGIN . END Up;3. 触发器答：1) 三

23、个语句(INSERT/UPDATE/DELETE); 2) 二种类型(之前/之后); 3) 二种级别(row-level/statement-level); 所以一共有 3 X 2 X 2 = 124. 触发器的限制答：1) 不应该使用事务控制语句; 2) 不能声明任何LONG或LONG RAW变量; 3) 可以访问的表有限。5. 触发器的主体可以访问的表答：1) 不可以读取或修改任何变化表(被DML语句正在修改的表); 2) 不可以读取或修改限制表(带有约束的表)的主键、唯一值、外键列。(4)Java开发中使用Oracle的ORA-01000很多朋友在Java开发中，使用Oracle数据库的

24、时候，经常会碰到有ORA-01000: maximum open cursors exceeded.的错误。实际上，这个错误的原因，主要还是代码问题引起的。 ora-01000: maximum open cursors exceeded. 表示已经达到一个进程打开的最大游标数。 这 样的错误很容易出现在Java代码中的主要原因是：Java代码在执行conn.createStatement()和 conn.prepareStatement()的时候，实际上都是相当与在数据库中打开了一个cursor。尤其是，如果你的 createStatement和prepareStatement是在一个循环里

25、面的话，就会非常容易出现这个问题。因为游标一直在不停的打开，而且没 有关闭。一般来说，我们在写Java代码的时候，createStatement和prepareStatement都应该要放在循环外面，而且 使用了这些Statment后，及时关闭。最好是在执行了一次executeQuery、executeUpdate等之后，如果不需要使用结果集 （ResultSet）的数据，就马上将Statment关闭。对于出现ORA-01000错误这种情况，单纯的加大open_cursors并不是好办法，那只是治标不治本。实际上，代码中的隐患并没有解除。 而且，绝大部分情况下，open_cursors只需要设

26、置一个比较小的值，就足够使用了，除非有非常特别的要求。(5)在store procedure中执行 DDL语句一 是：execute immediate update |table_chan| set |column_changed| = |v_trans_name| where empid = |v_empid| ;二是：The DBMS_SQL package can be used to execute DDL statements directly from PL/SQL. 这是一个创建一个表的过程的例子。该过程有两个参数：表名和字段及其类型的列表。CREATE OR REPLACE

27、PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS cursor1 INTEGER; BEGIN cursor1 := dbms_sql.open_cursor; dbms_sql.parse(cursor1, CREATE TABLE | tablename | ( | cols | ), dbms_sql.v7); dbms_sql.close_cursor(cursor1); end; / 2 如何找数据库表的主键字段的名称?SQLSELECT * FROM user_constraintsWHERE CONSTRAINT_T

28、YPE=P and table_name=TABLE_NAME;3 如何查询数据库有多少表?SQLselect * from all_tables;4 使用sql统配符通 配符 描述 示例 % 包含零个或更多字符的任意字符串。 WHERE title LIKE %computer% 将查找处于书名任意位置的包含单词 computer 的所有书名。 _（下划线） 任何单个字符。 WHERE au_fname LIKE _ean 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。 指定范围 (a-f) 或集合 (abcdef) 中的任何单个字符。 WHERE au_lname