Procedures
|
| < Day Day Up > |
|
There are five procedures in the ORA_EMP database:
CREATE OR REPLACE PROCEDURE AddNewEmployee ( p_FirstName employees.first_name%TYPE, p_LastName employees.last_name%TYPE, p_Department employees.department%TYPE) AS BEGIN -- Insert a new row in the employees table. Use -- employee_sequence to generate the new employee ID, and -- 0 for current_projects. INSERT INTO employees (emp_id, first_name, last_name, department, current_projects) VALUES (employee_sequence.nextval, p_FirstName, p_LastName, p_Department, 0); COMMIT; END AddNewEmployee; CREATE OR REPLACE PROCEDURE Assign ( /* Promotes the employee identified by the p_EmployeeID parameter in the account identified by the p_Dept_code and p_AcctId parameters. Before calling AccountPackage.AddEmployee, which actually adds the employee to the account, this procedure verifies that there is room in the account, and that the account exists. */ p_EmployeeID IN employees.emp_id%TYPE, p_Dept_code IN accounts.dept_code%TYPE, p_AcctId IN accounts.acct_id%TYPE) AS v_CurrentEmployees NUMBER; -- Current number of empoloyees in the account v_MaxEmployees NUMBER; -- Maximum number of employees in the account BEGIN /* Determine the current number of employees registered, and the maximum number of employees allowed to be hired for this dept. */ SELECT current_employees, max_employees INTO v_CurrentEmployees, v_MaxEmployees FROM accounts WHERE acct_id = p_AcctId AND dept_code = p_Dept_code; /* Make sure there is enough room for this additional employees. */ IF v_CurrentEmployees + 1 > v_MaxEmployees THEN RAISE_APPLICATION_ERROR(-20000, 'Can''t assign more employees to ' || p_Dept_code || ' ' || p_AcctId); END IF; /* Add the employee to the account. */ AccountPackage.AddEmployee(p_EmployeeID, p_Dept_code, p_AcctId); EXCEPTION WHEN NO_DATA_FOUND THEN /* Account information passed to this procedure doesn't exist. Raise an error to let the calling program know of this. */ RAISE_APPLICATION_ERROR(-20001, p_Dept_code || ' ' || p_AcctId || ' doesn''t exist!'); END Assign; CREATE OR REPLACE PROCEDURE EMPLOYEEDYNAMICQUERY ( /* Uses DBMS_SQL to query the employees table, and puts the * results in temp_table. The first names, last names, and * majors are inserted for up to two majors inputted. */ p_department1 IN employees.department%TYPE DEFAULT NULL, p_department2 IN employees.department%TYPE DEFAULT NULL) AS v_CursorID INTEGER; v_SelectStmt VARCHAR2(500); v_FirstName employees.first_name%TYPE; v_LastName employees.last_name%TYPE; v_Department employees.department%TYPE; v_Dummy INTEGER; BEGIN -- Open the cursor for processing. v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Create the query string. v_SelectStmt := 'SELECT first_name, last_name, department FROM employees WHERE department IN (:d1, :d2) ORDER BY v_Department, last_name'; -- Parse the query. DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE); -- Bind the input variables. DBMS_SQL.BIND_VARIABLE(v_CursorID, ':d1', p_department1); DBMS_SQL.BIND_VARIABLE(v_CursorID, ':d2', p_department2); -- Define the select list items. DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_FirstName, 20); DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20); DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Department, 30); -- Execute the statement. We don't care about the return -- value, but we do need to declare a variable for it. v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); -- This is the fetch loop. LOOP -- Fetch the rows into the buffer, and also check for the exit -- condition from the loop. IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN EXIT; END IF; -- Retrieve the rows from the buffer into PL/SQL variables. DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_FirstName); DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName); DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Department); -- Insert the fetched data into temp_table. INSERT INTO temp_table (char_col) VALUES (v_FirstName || ' ' || v_LastName || ' is a ' || v_Department || ' department.'); END LOOP; -- Close the cursor. DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Commit our work. COMMIT; EXCEPTION WHEN OTHERS THEN -- Close the cursor, then raise the error again. DBMS_SQL.CLOSE_CURSOR(v_CursorID); RAISE; END EmployeeDynamicQuery; / CREATE OR REPLACE PROCEDURE SELECTROW ( pEmp_ID IN EMPLOYEES.EMP_ID%TYPE, pRow OUT REFPKG.RCT1 ) IS BEGIN OPEN pRow FOR SELECT FIRST_NAME, LAST_NAME, DEPARTMENT, BAND FROM EMPLOYEES WHERE Emp_ID = Emp_ID; END; CREATE OR REPLACE PROCEDURE ShowFullAccounts AS CURSOR c_Accounts IS SELECT dept_code, acct_id FROM Accounts; BEGIN FOR v_AccountRecord IN c_Accounts LOOP -- Record all Accounts which don't have very much room left -- in temp_table. IF AccountFull(v_AccountRecord.dept_code, v_AccountRecord.acct_id) THEN INSERT INTO temp_table (char_col) VALUES (v_AccountRecord.dept_code || ' ' || v_AccountRecord.acct_id || ' is almost full!'); END IF; END LOOP; END ShowFullAccounts;
|
| < Day Day Up > |
|
