第一篇：Oracle創(chuàng)建函數(shù)和過程

ORACLE PL/SQL編程之六：把過程與函數(shù)說透(窮追猛打，把根兒都拔起!)

繼上篇：ORACLE PL/SQL編程之八：把觸發(fā)器說透 得到了大家的強力支持，感謝。接下來再下猛藥，介紹下一篇，大家一定要支持與推薦呀~！我也才有動力寫后面的。

本篇主要內容如下：

6.1 引言 6.2 創(chuàng)建函數(shù) 6.3 存儲過程 6.3.1 創(chuàng)建過程 6.3.2 調用存儲過程 6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 開發(fā)存儲過程步驟 6.3.6 刪除過程和函數(shù) 6.3.7 過程與函數(shù)的比較

6.1 引言

過程與函數(shù)（另外還有包與觸發(fā)器）是命名的PL/SQL塊（也是用戶的方案對象），被編譯后存儲在數(shù)據(jù)庫中，以備執(zhí)行。因此，其它PL/SQL塊可以按名稱來使用他們。所以，可以將商業(yè)邏輯、企業(yè)規(guī)則寫成函數(shù)或過程保存到數(shù)據(jù)庫中，以便共享。過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲在數(shù)據(jù)庫中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調用者返回數(shù)據(jù)，而過程則不返回數(shù)據(jù)。在本節(jié)中，主要介紹：

1． 創(chuàng)建存儲過程和函數(shù)。2． 正確使用系統(tǒng)級的異常處理和用戶定義的異常處理。

3． 建立和管理存儲過程和函數(shù)。

6.2 創(chuàng)建函數(shù)

1.創(chuàng)建函數(shù)

語法如下：

CREATE [OR REPLACE] FUNCTION function_name(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],......[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])[ AUTHID DEFINER | CURRENT_USER ] RETURN return_type IS | AS

<類型.變量的聲明部分> BEGIN

執(zhí)行部分

RETURN expression EXCEPTION 異常處理部分 END function_name;

? IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實參傳遞給形參，進入函數(shù)內部，但只能讀不能寫，函數(shù)返回時實參的值不變。OUT模式的形參會忽略調用時的實參值（或說該形參的初始值總是NULL），但在函數(shù)內部可以被讀或寫，函數(shù)返回時形參的值會賦予給實參。IN OUT具有前兩種模式的特性，即調用時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。調用時，對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。

? 一般，只有在確認function_name函數(shù)

是新函數(shù)或是要更新的函數(shù)時，才使用OR REPALCE關鍵字，否則容易刪除有用的函數(shù)。

例1.獲取某部門的工資總和：

--獲取某部門的工資總和 CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER, Emp_count OUT NUMBER)RETURN NUMBER IS

V_sum NUMBER;BEGIN

SELECT SUM(SALARY), count(*)INTO V_sum, emp_count FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;RETURN v_sum;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END get_salary;

2.函數(shù)的調用

函數(shù)聲明時所定義的參數(shù)稱為形式參數(shù)，應用程序調用時為函數(shù)傳遞的參數(shù)稱為實際參數(shù)。應用程序在調用函數(shù)時，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

第一種參數(shù)傳遞格式：位置表示法。

即在調用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯(lián)起來進行傳遞。用這種方法進行調用，形參與實參的名稱是相互獨立，沒有關系，強調次序才是重要的。

格式為：

argument_value1[,argument_value2 …]

例2：計算某部門的工資總和：

DECLARE

V_num NUMBER;V_sum NUMBER;BEGIN

V_sum :=get_salary(10, v_num);DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END;

第二種參數(shù)傳遞格式：名稱表示法。

即在調用時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯(lián)起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關系，名稱的對應關系才是最重要的，次序并不重要。

格式為：

argument => parameter [,…]

其中：argument 為形式參數(shù)，它必須與函數(shù)定

義時所聲明的形式參數(shù)名稱相同parameter 為實際參數(shù)。

在這種格式中，形勢參數(shù)與實際參數(shù)成對出現(xiàn)，相互間關系唯一確定，所以參數(shù)的順序可以任意排列。例3：計算某部門的工資總和：

DECLARE

V_num NUMBER;V_sum NUMBER;BEGIN

V_sum :=get_salary(emp_count => v_num, dept_no => 10);DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END;

第三種參數(shù)傳遞格式：組合傳遞。

即在調用一個函數(shù)時，同時使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個參數(shù)，只要其中有一個參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。

例4：

CREATE OR REPLACE FUNCTION demo_fun(Name VARCHAR2,--注意VARCHAR2不能給精度，如：VARCHAR2(10)，其它類似 Age INTEGER, Sex VARCHAR2)RETURN VARCHAR2 AS

V_var VARCHAR2(32);BEGIN

V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;RETURN v_var;END;

DECLARE

Var VARCHAR(32);BEGIN

Var := demo_fun('user1', 30, sex => '男');DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40, sex => '男');DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);DBMS_OUTPUT.PUT_LINE(var);END;

無論采用哪一種參數(shù)傳遞方法，實際參數(shù)和形式參數(shù)之間的數(shù)據(jù)傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調用函數(shù)時，將實際參數(shù)的地址指針傳遞給形式參數(shù)，使形式參數(shù)和實際參數(shù)指向內存中的同一區(qū)域，從而實現(xiàn)參數(shù)數(shù)據(jù)的傳遞。這種方法又稱作參照法，即形式參數(shù)參照實際參數(shù)數(shù)據(jù)。輸入?yún)?shù)均采用傳址法傳遞數(shù)據(jù)。傳值法是指將實際參數(shù)的數(shù)據(jù)拷貝到形式參數(shù)，而不是傳遞實際參數(shù)的地址。默認時，輸出參數(shù)和輸入/輸出參數(shù)均采用傳值法。在函數(shù)調用時，ORACLE將實際參數(shù)數(shù)據(jù)拷貝到輸入/輸出參數(shù)，而當函數(shù)正常運行退出時，又將輸出形式參數(shù)和輸入/輸出形式參數(shù)數(shù)據(jù)拷貝到實際參數(shù)變量中。

