過程是執行某些操作的子程序，它是執行特定任務的模塊，它可以被賦予參數，存儲在數據庫中。以下代碼
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
);
