九乡新闻网高就业率大学:oracle使用七(子程序和程序包)
来源：百度文库 编辑：九乡新闻网 时间：2024/04/29 17:29:40
过程是执行某些操作的子程序，它是执行特定任务的模块，它可以被赋予参数，存储在数据库中。以下代码
create or replace procedure
myproce(employeeid number)
as
empid number(8);
firstname varchar2(20);
lastname varchar2(20);
begin
select employee_id,first_name,last_name into empid,firstname,lastname
from employees2 where employee_id
dbms_output.put_line(''employeeid = ''||empid );
dbms_output.put_line(''firstname = ''||firstname);
dbms_output.put_line(''lastname = ''|| lastname);
exception
when no_data_found then
dbms_output.put_line(''没有发现数据'');
end;
/
执行以上存储过程：
set serveroutput on
declare
employeeid number(8);
begin
employeeid := 2;
myproce(employeeid);
end;
/
过程参数模式：参数传递的模式有三种IN , OUT , IN OUT
IN 是参数的默认模式，这种模式定义的参数在程序运行的时候已经具有值，在过程序体中这个值不会改变。
OUT 模式定义的参数只在过程内部赋值。
IN OUT 模式定义的参数当过程运行时可能已经具有值，但是在过程体中也可以修改
以下代码演示了 OUT 参数模式的使用，以下代码通过 employeeid 查询获取divisionid和jobid
create or replace procedure
showInfo(employeeid in number,jobid out varchar2,divisionid out varchar2)
is
jid varchar2(20);
did varchar2(20);
begin
dbms_output.put_line(''proce jobid='' ||jobid); select job_id,division_id into jid,did from employees2 where
employee_id =employeeid;
jobid :=jid;
divisionid := did;
end;
/
执行以上存储过程：
set serveroutput on
declare
jobid varchar2(20);
divisionid varchar2(20);
employeeid number(3);
begin
employeeid :=2;
showInfo(employeeid,jobid,divisionid);
dbms_output.put_line(''employeeid =''||employeeid || '' '' ||''divisionid =''||divisionid
||'' ''||''job_id=''||jobid);
end;
/
以下代码数据交换演示了如何使用IN OUT 参数的过程。
CREATE OR REPLACE PROCEDURE
swap(p1 IN OUT NUMBER, p2 IN OUT NUMBER) AS
v_temp NUMBER;
BEGIN
v_temp := p1;
p1 := p2;
p2 := v_temp;
END;
/
SET SERVEROUTPUT ON
DECLARE
num1 NUMBER := 100;
num2 NUMBER := 200;
BEGIN
swap(num1, num2);
DBMS_OUTPUT.PUT_LINE(''num1 = '' || num1);
DBMS_OUTPUT.PUT_LINE(''num2 = '' || num2);
END;
/
存储过程赋权限，以下代码演示了把存储过程授予user1用户：
Grant execute on showInfo swap to user1;
删除存储过程：
查看过程中的错误，如果在创建过程或者函数时数据库报告错误，可以通过 show errors 命令查看其中的错误。
Show errors;
Select * from user_procedures 查看存储过程信息。
列名称
列类型
Object_name
对象名，可以是过程，函数或者是包名
Procedure_name
过程名
aggregate
过程是否是聚合函数。该值为YES 或 NO
impltypeowner
实现类型的所有者的名称
impltypename
实现类型名
parallel
过程或者函数是否支持并行查询，该值为 YES 或 NO
函数：函数与过程很类似，也是小规模的程序。
以下代码创建一个简单函数：
CREATE OR REPLACE FUNCTION
fun_hello RETURN VARCHAR2 IS
BEGIN
RETURN ''朋友，您好！今天是'' || TO_CHAR(SYSDATE, ''DAY'');
END;
/
执行以上函数：
SELECT fun_hello FROM DUAL;
以下代码创建一个带有业务功能的函数：
create or replace function
myfunction(employeeid number)
return varchar2 is
firstname varchar2(20);
lastname varchar2(20);
sal number(8);
begin
select first_name , last_name ,salary into firstname,lastname,sal from employees2
where empl
if sal > 150000 then
return firstname || '' ''||lastname ||''优秀员工'';
else
return firstname || '' ''||lastname ||''不是优秀员工'';
end if;
end;
执行以上函数：
set serveroutput on
declare
empinfo varchar2(50);
begin
empinfo := myfunction(2);
dbms_output.put_line(empinfo);
end;
/
以下代码通过 drop 删除item_price_range 函数：
Drop function myfunction;
自主事务处理：
自主事务处理是由另一个事务处理（主事务处理）启动的独立事务处理。自主事务可以暂停主事务处理过程内的SQL操作，提交或回退操作，然后恢复主事务处理。当一个过程调用另一个过程时，在其中任一过程中进行的任何更改在这两个过程中都是可见的，任何提交或者回退语句均将影响这两个过程中的事务处理。以下代码演示了两个存储过程：
create or replace procedure
myproc1 is
firstname varchar2(20);
pragma autonomous_transaction;
begin
select first_name into firstname from employees2 where employee_id =2;
dbms_output.put_line(''myproc1 firstname =''||firstname);
rollback;
end;
/
create or replace procedure
myproc2 is
firstname varchar2(20);
begin
update employees2 set first_name = ''scott'' where employee_id = 2;
myproc1();
select first_name into firstname from employees2 where employee_id =2;
dbms_output.put_line(''myproc2 firstname = '' ||firstname);
end;
/
执行存储过程myproc2:
EXECUTE myproc2;
程序包主体
程序包是对相关过程、函数、变量、游标和异常等对象的封装，以下代码演示了程序包的使用：
首先执行：
create or replace package mypack
is
procedure employee2_proc(employeeid number);
function employee2_function return varchar2 ;
/
其次执行：
create or replace package body mypack as
procedure employee2_proc(employeeid number)
is
firstname varchar2(20);
lastname varchar2(20);
divisionid varchar2(20);
begin
select first_name,last_name,division_id into firstname,lastname,divisionid
from employees2 where employee_id = employeeid ;
dbms_output.put_line(''first_name = ''||firstname || ''lastname=''||lastname || ''divisionid =''|| divisionid);
end employee2_proc;
function employee2_function
return varchar2 is
jobid varchar2(20);
sal number(8);
begin
select job_id,salary into jobid,sal from employees2 where employee_id =2;
if jobid = ''MGR'' then
dbms_output.put_line(''job = Manager'' || '' salary = ''||sal);
end if;
end employee2_function;
end mypack;
/
以下代码执行程序包中的employee2_proc过程
Set serveroutput on
execute mypack.employee2_proc(2);
以下代码执行程序包中的employee2_function函数
declare
empinfo varchar2(50);
begin
empinfo := mypack.employee2_function;
dbms_output.put_line(''empinfo =''|| empinfo);
end;
/
程序包中的游标：
1． 创建程序包：
create or replace package my_cur as
cursor employeecur return employees2;
end;
2． 创建程序包主体：
create or replace package body my_cur as
cursor employeecur return employees2 is
select * from employees2 where salary >120000;
end;
/
执行以上程序包中的my_cur 游标。
declare
myrecord employees2%rowtype;
begin
open my_cur.employeecur;
loop
fetch my_cur.employeecur into myrecord;
exit when my_cur.employeecur %notfound;
dbms_output.put_line(''employee_id =''||myrecord.employee_id || ''
firstname =''|| myrecord.first_name ||'' lastname =''|| myrecord.last_name);
end loop;
end;
/
获取子程序和程序包的信息：
COLUMN OBJECT_NAME FORMAT A18
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN (''PROCEDURE'', ''FUNCTION'',
''PACKAGE'', ''PACKAGE BODY'');
获取user_source 视图结构：
DESC USER_SOURCE
获取程序包中的子程序 TEST 的源代码。
COLUMN LINE FORMAT 9999
COLUMN TEXT FORMAT A50
SELECT line, text FROM USER_SOURCE
WHERE NAME=''TEST'';
获取程序包中的子程序规范信息：
Desc pack_me;
CREATE TABLE ORDER_DETAIL
(
ORDERNO VARCHAR2(5) PRIMARY KEY,
ODATE DATE,
VENCODE VARCHAR2(5),
itemcode VARCHAR2(10),
qty_deld NUMBER,
OSTATUS CHAR(1),
DEL_DATE DATE,
ORDER_COST NUMBER
);