oracle学习笔记之存储过程

user:scott
 
SQL> create table mytest(name varchar2(30),password varchar2(30));
 
Table created
 
SQL> create or replace procedure pro_sp1 is --创建一个简单的存储过程
  2  begin
  3    insert into mytest('李慧','1314');
  4  end;
  5  /
 
Warning: Procedure created with compilation errors
 
SQL> show error;--显示创建语句的错误
Errors for PROCEDURE SCOTT.PRO_SP1:
 
LINE/COL ERROR
-------- -------------------------------------
3/22     PL/SQL: ORA-00928: 缺失 SELECT 关键字
3/3      PL/SQL: SQL Statement ignored
 
SQL> 
SQL> create or replace procedure pro_sp1 is
  2  begin
  3    insert into mytest(name,password) values('李慧','1314');
  4  end;
  5  /
 
Procedure created
 
SQL> exec pro_sp1;
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> set serveroutput on;--打开控制台输出


--创建一个带两个参数的存储过程
create or replace procedure sp_pro1(names varchar2,newsal number) is
begin
 update emp set sal=newsal where ename=names;
end;


--调用刚刚创建存储过程

exec sp_pro1('SCOTT',3500);

--创建一个存储过程，输入雇员名称，如果雇员工资低于2000就给他加20%
create or replace procedure sp_pro2(names varchar2) is
nums emp.sal%type;ids emp.empno%type;
begin
  select empno,sal into ids,nums from emp where ename=names;
  if nums<2000 then
    update emp set sal=nums*1.2 where empno=ids;
    dbms_output.put_line(names||'工资低于2000，给他加了20%');
   else 
     dbms_output.put_line(names||'工资不低于2000，不给加工资了');
   end if;
end;

--创建一个存储过程，输入雇员编号，返回该雇员的名称
--in 代表输入参数，out代表输出参数
create or replace procedure sp_pro3
(eno in number,names out varchar2) is
begin
  select ename into names from emp where empno=eno;
end;






--创建一个包，包里面定义了一个游标类型的数据
create or replace package testpackage as
type t_cursor is ref cursor;
end;
--创建一个存储过程，通过输入的部门号返回该部门的所以职工信息
--open emplist for代表打开游标并把查询的结果集放在emplist里面
create or replace procedure sp_pro4
(dno in number,emplist out testpackage.t_cursor) is
begin
open emplist for select * from emp where deptno=dno;
end;




--创建一个存储过程，通过输入部门编号获取该部门所有的记录条数
create or replace procedure sp_pro5
(dno in number,nums out number) is
begin
  select count(*) into nums from emp where deptno=dno;
end;

--创建包
create or replace package sp_package2 as
type sp_cursor is ref cursor;
end;
--通用分页存储过程
create or replace procedure 
sp_pro6(tablename in varchar2,--表或视图名称，输入参数
selectrow in varchar2,--要查询的列，输入参数
wheres in varchar2,--查询条件，输入时要输入where ....，输入参数
orders in varchar2,--排序条件，不包含order by ，输入参数
currentpage in number,--当前页，输入参数
pagesizes in number,--每页显示的条数，输入参数
pagecount out number,--总页数，输出参数
counts out number,--总条数，输出参数
emplist out sp_package2.sp_cursor--输出的结果集
) is
v_sql varchar2(32767);
begin
  v_sql:='select * from (select a1.*,rownum rn from (select '||selectrow||' from '||tablename||wheres||' order by '||orders||') a1 where rownum<='||currentpage*pagesizes||') where rn>='||((currentpage-1)*pagesizes+1);
  open emplist for v_sql;
  v_sql:='select count(1) from '||tablename||wheres;
  execute immediate v_sql into counts;--执行sql语句，并把值赋给counts
  if mod(counts,pagesizes)=0 then
     pagecount:=counts/pagesizes;
   else
     pagecount:=counts/pagesizes+1;
   end if;
  exception
    when no_data_found then
      pagecount:=0;
      counts:=0;
end;