C language development process, need to operate the database, use Oracle database generally will use Oracle Pro*C/C++ precompiler.
This article introduces the Oracle Pro*C/C++ precompiler in detail, learns its role in developing applications that manipulate Oracle data, and learns what it enables your applications to do. Specific as use.
Basic SQL statements
Executable SQL statements enable you to query, manipulate, and control Oracle data and create, define, and maintain Oracle objects, such as tables, views, and indexes. This chapter focuses on the statements used to query and manipulate data.
When executing a data-manipulation statement (such as INSERT, UPDATE, or DELETE), the only thing you care about, other than setting the value of any input main variable, is whether the statement succeeds or fails. To find out, you just need to check the SQLCA. (Executing any SQL statement sets the SQLCA variable.) You can check it in either of the following ways:
- Use the clean brook statement for implicit checking
- Explicitly check SQLCA variables
However, when a SELECT statement (query) is executed, you must also deal with the rows of data it returns. Queries can be categorized as follows:
- A query that does not return any rows (that is, just checks for existence)
- A query that returns only one row
- Returns a query with more than one row
Return cursors that need to be explicitly declared for queries with more than one line or use host arrays (host variables declared as arrays).
This chapter assumes the use of scalar host variables.
The following embedded SQL statements allow you to query and manipulate Oracle data:
| Embedded SQL statement | describe |
|---|---|
| SELECT | Returns rows from one or more tables. |
| INSERT | Add a new row to the table. |
| UPDATE | Modify rows in a table. |
| DELETE | Deletes unwanted rows from the table. |
The following embedded SQL statements allow you to define and manipulate explicit cursors:
| Embedded SQL statement | describe |
|---|---|
| DECLARE | Name the cursor and associate it with the query. |
| OPEN | Execute the query and identify the set of activities. |
| FETCH | Advance the cursor and retrieve each row in the active set line by line. |
| CLOSE | Disable the cursor (active set becomes undefined). |
In the sections that follow, you’ll first learn how to write INSERT, UPDATE, DELETE, and single-line SELECT statements. You then enter the multi-line SELECT statement.
The SELECT statement
Querying a database is a common SQL operation. To issue a query, use the SELECT statement. In the following example, you query the EMP table:
EXEC SQL SELECT ename, job, sal + 2000
INTO :emp_name, :job_title, :salary
FROM emp
WHERE empno = :emp_number;
Copy the codeThe column names and expressions following the keyword SELECT make up the selection list. The selection list in our example contains three items. Under conditions specified in the WHERE clause (and subsequent clauses, if any), Oracle returns the column value to the main variable in the INTO clause.
The number of items in the selection list should equal the number of primary variables in the INTO clause, so there is a place to store each return value.
In the simplest case, when a query returns a row, it has the form shown in the previous example. However, if the query can return multiple rows, you must use the cursor FETCH rows or SELECT them into an array of host variables. Cursors and FETCH statements are discussed later in this chapter.
If you write a query that returns only one row, but may actually return multiple rows, the result of the SELECT is indeterminate. Whether this causes an error depends on how you specify the SELECT_ERROR option. The default value YES generates an error if more than one row is returned.
Content available
You can use all of the following standard SQL clauses in your projects
The SELECT statement:
- INTO
- FROM
- WHERE
- CONNECT BY
- START WITH
- GROUP BY
- HAVING
- ORDER BY
- FOR UPDATE OF
With the exception of the INTO clause, the text of an embedded SELECT statement can be executed and tested interactively using SQLPlus. In SQLPlus, you use substitution variables or constants instead of input main variables.
The INSERT statement
Add rows to a table or view using INSERT statements. In the following example, you add a row to the EMP table:
EXEC SQL INSERT INTO emp (empno, ename, sal, deptno)
VALUES (:emp_number, :emp_name, :salary, :dept_number);
Copy the codeEach column you specify in the column list must belong to the table named in the INTO clause. The VALUES clause specifies the row of VALUES to be inserted. These values can be constants, host variables, SQL expressions, SQL functions (such as USER and SYSDATE), or user-defined PL/SQL functions.
The number of VALUES in the VALUES clause must equal the number of names in the column list. However, if the VALUES clause contains the VALUES of each column in the table, you can omit the list of columns in the order they are defined in the table.
About using subqueries
A subquery is a nested SELECT statement. Subqueries enable you to conduct a multi-part search. They can be used
- Provides values for comparisons in the WHERE, HAVING, and START WITH clauses of SELECT, UPDATE, and DELETE statements
- Defines the rowset to be inserted by the CREATE TABLE or INSERT statement
- Define values for the SET clause of the UPDATE statement
The following example uses a subquery to copy rows from one table to another in an INSERT statement:
EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno)
SELECT empno, ename, sal, deptno FROM emp
WHERE job= :job_title ;
Copy the codeThis INSERT statement uses a subquery to get intermediate results.
The UPDATE statement
Use the UPDATE statement to change the value of a specified column in a table or view. In the following example, we update the SAL and COMM columns EMP in the table:
EXEC SQL UPDATE emp
SET sal = :salary, comm = :commission
WHERE empno = :emp_number;
Copy the codeUse the optional WHERE clause to specify conditions for updating rows.
The SET clause lists the names of one or more columns for which you must provide a value. You can use subqueries to provide values, as shown in the following example:
SELECT * from emp where Sal = (SELECT AVG(Sal)*)1.1 FROM emp WHERE deptno = 20Empno = :emp_number;Copy the codeThe UPDATE statement has an optional returning clause, such as the INSERT and DELETE statements. It is only allowed after the optional WHERE condition.
The DELETE statement
DELETE rows from a table or view using the DELETE statement. In the following example, you remove all employees from the EMP table for a given department:
EXEC SQL DELETE FROM emp
WHERE deptno = :dept_number ;
Copy the codeWe use the optional WHERE clause to specify conditions for deleting rows.
The returning clause option can also be used for DELETE statements. It is allowed after the optional WHERE condition. In the previous example, it is a good practice to record the field values for each employee that is deleted.
The WHERE clause
Use the WHERE clause to SELECT, UPDATE, or DELETE only rows in a table or view that meet the search criteria. A WHERE clause search criterion is a Boolean expression that can include scalar host variables, host arrays (not in SELECT statements), subqueries, and user-defined storage functions.
If the WHERE clause is omitted, all rows in the table or view are processed. If the WHERE clause is omitted in an UPDATE or DELETE statement, Oracle sets SQLWARN [4] to ‘W’ in SQLCA to warn that all rows have been processed.