您的位置：360文档中心› oracle存储过程代码

oracle存储过程代码

合集下载

oracle存储过程编写与调用

oracle存储过程编写与调用

oracle存储过程编写与调用Oracle存储过程是一组预编译SQL语句的集合，其被存储在数据库中并可以重复使用。

它们被用来执行一系列的数据库操作，并可以接受参数作为输入，并返回结果。

编写和调用Oracle存储过程可以提高应用程序的性能和安全性。

以下是关于如何编写和调用Oracle存储过程的参考内容。

编写Oracle存储过程：1. 创建存储过程：使用CREATE PROCEDURE语句来创建一个新的存储过程。

例如：```sqlCREATE OR REPLACE PROCEDURE sp_exampleISBEGIN-- code goes hereEND;/```存储过程名称为"sp_example"。

2. 添加参数：存储过程可以接受输入参数和返回参数。

使用IN关键字来指定输入参数，使用OUT关键字来指定返回参数。

例如：```sqlCREATE OR REPLACE PROCEDURE sp_example(in_paramIN VARCHAR2, out_param OUT NUMBER)ISBEGIN-- code goes hereEND;/```3. 执行SQL语句：在存储过程中，可以执行各种SQL语句，包括SELECT、INSERT、UPDATE等。

例如：```sqlCREATE OR REPLACE PROCEDURE sp_exampleISBEGINSELECT * FROM employees;INSERT INTO departments VALUES (10, 'IT');UPDATE employees SET salary = salary * 1.1;END;/```调用Oracle存储过程：1. 调用存储过程：使用EXECUTE或EXEC关键字来调用存储过程。

例如： ```sqlEXEC sp_example;EXECUTE sp_example;```2. 传递参数：如果存储过程接受参数，则需要在调用时提供参数的值。

oracle procedures写法

oracle procedures写法

oracle procedures写法Oracle Procedures的写法是指在Oracle数据库中创建和使用存储过程的方法和规范。

存储过程是一组预编译的SQL语句，可在数据库中存储和执行。

它可以接收输入参数并返回输出参数，用于实现数据库操作的复杂逻辑和业务需求。

本文将介绍Oracle Procedures的编写和使用方法。

一、创建存储过程在Oracle数据库中创建存储过程需要使用PL/SQL语言。

PL/SQL 是Oracle专用的过程式编程语言，结合了SQL语句和常规编程语言的特性。

以下是创建存储过程的基本语法：```CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2,...)]IS[local_variable_declarations]BEGINexecutable_statements[EXCEPTIONexception_handling_statements]END procedure_name;```在上述语法中，`procedure_name`是存储过程的名称；`(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, ...)`是存储过程的输入参数列表，每个参数可以指定输入、输出或者两者兼备；`local_variable_declarations`是存储过程中的局部变量声明部分；`executable_statements`是存储过程的实现部分，可以包含SQL语句和控制结构；`EXCEPTION`和`exception_handling_statements`是可选的异常处理部分，用于捕获和处理存储过程执行过程中发生的异常。

下面是一个示例，展示如何创建一个简单的Oracle存储过程：```CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2)ISBEGINDBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');END greet_user;```上述存储过程名为"greet_user"，接收一个输入参数"p_name"，类型为VARCHAR2。

oracle数据库存储过程写法

oracle数据库存储过程写法

oracle数据库存储过程写法在Oracle数据库中，存储过程可以使用PL/SQL语言进行编写。

以下是一个简单的Oracle数据库存储过程的示例：```CREATE OR REPLACE PROCEDURE my_procedureIS-- 声明变量my_variable VARCHAR2(100);BEGIN-- 初始化变量my_variable := 'Hello, World!';-- 打印变量值DBMS_OUTPUT.PUT_LINE(my_variable);END;/```在这个示例存储过程中，我们首先使用`CREATE OR REPLACE PROCEDURE`语句定义存储过程，并指定存储过程的名称为`my_procedure`。

在存储过程主体中，我们声明了一个名为`my_variable`的变量，并使用`VARCHAR2(100)`指定了变量的数据类型和最大长度。

在`BEGIN`和`END`关键字之间，我们可以编写存储过程的逻辑。

在这个例子中，我们将字符串`Hello, World!`赋值给`my_variable`变量，并使用`DBMS_OUTPUT.PUT_LINE`过程将变量的值打印到输出窗口。

要执行这个存储过程，可以使用`EXECUTE`语句，例如：```EXECUTE my_procedure;```这将执行`my_procedure`存储过程，并在输出窗口中打印`Hello, World!`。

注意，在执行存储过程之前，需要先启用输出窗口，可以使用以下SQL语句启用输出窗口：```SET SERVEROUTPUT ON;```。

Oracle（存储过程、存储函数、用程序调用）

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存储过程写法及调用

oracle存储过程写法及调用

Oracle存储过程的写法及调用如下：存储过程定义语法：```sqlCREATE [ORREPLACE] PROCEDURE procedure_name(arg1 [mode1] datatype1, arg2 [mode2] datatype2)IS [AS]PL/SQLBlock;```其中，`procedure_name` 是存储过程的名称；`arg1` 和`arg2` 是存储过程的参数，包括参数名、模式（IN、OUT、IN OUT）和数据类型；`PL/SQLBlock` 是存储过程的主体部分，包括一系列的SQL语句。

如果存储过程没有参数，只需要定义存储过程的主体部分即可。

例如：```sqlCREATE PROCEDURE out_time ISBEGINDBMS_OUTPUT.PUT_LINE('procedure_1......');END;```如果存储过程有参数，需要在定义时指定参数名、模式和数据类型。

例如：```sqlCREATE PROCEDURE procedure_2(v_i IN NUMBER, v_j OUT NUMBER) ISBEGINv_j := v_i * 2;DBMS_OUTPUT.PUT_LINE('procedure_2......' || v_i || '......' || v_j);END;```在这个例子中，`procedure_2` 接受两个参数`v_i` 和`v_j`，其中`v_i` 是输入参数，`v_j` 是输出参数。

在存储过程内部，对`v_i` 进行运算，并将结果赋值给`v_j`，然后输出运算结果。

调用存储过程的方法如下：```sqlBEGINprocedure_name(arg1 => value1, arg2 => value2);END;```其中，`procedure_name` 是存储过程的名称；`arg1` 和`arg2` 是存储过程的参数，需要指定相应的值。

调用oracle存储过程

调用oracle存储过程

调用oracle存储过程调用Oracle存储过程是通过使用PL/SQL编程语言来实现的。

PL/SQL 是一种过程化的语言，可以用于编写存储过程、函数和触发器等数据库对象。

