 |
|
EnterpriseDB: SQL Terminal Interface
Oracle Tips by
Burleson
|
SQL
Terminal is a very basic, character based interface. Most of the
examples in this book were run in the SQL Terminal.
When you
launch the SQL Terminal, you are provided with help commands.
Welcome
to edb-psql 8.1.5.21, the EnterpriseDB interactive terminal.
Type:
\copyright for distribution terms
\h for help with SQL commands
\? for help with edb-psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning:
Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.
testdb=#
The \h
command provides a list of SQL commands. The majority of these
commands are covered in Chapter 3 of this book.
The \?
command is a very useful command as it lists all of the SQL Terminal
specific commands. Rather than list all of the commands here, I
suggest you log into the SQL Terminal and run the command yourself.
With the commands in SQL Terminal, you can connect to other databases,
turn query timing on, change OS file system directory, set and unset
variables and access the query buffer.
For
quick SQL commands, I often choose the SQL Terminal because it loads
fast and has minimal overhead. For heavy or interactive coding,
I usually choose SQL Interactive.
SQL Interactive Interface
The SQL
Interactive window is the GUI replacement to the SQL Terminal.
Results can be formatted in a grid, or displayed textually (as in SQL
terminal).
The
screen shot in Figure 5.41 is in Grid mode and is displaying an error.
You can see that the error is at position 3, which means I have a
misspelling in the SELECT statement. That error is fixed in
Figure 5.40 and the results of the query are displayed in a grid.
Figure
5.40: SQL Interactive Notices
Figure
5.41: SQL Interactive Grid
The
running man icon in the upper left runs the command in the SQL pane
(or press F5). If the SQL pane contains SQL statements, those
statements are executed. If the SQL pane contains procedural
code (such as an anonymous block), the code is executed. If the
SQL pane contains create statements for stored procedures, the code is
compiled.
The
three icons next to the running man allow you to open an existing
query text file, or save the query (existing or new).
The drop
down next to the icons determines the format of your output: Grid mode
for a grid or Text mode for PSQL.
The next
three icons are transaction control. I talked about transaction
control in detail in chapters 2 and 3.
Below
those icons is the history drop down. Select the down arrow and
get a drop down list of all previously run commands.
There is
a limit rows checkbox next to the history drop down. If you
select that checkbox, you can enter the number of rows that you wish
to limit. This is the equivalent of adding a LIMIT clause in
your query.
Below
that is the results window. At the top of the results window are
a set of tabs. Each time you run a different SQL command, you
will get a new tab for that command. If you click on the Notices
tab (Figure 5.42), you will see statistical information for the last
run command.
Figure
5.42: Grid Mode Notices
When you
use the wizard to create a stored procedure, Developer Studio launches
SQL Interactive with a template already loaded.
The
templates may change over time as more functionality is added (or
modified) in EnterpriseDB Advanced Server and in SPL. Below are
the current code templates provided by Developer Studio. For
each of these templates, change the names and add your own code.
Detailed
SPL programming is provided in Chapter 4.
Create
Package Template
CREATE OR REPLACE PACKAGE testschema.<package_name>
IS
PROCEDURE <procedure_name>;
FUNCTION <function_name> RETURN INTEGER;
END;
CREATE OR REPLACE PACKAGE BODY testschema.<package_name>
IS
PROCEDURE <procedure_name>
IS
BEGIN
--Replace with your code
NULL;
END;
FUNCTION <function_name> RETURN INTEGER
IS
BEGIN
-- Add your code here
RETURN
-1;
END;
BEGIN
NULL; -- Replace with initialization code
END;
Create
Procedure Template
CREATE OR REPLACE PROCEDURE testschema.<PROCEDURE_NAME>
IS
BEGIN
--Replace with your code
NULL;
END;
Create
Function Template
CREATE OR REPLACE FUNCTION testschema.<function_name>
RETURN INTEGER
IS
BEGIN
-- Add your code here
RETURN
-1;
END;
Create
Trigger Template
CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE INSERT OR UPDATE OR DELETE ON testschema.<table_name>
FOR EACH ROW
BEGIN
NULL; -- Replace with your code
END;
/
Developing with Other Languages
Developer Studio also allows you to develop in PL/pgSQL, PL/TCL and
any of the other languages supported by EnterpriseDB Advanced Server
and PostgreSQL.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
