An anonymous block is compiled and run each time the block is loaded. An anonymous block is stand-alone code that is not stored within the database and cannot be called by other blocks. The alternative to a standalone block is a procedure, a function, a package or a trigger which you can be stored in the database and called from other blocks. Procedures and functions can be grouped together as subprograms.
The structure of a named block such as a procedure or function as opposed to the
anonymous blocks that we have seen so far in this course, is shown below:
Header (used for named block/modules only) IS Declaration section BEGIN Processing/execution section EXCEPTION Exception section END;
For syntax and more in-depth descriptions, please check your text or the locations on the Web that have been suggested.
I am going to create a procedure to add donations to the donation table. Since I don't
want to mess up the actual table, I have created a copy as shown below.
SQL> CREATE TABLE new_donation AS 2 SELECT * FROM donation; Table created. SQL> SELECT * FROM new_donation; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 8 rows selected.
The code below creates a procedure with the name AddDonation. Notice that there is no
DECLARE in this block, the IS or AS serves the purpose. This procedure sets up variables
that will receive data when the procedure is called. The insert uses these variables to
add a record to the new_donations table. After I have written the code, I did the
@ adddonation which came back with the message, procedure created.
SQL> edit adddonation
CREATE OR REPLACE PROCEDURE AddDonation
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE,
p_contdate new_donation.contdate%TYPE,
p_contamt new_donation.contamt%TYPE) AS
BEGIN
INSERT INTO new_donation(idno, driveno, contdate, contamt)
VALUES(p_idno, p_driveno, p_contdate, p_contamt);
END AddDonation;
/
SQL> @ adddonation Procedure created.
Next a wrote a block of PL/SQL code that would call the procedure that I have just
written. I took in user input for the record that was being created. Notice again the
quotes around non-numeric fields. I then decided to use an IF and call the procedure only
if the v_contamt that the user entered was greater than 20. The call itself uses the
procedure name and passes the variables containing data for the new record to the
procedure. In the procedure above, I started out by giving the procedure the name
AddDonation and then used parenthesis to list the parameters that would be received.
Compare the parameters that would be received in AddDonation to the parameters that are
sent when the call is given. Notice that they match. Again notice that in the procedure
after the parameters in parenthesis the IS or AS is used followed by the BEGIN. The
parameters in the procedure are called formal parameters and the parameters in the calling
block are called actual parameters. The actual parameters are passed to the formal
parameters and they can also receive information if information is passed back.
SQL> edit call_adddonation
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contdate new_donation.contdate%TYPE :='&input_contdate';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
BEGIN
IF v_contamt > 20 THEN
AddDonation (v_idno, v_driveno, v_contdate, v_contamt);
END IF;
END;
/
SET VERIFY ON
SQL> @ call_adddonation Enter value for input_idno: 12121 Enter value for input_driveno: 300 Enter value for input_contdate: 10-JUN-99 Enter value for input_contamt: 75 PL/SQL procedure successfully completed. Input truncated to 5 characters SQL> SELECT * FROM new_donation; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 12121 300 10-JUN-99 75 9 rows selected.
Going a little further, we find that there are three modes for formal parameters: IN,
OUT or IN OUT. IN sends the parameters from the calling program to the procedure.
OUT sends the parameters from the procedure back to the calling program. IN OUT
passes to and receives from the procedure. The default mode is IN. Since no mode was s
pecified in the example above, the default was IN. To test this, I created the following
table:
SQL> CREATE TABLE inout_table 2 (col_in NUMBER, col_out NUMBER, col_inout NUMBER); Table created. SQL> DESC inout_table Name Null? Type ------------------------------- -------- ---- COL_IN NUMBER COL_OUT NUMBER COL_INOUT NUMBER
In the example below, there is a procedure and a calling PL/SQL block. The calling
block takes in three numbers and passes then to the procedure. In the sample run, I
inputed 15, 72 and 99. These three numbers were passed to the proc_calc procedure. The
input number is passed on a read only basis so I did no processing with the p_in. The
output number is passed on a write only basis and so the 72 is ignored and p_out is
calculated within the program as 15 + 15. The input/output number comes in as 99 but
within the program I add 15 + 15 + 15 so this is the number that appears on the table
through the insert.
SQL> edit proc_calc
CREATE OR REPLACE PROCEDURE proc_calc
(p_in IN NUMBER,
p_out OUT NUMBER,
p_inout IN OUT NUMBER) IS
BEGIN
p_out := p_in + p_in;
p_inout := p_in + p_in + p_in;
INSERT INTO inout_table (col_in, col_out, col_inout)
VALUES (p_in, p_out, p_inout);
END;
/
SQL> @ proc_calc Procedure created. SQL> edit calc_proc_calc
DECLARE v_in NUMBER :=&input_in; v_out NUMBER :=&input_out; v_inout NUMBER :=&input_inout; BEGIN proc_calc(v_in, v_out, v_inout); END; /
SQL> @ calc_proc_calc
Input truncated to 1 characters
Enter value for input_in: 15
old 2: v_in NUMBER :=&input_in;
new 2: v_in NUMBER :=15;
Enter value for input_out: 72
old 3: v_out NUMBER :=&input_out;
new 3: v_out NUMBER :=72;
Enter value for input_inout: 99
old 4: v_inout NUMBER :=&input_inout;
new 4: v_inout NUMBER :=99;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM inout_table;
COL_IN COL_OUT COL_INOUT
--------- --------- ---------
15 30 45
I then altered the procedure (proc_calc) to add the input that was received into p_inout
and the variable that was received in p_in and store the result in p_inout. I inputed 23,
33, 43 and the output acted as expected and added the 43 +23 to get 66.
CREATE OR REPLACE PROCEDURE proc_calc
(p_in IN NUMBER,
p_out OUT NUMBER,
p_inout IN OUT NUMBER) IS
BEGIN
p_out := p_in + p_in;
p_inout := p_inout + p_in;
INSERT INTO inout_table (col_in, col_out, col_inout)
VALUES (p_in, p_out, p_inout);
END;
/
SQL> @ calc_proc_calc
Input truncated to 1 characters
Enter value for input_in: 23
old 2: v_in NUMBER :=&input_in;
new 2: v_in NUMBER :=23;
Enter value for input_out: 33
old 3: v_out NUMBER :=&input_out;
new 3: v_out NUMBER :=33;
Enter value for input_inout: 43
old 4: v_inout NUMBER :=&input_inout;
new 4: v_inout NUMBER :=43;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM inout_table;
COL_IN COL_OUT COL_INOUT
--------- --------- ---------
15 30 45
23 46 66
I then went in and altered proc_calc to use p_out in the calculation of p_out as I had done
with p_inout. This time when I did the @ proc_calc I got a warning telling me that the
procedure was created with compilation errors. This was because I misused the field
defined as OUT which is write only. When I tried to execute the calling procedure I
received errors.SQL> edit proc_calc
CREATE OR REPLACE PROCEDURE proc_calc
(p_in IN NUMBER,
p_out OUT NUMBER,
p_inout IN OUT NUMBER) IS
BEGIN
p_out := p_out + p_in;
p_inout := p_inout + p_in;
INSERT INTO inout_table (col_in, col_out, col_inout)
VALUES (p_in, p_out, p_inout);
END;
/
SQL> @ proc_calc Input truncated to 1 characters Warning: Procedure created with compilation errors. SQL> @ calc_proc_calc Input truncated to 1 characters Enter value for input_in: 57 old 2: v_in NUMBER :=&input_in; new 2: v_in NUMBER :=57; Enter value for input_out: 45 old 3: v_out NUMBER :=&input_out; new 3: v_out NUMBER :=45; Enter value for input_inout: 89 old 4: v_inout NUMBER :=&input_inout; new 4: v_inout NUMBER :=89; proc_calc(v_in, v_out, v_inout); * ERROR at line 6: ORA-06550: line 6, column 4: PLS-00905: object SCOTT.PROC_CALC is invalid ORA-06550: line 6, column 4: PL/SQL: Statement ignored
Next, I changed the proc_calc back and changed the calling program calc_proc_calc to
include an INSERT to write a record to the table. This shows the information that comes
back from the procedure to the calling program.
SQL> edit proc_calc
CREATE OR REPLACE PROCEDURE proc_calc
(p_in IN NUMBER,
p_out OUT NUMBER,
p_inout IN OUT NUMBER) IS
BEGIN
p_out := p_in + p_in;
p_inout := p_inout + p_in;
INSERT INTO inout_table (col_in, col_out, col_inout)
VALUES (p_in, p_out, p_inout);
END;
/
SQL> edit calc_proc_calc
DECLARE
v_in NUMBER :=&input_in;
v_out NUMBER :=&input_out;
v_inout NUMBER :=&input_inout;
BEGIN
proc_calc(v_in, v_out, v_inout);
INSERT INTO inout_table (col_in, col_out, col_inout)
VALUES (v_in, v_out, v_inout);
END;
/
SQL> @ calc_proc_calc
Input truncated to 1 characters
Enter value for input_in: 71
old 2: v_in NUMBER :=&input_in;
new 2: v_in NUMBER :=71;
Enter value for input_out: 115
old 3: v_out NUMBER :=&input_out;
new 3: v_out NUMBER :=115;
Enter value for input_inout: 321
old 4: v_inout NUMBER :=&input_inout;
new 4: v_inout NUMBER :=321;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM inout_table;
COL_IN COL_OUT COL_INOUT
--------- --------- ---------
71 142 392
71 142 392