3.參數(shù)默認值

在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時可以使用DEFAULT關鍵字為輸入?yún)?shù)指定默認值。

例5：

CREATE OR REPLACE FUNCTION demo_fun(Name VARCHAR2, Age INTEGER, Sex VARCHAR2 DEFAULT '男')RETURN VARCHAR2 AS

V_var VARCHAR2(32);BEGIN

V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;RETURN v_var;END;

具有默認值的函數(shù)創(chuàng)建后，在函數(shù)調用時，如果沒有為具有默認值的參數(shù)提供實際參數(shù)值，函數(shù)將使用該參數(shù)的默認值。但當調用者為默認參數(shù)提供實際參數(shù)時，函數(shù)將使用實際參數(shù)值。在創(chuàng)建函數(shù)時，只能為輸入?yún)?shù)設置默認值，而不能為輸入/輸出參數(shù)設置默認值。

DECLARE

var VARCHAR(32);BEGIN

Var := demo_fun('user1', 30);DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40);DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);END;6.3 存儲過程

6.3.1 創(chuàng)建過程

建立存儲過程

在 ORACLE SERVER上建立存儲過程,可以被多個應用程序調用,可以向存儲過程傳遞參數(shù),也可以向存儲過程傳回參數(shù).創(chuàng)建過程語法:

CREATE [OR REPLACE] PROCEDURE procedure_name([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],......[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])[ AUTHID DEFINER | CURRENT_USER ] { IS | AS } <聲明部分> BEGIN

<執(zhí)行部分> EXCEPTION <可選的異常錯誤處理程序> END procedure_name;

說明：相關參數(shù)說明參見函數(shù)的語法說明。

例6．用戶連接登記記錄；

CREATE TABLE logtable(userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

例7．刪除指定員工記錄；

CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN employees.employee_id%TYPE)AS

No_result EXCEPTION;BEGIN

DELETE FROM employees WHERE employee_id = v_empno;IF SQL%NOTFOUND THEN RAISE no_result;END IF;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'的員工已被刪除!');EXCEPTION WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END DelEmp;

例8．插入員工記錄:

CREATE OR REPLACE PROCEDURE InsertEmp(v_empno in employees.employee_id%TYPE, v_firstname in employees.first_name%TYPE, v_lastname in employees.last_name%TYPE, v_deptno in employees.department_id%TYPE)AS

empno_remaining EXCEPTION;PRAGMA EXCEPTION_INIT(empno_remaining,-1);/*-1 是違反唯一約束條件的錯誤代碼 */ BEGIN

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);DBMS_OUTPUT.PUT_LINE('溫馨提示:插入數(shù)據(jù)記錄成功!');EXCEPTION WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('溫馨提示:違反數(shù)據(jù)完整性約束!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END InsertEmp;

例9．使用存儲過程向 departments表中插入數(shù)據(jù)。CREATE OR REPLACE PROCEDURE insert_dept(v_dept_id IN departments.department_id%TYPE, v_dept_name IN departments.department_name%TYPE, v_mgr_id IN departments.manager_id%TYPE, v_loc_id IN departments.location_id%TYPE)IS

ept_null_error EXCEPTION;PRAGMA EXCEPTION_INIT(ept_null_error,-1400);ept_no_loc_id EXCEPTION;PRAGMA EXCEPTION_INIT(ept_no_loc_id,-2291);BEGIN

INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id);DBMS_OUTPUT.PUT_LINE('插入部門'||v_dept_id||'成功');EXCEPTION WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20000, '部門編碼不能重復');WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR(-20001, '部門編碼、部門名稱不能為空');WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR(-20002, '沒有該地點');END insert_dept;

/*調用實例一: DECLARE ept_20000 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20000,-20000);ept_20001 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20001,-20001);ept_20002 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20002,-20002);BEGIN insert_dept(300, '部門300', 100, 2400);insert_dept(310, NULL, 100, 2400);insert_dept(310, '部門310', 100, 900);EXCEPTION WHEN ept_20000 THEN DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復');WHEN ept_20001 THEN DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');WHEN ept_20002 THEN DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯誤');END;

調用實例二： DECLARE ept_20000 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20000,-20000);ept_20001 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20001,-20001);ept_20002 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20002,-20002);BEGIN insert_dept(v_dept_name => '部門310', v_dept_id => 310, v_mgr_id => 100, v_loc_id => 2400);insert_dept(320, '部門320', v_mgr_id => 100, v_loc_id => 900);EXCEPTION WHEN ept_20000 THEN DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復');WHEN ept_20001 THEN DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');WHEN ept_20002 THEN DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯誤');END;*/

6.3.2 調用存儲過程

存儲過程建立完成后，只要通過授權，用戶就可以在SQLPLUS、ORACLE開發(fā)工具或第三方開發(fā)工具中來調用運行。對于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。ORACLE 使用EXECUTE 語句來實現(xiàn)對存儲過程的調用：

EXEC[UTE] procedure_name(parameter1, parameter2…);

例10： EXECUTE logexecution;

例11：查詢指定員工記錄；

CREATE OR REPLACE PROCEDURE QueryEmp(v_empno IN employees.employee_id%TYPE, v_ename OUT employees.first_name%TYPE, v_sal OUT employees.salary%TYPE)AS BEGIN

