PL/SQL小例子

最近学习了Oraclec的PL/SQL，以下是写的一些小例子，贴出来分享

--过程定义
create or replace procedure proc1(var_month in test.month%type) is
v_sal test.sal%type;
begin
select sal into v_sal from test where month=var_month;
if v_sal > 4000 then
   begin
         dbms_output.put_line(v_sal);
   end;
end if;
end;


--函数定义
create or replace function func1(var_month test.month%type) 
return test.sal%type 
is v_sal test.sal%type;
begin
select sal into v_sal from test where month=var_month;
return v_sal;
end;

--函数的调用
declare
       v_sal test.sal%type;
begin
      v_sal:=func1(7);
      dbms_output.put_line(v_sal);
end;

--带有复合变量的存储过程
create or replace procedure proc2
as
type emp_record is record
(
     name emp.ename%type,
     salary emp.sal%type,
     dno emp.deptno%type
);
user_record emp_record;
begin
select ename,sal,deptno into user_record from emp where empno=7788;
dbms_output.put_line('用户名'||user_record.name||', 薪水'||user_record.salary||', 部门编号'||user_record.dno);
end;


--定义一个包，包由两部分组成，包规范和包体
create or replace package mypackage is
procedure myproc(varempno in emp.empno%type);
function myfunc(varempno in emp.empno%type) return emp.sal%type;
end mypackage;

--下面是包体
create or replace package body mypackage is
procedure myproc(varempno in emp.empno%type)
as 
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=varempno;
dbms_output.put_line(v_sal);
end;

function myfunc(varempno in emp.empno%type)
return emp.sal%type
as
v_sal2 emp.sal%type;
begin
select sal into v_sal2 from emp where empno=''||varempno||'';
return v_sal2;
end;
end mypackage;

--调用包中的过程和方法
declare
v_sal emp.sal%type;
begin
mypackage.myproc(7788);
v_sal:=mypackage.myfunc(7788);
dbms_output.put_line(v_sal);
end;

--定义复合类型PL/SQL表实例，其实就是一个特殊数组
--index by binary_integer表示下表是整数
declare
type emp_table_type is table of emp.ename%type index by binary_integer;
table_ename emp_table_type;
begin
select ename into table_ename(0) from emp where empno=7788;
dbms_output.put_line('员工名:'||table_ename(0));
end;

--游标的使用

declare
type emp_cursor is ref cursor;
v_sal emp.sal%type;
v_ename emp.ename%type;
test_cursor emp_cursor;
begin
open test_cursor for select ename,sal from emp where deptno=&deptnum;
loop
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%NOTFOUND;
if v_sal < 2000 then
update emp set sal=2000 where ename=v_ename and deptno=&deptnum;
end if;
dbms_output.put_line('姓名:'||v_ename||' 工资为:'||v_sal);
end loop;
close test_cursor;
end;


--通过存储过程来使用游标和复合变量
create or replace procedure proc3(num in emp.deptno%type) as
type emp_type is record
(
v_ename emp.ename%type,
v_sal emp.sal%type
);
test_type emp_type;
cursor mycur is
select ename,sal from emp where deptno=num;
begin
if mycur%ISOPEN = false then
open mycur;
end if;
loop
fetch mycur into test_type;
exit when mycur%NOTFOUND;
dbms_output.put_line('用户名:'||test_type.v_ename||' 工资:'||test_type.v_sal);
end loop;
close mycur;
end;


--创建一个带有返回值的存储过程

create or replace procedure proc5(empnum in emp.empno%type,empsal out emp.sal%type,empjob out emp.job%type) is
begin
select sal,job into empsal,empjob from emp where empno=empnum;
end;

--定义一个包，并在包中定义游标以便能在存储过程参数中得到结果集
create or replace package testpackage as
type emp_cursor is ref cursor;
end testpackage;

--定义一个带有cursor类型的存储过程
create or replace procedure proc6(deptnum in number,test_cursor out testpackage.emp_cursor) is
begin
open test_cursor for select * from emp where deptno=deptnum;
end;

--分页语句
select * from (select A.*,rownum rn from (select * from emp) A) where rn>=5 and rn<=10;


--定义一个包，并在包中定义一个取得分页结果集的游标
create or replace package fypackage as
type fenye_cursor is ref cursor;
end fypackage;

--定义一个存储过程用来表示分页
create or replace procedure fenye(
tableName in varchar2,--查询的表名
myPageSize in number,--每页显示记录条数
pageNum in number,--第几页
allRows out number,--所有记录条数
allPages out number,--所有页数
result_cursor out fypackage.fenye_cursor--返回结果集游标
) is
v_sql varchar2(1000);
v_start number:=(pageNum-1)*myPageSize+1;
v_end number:=pageNum*myPageSize;
begin
v_sql:='select * from (select A.*,rownum rn from (select * from '||tableName||') A) where rn>='||v_start||' and rn<='||v_end;
open result_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into allRows;
if mod(allRows,myPageSize)=0 then
allPages:=allRows/myPageSize;
else
allPages:=allRows/myPageSize+1;
end if;
end;