1.使用包
在sqlplus中调用函数:
set serveroutput
on
begin
--使用包dbms_output
dbms_output.put_line( 'hello world');
end;
/
begin
--使用包dbms_output
dbms_output.put_line( 'hello world');
end;
/
2. 变量赋值
declare
v_ename varchar2(5);
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename, v_sal from emp where empno=&no; --注意赋值的顺序, 还有 &表示要从控制台输入变量
dbms_output.put_line('雇员名:‘||v_ename); --字符串拼接
end;
/
begin
select ename,sal into v_ename, v_sal from emp where empno=&no; --注意赋值的顺序, 还有 &表示要从控制台输入变量
dbms_output.put_line('雇员名:‘||v_ename); --字符串拼接
end;
/
into v_name: 查出来赋予变量值
||:字符串拼接
&:表示要从控制台输入变量
3. 异常处理
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename, v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:‘||v_ename);
exception
when no_data_found then
dbms_output.put_line( 'The input number is wrong');
end;
/
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename, v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:‘||v_ename);
exception
when no_data_found then
dbms_output.put_line( 'The input number is wrong');
end;
/
4. 传递参数
create
procedure sp_pro3(spname varchar2, newSal number)
is
begin
update emp set sal=newsal where ename=spName;
end;
/
begin
update emp set sal=newsal where ename=spName;
end;
/
执行:exec sp_proc3('scott', 3567);
5. java程序调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection("jdbc:oracle:thin@127.0.0.1:1521:wilson","scott","tiger");
//通过连接,取得存储过程
CallableStatement cs = conn.prepareCall("{call sp_pro3(?,?)}");
//对里面的内容赋值
cs.setString(1,"smith");
cs.setInt(2,1000);
//执行存储过程
cs. execute();
//释放资源
cs. close();
conn. close();
Connection conn = DriverManage.getConnection("jdbc:oracle:thin@127.0.0.1:1521:wilson","scott","tiger");
//通过连接,取得存储过程
CallableStatement cs = conn.prepareCall("{call sp_pro3(?,?)}");
//对里面的内容赋值
cs.setString(1,"smith");
cs.setInt(2,1000);
//执行存储过程
cs. execute();
//释放资源
cs. close();
conn. close();
6. 函数
create function sp_fun1(spName
varchar)
return
is number yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
var income number
call sp_fun1('scott') into income ;
7. 包
--创建包sp_package
create package sp_package is
procedure update_sal( name varchar2, newsal number);
function annual_income( name varchar2) return number);
end;
/
create package sp_package is
procedure update_sal( name varchar2, newsal number);
function annual_income( name varchar2) return number);
end;
/
把包里面的存储过程和函数实现:
create
or
replace package body sp_package
is
procedure update_sal( name varchar2, newsal number) is
begin
update emp set sal=newsal where ename= name;
end;
function annual_income( name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename= name;
return annual_salary'
end;
end;
procedure update_sal( name varchar2, newsal number) is
begin
update emp set sal=newsal where ename= name;
end;
function annual_income( name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename= name;
return annual_salary'
end;
end;
使用包中的函数和存储过程
exec sp_package.update_sal(
'scott',120);
本文转自 tianya23 51CTO博客,原文链接:http://blog.51cto.com/tianya23/260332,如需转载请自行联系原作者