SELECT last_name || last_name, salary INTO v_ename, v_sal FROM employees WHERE employee_id = v_empno;DBMS_OUTPUT.PUT_LINE('溫馨提示:編碼為'||v_empno||'的員工已經(jīng)查到!');EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END QueryEmp;--調用

DECLARE

v1 employees.first_name%TYPE;v2 employees.salary%TYPE;BEGIN

QueryEmp(100, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);QueryEmp(103, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);QueryEmp(104, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);END;

例12．計算指定部門的工資總和，并統(tǒng)計其中的職工數(shù)量。

CREATE OR REPLACE PROCEDURE proc_demo(dept_no NUMBER DEFAULT 10, sal_sum OUT NUMBER, emp_count OUT NUMBER)IS BEGIN

SELECT SUM(salary), COUNT(*)INTO sal_sum, emp_count FROM employees WHERE department_id = dept_no;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END proc_demo;

DECLARE V_num NUMBER;V_sum NUMBER(8, 2);BEGIN

Proc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE('溫馨提示:30號部門工資總和：'||v_sum||',人數(shù)：'||v_num);Proc_demo(sal_sum => v_sum, emp_count => v_num);DBMS_OUTPUT.PUT_LINE('溫馨提示:10號部門工資總和：'||v_sum||',人數(shù)：'||v_num);END;

在PL/SQL 程序中還可以在塊內建立本地函數(shù)和過程，這些函數(shù)和過程不存儲在數(shù)據(jù)庫中，但可以在創(chuàng)建它們的PL/SQL 程序中被重復調用。本地函數(shù)和過程在PL/SQL 塊的聲明部分定義，它們的語法格式與存儲函數(shù)和過程相同，但不能使用CREATE OR REPLACE 關鍵字。

例13：建立本地過程，用于計算指定部門的工資總和，并統(tǒng)計其中的職工數(shù)量；

DECLARE V_num NUMBER;V_sum NUMBER(8, 2);PROCEDURE proc_demo(Dept_no NUMBER DEFAULT 10, Sal_sum OUT NUMBER, Emp_count OUT NUMBER)IS BEGIN

SELECT SUM(salary), COUNT(*)INTO sal_sum, emp_count FROM employees WHERE department_id=dept_no;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END proc_demo;--調用方法： BEGIN

Proc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE('30號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);Proc_demo(sal_sum => v_sum, emp_count => v_num);DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);END;

6.3.3 AUTHID 過程中的AUTHID 指令可以告訴ORACLE，這個過程使用誰的權限運行．默任情況下，存儲過程會作為調用者的過程運行，但是具有設計者的特權．這稱為設計者權利運行．

例14：建立過程，使用

Connect HR/qaz DROP TABLE logtable;CREATE table logtable(userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution AUTHID DEFINER IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT / AS SYSDBA GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;

CONNECT testuser1/userpwd1 INSERT INTO HR.LOGTABLE VALUES(USER, SYSDATE);EXECUTE HR.logexecution

AUTOID DEFINER； CONNECT HR/qaz SELECT * FROM HR.logtable;

例15：建立過程，使用

CONNECT HR/qaz

CREATE OR REPLACE PROCEDURE logexecution AUTHID CURRENT_USER IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT testuser1/userpwd1 INSERT INTO HR.LOGTABLE VALUES(USER, SYSDATE);EXECUTE HR.logexecution

AUTOID CURRENT_USER；

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE8i 可以支持事務處理中的事務處理的概念．這種子事務處理可以完成它自己的工作，獨立于父事務處理進行提交或者回滾．通過使用這種方法，開發(fā)者就能夠這樣的過程，無論父事務處理是提交還是回滾，它都可以成功執(zhí)行．

例16：建立過程，使用自動事務處理進行日志記錄；

DROP TABLE logtable;

CREATE TABLE logtable(Username varchar2(20), Dassate_time date, Mege varchar2(60));

CREATE TABLE temp_table(N number);

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)AS PRAGMA AUTONOMOUS_TRANSACTION;BEGIN

INSERT INTO logtable VALUES(user, sysdate, p_message);COMMIT;END log_message;BEGIN

Log_message(‘About to insert into temp_table‘);

INSERT INTO temp_table VALUES(1);Log_message(‘Rollback to insert into temp_table‘);

ROLLBACK;END;

SELECT * FROM logtable;SELECT * FROM temp_table;

例17：建立過程，沒有使用自動事務處理進行日志記錄；

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)AS BEGIN

INSERT INTO logtable VALUES(user, sysdate, p_message);COMMIT;END log_message;BEGIN

Log_message('About to insert into temp_table');INSERT INTO temp_table VALUES(1);Log_message('Rollback to insert into temp_table');ROLLBACK;END;

SELECT * FROM logtable;SELECT * FROM temp_table;

6.3.5 開發(fā)存儲過程步驟

開發(fā)存儲過程、函數(shù)、包及觸發(fā)器的步驟如下：

6.3.5.1 使用文字編輯處理軟件編輯存儲過程源碼 使用文字編輯處理軟件編輯存儲過程源碼，要用類似WORD 文字處理軟件進行編輯時，要將源碼存為文本格式。

6.3.5.2 在SQLPLUS或用調試工具將存儲過程程序進行解釋

在SQLPLUS或用調試工具將存儲過程程序進行解釋；

在SQL>下調試，可用START 或GET 等ORACLE命令來啟動解釋。如： SQL>START c:stat1.sql

如果使用調式工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。

6.3.5.3 調試源碼直到正確

我們不能保證所寫的存儲過程達到一次就正確。所以這里的調式是每個程序員必須進行的工作之一。在SQLPLUS下來調式主要用的方法是：

? 使用 SHOW ERROR命令來提示源碼的錯誤位置；

? 使用 user_errors 數(shù)據(jù)字典來查看各存儲過程的錯

