Oracle实践–PL/SQL基础之存储过程

pl/sql基础之存储过程

存储过程：过程是用于完成特定任务的子程序(代码的集合)

/*

子程序的优点：1.模块化，将程序分解为逻辑模块；

2.可重用性，可以被任意数目的程序调用；

3，可维护性，简化维护操作；

4.安全性：通过设置权限，使数据更安全

*/

–存储过程1，打印helloworld

create or replace procedure my_pro
is   --用于声明一些变量
 v_string varchar2(20) :='helloworld!';
begin
 dbms_output.put_line(v_string);
end;

调用方式有三种：

begin
 my_pro;--这种方式调用无参的存储过程，括号可以省略
 my_pro();
end;

call my_pro();--无参的也必须带括号

在command中输入 set serveroutput on  execute my_pro();

–存储过程2，带参数的，默认是输入参数

create or replace procedure my_pro2(v_sal  number)--参数，要制定类型，varchar不能指定长度
is
  v_sql varchar2(200);
  v_emp emp%rowtype;
begin
 v_sql :='select * from emp where sal =: s';
 execute immediate v_sql into v_emp
 using v_sal;
 dbms_output.put_line(v_emp.ename);
 exception
   when no_data_found then
     dbms_output.put_line('没有找到！');
end;
 
select *from emp;
call my_pro2(820);

–存储过程3，带参数，输入(in)和输出(out)参数,关键字：in out,不写默认是in

create or replace procedure my_pro3(v_sal number, v_name out emp.ename%type)--out
is
 v_sql varchar2(200);
begin
 v_sql :='select ename from emp where sal =: s';
 execute immediate v_sql into v_name
 using v_sal;
end;

–存储过程4，输入输出类型，in out,交换两个数

create or replace procedure swap(v_num1 in out number, v_num2 in out number)-- in out 顺序不能颠倒
as
 v_temp number;
begin
 v_temp := v_num1;
 v_num1 := v_num2;
 v_num2 := v_temp;
end;

–调用存储过程4

declare
 v_num1 number :='&num1';
 v_num2 number :='&num2';
begin
 dbms_output.put_line('交换前：num1 = '||v_num1||'-----num2 = '||v_num2);
 swap(v_num1,v_num2);
 dbms_output.put_line('交换后：num1 = '||v_num1||'-----num2 = '||v_num2);
end;

—存储过程5，计算总页数和总记录数

create or replace procedure countpageandrecords
      (tablename varchar2, pagesize number,totalrecord out number,totalpage out number)
as
 v_sql varchar2(200);
begin
 v_sql :='select count(*) from '||tablename;
 execute immediate v_sql into totalrecord;
 totalpage :=ceil(totalrecord/pagesize);
  --计算方式二
 if mod(totalrecord,pagesize) =0 then
   totalpage := totalrecord/pagesize;
 else
   totalpage :=floor(totalrecord/pagesize)+1;
 end if;
end;

–测试储过程5

declare
 tablename varchar2(50) :='&表名：';
 pagesize number :='&分页单位：';
 totalrecord number;
 totalpage number;
begin
 countpageandrecords(tablename,pagesize,totalrecord,totalpage);
 dbms_output.put_line('总数为：'||totalrecord);
 dbms_output.put_line('总页数为：'||totalpage);
end;