Third step at defining type sid in sql, PL-SQL Programming

Assignment Help:

Third Step at defining type SID in SQL

CREATE DOMAIN SID AS VARCHAR(5)

CHECK ( VALUE IS NOT NULL AND

SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND

CAST('+'||SUBSTRING(VALUE FROM 2) AS INTEGER) >= 0 );

Explanation:

  1. DOMAIN SID announces that a domain named SID is being defined to the system.
  2. AS VARCHAR(5) specifies that values in domain SID are certain values of type VARCHAR(5).
  3. CHECK ( ... ) specifies a constraint defining exactly which values of type VARCHAR(5) are in the domain SID. Note that the key word VALUE, which is available only in domain constraints, refers (in this particular example) to an arbitrary value of type VARCHAR(5).
  4. VALUE IS NOT NULL specifies that the null value of type VARCHAR(5) is not a value in the domain. This is needed because the other conjuncts evaluate to UNKNOWN if VALUE is the null value and a domain constraint is deemed to be violated only when it evaluates to FALSE.
  5. SUBSTRING(VALUE FROM 1 FOR 1) = 'S' specifies that every value in the domain must begin with S. Note SQL's deliberate use of "noise" words in the invocation of SUBSTRING, the justification for which is to distinguish invocations of system-defined operators from those of user-defined ones.
  6. CAST('+'||SUBSTRING(VALUE FROM 2) AS INTEGER) >= 0 is an attempt to emulate an invocation of IS_DIGITS, perhaps showing how a user-defined operator of that name might be implemented in SQL. The character "+" is concatenated to the putatively numeric portion of the string in order to exclude values such as 'S+123' from the domain (the string '+123' can be cast as an integer but '++123' cannot).

Related Discussions:- Third step at defining type sid in sql

Join and and in sql, JOIN and AND in SQL In this Section is all about ...

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Procedures - syntax, Procedures The procedure is a subprogram which can...

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Loop statements, LOOP Statements The LOOP statements execute a series o...

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Relational shema.., Find the account numbers of all customers whose balance...

Find the account numbers of all customers whose balance is more than 10,000 $

Using extend - collection method, Using EXTEND To enlarge the size of ...

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Semijoin and composition - sql, Semijoin and Composition - SQL For sem...

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Data types in sql - character, Data Types in SQL - Character CHARACTER...

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Use bulk binds - improve performance of application, Use Bulk Binds If...

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Out mode - parameter modes, OUT Mode An OUT parameter returns values t...

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Built-in functions-comparison operators, Built-In Functions The PL/SQL p...

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd