基本使用方法及示例
1, 基本结构:
CREATE OR REPLACE PROCEDURE 存储过程名字
- (参数 1 IN NUMBER, 参数 2 IN NUMBER)
- AS
变量 1 INTEGER :=0;
变量 2 DATE;
BEGIN
END 存储过程名字
2, 无参形式的 procedure:
-- 无参 procedure
- create or replace procedure pro_no_param
- is
- begin
- dbms_output.put_line('the procedure without params');
- end pro_no_param;
-- 调用
--one: 无参的 procedure 名字后面必须要 ();
call pro_no_param();
--two:procedure 名称后面可以没有 ();
- begin
- pro_no_param();
- end;
3, 参数类型为 IN 的 procedure:
-- 有参 procedure 只有 IN 类型
- create or replace procedure pro_in_param(
- v_1 in number,
- v_2 in varchar2,
- v_3 in date
- )
- is
- begin
- dbms_output.put_line('v1:' || v_1 || 'v2:' || v_2 || 'v2:'|| (to_char(v_3, 'yyyy-mm-dd')));
- end pro_in_param;
- begin
- pro_in_param(1, 'chy', sysdate);
- end;
4, 参数类型为 OUT 的 procedure:
-- 有参 procedure 只有 OUT 类型
- create or replace procedure pro_out_param(
- v1 out number,
- v2 out char
- )
- is
- begin
- v1 := 2;
- v2 := 'andyChen';
- end pro_out_param;
-- 记得声明用于存放 procedure 的 out 值的变量
-- 语句结束了一定记得结尾的 -- ;
- declare
- v_1 number;
- v_2 varchar2(200);
- begin
- pro_out_param(v_1, v_2);
- dbms_output.put_line('v1:' || v_1 || 'v2:' || v_2);
- end;
5, 参数类型同时为 IN 和 OUT 的 procedure:
-- 同时为 INOUT 参数的 procedure
-- 用同一变量接收传入的值然后将这个变量当作输出的值赋给执行时声明的变量
- create or replace procedure pro_in_out_param(
- in_out_param in out varchar2
- )
- is
- begin
- in_out_param := 'in_out_param and' || in_out_param;
- end pro_in_out_param;
- declare
- in_out_param varchar2(222) := 'detail param';
- begin
- pro_in_out_param(in_out_param);
- dbms_output.put_line(in_out_param);
- end;
三: 实例
- CREATE TABLE user_info
- (
- id VARCHAR2(4) not null primary key,
- name VARCHAR2(15),
- pwd VARCHAR2(15),
- address VARCHAR2(30)
- );
-- 创建一个添加用户的 stored_procedure;
- create or replace procedure pro_addUser(
- n_id user_info.id%type,
- n_name user_info.name%type,
n_pwd user_info.pwd%TYPE,
- n_address user_info.address%TYPE
- )
- as
- begin
-- 插入数据
- insert into user_info(id,name,pwd,address)
- values(n_id, n_name, n_pwd, n_address);
- end pro_addUser;
-- 调用, 有变量需要声明的时候才有 declare, 没有就直接 begin
- begin
- pro_addUser('1', 'chy', 'admin', 'nanjin');
- if SQL%found then
- dbms_output.put_line('add successed');
- end if;
- end;
-- 根据 id 查询用户名和密码
- create or replace procedure pro_getUserInfo(
- n_id user_info.id%type,
- n_name out user_info.name%type,
n_pwd out user_info.pwd%type
- )
- as
- begin
select user_info.name, user_info.pwd into n_name, n_pwd
- from user_info
- where user_info.id=n_id;
- end pro_getUserInfo;
-- 调用
- declare
- v_id user_info.id%type := '1';
- v_name user_info.name%type;
- v_pwd user_info.pwd%type;
- begin
- pro_getUserInfo(v_id, v_name, v_pwd);
- dbms_output.put_line('name:' || v_name || 'pwd:' || v_pwd);
- end;
-- 打印九九乘法表
- create or replace procedure pro_multiplication_table
- is
- i integer;
- j integer;
- begin
- for i in 1..9 loop
- for j in 1..9 loop
- if i>=j then
- DBMS_output.put(To_Char(j)||'*'||to_char(i)||'='||to_char(i*j)||' ');
- end if;
- end loop;
- DBMS_output.put_line('');
- end loop;
- end pro_multiplication_table;
-- 调用
call pro_multiplication_table();
-- 使用自定义游标, 根据工作 and 薪水查询员工姓名
create or replace procedure pro_getName(
n_sal emp.sal%type,
n_ename out emp.ename%type,
n_job in out emp.job%type
- )
- is
- n_count number;
- cursor cur is select ename from emp where emp.sal> n_sal and emp.job=n_job;
- n_row cur%rowtype;
- begin
- select count(*) into n_count from emp where emp.sal> n_sal and emp.job=n_job;
- if n_count> 1 then
- for n_row in cur loop
- DBMS_output.put_line('职工姓名为:'||n_row.ename||'工作为:'||n_job);
- end loop;
- else
- DBMS_output.put_line('未查到符合条件的记录!');
- end if;
- end pro_getName;
-- 调用
- declare
- v_sal emp.sal%type := 2000;
- v_job emp.job%type :='MANAGER';
- v_ename emp.ename%type;
- begin
- pro_getName(v_sal, v_ename, v_job);
- end;
--ref cursor 的使用
-- 创建存放弱引用和强引用的 cursor 的包
- create or replace package refcursor_pkg
- as
- type weak_ref_cursor is ref cursor;
- type strong_ref_cursor is ref cursor return emp%rowtype;
- end refcursor_pkg;
-- 将弱引用的 cursor 作为结果返回
- create or replace procedure test(
- p_deptno in number,
- p_cursor out refcursor_pkg.weak_ref_cursor
- )
- is begin
- open p_cursor for select * from emp where deptno=p_deptno;
- end test;
- /** 或者不用包直接使用下面这种定义
- create or replace procedure test_1(
- p_deptno IN number,
- p_cursor OUT SYS_REFCURSOR
- )
- is
- begin
- open p_cursor FOR select *from emp where deptno = p_deptno;
- end test_1;
- */
- declare
- v_deptno number := 20;
- v_cursor refcursor_pkg.weak_ref_cursor;
- r_emp emp%rowtype;
- begin
- test(v_deptno, v_cursor);
- loop
- fetch v_cursor into r_emp;
- exit when v_cursor%notfound;
- dbms_output.put_line('empno:' || r_emp.empno || 'ename:' || r_emp.ename || 'job:' || r_emp.job);
- end loop;
- close v_cursor;
- end;
- /**//java 中使用 ref cursor
- public void method() throws SQLException{
- Connection conn = getConnection();
- CallableStatement cstmt = null;
- ResultSet rs = null;
- int deptno = 10;
- Object temp;
- try{
- cstmt = conn.prepareCall("begin test(?,?); end;");
- cstmt.setInt(1, deptno);
- cstmt.registerOutParameter(2, OracleTypes.CURSOR);
- cstmt.execute();
- rs = (ResultSet) cstmt.getObject(2);
- ResultSetMetaData rsm = rs.getMetaData();
- int columnCount = rsm.getColumnCount();
- while (rs.next()){
- for (int j=0;j< columnCount;j++){
- temp = rs.getObject(j+1);
- }
- }
- } finally {
- if (!rs==null){
- rs.close();
- }
- if (!stmt==null){
- stmt.close();
- }
- if (!conn==null){
- conn.close();
- }
- }
- }
- */
来源: http://www.bubuko.com/infodetail-2606008.html