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

Scoping-naming conventions, Scoping Within the similar scope, all the de...

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.

Sql query for testing triggers and stored procedures , Description: Dem...

Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Using not null-declarations in sql, Using NOT NULL Besides assigning an ...

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Data types in sql - integer, Data Types in SQL - Integer INTEGER or  s...

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Sequential control - pl/sql, Sequential Control Dissimilar to the IF and ...

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Using rename in combination with join - sql, Using RENAME in combination wi...

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

Transaction visibility, Transaction Visibility As the figure shows, th...

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Parameter & keyword description-execute immediate statement, Parameter and ...

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Exception handling, set serveroutput on declare a number(5); b n...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Character types in pl/sql, Character Types The Character types allow yo...

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

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