Oracle数据库之开发PL/SQL子程序和包

PL/SQL块分为匿名块与命名块，命名块又包含子程序、包和触发器。

过程和函数统称为PL/SQL子程序，我们可以将商业逻辑、企业规则写成过程或函数保存到数据库中，以便共享。

过程和函数均存储在数据库中，并通过参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程不返回数据。

1. 存储过程概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中。经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

2. 创建过程

语法：

1 CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
2    [ ( parameter_declaration [, parameter_declaration ]... ) ]
3    [ invoker_rights_clause ]
4    { IS | AS }
5    { [ declare_section ] body | call_spec | EXTERNAL} ;

 

说明：

procedure_name：过程名称。

parameter_declaration：参数声明，格式如下：

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
          | { OUT | IN OUT } [ NOCOPY ] datatype

IN：输入参数。

OUT：输出参数。

IN OUT：输入输出参数。

invoker_rights_clause：这个过程使用谁的权限运行，格式：

AUTHID { CURRENT_USER | DEFINER }

declare_section：声明部分。

body：过程块主体，执行部分。

一般只有在确认procedure_name过程是新过程或是要更新的过程时，才使用OR REPALCE关键字，否则容易删除有用的过程。

示例1：

1 CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
2    tot_emps NUMBER;
3    BEGIN
4       DELETE FROM employees
5       WHERE employees.employee_id = remove_emp.employee_id;
6       tot_emps := tot_emps - 1;
7    END;

 示例2：

 1 CREATE OR REPLACE PROCEDURE insert_emp(
 2    v_empno     in employees.employee_id%TYPE,
 3    v_firstname in employees.first_name%TYPE,
 4    v_lastname  in employees.last_name%TYPE,
 5    v_deptno    in employees.department_id%TYPE
 6    )
 7 AS
 8    empno_remaining EXCEPTION;
 9    PRAGMA EXCEPTION_INIT(empno_remaining, -1);
10 BEGIN
11    INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
12    VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);
13    DBMS_OUTPUT.PUT_LINE('插入成功！');
14 EXCEPTION
15    WHEN empno_remaining THEN
16       DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
17       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
18    WHEN OTHERS THEN
19       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
20 END;

 

 

3. 使用过程参数

当建立过程时，既可以指定过程参数，也可以不提供任何参数。

过程参数包括输入参数、输出参数和输入输出参数，其中输入参数（IN）用于接收调用环境的输入数据，输出参数（OUT）用于将输出数据传递到调用环境，而输入输出参数（IN OUT）不仅要接收输入数据，而且还要输出数据到调用环境。

3.1 带有输入参数的过程

通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默认参数模式是输入参数，另外可以使用IN关键字显式定义输入参数。

示例：

 1 CREATE OR REPLACE PROCEDURE insert_emp(
 2     empno employee.empno%TYPE,
 3     ename employee.ename%TYPE,
 4     job employee.job%TYPE,
 5     sal employee.sal%TYPE,
 6     comm IN employee.comm%TYPE,
 7     deptno IN employee.deptno%TYPE
 8     )
 9 IS
10 BEGIN
11     INSERT INTO employee VALUES(empno, ename, job, sal, comm, depno);
12 END;

 

 

3.2 带有输出参数的过程

通过在过程中使用输出参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要指定参数模式OUT。

示例：

1 CREATE OR REPLACE PROCEDURE update_sal(
2     eno NUMBER,
3     salary NUMBER,
4     name out VARCHAR2)
5 IS
6 BEGIN
7     UPDATE employee SET sal=salary WHERE empno=eno
8     RETURNING ename INTO name;
9 END;

3.3 带有输入输出参数的过程

通过在过程中使用输入输出参数，可以在调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为IN OUT。

示例：

 1 CREATE OR REPLACE PROCEDURE divide(
 2     num1 IN OUT NUMBER,
 3     num2 IN OUT NUMBER)
 4 IS
 5     v1 NUMBER;
 6     v2 NUMBER;
 7 BEGIN
 8     v1 := trunc(num1 / num2);
 9     v2 := mod(num1,num2);