誤位置。

6.3.5.4 授權執(zhí)行權給相關的用戶或角色

如果調式正確的存儲過程沒有進行授權，那就只有建立者本人才可以運行。所以作為應用系統(tǒng)的一部分的存儲過程也必須進行授權才能達到要求。在SQL*PLUS下可以用GRANT命令來進行存儲過程的運行授權。

GRANT語法：

GRANT system_privilege | role TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object TO user | role | PUBLIC [WITH GRANT OPTION]

--例子：

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

6.3.5.5 與過程相關數(shù)據(jù)字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS, ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相關的權限:

CREATE ANY PROCEDURE DROP ANY PROCEDURE

在SQL*PLUS 中，可以用DESCRIBE 命令查看過程的名字及其參數(shù)表。

DESC[RIBE] Procedure_name;

6.3.6 刪除過程和函數(shù)

1．刪除過程

可以使用DROP PROCEDURE命令對不需要的過程進行刪除，語法如下：

DROP PROCEDURE [user.]Procudure_name;

2．刪除函數(shù)

可以使用DROP FUNCTION 命令對不需要的函數(shù)進行刪除，語法如下：

DROP FUNCTION [user.]Function_name;

--刪除上面實例創(chuàng)建的存儲過程與函數(shù) DROP PROCEDURE logexecution;DROP PROCEDURE delemp;DROP PROCEDURE insertemp;DROP PROCEDURE fireemp;DROP PROCEDURE queryemp;DROP PROCEDURE proc_demo;DROP PROCEDURE log_message;DROP FUNCTION demo_fun;DROP FUNCTION get_salary;

6.3.7 過程與函數(shù)的比較

使用過程與函數(shù)具有如下優(yōu)點：

1、共同使用的代碼可以只需要被編寫和測試一次，而被需要該代碼的任何應用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL庫）調用。

2、這種集中編寫、集中維護更新、大家共享（或重用）的方法，簡化了應用程序的開發(fā)和維護，提高了效率與性能。

3、這種模塊化的方法，使得可以將一個復雜的問題、大的程序逐步簡化成幾個簡單的、小的程序部分，進行分別編寫、調試。因此使程序的結構清晰、簡單，也容易實現(xiàn)。

4、可以在各個開發(fā)者之間提供處理數(shù)據(jù)、控制流程、提示信息等方面的一致性。

5、節(jié)省內存空間。它們以一種壓縮的形式被存儲在外存中，當被調用時才被放入內存進行處理。并且，如果多個用戶要執(zhí)行相同的過程或函數(shù)時，就只需要在內存中加載一個該過程或函數(shù)。

6、提高數(shù)據(jù)的安全性與完整性。通過把一些對數(shù)據(jù)的操作放到過程或函數(shù)中，就可以通過是否授予用戶有執(zhí)行該過程或的權限，來限制某些用戶對數(shù)據(jù)進行這些操作。

過程與函數(shù)的相同功能有：

1、都使用IN模式的參數(shù)傳入數(shù)據(jù)、OUT模式的參數(shù)返

回數(shù)據(jù)。

2、輸入?yún)?shù)都可以接受默認值，都可以傳值或傳引導。

3、調用時的實際參數(shù)都可以使用位置表示法、名稱表示

法或組合方法。

4、都有聲明部分、執(zhí)行部分和異常處理部分。

5、其管理過程都有創(chuàng)建、編譯、授權、刪除、顯示依賴

關系等。

使用過程與函數(shù)的原則：

1、如果需要返回多個值和不返回值，就使用過程；如果只需要返回一個值，就使用函數(shù)。

2、過程一般用于執(zhí)行一個指定的動作，函數(shù)一般用于計算和返回一個值。

3、可以SQL語句內部（如表達式）調用函數(shù)來完成復雜的計算問題，但不能調用過程。所以這是函數(shù)的特色。

第二篇：oracle中的sign函數(shù)詳解

sign

在Oracle/PLSQL中, sign 函數(shù)返回一個數(shù)字的正負標志.語法如下：sign(number)number 要測試標志的數(shù)字.If number < 0, then sign returns-1.If number = 0, then sign returns 0.If number > 0, then sign returns 1.應用于: ? Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g 例如: sign(-23)would return-1 sign(0.001)would return-1 sign(0)would return 0 sign(0.001)would return 1 sign(23)would return 1 sig(23.601)would return 1

第三篇：Kettle創(chuàng)建oracle連接

Kettle創(chuàng)建oracle連接，在test的時候報錯的解決方案

1、先點擊Cancel進入kettle主界面

依次FILE--NEW--JOB OR TRANSFORMATION，在transformation頁面下，點擊左邊的【Main Tree】【主對象樹】，雙擊【DB連接】，進行

數(shù)據(jù)庫連接配置 插播我的kettle配置： 我的版本是kettle4.4 環(huán)境變量設置：KETTLE_HOME F:ToolsKettle4.4data-integration

Path % KETTLE_HOME% 首先找到

Kettle 的主程序啟動方式：運行 data-integrationSpoon.bat。

Kettle 默認不帶 Oracle 驅動。于是我把 Oracle 的 JDBC 驅動 ojdbc14.jar/ojdbc14_g/ojdbc14dms/ojdbc14dms_g.jar 放到了 Kettle 的目錄data-integrationlibextJDBC

我遇到的連接錯誤：

錯誤連接數(shù)據(jù)庫 [orcl] : org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database

Error connecting to database:(using class oracle.jdbc.driver.OracleDriver)Io 異常: The Network Adapter could not establish the connection

org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database

Error connecting to database:(using class oracle.jdbc.driver.OracleDriver)Io 異常: The Network Adapter could not establish the connection

3、原因：

--

