PLSQL基础(四)

练习：
创建一张员工表 创建一个包，并且实现如下的功能。 对于员工表的增删改查功能。 计算员工总数的功能。 对于处理中的各种异常情况进行处理。
Package Body
CREATE OR REPLACE PACKAGE BODY TEAM_DATA AS PROCEDURE Add_Member( idcard IN PLS_INTEGER, fullname IN TypeFullName, age IN PLS_INTEGER DEFAULT 0) IS tmpSalary NUMBER := 2000; BEGIN myTeamMember.idcard := idcard; myTeamMember.fullname := fullname; myTeamMember.age := age; myTeamMember.salary := tmpSalary; END Add_Member; FUNCTION Calc_Member_Count RETURN PLS_INTEGER IS BEGIN RETURN SE_COUNT; END Calc_Member_Count; END TEAM_DATA;
PL/SQL Error Handling - 1
在过程和方法中，会经常用到异常处理
异常处理的格式： EXCEPTION WHEN ex_name_1 THEN statements_1 WHEN ex_name_2 OR ex_name_3 THEN statements_2 WHEN OTHERS THEN statements_3 END;


myTeamMember TypeMember;
SE_COUNT CONSTANT PLS_INTEGER := 10; CURSOR myTeamCursor IS SELECT * FROM MEMBER_TABLE; PROCEDURE Add_Member( idcard IN PLS_INTEGER, fullname IN TypeFullName, age IN PLS_INTEGER DEFAULT 0); FUNCTION Calc_Member_Count RETURN PLS_INTEGER; Business_Error EXCEPTION; END TEAM_DATA;
PL/SQL Error Handling - 2
DECLARE TYPE NUMBER_COLLECTION IS TABLE OF NUMBER; max_number NUMBER := 0; COLLECT_EMPTY_ERROR EXCEPTION; PROCEDURE GET_MAX_VALUE(nums IN NUMBER_COLLECTION, maxnum IN OUT NUMBER) IS BEGIN IF nums.count = 0 THEN RAISE COLLECT_EMPTY_ERROR; ELSE FOR i IN nums.FIRST .. ST LOOP IF maxnum < nums(i) THEN maxnum := nums(i); END IF; END LOOP; END IF; END GET_MAX_VALUE; BEGIN GET_MAX_VALUE(NUMBER_COLLECTION(1,2,3), max_number); DBMS_OUTPUT.put_line('MAX VALUE IS ' || max_number); GET_MAX_VALUE(NUMBER_COLLECTION(), max_number); DBMS_OUTPUT.put_line('MAX VALUE IS ' || max_number); EXCEPTION WHEN COLLECT_EMPTY_ERROR THEN DBMS_OUTPUT.put_line('THIS COLLECTION IS EMPTY.'); WHEN OTHERS THEN DBMS_OUTPUT.put_line('UNKNOWN EXCEPTION.');on
CREATE OR REPLACE PACKAGE TEAM_DATA AS SUBTYPE TypeFullName IS VARCHAR(50); TYPE TypeMember IS RECORD( idcard PLS_INTEGER, fullname VARCHAR2(50), age PLS_INTEGER, salary PLS_INTEGER);
PL/SQL开发 基础篇（四）
本篇主要讲解如下语言点： PL/SQL Packages PL/SQL Error Handling
PL/SQL Packages
所说的“包”，其实是各种类型，变量，常 量，过程，方法，游标以及异常处理的总和 它由两部分构成，包说明和包体 包说明类似于我们常说的接口 包体则是包说明的实现