begin
--使用包dbms_output
dbms_output.put_line('hello world');
end;
/
v_ename varchar2(5);
begin
select ename,sal into v_ename, v_sal from emp where empno=&no; --注意赋值的顺序, 还有&表示要从控制台输入变量
dbms_output.put_line('雇员名:‘||v_ename); --字符串拼接
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;
/
begin
update emp set sal=newsal where ename=spName;
end;
/
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();
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number);
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;