oracle储过程(超级有用).ppt

Oracle存储过程基础和实例;存储过程特点;存储过程特点;过程的语法结构;创建过程实例;参数;参数过程实例;仅有输入参数的过程 create or replace procedure stu_proc1(pno in student.sno%type) as pname varchar2(25); begin select sname into pname from student where sno=pno; dbms_output.put_line(pname); end; ;仅有输出参数的存储过程 create or replace procedure stu_proc2(pname out student.sname%type) as begin select sname into pname from student where sno=1; dbms_output.put_line(pname); end; 此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明 ;有输入\输出参数的存储过程： create or replace procedure stu_proc3 (pno in student.sno%type,pname out student.sname%type) as begin select sname into pname from student where sno=pno; dbms_output.put_line(pname); end; 此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明 ;Oracle函数调用存储过程;对无参数过程的调用： --函数 create or replace function get_pname return varchar2 is pname varchar2(20); begin stu_proc; select sname into pname from student where sno=1; return pname; end; --调用 declare begin dbms_output.put_line(在PL/SQL中打印的结果：||get_pname); end; ;对有输入参数过程的调用： create or replace function get_pname1(pno in number) return varchar2 is pname varchar2(20); begin stu_proc1(pno in student.sno%type) select sname into pname from student where sno=pno; return pname; end; --调用 declare begin dbms_output.put_line(在PL/SQL中打印的结果：||get_pname1(2)); end; ;对有输出参数过程的调用: create or replace function get_pname2(pname out varchar2) return varchar2 is begin stu_proc2(pname out student.sname%type); return pname; end; --调用 declare pname student.sname%type; begin dbms_output.put_line(在PL/SQL中打印的结果：||get_pname2(pname)); end; ;对有输入\输出参数过程的调用: create or replace function get_pname3(pno in number,pname out varchar2) return varchar2 is begin stu_proc3(pno in student.sno%type,pname out student.sname%type); return pname; end; --调用 declare pname student.sname%type; begin dbms_output.put_line(在PL/SQL中打印的结果：||get_pname3(2,pname)); end; ;JAVA调用数据库存储过程;仅有返回值的过程： public static void main(String[] ar