Variables in pl / sql. What is this code doing?

Question

I read the book "Oracle Database Architecture Expert"

I found this code that I do not understand, I do not understand this way of writing variables. Are these global variables?

how can I do this to check the timestamp for all rows in the table, and not just the deptno 10?

ops$tkyte%ORA11GR2> variable deptno number
ops$tkyte%ORA11GR2> variable dname varchar2(14)
ops$tkyte%ORA11GR2> variable loc varchar2(13)
ops$tkyte%ORA11GR2> variable last_mod varchar2(50)
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
 2 :deptno := 10;
 3 select dname, loc, to_char( last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' )
 4 into :dname,:loc,:last_mod
 5 from dept
 6 where deptno = :deptno;
 7 end;
 8 /
PL/SQL procedure successfully completed.

This is the second very basic SQL\*Plus question you've asked in the last day. Maybe you should familiarise yourself with the documentation before you start on Tom Kyte's fine book. [Find the docs here](https://docs.oracle.com/database/121/SQPUG/toc.htm) — APC, Nov 29 '18 at 13:24

score 2 · Accepted Answer · answered Nov 29 '18 at 13:06

2

These variables have been defined in SQL*Plus. The author used them to avoid declaration within the PL/SQL anonymous block and display their values with the PRINT command:

SQL> var dname varchar2(10)
SQL>
SQL> begin
  2    select dname
  3      into :dname
  4      from dept
  5      where deptno = 10;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print dname

DNAME
--------------------------------
ACCOUNTING

SQL>

Another option (which uses DBMS_OUTPUT.PUT_LINE to display the value) would be

SQL> set serveroutput on
SQL> declare
  2    l_dname varchar2(10);
  3  begin
  4    select dname
  5      into l_dname
  6      from dept
  7      where deptno = 10;
  8
  9    dbms_output.put_line(l_dname);
 10  end;
 11  /
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>

As of all timestamps: you'd remove parameter (in the WHERE clause) but - due to many rows in a table - you'd get TOO_MANY_ROWS error, so you'd have to, for exmaple, do it in a loop or return ref cursor.

answered Nov 29 '18 at 13:06

Littlefoot

131,892
15
35
57

Sorry, I don't understand the question. Difference between what? – Littlefoot Nov 29 '18 at 16:04
I think he could get that result with `select dname from dept where deptno = 10` – Goku Nov 29 '18 at 18:00
1

Ah! Yes, that would produce the same result. – Littlefoot Nov 29 '18 at 18:08
Why did he write unnecessary variables, and a lot of code? – Goku Nov 29 '18 at 18:45
I wouldn't know; you'd have to ask the author (or, possibly, read the book more carefully - maybe it is already explained). – Littlefoot Nov 29 '18 at 20:06

Variables in pl / sql. What is this code doing?

1 Answers1