存储过程例子
?
?
create table TESTTB(ID VARCHAR2(30),NAME VARCHAR2(30))Insert into testtb values('1','21');Insert into testtb values('2','22');Insert into testtb values('3','23');Insert into testtb values('4','24');1、用来插入数据，没有返回值CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2)ASBEGININSERT INTO HUANGBIAO.TESTTB(ID, NAME) VALUES (PARA1, PARA2);END TESTA;2、查询只有一个返回值的结果集合CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2)ASBEGINSELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1;END TESTB;3、查询有多个返回值的集合（一组数据）CREATE OR REPLACE PACKAGE TESTPACKAGE ASTYPE TEST_CURSOR IS REF CURSOR;end TESTPACKAGE;CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR outTESTPACKAGE.TEST_CURSOR) ISBEGINOPEN P_CURSOR FORSELECT * FROM HUANGBIAO.TESTTB;END TESTC;存储器和函数的区别？最本质的区别是存储过程是命令, 而函数是表达式的一部分create or replace procedure proc_select(table_id in varchar2)ashbsql varchar2(500);beginhbsql:='select name from testtb where id='||table_id;execute immediate hbsql;end;备注：上面的变量不能使用sql，即“hbsql”不能写为“sql”execute proc_select('3');create or replace procedure proc_insert(id in varchar2, --输入序号name in varchar2 --输入姓名) asstr_sql varchar2(500);beginstr_sql:='insert into testtb values(:1,:2)';execute immediate str_sql using id,name; --动态执行插入操作exceptionwhen others thennull;end ;SQL> execute proc_insert('11',’dinya’);?