Oracle基础学习笔记(五) (PL/SQL)

IF condition THEN

ELSIF condition THEN

ELSE

END IF;

 

LOOP

    ...

   CONTINUE;

   CONTINUE WHEN ...

   EXIT;

   EXIT WHEN ...

END LOOP

 

WHILE condition LOOP

END LOOP;

 

FOR var IN [REVERSE] 1..5 LOOP

END LOOP;

 

CURSOR cursor_name IS select_statement;

OPEN cursor_name; (the query isn't executed until you open the cursor)

LOOP

FETCH cursor_name INTO variable [, variable, ...];

EXIT WHEN cursor_name%NOTFOUND;

END LOOP;

CLOSE cursor_name;

 

FOR var IN cursor_name LOOP

END LOOP;

 

Constrainted Cursors

TYPE t_product_cursor IS REF CURSOR RETURN products%ROWTYPE;

v_product_cursor t_product_cursor;

OPEN v_product_cursor FOR

SELECT * FROM products WHERE id<5;

CLOSE v_product_cursor;

 

Unconstrainted Cursors

TYPE t_cursor IS REF CURSOR;

v_cursor t_cursor;

OPEN v_cursor FOR

SELECT * FROM products WHERE id<5;

 

OPEN v_cursor FOR

SELECT * FROM customers WHERE id<5;

CLOSE v_cursor;

 

 

CALL proc_name (var1,  var2);

CALL proc_name(var1_name=>var1, var2_name=>var2); (new feature in 11g)

SHOW ERRORS;

 

Triggers

A trigger may fire before of after a DML statement runs.

it can be a row-level trigger or for all the rows(statement-level trigger).

 

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF | FOR} trigger_event

ON table_name

[FOR EACH ROW]

[{FORWARD | REVERSE} CROSSEDITION]

[{FOLLOWS | PRECEDES} chema.other_trigger]

[{ENABLE | DISABLE}]

[WHEN trigger_condition]

BEGIN

  trigger_body

END;

 

ALTER TRIGGER trigger_name DISABLE;

ALTER TRIGGER trigger_name ENABLE;