DBMSOUTPUT package simple SELECT into PLSQL PLSQL does not have virtual IO

Dbmsoutput package simple select into plsql plsql

This preview shows page 7 - 11 out of 38 pages.

DBMS_OUTPUT package; simple SELECT into PL/SQL PL/SQL does not have virtual I/O statement(s) such as “READ”, but built-in package DBMS_OUTPUT has many I/O routines for use in PL/SQL DBMS_OUTPUT.PUT_LINE( ) is one of the most-used routines (This does not cause immediate output flush (does lazy writes)) The session variable SERVEROUTPUT must be toggled ON to display package’s session output (default is OFF) { has been added to htmlMarkup.sql script } Next, illustrate SQL retrieval of a single column value from a table into an anchored variable in PL/SQL
Image of page 7
8 First PL/SQL anonymous block example -- FileName.plsql, WJM, CSUS -- First PL/SQL anonymous block example -- Usage: Enable DBMS_OUTPUT output with: set serveroutput on, -- and compile via: SQL> @FileName.plsql DECLARE v_lname company.employee.lname%TYPE; BEGIN -- Limitation: only ONE can be returned into PL/SQL variable v_lname SELECT lname INTO v_lname FROM company.employee WHERE ssn like '333445555'; DBMS_OUTPUT.PUT_LINE ('Last name of SSN 333445555 is: ' || v_lname); -- Type conversion notes: to_char(v_lname) is illegal to_char call -- int type values do not need conversion by to_char END; / show errors Notes: 1. SQL syntax recognized without need of any extra interface syntax 2. || is the string concatenation operator in string expressions 3. A version of this code is on web site: blocks3.plsql String concatenation
Image of page 8
9 Bind and Anchored variables Bind variables (aka host variables) are: - declared in the host environment such as SQL*Plus Ex: SQL> variable g_double varchar2(20) - within PL/SQL code, a bind variable is accessed/used with “:” prefix (Ex: :g_double) - and a host variable can be assigned and PRINTed in SQL*Plus: SQL> BEGIN 2 :g_double := ‘Hi from PL/SQL’; 3 END; 4 / SQL> PRINT g_double (do not use leading “:” to refer to bind variable at host (here SQL*Plus) level) ------------------------------- 3 languages involved: Results of PRINT statement: host language: SQL*Plus G_DOUBLE 3GL language: PL/SQL -------------------------------- DB language: SQL Hi from PL/SQL Note: Identifier name convention: g_xxx is an SQL*Plus host variable Anchored variables are declared in PL/SQL code - declare via: x%TYPE, where x can be a declared PL/SQL variable or a table or view or column name or reference - iterative anchoring is allowed: v_comm NUMBER(7,2); v_total_comm v_comm%TYPE; v_net_comm v_total_comm %TYPE; - NOT NULL constraints are inherited from PL/SQL declarations, but not from SQL table columns
Image of page 9
10 Cursors and Exceptions A more general (larger) work area in PL/SQL is needed for SQL SELECT queries that have n rows results vs. first anonymous example with 1 row returned A cursor is the SQL-2 standard construct for a DB < - - > app interface A cursor has User data (rows) & very complex Meta data internals Cursor provides storage for the SQL statement and returned results and program /SQL/DB communication, such as errors.
Image of page 10
Image of page 11

You've reached the end of your free preview.

Want to read all 38 pages?

  • Spring '16
  • Gopal Rao
  • Oracle Database, Control flow

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture