pl/sql编程——过程、函数、包
pl/sql编程——过程、函数、包
CallableStatement cs = con.prepareCall("{call emp_pro(?,?)}");//4.给?赋值cs.setString(1,"SMITH");cs.setInt(2,10);//5.执行cs.execute();
--编写函数 返回指定人员的年薪create function annual_income(name varchar2)return number isannual_salary number(7, 2);begin select sal*12 + nvl(comm, 0) into annual_salary from emp where ename = name; return annual_salary;end;
SQL> var income numberSQL> call annual_incomec('scott') into :income; --注意:和income之间不能有空白SQL> print income
--使用create package命令创建包create package pkg_sal is procedure update_sal(name varchar2, new_sal number); function annual_income(name varchar2) return number;end;
--使用create package body创建包体create or replace package body pkg_sal is procedure update_sal(name varchar2, new_sal number) is begin update emp set sal = new_sal where ename = name; end; function annual_income(name varchar2) return number is income number(7, 2); begin select sal*12 + nvl(comm, 0) into income from emp where ename = name; return income; end;end;
?
3).如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。
?
?
