- 存储过程
- create or replace procedure procedure_name
- [(parameter[{in | out |in out}] datetype)]
- [is | as]
- begin
- end [procedure_name];
- 1 不带参数的存储过程
- create or replace procedure get_news is
- rows number;
- begin
- select count(*) into rows from stuInfo;
- dbms_output.put_line('values is '||rows);
- end get_news;
- --测试
- declare
- begin
- get_news();
- end;
- 2 带有输入输出参数的存储过程
- create or replace procedure multi_params(sex in varchar2,rows out number,msg in out varchar2) is
- begin
- select count(*) into rows from stuInfo where stuSex=sex;
- msg:=msg||sex||'生,共有'||rows||'人!';
- end multi_params;
- set serverout on
- declare
- inparm varchar(50):='男';
- outparm number(10);
- inoutparm varchar(50):='学生信息:>> ';
- begin
- multi_params(inparm,outparm,inoutparm);
- dbms_output.put_line(inoutparm);
- end;
- 测试窗口中测试
- declare
- inparm char(50);
- outparm integer;
- inoutparm char(50);
- begin
- :inparm:='男';
- :inoutparm :='学生信息:>> ';
- multi_params(:inparm,:outparm,:inoutparm);
- end;
- 命令窗口测试
- set serverout on
- declare
- inparm varchar(50):='男';
- outparm number(10);
- inoutparm varchar(50):='学生信息:>> ';
- begin
- multi_params(inparm,outparm,inoutparm);
- dbms_output.put_line(outparm||' '||inoutparm);
- end;
- /
- --最后一定要打上/表示语句块结束.
- 3 创建带有游标的过程
- 3.1 显示定义游标的格式
- create or replace procedure <procedure_name>
- is
- cursor <cursor_name> is <sql_statement>
- begin
- for<record_name> in <cursor_name> loop
- <other code>
- end loop;
- end <procedure_name>;
- create or replace procedure stuInfo_cur_pro
- is
- cursor stuinfo_cur
- is select stuName from stuInfo;
- begin
- for stuInfo_rec in stuinfo_cur loop
- dbms_output.put_line(stuInfo_rec.stuName);
- end loop;
- end stuInfo_cur_pro;
- 测试 exec stuInfo_cur_pro;
- 3.2 隐式定义游标格式
- create or replace procedure <procedure_name>
- is
- begin
- for <record_name> in (<sql_statement>) loop
- <other code>
- end loop;
- end <procedure_name);
- create or replace procedure stuInfo_cur_pro_hidden
- is
- begin
- for stuInfo_rec in (select stuName,stuSex from StuInfo) loop
- dbms_output.put_line(stuInfo_rec.stuName||' '||stuInfo_rec.stuSex);
- end loop;
- end stuInfo_cur_pro_hidden;
- 测试
- exec stuInfo_cur_pro_hidden;
- 4 带有批量绑定的存储过程
- create or replace procedure bluk_bind_pro is
- begin
- type bbp is table of Stuinfo.Stuname% type
- index by binary_integer;
- temp_bulk bbp;
- begin
- select stuName bulk collect into temp_bulk from stuInfo;
- end
- end bluk_bind_pro;
Oracle 10g 存储过程
最新推荐文章于 2021-04-10 04:12:15 发布