在本文中，我将为您提供一个简单的示例来演示如何调用Oracle存储过程。

首先，让我们创建一个简单的存储过程，该过程将接受一个参数并在Oracle数据库中插入一条记录。

下面是一个示例存储过程的代码：```CREATE OR REPLACE PROCEDURE insert_datap_name IN VARCHAR2,p_age IN NUMBERASBEGININSERT INTO person (name, age)VALUES (p_name, p_age);COMMIT;DBMS_OUTPUT.PUT_LINE('Data inserted successfully');EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Error occurred: ' ， SQLERRM);END;```上述代码中，我们创建了一个名为insert_data的存储过程，该过程接受两个参数：p_name和p_age。

该过程在person表中插入一条记录，并使用DBMS_OUTPUT.PUT_LINE来输出一些信息。

在异常处理部分，我们使用DBMS_OUTPUT.PUT_LINE输出任何可能发生的错误。

现在我们已经创建了存储过程，我们可以通过使用PL/SQL块来调用它。

下面是一个示例PL/SQL块，演示了如何调用存储过程：```DECLAREv_name VARCHAR2(20) := 'John';v_age NUMBER := 30;BEGINinsert_data(v_name, v_age);END;```通过使用DECLARE关键字，我们声明了两个变量v_name和v_age，并对它们进行了初始化。

Oracle存储过程模板

Oracle存储过程模板

Oracle存储过程模板1、⽆参数存储过程1CREATE OR REPLACE PROCEDURE stu_proc AS2--声明语句段3 v_name VARCHAR2(20);4BEGIN5--执⾏语句段6SELECT o.sname INTO v_name FROM student o WHERE o.id=4;7 dbms_output.put_line(v_name);8 EXCEPTION9--异常处理语句段10WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');11END;2、仅带⼊参的存储过程1CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type) AS2--声明语句段3 v_name varchar2(20);4BEGIN5--执⾏语句段6SELECT o.sname INTO v_name FROM student o where o.id=v_id;7 dbms_output.put_line(v_name);8 EXCEPTION9--异常处理语句段10WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');11END;3、仅带出参的存储过程1CREATE OR REPLACE PROCEDURE stu_proc(v_name OUT student.sname%type) AS2--声明语句段3BEGIN4--执⾏语句段5SELECT o.sname INTO v_name FROM student o where o.id=1;6 dbms_output.put_line(v_name);7 EXCEPTION8--异常处理语句段9WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');10END;4、带⼊参和出参的存储过程1--此种存储过程不能直接⽤call来调⽤，这种情况的调⽤将在下⾯oracle函数调⽤中说明2CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type, v_name OUT student.sname%type) AS 3--声明语句段4BEGIN5--执⾏语句段6SELECT o.sname INTO v_name FROM student o where o.id=v_id;7 dbms_output.put_line(v_name);8 EXCEPTION9--异常处理语句段10WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');11END;。

oracle存储过程及实现代码

oracle存储过程及实现代码
try{
con = getCon();
//调用存储过程
cs = con.prepareCall("{call ysf_login(?,?,?)}");
cs.setString(1, user.getUserName());
cs.setString(2, user.getPassword());
存储过程：
优势：效率高，速度快。但可维护性差，可移植性差。
存储过程有三种参数类型：输入参数IN，输出参数OUT，输入输出INOUT。
创建视图：
create or replace view ysf_view as
SELECT a.deptno,b.sal,b.ename FROM
(select avg(sal),deptno from emp group by deptno having avg(sal) > 4000)A INNER JOIN
p_para4 := V_PARA5;
end;
end;
--4、for循环
CREATE OR REPLACE PROCEDURE ysf_TEST1
(tid IN NUMBER)
IS
-- 游标
CURSOR c1 is SELECT title, content FROM b_topic WHERE t_id=tid;
ELSIF (p.content IS NULL) THEN
dbms_output.put_line('空的');
ELSE
dbms_output.put_line(p.title || '-->' || p.content);

oracle存储过程的创建和使用方法

oracle存储过程的创建和使用方法

oracle存储过程的创建和使用方法Oracle存储过程是一组预编译SQL语句，通过一个名称调用执行。

以下是Oracle存储过程的创建和使用方法：1. 创建存储过程：sqlCopy codeCREATE OR REPLACE PROCEDURE procedure_name (parameter_name1 IN data_type, parameter_name2 OUT data_type, ...) IS -- 声明变量 BEGIN -- SQL语句 EXCEPTION -- 异常处理 END procedure_name;其中，parameter_name1和parameter_name2为存储过程的输入和输出参数。

声明变量和编写SQL语句的代码都应该放在BEGIN和END之间。

2. 调用存储过程：scssCopy codeEXEC procedure_name(parameter_name1, parameter_name2, ...);其中，parameter_name1和parameter_name2是存储过程的输入和输出参数。

使用EXEC语句调用存储过程。

3. 示例：以下是一个简单的Oracle存储过程示例，该存储过程将向一个名为employee的表中插入新的记录：sqlCopy codeCREATE OR REPLACE PROCEDURE insert_employee ( emp_id IN NUMBER, emp_name IN VARCHAR2, emp_salary IN NUMBER,emp_department IN VARCHAR2) IS BEGIN INSERT INTO employee (employee_id, employee_name, employee_salary, employee_department) VALUES (emp_id, emp_name, emp_salary, emp_department); COMMIT; END insert_employee;可以使用以下语句调用该存储过程：arduinoCopy codeEXEC insert_employee(1001, 'John Smith', 5000, 'Sales');这将向employee表中插入一个新的记录，该记录包含员工ID为1001、姓名为John Smith、薪资为5000、部门为销售的信息。

详例实现java调用ORACLE的存储过程代码

详例实现java调用ORACLE的存储过程代码

详例实现java调用ORACLE的存储过程代码本篇文章主要是在java中调用ORACLE存储过程，并把要用到的存储过程和代码列了出来，给大家做一个参考：一：无返回值的存储过程存储过程为：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN V ARCHAR2) AS BEGININSERT INTO HYQ.B_ID (I_ID,I_NAME) V ALUES (PARA1, PARA2);END TESTA;然后呢，在java里调用时就用下面的代码：package com.hyq.src;import java.sql.*; import java.sql.ResultSet; public class TestProcedureOne {public TestProcedureOne() { }public static void main(String[] args ){String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");CallableStatement proc = null;proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");proc.setString(1, "100");proc.setString(2, "TestOne");proc.execute();}catch (SQLException ex2) { ex2.printStackTrace(); }catch (Exception ex2) { ex2.printStackTrace(); }finally{try {if(rs != null){rs.close();if(stmt!=null){ stmt.close(); }if(conn!=null){ conn.close(); } } }catch (SQLException ex1) { }} } }当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。

