Oracle´´½¨º¯ÊýºÍ¹ý³Ì

ÎÞÂÛ²ÉÓÃÄÄÒ»ÖÖ²ÎÊý´«µÝ·½·¨£¬Êµ¼Ê²ÎÊýºÍÐÎʽ²ÎÊýÖ®¼äµÄÊý¾Ý´«µÝÖ»ÓÐÁ½ÖÖ·½·¨£º´«Ö··¨ºÍ´«Öµ·¨¡£Ëùν´«Ö··¨ÊÇÖ¸ÔÚµ÷Óú¯Êýʱ£¬½«Êµ¼Ê²ÎÊýµÄµØÖ·Ö¸Õë´«µÝ¸øÐÎʽ²ÎÊý£¬Ê¹ÐÎʽ²ÎÊýºÍʵ¼Ê²ÎÊýÖ¸ÏòÄÚ´æÖеÄÍ¬Ò»ÇøÓò£¬´Ó¶øÊµÏÖ²ÎÊýÊý¾ÝµÄ´«µÝ¡£ÕâÖÖ·½·¨ÓÖ³Æ×÷²ÎÕÕ·¨£¬¼´ÐÎʽ²ÎÊý²ÎÕÕʵ¼Ê²ÎÊýÊý¾Ý¡£ÊäÈë²ÎÊý¾ù²ÉÓô«Ö··¨´«µÝÊý¾Ý¡£ ´«Öµ·¨ÊÇÖ¸½«Êµ¼Ê²ÎÊýµÄÊý¾Ý¿½±´µ½ÐÎʽ²ÎÊý£¬¶ø²»ÊÇ´«µÝʵ¼Ê²ÎÊýµÄµØÖ·¡£Ä¬ÈÏʱ£¬Êä³ö²ÎÊýºÍÊäÈë/Êä³ö²ÎÊý¾ù²ÉÓô«Öµ·¨¡£ÔÚº¯Êýµ÷ÓÃʱ£¬ORACLE½«Êµ¼Ê²ÎÊýÊý¾Ý¿½±´µ½ÊäÈë/Êä³ö²ÎÊý£¬¶øµ±º¯ÊýÕý³£ÔËÐÐÍ˳öʱ£¬ÓÖ½«Êä³öÐÎʽ²ÎÊýºÍÊäÈë/Êä³öÐÎʽ²ÎÊýÊý¾Ý¿½±´µ½Êµ¼Ê²ÎÊý±äÁ¿ÖС£

3. ²ÎÊýĬÈÏÖµ

ÔÚCREATE OR REPLACE FUNCTION Óï¾äÖÐÉùÃ÷º¯Êý²ÎÊýʱ¿ÉÒÔʹÓÃDEFAULT¹Ø¼ü×ÖΪÊäÈë²ÎÊýÖ¸¶¨Ä¬ÈÏÖµ¡£

Àý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;

¾ßÓÐĬÈÏÖµµÄº¯Êý´´½¨ºó£¬ÔÚº¯Êýµ÷ÓÃʱ£¬Èç¹ûûÓÐΪ¾ßÓÐĬÈÏÖµµÄ²ÎÊýÌṩʵ¼Ê²ÎÊýÖµ£¬º¯Êý½«Ê¹ÓøòÎÊýµÄĬÈÏÖµ¡£µ«µ±µ÷ÓÃÕßΪĬÈϲÎÊýÌṩʵ¼Ê²ÎÊýʱ£¬º¯Êý½«Ê¹ÓÃʵ¼Ê²ÎÊýÖµ¡£ÔÚ´´½¨º¯Êýʱ£¬Ö»ÄÜΪÊäÈë²ÎÊýÉèÖÃĬÈÏÖµ£¬¶ø²»ÄÜΪÊäÈë/Êä³ö²ÎÊýÉèÖÃĬÈÏÖµ¡£

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 ´´½¨¹ý³Ì

½¨Á¢´æ´¢¹ý³Ì

ÔÚ ORACLE SERVERÉϽ¨Á¢´æ´¢¹ý³Ì,¿ÉÒÔ±»¶à¸öÓ¦ÓóÌÐòµ÷ÓÃ,¿ÉÒÔÏò´æ´¢¹ý³Ì´«µÝ²ÎÊý,Ò²¿ÉÒÔÏò´æ´¢¹ý³Ì´«»Ø²ÎÊý.

´´½¨¹ý³ÌÓï·¨:

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 <Ö´Ðв¿·Ö> EXCEPTION

<¿ÉÑ¡µÄÒì³£´íÎó´¦Àí³ÌÐò> END procedure_name;

˵Ã÷£ºÏà¹Ø²ÎÊý˵Ã÷²Î¼ûº¯ÊýµÄÓ﷨˵Ã÷¡£

Àý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('ÎÂܰÌáʾ:ÄãÐèÒªµÄÊý¾Ý²»´æÔÚ!'); 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('ÎÂܰÌáʾ:²åÈëÊý¾Ý¼Ç¼³É¹¦!'); EXCEPTION

WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('ÎÂܰÌáʾ:Î¥·´Êý¾ÝÍêÕûÐÔÔ¼Êø!'); WHEN OTHERS THEN

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

Àý9£®Ê¹Óô洢¹ý³ÌÏò

departments±íÖвåÈëÊý¾Ý¡£

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

ÁªÏµ¿Í·þ£º779662525#qq.com(#Ìæ»»Îª@)