基于oracle数据库存储过程的创建及调用

            declare
            
    v_name 
            varchar2(20):=’zhangsan’;
    v_sal number;
    v_addr varchar2(200);
begin
    v_sal:=1580;
    select ‘ad’ into v_addr from dual;
    DBMS_output.put_line(‘姓名：’|| v_name|| ’薪水：’ || v_sal ||‘地址：’||v_addr);
end;

            declare
            
    v_name vachar2(
            20
            );
    v_sal 
            number
            ;

            begin
            select ename,sal into v_name,v_sal from emp where empno=7839;
    DBMS_output.put_line(‘姓名：’|| v_name || ‘,薪水：’|| v_sal);
end;

            declare
            
    v_name emp.ename
            %
            TYPE;
    v_sal emp.sal
            %
            TYPE;

            begin
            select ename,sal into v_name,v_sal from emp where empno=7839;
    DBMS_output.put_line(‘姓名：’|| v_name || ‘,薪水：’|| v_sal);
end;

            declare
            
    v_emp emp
            %
            ROWTYPE;

            begin
            select
            *
            into v_emp from emp where empno=7839;
    DBMS_output.put_line(‘姓名：’|| v_emp.ename || ‘,薪水：’|| v_emp.sal);
end;

            begin
            if 条件1 then 执行1
    elsif 条件2 then 执行2
    else 执行3
    endif;
end;

            declare
            --
            声明变量接受emp中的数量
    v_count number;
beginselectcount(1) into v_count from emp;
    if v_count>20then DBMS_output.put_line(‘emp表中的记录数超过了20条为：’|| v_count);
    elsif v_count>=10then DBMS_output.put_line(‘emp表中的记录数在10-20条为：’|| v_count);
    else DBMS_output.put_line(‘emp表中的记录数10条以下为：’|| v_count);
    endif;
end;

            begin
            
    loop
    
            exit
            when
             退出循环条件
    
            end
             loop;

            end;

            declare
            
    v_num 
            number:=1;
begin
    loop
    exitwhen v_num>10;
    dbms_output.put_line(v_num);
    v_num+=v_num+1;
    end loop;
end;

            declare
            --
            声明游标
            cursor c_emp isselect ename,sal from emp;
--声明变量用来接受游标中的元素
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin--打开游标open c_emp;
遍历游标
    loop
    
--获取游标中的数据fetch c_emp into v_ename,v_sal;
--退出循坏条件exitwhen c_emp%notfound;
    dbms_output.put_line(‘姓名：’||v_name||’，薪水：’||v_sal);
    end loop;
--关闭游标close c_emp;
end;

            declare
            --
            声明游标
            cursor c_emp(v_deptno emp.deptno%type) isselect ename,sal from emp where deptno=v_deptno;
--声明变量用来接受游标中的元素
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin--打开游标open c_emp;
遍历游标
    loop
--获取游标中的数据fetch c_emp into v_ename,v_sal;
--退出循坏条件exitwhen c_emp%notfound;
    dbms_output.put_line(v_name || v_sal);
    end loop;
--关闭游标close c_emp;
end;

            create
            or
            replace
            procedure 过程名称[(参数列表)]isbeginend[过程名称];

            create
            or
            replace
            procedure p_hello asbegin
    dbms_output.put_line(‘hello word’);
end p_hello;

begin
p_hello;
end;

            create
            or
            replace
            procedure p_querynameandsal(v_empno in emp.empno%type) is--声明变量接受查询结果
    v_name emp.ename%type;
    v_sal emp.sal%type;
begin--查询emp表中某个员工的姓名和薪水并赋值给变量select ename,sal into v_name,v_sal from emp where empno=v_empno;
    dbms_output.put_line(v_name || v_sal);
end;

            declare
            
    i 
            integer
            ;

            begin
            
    p_querynameandsal(
            7839
            );

            end
            exec p_querynameandsal(7839);

            create
            or
            replace
            procedure p_querysal_out(v_empno in emp.empno%type,o_sal out emp.sal%type) asbeginselect sal into o_sal from emp where empno=v_empno;
end;

            declare
            
    v_sal emp.sal
            %
            type;

            begin
            
    p_querysal_out(
            7839
            ,v_sal);
    dbms_output.put_line(v_sal);

            end
        

            import
             oracle.jdbc.OracleTypes;

            import
             java.sql.CallableStatement;

            import
             java.sql.DriverManager;

            import
             java.sql.Connection;

            public
            class
             JdbcTest{
    
            public
            static
            void
             main(String[] args){
        
            //
            1.加载驱动
                    Class.forName(“oracle.jdbc.driver.PracleDriver”);
        
            //
            2.获取连接
        String url=”jdbc:oracle:thin:@localhost:1521:xe”;
        String name=”scott”;
        String password=”tigger”;
        Connection conn=DriverManager.getConnection(url,name,password);
        //3.获取语句对象
        String sql=”{call p_querysal_out(?,?)}”;
        CallableStatement call=conn.prepareCall(sql);
        //4.设置输出参数
        call.setInt(1,7839);
        //5.注册输出参数
        call.registerOutParameter(2,OracleTypes.DOUBLE);
        //6.执行存储过程        call.execute();
        //7.获取输出参数double sal=call.getDouble(2);
        System.out.println(“薪水：”+sal);
        //8.释放资源        call.close();
        conn.close();
    }
}