Oracle系列之存储过程

create or replace procedure isPrime(x number) as
flag number:=1;
begin
if x<2 then
dbms_output.put_line('not prime');
else
for i in 2..x 
loop
if i*i>x then
gotohere;
end if;
if mod(x,i)=0 then
flag:=0;
gotohere;
end if;
endloop;
<<here>>
if flag=1 then
dbms_output.put_line('is prime');
else
dbms_output.put_line('not prime');
end if;
end if;
end;
/

call isPrime(10);
call isPrime(11);

create tablemytest(
name varchar2(30),
password varchar2(30)
);
create or replace  procedure fj_pro1 is
begin
insert into mytest values('jack','123456');--执行部分
end;
/
--执行过程删除名称为jack对应的列
create or replace  procedure fj_pro1 is
begin
delete from mytest where name='jack';--执行部分
end;
/

call procedurename(参数值1，参数值2);

create or replace procedure fj_pro2(a1name varchar2,a1sal number) is
begin
update tb_Employee set sal=a1sal where ename=a1name; 
end;
/

call fj_pro2('SCOTT',150);--这样SCOTT的工资就被修改成了150
select * from tb_Employee where ename='SCOTT';

create or replace procedure fj_pro3(fjname varchar2) is
v_sal tb_Employee.sal%type;--定义
begin
select sal into v_sal from tb_Employee where ename=fjname;--执行
if v_sal<2000 then--判断
update tb_Employee set sal=sal*1.1 where ename=fjname;
end if;
end;
/

call fj_pro3('SCOTT');--调用
select * from tb_Employee where ename='SCOTT';

create or replace procedure fj_pro4(fjname varchar2) is
v_comm tb_Employee.comm%type;--定义
begin
select comm into v_comm from tb_Employee where ename=fjname;--执行
if v_comm<>0 then--判断
update tb_Employee set comm=comm+100 where ename=fjname;
else
update tb_Employee set comm=comm+200 where ename=fjname;
end if;
end;
/

call fj_pro4('SCOTT');--调用
select * from tb_Employee where ename='SCOTT';

create or replace procedure fj_pro5(fjno number) is
v_job tb_Employee.job%type;--定义
begin
select job into v_job from tb_Employee where pk_Employee_ID=fjno;--执行
if v_job='PRESIDENT' then
update tb_Employee set sal=sal+1000 where pk_Employee_ID=fjno;
elsif v_job='MANAGER' then
update tb_Employee set sal=sal+500 where pk_Employee_ID=fjno;
else
update tb_Employee set sal=sal+200 where pk_Employee_ID=fjno;
end if;
end;
/

call fj_pro5(7788);--调用
select * from tb_Employee where ename='SCOTT';

create or replace procedure updatesal(p_empno tb_Employee.pk_Employee_ID%type) as
v_deptno tb_Employee.deptno%type;
v_inc tb_Employee.sal%type;
begin
select deptno into v_deptno from tb_Employee where pk_Employee_ID=p_empno;  
casev_deptno
when 10 then v_inc:=150;
when 20 then v_inc:=200;
when 30 then v_inc:=250;
else  v_inc:=300;
end case;
update tb_Employee set sal=sal+v_inc where pk_Employee_ID=p_empno;   
end;
/

call updatesal(7788);--调用
select * from tb_Employee where ename='SCOTT';

users表中，用户雇员号从1开始增加
create tableusers(
userno number,
db_user varchar2(40)
);
create or replace procedure fj_pro6(fjname varchar2) is
v_num number:=1;--定义:=表示赋值
begin
loop
insert into users values(v_num,fjname);
exit when v_num=10;--判断退出条件
v_num:=v_num+1;--自增
endloop;
end;
/

call fj_pro6('root1');--调用，这样表中会有10个db_user为root1的列
select * from users;

create or replace procedure fj_pro7(fjname varchar2) is
v_num number:=11;--定义:=表示赋值
begin
while v_num<=20loop
insert into users values(v_num,fjname);
v_num:=v_num+1;
endloop;
end;
/

call fj_pro7('root1');--调用
select * from users;

create tablebook(
bookid number,bookname varchar2(50),publishhouse varchar2(50)
);
--in:表示这是一个输入参数，默认是in
create or replace procedurefj_pro8(
fjbookid in number,
fjbookname in varchar2,
fjpublishhouse in varchar2
) is
begin 
insert into book values(fjbookid，fjbookname,fjpublishhouse);
end;
/

create or replace procedurefj_pro9(
fjno in number,
fjname out varchar2,
fjsal out number,
fjjob out varchar2
) is
begin 
select ename,sal,job into fjname,fjsal,fjjob from tb_Employee where pk_Employee_ID=fjno;
end;
/

create or replace package testpackage as
type test_cursor is ref cursor;
endtestpackage;
/

create or replace procedurefj_pro10(
fjno in number,
p_sursor out testpackage.test_cursor
) is
begin
open p_sursor for select * from tb_Employee where deptno=fjno;
end;
/

select t1.*,rownum rn from (select * fromtb_Employee) t1;
select t1.*,rownum rn from (select * from tb_Employee) t1 where rownum<=5;
select * from (select t1.*,rownum rn from(select * from tb_Employee) t1 where rownum<=5) where rn>=2;

create or replace procedurefenye(
table_name in varchar2,
pagesize in number,--每页大小
pagenow in number,--当前页
myrows out number,--总记录数
mypagecount out number,--总页数
p_sursor out testpackage.test_cursor--返回的记录集
) is
v_sql varchar2(1000);--定义部分，定义一个sql语句
v_begin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql:='select * from (select t1.*,rownum rn from(select * from '||table_name||'order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;--执行部分
open p_sursor for v_sql;--把游标和sql语句关联起来
--计算 myrows和 mypagecount
v_sql:='select COUNT(*) from '||table_name;
--执行sql,并把返回的值，赋给 myrows
execute immediate v_sql intomyrows;
--计算 mypagecount
if mod(myrows,pagesize)=0 then
mypagecount:=myrows/pagesize;
else
mypagecount:=myrows/pagesize+1;
end if;
close p_sursor;--关闭游标
end;
/

declare
v_ename tb_Employee.ename%type;--定义
begin
--
select ename  into v_ename from tb_Employee where pk_Employee_ID=&no;
dbms_output.put_line('雇员名是：'||v_ename);
exception 
when no_data_found then
dbms_output.put_line('雇员号输入有误');
end;
/

case_not_found
create or replace procedure fj_pro11(fjno number) is
v_sal tb_Employee.sal%type;
begin
select sal into v_sal from tb_Employee where pk_Employee_ID=fjno;
case
when v_sal<1000 then
update tb_Employee set sal=sal+100 where pk_Employee_ID=fjno;
when v_sal<2000 then
update tb_Employee set sal=sal+200 where pk_Employee_ID=fjno;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');
end;
/
call fj_pro11(7900);

create or replace procedureex_test(
fjno number
) is
--定义一个例外
myex exception;
begin 
update tb_Employee set sal=sal+1000 where pk_Employee_ID=fjno;--更新用户sal
--sql%notfound这是表示没有 update
--raise myex;触发 myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('没有更新任何用户');
end;
/

call ex_test(7788);--调用
call ex_test(1111);--没定义例外之前这样也是不会出错的