2022年oracle存储过程知识点.docx-淘文阁

资源描述

《2022年oracle存储过程知识点.docx》由会员分享，可在线阅读，更多相关《2022年oracle存储过程知识点.docx（29页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思1SEQNAME.NEXTVAL里面的值如何读出来？可以直接在insert into test valuesSEQNAME.NEXTVAL 是可以用这样：SELECT tmp#_seq.NEXTVAL INTO id_temp FROM DUAL; 然后可以用 id_temp 2PLS-00103: 显现符号在需要以下之一时：代码如下：IF sum0 THEN begin INSERT INTO emesp.tp_sn_PRoduction_log VALUES r_s

2、erial_number, , id_temp; EXIT; end; 始终报 sum0 这是个很郁闷的问题由于变量用了 sum 所以不行,后改为i_sum0 3Oracle 语法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

3、答： 1 PL/SQLProcedual language/SQL 言; 是在标准 SQL的基础上增加了过程化处理的语2 Oracle 客户端工具拜访 Oracle 服务器的操作语言 ; 3 Oracle 对SQL的扩充 ; 4. PL/SQL 的优缺点答：优点：1 结构化模块化编程,不是面对对象 ; 2 良好的可移植性不管 Oracle 运行在何种操作系统 ; 3 良好的可保护性编译通过后储备在数据库里 ; 4 提升系统性能 ; 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 1 页,共 16 页 - - - - - - - - - 名师归纳

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、八个字符左右最合适 ; 5. 变量声明答：语法Var_name type CONSTANTNOT NULL:=value; 注： 1 申明时可以有默认值也可以没有; ; 2 如有 CONSTANTNOT NULL, 变量肯定要有一个初始值3 赋值语句为 “:= ”; 4 变量可以认为是数据库里一个字段 ; 5 规定没有初始化的变量为 NULL; 第三章1. 数据类型答： 1 标量型：数字型、字符型、布尔型、日期型 ; 2 组合型： RECORD常用、TABLE常用、VARRAY 较少用 3 参考型： REF CURSOR游标、REF object_type 4 LOBLarge O

6、bject 2. %TYPE 答：变量具有与数据库的表中某一字段相同的类型例： v_FirstName studengts.first_name%TYPE; 3. RECORD 类型答： TYPE record_name IS RECORD record_name 为变量名称 */ /* 其中 TYPE,IS,RECORD 为关键字,field1 type NOT NULL:=expr1, /* 每个等价的成员间用逗号分隔*/ 第 2 页,共 16 页细心整理归纳精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结精品

7、学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思field2 type NOT NULL:=expr2, /* 假如一个字段限定NOT NULL ,那么它必需拥有一个初始值 */ . /* 全部没有初始化的字段都会初始为NULL fieldn type NOT NULL:=exprn; 4. %ROWTYPE 答：返回一个基于数据库定义的类型DECLARE v_StuRec Student%ROWTYPE; /*Student为表的名字 */ 注：与 3中定一个 record 相比,一步就完成,而要申明 ; b. 实例化变量 ; 5.

8、 TABLE类型3中定义分二步： a. 全部的成员变量都答： TYPE tabletype IS TABLE OF 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_Student100 FROM Student WHERE id = 1001; END; 注： 1 行的数目的限制由 6. 变量的作用域和可见性BINARY_INTEGER

9、的范畴打算 ; 答： 1 执行块里可以嵌入执行块 ; 2 里层执行块的变量对外层不行见 ; 3 里层执行块对外层执行块变量的修改会影响外层块变量的值 ; 第四章1. 条件语句答： IF boolean_expression1 THEN . ELSIF boolean_expression2 THEN /* 留意是 ELSIF,而不是 ELSEIF*/ . . /*ELSE 语句不是必需的,但END IF; 是必需的 */ ELSE END IF; 2. 循环语句答： 1 Loop 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 3 页,共 16 页

10、 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思. IF boolean_expr THEN /* */ EXIT; /* EXIT WHEN boolean_expr */ END IF; /* */ END LOOP; 2 WHILE boolean_expr LOOP . END LOOP; 3 FOR loop_counter IN REVERSE low_blound.high_bound LOOP . END LOOP; 注： a. 加上 REVERSE 表示递减,从终

11、止边界到起始边界,递减步长为一 ; 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 语句答：在语句块中加空语句,用于补充语句的完整性；示例：IF boolean_expr THEN . ELSE NULL; END IF; 细心整理归纳精选学习资料 - - - - - - - - - - -

12、- - - - 第 4 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思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

