(Oracle管理)SQL中调用ORACLE存储过程
oracle存储过程学习经典语法实例调用
O r a c l e存储过程学习目录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 的区别。
function有返回值,并且可以直接在Query中引用function和或者使用function的返回值。
本质上没有区别,都是 PL/SQL 程序,都可以有返回值。
最根本的区别是：存储过程是命令, 而函数是表达式的一部分。
比如：select max NAME FROM但是不能 exec max NAME 如果此时max是函数。
PACKAGE是function,procedure,variables 和sql 语句的组合。
package允许多个procedure使用同一个变量和游标。
创建 procedure的语法：Sql 代码：可以使用 create or replace procedure 语句, 这个语句的用处在于,你之前赋予的excute 权限都将被保留。
IN, OUT, IN OUT用来修饰参数。
实例：调用Oracle数据库存储过程的主要步骤
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，FienReport使用时只要调用即可。
调用Oracle数据库存储过程主要有两步：第一步，定义存储过程;第二步，调用存储过程。
下面以一个具体的实例来学习如何使用FineReport调用Oracle数据库存储过程的。
第一步，Oracle定义存储过程StScroe是Oracle数据库中的一张表，其中记录了学生的成绩信息，表结构如下：表：记录了学生的成绩信息定义返回列表的存储过程——由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了，所以定义存储过程要分两部分：1.建立一个程序包，如下：CREATE OR REPLACE PACKAGE TESTPACKAGE ASTYPE Test_CURSOR IS REF CURSOR;END TESTPACKAGE;2.建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE p_STSCORE(Class in varchar2,p_CURSOR out TESTPACKAGE.Test_CURS OR) ISBEGINOPEN p_CURSOR FOR SELECT * FROM FR.STSCORE where STSCORE.ClassNo=Class;END p_STSCORE;第二步，调用存储过程1.启动FineReport设计器，右击数据源面板，选择私有数据源，弹出私有数据源对话框。
2.点击增加按钮，新建一个私有数据源，名为ds1，数据库选择为Oracle数据源，查询类型为存储过程，在sql文本框中写如下语句调用存储过程：{call fr.p_stscore('[?Class|Class1?]',?)}在sql文本框中写下调用存储过程语句3.点击预览按钮，可以预览查询到的数据，如下所示：预览查询到的数据至此，FineReport设计器成功调用Oracle数据库存储过程，您可以根据报表需求随心所欲地设计报表样式了。
oracle存储过程的用法
oracle存储过程的用法一、存储过程概述存储过程是在数据库中预先编译好的SQL语句集合，它可以在数据库中作为一个独立的对象存在，并由数据库管理系统执行。
存储过程具有可重复使用、可编译优化、可嵌套调用等优点，是数据库开发中常用的一种技术手段。
二、存储过程的创建要创建存储过程，需要使用Oracle数据库的SQL开发工具（如SQLDeveloper）或命令行工具（如SQL*Plus）。
创建存储过程的语法如下：```sqlCREATEPROCEDUREprocedure_name[parameter_list][IS|AS]block_of_code[LANGUAGE][sql][EXECUTEIMMEDIATE]SQL_CODE```其中，`procedure_name`是存储过程的名称，`parameter_list`是可选的参数列表，`block_of_code`是存储过程的主体代码，`LANGUAGE`指定存储过程的编程语言（如PL/SQL），`EXECUTEIMMEDIATE`用于执行动态SQL代码。
三、存储过程的参数存储过程的参数可以是输入参数、输出参数或输入/输出参数。
输入参数表示从调用方传递给存储过程的值，输出参数表示存储过程的结果值返回给调用方，而输入/输出参数则同时具有输入和输出功能。
在存储过程中，可以使用SQL数据类型（如VARCHAR2、NUMBER、DATE等）或Oracle特有的数据类型（如RAW、BLOB等）来定义参数。
同时，还可以使用PL/SQL的数据类型（如RAW、BLOB、CLOB等）来定义存储过程的局部变量和输出参数。
四、存储过程的调用要调用存储过程，需要使用EXECUTE语句或调用对象的方法。
例如：```sqlEXECUTEprocedure_name(parameter1,parameter2);```或```vbnetprocedure_object.procedure_method();```其中，`procedure_name`是存储过程的名称，`parameter1`和`parameter2`是传递给存储过程的参数值。
SQL中调用ORACLE存储过程
SQL Server调用Oracle的存储过程收藏原文如下：通过SQL Linked Server 执行0rac 1 e存储过程小结1举例我们可以通过下面的方法在SQL Server中通过Linked Server来执行Oracle存储过程。
(1)Oracle PackagePACKAGE Test PACKAGE ASTYPE t_t is TABLE of VARCHAR2(30)INDEX BY BINARY,INTEGER；PROCEDURE Test procedure1(p BATCH」D IN VARCHAR2,p__Number IN number,P.MSG OUT t_t.p MSG1 OUT t_t);END Test PACKAGE；PACKAGE BODY Test PACKAGE ASPROCEDURE Test procedure1(p BATCH一ID IN VARCHAR2,p Number IN number,P.MSG OUT t_t,p MSG1 OUT t_t)ASBEGINp. MSGp. MSG(2)： = ,b,；p. MSG(3)=a‘；p MSGl(l)：= Qbc‘；RETURN；MIT；EXCEPTIONWHEN OTHERS THENROLLBACK；END Test procedure1；END Test PACKAGE；(2)在SQL Server中通过Linked Server 来执行Oracle 存储过程declare BatchID nvarchar (40)declare QueryStr nvarchar (1024)declare StatusCode nvarchar(100)declare sq1 nvarchar(1024)set BatchID=，AM*SET QueryStr=, {CALL GSN. Test_PACKAGE. Test_procedurel(* * *1，+BatchID+,1'".八'‘4’'''.{resultset 3. p_MSG}.{resultset 1, p_ MSG1})}1(3)执行结果(a)select sql=r SELECT StatusCode=p. msg FROM OPENQUERY (HI4DB__MS,r11-Query Str+''')'exec sp executesql sql,N f StatusCode nvarchar(100) output*,StatusCode outpu tprint StatusCode答案:StatusCode=, a'(b) select sql=f SELECT top 3 StatusCode=p_msg FROM OPENQUERY (HI4DB MS,-QueryStr+,,F)rexec sp_executesql sql,N1StatusCode nvarchar(100) output *.StatusCode outpu print StatusCode答案:StatusCode=, a(c)select sql=f SELECT top 2 StatusCode=p_msg FROM OPENQUERY (HI4DB MS,1r r -QueryStr+，，f)rexec sp_executesql sql.N1StatusCode nvarchar(100) output r.StatusCode outpu tprint StatusCode答案:StatusCode=， b'(d)select sql=r SELECT top 1 StatusCode=p_msg FROM OPENQUERY (HI4DB MS,1'r-QueryStr+，，f)rexec sp executesql sqlStatusCode nvarchar(100) output1,StatusCode outpu print StatusCode答案:StatusCode二'c(e)SET QueryStr=，{CALL GSN. Test.PACKAGE. Test procedure1C11f，+BatchID+，11 *r / 1''4'' '' • {resultset 1, p. MSG1}. {resultset 3. p_MSG})}'----------------------------------- (注意这里p_MSG1 和P MSG交换次序了)EXEC(r SELECT p…msgl FROM OPENQUERY (HI4DB MS/r,-QueryStr+,r1)r) select sql=r SELECT StatusCode=p_msgl FROM OPENQUERY (HI4DB MS/r,-QuerySexec sp executesql sql,N*StatusCode nvarchar(100) output*,StatusCode outpuprint StatusCode答案：StatusCode=" abc*2上述使用方法的条件(1)Link Server 要使用Microsoft 的Driver (Microsoft OLE DB Provider fo r Oracle)(2)Oracle Package中的Procedure的返回参数是Table类型，目前table只试成功一个栏位。
调用oracle存储过程(详解)
PL/SQL 块的结构和实例
• • • • • • • • • • • • • • • • • • • • • • •
1.使用各种if 语句 2.使用循环语句 3.使用控制语句——goto 和null； 条件分支语句 pl/sql 中提供了三种条件分支语句if—then，if – then – else，if – then – elsif – then 这里我们可以和java 语句进行一个比较 简单的条件判断 if – thenn 问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就 给该员工工资增加10%。 Sql 代码 1. create or replace procedure sp_pro6(spName varchar2) is 2. --定义 3. v_sal emp.sal%type; 4. begin 5. --执行 6. select sal into v_sal from emp where ename=spName; 7. --判断 8. if v_sal<2000 then 9. update emp set sal=sal+sal*10% where ename=spName; 10. end if; 11.end; 12./
PL/SQL 块的结构和实例
• • • • • •
pl/sql分类 -- 过程，函数，包，触发器 过程 过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指 定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分； 通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus 中可以 使用create procedure 命令来建立过程。
PL/SQL 块的结构和实例
Oracle（存储过程、存储函数、用程序调用）
Oracle （存储过程、存储函数、⽤程序调⽤）指存储在数据库中的供所有⽤户程序带哦⽤的⼦程序（PL/SQL ）叫存储过程（不能返回值）、存储函数（可以通过return 语句返回值）1、存储过程为了完成特定功能的SQL 语句集，经编译后存储在数据库中。
（1）新建：（2）书写存储过程的代码：（3）编译运⾏代码：（4）调⽤存储过程：2、存储函数create or replace procedure raiseSalary(eno in number)ispsal emp.sal %type;beginselect sal into psal from emp where empno=eno;update emp set sal= sal + 100 where empno = eno ;dbms_output.put_line('前:'||psal||'后:'||(psal+100));end raiseSalary;存储函数与存储过程的结构类似，但是必须有⼀个return ⼦句，⽤于返回函数值。
（1）创建⼀个存储函数：（2）书写代码：（3）右键选择test：3、存储过程和存储函数的OUT（1）创建存储过程：（2）书写程序：查询员⼯的信息（3）测试结果：create or replace function queryEmpIncome(eno in number)return numberispsal emp.sal %type;pcomm m %type;beginselect sal,comm into psal,pcomm from emp where empno=eno;return psal*12+nvl(pcomm,0);end queryEmpIncome;create or replace procedure queryEmpInfeno(eno in number,pename out varchar2,psal out number,pjob out varchar2)isbeginselect ename,sal,job into pename,psal,pjob from emp where empno=eno; end queryEmpInfeno;4、java程序调⽤存储过程和存储函数（1）先在虚拟机中找到需要导⼊的jar包并进⾏导⼊：（2）书写⼀个⼯具类：package pers.zhb.utils;import java.sql.*;public class JDBCUtils {private static String driver = "oracle.jdbc.OracleDriver";private static String url = "jdbc:oracle:thin:@192.168.125.129:1521/orcl"; private static String user = "scott";private static String password = "tiger";static{try {Class.forName(driver);} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();}return null;}public static void release(Connection conn, Statement st, ResultSet rs){ if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;}}if(st != null){try {st.close();} catch (SQLException e) {e.printStackTrace();}finally{st = null;}}if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}}}}（3）创建测试类，调⽤存储过程和存储函数：public class Test {public void testProcedure(){String sql = "{call raiseSalary(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.setInt(1,7839);call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); call.execute();String name = call.getString(2);double sal = call.getDouble(3);String job = call.getString(4);System.out.println(name+"\t"+sal+"\t"+job);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public void testFunction(){String sql = "{?=call queryEmpIncome(?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.registerOutParameter(1, OracleTypes.NUMBER); call.setInt(2, 7839);call.execute();double income = call.getDouble(1);System.out.println(income);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public static void main(String [] args){Test test=new Test();test.testFunction();}}。
oracle sql查询存储过程内容
oracle sql查询存储过程内容Oracle SQL查询存储过程内容在Oracle数据库中，存储过程是一种预编译的数据库对象，用于封装一系列的SQL语句和业务逻辑。
当我们需要查询存储过程的内容时，可以通过以下方式实现：1. 使用Oracle SQL开发工具Oracle SQL开发工具如SQL Developer、Toad等，提供了直接查询数据库对象的功能。
通过连接到相应的数据库，我们可以执行以下步骤：•打开SQL开发工具并连接到数据库。
•在数据库导航栏中选择存储过程所在的模式（Schema）。
•展开“存储过程”（或类似的选项）文件夹，找到目标存储过程。
•右键单击存储过程，并选择“查看”选项。
•在弹出的窗口中，可以查看到存储过程的代码或源码。
2. 使用SQL查询系统表Oracle数据库提供了一些系统表，存储了数据库对象的元数据信息。
我们可以通过查询这些系统表来获取存储过程的内容。
以下是一些常用的系统表和他们的用途：•ALL_OBJECTS：包含了数据库中所有的对象信息，包括存储过程。
•ALL_SOURCE：存储了数据库中所有对象的源码信息，包括存储过程的代码。
•ALL_PROCEDURES：记录了所有存储过程的详细信息，包括存储过程名称、所属模式等。
通过执行类似以下的SQL查询语句，我们可以获取存储过程的内容：SELECT textFROM all_sourceWHERE object_type = 'PROCEDURE'AND owner = 'SCHEMA_NAME'AND name = 'PROCEDURE_NAME';请注意将上述查询语句中的’SCHEMA_NAME’和’PROCEDURE_NAME’替换为实际的模式名称和存储过程名称。
3. 使用DBMS_METADATA包Oracle数据库提供了一个名为DBMS_METADATA的强大的包，它可以用于获取数据库对象的元数据信息。
oracle调用存储过程的sql语句
oracle调用存储过程的sql语句
Oracle数据库中，调用存储过程可以使用SQL语句，具体步骤如下：
1. 创建存储过程
先在数据库中创建存储过程。
例如，创建一个名为'my_proc'的存储过程，代码如下：
CREATE OR REPLACE PROCEDURE my_proc
IS
BEGIN
-- 存储过程的具体操作，可以包括SQL语句、PL/SQL代码等 ...
END;
2. 调用存储过程
在SQL语句中调用存储过程，可以使用如下语句：
BEGIN
my_proc; -- 调用存储过程
END;
调用存储过程时，可以传入参数。
例如，假设存储过程需要传入一个参数'param1'，可以使用如下语句：
BEGIN
my_proc(param1); -- 调用存储过程，并传入参数
END;
在SQL语句中调用存储过程时，还可以将存储过程的返回值赋值给变量。
例如，假设存储过程返回一个数值类型的值'result'，可以使用如下语句：
DECLARE
result NUMBER;
BEGIN
result := my_proc; -- 调用存储过程，并将返回值赋值给result变量
END;
以上是Oracle调用存储过程的SQL语句的简要介绍。
在实际使用中，需要根据具体情况灵活应用。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL Server 调用Oracle的存储过程收藏原文如下：通过SQL Linked Server 执行Oracle 存储过程小结1 举例我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。
(1) Oracle PackagePACKAGE Test_PACKAGE ASTYPE t_t is TABLE of VARCHAR2(30)INDEX BY BINARY_INTEGER;PROCEDURE Test_procedure1( p_BATCH_ID IN VARCHAR2,p_Number IN number,p_MSG OUT t_t,p_MSG1 OUT t_t);END Test_PACKAGE;PACKAGE BODY Test_PACKAGE ASPROCEDURE Test_procedure1( p_BATCH_ID IN VARCHAR2,p_Number IN number,p_MSG OUT t_t,p_MSG1 OUT t_t)ASBEGINp_MSG(1):='c';p_MSG(2):='b';p_MSG(3):='a';p_MSG1(1):='abc';RETURN;COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK;END Test_procedure1;END Test_PACKAGE;(2) 在SQL Server中通过Linked Server 来执行Oracle 存储过程declare @BatchID nvarchar (40)declare @QueryStr nvarchar (1024)declare @StatusCode nvarchar(100)declare @sql nvarchar(1024)set @BatchID='AAA'SET @QueryStr='{CALL GSN. Test_PACKAGE.Test_procedure1('''''+@BatchID +''''',''''4'''',{resultset 3, p_MSG},{resultset 1, p_MSG1})}'(3)执行结果(a)select @sql='SELECT @StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,'' '+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e outputprint @StatusCode答案:@StatusCode=’a’(b)select @sql='SELECT top 3 @StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,'''+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e outputprint @StatusCode答案:@StatusCode=’a’(c)select @sql='SELECT top 2 @StatusCode=p_msg FROM OPENQUERY (HI4DB _MS,'''+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e outputprint @StatusCode答案:@StatusCode=’b’(d)select @sql='SELECT top 1 @StatusCode=p_msg FROM OPENQUERY (HI4DB _MS,'''+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e outputprint @StatusCode答案:@StatusCode=’c’(e)SET @QueryStr='{CALL GSN. Test_PACKAGE.Test_procedure1('''''+@BatchID +''''',''''4'''',{resultset 1, p_MSG1},{resultset 3, p_MSG})}'----(注意这里p_MS G1和p_MSG交换次序了)EXEC('SELECT p_msg1 FROM OPENQUERY (HI4DB_MS,'''+@QueryStr+''')') select @sql='SELECT @StatusCode=p_msg1 FROM OPENQUERY (HI4DB_MS, '''+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode outputprint @StatusCode答案: @StatusCode=’abc’2 上述使用方法的条件(1) Link Server要使用Microsoft的Driver（Microsoft OLE DB Provider for Oracl e）(2) Oracle Package中的Procedure的返回参数是Table类型，目前table只试成功一个栏位。
(3) SQL Server的Store Procedure调用Oracle Procedure时，返回参数名字必须和Procedure相同。
3 上述方法的要点(1) 如果要实现“Oracle和SQL Server数据库”之间的Trans处理，则Oracle的Proce dure不要有Commit,rollback等语句，让SQL Server的Store Procedure去控制整个Trans何时commit。
(2) 假如返回参数大于1个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)。
但是输入参数和返回参数的顺序不能调换。
(3) { resultset n, p_MSG1}，这里的n，表示返回表的行数。
N可以大于等于实际的行数，但不能小于实际的行数，会报错。
(4) 假如返回表有多行记录,执行select @sql='SELECT @StatusCode=p_msg FROM OPENQUERY (HI4DB_MS, '''+@QueryStr+''')'exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode outputprint @StatusCode, @StatusCode中的值为最后一行记录的值, 如执行结果(a)。
4 动态SQL语句(1)普通SQL语句可以用Exec执行eg: Select * from MCITYExec('select * from MCITY)sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N(2)字段名，表名，数据库名之类作为变量时，必须用动态SQLeg: declare @FielsName varchar(20)declare @sqls nvarchar(1000)set @FielsName = 'CITY'Select @FielsName from MCITY -- 错误Exec('select ' + @FielsName + ' from MCITY ') -- 请注意加号前后的单引号的边上要加空格set @sqls='select ' + @FielsName + ' from MCITY 'exec sp_executesql @sqls当然将字符串改成变量的形式也可declare @s varchar(1000)set @s = 'select ' + @FielsName + ' from MCITY'Exec(@s) -- 成功exec sp_executesql @s -- 此句会报错declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)set @s = 'select ' + @fname + ' from from MCITY'Exec(@s) -- 成功exec sp_executesql @s -- 此句正确(3) 输出参数eg: declare @num intdeclare @sqls nvarchar(1000)declare @strTableName nvarchar(55)set @strTableName='MCITY'set @sqls='select count(*) from ' +@strTableNameexec (@sqls)如何能将exec执行的结果存入变量@num中declare @num intdeclare @sqls nvarchar(1000)declare @strTableName nvarchar(55)set @strTableName='MCITY'set @sqls='select @a=count(*) from '+@strTableNameexec sp_executesql @sqls,N'@a int output',@num outputselect @num（注：以上SQL在sv-02,Qservice下测试通过。