Results for BETWEEN


21 to 40 of 3258 results. Prev  Next
Results per page:   20  | 40 | 60


User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of Implicit Cursor AttributesImplicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL. Note: The SQL cursor has another attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. For

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Advantages of Autonomous TransactionsOnce started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Cursor FOR LoopsIn most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed. Consider the PL/SQL

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Passing Parameters to a Cursor FOR LoopYou can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have salaries higher than $2000 and/or commissions larger than their salaries. -- available online in file 'examp8' DECLARE CURSOR emp_cursor(dnum NUMBER) IS SELECT sal, comm

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Why Use Cursor Variables?Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Controlling Cursor VariablesYou use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable. Opening a Cursor Variable The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, and identifies the result set. Here

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Cursor Variable Example: Manipulating Host Variables in SQL*PlusA host variable is a variable you declare in a host environment, then pass to one or more PL/SQL programs, which can use it like any other variable. In the SQL*Plus environment, to declare a host variable, use the command VARIABLE. For example, you declare a variable of type NUMBER as follows: VARIABLE return_code NUMBER Both SQL*Plus and PL/SQL can reference the host variable, and SQL*Plus can display

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Cursor ExpressionsA cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. Thus, a single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, then any nested cursors within

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of Transaction Processing in PL/SQLThis section explains how to do transaction processing. You learn the basic techniques that safeguard the consistency of your database, including how to control whether changes to Oracle data are made permanent or undone. The jobs or tasks that Oracle manages are called sessions. A user session is started when you run an application program or an Oracle tool and connect to Oracle. To allow user sessions

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Undoing Partial Changes with SAVEPOINTSAVEPOINT names and marks the current point in the processing of a transaction. Used with the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction. In the example below, you mark a savepoint before doing an insert. If the INSERT statement tries to store a duplicate value in the empno column, the predefined exception DUP_VAL_ON_INDEX is raised. In that

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

How Oracle Does Implicit RollbacksBefore executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back. If you exit a stored subprogram with

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Setting Transaction Properties with SET TRANSACTIONYou use the SET TRANSACTION statement to begin a read-only or read-write transaction, establish an isolation level, or assign your current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. During a read-only transaction, all queries refer to the same snapshot of the database,

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Doing Independent Units of Work with Autonomous TransactionsA transaction is a series of SQL statements that does a logical unit of work. Often, one transaction starts another. In some applications, a transaction must operate outside the scope of the transaction that started it. This can happen, for example, when a transaction calls out to a data cartridge. An autonomous transaction is an independent transaction started by another transaction, the main transaction.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Autonomous TriggersAmong other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit inserts into the shadow table whether or not you commit inserts into the main table. -- create a main table

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

6 Interaction Between PL/SQL and OracleKnowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. -- Samuel Johnson This chapter helps you harness the power of Oracle. You learn how PL/SQL supports the SQL commands, functions, and operators that let you manipulate Oracle data. You also learn how to manage cursors, use cursor variables, and process transactions. This chapter discusses the following

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

SQL FunctionsPL/SQL lets you use all the SQL functions including the following aggregate functions, which summarize entire columns of Oracle data: AVG, COUNT, GROUPING, MAX, MIN, STDDEV, SUM, and VARIANCE. Except for COUNT(*), all aggregate functions ignore s. You can use the aggregate functions in SQL statements, but not in procedural statements. Aggregate functions operate on entire columns unless you use the

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Using Cursor SubqueriesYou can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a REF CURSOR that represents the rows returned by the cursor subquery: SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable))); Cursor subqueries are often used with table functions, which

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

What Are Cursor Variables?Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR. To execute a multi-row query, Oracle opens

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Avoiding Errors with Cursor VariablesIf both cursor variables involved in an assignment are strongly typed, they must have the same datatype. In the following example, even though the cursor variables have the same return type, the assignment raises an exception because they have different datatypes: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv ( emp_cv

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Restrictions on Cursor VariablesCurrently, cursor variables are subject to the following restrictions: You cannot declare cursor variables in a package. For example, the following declaration is not allowed:CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowed END emp_stuff; Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot





Was this page helpful?

Integrated Cloud Applications & Platform Services