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
Using a Join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 JOIN CONTRACT ON EMPLOYEE

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

BETWEEN Operator The operator BETWEEN, tests whether the value lies in a specified series. That means "greater than or equivalent to low value and less than or equivalent to hig

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Usefulness of Data Type In SQL, as in most computer languages, a type can be used for constraining the values that are permitted to be used for some purpose. In particular, i

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst