条件分支结构、循环控制结构和存储过程--分页存储过程oracle
条件分支结构、循环控制结构和存储过程--分页存储过程oracle
条件分支结构案例
1、编写一个过程，可以输入一个雇员姓名，如果该雇员的工资低于2000，就给该雇员工资增加10%。
/*
结构：
IF 条件THEN
执行语句;
END IF;
*/
CREATE OR REPLACE PROCEDURE add_sal_prc(p_name IN VARCHAR2) IS
n_sal myemp.sal%TYPE;
BEGIN
SELECT sal INTO n_sal
FROM myemp WHERE ename = p_name;
IF n_sal < 2000 THEN
UPDATE myemp
SET sal = sal * (1 + 0.1)
WHERE ename = p_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('未找到相关信息.');
END add_sal_prc;
/
2、编写一个过程，在myemp表中，如果某个雇员的工资低于2000，就给该雇员工资增加10%。
CREATE OR REPLACE PROCEDURE update_myemp_sal_prc IS CURSOR c_test IS
SELECT * FROM myemp FOR UPDATE;
BEGIN
FOR var_data IN c_test LOOP
IF var_data.sal < 2000 THEN
UPDATE myemp
SET sal = sal * 1.1
WHERE CURRENT OF c_test;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('未找到相关信息.');
END update_myemp_sal_prc;
/
3、编写一个过程，可以输入一个雇员姓名，如果该雇员的奖金不是0，就在原来的基础上增加100，如果奖金是0，就把该奖金设置为200；
/*
结构：
IF 条件THEN
执行语句;
ELSE
执行语句;
END IF;
*/
CREATE OR REPLACE PROCEDURE
update_myemp_user_comm_prc(p_name IN VARCHAR2) IS
comm_tmp https://www.360docs.net/doc/3b4846072.html,m%TYPE;
BEGIN
SELECT NVL(comm, 0) comm
INTO comm_tmp
FROM myemp
WHERE ename = p_name;
IF comm_tmp != 0 THEN
UPDATE myemp
SET comm = comm + 100
WHERE ename = p_name;
ELSE
UPDATE myemp
SET comm = 200
WHERE ename = p_name;
END IF;
END update_myemp_user_comm_prc;
/
4、编写一个过程，在myemp表中，如果该雇员的奖金不是0，就在原来的基础上增加100，如果奖金是0，就把该奖金设置为200；
CREATE OR REPLACE PROCEDURE update_myemp_comm_prc IS CURSOR c_test IS
SELECT ename, sal,
NVL(comm, 0) comm
FROM myemp FOR UPDATE;
BEGIN
FOR var_data IN c_test LOOP
IF var_https://www.360docs.net/doc/3b4846072.html,m != 0 THEN
UPDATE myemp
SET comm = comm + 100
WHERE CURRENT OF c_test;
ELSE
UPDATE myemp
SET comm = 200
WHERE CURRENT OF c_test;
END IF;
END LOOP;
END update_myemp_comm_prc;
/
5、编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT 就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资的增加500，其它职位的雇员的工资增加200;
/*
结构：
IF 条件THEN
执行语句；
ELSIF 条件THEN
执行语句；
.
.
.
ELSE
执行语句；
END IF;
*/
CREATE OR REPLACE PROCEDURE modify_sal_prc(p_empno IN INTEGER) IS
v_job myemp.job%TYPE;
BEGIN
SELECT job INTO v_job
FROM myemp
WHERE empno = p_empno;
IF v_job = 'PRESIDENT' THEN
UPDATE myemp
SET sal = sal + 1000
WHERE empno = p_empno;
ELSIF v_job = 'MANAGER' THEN
UPDATE myemp
SET sal = sal + 500
WHERE empno = p_empno;
ELSE
UPDATE myemp
SET sal = sal + 200
WHERE empno = p_empno;
END IF;
END modify_sal_prc;
/
扩展：如果是对所有雇员的工资进行修改，应该使用游标，请参考第2或第4题。
循环语句
1、循环语句LOOP案例，LOOP循环语句至少会被执行一次；
/*
结构：
LOOP
循环体；
退出循环条件；
END LOOP;
LOOP循环至少会被执行一次
*/
CREATE OR REPLACE PROCEDURE loop_demo_prc(p_name IN VARCHAR2) IS
i_id INTEGER := 1;
BEGIN
LOOP
INSERT INTO tb_login VALUES (i_id, p_name);
i_id := i_id + 1;
EXIT WHEN i_id = 11;
END LOOP;
END loop_demo_prc;
2、循环语句while案例
/*
结构：
while 循环条件LOOP
循环体
END LOOP;
*/
CREATE OR REPLACE PROCEDURE while_demo_prc(p_name IN VARCHAR2) IS
i_id INTEGER := 11;
BEGIN
while i_id < 21 LOOP
INSERT INTO tb_login VALUES (i_id, p_name);
i_id := i_id + 1;
END LOOP;
END while_demo_prc;
3、循环语句for案例
/*
结构：
for 变量IN num1..num2 LOOP
循环体
END LOOP;
说明：num1和num2必须是大于0的整数，且num2>num1
*/
CREATE OR REPLACE PROCEDURE for_demo_prc(p_name IN VARCHAR2) IS
i_id INTEGER := 21;
BEGIN
FOR i IN 21 .. 31 LOOP
INSERT INTO tb_login VALUES (i_id, p_name);
i_id := i_id + 1;
END LOOP;
END for_demo_prc;
/
4、循环语句GOTO案例—建议尽量不要使用
/*
结构：
LOOP
IF 条件THEN
GOTO 标识;
END IF;
改变条件的语句;
其它执行语句;
END LOOP;
<<标识>>
*/
DECLARE
i_num INT := 1;
BEGIN
LOOP
IF i_num > 10 THEN
GOTO flg;
END IF;
DBMS_OUTPUT.put_line('i_num = ' || i_num);
i_num := i_num + 1;
END LOOP;
<
DBMS_OUTPUT.put_line('循环结束');
END;
/
顺序控制语句
1、顺序控制语句—NULL案例;
/*
说明：NULL语句不会执行任何操作，使用
NULL语句的好处主要是提高PL/SQL的可读性
*/
DECLARE
v_name myemp.ename%TYPE;
n_sal myemp.sal%TYPE;
BEGIN
SELECT ename, sal
INTO v_name, n_sal
FROM myemp
WHERE empno = &empno;
IF n_sal < 3000 THEN
UPDATE myemp
SET comm = sal * 0.1
WHERE ename = v_name;
ELSE
NULL;
END IF;
END;
/
分页储存过程
无返回值的存储过程：
1、现有一张表books，表结构如下：
--表books
DROP TABLE books PURGE;
CREATE TABLE books(
book_id NUMBER(4),
book_name VARCHAR2(50),
press VARCHAR2(50),
CONSTRAINT PK_bookID PRIMARY KEY(book_id) );
请编写一个存储过程，可以向表books添加书，要求通过java程序调用该过程。存储过程：add_book_prc()
--存储过程add_book_prc
DROP PROCEDURE add_book_prc;
CREATE PROCEDURE add_book_prc(p_bookID IN NUMBER,
p_bookName IN VARCHAR2,
p_press IN VARCHAR2) IS
BEGIN
INSERT INTO books VALUES(p_bookID,p_bookName,p_press);
END add_book_prc;
/
输入内容过滤
package org.lxh.addbook;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
public class InputData {
private BufferedReader buf = null;
public InputData() {
// 将字节输入流转换为字符流存放在缓冲区中
this.buf = new BufferedReader(new InputStreamReader(System.in));
}
public String getString(String info) {// 读取输入的数据
String str = null;
System.out.print(info);// 打印提示输入的信息
try {
str = this.buf.readLine();// 按行读取
} catch (IOException e) {
System.out.println("读取数据失败！");
}
return str;
}
// 将判断输入的数据是否为整数
public int getInt(String info, String err) {
boolean flag = true;
int temp = 0;
while (flag) {
String str = this.getString(info);
if (str.matches("}")) {
flag = false;
temp = Integer.parseInt(str);
} else {
System.out.print(err);
}
}
return temp;
}
}
调用过程函数，向数据表books中增加数据
package org.lxh.addbook;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class AddBook {
// 加载驱动程序（之前在项目属性中配置的jdbc的驱动程序的jar包中）public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
// 连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public static final String DBURL = "jdbc:oracle:thin:@192.168.1.12:1521:mldn"; // 连接数据库的用户名
public static final String DBUSER = "scott";
// 连接数据库的用户名的密码
public static final String DBPWD = "tiger";
public static void main(String[] args) throws Exception {
// 加载oracle驱动
Class.forName(DBDRIVER);
// 得到连接
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD); // 调用存储过程
CallableStatement cs = con.prepareCall("{call add_book_prc(?,?,?)}"); InputData input = new InputData();
int bookID = input.getInt("请输入书本编号：", "书本编号必须是4位整数,"); String bookName = input.getString("请输入书本名称：");
String press = input.getString("请输入出版社：");
// 设置输入参数的值
cs.setInt(1, bookID);
cs.setString(2, bookName);
cs.setString(3, press);
// 执行SQL操作
cs.execute();
// 关闭数据库资源
cs.close();
con.close();
}
}
有返回值的存储过程：
1、案例：输入雇员的编号，返回该雇员的姓名—有输入输出的存储过程
--有输入和输出的存储过程
CREATE OR REPLACE PROCEDURE get_name_prc(p_empno IN INTEGER, p_name OUT VARCHAR2) IS
BEGIN
SELECT ename INTO p_name FROM emp WHERE empno = p_empno;
END get_name_prc;
调用回值的存储过程方法如下：
--执行有输入和输出的存储过程
DECLARE
n_name emp.ename%Type;
BEGIN
get_name_prc(&empno, n_name);
DBMS_OUTPUT.put_line(n_name);
END;
Java程序调用有输入和输出的存储过程
输入内容过滤，同上。
package org.lxh.getname;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import org.lxh.addbook.InputData;
public class GetName {
// 加载驱动程序（之前在项目属性中配置的jdbc的驱动程序的jar包中）public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
// 连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public static final String DBURL = "jdbc:oracle:thin:@192.168.1.12:1521:mldn"; // 连接数据库的用户名
public static final String DBUSER = "scott";
// 连接数据库的用户名的密码
public static final String DBPWD = "tiger";
public static void main(String[] args) throws Exception {
// 加载oracle驱动
Class.forName(DBDRIVER);
// 得到连接
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD); // 调用存储过程
CallableStatement cs = con.prepareCall("{call get_name_prc(?,?)}");
cs.registerOutParameter(2, Types.VARCHAR);
InputData input = new InputData();
int empno = input.getInt("请输入雇员编号：", "雇员编号必须是4位整数,"); cs.setInt(1, empno);
cs.execute();
String str = cs.getString(2);
cs.close();
con.close();
System.out.println("雇员编号" + empno + "的姓名是：" + str);
}
}
存储过程返回多个值
2、编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和职位。CREATE OR REPLACE PROCEDURE get_info_prc(p_empno IN INTEGER, p_name OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2) IS
BEGIN
SELECT ename, job, sal
INTO p_name, p_job, p_sal
FROM emp
WHERE empno = p_empno;
END get_info_prc;
Java程序
输入内容过滤同上
package org.lxh.getinfo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import org.lxh.addbook.InputData;
public class GetInfo {
// 加载驱动程序（之前在项目属性中配置的jdbc的驱动程序的jar包中）public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
// 连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public static final String DBURL = "jdbc:oracle:thin:@192.168.1.12:1521:mldn"; // 连接数据库的用户名
public static final String DBUSER = "scott";
// 连接数据库的用户名的密码
public static final String DBPWD = "tiger";
public static void main(String[] args) throws Exception {
// 加载oracle驱动
Class.forName(DBDRIVER);
// 得到连接
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD);
// 调用存储过程
CallableStatement cs = con.prepareCall("{call get_info_prc(?,?,?,?)}");
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.NUMERIC);
cs.registerOutParameter(4, Types.VARCHAR);
InputData input = new InputData();
int empno = input.getInt("请输入雇员编号：", "雇员编号必须是4位整数,"); cs.setInt(1, empno);
cs.execute();
String name = cs.getString(2);
double sal = cs.getDouble(3);
String job = cs.getString(4);
cs.close();
con.close();
System.out.println("雇员编号" + empno + "的姓名是：" + name + ",职业：" + job + ",工资：" + sal);
}
}
有返回值的存储过程(列表[结果集])
由于oracle存储过程返回值是由OUT指定的参数代替，只能返回单值的内容。而对于列表、集合，不能用一般的参数，必须要使用package包。
3、案例：编写一个存储过程，输入部门编号，返回该部门所有雇员信息。
建立包test_pkg
CREATE OR REPLACE PACKAGE test_pkg IS
TYPE test_curtype IS REF CURSOR;
END test_pkg;
存储过程：test_prc
CREATE OR REPLACE PROCEDURE test_prc(p_deptno IN INTEGER,
c_test OUT test_pkg.test_curtype) IS
BEGIN
OPEN c_test FOR
SELECT * FROM emp WHERE deptno = p_deptno;
END test_prc;
用oracle块测试集合
DECLARE
c_testprc test_pkg.test_curtype;
v_data emp%ROWTYPE;
BEGIN
test_prc(&deptno, c_testprc);
LOOP
FETCH c_testprc
INTO v_data;
EXIT WHEN c_testprc%NOTFOUND;
DBMS_OUTPUT.put_line(v_data.empno || '/' || v_data.ename); END LOOP;
CLOSE c_testprc;
END;
Java程序
输入内容过滤
package org.lxh.testcursor;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
public class InputData {
private BufferedReader buf = null;
public InputData() {
// 将字节输入流转换为字符流存放在缓冲区中
this.buf = new BufferedReader(new InputStreamReader(System.in)); }
public String getString(String info) {// 读取输入的数据
String str = null;
System.out.print(info);// 打印提示输入的信息
try {
str = this.buf.readLine();// 按行读取
} catch (IOException e) {
System.out.println("读取数据失败！");
}
return str;
}
// 将判断输入的数据是否为整数
public int getInt(String info, String err) {
boolean flag = true;
int temp = 0;
while (flag) {
String str = this.getString(info);
if (str.matches("}")) {
flag = false;
temp = Integer.parseInt(str);
} else {
System.out.print(err);
}
}
return temp;
}
}
通过设置存储过程的输出参数为OracleTypes.CURSOR类型，获取结果集package org.lxh.testcursor;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;
public class TestCursor {
// 加载驱动程序（之前在项目属性中配置的jdbc的驱动程序的jar包中）public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
// 连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public static final String DBURL = "jdbc:oracle:thin:@192.168.1.12:1521:mldn"; // 连接数据库的用户名
public static final String DBUSER = "scott";
// 连接数据库的用户名的密码
public static final String DBPWD = "tiger";
public static void main(String[] args) throws Exception {
// 加载oracle驱动
Class.forName(DBDRIVER);
// 得到连接
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD); // 调用存储过程
CallableStatement cs = con.prepareCall("{call test_prc(?,?)}");
// 设置存储过程参数的类型
cs.registerOutParameter(2, OracleTypes.CURSOR);
// 设置输入参数的值
InputData input = new InputData();
int deptno = input.getInt("请输入部门编号：", "部门编号必须是2位整数,"); cs.setInt(1, deptno);
// 执行SQL操作
cs.execute();
// 取得结果集
ResultSet rs = (ResultSet) cs.getObject(2);
// 输出结果集的部分内容
while (rs.next()) {
System.out.println("雇员编号：" + rs.getInt(1) + ",姓名："
+ rs.getString(2) + ",职业：" + rs.getString("JOB"));
}
// 关闭数据库资源
rs.close();
cs.close();
con.close();
}
}
分页存储过程
请编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数、和返回的结果集。
--分页存储过程
/*
输入参数：
p_table_name：表名
p_records_per_page：每页显示的记录
p_row_amount：表中总的记录数
p_page_amount：表示总页数
c_test：REF CURSOR游标变量，no return type
c_test属于以下的游标类型
CREATE OR REPLACE PACKAGE test_pkg IS
TYPE test_curtype IS REF CURSOR;
END test_pkg;
v_sql：专门处理的SQL语句
i_current_page_first_record：表示当前页的第一条记录
EXECUTE IMMEDIATE 'SQL语句' [INTO 变量] ：
表示执行SQL语句，可以把查询结果存在变量中,
该变量只能存储单行或单个数据，不能存储多行数据，
如果确实要存储多行数据，请使用显示游标或REF游标。
*/
CREATE OR REPLACE PROCEDURE test_paging_prc(p_table_name IN VARCHAR2,
p_records_per_page IN INTEGER,
p_current_page IN INTEGER,
p_row_amount OUT INTEGER,
p_page_amount OUT INTEGER,
c_test OUT test_pkg.test_curtype) IS
v_sql VARCHAR2(1000);
i_current_page_first_record INTEGER;
BEGIN
i_current_page_first_record := (p_current_page - 1) * p_records_per_page + 1; v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
--取得总记录数
EXECUTE IMMEDIATE v_sql
INTO p_row_amount;
--取得总页数
IF (MOD(p_row_amount,p_records_per_page) = 0) THEN
p_page_amount := p_row_amount / p_records_per_page;
ELSE
p_page_amount := trunc(p_row_amount / p_records_per_page) + 1;
END IF;
IF p_current_page = p_page_amount THEN
v_sql := 'SELECT *
FROM (SELECT ROWNUM i_rowid, temp.*
FROM (SELECT * FROM ' || p_table_name ||
') temp
WHERE ROWNUM <= ' || p_row_amount || ')tmp
WHERE tmp.i_rowid >=' || i_current_page_first_record;
OPEN c_test FOR v_sql;
ELSE
v_sql := 'SELECT *
FROM (SELECT ROWNUM i_rowid, temp.*
FROM (SELECT * FROM ' || p_table_name ||
') temp
WHERE ROWNUM <=' || p_current_page * p_records_per_page || ')tmp WHERE tmp.i_rowid >=' || i_current_page_first_record;
OPEN c_test FOR v_sql;
END IF;
END test_paging_prc;
/
java程序
package org.lxh.testpageprc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;
public class TestPage {
// 加载驱动程序（之前在项目属性中配置的jdbc的驱动程序的jar包中）public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
// 连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public static final String DBURL = "jdbc:oracle:thin:@192.168.1.12:1521:mldn"; // 连接数据库的用户名
public static final String DBUSER = "scott";
// 连接数据库的用户名的密码
public static final String DBPWD = "tiger";
public static void main(String[] args) throws Exception {
// 加载oracle驱动
Class.forName(DBDRIVER);
// 得到连接
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD); // 调用存储过程
CallableStatement cs = con
.prepareCall("{call test_paging_prc(?,?,?,?,?,?)}");
cs.setString(1, "myemp");
cs.setInt(2, 4);
cs.setInt(3, 3);
cs.registerOutParameter(4, OracleTypes.INTEGER);
cs.registerOutParameter(5, OracleTypes.INTEGER);
cs.registerOutParameter(6, OracleTypes.CURSOR);
cs.execute();
System.out.println("总记录：" + cs.getInt(4));
System.out.println("总页数：" + cs.getInt(5));
ResultSet rs = (ResultSet) cs.getObject(6);
while (rs.next()) {
System.out.println("序号：" + rs.getInt(1) + " ,雇员编号：" + rs.getInt(2)
+ ",雇员姓名：" + rs.getString(3));
}
rs.close();
cs.close();
con.close();
}
}
ORACLE存储过程
ORACLE提供了四种类型的可存储的程序： 函数, 过程. 包,触发器 一．声明部分(Declarationsection) （1）声明部分包含了变量和常量的数据类型和初始值 （2）这个部分是由关键字DECLARE开始 （3）如果不需要声明变量或常量，那么可以忽略这一部分； 二．执行部分(Executablesection) （1）执行部分是PL/SQL块中的指令部分， （2）由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。 三．异常处理部分(Exceptionsection) 这一部分是可选的，在这一部分中处理异常或错误。 过程存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。 1.创建存储过程 CREATE[ORREPLACE]PROCEDURE过程名 (参数1{IN/OUT/INOUT}类型, 参数2{IN/OUT/INOUT}类型, …….
参数N{IN/OUT/INOUT}类型, )IS/AS 过程体 BEGIN END存储过程名字 说明： （1）ORREPLACE关键字可选，但一般会使用，功能为如果同名的过程已存在，则删除同名过程，然后重建，以此来实现修改过程的目的。 （2）过程可以包括多个参数，参数模式有IN/OUT/INOUT三种，默认为IN,也可以没参数。 （3）IS/AS键字也等价 （4）过程体为该过程的代码部分,是一个含有声明部分，执行部分和异常处理部分的PL/SQL块。但需要注意的是，在过程的声明体中不能使用DECLARE关键字，由IS或AS来代替。 注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出 类型可以使用任意Oracle中的合法类型。 2，变量带取值范围，后面接分号 3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录 4，用select。。。into。。。给变量赋值 5，在代码中抛异常用raise+异常名 2.查看过程
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 。。。into。。。给变量赋值 5，在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) ,
如何在Oracle中使用Java存储过程(详解)
如何在Oracle中使用Java存储过程 (详解) 一、如何缔造java存储过程？ 通常有三种步骤来缔造java存储过程。 1. 使用oracle的sql语句来缔造： e.g. 使用create or replace and compile java source named "" as 后边跟上java源程序。要求类的步骤必须是public static的，威力用于存储过程。 1.SQL> create or replace and compile java source named "javademo1 " 2. as 3. import java.sql.*; 4. public class JavaDemo1 5. { 6. public static void main(String[] argv) 7. { 8. System.out.println("hello, java demo1"); 9. } 10. } 11. / 12. 13.Java 已 14. 15.缔造。 16. 17.SQL> show errors java source "javademo1" 18.没有 19. 20.舛误。 21. 22.SQL> create or replace procedure javademo1 23. 2 as 24.3 language java name JavaDemo1.main(https://www.360docs.net/doc/3b4846072.html,ng.String[]); 25. 4 / 26. 27.过程已 28.
30. 31.SQL> set serveroutput on 32.SQL> call javademo1(); 33. 34.调用 35.实现。 36. 37.SQL> call dbms_java.set_output(5000); 38. 39.调用 40.实现。 41. 42.SQL> call javademo1(); 43.hello, java demo1 44. 45.调用 46.实现。 47. 48.SQL> call javademo1(); 49.hello, java demo1 50.调用 51.实现。 2. 使用外部class文件来装载缔造 e.g. 这里既然用到了外部文件，必定要将class文件放到oracle Server 的某一目录下边。 1.public class OracleJavaProc 2.{ 3. public static void main(String[] argv) 4. { 5. System.out.println("Its a Java Oracle procedure."); 6. } 7.} 8. 9. 10.SQL> grant create any directory to scott; 11. 12.授权 13.顺利。 14. 15.SQL> conn scott/tiger@iihero.oracledb
Oracle存储过程基本结构
1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct wher e xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 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 CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHE RE 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; 8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 关于oracle存储过程的若干问题备忘 1.在oracle中，数据表别名不能加as，如：
oracle存储过程的详细列子说明加分析
文章分类：数据库存储过程创建语法: （1）无参 create or replace procedure 存储过程名as 变量1类型（值范围）； 变量2类型（值范围）； Begin Excepti on End; （2）带参 create or replace procedure 存储过程名（param1 in type，param2 out type）as 变量1类型（值范围）； 变量2类型（值范围）； Begin Select count（*） into 变量1 from 表A where 列名=param1 ； If （判断条件）then Select 列名into 变量2 from 表A where 列名=param1; Dbms_output.Put_line（打印信息'）; Elseif （判断条件）then Dbms_output.Put_line（打印信息'）; Else Raise 异常名（NO DATA FOUND）; End if; Excepti on When others the n Rollback; End; 注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出
2, 变量带取值范围，后面接分号 3, 在判断语句前最好先用count ( *)函数判断是否存在该条操作记录 4, 用select。。。into。。。给变量赋值 5，在代码中抛异常用raise+异常名 以命名的异常 命名的系统异常产生原因 ACCESS INTO NULL 未定义对象 CASE_NOT_FOUND CASE中若未包含相应的WHEN，并且没有设置 ELSE 时 COLLECTION_IS_NULL 集合元素未初始化 CURSER ALREADY OPEN 游标已经打开 DUP VAL ON INDEX 唯一索引对应的列上有重复的值 INVALID_CURSOR 在不合法的游标上进行操作 INVALID_NUMBER 内嵌的SQL语句不能将字符转换为数字 NO_DATA_FOUND 使用select into未返回行，或应用索引表未初始化的 TOO MANY ROWS 执行select into时，结果集超过一行ZERO_DIVIDE 除数为0 SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或VARRAY的最大值
oracle存储过程基本语法
oracle 存储过程的基本语法及注意事项 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 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 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; 8.用pl/sql developer debug
oracle存储过程(语法实例)
Oracl e 存储过程学习资料（内部适用） 目录 Oracle 存储过程1 Oracle存储过程基础知识2 Oracle存储过程的基本语法3 关于Oracle存储过程的若干问题备忘5 1.在Oracle中，数据表别名不能加as。5 2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用 游标的话就另当别论了。5 3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。5 4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会 报错5 5.在存储过程中，关于出现null的问题6 6.Hibernate调用Oracle存储过程6 用Java调用Oracle存储过程总结6 一、无返回值的存储过程7 二、有返回值的存储过程（非列表）8 三、返回列表10 在存储过程中做简单动态查询11 一、本地动态SQL12 二、使用DBMS_SQL包14 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)的区别。function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。 本质上没有区别,都是PL/SQL 程序,都可以有返回值。最根本的区别是：存储过程是命令, 而函数是表达式的一部分。比如： select max(NAME) FROM 但是不能exec max(NAME) 如果此时max是函数。 PACKAGE是function，procedure，variables 和sql 语句的组合。package允许多个procedure使用同一个变量和游标。 创建procedure的语法： 可以使用create or replace procedure 语句，这个语句的用处在于，你之前赋予的excute 权限都将被保留。 IN, OUT, IN OUT用来修饰参数。 IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。 OUT 表示PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。
oracle存储过程详细使用手册整理版
oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 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 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; 8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 Oracle存储过程总结 1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名变量类型) begin --存储过程的执行体 end test; 打印出输入的时间信息 E.g: create or replace procedure test(workDate in Date) is
oracle存储过程学习经典[语法+实例+调用]
Oracl e 存储过程学习 目录 Oracle 存储过程 (1) Oracle存储过程基础知识 (1) Oracle存储过程的基本语法 (2) 关于Oracle存储过程的若干问题备忘 (4) 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)的区别。
oracle存储过程超详细使用手册
Oracle存储过程总结 1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名变量类型) begin --存储过程的执行体 end test; 打印出输入的时间信息 E.g: create or replace procedure test(workDate in Date) is begin dbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-d d)); end test; 2、变量赋值 变量名 := 值; E.g： create or replace procedure test(workDate in Date) is x number(4,2); begin x := 1; end test; 3、判断语句: if 比较式 then begin end; end if; E.g create or replace procedure test(x in number) is begin if x >0 then begin x := 0 - x; end;
end if; if x = 0 then begin x: = 1; end; end if; end test; 4、For 循环 For ... in ... LOOP --执行语句 end LOOP; (1)循环遍历游标 create or replace procedure test() as Cursor cursor is select name from student; name varchar(20); begin for name in cursor LOOP begin dbms_output.putline(name); end; end LOOP; end test; (2)循环遍历数组 create or replace procedure test(varArray in myPackage.TestArray) as --(输入参数varArray 是自定义的数组类型，定义方式见标题6) i number; begin i := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张 --表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 for i in 1..varArray.count LOOP
Oracle存储过程基本语法
Oracle存储过程基本语法 存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skelet on存储过程, 如果存在就覆盖它; 行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 。。。into。。。给变量赋值 5，在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) , the_count OUT NUMBER, ) AS --定义变量 vs_msg VARCHAR2(4000); --错误信息变量 vs_ym_beg CHAR(6); --起始月份 vs_ym_end CHAR(6); --终止月份 vs_ym_sn_beg CHAR(6); --同期起始月份
oracle存储过程代码实例一
oracle存储过程代码实例一 1、用来插入大量测试数据的存储过程 CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST ( ST_NUM IN NUMBER, ED_NUM IN NUMBER ) IS BEGIN declare i number; begin FOR i IN ST_NUM..ED_NUM LOOP INSERT 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 --返回值) is begin insert into td values(varAppTypeId,varFlowId,DateLength) returning 1 into ReturnValue; --返回值 commit; exception when others then
rollback; end; 存储过程的执行 sql>variable testvalue number; sql>execute spaddflowdate('v','v',2,:testvalue); sql>print 就可以看到执行结果 3、用包实现存储过程返回游标： create or replace package test_p as type 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 begin OPEN taxpayerList FOR select * from td where tag='0'; end getinfor; end test_p; / 运行：
ORACLE存储过程详解,教程
第8章存储过程 8.1 存储过程和函数 8.1 存储过程和函数 8.1.1 认识存储过程和函数 存储过程和函数也是一种PL/SQL 块，是存入数据库的 PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程 序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳如下： * 存储过程和函数以命名的数据库对象形式存储于数据库当 中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。* 存储过程和函数可由数据库提供安全保证，要想使用存储 过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。 * 存储过程和函数的信息是写入数据字典的，所以存储过程 可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。 * 像其他高级语言的过程和函数一样，可以传递参数给存储 过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。 存储过程和函数需要进行编译，以排除语法错误，只有编 译通过才能调用。 8.1.2 创建和删除存储过程 创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下： CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数 [IN|OUT|IN OUT] 数据类型...)] {AS|IS} [说明部分] BEGIN 可执行部分 [EXCEPTION 错误处理部分] END [过程名];