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