1、connection name:orcl【這個是我的數(shù)據(jù)庫名，連接時我也用這個名字，DB名字也是這個】

--

2、Host-name：說實話，我重裝系統(tǒng)之后還真沒注意主機名是什么，我是在

E:oracleproduct10.2.0db_1networkADMIN中的tnsnames.ora中查到的，ORCL =(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = john-PC)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =orcl)))端口號寫1521 Username和password 就是我連接oracle的用戶名和密碼，點擊test按鈕，終于連接成功了！

第四篇：oracle創(chuàng)建臨時表

Oracle數(shù)據(jù)庫臨時表管理心得

我們在創(chuàng)建數(shù)據(jù)表的時候，若沒有特殊的指明，那么我們創(chuàng)建的表是一個永久的關系型表格，也就是說，這個表格中對應的數(shù)據(jù)，除非是我們顯示的刪除的話，表中的數(shù)據(jù)是永遠都存在的。相對應的，在Oracle數(shù)據(jù)庫中還有一種類型的表，叫做臨時表。這個臨時表跟永久表最大的區(qū)別就是表中的數(shù)據(jù)不會永遠的存在。當一個會話結束或者事務結束的時候，這個臨時表中的數(shù)據(jù)，不用用戶自己刪除，數(shù)據(jù)庫自己會自動清

除。

1、事務臨時表的管理。

(1)事務臨時表的創(chuàng)建。

Oracle數(shù)據(jù)庫根據(jù)臨時表的性質不同，可以分為事務臨時表與會話臨時表。事務臨時表是指數(shù)據(jù)只有在當前事務內有效。一般情況下，如果在創(chuàng)建數(shù)據(jù)表的時候，沒有特殊指明這表是會話臨時表的話，則該表默認為事務臨時表。

我們可以以下面的語句創(chuàng)建事務臨時表。

Create global temporary table Temp_user

(ID NUMBER(12)Primary key,name varchar2(10));

筆者建議：

這個創(chuàng)建臨時表的語句中，雖然沒有顯性的指明該表是事務臨時表，但是，默認的情況下，若沒有指明是什么臨時表的話，系統(tǒng)默認是事務臨時表。我們要創(chuàng)建事務臨時表時，可以不指定關鍵字。但是，這查看起來比較麻煩。我建議，無論在建立什么臨時表，都要利用具體的關鍵字來顯形的指明，這大家看起來都方便。一般可以利用ON COMMIT DELETE ROWS關鍵字來說明該表就是事務性的臨時表，而不是會話性質的臨時表。

(2)事務臨時表數(shù)據(jù)的變化分析。

事務臨時表的話，當事務結束的時候，就會清空這個事務臨時表。所以，當我們在數(shù)據(jù)庫臨時表中插入數(shù)據(jù)后，只要事務沒有提交的話，該表中的數(shù)據(jù)就會存在。但是，當事務提交以后，該表中的數(shù)據(jù)就會被刪除。而且，這個變化不會在重做日志中

顯示。

具體事務臨時表與會話臨時表有什么區(qū)別，我們在介紹完會話臨時表后會詳細介

紹。

2、會話臨時表的管理。

會話臨時表，顧名思義，是指數(shù)據(jù)只在當前會話內有效的臨時表。關閉當前會話或者進行新的連接之后，數(shù)據(jù)表中的內容就會被清除。那會話臨時表跟事務臨時表到底有什么區(qū)別呢?我們以一個實例來看其中的區(qū)別。

(1)首先，創(chuàng)建一個會話臨時表。

CREATE GLOBAL TEMPOPARY TABLE TEMP_USER

(ID NUMBER(12)Primary key,name varchar2(10))

ON COMMIT PRESERVE ROWS;

也就是說，會話臨時表跟事務臨時表的創(chuàng)建語法大致相同，只有最后的關鍵字有區(qū)別。不過兩個表雖然類似，但是其內部的處理機制還是有比較大的區(qū)別。

(2)往該表中插入數(shù)據(jù)。

Insert into TEMP_USER values(1001,’victor’);

往數(shù)據(jù)庫臨時表中插入數(shù)據(jù)的方法，跟往普通表中插入數(shù)據(jù)的方法是一樣的，都利用insert into語句進行操作。該臨時表的數(shù)據(jù)在會話結束之前都是存在這個表格

中的。

(3)提交該事務并查詢相關記錄。

我們利用COMMIT的語句把該事務提交以后，再用SELECT查詢語句進行查詢。我們知道，若該表是事務臨時表的話，則當該事務結束以后，該表中的內容就會被刪除。但是，這是會話臨時表，所以即使該事務提交了，但是，利用SELECT語句進行查詢

時，仍然可以查到該條員工記錄。

(4)結束當前會話，并重新連接數(shù)據(jù)庫。

關閉當前會話，從新連接到數(shù)據(jù)庫后，再利用SELECT語句查詢時，會有什么結果呢?此時，就查不到我們剛才插入的數(shù)據(jù)。這也就是說，在關閉對話的時候，數(shù)據(jù)庫系統(tǒng)已經(jīng)把原有的數(shù)據(jù)刪除了。從以上的分析我們可以看中，會話臨時表與事務臨時表主要的差異就在于刪除數(shù)據(jù)時機的不同。事務性臨時表是在事務提交的時候清除數(shù)據(jù)，而會話性臨時表則是在關閉當前會話的時候清除臨時表。只要當前會話沒有關閉，即使事務完成了，會話臨時表中的數(shù)據(jù)仍然存在，不會被清除。

3、臨時表管理需要注意的地方。

臨時表相對與其他表來說，是一種比較特殊的表結構，但是，作用又比較大，Oracle數(shù)據(jù)庫若沒有這種表的話，還真是不行。為了管理好這種特殊的表，我們需要

