Effect of anonymous columns, PL-SQL Programming

Effect of Anonymous Columns

Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is created, column definitions have to be implied by that initial value, so the question arises, how can a VALUES expression provide the initial value for a base table? The answer is that you have to learn an extra syntactic construct for that purpose, shown in Example.

Example: Specifying an initial value for a base table.

CREATE TABLE ENROLMENT (StudentId, Name, CourseId)

AS (VALUES (SID ('S1'), 'Anne', CID ('C1')),

(SID ('S2'), 'Boris', CID ('C1')))

WITH DATA;

Explanation:

  1. (StudentId, Name, CourseId) provides the names, positionally corresponding to the anonymous columns of the AS table.
  2. WITH DATA specifies that the given table is indeed to be the initial value. Curiously, this is required, unless WITHOUT DATA is written instead, in which case the AS table serves only to determine the declared types of the columns of the base table.
  3. SQL does not allow constraints to be declared if AS is used in place of explicit column definitions.
Posted Date: 1/18/2013 2:56:02 AM | Location : United States







Related Discussions:- Effect of anonymous columns, Assignment Help, Ask Question on Effect of anonymous columns, Get Answer, Expert's Help, Effect of anonymous columns Discussions

Write discussion on Effect of anonymous columns
Your posts are moderated
Related Questions
which operation is used if we are interested in only certain columns of a table?

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a