数据库小技能：存储过程（stored procedure）
数据库小技能：存储过程（stored procedure）
公众号iOS逆向
发布于 2022-12-19 09:47:30
发布于 2022-12-19 09:47:30
代码可运行
运行总次数：0
代码可运行
引言
过程（procedure）又叫存储过程（stored procedure），是一个有名称的PL/SQL程序块 。
过程相当于java中的方法， 它注重的是实现某种业务功能 。 函数（function）也相当于java中的方法，它 注重计算并且总是有返回结果 。
过程和函数都是能够永久存储在数据库中的程序代码块，应用时通过调用执行 。
I 过程的基本结构
create [or replace ] procedure 过程名称(形参名称 形参类型,形参名称 形参类型······)
is | as
定义变量
begin
过程体
exception
异常
end;
1.1 含有输入参数的过程
输入参数 用in 标识 可省略
--过程
create or replace procedure mypro(p_id in number,p_sal in number)
is
v_count number(3);
v_no number(3);
begin
select count(*) into v_count from emp where empno = p_id;
if v_count > 0 then
update emp set sal = sal+p_sal where empno=p_id;
v_no := sql%rowcount;
commit;
dbms_output.put_line(v_no||'rows updated');
else
dbms_output.put_line('记录不存在');
end if;
end;
1.2 无参的过程
--无参的过程
create or replace procedure mypro_noparam
is
v_count number(3);
begin
select count(*) into v_count from emp where empno = 7788;
dbms_output.put_line(v_count||' 条记录');
end;
1.3 有输出参数的过程
输出参数用 out 标识
--含有输出参数的过程
create or replace procedure mypro(p_id in number, p_sal out number)
is
begin
select sal into p_sal from emp where empno=p_id;
end;
II 过程的调用
2.1 通过匿名块调用
- 输入参数
--通过匿名块调用过程
begin
mypro(1234,100);
end;
- 输出参数过程
declare
v_sal number(10);
begin
mypro(7788,v_sal);
dbms_output.put_line(v_sal);
end;
- 无参的过程
begin
mypro_noparam;
end;
2.2 命令行调用
#1. 调用输入参数
SQL> exec mypro(7788,3000);
# 2.输出参数
SQL> var v_sal number; # 注册变量
SQL> exec mypro(7788,:v_sal); #:变量名称 使用变量接收输出
# 3.调用无参
SQL> exec mypro;
————————————————
版权声明：本文为CSDN博主「iOS逆向」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
示例：
- 写一个过程来封装emp表中的数据
/*写一个过程封装emp表中的数据*/
create or replace procedure my_pro
is
type v_table is table of emp%rowtype index by binary_integer;
--定义表类型的变量
v_data v_table;
--定义游标类型
type table_cursor is ref cursor return emp%rowtype;
--定义游标变量
v_myCursor table_cursor;
v_index binary_integer:=0;
begin
--开启游标
open v_myCursor for select * from emp;
--获取数据
loop
fetch v_myCursor into v_data(v_index);
--打印数据
exit when v_myCursor%notfound;
dbms_output.put_line(v_data(v_index).deptno||'____________'||v_data(v_index).empno);
--下标的自增
v_index :=v_index+1;
end loop;
close v_myCursor;
end;
--调用过程
begin
my_pro;
end;
- 写一个过程：输入员工编号，通过游标获取，输出该员工对应下属的信息 。
/*写一个过程输入员工编号，通过游标获取输出该员工对应下属的信息*/
create or replace procedure my_pro(p_id in number)
is
type v_table is table of emp%rowtype index by binary_integer;
--定义表类型的变量
v_data v_table;
--定义游标类型
type table_cursor is ref cursor return emp%rowtype;
--定义游标变量
v_myCursor table_cursor;
v_index binary_integer:=0;
begin
--开启游标
open v_myCursor for select * from emp where mgr=p_id;
--获取数据
loop
fetch v_myCursor into v_data(v_index);
--打印数据
exit when v_myCursor%notfound;
dbms_output.put_line(v_data(v_index).deptno||'____________'||v_data(v_index).empno);
--下标的自增
v_index :=v_index+1;
end loop;
close v_myCursor;
end;
--调用过程
begin
my_pro(&no);
end;
- 计算100-200的素数
/*计算100-200的素数*/
declare
begin
execute immediate ('create or replace procedure myPro
is
v_flag boolean ;
begin
for i in 100..200 loop
v_flag:=true;--默认为是素数
--增加除数
for j in 2..i/2 loop
if i mod j =0 then
v_flag:=false;--标记为不是素数
exit;
end if;
end loop;
--打印素数
if v_flag then
dbms_output.put_line(i);
end if;
end loop;
end;
');
end;
begin
myPro;
end;
2.3 通过java调用过程
package jdbcPlSQL;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.driver.OracleTypes;
import util.CoonectionUtil;
public class TestPLSQL {
public static void main(String[] args) {
Connection conn=null;
CallableStatement cstmt=null;
//调用无参的过程
//String sql="{call my_Porcedure()}";
//调用有参的过程
//String sql="{call mypro(?,?)}";
//调用有输出参数的过程
String sql="{call myPorcedure(?,?)}";
try {
conn=CoonectionUtil.getConn();
cstmt=conn.prepareCall(sql);
cstmt.setInt(1, 7369);
//住入输出参数oracle.jdbc.driver.OracleTypes//Types
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.execute();
//获取输出参数的值
System.out.println(cstmt.getInt(2));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
III 自定义函数
/*创建函数*/
create or replace function my_fun(p_deptno number)
return number
is
v_num number(10);
begin
select count(*) into v_num from emp where deptno=p_deptno;
return v_num;
end;
declare
v_num number(10);
begin
v_num :=my_fun(20);
dbms_output.put_line(v_num);
end;
/*含有输出参数的函数*/
create or replace function my_fun(p_id number,p_ename out varchar2)
return number
is
v_deptno number(10);
begin
select deptno,ename into v_deptno,p_ename from emp where empno=p_id;
return v_deptno;
end;
--调用含有输出参数的函数
declare
v_deptno number(10);
v_ename varchar2(30);
begin
v_deptno := my_fun(7788,v_ename);
dbms_output.put_line(v_deptno||'*************'||v_ename);
end;
IV 异常
系统异常分为预定义异常和非预定义异常。
- 预定义异常：是由数据库定义好，含有异常编码，异常名称，异常信息；
大概有20种，例如
too_many_rouws;no_data_found;zero_divide。
- 非预定义异常：异常编码，异常信息，但没有异常名称。
4.1非预定义异常
代码示例
/*非预定义的异常*/
declare
--定义异常名称
e_noFather exception;
--绑定异常名称与异常编码
pragma exception_init(e_noFather,-02291);
begin
insert into emp(empno,deptno) values(1111,99);
commit;
--处理异常
exception
when e_noFather then
dbms_output.put_line('该部门不存在');
end;
4.2 自定义异常
/*自定义异常*/
declare
v_ename_search varchar2(20);
v_empno number(10):=&no;
v_ename varchar2(20):=upper('&pwd');
--自定义异常
--定义异常名称
e_pwdErr exception;
--绑定异常名称与异常编码
pragma exception_init(e_pwdErr,-20291);
begin
select ename into v_ename_search from emp where empno=v_empno;
--产生异常
if v_ename_search=v_ename then
dbms_output.put_line('登陆成功');
else
--raise e_pwdErr;
--抛异常
dbms_standard.raise_application_error(-20291,'密码不正确',false);
end if;
exception
when no_data_found then
dbms_output.put_line('该用户不存在');
--when e_pwdErr then
--dbms_output.put_line('密码不正确');
end;
/*自定义异常根据员工号计算谁工资低于3000，并计算他的入职时间*/
create or replace procedure my_pro(v_empno number)
is
--员工的的sal
v_sal number(20);
--员工的入职时间
v_time number(10);
--自定义异常
--定义异常名称
e_lowsal exception;
--绑定异常名称与异常编码
pragma exception_init(e_lowsal,-20291);
begin
select sal into v_sal from emp where empno=v_empno;
--产生异常
if v_sal<3000 then
select (sysdate-hiredate) into v_time from emp where empno=v_empno;
raise e_lowsal; --产生异常
end if;
exception
--员工不存在的异常
when no_data_found then
dbms_output.put_line('该员工不存在');
when e_lowsal then
dbms_output.put_line(v_empno||'白吃了'||v_time||'天这么长的时间的饭，还只拿这样的工资'||v_sal);
end;
V 包
包用于管理过程和函数，包分为包头和包体。
- 包一定要有包头，包头负责声明函数、过程、变量和常量。包头可单独定义，单独定义的包头只能含有常量。
- 包体具体来实现包头所声明定义的函数和过程，包体封装实现。
5.1 代码示例
/*声明包头*/
--声明包头
create or replace package package_emp
is
--声明游标类型
type my_cursor is ref cursor return emp%rowtype;
--定义过程
procedure my_pro (p_cursor out my_cursor );
--定义函数
function my_fun return number;
end;
/*声明包体*/
create or replace package body package_emp
is
--实现过程
procedure my_pro (p_cursor out my_cursor )
is
begin
open p_cursor for select * from emp;
end;
--函数的实现
function my_fun
return number
is
v_num number(10);
begin
select sum(sal) into v_num from emp;
return v_num;
end;
end;
/*调用包中的过程与方法*/
declare
--不要return
type my_cursor is ref cursor ;
--定义游标类型的变量
v_cursor my_cursor;
--总工资
v_num number(10);
--记录类型的变量
v_recored emp%rowtype;
begin
package_emp.my_pro(v_cursor);
--打印数据
loop
--游标已在包体中开启
fetch v_cursor into v_recored;
dbms_output.put_line(v_recored.ename);
exit when v_cursor%notfound;
end loop;
close v_cursor;
--获取函数的返回值
v_num:=package_emp.my_fun;
dbms_output.put_line(v_num);
end;
5.2 jdbc调用含有包的过程体
代码示例
package package_JDBC;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleTypes;
import testlife.ConnectionUtil;
/**
*
* @author zhang_kn
* 调用包中的过程
*/
public class Package_Procedure {
public static void main(String[] args){
//获取连接
Connection conn=ConnectionUtil.getConn();
//System.out.println(conn);
//调用在包中的过程,该过程含有输出参数
String sql="{call package_emp.my_pro(?)}";
CallableStatement cstmt=null;
ResultSet rs=null;
try {
cstmt=conn.prepareCall(sql);
//注入参数
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
//获取输出参数
rs=(ResultSet) cstmt.getObject(1);
while(rs.next()){
//获取数据
System.out.println(rs.getInt(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
ConnectionUtil.close(conn,cstmt,rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-12-19，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
暂无评论
推荐阅读
推荐阅读
相关推荐
django xadmin 集成DjangoUeditor富文本编辑器
更多 >
腾讯云开发者
