过程和函数
　a.将过程的执行权限授予其他用户：GRANT EXECUTE ON find_emp TO MARTIN;
　b.删除过程：DROP PROCEDURE find_emp
　c.函数的调用：SELECT fun_hello FROM DUAL;
? d.查看所有的过程:select object_name,created,status from user_objects
???????????????????????????? where object_type in ('PROCEDURE','FUNCTION')
? e.查看过程源码? select text from user_source where name='procedure_name';

１.在命令窗口中调用有一个in参数,一个out参数的存储过程
??? CREATE OR REPLACE PROCEDURE FIND_EMP(AID in CHAR,res out varchar2)
???AS
???ENAME DEPTO.NAME%TYPE;
???BEGIN
???SELECT NAME INTO ENAME from bankaccount? WHERE ID=AID;
???res:='姓名是:'||ename;
???EXCEPTION
???WHEN NO_DATA_FOUND THEN
???res:='not found';
???END FIND_EMP;
? 调用:
???VAR SSS VARCHAR2;
????? EXECUTE FIND_EMP('1111',:SSS);
?? 用java调用:
???import java.sql.CallableStatement;
???import java.sql.Connection;
???import java.sql.DriverManager;
???import java.sql.ResultSet;
???import java.sql.Types;
???
??? public class Sub
???{??
????static Connection co=null;
????public static Connection connection()
????{
?????try
?????{
?????Class.forName("oracle.jdbc.driver.OracleDriver");
?????co=DriverManager.getConnection
?????????????????? ("jdbc:oracle:thin:@192.168.0.111:1521:emp","scott","admin");
?????}catch(Exception e)
?????{
??????System.out.println(e);
?????}
?????return co;
????}
????public void query() throws Exception
????{
?????? Connection conn=connection();
?????? CallableStatement c=conn.prepareCall("{call FIND_EMP(?,?)}");
?????? c.registerOutParameter(2,Types.VARCHAR);
?????? c.setString(1,"11111");
?????? c.execute();
?????? String bal=c.getString(2);
?????? System.out.println(bal);??
?????? conn.close();
????}
????public static void main(String args[]) throws Exception
????{
?????Sub f=new Sub();
?????f.query();
????}
???}
?２.在java中调用pl/sql函数例子
???? 函数:
?????? CREATE OR REPLACE FUNCTION query3(id1 in char) RETURN VARCHAR2
??????????? AS BAL VARCHAR2(20);
??????????? A_ID CHAR(5);
??????????? BEGIN
??????????? SELECT BALANCE INTO? A_ID FROM BANKACCOUNT where ID=id1;
??????????? BAL:='您的余额为'||A_ID;
???????????? RETURN BAL;
??????????? END query3;
???? 调用:
?????import java.sql.CallableStatement;
?????import java.sql.Connection;
?????import java.sql.DriverManager;
?????import java.sql.ResultSet;
?????import java.sql.Types;
?????
????? class Function
?????{??
??????static Connection co=null;
??????public static Connection connection()
??????{
???????try
???????{
???????Class.forName("oracle.jdbc.driver.OracleDriver");
???????co=DriverManager.getConnection
???????????????????? ("jdbc:oracle:thin:@192.168.0.111:1521:emp","scott","admin");
???????}catch(Exception e)
???????{
????????System.out.println(e);
???????}
???????return co;
??????}
??????public void query() throws Exception
??????{
???????? Connection conn=connection();
???????? CallableStatement c=conn.prepareCall("{? = call QUERY3(?)}");
???????? c.registerOutParameter(1,Types.VARCHAR);
???????? c.setString(2,"11111");
???????? c.execute();
???????? String bal=c.getString(1);
???????? System.out.println(bal);??
???????? conn.close();
??????}
??????public static void main(String args[]) throws Exception
??????{
???????Function f=new Function();
???????f.query();
??????}
?????}
３.调用一个in参数，一个out参数的另一种方法
????? CREATE OR REPLACE PROCEDURE TEST(VAL1 IN VARCHAR2,VAL2 OUT NUMBER) IS
???IDD NUMBER;
???BEGIN
???SELECT ID INTO IDD FROM DEPTO WHERE ADDRESS=VAL1;
???IF SQL%FOUND THEN
???VAL2:=300;
???END IF;
???END;
??? 调用：
???DECLARE
???VA1 DEPTO.ADDRESS%TYPE:='dalu';