PLSQL

 

Set serveroutput on size 10000

Declare

X varchar2(10);

Y INTEGER=123;

Z STRING(10)=’123’;

Begin

X:=’dddd’;

DBMS_OUTPUT.PUT_LINE(‘DDD’||X);

END;

/

SAVE C:/SQL.TXT

 

 

 

分支语句

IF …THEN

ELSIF…THEN

ELSE

..

END IF

 

 

//

DECLAREE

A NUMBER;

B VARCHAR2(10);

BEGIN

A:=2;

IF A=1 THEN

B:=’A’;

ELSIF A=2 THEN

B:=’B’;

ELSE

B:=’C’;

END IF;

DBMS_OUTPUT.PUT_LINE(B);

END;

 

 

CASE:用法

CASE

WHEN … THEN..

ELSE

END CASE

DECLAREE

A NUMBER;

B VARCHAR2(10);

BEGIN

A:=2;

CASE

WHEN A=1 THEN B:=’A’;

WHEN A=2 THEN B:=’B’;

WHEN A=3 THEN B:=’C’;

ELSE

B:=’O’;

END CASE;

DBMS_OUTPUT.PUT_LINE(B);

END;

 

 循环语句

 

1.       LOOP

….

END LOOP;

2 WHILE expression LOOP

 END LOOP

3

 FOR counter IN start_value.. end_value LOOP

 

..

END LOOP;

 

 

 

DECLARE

X NUMBER;

BEGIN

X:=0;

LOOP

X:=X+1;

IF X>=3 THEN

EXIT;

END IF;

DBMS_OUTPUT.PUT_LINE();

END LOOP;

DBMS_OUTPUT.PUT_LINE();

END;

/

 

DECLARE

X NUMBER;

BEGIN

X:=0;

LOOP

X:=X+1;

EXIT WHEN X>=3;

DBMS_OUTPUT.PUT_LINE();

END LOOP;

DBMS_OUTPUT.PUT_LINE();

END;

/

 

DECLARE

X NUMBER;

BEGIN

X:=0;

WHILE X<=3 LOOP

X:=X+1;

DBMS_OUTPUT.PUT_LINE();

END LOOP;

DBMS_OUTPUT.PUT_LINE();

END;

/

 

BEGIN

FOR I IN 1..5 LOOP

DBMS--------------

END LOOP;

 

END;

 

BEGIN

FOR I IN REVERSE 1..5 LOOP

DBMS--------------

END LOOP;

 

END;

 

 

 

 

 

DECLARE

TEST VARCHAR2(10);

BEGIN

SELECT NAME INTO TEST FROM TABLEA WHERE ID=’’;

DBMS_OUTPUT.PUT_LINE(TEST);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT>PUT_LINE();

END;

 

 

//自定义异常

DECLARE

TEST VARCHAR2(10);

E EXCEPTION;

BEGIN

SELECT NAME INTO TEST FROM TABLEA WHERE ID=’’;

IF TEST<>’DD’ THEN

RAISE E;

END IF;

…

EXCEPTION

WHEN E THEN

。。。

 

END;

 

 

游标的属性

%FOUND

%ISOPEN

$NOTFOUND

%ROWCOUNT

 

 

 

 

 

DECARE

CURSOR MYCUR IS

SELECT * FROM BOOKS;

MYRECORD BOOKS%ROWTYPE;

BEGIN

OPEN MYCUR;

FETCH MYCUR INTO MYRECORD;

WHILE MYCUR%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(MYRECORD.BOOS_ID);

FETCH MYCUR INTO MYRECORD;

END LOOP;

CLOSE MYCUR;

END;

 

带参数的游标

DECALRE

CURSOR CUR_PARA (ID VARCHAR2) IS

SELECT BOOKS_NAME FORM BOOKS WHERE BOOKS_ID=ID;

T_NAME BOOKS.BOOKS_NAME%TYPE;

BEGIN

OPEN CUR_PARA(‘001’);

LOOP

FETCH CUR_PARA INTO T_NAME;

EXIT WHEN CUR_PARA%NOTFOUND;

DBMS…………….

END LOOP;

CLOSE CUR_PARA;

END;