条件分支结构、循环控制结构和存储过程--分页存储过程oracle

1、条件分支结构、循环控制结构和存储过程-分页存储过程oracle条件分支结构案例1、编写一个过程，可以输入一个雇员姓名，如果该雇员的工资低于2000，就给该雇员工资增加10%。/*结构：IF 条件 THEN执行语句;END IF;*/CREATE OR REPLACE PROCEDURE add_sal_prc(p_name IN VARCHAR2) ISn_sal myemp.sal%TYPE;BEGINSELECT sal INTO n_sal FROM myemp WHERE ename = p_name;IF n_sal < 2000 THENUPDATE myemp SET sa

2、l = sal * (1 + 0.1) WHERE ename = p_name;END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.put_line('未找到相关信息.');END add_sal_prc;/2、编写一个过程，在myemp表中，如果某个雇员的工资低于2000，就给该雇员工资增加10%。CREATE OR REPLACE PROCEDURE update_myemp_sal_prc ISCURSOR c_test ISSELECT * FROM myemp FOR UPDATE;BEGINFOR var_data

3、 IN c_test LOOPIF var_data.sal < 2000 THENUPDATE myemp SET sal = sal * 1.1 WHERE CURRENT OF c_test;END IF;END LOOP;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.put_line('未找到相关信息.');END update_myemp_sal_prc;/3、编写一个过程，可以输入一个雇员姓名，如果该雇员的奖金不是0，就在原来的根底上增加100，如果奖金是0，就把该奖金设置为200；/*结构：IF 条件 THEN执行语

4、句;ELSE 执行语句;END IF;*/CREATE OR REPLACE PROCEDURE update_myemp_user_comm_prc(p_name IN VARCHAR2) IScomm_tmp myemp m%TYPE;BEGINSELECT NVL(comm, 0) comm INTO comm_tmpFROM myempWHERE ename = p_name;IF comm_tmp != 0 THENUPDATE myemp SET comm = comm + 100 WHERE ename = p_name;ELSEUPDATE myemp SET comm = 2

5、00 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 ISCURSOR c_test ISSELECT ename, sal, NVL(comm, 0) comm FROM myemp FOR UPDATE;BEGINFOR var_data IN c_test LOOPIF var_data m !

6、= 0 THENUPDATE myemp SET comm = comm + 100WHERE CURRENT OF c_test;ELSEUPDATE 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执行语句；.

7、ELSE 执行语句；END IF;*/CREATE OR REPLACE PROCEDURE modify_sal_prc(p_empno IN INTEGER) ISv_job myemp.job%TYPE;BEGINSELECT job INTO v_job FROM myemp WHERE empno = p_empno;IF v_job = 'PRESIDENT' THENUPDATE myemp SET sal = sal + 1000 WHERE empno = p_empno;ELSIF v_job = 'MANAGER' THENUPDATE m

8、yemp SET sal = sal + 500 WHERE empno = p_empno;ELSEUPDATE 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

9、 IN VARCHAR2) ISi_id INTEGER := 1;BEGINLOOPINSERT 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) ISi_id INTEGER := 11;BEGINwhile i_id &

10、lt; 21 LOOPINSERT 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) ISi_id INTEGER := 21;BEGINFOR i IN 21 . 31 LOOPIN

11、SERT INTO tb_login VALUES (i_id, p_name);i_id := i_id + 1;END LOOP;END for_demo_prc;/4、循环语句GOTO案例建议尽量不要使用/*结构：LOOPIF 条件 THENGOTO 标识;END IF;改变条件的语句;其它执行语句;END LOOP;<<标识>>*/DECLAREi_num INT := 1;BEGINLOOPIF i_num > 10 THENGOTO flg;END IF;DBMS_OUTPUT.put_line('i_num = ' | i_num);

12、i_num := i_num + 1;END LOOP;<<flg>>DBMS_OUTPUT.put_line('循环结束');END;/顺序控制语句1、顺序控制语句NULL案例;/*说明：NULL语句不会执行任何操作，使用NULL语句的好处主要是提高PL/SQL的可读性*/DECLAREv_name myemp.ename%TYPE;n_sal myemp.sal%TYPE;BEGINSELECT ename, salINTO v_name, n_sal FROM myemp WHERE empno = &empno;IF n_sal <

