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

Pl sql code to declare cursors with parameter, Write a pl/sql block that de...

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Assignment 4, I need a query for PL/SQL, selecting names with cursor, goes ...

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Rollback behavior - bulk bind performance improvement, Rollback Behavior ...

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Accessing attributes in pl sql, Accessing Attributes: You can refer to ...

Accessing Attributes: You can refer to an attribute only by its name not by its position in the object type. To access or modify the value of an attribute, you can use the dot

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA...

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

Program, heap sort program in pl/sql

heap sort program in pl/sql

Keyword & parameter description - expressions, Keyword & Parameter Descript...

Keyword & Parameter Description: boolean_expression: This is an expression which yields the Boolean value that is TRUE, FALSE, & NULL. character_expression: This

Null statement-sequential control, NULL Statement The NULL statement c...

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Substitution and instantiation - sql, Substitution and Instantiation - SQL ...

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Assignment of DBMS in SQL server, i have an assignment of DBMS subject, thi...

i have an assignment of DBMS subject, this assignment is based on SQL server and power BI

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