13、 c_stup_id student.ID%TYPE SELECT * FROM student WHERE ID = p_id; 3 打开游标： OPEN Cursor_name; CURSOR; /* 相当于执行 select 语句,且把执行结果存入4 从游标中取数： a. FETCH cursor_name INTO var1, var2, .; /* 变量的数量、类型、次序要和 Table 中字段一样 ;*/ b. FETCH cursor_name INTO record_var; 注：将值从 CURSOR取出放入变量中,每 5 关闭游标 : CLOSE Cursor_name; 注

14、： a. 游标使用后应当关闭 ; FETCH一次取一条记录 ; b. 关闭后的游标不能 FETCH和再次 CLOSE; c. 关闭游标相当于将内存中 2. 游标的属性答： 1 %FOUND: 是否有值 ; 2 %NOTFOUND: 是否没有值 ; CURSOR的内容清空 ; 3 %ISOPEN: 是否是打开状态 ; 4 %ROWCOUNT: CURSOR 当前的记录号 ; 3. 游标的 FETCH循环答： 1 LOOP FETCH cursor INTO . EXIT WHEN cursor%NOTFOUND; END LOOP; 2 WHILE cursor%FOUND LOOP FETC

15、H cursor INTO . END LOOP; 3 FOR var IN cursor LOOP FETCH cursor INTO. /* 当cursor 中没记录后退出 */ 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 5 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思END LOOP; 第六章 1. 反常答： DECLARE . e_TooManyStudents EXCEPTION; /* 申明反常*/

16、 . BEGIN . RAISE e_TooManyStudents; /* 触发反常*/ . EXCEPTION WHEN e_TooManyStudents THEN /* 触发反常*/ . WHEN OTHERS THEN /* 处理全部其他反常*/ . END; 2004-9-8 星期三阴PL/SQL 数据库编程下 1. 储备过程 PROCEDURE 答：创建过程：CREATE OR REPLACE PROCEDURE proc_name arg_nameIN|OUT|IN OUTTYPE, arg_nameIN|OUT|IN OUTTYPE IS|AS procedure_body

17、 1 IN: 表示该参数不能被赋值只能位于等号右边 ; 2 OUT: 表示该参数只能被赋值只能位于等号左边 ; 3 IN OUT: 表示该类型既能被赋值也能传值 ; 2. 储备过程例子答： CREATE OR REPLACE PROCEDURE ModeTest p_InParm IN NUMBER, p_OutParm OUT NUMBER, 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 6 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法

18、,在循序而渐进 ,熟读而精思p_InOutParm IN OUT NUMBER IS v_LocalVar 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 ModeTest12, v_var1, 10; END; 注：此时

19、v_var1 等于 7 4. 指定实参的模式答： 1 位置标示法：调用时添入全部参数,实参加形参按次序一一对应 ; 2 名字标示法：调用时给出形参名字,并给出实参 ModeTestp_InParm=12, p_OutParm=v_var1, p_Inout=10; 注： a. 两种方法可以混用 ; b. 混用时第一个参数必需通过位置来指定；5. 函数 Function 与过程 Procedure 的区分答： 1 过程调用本身是一个 PL/SQL 语句可以在命令行中通过 exec 语句直接调用 ; 2 函数调用是表达式的一部分 ; 6. 函数的声明答： CREATE OR REPLACE

20、 PROCEDURE proc_name arg_nameIN|OUT|IN OUTTYPE, 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 7 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思arg_nameIN|OUT|IN OUTTYPE RETURN TYPE IS|AS procedure_body 注： 1 没有返回语句的函数将是一个错误 ; 7. 删除过程与函数答： DROP PROCEDURE proc

21、_name; DROP FUNCTION func_name; 第八章1. 包答： 1 包是可以将相关对象储备在一起的 PL/SQL 的结构 ; 2 包只能储备在数据库中,不能是本地的 ; 3 包是一个带出名字的声明 ; 4 相当于一个 PL/SQL 块的声明部分 ; 5 在块的声明部分显现的任何东西都能显现在包中 ; 6 包中可以包含过程、函数、游标与变量 ; 7 可以从其他 PL/SQL 块中引用包,包供应了可用于PL/SQL 的全局变量；8 包有包头和包主体,如包头中没有任何函数与过程,就包主体可以不需要；2. 包头答： 1 包头包含了有关包的内容的信息,包头不含任何过程的代码；2

22、语法：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 PACKAGE pak_test AS PROCEDURE RemoveStudentp_StuID IN students.id%TYPE; TYPE t_StuIDTable I