10     num1 := v1;
11     num2 := v2;
12 END;

 

 

4. 调用过程

当在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令，而在PL/SQL块中过程可以直接引用。

ORACLE使用EXECUTE语句来调用存储过程语法：

 1 EXEC[UTE] procedure_name(parameter1, parameter2, …); 

示例：

1 -- 调用删除员工的过程
2 EXEC remove_emp(1);
3
4 -- 调用插入员工的过程
5 EXECUTE insert_emp(1, 'tommy', 'lin', 2);

 

 示例：

1 DECLARE
2     v_name employee.ename%type;
3 BEGIN
4     update_sal(&eno,&salary,v_name);
5     dbms_output.put_line('姓名:'||v_name);
6 END;

 

 

5. 函数介绍

函数是一段独立的PL/SQL程序代码，它执行某个特定的、明确的任务。通常，函数将处理从程序的调用部分传递给它的信息，然后返回单个值。信息通过称为参数的特殊标识符传递给函数，然后通过RETURN语句返回。

6. 创建函数

语法：

 1 CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
 2   [ ( parameter_declaration [, parameter_declaration]... )
 3   ]
 4   RETURN datatype
 5   [ { invoker_rights_clause
 6     | DETERMINISTIC
 7     | parallel_enable_clause
 8     | RESULT_CACHE  [ relies_on_clause ]
 9     }...
10   ]
11   { { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
12   | [ PIPELINED ] { IS | AS } { [ declare_section ] body
13                               | call_spec
14                               | EXTERNAL
15                               }
16   } ;

 

示例：

1 CREATE FUNCTION get_bal(acc_no IN NUMBER)
2    RETURN NUMBER
3    IS
4      acc_bal NUMBER(11,2);
5    BEGIN
6       SELECT order_total INTO acc_bal FROM orders
7       WHERE customer_id = acc_no;
8       RETURN(acc_bal);
9     END;

 

 

函数参数也有输入、输出、输入输出三种模式：IN、OUT、IN OUT是形参的模式。若省略，则为IN模式。

和过程一样，IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。

OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。

IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。

调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。

示例：

 1 CREATE OR REPLACE FUNCTION get_salary(
 2   dept_no IN NUMBER DEFAULT 1,
 3   emp_count OUT NUMBER)
 4   RETURN NUMBER
 5 IS
 6   V_sum NUMBER;
 7 BEGIN
 8   SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES
 9   WHERE DEPARTMENT_ID=dept_no;
10   RETURN v_sum;
11 EXCEPTION
12    WHEN NO_DATA_FOUND THEN
13       DBMS_OUTPUT.PUT_LINE('数据不存在');
14    WHEN OTHERS THEN
15       DBMS_OUTPUT.PUT_LINE('其它异常：');
16       DBMS_OUTPUT.PUT_LINE('错误号：' || SQLCODE||'，错误消息：'||SQLERRM);
17 END get_salary;

 

 

7 函数调用

语法：

1 function_name([[parameter_name1 =>] value1[, [parameter_name2 =>] value2, ...]]);

 

示例1：

1 DECLARE
2   v_num NUMBER;
3   v_sum NUMBER;
4 BEGIN
5   v_sum := get_salary(27, v_num);
6   DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
7 END;

 

示例二：

1 DECLARE
2   v_num NUMBER;
3   v_sum NUMBER;
4 BEGIN
5   v_sum := get_salary(dept_no => 27, emp_count => v_num);
6   DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
7 END;

 

示例3：

1 DECLARE
2   v_num NUMBER;
3   v_sum NUMBER;
4 BEGIN
5   v_sum := get_salary(emp_count => v_num);
6   DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
7 END;

 

 

8. 删除过程或函数

删除过程语法：

DROP PROCEDURE [schema.]procudure_name;

删除函数语法：

DROP FUNCTION [schema.]function_name;

9. 过程与函数比较

过程 函数
作为PL/SQL语句执行 作为表达式的一部分执行
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以RETURN语句，但是与函数不同，它不能用于返回值 必须包含至少一条RETURN语句

过程与函数的相同功能有：

  1. 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
  2. 输入参数都可以接受默认值，都可以传值或传引导。
  3. 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
  4. 都有声明部分、执行部分和异常处理部分。
  5. 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

-- 包

1. 简介

包(PACKAGE)是一种数据对象，它是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识。

包类似于JAVA或C#语言中的类，包中的变量相当于类中的成员变量，过程和函数相当于类方法。

通过使用包，可以简化应用程序设计，提高应用性能，实现信息隐藏、子程序重载等面向对象语言所具有的功能。

与高级语言中的类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。

一般是先编写独立的过程与函数，待其较为完善或经过充分验证无误后，再按逻辑相关性组织为程序包。

2. 包的优点

  1. 模块化：使用包，可以封装相关的类型、对象和子程序。把一个大的功能模块划分成多个小的功能模块，分别完成各自的功能，这样组织的程序易于编写，理解和管理。
  2. 更轻松的应用程序设计：包规范部分和包体部分可以分别创建并编译。换言之，我们可以在没有编写包体的情况下编写包规范的代码并进行编译。
  3. 信息隐藏：包中的元素可以分为公有元素和私有元素，公有元素可被程序包内的过程、函数等访问，还可以被包外的PL/SQL访问。但对于私有元素只能被包内的过程、函数等访问。对于用户，只需知道包规范，不用了解包体的具体细节。
  4. 性能更佳：应用程序第一次调用程序包中的某个元素时，就将整个程序包加载到内存中，当第二次访问程序包中的元素时，ORACLE将直接从内在中读取，而不需要进行磁盘I/O操作而影响速度，同时位于内存中的程序包可被同一会话期间的其它应用程序共享。因此，程序包增加了重用性并改善了多用户、多应用程序环境的效率。

3. 包的定义

PL/SQL中的包由包规范和包体两部分组成。建立包时，首先要建立包规范，然后再建立对包规范的实现–包体。

包规范用于声明包的公用组件，如变量、常量、自定义数据类型、异常、过程、函数、游标等。包规范中定义的公有组件不仅可以在包内使用，还可以由包外其他过程、函数使用。但需要说明与注意的是，为了实现信息的隐藏，建议不要将所有组件都放在包规范处声明，只应把公共组件放在包规范部分。

包体是包的具体实现细节，它实现在包规范中声明的所有公有过程、函数、游标等。也可以在包体中声明仅属于自己的私有过程、函数、游标等。

3.1 建立包规范

语法：

1 CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
2  PACKAGE [ schema. ] package_name
3   [ invoker_rights_clause ]
4   { IS | AS } item_list_1 END [ package_name ] ;

item_list_1：声明包的公用组件列表

 1 { type_definition -- 数据类型
 2 | cursor_declaration -- 游标
 3 | item_declaration -- 变量、常量等
 4 | function_declaration -- 函数
 5 | procedure_declaration -- 过程
 6 }
 7   [ { type_definition
 8     | cursor_declaration
 9     | item_declaration
10     | function_declaration
11     | procedure_declaration
12     | pragma
13     }
14   ]...

 

示例：
 1 CREATE OR REPLACE PACKAGE emp_mgmt AS
 2    -- 函数
 3    FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
 4       manager_id NUMBER, salary NUMBER,
 5       commission_pct NUMBER, department_id NUMBER)
 6       RETURN NUMBER;
 7    FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
 8       RETURN NUMBER;
 9    -- 过程