Oracle存储过程语法

Oracle存储过程语法

Oracle存储过程语法Oracle存储过程基本语法存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END;⾏1: CREATE OR REPLACE PROCEDURE 是⼀个SQL语句通知Oracle数据库去创建⼀个叫做skeleton存储过程, 如果存在就覆盖它; ⾏2: IS关键词表明后⾯将跟随⼀个PL/SQL体。

⾏3: BEGIN关键词表明PL/SQL体的开始。

⾏4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中⾄少需要有⼀句; ⾏5: END关键词表明PL/SQL体的结束存储过程创建语法： create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; --vs_msg VARCHAR2(4000); 变量2 类型（值范围）;复制代码代码如下:Begin Select count(*) into 变量1 from 表A where列名=param1；If (判断条件) then Select 列名 into 变量2 from 表A where列名=param1； Dbms_output。

Put_line(‘打印信息'); Elsif (判断条件) then Dbms_output。

Put_line(‘打印信息'); Else Raise 异常名（NO_DATA_FOUND）; End if; Exception When others then Rollback; End; 注意事项：1，存储过程参数不带取值范围，in表⽰传⼊，out表⽰输出类型可以使⽤任意Oracle中的合法类型。

2，变量带取值范围，后⾯接分号3，在判断语句前最好先⽤count（*）函数判断是否存在该条操作记录4，⽤select 。

oracle复杂的存储过程代码实例

oracle复杂的存储过程代码实例

oracle复杂的存储过程代码实例
Oracle 数据库的存储过程是一种在数据库中存储复杂程序，这个程序可以被调用执行。

下面是一个 Oracle 存储过程的简单示例，这个存储过程将根据提供的参数更新一个表中的数据：
```sql
CREATE OR REPLACE PROCEDURE update_employee_salary(
p_emp_id IN NUMBER,
p_salary IN NUMBER
) AS
BEGIN
UPDATE employees
SET salary = p_salary
WHERE employee_id = p_emp_id;
COMMIT;
END;
/
```
在这个例子中，我们创建了一个名为 `update_employee_salary` 的存储过程，它接受两个参数：`p_emp_id` 和 `p_salary`。

这个存储过程会更新
`employees` 表中对应 `employee_id` 的员工的工资。

要调用这个存储过程，你可以使用以下 SQL 语句：
```sql
BEGIN
update_employee_salary(1, 5000);
END;
/
```
这个例子是非常基础的，实际使用中的存储过程可能会涉及到更复杂的 SQL 查询和逻辑处理。

在实际使用中，需要根据具体的业务需求来设计和编写存储过程。

oracle 存储过程写法

oracle 存储过程写法

oracle 存储过程写法在Oracle 数据库中，存储过程是一组预定义的SQL 语句集，它们可以在数据库中进行封装和重复使用。

以下是一个简单的Oracle 存储过程的示例：```plsqlCREATE OR REPLACE PROCEDURE example_procedure (p_employee_id IN NUMBER,p_salary_increase IN NUMBER) ASBEGIN--变量声明DECLAREv_current_salary NUMBER;v_new_salary NUMBER;BEGIN--获取当前薪水SELECT salary INTO v_current_salaryFROM employeesWHERE employee_id = p_employee_id;--计算新薪水v_new_salary := v_current_salary + p_salary_increase;--更新薪水UPDATE employeesSET salary = v_new_salaryWHERE employee_id = p_employee_id;--提交事务COMMIT;EXCEPTIONWHEN OTHERS THEN--处理异常ROLLBACK;DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);END;END example_procedure;/```在这个示例中，存储过程名为`example_procedure`，它接受两个输入参数`p_employee_id` 和`p_salary_increase`。

存储过程包含了一些SQL 语句，用于获取员工的当前薪水、计算新薪水并更新数据库中的记录。

存储过程还包含了异常处理部分，以处理可能发生的异常，并回滚事务。

要注意的是，存储过程的具体实现和逻辑会根据业务需求而变化。

oracle存储过程、声明变量、for循环

oracle存储过程、声明变量、for循环

oracle存储过程、声明变量、for循环oracle存储过程、声明变量、for循环1、创建存储过程create or replace procedure test(var_name_1 in type,var_name_2 out type) as--声明变量(变量名变量类型)begin--存储过程的执⾏体end test;打印出输⼊的时间信息E.g:create or replace procedure test(workDate in Date) isbegindbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd'));end test;2、变量赋值变量名 := 值;E.g：create or replace procedure test(workDate in Date) isx number(4,2);beginx :=1;end test;3、判断语句:if ⽐较式 then begin end; end if;E.gcreate or replace procedure test(x in number) isbeginif x >0thenbeginx :=0- x;end;end if;if x =0thenbeginx: =1;end;end if;end test;4、For 循环For ... in ... LOOP--执⾏语句end LOOP;(1)循环遍历游标create or replace procedure test() asCursor cursor is select name from student; name varchar(20);beginfor name in cursor LOOPbegindbms_output.putline(name);end;end LOOP;end test;(2)循环遍历数组create or replace procedure test(varArray in myPackage.TestArray) as--(输⼊参数varArray 是⾃定义的数组类型，定义⽅式见标题6)i number;begini := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语⾔不同。

oracle存储过程代码实例一

oracle存储过程代码实例一

oracle存储过程代码实例一1、用来插入大量测试数据的存储过程CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM IN NUMBER)ISBEGINdeclarei number;beginFOR i IN ST_NUM..ED_NUM LOOPINSERT INTO tb values(i,i,'3','3','3',100,'0');END LOOP;end;END;运行：sql>execute INSERTAMOUNTTEST(1,45000) -- 一次插入45000条测试数据2、从存储过程中返回值create or replace procedure spaddflowdate(varAppTypeId in varchar2,varFlowId in varchar2,DateLength in number,ReturnValue out number --返回值)isbegininsert into td values(varAppTypeId,varFlowId,DateLength)returning 1 into ReturnValue; --返回值commit;exceptionwhen others thenrollback;end;存储过程的执行sql>variable testvalue number;sql>execute spaddflowdate('v','v',2,:testvalue);sql>print就可以看到执行结果3、用包实现存储过程返回游标：create or replace package test_pastype outList is ref cursor;PROCEDURE getinfor(taxpayerList out outList);end test_p;/create or replace package body test_p as PROCEDURE get infor(taxpayerList out outList) is beginOPEN taxpayerList FOR select * fromtd where tag='0';end getinfor;end test_p;/运行：set serverout on; --将输出工具打开variable x refcursor;execute test_p.getinfor(:x);exec test_p.getinfor(:x);print x;drop package test_p;认识存储过程和函数存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。

Springboot调用Oracle存储过程的两种方式及完整代码

Springboot调用Oracle存储过程的两种方式及完整代码

Springboot调⽤Oracle存储过程的两种⽅式及完整代码前⾔因⼯作需要将公司SSH项⽬改为Spingboot项⽬，将项⽬中部分需要调⽤存储过程的部分⽤entityManagerFactory.unwrap(SessionFactory.class).openSession()来获取Session实现后发现项⽬访问数据库超过⼗次就会挂掉，原因是Springboot连接池数量默认为10，猜测是每次访问数据库后连接未释放导致的，⼿动关闭session后问题解决。

解决问题的过程中⼜发现了另外两种调⽤⽅式：直接⽤EntityManager的createStoredProcedureQuery（）⽅法调⽤（推荐）通过如下⽅式获取Session来调⽤，这种⽅式不需要⼿动关闭Session来释放连接，具体原因我也没搞明⽩，有知道的朋友欢迎指点Session session = entityManager.unwrap(Session.class);完整代码package com.hzjd.produre.repository;import javax.persistence.EntityManager;import javax.persistence.EntityManagerFactory;import javax.persistence.ParameterMode;import javax.persistence.PersistenceContext;import javax.persistence.StoredProcedureQuery;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.procedure.ProcedureCall;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import com.hzjd.produre.bean.QueryResponse;import com.hzjd.produre.utils.Assistant;@Repositorypublic class ProdureDAO {public final static String PUBLIC_PAG_SYS_GETNEXTID = "PUBLIC_PAG.SYS_GETNEXTID";public final static String PSBC_QUERYBILL = "PSBCPAY.QUERYBILL";@PersistenceContextEntityManager entityManager;@AutowiredEntityManagerFactory entityManagerFactory;public Session getSession() {return entityManagerFactory.unwrap(SessionFactory.class).openSession();}/*** 使⽤entityManager调⽤存储过程** @param pay_ID* @return*/public QueryResponse queryBill1(String pay_ID) throws Exception {QueryResponse queryResponse = new QueryResponse();StoredProcedureQuery call = entityManager.createStoredProcedureQuery(PSBC_QUERYBILL);call.registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1, pay_ID);call.registerStoredProcedureParameter(2, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(3, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(4, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(5, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(6, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(7, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(8, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(9, String.class, ParameterMode.OUT);call.registerStoredProcedureParameter(10, String.class, ParameterMode.OUT);call.execute();queryResponse.getBody().setPAY_ID(pay_ID);queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputParameterValue(2)));queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputParameterValue(3)));queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputParameterValue(5)));queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputParameterValue(6)));queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputParameterValue(8)));int errorcode = Assistant.nullToInt(call.getOutputParameterValue(9));String errormsg = Assistant.nullToEmpty(call.getOutputParameterValue(10));if (errorcode == 0) {return queryResponse;} else {throw new Exception(errormsg);}}/*** 使⽤sessionFactory开启Session调⽤存储过程** @param pay_ID* @return*/public QueryResponse queryBill2(String pay_ID) throws Exception {QueryResponse queryResponse = new QueryResponse();// 调⽤完成后需关闭Session否则会出现连接失效try (Session session = getSession();) {ProcedureCall call = session.createStoredProcedureCall(PSBC_QUERYBILL);call.registerParameter(1, String.class, ParameterMode.IN).bindValue(pay_ID);call.registerParameter(2, String.class, ParameterMode.OUT);call.registerParameter(3, String.class, ParameterMode.OUT);call.registerParameter(4, String.class, ParameterMode.OUT);call.registerParameter(5, String.class, ParameterMode.OUT);call.registerParameter(6, String.class, ParameterMode.OUT);call.registerParameter(7, String.class, ParameterMode.OUT);call.registerParameter(8, String.class, ParameterMode.OUT);call.registerParameter(9, String.class, ParameterMode.OUT);call.registerParameter(10, String.class, ParameterMode.OUT);queryResponse.getBody().setPAY_ID(pay_ID);queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(2))); queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(3))); queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(5))); queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(6))); queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(8)));int errorcode = Assistant.nullToInt(call.getOutputs().getOutputParameterValue(9));String errormsg = Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(10));if (errorcode == 0) {return queryResponse;} else {throw new Exception(errormsg);}}}/*** 使⽤sessionFactory开启Session调⽤存储过程** @param pay_ID* @return*/public QueryResponse queryBill3(String pay_ID) throws Exception {QueryResponse queryResponse = new QueryResponse();Session session = entityManager.unwrap(Session.class);ProcedureCall call = session.createStoredProcedureCall(PSBC_QUERYBILL);call.registerParameter(1, String.class, ParameterMode.IN).bindValue(pay_ID);call.registerParameter(2, String.class, ParameterMode.OUT);call.registerParameter(3, String.class, ParameterMode.OUT);call.registerParameter(4, String.class, ParameterMode.OUT);call.registerParameter(5, String.class, ParameterMode.OUT);call.registerParameter(6, String.class, ParameterMode.OUT);call.registerParameter(7, String.class, ParameterMode.OUT);call.registerParameter(8, String.class, ParameterMode.OUT);call.registerParameter(9, String.class, ParameterMode.OUT);call.registerParameter(10, String.class, ParameterMode.OUT);queryResponse.getBody().setPAY_ID(pay_ID);queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(2))); queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(3))); queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(5))); queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(6))); queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(8)));int errorcode = Assistant.nullToInt(call.getOutputs().getOutputParameterValue(9));String errormsg = Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(10));if (errorcode == 0) {return queryResponse;} else {throw new Exception(errormsg);}}}总结到此这篇关于Spring boot调⽤Oracle存储过程的两种⽅式及完整代码的⽂章就介绍到这了,更多相关Springboot调⽤Oracle存储过程内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。

oracle的存储过程写法

oracle的存储过程写法

oracle的存储过程写法Oracle的存储过程写法Oracle是一款非常常用的关系型数据库管理系统，其存储过程是一种非常重要的功能。

下面我们来看一下Oracle的存储过程写法。

1. 创建存储过程在Oracle中，创建存储过程需要使用CREATE PROCEDURE语句。

下面是一个简单的例子：CREATE PROCEDURE my_procedureASBEGIN-- 存储过程的代码END;在这个例子中，我们创建了一个名为my_procedure的存储过程，其中存储过程的代码写在BEGIN和END之间。

2. 存储过程的参数存储过程可以接受参数，这些参数可以是输入参数、输出参数或输入/输出参数。

下面是一个接受输入参数的例子：CREATE PROCEDURE my_procedure(input_param IN VARCHAR2)ASBEGIN-- 存储过程的代码在这个例子中，我们定义了一个名为input_param的输入参数。

3. 存储过程的返回值存储过程可以返回一个值，这个值可以是一个标量值或一个游标。

下面是一个返回标量值的例子：CREATE PROCEDURE my_procedure(output_param OUT NUMBER)ASBEGINSELECT COUNT(*) INTO output_param FROM my_table;END;在这个例子中，我们定义了一个名为output_param的输出参数，并将一个查询结果赋值给这个参数。

4. 存储过程的异常处理存储过程中可能会出现异常，我们需要对这些异常进行处理。

下面是一个异常处理的例子：CREATE PROCEDURE my_procedureASBEGINBEGIN-- 存储过程的代码EXCEPTIONWHEN OTHERS THEN-- 异常处理的代码END;在这个例子中，我们使用BEGIN和END将存储过程的代码括起来，并使用EXCEPTION关键字来处理异常。

常用的oracle存储过程.

常用的oracle存储过程.

create or replace procedure P_DM_VOICE_TOT_M10(V_ACCT_DATE VARCHAR2, V_RETCODE OUT VARCHAR2, V_RETINFO OUT VARCHAR2 IS /*@ **************************************************************** *名称 --%@NAME:P_DM_VOICE_TOT_M10 *功能描述 --%@COMMENT:移动业务订购实例短信汇总信息(2G-日 *执行周期 --%@PERIOD:月 *参数 --%@PARAM:V_ACCT_DAY 执行时间,格式YYYYMMDD *参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志（SUCCESS/FAIL） *参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述 *创建人 --%@CREATOR: ghm *创建时间 --%@CREATED_TIME:2012-07-17 *备注 --%@REMARK: *来源表--%@FROM: BDWA.DM_VOICE_TOT_M10@ODSDB *目标表 --%TO:DM_VOICE_TOT_M10 *修改记录 --%@MODIFY: 姓名日期修改说明****************************************************************** @*/ V_ROWCOUNT NUMBER; --用于保存记录数 V_PKG VARCHAR2(40; --过程包名V_PROCNAME VARCHAR2(100; --过程名 V_TIME DATE; ----当前时间，用于保存过程开始执行时间 V_LOG_MSG VARCHAR2(100; ----日志内容 V_STRSQL VARCHAR2(1000; V_ACCT_MONTH varchar2(6; V_ACCT_DAY varchar2(2; BEGIN V_PKG := 'SRC'; V_PROCNAME := 'SRC.P_DM_VOICE_TOT_M10';V_TIME := SYSDATE; V_LOG_MSG := '汇表'; /*V_ACCT_MONTH :=substr(V_ACCT_DATE, 1, 6; V_ACCT_DAY := substr(V_ACCT_DATE, 7, 2;*/ --插入开始日志 SRC.P_INSERT_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME,V_LOG_MSG, V_TIME, 'DM_VOICE_TOT_M10'; --删除当期数据 V_STRSQL :='truncate TABLE DM_VOICE_TOT_M10'; EXECUTE IMMEDIATE V_STRSQL; COMMIT; --插入数据 INSERT /*+APPEND*/ INTO DM_VOICE_TOT_M10 SELECT * FROM bi_rw.DM_VOICE_TOT_M10@ODSDB T; --获得记录数V_ROWCOUNT := SQL%ROWCOUNT; COMMIT; --过程执行成功,更新日志V_RETCODE := 'SUCCESS'; V_RETINFO := '结束'; -- 变更日志信息SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE, V_RETINFO, SYSDATE, V_ROWCOUNT; --异常 EXCEPTION WHEN OTHERSTHEN V_RETCODE := 'FAIL'; V_RETINFO := SQLERRM;SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE, V_RETINFO, SYSDATE, V_ROWCOUNT; end P_DM_VOICE_TOT_M10; ------------------------------ ----------------------------- create or replace procedureP_DM_VOICE_TOT_M11(V_ACCT_DATE VARCHAR2, V_RETCODE OUT VARCHAR2,V_RETINFO OUT VARCHAR2 IS /*@**************************************************************** *名称 --%@NAME:P_DM_VOICE_TOT_M11 *功能描述 --%@COMMENT:移动业务订购实例短信汇总信息(2G-日 *执行周期 --%@PERIOD:月 *参数 --%@PARAM:V_ACCT_DAY 执行时间,格式YYYYMMDD *参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志（SUCCESS/FAIL） *参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述 *创建人 --%@CREATOR: ghm *创建时间 --%@CREATED_TIME:2012-07-17 *备注 --%@REMARK: *来源表--%@FROM: BDWA.DM_VOICE_TOT_M11@ODSDB *目标表 --%TO:DM_VOICE_TOT_M11 *修改记录 --%@MODIFY: 姓名日期修改说明****************************************************************** @*/ V_ROWCOUNT NUMBER; --用于保存记录数 V_PKG VARCHAR2(40; --过程包名V_PROCNAME VARCHAR2(100; --过程名 V_TIME DATE; ----当前时间，用于保存过程开始执行时间 V_LOG_MSG VARCHAR2(100; ----日志内容 V_STRSQL VARCHAR2(1000; V_ACCT_MONTH varchar2(6; V_ACCT_DAY varchar2(2; BEGIN V_PKG := 'SRC'; V_PROCNAME := 'SRC.P_DM_VOICE_TOT_M11';V_TIME := SYSDATE; V_LOG_MSG := '汇表'; /*V_ACCT_MONTH :=substr(V_ACCT_DATE, 1, 6; V_ACCT_DAY := substr(V_ACCT_DATE, 7, 2;*/ --插入开始日志 SRC.P_INSERT_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME,V_LOG_MSG, V_TIME, 'DM_VOICE_TOT_M11'; --删除当期数据 V_STRSQL :='truncate TABLE DM_VOICE_TOT_M11'; EXECUTE IMMEDIATE V_STRSQL; COMMIT; --插入数据 INSERT /*+APPEND*/ INTO DM_VOICE_TOT_M11SELECT * FROM bi_rw.DM_VOICE_TOT_M11@ODSDB T; --获得记录数V_ROWCOUNT := SQL%ROWCOUNT; COMMIT; --过程执行成功,更新日志V_RETCODE := 'SUCCESS'; V_RETINFO := '结束'; -- 变更日志信息SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE,V_RETINFO, SYSDATE, V_ROWCOUNT; --异常 EXCEPTION WHEN OTHERS THEN V_RETCODE := 'FAIL'; V_RETINFO := SQLERRM;SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE,V_RETINFO, SYSDATE, V_ROWCOUNT; end P_DM_VOICE_TOT_M11; ------------------------------------- ----------------------------------- create or replace procedureP_DM_VOICE_TOT_M12(V_ACCT_DATE VARCHAR2, V_RETCODE OUT VARCHAR2, V_RETINFO OUT VARCHAR2 IS /*@**************************************************************** *名称 --%@NAME:P_DM_VOICE_TOT_M12 *功能描述 --%@COMMENT:移动业务订购实例短信汇总信息(2G-日 *执行周期 --%@PERIOD:月 *参数 --%@PARAM:V_ACCT_DAY 执行时间,格式YYYYMMDD *参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志（SUCCESS/FAIL） *参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述*创建人 --%@CREATOR: ghm *创建时间 --%@CREATED_TIME:2012-07-17 *备注--%@REMARK: *来源表 --%@FROM: BDWA.DM_VOICE_TOT_M12@ODSDB *目标表 --%TO: DM_VOICE_TOT_M12 *修改记录 --%@MODIFY: 姓名日期修改说明 ****************************************************************** @*/ V_ROWCOUNT NUMBER; --用于保存记录数 V_PKG VARCHAR2(40; --过程包名 V_PROCNAME VARCHAR2(100; --过程名 V_TIME DATE; ----当前时间，用于保存过程开始执行时间 V_LOG_MSG VARCHAR2(100; ----日志内容 V_STRSQL VARCHAR2(1000; V_ACCT_MONTH varchar2(6; V_ACCT_DAY varchar2(2; BEGIN V_PKG := 'SRC'; V_PROCNAME := 'SRC.P_DM_VOICE_TOT_M12';V_TIME := SYSDATE; V_LOG_MSG := '汇表'; /*V_ACCT_MONTH :=substr(V_ACCT_DATE, 1, 6; V_ACCT_DAY := substr(V_ACCT_DATE, 7, 2;*/ --插入开始日志 SRC.P_INSERT_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME,V_LOG_MSG, V_TIME, 'DM_VOICE_TOT_M12'; --删除当期数据 V_STRSQL :='truncate TABLE DM_VOICE_TOT_M12'; EXECUTE IMMEDIATE V_STRSQL; COMMIT; --插入数据 INSERT /*+APPEND*/ INTO DM_VOICE_TOT_M12 SELECT * FROM bi_rw.DM_VOICE_TOT_M12@ODSDB T; --获得记录数V_ROWCOUNT := SQL%ROWCOUNT; COMMIT; --过程执行成功,更新日志V_RETCODE := 'SUCCESS'; V_RETINFO := '结束'; -- 变更日志信息SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE,V_RETINFO, SYSDATE, V_ROWCOUNT; --异常 EXCEPTION WHEN OTHERS THEN V_RETCODE := 'FAIL'; V_RETINFO := SQLERRM;SRC.P_UPDATE_LOG(V_ACCT_DATE, V_PKG, V_PROCNAME, V_RETCODE,V_RETINFO, SYSDATE, V_ROWCOUNT; end P_DM_VOICE_TOT_M12; ------------------------------------ ------------------------------------ create or replace procedurep_DM_VOICE_TOT_M20( V_RETCODE OUT VARCHAR2, V_RETINFO OUT VARCHAR2 IS /*@**************************************************************** *名称 --%@NAME:p_DM_VOICE_TOT_M20 *功能描述 --%@COMMENT:统计 *执行周期--%@PERIOD:日 *参数 --%@PARAM:V_ACCT_DAY 执行时间,格式YYYYMMDD *参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志（SUCCESS/FAIL） *参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述 *创建人 --%@CREATOR: 宋勇 *创建时间 --%@CREATED_TIME:2012-08-01 *备注 --%@REMARK: *来源表 --%@FROM: *目标表 --%TO: *修改记录 --%@MODIFY: 姓名日期修改说明****************************************************************** @*/ V_ROWCOUNT NUMBER; --用于保存记录数 V_PKG VARCHAR2(40; --过程包名V_PROCNAME VARCHAR2(100; --过程名 V_TIME DATE; ----当前时间，用于保存过程开始执行时间 V_LOG_MSG VARCHAR2(100; ----日志内容 V_STRSQL VARCHAR2(1000; BEGIN V_PKG := 'SRC'; V_PROCNAME :='P_DM_VOICE_TOT_M20'; V_TIME := SYSDATE; V_LOG_MSG := '统计标准差'; --插入开始日志 SRC.P_INSERT_LOG(' ', V_PKG, V_PROCNAME, V_LOG_MSG,V_TIME, 'p_DM_VOICE_TOT_M20'; V_STRSQL := 'truncate tableDM_VOICE_TOT_M20'; EXECUTE IMMEDIATE V_STRSQL; COMMIT; INSERT Into DM_VOICE_TOT_M20 SELECT B.*, A.CDR_DENS_STD1,A.DUR_DENS_STD1, A.OPP_MOBILE_STD1, A.CDR_DENS_CV1,A.DUR_DENS_CV1, A.OPP_MOBILE_CV1 FROM (SELECT t.SVC_ID,F_BYTESTD(t.CDR_DENS CDR_DENS_STD1, F_BYTESTD(t.DUR_DENSDUR_DENS_STD1, F_BYTESTD(t.OPP_MOBILE OPP_MOBILE_STD1,F_BYTECV(t.CDR_DENS CDR_DENS_CV1, F_BYTECV(t.DUR_DENSDUR_DENS_CV1, F_BYTECV(t.OPP_MOBILE OPP_MOBILE_CV1 FROMsrc.DM_VOICE_TOT_M10 T A, (SELECT t.SVC_ID, F_BYTESTD(t.CDR_DENS CDR_DENS_STD, F_BYTESTD(t.DUR_DENS DUR_DENS_STD,F_BYTESTD(t.OPP_MOBILE OPP_MOBILE_STD, F_BYTECV(t.CDR_DENSCDR_DENS_CV, F_BYTECV(t.DUR_DENS DUR_DENS_CV,F_BYTECV(t.OPP_MOBILE OPP_MOBILE_CV, t.FST_DATE, t.LST_DATE,t.GENT_TAG, 30 - F_BYTECNT(t.CDR_DENS, '0' CDR_DENS_CNT, 30 -F_BYTECNT(t.DUR_DENS, '0' DUR_DENS_CNT, 30 -F_BYTECNT(t.OPP_MOBILE, '0' OPP_MOBILE_CNT, F_BYTESUM(t.DUR_DENS / decode(F_BYTESUM(t.CDR_DENS,0,1,F_BYTESUM(t.CDR_DENS DUR_CDR,F_BYTESUM(t.CDR_DENS / 30 CDR_30, F_BYTESUM(t.DUR_DENS / 30 DUR_30, F_BYTESUM(t.CDR_DENS / DECODE((30 - F_BYTECNT(t.CDR_DENS, '0',0,1,(30 - F_BYTECNT(t.CDR_DENS, '0' CDR_CNT, F_BYTESUM(DUR_DENS /DECODE((30 - F_BYTECNT(t.DUR_DENS, '0',0,1,(30 - F_BYTECNT(t.DUR_DENS, '0' DUR_CNT FROM src.DM_VOICE_TOT_M13 T B WHERE A.SVC_ID =B.SVC_ID; --获得记录数 V_ROWCOUNT := SQL%ROWCOUNT; COMMIT; --过程执行成功,更新日志 V_RETCODE := 'SUCCESS '; V_RETINFO := '结束 '; -- 变更日志信息 SRC.P_UPDATE_LOG(' ', V_PKG,V_PROCNAME, V_RETCODE, V_RETINFO, SYSDATE, V_ROWCOUNT; --异常EXCEPTION WHEN OTHERS THEN V_RETCODE := ' FAIL '; V_RETINFO := SQLERRM; SRC.P_UPDATE_LOG(' ', V_PKG, V_PROCNAME, V_RETCODE, V_RETINFO, SYSDATE, V_ROWCOUNT; END;。

ORACLE中用存储过程生成SQL代码

ORACLE中用存储过程生成SQL代码

ORACLE中用存储过程生成SQL代码ORACLE中用存储过程生成增删改查的SQL代码的方法在我们日常软件开发过程中，经常需要跟数据库打交道，比如基本的存、取数据都需要对表进行INSERT、SELECT操作，由于这类操作的语法格式基本相同，但是每个表的字段又不一样，如果手工去写这些SQL语句，不仅效率较低，而且容易出错，为此，本文开发了一种简易的SQL代码生成方法。

本方法的优点在于：不需要安装任何工具，而是通过直接在数据库新增一个存储过程，只需在数据库运行下列代码，就可以看到存储过程多了一个AA_GENDML，如图：然后就可以使用该存储过程来生成各种基本操作的SQL代码了，在PL DEVELOPER中打开SQL WINDOW，输入如下语句点击执行后在OUTPUT页面里就可以看到生成的SQL代码了，想要那一段，就拷到你的程序里吧，够简单吧：)* 功能描述：生成基于表的增删改查的SQL代码,SELECT可以从INSERT语句中摘取,UPDATE可以从MERGE语句中摘取* 输入参数：IV_NAME-表名* 输出参数：* 备注：使用方法-在sql widow中用BEGINAA_GENDML('T_A'); END;执行后看OUTPUT里生成的代码*------------------------------------------------------------* 修改历史* 修改原因@日期 BY************************************************* ************/ V_NAME VARCHAR2(50); --表名V_STR_COLUMN VARCHAR2(4000); --表中各列用'，'拼接成的字符串，每5列一行V_STR_ACOLUMN VARCHAR2(4000);--表中各列用'，'拼接成的字符串，列名前面加'A.'，每5列一行V_STR_SET VARCHAR2(4000);--UPDATE中的set部分字符串V_STR_INSERT VARCHAR2(4000);--插入表的语句V_STR_MERGE VARCHAR2(4000);--MERGE表的语句V_COUNT NUMBER;V_N NUMBER;BEGINV_NAME := UPPER(TRIM(IV_NAME));V_STR_COLUMN := NULL;V_STR_ACOLUMN := NULL;V_STR_SET := NULL;V_COUNT := 1;/*-----------------------------------------------------用游标取出该表的各个字段，按顺序组成各字符串-------------------------------------------------------*/FOR V_CUR IN(SELECT CNAME FROM COL WHERETNAME=V_NAME ORDER BY COLNO)LOOPV_STR_COLUMN := V_STR_COLUMN || V_AME || ',';V_STR_ACOLUMN := V_STR_ACOLUMN || 'A.' ||V_AME || ',';V_STR_SET := V_STR_SET || ' T.' || V_AME || ' = A.' || V_AME || ',';IF MOD(V_COUNT,5)=0THENV_STR_COLUMN := V_STR_COLUMN || CHR(10);V_STR_ACOLUMN := V_STR_ACOLUMN || CHR(10);END IF;IF MOD(V_COUNT,40)=0THENV_STR_SET := V_STR_SET || '#';ELSEV_STR_SET := V_STR_SET || CHR(10);END IF;V_COUNT := V_COUNT + 1;END LOOP;V_STR_COLUMN := RTRIM(V_STR_COLUMN,',');V_STR_ACOLUMN := RTRIM(V_STR_ACOLUMN,',');V_STR_SET := RTRIM(V_STR_SET,'#');V_STR_SET := RTRIM(V_STR_SET,CHR(10));V_STR_SET := RTRIM(V_STR_SET,',');/*-----------------------------------------------------输出INSERT语句代码-------------------------------------------------------*/DBMS_OUTPUT.PUT_LINE('/*-----------------INSERT------------------------------');DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------*/');V_STR_INSERT := 'INSERT INTO '|| V_NAME || '('|| CHR(10) || V_STR_COLUMN || ')';V_STR_INSERT := V_STR_INSERT || CHR(10) || 'SELECT '|| V_STR_COLUMN || CHR(10) || 'FROM '|| V_NAME || '_0;';DBMS_OUTPUT.PUT_LINE(V_STR_INSERT);/*-----------------------------------------------------输出MERGE语句代码,UPDATE部分相当于UPDATE语句代码-------------------------------------------------------*/DBMS_OUTPUT.PUT_LINE(CHR(10) ||'/*-----------------MERGE------------------------------');DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------*/');V_STR_MERGE := 'MERGE INTO ' || V_NAME || ' T' || CHR(10) || 'USING ' || V_NAME || '_0 A';V_STR_MERGE := V_STR_MERGE || CHR(10) ||'ON(T.=A.)' || CHR(10) || 'WHEN MATCHED THEN' || CHR(10) || 'UPDATE';V_STR_MERGE := V_STR_MERGE || CHR(10) || 'SET '; DBMS_OUTPUT.PUT_LINE(V_STR_MERGE);LOOPEXIT WHEN V_STR_SET IS NULL;V_N := INSTR(V_STR_SET,'#',1);IF V_N > 0THENDBMS_OUTPUT.PUT_LINE(SUBSTR(V_STR_SET,1,V_N-1));V_STR_SET := SUBSTR(V_STR_SET,V_N + 1);ELSEDBMS_OUTPUT.PUT_LINE(V_STR_SET);V_STR_SET := NULL;END IF;END LOOP;V_STR_MERGE := 'WHEN NOT MATCHED THEN' || CHR(10) || 'INSERT(' || V_STR_COLUMN || ')';。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

/****************************存储过程*******************************/ //序列create sequence idstart with 1increment by 1nomaxvaluenocyclecache 10///后台添加文章信息CREATE OR REPLACE PROCEDURE InsertArticle(title in varchar2,first_id in number,author in varchar2,img in varchar2,content in long) ASBEGININSERT INTO article (id,title,first_id,author,img,ntime,content)V ALUES (id.nextval,title,first_id,author,img,sysdate,content);END InsertArticle;///后台文章的修改create or replace procedure updateArticle(tit in varchar2,firstid in number,aut in varchar2,im in varchar2,cont in long,idd in number) isbeginupdate article set title=tit,first_id=firstid,author=aut,img=im,content=contwhere id=idd;end updateArticle;//后台文章删除create or replace procedure deleteArticle(idd in number) isbegindelete from articlewhere id in (idd);end deleteArticle;//首页输出文章标题CREATE OR REPLACE PROCEDURE searchArticle (title out varchar2) isbeginselect titleinto titlefrom article;end searchArticle;//首页输出单个文章信息CREATE OR REPLACE PROCEDURE seaOneArt (idd in number,title out varchar2,first_id out number,author out varchar2,img out varchar2,content out long) isbeginselect title,first_id,author,img,contentinto title,first_id,author,img,contentfrom article whereid =idd ;end seaOneArt;/// 分类单条(first_id表)CREATE OR REPLACE PROCEDURE seaOnefirst (idd in number,text out varchar2,) isbeginselect textinto textfrom first_id whereid =idd ;end seaOnefirst;//增加链接CREATE OR REPLACE PROCEDURE Insertlink(title in varchar2,url in varchar2,type in number) asbegininsert into url_link (id,title,url,type,num) values(id.nextval,title,url,type,0);end Insertlink;//链接修改create or replace procedure Updatelink(utitle in varchar2, uurl in varchar2,utype in number,idd in number) isbeginupdate url_link set title=utitle,type=utype,url=uurlwhere id=idd;end Updatelink;///链接删除create or replace procedure deletelink(idd in number) is begindelete from url_linkwhere id=idd;end deletelink;//增加行业链接CREATE OR REPLACE PROCEDURE InsertHylink (title in varchar2,url in varchar2,type in number) asbegininsert into hyfl_link (id,title,url,type,num) values(id.nextval,title,url,type,0);end InsertHylink;//行业链接修改create or replace procedure UpdateHylink(utitle in varchar2, uurl in varchar2,utype in number,idd in number) isbeginupdate hyfl_link set title=utitle,type=utype,url=uurl where id=idd;end UpdateHylink;///行业链接删除create or replace procedure deleteHylink(idd in number) is begindelete from hyfl_linkwhere id=idd;end deleteHylink;//图片广告添加create or replace procedure AddGg(title in varchar2,img in varchar2,url in varchar) asbegininsert into g_g (id,img,url,type,title,num) values(id.nextval,img,url,1,title,0);end addGg;//图片广告修改create or replace procedure UpdateGg(uimg in varchar2,utitle in varchar2,uurl in varchar2,uid in number) isbeginupdate g_g set img=uimg,title=utitle,url=uurlwhere id=uid;end UpdateGg;//图片广告删除create or replace procedure DeleteGg(uid in number) isbegindelete from g_gwhere id=uid;end DeleteGg;//增加管理员create or replace procedure addadmin(username in varchar2,passwd in varchar2) asbegininsert into admin (id,username,password,tiem)values (id.nextval,username,passwd,sysdate);end addadmin;//修改管理员create or replace procedure updateadmin(uname in varchar2,upasswd in varchar2,idd in number) isbeginupdate admin set username=uname,password=upasswd where id=idd;end updateadmin;//删除管理员create or replace procedure deleteadmin(idd in number) isbegindelete from admin where id=idd;end deleteadmin;/前台//首页今日关注create or replace procedure todaygz(cont out long) asbeginselect content into cont from articlewhere first_id=3 order by id desc limit 0,1;end todaygz;//更新访问量create or replace procedure addnum(idd in number) asbeginupdate url_link set num=num+1 where id=idd;end addnum;//站内搜索CREATE OR REPLACE PROCEDURE searchurl (text in varchar2,title out varchar2,url out number) isbeginselect title,urlinto title,url_linkfrom link wheretitle like %text% ;end seaOneArt;/*************************函数*****************************///登录函数create or replace function mylogin(name in varchar2,passwd in varchar2,num out number) return numberasnumd number;num number;beginselect count(*) into numdfrom adminwhere username=name and password=passwd;num:=numd;if num=0 thenreturn(num);elsereturn(num);end if;end mylogin;//*********************视图*************************///article与first_id表的视图create or replace view article_id (idd,title,author,first_id,text,ntime)select t1.id,t1.title,t1.author,t1.first_id,t2.text,t1.ntimefrom article t1,first_id t2where t1.first_id=t2.id;/**************************游标***********************/ DECLARECURSOR art_curIS SELECT id,title,first_id,content,time,author,img FROM article; TYPE articles IS RECORD(IDS article.id%type,TITLES article.title%type,FRISTS article.first_id%type,CONS article.content%type,TIMES article.time%type,AUTHORS article.author%type,IMGS article.img%type);art_row articles;BEGINOPEN art_cur;LOOPFETCH art_cur INTO art_row;EXIT WHEN art_cur%notfound;dbms_output.put('编号：'||art_row.IDS);dbms_output.put('//标题：'||art_row.TITLES);dbms_output.put('//类别：'||art_row.FRISTS);dbms_output.put('//内容：'||art_row.CONS);dbms_output.put('//时间：'||art_row.TIMES);dbms_output.put('//作者：'||art_row.AUTHORS);dbms_output.put_line('//图片：'||art_row.IMGS);END LOOP;CLOSE art_cur;END;DECLARECURSOR url_curIS SELECT id,title,url,type,num FROM url_link;TYPE urls IS RECORD(IDS url_link.id%type,TITLES url_link.title%type,URLS url_link.url%type,TYPS url_link.type%type,NUMS url_link.num%typeurl_row urls;BEGINOPEN url_cur;LOOPFETCH url_cur INTO url_row;EXIT WHEN url_cur%notfound;dbms_output.put('编号：'||url_row.IDS);dbms_output.put('//标题：'||url_row.TITLES);dbms_output.put('//地址：'||url_row.URLS);dbms_output.put('//类型：'||url_row.TYPS);dbms_output.put_line('//统计：'||url_row.NUMS);END LOOP;CLOSE url_cur;END;。

相关文档
最新文档