注意幾個細節(jié)。

一是要注意臨時表不能永久的保存數(shù)據(jù)。只所以稱為臨時表，就是因為該表中的內容只是臨時存在的。當一個會話或者事務結束時，該表中的內容就會被自動清空。所以，在臨時表中，一般不要保存永久數(shù)據(jù)。在實務中，有個不好的操作習慣，就是有些人在測試數(shù)據(jù)庫的時候，喜歡把測試的數(shù)據(jù)放在臨時數(shù)據(jù)表中。其實，這是對Oralce臨時數(shù)據(jù)表認識的錯誤。若我們在數(shù)據(jù)庫中，把要測試的數(shù)據(jù)，如銷售定單的內容放在數(shù)據(jù)庫的臨時表中的話，則在其他功能中，如要測試銷售定單日報表的功能時，就會找不到相關的定單內容。因為離開特定的會話或者事務的話，臨時表中的內容就會不存在了。所以，Oralce數(shù)據(jù)庫中所講的臨時表不是給我們來存儲測試數(shù)據(jù)的。

二是臨時表中的數(shù)據(jù)不會備份、恢復，對其的修改也不會有任何的日志信息。若我們在操作數(shù)據(jù)庫的時候，往數(shù)據(jù)庫的臨時表中存入了一些信息。此時突然服務器出現(xiàn)當機。此時，我們想通過數(shù)據(jù)庫備份文件恢復數(shù)據(jù)庫臨時表中的內容，或者查看臨時表的日志信息，都是無法實現(xiàn)的。也就是說，當服務器以外死機重新啟動后，臨時表中的內容就會被清空。在數(shù)據(jù)庫的任何地方，如數(shù)據(jù)庫備份文件或者日志信息中，都查不到在重新啟動之前數(shù)據(jù)庫臨時表中保存了哪些內容，就好象根本沒有對臨時表

進行操作一樣。

三是臨時表表空間的管理。臨時表在Oracle數(shù)據(jù)庫中，也是表的一種，其也有對應的表空間。在創(chuàng)建臨時表的時候，若我們不指定表空間的話，默認的表空間是SYSTEM。對于臨時表的表空間管理的話，我們需要注意一個小的細節(jié)。若我們把臨時表的表空間歸屬為SYSTEM的話，也就是說，在創(chuàng)建臨時表的時候不具體指定具體的表空間，則這個默認的表空間是不能被刪除的。而若我們在創(chuàng)建臨時表表空間的時候，指定為SYSTEM以外的表空間的話，則在不需要這表空間的時候，我們可以刪除。所以，為了后續(xù)管理的方便，筆者還是建議大家在創(chuàng)建臨時表的時候，要指定表空間。

四是要注意一個問題，臨時表只是數(shù)據(jù)是臨時的，而表仍然是永久的。也就是說，當一個會話結束或者一個事務完成時，其臨時表中的數(shù)據(jù)雖然刪除了，但是，臨時表本身仍然是存在的。也就是說。Oracle數(shù)據(jù)庫中的臨時表表是全局的，只是數(shù)據(jù)是臨時的。這跟SQL Server數(shù)據(jù)庫系統(tǒng)具有比較大的區(qū)別。其實，這兩個數(shù)據(jù)庫在臨時表的處理上有很大的不同，各有各的特色。在以后的文章中，我會專門敘述這兩種數(shù)據(jù)庫在臨時表管理機制上的不同，歡迎大家關注。

五是要注意Oracle數(shù)據(jù)庫在給臨時表填入數(shù)據(jù)的時候，不會對相應的記錄加鎖。也就是說，當在臨時表上執(zhí)行DML語句的操作時，不會給記錄加鎖，也不會將數(shù)據(jù)的變化內容寫到重做(REDO)日志中。所以不能用臨時表保存永久的數(shù)據(jù)，也不能對臨時表進行共同的操作。這是新手在管理數(shù)據(jù)庫臨時表經(jīng)常會碰到的問題。

六是臨時表與普通表之間不能相互轉換。在一般情況下，臨時表建立后，該表就不能被轉換成永久表。所以，這也說明一個道理，利用臨時表作為數(shù)據(jù)庫設計時候的測試表不合適。這個臨時表可能跟我們按字面意思理解的臨時表有誤，不是我們所認為的為了測試表結構而建立的臨時表。這一點是我們在剛開始接觸ORACLE數(shù)據(jù)庫時，經(jīng)常會犯的錯誤。

第五篇：18位身份證和組織機構代碼校驗ORACLE函數(shù)

18位身份證和組織機構代碼校驗ORACLE函數(shù) 18位身份證和組織機構代碼校驗ORACLE函數(shù)

18位身份證標準在國家質量技術監(jiān)督局于1999年7月1日實施的GB11643-1999《公民身份號碼》中做了明確規(guī)定。

GB11643-1999《公民身份號碼》為GB11643-1989《社會保障號碼》的修訂版，其中指出將原標準名稱“社會保障號碼”更名為“公民身份號碼”，另外GB11643-1999《公民身份號碼》從實施之日起代替GB11643-1989。

公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位校驗碼。其含義如下：

1.地址碼：表示編碼對象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼，按GB/T2260的規(guī)定執(zhí)行。

2.出生日期碼：表示編碼對象出生的年、月、日，按GB/T7408的規(guī)定執(zhí)行，年、月、日分別用4位、2位、2位數(shù)字表示，之間不用分隔符。

3.順序碼：表示在同一地址碼所標識的區(qū)域范圍內，對同年、同月、同日出生的人編定的順序號，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性。

校驗的計算方式：

1.對前17位數(shù)字本體碼加權求和

公式為：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份證號碼數(shù)字值，Wi表示第i位置上的加權因子，其各位對應的值依次為： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.以11對計算結果取模