10    PROCEDURE remove_emp(employee_id NUMBER);
11    PROCEDURE remove_dept(department_id NUMBER);
12    PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
13    PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
14    -- 异常
15    no_comm EXCEPTION;
16    no_sal EXCEPTION;
17 END emp_mgmt;

 

3.2 建立包体语法：
1 CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ] package_name
2 { IS | AS }
3     BEGIN statement [ statement | pragma ]...
4       [ EXCEPTION exception_handler [ exception_handler ]... ]
5  [ initialize_section ]
6 END [ package_name ] ;

 

示例：
 1 CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
 2    tot_emps NUMBER;
 3    tot_depts NUMBER;
 4 FUNCTION hire
 5    (last_name VARCHAR2, job_id VARCHAR2,
 6     manager_id NUMBER, salary NUMBER,
 7     commission_pct NUMBER, department_id NUMBER)
 8    RETURN NUMBER IS new_empno NUMBER;
 9 BEGIN
10    SELECT employees_seq.NEXTVAL
11       INTO new_empno
12       FROM DUAL;
13    INSERT INTO employees
14       VALUES (new_empno, 'First', 'Last','first.example@example.com',
15               '(415)555-0100','18-JUN-02','IT_PROG',90000000,00,
16               100,110);
17       tot_emps := tot_emps + 1;
18    RETURN(new_empno);
19 END;
20 FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
21    RETURN NUMBER IS
22       new_deptno NUMBER;
23    BEGIN
24       SELECT departments_seq.NEXTVAL
25          INTO new_deptno
26          FROM dual;
27       INSERT INTO departments
28          VALUES (new_deptno, 'department name', 100, 1700);
29       tot_depts := tot_depts + 1;
30       RETURN(new_deptno);
31    END;
32 PROCEDURE remove_emp (employee_id NUMBER) IS
33    BEGIN
34       DELETE FROM employees
35       WHERE employees.employee_id = remove_emp.employee_id;
36       tot_emps := tot_emps - 1;
37    END;
38 PROCEDURE remove_dept(department_id NUMBER) IS
39    BEGIN
40       DELETE FROM departments
41       WHERE departments.department_id = remove_dept.department_id;
42       tot_depts := tot_depts - 1;
43       SELECT COUNT(*) INTO tot_emps FROM employees;
44    END;
45 PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
46    curr_sal NUMBER;
47    BEGIN
48       SELECT salary INTO curr_sal FROM employees
49       WHERE employees.employee_id = increase_sal.employee_id;
50       IF curr_sal IS NULL
51          THEN RAISE no_sal;
52       ELSE
53          UPDATE employees
54          SET salary = salary + salary_incr
55          WHERE employee_id = employee_id;
56       END IF;
57    END;
58 PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
59    curr_comm NUMBER;
60    BEGIN
61       SELECT commission_pct
62       INTO curr_comm
63       FROM employees
64       WHERE employees.employee_id = increase_comm.employee_id;
65       IF curr_comm IS NULL
66          THEN RAISE no_comm;
67       ELSE
68          UPDATE employees
69          SET commission_pct = commission_pct + comm_incr;
70       END IF;
71    END;
72 END emp_mgmt;