23、S TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER; END pak_test; 3. 包主体答： 1 包主体是可选的,如包头中没有任何函数与过程,就包主体可以不需要；细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 8 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思2 包主体与包头存放在不同的数据字典中；3 如包头编译不胜利,包主体无法正确编译；4 包主体包含了全部在

24、包头中声明的全部过程与函数的代码；5 示例：CREATE OR REPLACE PACKAGE BODY pak_test AS PROCEDURE RemoveStudentp_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.AddStudent100010, CS, 101; 2 在包

25、主体中可以直接使用包头中声明的对象和过程不需加包名 ; 5. 包中子程序的重载答： 1 同一个包中的过程与函数都可以重载 ; 2 相同的过程或函数名字,但参数不同 ; 6. 包的初始化答： 1 包存放在数据库中 ; 2 在第一次被调用的时候,包从数据库中调入内存并被初始化 ; 3 包中定义的全部变量都被安排内存 ; 4 每个会话都将拥有自己的包内变量的副本；第九章1. 触发器答： 1 触发器与过程 / 函数的相同点a. 都是带出名字的执行块 ; b. 都有声明、执行体和反常部分 ; 2 触发器与过程 / 函数的不同点a. 触发器必需储备在数据库中 ; b. 触发器自动执行 ; 2. 创建触发

26、器答： 1 语法：CREATE OR REPLACE TRIGGER trigger_name 细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 9 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思BEFORE|AFTER triggering_event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body; 2 范例：CREATE OR R

27、EPLACE 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 三个语句 INSERT/UPDATE/DELETE; 2 二种类型之前 / 之后 ; 3 二种级别 row-level/statement-level; 所以一共有 3 X 2 X 2 = 12 4. 触发器的限制答： 1 不应当使用事务掌握语句

28、 ; 2 不能声明任何 LONG 或LONG RAW 变量 ; 3 可以拜访的表有限；5. 触发器的主体可以拜访的表答： 1 不行以读取或修改任何变化表被DML 语句正在修改的表; 2 不行以读取或修改限制表带有约束的表的主键、唯独值、外键列；4Java 开发中使用 Oracle 的ORA-01000 许多伴侣在 Java开发中,使用 Oracle 数据库的时候,常常会遇到有 ORA-01000: maximum open cursors exceeded. 的错误；实际上,这个错误的缘由,主要仍是代码问题引起的；ora-01000: maximum open cursors exceede

29、d. 表示已经达到一个进程打开的最大游标数；这样的错误很简单显现在Java代码中的主要缘由是：Java代码在执行第 10 页,共 16 页 - - - - - - - - - conn.createStatement和 conn.prepareStatement的时候,实际上都是相当与在数据库中打开了一个 cursor ；特殊是 ,假如你的createStatement和prepareStatement是在一个循环里面的话,就会特别简单显现这个问题；由于游标始终在不停的打开,而且没有关细心整理归纳精选学习资料 - - - - - - - - - - - - - - -名师归纳总结精品学

30、习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思闭；一般来说,我们在写 Java代码的时候, createStatement 和prepareStatement 都应当要放在循环外面,而且使用了这些 Statment 后,准时关闭；最好是在执行了一次executeQuery、executeUpdate 等之后,假如不需要使用结果集（ResultSet ）的数据,就立刻将 Statment 关闭；对于显现 ORA-01000 错误这种情形,单纯的加大open_cursors并不是好方法,那只是治标不治本；实际上,代码中的隐患并没有解除；而

32、字段及其类型的列表；CREATE OR REPLACE PROCEDURE ddlproc tablename varchar2, cols varchar2 AS cursor1 INTEGER; BEGIN cursor1 := dbms_sql.open_cursor; dbms_sql.parsecursor1, CREATE TABLE | tablename | dbms_sql.v7; dbms_sql.close_cursorcursor1; end; / 2 如何找数据库表的主键字段的名称 . SQLSELECT * FROM user_constraints | cols

33、| , WHERE CONSTRAINT_TYPE=P and table_name=TABLE_NAME; 3 如何查询数据库有多少表 . SQLselect * from all_tables; 4 使用 sql 统配符通配符描述示例 % 包含零个或更多字符的任意字符串；WHERE title LIKE _（下划第 11 页,共 16 页 %computer% 将查找处于书名任意位置的包含单词computer 的全部书名；线）任何单个字符；WHERE au_fname LIKE _ean 将查找以ean 结尾的全部4 个字细心整理归纳精选学习资料 - - - - - - - -