Y = mod(S, 11)

3.根據(jù)模的值得到對應的校驗碼

對應關系為：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校驗碼： 1 0 X 9 8 7 6 5 4 3 2

15位的身份證號

dddddd yymmdd xx p

18位的身份證號

dddddd yyyymmdd xx p y

其中dddddd為地址碼（省地縣三級）18位中的和15位中的不完全相同 yyyymmdd yymmdd 為出生年月日 xx順號類編碼 p性別

18位中末尾的y為校驗碼，在網(wǎng)上可以找到算法

（1）、前兩個數(shù)字代表省份編碼。如：32表示江蘇省，34代表安徽省……（2）、第3、4位上的數(shù)字代表市級編碼。如：01代表南京市，05代表蘇州市……（3）、第5、6位上的數(shù)字代表區(qū)或縣編碼。如：02代表玄武區(qū)，11代表浦口區(qū)……

（4）、在第7-14位上行政區(qū)代碼后的數(shù)字表示某個人的出生年月。提問：“為什么2月要用02來表示而不用2來表示呢？”（用0來占位可以統(tǒng)一位數(shù)。）

（5）、出生年、月、日后面的兩個數(shù)字表示居住地所在的派出所編碼。如陽溝街所在的號碼是04。（6）、老身份證的最后一位，新身份證的倒數(shù)第二位數(shù)表示性別。提問：哪些數(shù)字表示男性？（1、3、5、7、9這樣的單數(shù)表示男性）。哪些數(shù)字表示女性？（0、2、4、6、8這樣的雙數(shù)表示女性）。（7）、新身份證的最后一位是前面17個數(shù)字按一定的公式加減乘除得來的，作為個人信息編碼，有的也用X來表示。目的是為了進一步防偽，至于計算公式，只有公安機關內部才知道

本資料從網(wǎng)上多處說明整理而來。

18位身份證標準在國家質量技術監(jiān)督局于1999年7月1日實施的GB11643-1999《公民身份號碼》中做了明確規(guī)定。

GB11643-1999《公民身份號碼》為GB11643-1989《社會保障號碼》的修訂版，其中指出將原標準名稱“社會保障號碼”更名為“公民身份號碼”，另外GB11643-1999《公民身份號碼》從實施之日起代替GB11643-1989。

公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位校驗碼。其含義如下：

1.地址碼：表示編碼對象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼，按GB/T2260的規(guī)定執(zhí)行。

2.出生日期碼：表示編碼對象出生的年、月、日，按GB/T7408的規(guī)定執(zhí)行，年、月、日分別用4位、2位、2位數(shù)字表示，之間不用分隔符。

3.順序碼：表示在同一地址碼所標識的區(qū)域范圍內，對同年、同月、同日出生的人編定的順序號，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性。

校驗的計算方式：

1.對前17位數(shù)字本體碼加權求和

公式為：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份證號碼數(shù)字值，Wi表示第i位置上的加權因子，其各位對應的值依次為： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.以11對計算結果取模

Y = mod(S, 11)

3.根據(jù)模的值得到對應的校驗碼

對應關系為：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校驗碼： 1 0 X 9 8 7 6 5 4 3 2

關于18位身份證號碼尾數(shù)是“X”的解釋

居民身份證的號碼是按照國家的標準編制的，由18位組成：前六位為行政區(qū)劃代碼，第七至第十四位為出生日期碼，第15至17位為順序碼，第18位為校驗碼。作為尾號的校驗碼，是由號碼編制單位按統(tǒng)一的公式計算出來的，如果某人的尾號是0-9，都不會出現(xiàn)X，但如果尾號是10，那么就得用X來代替，因為如果用10做尾號，那么此人的身份證就變成了19位，而19位的號碼違反了國家標準，并且我國的計算機應用系統(tǒng)也不承認19位的身份證號碼。Ⅹ是羅馬數(shù)字的10，用X來代替10，可以保證公民的身份證符合國家標準。但是我國的居民身份證在升位后，一些人的尾號變成了X，這部分人在工作生活中，例如去銀行存取錢、去汽車公司租賃汽車或者報名參加考試等等過程中，往往不被檢驗者理解，認為是假身份證，這樣的誤會給很多人的生活帶來不便。公安局的同志希望領到X身份證的同志正確理解這個數(shù)字的含義，查驗身份證的機關和單位更應該清楚這一點。根據(jù)〖中華人民共和國國家標準 GB 11643-1999〗中有關公民身份號碼的規(guī)定，公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位數(shù)字校驗碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗碼。

地址碼表示編碼對象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼。生日期碼表示編碼對象出生的年、月、日，其中年份用四位數(shù)字表示，年、月、日之間不用分隔符。順序碼表示同一地址碼所標識的區(qū)域范圍內，對同年、月、日出生的人員編定的順序號。順序碼的奇數(shù)分給男性，偶數(shù)分給女性。校驗碼是根據(jù)前面十七位數(shù)字碼，按照ISO 7064:1983.MOD 11-2校驗碼計算出來的檢驗碼。

★ 地址碼含義

身份證前六位是地區(qū)代碼，代碼的解釋規(guī)則如下： ABCDEF

A:國內區(qū)域 1 華北 三省二市 2 東北 三省 3 華東 六省一市 4 華南 六省 5 西南 四省一市 6 西北 五省 7 臺灣 8 港澳

B:省代碼

按照A劃定的分區(qū)定義省代碼，有直轄市的，直轄市列前，其余按離直轄市的距離排序，沒有直轄市的，按離北京的遠近排序。

11-15 京 津 冀 晉 蒙 21-23 遼 吉 黑