4. 调用包的组件

包的名称是唯一的，但对于两个包中的公有组件的名称可以相同，用“包名.公有组件名“加以区分。

示例：

 1 DECLARE
 2     new_dno NUMBER; -- 部门编号
 3 BEGIN
 4     -- 调用emp_mgmt包的create_dept函数创建部门：
 5     new_dno := emp_mgmt.create_dept(85, 100);
 6     DBMS_OUTPUT.PUT_LINE('部门编号：' || new_dno);
 7
 8     -- 调用emp_mgmt包的increase_sal过程为员工加薪：
 9     emp_mgmt.increase_sal(23, 800);
10 END;

5. 包中的游标

在包中使用无参游标，示例：

 1 --定义包规范
 2 CREATE OR REPLACE PACKAGE PKG_STU IS
 3     CURSOR getStuInfo RETURN stuInfo%ROWTYPE;
 4 END PKG_STU;
 5
 6 --定义包体
 7 CREATE OR REPLACE PACKAGE BODY PKG_STU AS
 8     CURSOR getStuInfo RETURN stuInfo%ROWTYPE IS
 9         SELECT * FROM stuInfo;
10 END PKG_STU;
11
12 --调用包组件
13 BEGIN
14     FOR stu_Record IN PKG_STU.getStuInfo LOOP
15        DBMS_OUTPUT.PUT_LINE('学员姓名:'||stu_Record.name||'，学号:'||stu_Record.id||'，年龄:'||stu_Record.age);
16     END LOOP;
17 END;

