Dbartisan Tutorial

[Adabella Frierdich]

Youcan connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer. When connected to the database, you can view schema objects, view the properties and data of Oracle Database tables, and use queries to retrieve data from Oracle Database tables.

This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle SQL Developer User's Guide for installation instructions.

If this is the first time you have started SQL Developer on your system, you are prompted to enter the path to the Java Development Kit (JDK) installation (for example, C:\Program Files\Java\jdk1.8.0_65). Either type the path after the prompt or browse to it, and then press the key Enter.

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password).

You can use the SQL*Plus command DESCRIBE to view the properties of the columns of the EMPLOYEES table in the HR schema and the SQL statement SELECT to view the data. To view other properties of the table, use static data dictionary views (for example, USER_CONSTRAINTS, USER_INDEXES, and USER_TRIGGERS).

If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the hr_conn information expands (see the information that follows "Click OK" in step 2).

The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)

To do the tutorials and examples in this section, you must be connected to Oracle Database as the user HR from SQL Developer. For instructions, see "Connecting to Oracle Database as User HR from SQL Developer".

The query disappears, and you can enter another SQL statement in the Worksheet. When you run another SQL statement, its result appears in the Query Result pane, replacing the result of the previously run SQL statement.

In displayed query results, default column headings are column names. To display a column under a new heading, specify the new heading (alias) immediately after the column name. The alias renames the column for the duration of the query, but does not change its name in the database.

The query in Example 2-5 selects the same columns as the query in "Tutorial: Selecting Specific Columns of a Table", but it also specifies aliases for them. Because the aliases are not enclosed in double quotation marks, they are displayed in uppercase letters.

Suppose that you want to select the FIRST_NAME, LAST_NAME, and DEPARTMENT_NAME of every employee. FIRST_NAME and LAST_NAME are in the EMPLOYEES table, and DEPARTMENT_NAME is in the DEPARTMENTS table. Both tables have DEPARTMENT_ID. You can use the query in Example 2-12.

Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in Example 2-12:

The select_list of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.

The concatenation operator () combines two strings into one string, by appending the second string to the first. For example, 'a''b'='ab'. You can use this operator to combine information from two columns or expressions in the same column of a query result.

For each time stamp value, Oracle Database also stores the fractional part of the second, whose precision you can specify. To store the time zone also, use the data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

The query in Example 2-18 uses the EXTRACT and SYSDATE functions to show how many years each employee in department 100 has been employed. The SYSDATE function returns the current date of the system clock as a DATE value. For more information about the SYSDATE function, see Oracle Database SQL Language Reference. For information about the EXTRACT function, see Oracle Database SQL Language Reference.

The query in Example 2-19 uses the SYSTIMESTAMP function to display the current system date and time. The SYSTIMESTAMP function returns a TIMESTAMP value. For information about the SYSTIMESTAMP function, see Oracle Database SQL Language Reference.

The table in the FROM clause of the query, DUAL, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from DUAL when you want to compute a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. For more information about selecting from DUAL, see Oracle Database SQL Language Reference.

The query in Example 2-20 uses the TO_CHAR function to convert HIRE_DATE values (which are of type DATE) to character values that have the format FMMonth DD YYYY . FM removes leading and trailing blanks from the month name. FMMonth DD YYYY is an example of a datetime format model. For information about datetime format models, see Oracle Database SQL Language Reference.

The query in Example 2-25 returns the last name and commission of the employees whose last names begin with 'B'. If an employee receives no commission (that is, if COMMISSION_PCT is NULL), the NVL function substitutes "Not Applicable" for NULL.

The query in Example 2-26 returns the last name, salary, and income of the employees whose last names begin with 'B', using the NVL2 function: If COMMISSION_PCT is not NULL, the income is the salary plus the commission; if COMMISSION_PCT is NULL, income is only the salary.

The DECODE function compares an expression to several search values. Whenever the value of the expression matches a search value, DECODE returns the result associated with that search value. If DECODE finds no match, then it returns the default value (if specified) or NULL (if no default value is specified).

The query in Example 2-29 uses the DECODE function to show proposed salary increases for three different jobs. The expression is JOB_ID; the search values are 'PU_CLERK', 'SH_CLERK', and 'ST_CLERK'; and the default is SALARY.

The arguments of the DECODE function can be any of the SQL numeric or character types. Oracle automatically converts the expression and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is NULL, then Oracle converts the return value to the data type VARCHAR2.

In this lesson, you learn how to use the debugger to identify errors in stored procedures. The SQL Anywhere sample database, demo.db, contains a stored procedure named debugger_tutorial, which contains a deliberate error. The debugger_tutorial procedure should return a result set that contains the name of the company that has placed the highest value of orders, and the value of their orders. It computes these values by looping over the result set of a query that lists companies and orders. (This result could be achieved without adding the logic into the procedure by using a SELECT FIRST query. The procedure is used to create a convenient example.) However, the bug contained in the debugger_tutorial procedure results in its failure to return the specified result set. In this lesson, you run the stored procedure and diagnose and fix the bug.

To diagnose the bug in the procedure, set breakpoints in the procedure, beginning with the first executable statement. Step through the code, watching the value of variables as the procedure is executed.

In the Debugger Details window, click the Local tab to display a list of local variables in the procedure together with their current value and data type. The Top_Company, Top_Value, This_Value, and This_Company variables are all uninitialized and are therefore NULL.

Copying tables in MySQL is a routine operation performed by DBAs, developers, and analysts dozens of times a day for various reasons and with various purposes. In this tutorial, we provide a detailed overview of the most common methods to copy a MySQL table structure and data.

In case you need to copy the table structure including primary keys, foreign keys and constraints, run the SHOW CREATE TABLE ... statement, then copy the script for the original table, change the name of the table and execute the script.

If you need to copy to another database schema only the table structure, use the CREATE TABLE LIKE statement, but again don't forget to specify the database names. Remember that by a schema in MySQL is typically meant a system schema. Simply, put a schema in MySQL corresponds to the concept of a database in SQL Server.

However, by running the query above, you will create a clone of the source table with all the column attributes, indexes, and keys. To copy the table structure only, you can run use a LIMIT clause in the query to make MySQL left aside the data.

Keep in mind that if you select Drop destination object, a DROP IF EXISTS operation is performed, followed by CREATE. If you are working with a table object, all its data will be irreversibly lost.

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.

Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.

Connecting to the server and database will require entering the host, port, database user and password. InterBase databases are defaulted to the port 3050 or 3051 and provide the default system administrator user, SYSDBA (not case-sensitive) with a password of masterkey (all lowercase). Note: It is recommended that you change the SYSDBA password for improved security.

3a8082e126