ORACLE存储过程学习经典[语法+实例+调用]
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
8. 用 pl/sql developer debug 连接数据库后建立一个 Test WINDOW 在窗口输入调用 SP 的代码,F9 开始 debug,CTRL+N 单步调试
9. Pl/Sql 中执行存储过程
在 sql*plus 中：
declare
--必要的变量声明，视你的过程而定
function 有返回值，并且可以直接在 Query 中引用 function 和或者使用 function 的返回 值。
本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命 令, 而函数是表达式的一部分。比如：
select max(NAME) FROM 但是不能 exec max(NAME) 如果此时 max 是函数。 PACKAGE 是 function ， procedure ， variables 和 sql 语 句 的 组 合 。 package 允 许 多 个 procedure 使用同一个变量和游标。
Oracle 存储过程学习
目录
Oracle 存储过程................................................................................................................................ 1 Oracle 存储过程基础知识................................................................................................................. 1 Oracle 存储过程的基本语法............................................................................................................. 2 关于 Oracle 存储过程的若干问题备忘............................................................................................4
将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出 NO_DATA_FOUND)
例子：
BEGIN SELECT col1,col2 into 变量 1,变量 2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ...
1. 在 Oracle 中，数据表别名不能加 as。........................................................................... 4 2. 在存储过程中，select 某一字段时，后面必须紧跟 into，如果 select 整个记录，利 用游标的话就另当别论了。.................................................................................................... 5 3. 在利用 select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。..............................................................................................................................5 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段 会报错......................................................................................................................................... 5 5. 在存储过程中，关于出现 null 的问题............................................................................ 5 6. Hibernate 调用 Oracle 存储过程.......................................................................................6 用 Java 调用 Oracle 存储过程总结................................................................................................... 6 一、 无返回值的存储过程................................................................................................ 6 二、 有返回值的存储过程（非列表）............................................................................ 8 三、 返回列表.....................................................................................................................9 在存储过程中做简单动态查询...................................................................................................... 11 一、 本地动态 SQL........................................................................................................... 12 二、 使用 DBMS_SQL 包.................................................................................................. 13 Oracle 存储过程调用 Java 方法...................................................................................................... 16 Oracle 高效分页存储过程实例....................................................................................................... 17
Oracle 存储过程基础知识
商业规则和业务逻辑可以通过程序存储在 Oracle 中，这个程序就是存储过程。 存储过程是 SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用 程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。 要创建一个过程对象（procedural object），必须有 CREATE PROCEDURE 系统权限。如果 这个过程对象需要被其他的用户 schema 使用，那么你必须有 CREATE ANY PROCEDURE 权 限。执行 procedure 的时候，可能需要 excute 权限。或者 EXCUTE ANY PROCEDURE 权限。 如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。
权限的话就可以访问。 执行者权限则需要调用这个 procedure 的用户拥有相关表和对象的权限。
Oracle 存储过程的基本语法
1. 基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 (
参数 1 IN NUMBER, 参数 2 IN NUMBER ) AS 变量 1 INTEGER :=0; 变量 2 DATE; BEGIN END 存储过程名字 2. SELECT INTO STATEMENT
创建 procedure 的语法：
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procedure [(argument [IN | OUT | IN OUT ] [NO COPY] datatype [, argument [IN | OUT | IN OUT ] [NO COPY] datatype]... )]
3. IF 判断 IF V_TEST=1 THEN
BEGIN do something
END; END IF; 4. while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5. 变量赋值 V_TEST := 123; 6. 用 for in 使用 cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名 1+cur_result.列名 2 END; END LOOP; END; 7. 带参数的 cursor
BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no;
END; 可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的
excute 权限都将被保留。 IN, OUT, IN OUT 用来修饰参数。 IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。 OUT 表示 PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。 authid 代表两种权限： 定义者权限（difiner right 默认），执行者权限（invoker right）。 定义者权限说明这个 procedure 中涉及的表，视图等对象所需要的权限只要定义者拥有
[ authid { current_user | definer }] { is | as } { pl/sql_subprogram_body | language { java name 'String' | c [ name, name] lΒιβλιοθήκη Baidubrary lib_name }] Sql 代码： CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER;
9. Pl/Sql 中执行存储过程
在 sql*plus 中：
declare
--必要的变量声明，视你的过程而定
function 有返回值，并且可以直接在 Query 中引用 function 和或者使用 function 的返回 值。
本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命 令, 而函数是表达式的一部分。比如：
select max(NAME) FROM 但是不能 exec max(NAME) 如果此时 max 是函数。 PACKAGE 是 function ， procedure ， variables 和 sql 语 句 的 组 合 。 package 允 许 多 个 procedure 使用同一个变量和游标。
Oracle 存储过程学习
目录
Oracle 存储过程................................................................................................................................ 1 Oracle 存储过程基础知识................................................................................................................. 1 Oracle 存储过程的基本语法............................................................................................................. 2 关于 Oracle 存储过程的若干问题备忘............................................................................................4
将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出 NO_DATA_FOUND)
例子：
BEGIN SELECT col1,col2 into 变量 1,变量 2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ...
1. 在 Oracle 中，数据表别名不能加 as。........................................................................... 4 2. 在存储过程中，select 某一字段时，后面必须紧跟 into，如果 select 整个记录，利 用游标的话就另当别论了。.................................................................................................... 5 3. 在利用 select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。..............................................................................................................................5 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段 会报错......................................................................................................................................... 5 5. 在存储过程中，关于出现 null 的问题............................................................................ 5 6. Hibernate 调用 Oracle 存储过程.......................................................................................6 用 Java 调用 Oracle 存储过程总结................................................................................................... 6 一、 无返回值的存储过程................................................................................................ 6 二、 有返回值的存储过程（非列表）............................................................................ 8 三、 返回列表.....................................................................................................................9 在存储过程中做简单动态查询...................................................................................................... 11 一、 本地动态 SQL........................................................................................................... 12 二、 使用 DBMS_SQL 包.................................................................................................. 13 Oracle 存储过程调用 Java 方法...................................................................................................... 16 Oracle 高效分页存储过程实例....................................................................................................... 17
Oracle 存储过程基础知识
商业规则和业务逻辑可以通过程序存储在 Oracle 中，这个程序就是存储过程。 存储过程是 SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用 程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。 要创建一个过程对象（procedural object），必须有 CREATE PROCEDURE 系统权限。如果 这个过程对象需要被其他的用户 schema 使用，那么你必须有 CREATE ANY PROCEDURE 权 限。执行 procedure 的时候，可能需要 excute 权限。或者 EXCUTE ANY PROCEDURE 权限。 如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。
权限的话就可以访问。 执行者权限则需要调用这个 procedure 的用户拥有相关表和对象的权限。
Oracle 存储过程的基本语法
1. 基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 (
参数 1 IN NUMBER, 参数 2 IN NUMBER ) AS 变量 1 INTEGER :=0; 变量 2 DATE; BEGIN END 存储过程名字 2. SELECT INTO STATEMENT
创建 procedure 的语法：
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procedure [(argument [IN | OUT | IN OUT ] [NO COPY] datatype [, argument [IN | OUT | IN OUT ] [NO COPY] datatype]... )]
3. IF 判断 IF V_TEST=1 THEN
BEGIN do something
END; END IF; 4. while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5. 变量赋值 V_TEST := 123; 6. 用 for in 使用 cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名 1+cur_result.列名 2 END; END LOOP; END; 7. 带参数的 cursor
BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no;
END; 可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的
excute 权限都将被保留。 IN, OUT, IN OUT 用来修饰参数。 IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。 OUT 表示 PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。 authid 代表两种权限： 定义者权限（difiner right 默认），执行者权限（invoker right）。 定义者权限说明这个 procedure 中涉及的表，视图等对象所需要的权限只要定义者拥有
[ authid { current_user | definer }] { is | as } { pl/sql_subprogram_body | language { java name 'String' | c [ name, name] lΒιβλιοθήκη Baidubrary lib_name }] Sql 代码： CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER;