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

DBMS_OUTPUT.PUT_LINE('ept_20002ûÓиõصã'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others³öÏÖÁËÆäËûÒì³£´íÎó'); 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³öÏÖÁËÆäËûÒì³£´íÎó'); END; */

6.3.2 µ÷Óô洢¹ý³Ì

´æ´¢¹ý³Ì½¨Á¢Íê³Éºó£¬Ö»ÒªÍ¨¹ýÊÚȨ£¬Óû§¾Í¿ÉÒÔÔÚ

SQLPLUS ¡¢ORACLE¿ª·¢¹¤

¾ß»òµÚÈý·½¿ª·¢¹¤¾ßÖÐÀ´µ÷ÓÃÔËÐС£¶ÔÓÚ²ÎÊýµÄ´«µÝÒ²ÓÐÈýÖÖ£º°´Î»Öô«µÝ¡¢°´Ãû³Æ´«µÝºÍ×éºÏ´«µÝ£¬´«µÝ·½·¨Ó뺯ÊýµÄÒ»Ñù¡£ORACLE ʹÓÃEXECUTE Óï¾äÀ´ÊµÏÖ¶Ô´æ´¢¹ý³ÌµÄµ÷Óãº

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||'µÄÔ±¹¤ÒѾ­²éµ½!'); EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('ÎÂÜ°Ìáʾ:ÄãÐèÒªµÄÊý¾Ý²»´æÔÚ!'); 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£®¼ÆËãÖ¸¶¨²¿ÃŵŤ×Ê×ܺͣ¬²¢Í³¼ÆÆäÖеÄÖ°¹¤ÊýÁ¿¡£

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('ÎÂÜ°Ìáʾ:ÄãÐèÒªµÄÊý¾Ý²»´æÔÚ!'); 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||',ÈËÊý£º'||v_num); Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('ÎÂÜ°Ìáʾ:10ºÅ²¿ÃŹ¤×Ê×ܺͣº'||v_sum||',ÈËÊý£º'||v_num); END;

ÔÚ

PL/SQL ³ÌÐòÖл¹¿ÉÒÔÔÚ¿éÄÚ½¨Á¢±¾µØº¯ÊýºÍ¹ý³Ì£¬ÕâЩº¯ÊýºÍ¹ý³Ì²»´æ´¢ÔÚÊý

¾Ý¿âÖУ¬µ«¿ÉÒÔÔÚ´´½¨ËüÃǵÄPL/SQL ³ÌÐòÖб»Öظ´µ÷Óᣱ¾µØº¯ÊýºÍ¹ý³ÌÔÚPL/SQL ¿éµÄÉùÃ÷²¿·Ö¶¨Ò壬ËüÃǵÄÓï·¨¸ñʽÓë´æ´¢º¯ÊýºÍ¹ý³ÌÏàͬ£¬µ«²»ÄÜʹÓÃCREATE OR REPLACE ¹Ø¼ü×Ö¡£

Àý13£º½¨Á¢±¾µØ¹ý³Ì£¬ÓÃÓÚ¼ÆËãÖ¸¶¨²¿ÃŵŤ×Ê×ܺͣ¬²¢Í³¼ÆÆäÖеÄÖ°¹¤ÊýÁ¿£»

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('ÄãÐèÒªµÄÊý¾Ý²»´æÔÚ!'); 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||'£¬ÈËÊý£º'||v_num); Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10ºÅ²¿ÃŹ¤×Ê×ܺͣº'||v_sum||'£¬ÈËÊý£º'||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£»

ÁªÏµ¿Í·þ£º779662525#qq.com(#Ì滻Ϊ@)