在包中使用有参数的游标，示例：

 1 --定义包规范
 2 CREATE OR REPLACE PACKAGE PKG_STU IS
 3     CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE;
 4 END PKG_STU;
 5
 6 --定义包体
 7 CREATE OR REPLACE PACKAGE BODY PKG_STU AS
 8     CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE IS
 9         SELECT * FROM stuInfo WHERE id=studentNo;
10 END PKG_STU;
11
12 --调用包组件
13 BEGIN
14     FOR stu_Record IN PKG_STU.getStuInfo(2) LOOP
15         DBMS_OUTPUT.PUT_LINE('学员姓名:'||stu_Record.name||'，学号:'||stu_Record.id||'，年龄:'||stu_Record.age);
16     END LOOP;
17 END;

由于游标变量是一个指针，其状态是不确定的，因此它不能随同包存储在数据库中，即不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类型，并可向包中的子程序传递游标变量参数。

示例：

 1 -- 创建包规范
 2 CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
 3   TYPE dept_cur_type IS REF CURSOR RETURN dept%ROWTYPE; --强类型
 4
 5   TYPE cur_type IS REF CURSOR;-- 弱类型
 6
 7   PROCEDURE proc_open_dept_var(
 8     dept_cur IN OUT dept_cur_type,
 9     choice INTEGER DEFAULT 0,
10     dept_no NUMBER DEFAULT 50,
11     dept_name VARCHAR DEFAULT '%');
12 END;
13
14 -- 创建包体
15 CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
16 AS
17   PROCEDURE proc_open_dept_var(
18     dept_cur IN OUT dept_cur_type,
19     choice INTEGER DEFAULT 0,
20     dept_no NUMBER DEFAULT 50,
21     dept_name VARCHAR DEFAULT '%')
22   IS
23   BEGIN
24     IF choice = 1 THEN
25       OPEN dept_cur FOR SELECT * FROM dept WHERE deptno = dept_no;
26     ELSIF choice = 2 THEN
27       OPEN dept_cur FOR SELECT * FROM dept WHERE dname LIKE dept_name;
28     ELSE
29       OPEN dept_cur FOR SELECT * FROM dept;
30     END IF;
31   END proc_open_dept_var;
32 END CURROR_VARIBAL_PKG;

 

 
 1 定义一个过程，打开弱类型的游标变量：
 2
 3 --定义过程
 4 CREATE OR REPLACE PROCEDURE proc_open_cur_type(
 5   cur IN OUT CURROR_VARIBAL_PKG.cur_type,
 6   first_cap_in_table_name CHAR)
 7 AS
 8 BEGIN
 9   IF first_cap_in_table_name = 'D' THEN
10     OPEN cur FOR SELECT * FROM dept;
11   ELSE
12     OPEN cur FOR SELECT * FROM emp;
13   END IF;
14 END proc_open_cur_type;
 1 测试包中游标变量类型的使用：
 2
 3 DECLARE
 4   dept_rec Dept%ROWTYPE;
 5   emp_rec Emp%ROWTYPE;
 6   dept_cur CURROR_VARIBAL_PKG.dept_cur_type;
 7   cur CURROR_VARIBAL_PKG.cur_type;
 8 BEGIN
 9   DBMS_OUTPUT.PUT_LINE('游标变量强类型：');
