·Ö»ØÍ˲åÈë²Ù×÷£¬È»ºóÏÔʾÏàÓ¦µÄ´íÎóÐÅÏ¢¡£
¡¾ÑµÁ·2¡¿ ʹÓÃRAISE_APPLICATION_ERRORº¯ÊýÒý·¢ÏµÍ³Òì³£¡£ Sql´úÂë
1. SET SERVEROUTPUT ON 2. DECLARE
3. New_no NUMBER(10); 4. BEGIN
5. New_no:=6789;
6. INSERT INTO emp(empno,ename) 7. VALUES(new_no, 'JAMES');
8. IF new_no<7000 THEN 9. ROLLBACK;
10. RAISE_APPLICATION_ERROR(-20001, '±àºÅСÓÚ7000µÄÏÂÏÞ£¡'); 11. END IF;
12. IF new_no>8000 THEN
13. ROLLBACK;
14. RAISE_APPLICATION_ERROR (-20002, '±àºÅ´óÓÚ8000µÄÏÂÏÞ£¡'); 15. END IF; 16. END;
Ö´Ðнá¹ûΪ£º Sql´úÂë
1. DECLARE 2. *
3. ERROR λÓÚµÚ 1 ÐÐ:
4. ORA-20001: ±àºÅСÓÚ7000µÄÏÂÏÞ£¡ 5. ORA-06512: ÔÚline 9
˵Ã÷£ºÔÚ±¾ÑµÁ·ÖУ¬Ê¹ÓÃRAISE_APPLICATION_ERRORÒý·¢×Ô¶¨ÒåÒì³££¬²¢ÒÔϵͳ´íÎóµÄ·½Ê½½øÐÐÏÔʾ¡£´íÎó±àºÅΪ20001ºÍ20002¡£
×¢Ò⣺ͬÉÏÒ»¸öѵÁ·±È½Ï£¬´ËÖÖ·½·¨²»ÐèÒªÊÂÏȶ¨ÒåÒì³££¬¿ÉÖ±½ÓÒý·¢¡£
¿ÉÒԲο¼ÏÂÃæµÄ³ÌÐòƬ¶Ï½«³ö´íÐÅÏ¢¼Ç¼µ½±íÖУ¬ÆäÖУ¬errorsΪ¼Ç¼´íÎóÐÅÏ¢µÄ±í£¬SQLCODEΪ·¢ÉúÒì³£µÄ´íÎó±àºÅ£¬SQLERRMΪ·¢ÉúÒì³£µÄ´íÎóÐÅÏ¢¡£ DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255); BEGIN ...
EXCEPTION ...
WHEN OTHERS THEN
v_error_code := SQLCODE ;
v_error_message := SQLERRM ; INSERT INTO errors
VALUES(v_error_code, v_error_message); END;
¡¾Á·Ï°1¡¿Ð޸ĹÍÔ±µÄ¹¤×Ê£¬Í¨¹ýÒý·¢Òì³£¿ØÖÆÐ޸ķ¶Î§ÔÚ600¡«6000Ö®¼ä¡£ ½×¶ÎѵÁ·
¡¾ÑµÁ·1¡¿ ½«¹ÍÔ±´ÓÒ»¸ö±í¸´ÖƵ½ÁíÒ»¸ö±í¡£ ²½Öè1£º´´½¨Ò»¸ö½á¹¹Í¬EMP±íÒ»ÑùµÄбíEMP1£º
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2; ²½Öè2£ºÍ¨¹ýÖ¸¶¨¹ÍÔ±±àºÅ£¬½«¹ÍÔ±ÓÉEMP±íÒÆ¶¯µ½EMP1±í£º Sql´úÂë
1. SET SERVEROUTPUT ON 2. DECLARE
3. v_empno NUMBER(5):=7788; 4. emp_rec emp%ROWTYPE; 5. BEGIN
6. SELECT * INTO emp_rec FROM emp WHERE empno=v_empno; 7. DELETE FROM emp WHERE empno=v_empno; 8. INSERT INTO emp1 VALUES emp_rec; 9. IF SQL%FOUND THEN 10. COMMIT;
11. DBMS_OUTPUT.PUT_LINE('¹ÍÔ±¸´ÖƳɹ¦£¡'); 12. ELSE
13. ROLLBACK;
14. DBMS_OUTPUT.PUT_LINE('¹ÍÔ±¸´ÖÆÊ§°Ü£¡'); 15. END IF; 16. END;
Ö´Ðнá¹ûΪ£º ¹ÍÔ±¸´ÖƳɹ¦£¡
PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
²½Öè2£ºÏÔʾ¸´Öƽá¹û£º
SELECT empno,ename,job FROM emp1; Ö´Ðнá¹ûΪ£º Sql´úÂë
1. EMPNO ENAME JOB
2. ------------- -------------- ---------------- 3. 7788 SCOTT ANALYST
˵Ã÷£ºemp_rec±äÁ¿ÊǸù¾Ýemp±í¶¨ÒåµÄ¼Ç¼±äÁ¿£¬SELECT...INTO...Óï¾ä½«Õû¸ö¼Ç¼´«¸ø¸Ã±äÁ¿¡£INSERTÓï¾ä½«Õû¸ö¼Ç¼±äÁ¿²åÈëemp1±í£¬Èç¹û²åÈë³É¹¦(SQL%FOUNDÎªÕæ)£¬ÔòÌá½»ÊÂÎñ£¬·ñÔò»Ø¹ö³·ÏúÊÂÎñ¡£ÊÔÐ޸ĹÍÔ±±àºÅΪ7902£¬ÖØÐÂÖ´ÐÐÒÔÉϳÌÐò¡£ ¡¾ÑµÁ·2¡¿ Êä³ö¹ÍÔ±¹¤×Ê£¬¹ÍÔ±¹¤×ÊÓò»Í¬¸ß¶ÈµÄ*±íʾ¡£ ÊäÈë²¢Ö´ÐÐÒÔϳÌÐò£º Sql´úÂë
1. SET SERVEROUTPUT ON 2. BEGIN
3. FOR re IN (SELECT ename,sal FROM EMP) LOOP
4. DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));
5. END LOOP; 6. END;
Êä³ö½á¹ûΪ£º Sql´úÂë
1. SMITH ********
2. ALLEN **************** 3. WARD *************
4. JONES ****************************** 5. MARTIN *************
6. BLAKE *****************************
7. CLARK *****************************
8. SCOTT ******************************
9. KING ************************************************** 10. TURNER *************** 11. ADAMS *********** 12. JAMES **********
13. FORD ****************************** 14. MILLER ************* 15. Ö´Ðнá¹ûΪ£º
16. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£ºµÚÒ»¸örpadº¯Êý²úÉú¶ÔÆëЧ¹û£¬µÚ¶þ¸örpadº¯Êý¸ù¾Ý¹¤×ʶî²úÉú²»Í¬ÊýÄ¿µÄ*¡£¸Ã³ÌÐò²ÉÓÃÁËÒþʽµÄ¼òÂÔÓαêÑ»·ÐÎʽ¡£
¡¾ÑµÁ·3¡¿ ±àд³ÌÐò£¬¸ñʽ»¯Êä³ö²¿ÃÅÐÅÏ¢¡£ ÊäÈë²¢Ö´ÐÐÈçϳÌÐò£º Sql´úÂ룺
1. SET SERVEROUTPUT ON
2. DECLARE 3. 4. 5. 6.
v_count number:=0;
CURSOR dept_cursor IS SELECT * FROM dept; BEGIN
DBMS_OUTPUT.PUT_LINE('²¿ÃÅÁбí');
7. DBMS_OUTPUT.PUT_LINE('---------------------------------'); 8. FOR Dept_record IN dept_cursor LOOP
9. DBMS_OUTPUT.PUT_LINE('²¿ÃűàºÅ£º'|| Dept_record.deptno); 10. DBMS_OUTPUT.PUT_LINE('²¿ÃÅÃû³Æ£º'|| Dept_record.dname); 11. DBMS_OUTPUT.PUT_LINE('ËùÔÚ³ÇÊУº'|| Dept_record.loc); 12. DBMS_OUTPUT.PUT_LINE('---------------------------------'); 13. v_count:= v_count+1;
14. END LOOP;
15. DBMS_OUTPUT.PUT_LINE('¹²ÓÐ'||to_char(v_count)||'¸ö²¿ÃÅ£¡'); 16. END;
Êä³ö½á¹ûΪ£º Sql´úÂë
1. ²¿ÃÅÁбí
2. ------------------------------------ 3. ²¿ÃűàºÅ£º10
4. ²¿ÃÅÃû³Æ£ºACCOUNTING 5. ËùÔÚ³ÇÊУºNEW YORK 6. 7. 8. 9.
------------------------------------ ²¿ÃűàºÅ£º20
²¿ÃÅÃû³Æ£ºRESEARCH ËùÔÚ³ÇÊУºDALLAS
10. ...
11. ¹²ÓÐ4¸ö²¿ÃÅ£¡
12. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£º¸Ã³ÌÐòÖн«×Ö¶ÎÄÚÈÝ´¹Ö±ÅÅÁС£V_count±äÁ¿¼Ç¼ѻ·´ÎÊý£¬¼´²¿ÃŸöÊý¡£ ¡¾ÑµÁ·4¡¿ ÒÑ֪ÿ¸ö²¿ÃÅÓÐÒ»¸ö¾Àí£¬±àд³ÌÐò£¬Í³¼ÆÊä³ö²¿ÃÅÃû³Æ¡¢²¿ÃÅ×ÜÈËÊý¡¢×ܹ¤×ʺͲ¿ÃžÀí¡£
ÊäÈë²¢Ö´ÐÐÈçϳÌÐò£º Sql´úÂë
1. SET SERVEROUTPUT ON 2. DECLARE
3. v_deptno number(8); 4. v_count number(3); 5. v_sumsal number(6);