34、- - - - - - - - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思母的名字（ Dean 、Sean 等）；指定范畴a-f 或集合abcdef 中的任何单个字符； WHERE au_lname LIKE C-Parsen 将查找以 arsen 结尾且以介于 C 与 P 之间的任何单个字符开头的作者姓氏,例如,Carsen 、Larsen 、 Karsen 等；不属于指定范围 a-f 或集合 abcdef 的任何单个字符；WHERE au_lname LIKE del%

35、将查找以 de 开头且其后的字母不为 l 的全部作者的姓氏；5使一般用户有查看 v$session 的权限 GRANT SELECT ON SYS.V_$OPEN_CURSOR TO SFISM4; GRANT SELECT ON SYS.V_$SESSION TO SFISM4; 常用函数 distinct 去掉重复的minus 相减在第一个表但不在其次个表 SELECT * FROM FOOTBALL MINUS SELECT * FROM SOFTBALL; intersect 相交 INTERSECT 返回两个表中共有的行；SELECT * FROM FOOTBAL ；UNION

36、 ALL 与 UNION 一样对表进行了合并但是它不去掉重复的记录；汇总函数 count select count* from test; SUM SUM 就犹如它的本意一样它返回某一列的全部数值的和；SELECT SUMSINGLES TOTAL_SINGLES FROM TEST; SUM 只能处理数字假如它的处理目标不是数字你将会收到如下信息输入 /输出 SQLSELECT SUMNAME FROM TEAMSTATS; ERROR ORA-01722 invalid number no rows selected 该错误信息当然的合理的由于 AVG NAME 字段是无法进行汇总的；细

37、心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 12 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思AVG 可以返回某一列的平均值；SELECT AVGSO AVE_STRIKE_OUTS FROM TEAMSTATS；MAX 假如你想知道某一列中的最大值请使用 MAX ；SELECT MAXHITS FROM TEAMSTATS; MIN MIN 与MAX 类似它返回一列中的最小数值；VARIANCE VARIANCE

38、方差不是标准中所定义的但它却是统计领域中的一个至关重要的数值；SELECT VARIANCEHITS FROM TEAMSTATS; STDDEV 这是最终一个统计函数 STDDEV 返回某一列数值的标准差；SELECT STDDEV HITS FROM TEAMSTATS；日期时间函数 ADD_MONTHS ADD_MONTHS 也可以工作在 select 之外该函数的功能是将给定的日期增加一个月举例来说由于一些特殊的缘由上述的方案需要推迟两个月那么就用到了；LAST_DAY LAST_DAY 可以返回指定月份的最终一天 . MONTHS_BETWEEN 假如你想知道在给定的两个日期中有多

39、少个月可以使用MONTHS_BETWEEN ；select task, startdate, enddate ,months betweenStartdate,enddate duration from project; 返回结果有可能是负值 . 可以利用负值来判定某一日期是否在另一个日期之前下例将会显示全部在 1995 年5 月19 日以前开头的竞赛 . SELECT * FROM PROJECT WHERE MONTHS_BETWEEN 19-MAY-95, STARTDATE0; NEW_TIME 假如你想把时间调整到你所在的时区你可以使用 NEW_TIME. SQLSELECT END

40、DATE EDT, NEW_TIMEENDDATE, EDT, PDT FROM PROJECT; NEXT_DAY NEXT_DAY 将返回与指定日期在同一个星期或之后一个星期内的你所要求的星期天细心整理归纳精选学习资料 - - - - - - - - - - - - - - - 第 13 页,共 16 页 - - - - - - - - - 名师归纳总结精品学习资料 - - - - - - - - - - - - - - -读书之法 ,在循序而渐进 ,熟读而精思数的准确日期假如你想知道你所指定的日期的星期五是几号可以这样做 . SQLSELECT STARTDATE, NEXT_DAY

41、STARTDATE, FRIDAY FROM PROJECT; SYSDATE SYSDATE 将返回系统的日期和时间；SELECT DISTINCT SYSDATE FROM PROJECT ；数学函数 ABS ABS 函数返回给定数字的肯定值 CEIL 和FLOOR CEIL 返回与给定参数相等或比给定参数在的最小整数与给定参数相等或比给定参数小的最大整数 . COS COSH SIN SINH TAN TANH .FLOOR 就正好相反它返回COS SIN TAN 函数可以返回给定参数的三角函数值默认的参数认定为弧度制 . EXP EXP 将会返回以给定的参数为指数以 e 为底数的幂 . LN and LOG

展开阅读全文