过程是执行某些操作的子程序,它是执行特定任务的模块,它可以被赋予参数,存储在数据库中。以下代码 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 ); |
|