OracleÓαêʹÓ÷½·¨¼°Óï·¨´óÈ«

·Ö»ØÍ˲åÈë²Ù×÷£¬È»ºóÏÔʾÏàÓ¦µÄ´íÎóÐÅÏ¢¡£

¡¾ÑµÁ·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);

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