plsql小笔记

create function sp_fun2(spName varchar2) return number is 
yearSal number(7,2);
begin
select sal*12 into yearSal from emp where ename=spName;
return yearSal;
end;

--创建一个包头

create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;

--创建一个包体

create or replace package body sp_package is
procedure update_sal(name varchar2,newSal number) is
begin
update emp set sal=newSal where ename=name;
end;
function annual_income(name varchar2) return number is
annual_salary number;
begin
select sal*12 into annual_salary from emp where ename=name;
return annual_salary;
end;
end;

--变量1
declare
c_tax_rate number(3,2):=0.03;

--变量2
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是:'||v_ename||' 工资:'||v_sal||'  缴税:'||v_tax_sal);
end;

--定义游标
declare
--定义一个游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename ||' 工资:'||v_sal );
end loop;
end;

--控制语句
create or replace procedure sp_pro6(spName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<1000 then
update emp set sal=sal+sal*10 where ename=spName;
end if;
end;


--循环自增
create or replace procedure sp_pro7(spName varchar2) is
v_num number:=1;
begin
loop
  insert into users values(v_num,spName);
  exit when v_num=10;
  v_num:=v_num+1;
end loop;
end;


--分页

--1)创建一个表
create table book(bookId number,bookName varchar2(50),publicHouse varchar2(50));

--2)编写存储过程
create or replace procedure sp_pro8(spBookId in number,spbookName in varchar2,sppublicHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublicHouse);
end;


--案例:有输出和输入的存储过程
create or replace procedure sp_pro9(spno in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;

--案例2:有输出和输入的存储过程(非结果集)
create or replace procedure sp_pro10(spno in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
end;


--有返回值的存储过程(结果集)
--1)建立一个包
create or replace package testpackage AS TYPE test_cursor is ref cursor;
end testpackage;

--2)建立存储过程
create or replace procedure sp_pro11(spNo in number,p_cursor out testpackage.test_cursor) is
begin
  open p_cursor for select * from emp where deptno=spNo;
end;

--3)在java中调用

---4)
  select t1.*,rownum rn from (select * from emp)t1 where rownum<=10;

select * from (select t1.*,rownum rn from (select * from emp)t1 where rownum<=10) where rn>=6;



--开始编写分页的过程


create or replace procedure fenye(tableName in varchar2,
myPageSize in number,
myPageNo in number,
myrows out number,
myPageCount out number,
p_cursor out testpackage.test_cursor) is
--定义
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(myPageNo-1)*myPageSize+1;
v_end number:=myPageNo*myPageSize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName ||' )t1 where rownum<='||v_end||') where rn>='||v_begin;
--打开游标(和sql关联)
open p_cursor for v_sql;
--计算
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,myPageSize)=0 then
myPageCount:=myrows/myPageSize;
else
myPageCount:=myrows/myPageSize+1;
end if;
--关闭游标
--close p_cursor;
end;