10   CURROR_VARIBAL_PKG.proc_open_dept_var(dept_cur, 1, 30);
11   FETCH dept_cur INTO dept_rec;
12   WHILE dept_cur%FOUND LOOP
13     DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
14     FETCH dept_cur INTO dept_rec;
15   END LOOP;
16   CLOSE dept_cur;
17
18   DBMS_OUTPUT.PUT_LINE('游标变量弱类型：');
19   CURROR_VARIBAL_PKG.proc_open_dept_var(cur, 2, dept_name => 'A%');
20   FETCH cur INTO dept_rec;
21   WHILE cur%FOUND LOOP
22     DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
23     FETCH cur INTO dept_rec;
24   END LOOP;
25
26   DBMS_OUTPUT.PUT_LINE('游标变量弱类型—dept表：');
27   proc_open_cur_type(cur, 'D');
28   FETCH cur INTO dept_rec;
29   WHILE cur%FOUND LOOP
30     DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
31     FETCH cur INTO dept_rec;
32   END LOOP;
33
34   DBMS_OUTPUT.PUT_LINE('游标变量弱类型—emp表：');
35   proc_open_cur_type(cur, 'E');
36   FETCH cur INTO emp_rec;
37   WHILE cur%FOUND LOOP
38     DBMS_OUTPUT.PUT_LINE(emp_rec.empno||':'||emp_rec.ename);
39     FETCH cur INTO emp_rec;
40   END LOOP;
41   CLOSE cur;
42 END;

 

 

6. 子程序重载

所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。

在调用重载子程序时，主程序将根据实际参数的类型和数目，自动确定调用哪个子程序。

PL/SQL允许对包内子程序和本地子程序进行重载。

示例：

 1 -- 定义包规范
 2 CREATE OR REPLACE PACKAGE PKG_EMP AS
 3     FUNCTION get_salary(eno NUMBER) RETURN NUMBER;
 4     FUNCTION get_salary(empname VARCHAR2) RETURN NUMBER;
 5 END PKG_EMP;
 6
 7 -- 定义包体
 8 CREATE OR REPLACE PACKAGE BODY PKG_EMP AS
 9     FUNCTION get_salary(eno NUMBER) RETURN NUMBER
10       IS
11         v_salary NUMBER(10, 4);
12       BEGIN
13         SELECT sal INTO v_salary FROM emp WHERE empno=eno;
14         RETURN v_salary;
15       END;
16
17     FUNCTION get_salary(empname VARCHAR2) RETURN NUMBER
18       IS
19         v_salary NUMBER(10, 4);
20       BEGIN
21         SELECT sal INTO v_salary FROM emp WHERE ename=empname;
22         RETURN v_salary;
23       END;
24 END PKG_EMP;

 

 

 1 测试：
 2
 3 DECLARE
 4   v_sal NUMBER(10, 4);
 5 BEGIN
 6   v_sal := PKG_EMP.get_salary(7499);
 7   DBMS_OUTPUT.PUT_LINE('工资：' || v_sal);
 8   v_sal := PKG_EMP.get_salary('MARTIN');
 9   DBMS_OUTPUT.PUT_LINE('工资：' || v_sal);
10 END;

 

时间： 2024-10-26 04:47:22

Oracle数据库之开发PL/SQL子程序和包的相关文章

把Oracle数据库移植到Microsoft SQL Server 7.0

oracle|server|数据|数据库  把Oracle数据库移植到Microsoft SQL Server 7.0 摘要:本文是为那些想把自己的Oracle应用程序转换为Microsoft SQL Server应用程序的开发人员编写的.本文描述了一个成功的转换所需要的工具.过程和技术.同时强调了建立高性能.高度并行的SQL Server应用程序的基本的设计要素. 本文的读者应该具有: Oracle关系型数据管理系统(RDBMS)的坚实基础. 普通数据库管理知识. 熟悉Oracle SQL和P

Oracle数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误

  Oracle 数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误 (版权声明,本人原创或者翻译的文章如需转载,如转载用于个人学习,请注明出处;否则请与本人联系,违者必究) 如何收集指定SQL的执行计划对开发人员来说非常重要的,这里记录下基础的收集方式,以便查阅和其他人参考. 1. 链接到sqlplus,如下图 2. 执行下面两个的命令之一 set autotrace on; (说明:打开自动分析统计,并显示SQL语句的运行结果) 3. 输入并执行要搜集执行计划的SQL语句

