PL/SQL基础

1.基本的PL/SQL代码块如下所示：

DECLARE
/* Declarative section - PL/SQL variables, types, cursors,
and local subprograms go here. */
BEGIN
/* Executable section - procedural and SQL statements go here.
This is the main section of the block and the only one
that is required. */
EXCEPTION
/* Exception-handling section - error-handling statements go
here. */
END;

2.循环结构

DECLARE
  v_LoopCounter BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);
    v_LoopCounter := v_LoopCounter + 1;
    EXIT WHEN v_LoopCounter > 50;
  END LOOP;
END;

3.存储过程
示例

CREATE OR REPLACE PROCEDURE PrintStudents(
  p_Major IN students.major%TYPE) AS
  CURSOR c_Students IS 
    SELECT first_name, last_name
    FROM students
    WHERE major = p_Major;
BEGIN
  FOR v_StudentRec IN c_Students LOOP
    DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || ' ' || v_StudentRec.last_name);
  END LOOP;
END;

调用

BEGIN
  PrintStudents('Computer Science');
END;

4.程序包

CREATE OR REPLACE PACKAGE RoomsPkg AS
  PROCEDURE NewRoom(p_Building rooms.building%TYPE,
  p_RoomNum rooms.room_number%TYPE,
  p_NumSeats rooms.number_seats%TYPE,
  p_Description rooms.description%TYPE);
  PROCEDURE DeleteRoom(p_RoomID IN rooms.room_id%TYPE);
END RoomsPkg;

CREATE OR REPLACE PACKAGE BODY RoomsPkg AS
  PROCEDURE NewRoom(p_Building rooms.building%TYPE,
  p_RoomNum rooms.room_number%TYPE,
  p_NumSeats rooms.number_seats%TYPE,
  p_Description rooms.description%TYPE) IS
  BEGIN
    INSERT INTO rooms
(room_id, building, room_number, number_seats, description)
    VALUES (room_sequence.NEXTVAL, p_Building, p_RoomNum, p_NumSeats,
p_Description);
  END NewRoom;

  PROCEDURE DeleteRoom(p_RoomID IN rooms.room_id%TYPE) IS
  BEGIN
    DELETE FROM rooms WHERE room_id = p_RoomID;
  END DeleteRoom;
END RoomsPkg;

5.执行动态SQL
（需要Oracle8i或更高的版本）

CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
  v_SQLString VARCHAR2(100);
BEGIN
  -- Build the string based on the input parameter.
  v_SQLString := 'DROP TABLE ' || p_Table;
  EXECUTE IMMEDIATE v_SQLString;
END DropTable;