Infolinks

Friday 13 July 2012

Oracle PL/SQL

Oracle PL/SQL

Oracle Procedural SQL extensions:
PL/SQL Structure
  DECLARE - also declaring Table Variables
  BEGIN-EXCEPTION-END

Operators
Conditional Statements - IF

Cursor commands

1) Cursor DECLARE - Define structure & create a named SQL area
2) Cursor OPEN    - Interpret any bind variables and Query the database  
3) Cursor FETCH   - Load the current row into variables
4) Cursor CLOSE   - When there are no more rows to process

Looping Statements - LOOP, WHILE, FOR
Cursor FOR loops

Cursor Variables (REF cursors)

1) REF Cursor DECLARE - Define structure & create a named SQL area
2) REF Cursor OPEN    - Interpret any bind variables and Query the database  
3) REF Cursor FETCH   - Load the current row into variables
4) REF Cursor CLOSE   - When there are no more rows to process

SELECT... INTO v_myvar... ;
INSERT INTO... VALUES... ;
UPDATE... SET... =... WHERE CURRENT OF cursor_name;
DELETE FROM... WHERE CURRENT OF cursor_name;

No comments:

Post a Comment