oracle10g-Oralce通过DBCA建立数据库，用PL/SQL登陆不上，但可以登陆安装时默认的orcl实例

问题描述 Oralce通过DBCA建立数据库,用PL/SQL登陆不上,但可以登陆安装时默认的orcl实例 Oracle通过DBCA建立数据库实例,用PL/SQL登陆不上,但可以登陆安装oracle时默认的orcl实例,tnsnames.ora没有问题,服务都已经开启... 解决方案 报错没有嘛,如果报错能把错误信息粘贴出来吗 解决方案二: 看端口是否指定正确 看是否具备权限 看防火墙是否对端口进行阻止 问这类问题请附上错误提示或截图.

ORACLE数据库应用开发常见问题及排除_oracle

正在看的ORACLE教程是:ORACLE数据库应用开发常见问题及排除.硬件平台:SUN Ultra Enterprise 3000  操作系统:Solaris 2.5(中文简体)  磁盘:4.2GB  内存:256MB  ORACLE B版本:7.3.2.1  安装目录:/database/oracle(/database下有2GB空间) 安装产品:ORACLE RDBMS,SQL*NET,SQL*Plus,Pro *C,Server Manager等 产品安装正常,运行稳定. 二.应用开发中常

求oracle数据库的分页显示sql语句

问题描述 求oracle数据库的分页显示sql语句 求大神告知oracle数据库的分页显示sql语句,用rownum查不到 解决方案 http://www.cnblogs.com/xiaopang2010/archive/2012/07/23/2604880.htmlhttp://www.2cto.com/database/201304/206296.html 解决方案二: String querySql = ""; querySql += "select x.* from

oracle复习笔记之PL/SQL程序所要了解的知识点_oracle

复习内容: PL/SQL的基本语法.记录类型.流程控制.游标的使用. 异常处理机制.存储函数/存储过程.触发器. 为方便大家跟着我的笔记练习,为此提供数据库表文件给大家下载:点我下载 为了要有输出的结果,在写PL/SQL程序前都在先运行这一句:set serveroutput on 结构:declare --声明变量.类型.游标begin --程序的执行部分(类似于java里的main()方法)exception --针对begin块中出现的异常,提供处理的机制 --when...then...

Oracle数据库设计开发阶段性能优化策略

引言 Oracle是目前使用最为广泛的大型数据库管理系统,提高Oracle数据库系统的运行效率,是整个计算机信息系统高效运转的前提和保证.影响Oracle数据库应用系统性能的因素很多,既有软件方面的因素,也包括数据运行的硬件环境.网络环境.数据库管理和维护方面的因素等.数据库系统设计开发阶段是Oracle应用优化的最佳阶段,也是主动优化阶段,能达到以最小成本获得最大性能增益的目的.通过对其逻辑存储结构和物理存储结构设计进行优化,使之在满足需求条件下,时空开销性能最佳,可以解决数据库系统运行过程中

Oracle 数据库的分页查询sql语句

oracle 数据库教程的分页查询sql语句   oracle       select * from (select rownum r,t1.* from 表名称 t1 where rownum < m + n) t2 where t2.r >= m 例如从表sys_option(主键为sys_id)中从10条记录还是检索20条记录,语句如下: select * from (select rownum r,t1.* from sys_option where rownum < 30 )

php连接oracle数据库并查询数据sql

记php支持oracle连接函数 php.ini文件中的配置,去掉  ;extention = php_oci8.dll,去掉前面的分号 重启apache就可以了, 如果不行,我们再把php目录中的php_oci8.dll拷到windows系统的system32下面去吧.oracle数据库建立链接  代码如下 复制代码 1.$conn = oci_connect('username','password',"(DEscriptION=(ADDRESS=(PROTOCOL =TCP)(HOST=1