PL/SQL结构与实例
PL/SQL程序由三个块组成:声明部分、执行部分、异常处理部分。PL/SQL有下列程序单元:过程,函数,包说明,包体 。存储过程是作为对象存在于oracle数据库的程序单元。oracle实现存储过程作为过程、函数和包。 PL/SQL不能调用包,可以调用包中可见部分的过程和函数。包是封装同子程序一样持久数据的程序单元。
参数模式有:IN 、OUT、IN OUT。
PL/SQL的结构如下:
DECLARATIVE
----声明部分:在此声明PL/SQL中使用到的变量、类型、及游标,以及局部变量中的存储过程和函数
BEGIN
----执行部分:过程及SQL语句,及程序的主要部分
EXCPTION
----执行异常部分:错误处理
END;
其中,执行部分不能省略;
Eg1 过程:
CREATE OR REPLACE PROCEDURE print_temp
IS
v_average NUMBER;
v_sum NUMBER;
BEGIN
SELECT AVG(n), SUM(n) INTO v_average, v_sum
FROM TEMP;
dbms_output.put_line('Average:'||v_average);
dbms_output.put_line('Sum:'||v_sum);
END print_temp;
IS
v_average NUMBER;
v_sum NUMBER;
BEGIN
SELECT AVG(n), SUM(n) INTO v_average, v_sum
FROM TEMP;
dbms_output.put_line('Average:'||v_average);
dbms_output.put_line('Sum:'||v_sum);
END print_temp;
sqlplus调用过程:
SQL>execute insert_temp;
SQL>execute insert_temp();
都是正确的。
SQL>execute insert_temp;
SQL>execute insert_temp();
都是正确的。
Eg2 函数:
CREATE OR REPLACE FUNCTION tomorrow RETURN DATE
IS
next_day DATE;
BEGIN
next_day := SYSDATE + 1;
RETURN next_day; //函数都有返回值,没有function必须有return
END tomorrow;
IS
next_day DATE;
BEGIN
next_day := SYSDATE + 1;
RETURN next_day; //函数都有返回值,没有function必须有return
END tomorrow;
Eg3 包
CREATE OR REPLACE PACKAGE students_pkg IS
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL);
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER;
END students_pkg;
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL);
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER;
END students_pkg;
Eg4 包主体(上面包的实例)
CREATE OR REPLACE PACKAGE BODY students_pkg IS
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL)
IS
BEGIN
INSERT INTO students VALUES
('A'||students_pk_seq.NEXTVAL,
v_student_name,
v_college_major,
v_status,
v_state,
v_license_no);
END add_student;
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER
IS
ccount INTEGER;
BEGIN
SELECT COUNT (*) INTO ccount
FROM students, major_lookup
WHERE students.college_major = major_lookup.major
AND major_lookup.major_desc =
nvl(v_major,major_lookup.major_desc)
AND students.status = nvl(v_status,students.status);
RETURN ccount;
END NO_OF_STUDENTS;
END students_pkg;
使用下面方式调用:
temp_operations.insert_temp;
temp_operations.insert_temp();
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL)
IS
BEGIN
INSERT INTO students VALUES
('A'||students_pk_seq.NEXTVAL,
v_student_name,
v_college_major,
v_status,
v_state,
v_license_no);
END add_student;
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER
IS
ccount INTEGER;
BEGIN
SELECT COUNT (*) INTO ccount
FROM students, major_lookup
WHERE students.college_major = major_lookup.major
AND major_lookup.major_desc =
nvl(v_major,major_lookup.major_desc)
AND students.status = nvl(v_status,students.status);
RETURN ccount;
END NO_OF_STUDENTS;
END students_pkg;
使用下面方式调用:
temp_operations.insert_temp;
temp_operations.insert_temp();
附:PL/SQL模式
PL/SQL程序有3种模式:IN (default),IN OUT,OUT
(1)、IN 模式参数是一个常量
IN模式参数是一个常量必须被看作常量。下面的过程将不能编译成功以为第3行是一个IN模式变量
PROCEDURE print_next_value(v_data IN INTEGER) IS
BEGIN
v_data := v_data+1; -- compile error
dbms_output.put_line(v_data);
END;
常量可以用在表达式中,下面的用法是正确的:
PROCEDURE print_next_value(v_data IN INTEGER) IS
BEGIN
dbms_output.put_line(v_data+1);
END;
(2)、IN OUT模式
IN OUT模式的变量既可以在赋值语句的左边,也可以在赋值语句的右边。
PROCEDURE change_data(v_data IN OUT INTEGER) IS
BEGIN
for i in 1..10 loop
v_data := v_data + 1;
end loop;
END;
(3)、OUT模式
在下例中,第4行之前,v_data变量是一个null,在使用OUT模式变量前必须先给他赋一个值:
PROCEDURE provide_data(v_data OUT INTEGER)
IS
BEGIN
v_data := 100;
FOR i IN 1..10 LOOP
v_data := v_data +1;
END LOOP;
END;
(4)、参数默认值
过程或函数说明可以为IN或IN OUT参数定义一个初始默认值。下面两种方法都是正确的
PROCEDURE name
(argument mode datatype := a_default_value);
PROCEDURE name
(argument mode datatype DEFAULT a_default_value);
下面函数定义了以默认半径为1返回圆的面积:
FUNCTION circle
(radius IN NUMBER := 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius**2;
END;
FUNCTION circle
(radius IN NUMBER DEFAULT 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius**2;
END;
(5)、%TYPE
%TYPE 的意思是变量声明类型和数据库表的指定字段类型一致。
variable_name table_name.column_name%TYPE;
(1)、IN 模式参数是一个常量
IN模式参数是一个常量必须被看作常量。下面的过程将不能编译成功以为第3行是一个IN模式变量
PROCEDURE print_next_value(v_data IN INTEGER) IS
BEGIN
v_data := v_data+1; -- compile error
dbms_output.put_line(v_data);
END;
常量可以用在表达式中,下面的用法是正确的:
PROCEDURE print_next_value(v_data IN INTEGER) IS
BEGIN
dbms_output.put_line(v_data+1);
END;
(2)、IN OUT模式
IN OUT模式的变量既可以在赋值语句的左边,也可以在赋值语句的右边。
PROCEDURE change_data(v_data IN OUT INTEGER) IS
BEGIN
for i in 1..10 loop
v_data := v_data + 1;
end loop;
END;
(3)、OUT模式
在下例中,第4行之前,v_data变量是一个null,在使用OUT模式变量前必须先给他赋一个值:
PROCEDURE provide_data(v_data OUT INTEGER)
IS
BEGIN
v_data := 100;
FOR i IN 1..10 LOOP
v_data := v_data +1;
END LOOP;
END;
(4)、参数默认值
过程或函数说明可以为IN或IN OUT参数定义一个初始默认值。下面两种方法都是正确的
PROCEDURE name
(argument mode datatype := a_default_value);
PROCEDURE name
(argument mode datatype DEFAULT a_default_value);
下面函数定义了以默认半径为1返回圆的面积:
FUNCTION circle
(radius IN NUMBER := 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius**2;
END;
FUNCTION circle
(radius IN NUMBER DEFAULT 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius**2;
END;
(5)、%TYPE
%TYPE 的意思是变量声明类型和数据库表的指定字段类型一致。
variable_name table_name.column_name%TYPE;