oracle怎么定义子程序,oracle使用七(子程序和程序包)

过程是执行某些操作的子程序，它是执行特定任务的模块，它可以被赋予参数，存储在数据库中。以下代码

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;

删除存储过程：

Drop procedureb showInfo;

查看过程中的错误，如果在创建过程或者函数时数据库报告错误，可以通过 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 ;

end;

/

其次执行：

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_ord     NUMBER,

qty_deld    NUMBER,

OSTATUS     CHAR(1),

DEL_DATE    DATE,

ORDER_COST  NUMBER

);