This section begins the discussion of the programming statements that can be used in an EDB-SPL program.
The simplest statement is the NULL statement. This statement is an executable statement that does nothing.
NULL;
The following is the simplest, possible valid EDB-SPL program.
BEGIN
NULL;
END;
The NULL statement can act as a placeholder where an executable statement is required such as in a branch of an IF-THEN-ELSE statement.
For example:
CREATE OR REPLACE PROCEDURE divide_it (
p_numerator IN NUMBER,
p_denominator IN NUMBER,
p_result OUT NUMBER
)
IS
BEGIN
IF p_denominator = 0 THEN
NULL;
ELSE
p_result := p_numerator / p_denominator;
END IF;
END;
The assignment statement sets a variable or a formal parameter of mode OUT or IN OUT specified on the left side of the assignment, :=, to the evaluated expression specified on the right side of the assignment.
variable := expression;
variable is an identifier for a previously declared variable, OUT formal parameter, or IN OUT formal parameter. expression is an expression that produces a single value. The value produced by the expression must have a compatible data type with that of variable. See Chapter 9 for a discussion of data type compatibility and conversion.
While the dept_salary_rpt example in Section 13.3 showed assignment statements used in variable declarations, a variation of this example shows the typical use of assignment statements in the executable section of the procedure.
CREATE OR REPLACE PROCEDURE dept_salary_rpt (
p_deptno NUMBER
)
IS
todays_date DATE;
rpt_title VARCHAR2(40);
base_sal INTEGER;
base_comm_rate NUMBER;
base_annual NUMBER;
BEGIN
todays_date := SYSDATE;
rpt_title := 'Report For Department # ' || p_deptno || ' on ' || todays_date;
base_sal := 35525;
base_comm_rate := 1.33333;
base_annual := ROUND(base_sal * base_comm_rate, 2);
DBMS_OUTPUT.PUT_LINE(rpt_title);
DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
The SELECT INTO statement is an EDB-SPL variation of the SQL SELECT command, the differences being:
That SELECT INTO is designed to assign the results to variables or records where they can then be used in EDB-SPL program statements.
The accessible result set of SELECT INTO is at most one row.
Other than the above, all of the clauses of the SELECT command such as WHERE, ORDER BY, GROUP BY, HAVING, etc. are valid for SELECT INTO. The following are the two variations of SELECT INTO.
SELECT select_expressions INTO target FROM ...;
target is a comma-separated list of simple variables. select_expressions and the remainder of the statement are the same as for the SQL SELECT command. See Chapter 6 for a discussion of the SELECT command. The selected values must exactly match in data type, number, and order the structure of the target or a run-time error occurs.
SELECT * INTO record FROM table ...;
record is a record that has previously been declared using table%ROWTYPE where table is the table referenced in the FROM table clause.
If the query returns zero rows, null values are assigned to the target(s). If the query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. (Note that "the first row" is not well-defined unless you've used ORDER BY.)
Note: In both cases, where no row is returned or more than one row is returned, EDB-SPL throws an exception.
You can use the WHEN NO_DATA_FOUND clause in an EXCEPTION block to determine whether the assignment was successful (that is, at least one row was returned by the query).
This version of the emp_sal_query procedure uses the variation of SELECT INTO that returns the result set into a record. Also note the addition of the EXCEPTION block containing the WHEN NO_DATA_FOUND conditional expression.
CREATE OR REPLACE PROCEDURE emp_sal_query (
p_empno IN emp.empno%TYPE
)
IS
r_emp emp%ROWTYPE;
v_avgsal emp.sal%TYPE;
BEGIN
SELECT * INTO r_emp
FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);
SELECT AVG(sal) INTO v_avgsal
FROM emp WHERE deptno = r_emp.deptno;
IF r_emp.sal > v_avgsal THEN
DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
|| v_avgsal);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
|| v_avgsal);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;
If the query is executed with a non-existent employee number the results appear as follows.
EXEC emp_sal_query(0); Employee # 0 not found
Another conditional clause of use in the EXCEPTION section with SELECT INTO is the TOO_MANY_ROWS exception. If more than one row is selected by the SELECT INTO statement an exception is thrown by EDB-SPL.
When the following block is executed, the TOO_MANY_ROWS exception is thrown since there are many employees in the specified department.
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found');
DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);
END;
More than one employee found
First employee returned is ADAMS
Note: See Section 13.5.5 for more information on exception handling.
The INSERT command available in the SQL language can also be used in EDB-SPL programs.
An expression in the EDB-SPL language can be used wherever an expression is allowed in the SQL INSERT command. Thus, EDB-SPL variables and parameters can be used to supply values to the insert operation.
The following is an example of a procedure that performs an insert of a new employee using data passed from a calling program.
CREATE OR REPLACE PROCEDURE emp_insert (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_job IN emp.job%TYPE,
p_mgr IN emp.mgr%TYPE,
p_hiredate IN emp.hiredate%TYPE,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE,
p_deptno IN emp.deptno%TYPE
)
IS
BEGIN
INSERT INTO emp VALUES (
p_empno,
p_ename,
p_job,
p_mgr,
p_hiredate,
p_sal,
p_comm,
p_deptno);
DBMS_OUTPUT.PUT_LINE('Added employee...');
DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
DBMS_OUTPUT.PUT_LINE('----------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS exception on INSERT of employee # ' || p_empno);
DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
END;
If an exception occurs all database changes made in the procedure are automatically rolled back. In this example the EXCEPTION section with the WHEN OTHERS clause catches all exceptions. Two variables are displayed. SQLCODE is a number that identifies the specific exception that occurred. SQLERRM is a text message explaining the error. See Section 13.5.5 for more information on exception handling.
The following shows the output when this procedure is executed.
EXEC emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);
Added employee...
Employee # : 9503
Name : PETERSON
Job : ANALYST
Manager : 7902
Hire Date : 31-MAR-05
Salary : 5000
Commission :
Dept # : 40
----------------------
SELECT * FROM emp WHERE empno = 9503;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9503 PETERSON ANALYST 7902 31-MAR-05 5000 40
See Section 5.1 and INSERT for more information on the SQL INSERT command.
The UPDATE command available in the SQL language can also be used in EDB-SPL programs.
An expression in the EDB-SPL language can be used wherever an expression is allowed in the SQL UPDATE command. Thus, EDB-SPL variables and parameters can be used to supply values to the update operation.
CREATE OR REPLACE PROCEDURE emp_comp_update (
p_empno IN emp.empno%TYPE,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE
)
IS
BEGIN
UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('New Salary : ' || p_sal);
DBMS_OUTPUT.PUT_LINE('New Commission : ' || p_comm);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
The SQL%FOUND conditional expression returns "true" if a row is updated, "false" otherwise. See Section 13.4.7 for a discussion of SQL%FOUND and other similar expressions.
The following shows the update on the employee using this procedure.
EXEC emp_comp_update(9503, 6540, 1200);
Updated Employee # : 9503
New Salary : 6540
New Commission : 1200
SELECT * FROM emp WHERE empno = 9503;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9503 PETERSON ANALYST 7902 31-MAR-05 6540 1200 40
See Section 5.2 and UPDATE for information on the SQL UPDATE command.
The DELETE command available in the SQL language can also be used in EDB-SPL programs.
An expression in the EDB-SPL language can be used wherever an expression is allowed in the SQL DELETE command. Thus, EDB-SPL variables and parameters can be used to supply values to the delete operation.
CREATE OR REPLACE PROCEDURE emp_delete (
p_empno IN emp.empno%TYPE
)
IS
BEGIN
DELETE FROM emp WHERE empno = p_empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || p_empno);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
The SQL%FOUND conditional expression returns "true" if a row is deleted, "false" otherwise. See Section 13.4.7 for a discussion of SQL%FOUND and other similar expressions.
The following shows the deletion of an employee using this procedure.
EXEC emp_delete(9503); Deleted Employee # : 9503 SELECT * FROM emp WHERE empno = 9503; no rows selected
See Section 5.3 and DELETE for information on the SQL DELETE command.
There are several attributes that can be used to determine the effect of a command. SQL%FOUND is a boolean that returns true if at least one row was affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved one or more rows.
BEGIN
UPDATE emp SET mgr = '7900' WHERE job = 'CLERK';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('At least one row has been updated');
END IF;
END;
SQL%NOTFOUND is the opposite of SQL%FOUND. SQL%NOTFOUND returns true if no rows were affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved no rows.
BEGIN
UPDATE emp SET job = 'CLERK' WHERE empno = 9000;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows were updated');
END IF;
END;
SQL%ROWCOUNT provides the number of rows affected by an INSERT, UPDATE or DELETE command.
BEGIN
UPDATE emp SET mgr = 7900 WHERE deptno = 30;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows were updated');
END;