31-37 滬 蘇 浙 皖 閩 贛 魯 41-46 豫 鄂 湘 粵 桂 瓊 50-54 渝 川 貴 云 藏 61-65 陜 甘 青 寧 新 81-82 港 澳

CD:城市代碼

從01開始排，對于直轄市，CD=01表示市轄區(qū)，CD=02表示轄縣； 省的城市代碼從省會開始排，其余依92式機動車號牌的順序排列，比如2101=沈陽 2102=大連……

只有地級城市有獨立的城市代碼，縣級市沒有。EF:市轄區(qū)、郊區(qū)、郊縣、縣級市代碼 如果EF=00，指代這個城市，不特定區(qū)縣；

對于非直轄市，如EF=01，指代市轄區(qū)（任意一個區(qū)），02開始指代特定的區(qū)。其中：

E=0代表市轄區(qū)，E=1代表郊區(qū)，E=2代表郊縣，E=8代表縣級市。

對于直轄市，從01開始就依次排區(qū)，沒有市區(qū)和郊區(qū)的代碼區(qū)分。

★ 校驗碼計算方法

下面舉例說明校驗碼的計算方法。

15位的身份證編碼首先把出生年擴展為4位，簡單的就是增加一個19，但是這對于1900年出生的人不使用（這樣的壽星不多了）

某男性公民身份號碼本體碼為***01，首先按照公式(1)計算：

∑(ai×Wi)mod 11……………………………………(1)

公式(1)中：

i----表示號碼字符從右至左包括校驗碼在內的位置序號； ai----表示第i位置上的號碼字符值；

Wi----示第i位置上的加權因子，其數(shù)值依據(jù)公式Wi=（2 的 i-1 次冪）(mod 11)計算得出。

i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 ai 3 4 0 5 2 4 1 9 8 0 0 1 0 1 0 0 1 a1 Wi 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1

ai×Wi 21 36 0 25 16 16 2 9 48 0 0 9 0 5 0 0 2 a1

根據(jù)公式(1)進行計算：

∑(ai×Wi)=（21+36+0+25+16+16+2+9+48++0+0+9+0+5+0+0+2)= 189

189 ÷ 11 = 17 + 2/11

∑(ai×Wi)mod 11 = 2

然后根據(jù)計算的結果，從下面的表中查出相應的校驗碼，其中X表示計算結果為10（男性為大寫X；女性為小寫x）：

∑(ai×WI)(mod 11)0 1 2 3 4 5 6 7 8 9 10 校驗碼字符值ai 1 0 X 9 8 7 6 5 4 3 2

根據(jù)上表，查出計算結果為2的校驗碼為所以該人員的公民身份號碼應該為 ***01X。程序代碼:

CREATE OR REPLACE FUNCTION f_Id_Verify(Pid VARCHAR2)RETURN VARCHAR2 IS RESULT VARCHAR2(58);TYPE Char_Tabletype IS TABLE OF VARCHAR2(1)NOT NULL INDEX BY BINARY_INTEGER;TYPE Num_Tabletype IS TABLE OF NUMBER NOT NULL INDEX BY BINARY_INTEGER;Tab_a Char_Tabletype;Tab_w Num_Tabletype;Tab_i Char_Tabletype;i NUMBER(2):= 0;Len NUMBER(2):= Length(Rtrim(Ltrim(Pid)));Sigma NUMBER(4):= 0;BEGIN Tab_i(0):= '1';Tab_i(1):= '0';Tab_i(2):= 'X';Tab_i(3):= '9';Tab_i(4):= '8';Tab_i(5):= '7';Tab_i(6):= '6';Tab_i(7):= '5';Tab_i(8):= '4';Tab_i(9):= '3';Tab_i(10):= '2';--Pid := TRIM(Pid);IF Len = 18 THEN FOR i IN 1..17 LOOP Tab_w(i):= MOD(Power(2,((191)), 11);Tab_a(i):= Substr(Pid, i, 1);Sigma := Sigma + Tab_w(i)* Tab_a(i);END LOOP;RESULT := Substr(Pid, 1, 17)|| Tab_i(MOD(Sigma, 11));END IF;IF RESULT = Pid THEN RETURN('正確');ELSE RETURN('錯誤,應該是：' || RESULT);END IF;EXCEPTION WHEN OTHERS THEN BEGIN RETURN('錯誤');Dbms_Output.Put_Line('發(fā)生了異常的錯誤');END;END f_Id_Verify;/

組織機構代碼是每一個機關、社會團體、企事業(yè)單位在全國范圍內唯一的、始終不變的法定代碼標識。最新使用的組織機構代碼在1997年頒布實施，由8位數(shù)字（或大寫拉丁字母）本體代碼和1位數(shù)字（或大寫拉丁字母）校驗碼組成。本體代碼采用系列（即分區(qū)段）順序編碼方法。校驗碼按下列公式計算： 8 C9 = 11MOD(∑Ci * Wi，11)…(2)FOR i IN 1..8 LOOP Tab_w(i):= MOD(Power(2,((101)), 11);--Tab_w(i):= MOD(Power(2,((101)), 11);就是加權算法值 Tab_a(i):= Substr(Pid, i, 1);Sigma := Sigma + Tab_w(i)* Tab_a(i);END LOOP;IF MOD(Sigma, 11)= 1 THEN RESULT := Substr(Pid, 1, 8)|| 'X';ELSE RESULT := Substr(Pid, 1, 8)|| To_Char(11-MOD(Sigma, 11));END IF;END IF;IF RESULT = Pid THEN RETURN('正確');ELSE RETURN('錯誤,應該是：' || RESULT);END IF;EXCEPTION WHEN OTHERS THEN BEGIN RETURN('錯誤');Dbms_Output.Put_Line('發(fā)生了異常的錯誤');END;END Jgid_Verify;