13、 3000 THENUPDATE myemp SET comm = sal * 0.1 WHERE ename = v_name;ELSENULL;END IF;END;/分页储存过程无返回值的存储过程：1、现有一张表books，表结构如下：-表booksDROP 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添加书，要求通过j

14、ava程序调用该过程。存储过程：add_book_prc()-存储过程add_book_prcDROP PROCEDURE add_book_prc;CREATE PROCEDURE add_book_prc(p_bookID IN NUMBER,p_bookName IN VARCHAR2,p_press IN VARCHAR2) ISBEGININSERT INTO books VALUES(p_bookID,p_bookName,p_press);END add_book_prc;/输入内容过滤package org.lxh.addbook;import java.io.Buffered

15、Reader;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.ou

16、t.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(""

17、;) 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 Stri

18、ng 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&qu

19、ot;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 = in

20、put.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()

21、;有返回值的存储过程：1、案例：输入雇员的编号，返回该雇员的姓名有输入输出的存储过程-有输入和输出的存储过程CREATE OR REPLACE PROCEDURE get_name_prc(p_empno IN INTEGER,p_name OUT VARCHAR2) ISBEGINSELECT ename INTO p_name FROM emp WHERE empno = p_empno;END get_name_prc;调用回值的存储过程方法如下：-执行有输入和输出的存储过程DECLAREn_name emp.ename%Type;BEGINget_name_prc(&empno

22、, 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包中pub

23、lic 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 D

24、BPWD = "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, Typ

25、es.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、编写一个过程，可以输入雇员的

26、编号，返回该雇员的姓名、工资和职位。CREATE OR REPLACE PROCEDURE get_info_prc(p_empno IN INTEGER,p_name OUT VARCHAR2,p_sal OUT NUMBER,p_job OUT VARCHAR2) ISBEGINSELECT ename, job, salINTO p_name, p_job, p_salFROM empWHERE empno = p_empno;END get_info_prc;Java程序输入内容过滤同上package org.lxh.getinfo;import java.sql.CallableSt

27、atement;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

28、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);/ 得到连接Co

29、nnection 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

30、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

31、+ ",职业：" + job+ ",工资：" + sal);有返回值的存储过程(列表结果集)由于oracle存储过程返回值是由OUT指定的参数代替，只能返回单值的内容。而对于列表、集合，不能用一般的参数，必须要使用package包。3、案例：编写一个存储过程，输入部门编号，返回该部门所有雇员信息。建立包test_pkgCREATE OR REPLACE PACKAGE test_pkg ISTYPE test_curtype IS REF CURSOR;END test_pkg;存储过程：test_prcCREATE OR REPLACE PROCEDU

32、RE test_prc(p_deptno IN INTEGER,c_test OUT test_pkg.test_curtype) ISBEGINOPEN c_test FORSELECT * FROM emp WHERE deptno = p_deptno;END test_prc;用oracle块测试集合DECLAREc_testprc test_pkg.test_curtype;v_data emp%ROWTYPE;BEGINtest_prc(&deptno, c_testprc);LOOPFETCH c_testprcINTO v_data;EXIT WHEN c_testpr

33、c%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 Inp

34、utData() / 将字节输入流转换为字符流存放在缓冲区中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

35、;/ 将判断输入的数据是否为整数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 o

36、rg.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&

37、quot;/ 连接地址是由各个数据库生产商单独提供的，所以需要单独记住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 Excep

38、tion / 加载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

39、 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) + ",职业："

40、+ 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 typec_test属于以下的游标类型CREATE OR REPLACE PACKAG

41、E test_pkg ISTYPE 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,

42、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) ISv_sql VARCHAR2(1000);i_current_page_first_record INTEGER;BEGINi_current_page_first_record := (p_current_page - 1) * p_records_per_page + 1;v_sql := 'SELE

43、CT COUNT(*) FROM ' | p_table_name;-取得总记录数EXECUTE IMMEDIATE v_sqlINTO 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_am

44、ount THENv_sql := 'SELECT *FROM (SELECT ROWNUM i_rowid, temp.*FROM (SELECT * FROM ' | p_table_name |') tempWHERE ROWNUM <= ' | p_row_amount | ')tmpWHERE tmp.i_rowid >=' | i_current_page_first_record;OPEN c_test FOR v_sql;ELSEv_sql := 'SELECT *FROM (SELECT ROWNUM i_rowid, temp.*FROM (SELECT * FROM ' | p_table_name |') tempWHERE ROWNUM <=' | p_current_page * p_records_per_page |