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£»