Oralce 开发包 和 游标
程序员文章站
2022-05-20 18:31:27
...
--________________________________开发包______________________________________________________
--包 ：组合逻辑相关的PL/SQL 类型（table 类型，record 类型，游标和游标变量， 过程和函数） 提高性能，隐藏信息（私有），子程序重载
--1.创建包 （包:由包规范 和包体 组成， 创建包是要首先创建包规范 ，然后 包体） 实际上包规范是包与应用程序之间的接口
--A 首先 创建包规范 create package
--语法：
create [or replace] package package_name --package_name 指定包名
is|AS --用于定义共有组件
public type end item declareations
subprogram specifications
end package_name;
--示例
create or replace package emp_package --定义emp_package
As
g_deptno number(3):=30; --定义公有变量
procedure pro_add_employee --定义公有的 过程
(
param_empno number,
param_ename varchar2,
param_sal number,
param_deptno number:=g_deptno
)
--定义公有的函数fun_get_sal;
function fun_get_sal(param_empnno number)return number;
--***过程和函数 都没有给出具体的实现 所以 公有的函数和过程 只有在创建了包体后才能调用
END emp_package;
--B: 创包体； create package body （实现包规范中定义的过程和函数） 可以定义私有的组件（变量，常量，过程， 函数）只能在包体中使用
--语法
create [or replace] package body package_name --package_name用于指定包名 和包规范名称一样
iS|AS --定义*组件的开始 并实现公有的函数和过程
private type and item declarations subprogram bodies
end package_name;
--示例 1
create or replace package body emp_package --创建包体名
As
--定义私有的 函数
function fun_vallidate_dept(param_deptno number)
return boolean
as
v_temp number;
begin
select 1 into v_temp from dept where deptno=param_deptno;
return true;
Exception
when no_data_found then
return false;
END;
--实现 包规范中的 pro_add_employee 过程
procedure pro_add_employee
(
param_name number,
param_ename varchar2,
param_sal number,
param_deptno number:=g_deptno
)
AS
begin
if fun_vallidate_dept(param_deptno) then --调用了同一包中的 组件 fun_vallidate_dept
insert into emp(empno,ename,sal,deptno) values(param_empno,param_ename,param_sal,param_deptno);
else
raise_application_error(-2000,'不存在该部门！');
end if;
Exception
when DUP_VAL_On_index then
raise_application_error(-2002,'该员工编号已经存在！');
end;
--实现公有函数fun_get_sal
function fun_get_sal(param_empno number) return number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=param_empno;
return v_sal;
Exception
wher no_data_found then
raise_application_error(-2000,'该员工不存在！');
end;
end emp_package;
--在同一包中调用
直接调用（如上示例 的 pro_pro_add_employee）： fun_vallidate_dept
--调用包的公用变量
begin
emp_package.g_deptno:=20;
end;
--调用包的公有过程
begin
emp_package.g_deptno:=20;
emp_package.proc_add_employee(8888,'chen',3000);
emp_package.proc_add_employee(8889,'yaun',3000,30);
end;
--调用包的公用函数 （要定义变量来接受返回值）
declare
v_sal emp.sal%type;
begin
v_sal:=emp_package.fun_get_sal(7788);
DBMS_OUTPUT.put_Line('员工7788的工资是：'||V-sal);
end;
--不同用户调用包的公用组件 (要有权限)
grant execute on emp_package to hr;
conn hr/hr@oracl;
begin
scott.emp_package.pro_add_employee(2010,'tiger',2000);
end;
--查看包的源代码
conn scott/tiger@orcl
select text from user_source where name='emp_package' and type='package';
--删除包
drop package emp_package;
--__________________________________游标___________________________________________---
--游标分为 1.隐含游标：（处理 select into 和DML语句）
--2.显示游标： (处理select 语句返回多行数据)
--显示游标 （定义游标declare，打开游标open，提取数据fethch，关闭游标close）
--1.定有游标 （在使用显示游标的时候，必须首先在定义部分定义游标，用于定义游标所对应的select语句）
--语法
cursor cursor_name is select_statement; --cursor_name 游标的名称，select_statement：指定游标对应的select 语句
--2.打开游标 （打开游标的时候，oracle 会执行游标所对应的select 语句，将select语句的结果暂时放在结果集中）
open cursor_name； --cursor_name :必须在定义部分定义已经定义了的游标
--3.提取数据 （oracle 9i之前 fetch 每次只能提取一行数据 在oracle 9i 之后 通过fetch ....bulk collect into 语句一次可提取多行数据）
fetch cusor_name into variable1,variable2,....; --variavle：用于指定接受游标数据的变量
--或者
fetch cusor_name bulk collect into colllec1,collect2,.....; --collect:用于指定接受游标结果的集合变量
--4.关闭游标
close cusor_name;
--显示游标的属性 （返回显示游标的执行信息 包括 %isopen,%found,%notfound,%rowcount）
--1.isopen (判断游标是否已经打开)
if cusor_name %isopen then
执行语句
else
open cusor_name;
end if;
--2.%found (检测是否从数据集中提取到数据,提取到 返回true)
LOOP
fetch cusor_name into var1,var2;
if cusro_name%fount then
执行语句；
else
exit;
end if;
ENd LOOP;
--3.%notfound (检测是否从数据集中提取到数据,没有提取到 返回true)
LOOP
fetch cusor_name into var1,var2;
exit when cusor_name%notfound;
....
END LOOP;
--4.%rowcount (返回到当前行为止，已经提取到的世纪行数)
LOOP
fetch cusor_name into var1,var2;
if cusor_name%rowcount>n then --执行提取数据的行数大于n时候，要执行的语句
.....
END if；
exit
when cusor_name%notfound;
END LOOP;
--显示游标的使用示例
--1.在显示游标中使用 fetch ....into ..语句 （fetch ...into.. 每次只能提取一行，要处理结果集中多行数据，必须使用循环语句）;
declare
cursor emp_cursor is select ename,sal from emp where deptno=30;
v_name emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
Loop
fetch emp_cursor into v_name,v_sal;
exit when emp_cursor%notfound;
DBMS_OUTPUT.put_line(v_name||' '||v_sal);
end LOOP;
close emp_cursor;
end;
--2.在显示游标中，使用fetch...bulk collect into提取所有的数据
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
type emp_record is record
(
ename emp.ename%type,
sal emp.sal%type
);
type ename_table_type is table of emp_record;
v_name_table ename_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into v_ename_table;
close emp_cursor;
for i in v_ename_table.first..v_emp_table.last llllllllllllllllllll.
LOOP
DBMS_OUTPUT.put_line(v_ename_table(i).ename||' '||v_ename_table(i).sal);
END loop;
END;
--3.在游标中使用属性
cursor emp_cursor is select ename from emp where deptno=10;
type ename_table_type is table of varchar2(10);
v_ename_table ename_table_type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
fetch emp_cursor bulk collect into v_name_table;
DBMS_OUTPUT.put_line('提取的总行数'||emp_cursor%rowcount);
close emp_corsor;
end;
--4.基于游标定义记录变量 （%rowtype可以基于表和视图定义记录变量，还可以基于游标定义记录变量）
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
LOOP
fetch emp_cursor into emp_record;
exit when emp_cursor¬found;
DBMS_OUTPUT.put_line(emp_record.ename||' '||emp_record_sal);
end LOOP;
end;
--5.参数游标 （使用不同参数多次打开游标可以生成不同的结果集）
cursor cursor_name(parameter_name datatype) is select_statement;
--示例
declare --下面 定义了带参数的游标 指定数据类型不能指定长度
cursor emp_cursor(param_dept number) is select ename,sal from emp where deptno=param_dept;
emp_record emp_cursor%rowtype;
begin
open emp_cursor(10); --打开游标时候 传入参数 （10） 表示查询 部门编号为10 的所有员工的姓名和工资
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
DBMS_OUTPUT.put_line(emp_cursor.ename||' '||emp_cursor.sal);
end loop;
close emp_cursor;
end;
--6.是有游标更新或删除数据 （在定义游标时候 带有for update）
--语法
cursor cursor_name(param_naem datatype) is select_statement for update [nowalt];
--更新数据
declare
cursor emp_cursor is select ename,sal from emp for update;
v_emp_row emp_cursor%rowtype;
v_update_emp_count number(2):=0;
begin
open emp_cursor;
LOOP
fetch emp_cursor into v_emp_row;
exit when emp_cursor%notfound;
if v_emp_row.sal<2000 then
update emp set sal=sal+100 where current of emp_cursor;
v_update_emp_count:=v_update_emp_count+1;
end if;
end LOOP;
dbms_output.put_line('共有'||v_update_emp_count||'名员工被更新了！');
close emp_cursor;
end;
--使用游标删除数据
declare
cursor emp_cursor is select deptno form emp for update;
v_emp_row emp_cursor%rowtype;
v_update_emp_count mumber(2):=0;
begin
open emp_cursor;
loop
fetch emp_cursor into v_emp_row;
exit when emp_cursor%notfound;
if v_emp_row.deptno=30 then
delete emp where current of emp_currsor;
v_update_emp_count:=v_update_emp_count+1;
end if;
end loop;
DBMS_OUTPUT.put_Line('共有'||v_update_emp_count||'名雇员删除了！');
close emp_cursor;
end;
declare
cursor emp_cursor is
select ename,sal from emp form update nowalt;
v_emp_row emp_sursor%RowType;
u_upate_emp_count number(2):=0;
begin
open emp_cursor;
loop
fetch emp_cursor into v_emp_row;
exit when emp_cursor%notfound;
if v_emp_row.sal<2000 then
update emp set sal=sal+100 where current of emp_cursor;
v_updae_emp_count:=v_update_emp_count+1;
end if
end loop;
dbms_output.put_line('共有'|| _update_emp_count||‘员工被更新了 ！’);
close emp_cursor;
end
--游标for 循环
declare
--定义游标
cursor emp_cursor is select ename，sal form emp;
begin
for emp_row in emp_cursor loop
dbms_output.put_line('第'||emp_cursor%RowCount ||'个员工：'||emp_row.ename);
end loop;
end;
在for 游标中直接使用 子查询
begin
for emp_row int (select ename,sal form emp) loop
ebms_output.put_line(emp_row.ename);
end loop;
end;
--使用游标变量
定义ref cursor 类型 和游标变量
type ref_type_name is ref cursor；
cursor_variable ref_type_name;
--游标变量使用
依次显示部门编号为30 的所有员工的 说明使用游标变量的方法
declare
TYPE emp_cursor_type is REF CURSOR;
emp_cursor emp_cursor_type;
emp_row emp%ROWTYPE;
begin
open emp_cursor for
select * from em where deptno=30;
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%RowCount||'个员工：'||emp_row.ename);
end loop;
close emp_cursor;
end;
---使用游标变量开发返回结果集的子程序
create or replace procedure proc_getEmpsByDeptNO
（
param_deptno number,
param_resultset out SYS_REFCURSOR
）
as
begin
open param_resultset for
select ename,sal from emp where deptno=param_deptNo;
end;
declare
TYPE emp_record_type is record
(
ename varchar2(20),
sal number(7,2)
) ;
v_emp_rows SYS_REFCURSOR;
v_deptno number(2):=30;
v_emp_row emp_record_type;
begin
pro_getEmpByDeptno(v_deptno,v_emp_rows);
LOOP
fetch v_emp_Row into v_emp_row;
exit when v_emp_rows%NOTFOUND;
DBMS.output.put_line('第'||v_emp_rows%rowcount||‘员工 名称’||v_emp_row.ename||'工资'||v_emp.row.sal);
end loop;
close v_emp_rows;
end;
--开发返回结果集的函数
create or replace function fun_getEmpByDateYear
(
param_hireDareYear number
)
return sys_REFCURSOR
as
param_resultset sys_REFCUSOR;
begin
open param_resultset for
select ename,sal from emp where EXTRACT(Year from hrdare )=param_hirdDateYear;
return param_resultset;
end
declare
TYPE emp_record_Type is record
(
ename varchar2(20);
sal number(7,1)
);
v_emp_rows SYS_REFCURSOR;
v_hireDateYear number(4):=1981;
v_emp_row emp_record_type;
begin
v_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);
loop
fetch v_emp_rows into v_emp_row;
exit when v_emp_rows%NOTFOUND；
DBMS.output.put_line('第'||v_emp_rows%rowcount ||'个员工的名称：'||v_emp_row.name||'工资'||v_emp_row.sal);
end loop;
close v_emp_rows;
end;
--隐含游标的属性 （SQL%FOUND SQL％NOTFOUND SQL％rowcount SQL％isopen）
1 ：SQL%FOUND
decare
v_empno number(4):=7788;
begin update emp set empno=v_empno where empno=v_empno;
if sql%fount then
dbms_out.put.put_line('存在该员工');
else
dbms_output.put_line('不存在该员工');
end if;
end ;
2: SQL％NOTFOUND 与 SQL%FOUND 相反
3： declare
v_deptno number(2):=20;
v_rows_count number;
begin updare emp set sal =sal+100 where deptno=v_deptno;
v_row_count:=Sql%rowCount;
if v_rows_count =0 then
dbms_output.put_line('没有员工被更新！');
else
dbms_output.put_line('共有：'||_rows_count||'个员工被更新了！');
end if;
end;
上一篇： Linux系统打造man中文帮助手册教程
推荐阅读