Oracle SELECT

The SELECT statement contains input variables and output variables.

Input variables are passed to the database as part
of the execution and the substitution as described above for DELETE,
INSERT, AND UPDATE.
Output variables are used to return values based
on the result of the SELECT statement.

Example 1

Assume you enter the following statement:

SELECT emp_name, emp_salary

1	SELECT emp_name, emp_salary

    INTO :emp_name_var, :emp_salary_var

1	INTO :emp_name_var, :emp_salary_var

    FROM employee WHERE emp_id = :emp_id_var;

1	FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var, emp_salary_var,
and emp_name_var are variables defined within
the scope of the script that contains the SELECT statement, and
emp_id_var is processed as described in the DELETE
example above.

Both emp_name_var and emp_salary_var
are output variables that will be used to return values from the
database. The data types of emp_name_var and emp_salary_var
should be the PowerScript data types that best match the Oracle
data type. When the data types do not match perfectly, PowerBuilder converts
them.

How big should numeric output variables be?

For numeric data, the output variable must be large enough
to hold any value that may come from the database.

Assume the value for emp_id_var is 691 as
in the previous example. When the SELECT statement executes, the
database receives the following statement:

SELECT emp_name,emp_salary

1	SELECT emp_name,emp_salary

    FROM employee WHERE emp_id=691;

1	FROM employee WHERE emp_id=691;

If the statement executes with no errors, data locations for
the result fields are bound internally. The data returned into these
locations is then converted as necessary, and the appropriate PowerScript
variables are set to those values.

Example 2

This example assumes the default transaction object (SQLCA)
has been assigned valid values and a successful CONNECT has executed.
It also assumes the data type of the emp_id column in the
employee table is CHARACTER[10].

The user enters an employee ID into the line edit sle_Emp
and clicks the button Cb_Delete to delete the employee.

The script for the Clicked event in the CommandButton Cb_Delete
is:

// Make sure we have a value.

1	// Make sure we have a value.

if sle_Emp.text &lt;&gt; "" then

1	if sle_Emp.text <> "" then

// Since we have a value, let's try to delete it.

1	// Since we have a value, let's try to delete it.

    DELETE FROM employee

1	DELETE FROM employee

        WHERE emp_id = :sle_Emp.text;

1	WHERE emp_id = :sle_Emp.text;

// Test to see if the DELETE worked.

1	// Test to see if the DELETE worked.

    if SQLCA.sqlcode = 0 then

1	if SQLCA.sqlcode = 0 then

// It seems to have worked, let user know.

1	// It seems to have worked, let user know.

        MessageBox( "Delete",&amp;

1	MessageBox( "Delete",&

            "The delete processed successfully!")

1	"The delete processed successfully!")

    else

else

// It didn't work.

1	// It didn't work.

        MessageBox("Error", &amp;

1	MessageBox("Error", &

            "The delete failed. Invalid Employee ID")

1	"The delete failed. Invalid Employee ID")

    end if

end if

else

else

// No input value. Prompt user.

1	// No input value. Prompt user.

    MessageBox( "Error", &amp;

1	MessageBox( "Error", &

    "An employee ID is required for delete!")

1	"An employee ID is required for delete!")

end if

end if

Error checking

Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate
a specific point.

Example 3

This example assumes the default transaction object (SQLCA)
has been assigned valid values and a successful CONNECT has executed.
The user wants to extract rows from the employee table and insert
them into the table named extract_employees. The extraction
occurs when the user clicks the button Cb_Extract. The
boolean variable YoungWorkers is set to TRUE or FALSE elsewhere
in the application.

The script for the Clicked event for the CommandButton Cb_Extract
is:

integer&#160;&#160;&#160;&#160;EmployeeAgeLowerLimit

1	integer    EmployeeAgeLowerLimit

integer&#160;&#160;&#160;&#160;EmployeeAgeUpperLimit

1	integer    EmployeeAgeUpperLimit

// Do they have young workers?

1	// Do they have young workers?

if (YoungWorkers = TRUE ) then

1	if (YoungWorkers = TRUE ) then

// Yes - set the age limit in the YOUNG range.

1	// Yes - set the age limit in the YOUNG range.

// Assume no employee is under legal working age.

1	// Assume no employee is under legal working age.

    EmployeeAgeLowerLimit = 16

1	EmployeeAgeLowerLimit = 16

// Pick an upper limit.

1	// Pick an upper limit.

    EmployeeAgeUpperLimit = 42

1	EmployeeAgeUpperLimit = 42

else

else

// No - set the age limit in the OLDER range.

1	// No - set the age limit in the OLDER range.

    EmployeeAgeLowerLimit = 43

1	EmployeeAgeLowerLimit = 43

// Pick an upper limit that includes all

1	// Pick an upper limit that includes all

// employees.

1	// employees.

    EmployeeAgeUpperLimit = 200

1	EmployeeAgeUpperLimit = 200

end if

end if

INSERT INTO extract_employee (emp_id,emp_name)

1	INSERT INTO extract_employee (emp_id,emp_name)

    SELECT emp_id, emp_name FROM employee

1	SELECT emp_id, emp_name FROM employee

        WHERE emp_age &gt;= :EmployeeAgeLowerLimit AND

1	WHERE emp_age >= :EmployeeAgeLowerLimit AND

        emp_age &lt;= :EmployeeAgeUpperLimit;

1	emp_age <= :EmployeeAgeUpperLimit;

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 12.6

August 14, 2023

Was this article helpful?

0 Comments

Inline Feedbacks

View all comments

Oracle SELECT – PB Docs 126

Oracle SELECT

Example 1

Example 2

Example 3

My Website Links