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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值