Posted on August 11, 2008 by Joey
Constraints define conditions about a database that must remain true. They are used to ensure data integrity. Commonly used constraints are primary key constraints, foreign key constraints, and unique constraints. Constraints are also used to enforce non-null column values.
A constraint is typically defined as part of the table definition as is created using something similar to the following syntax:
CREATE TABLE ORDERS (
ORDER_ID NUMBER(7) NOT NULL,
PRODUCT_ID NUMBER(7) NOT NULL,
PRODUCT_NAME VARCHAR2(100) NOT NULL,
CONSTRAINT ORDER_ID_PK
PRIMARY KEY(ORDER_ID),
CONSTRAINT PRODUCTS_PRODUCT_ID_FK
FOREIGN KEY(PRODUCT_ID)
REFERENCES PRODUCTS(PRODUCT_ID)
)
The preceding table definition creates two constraints: one for the primary key on the ORDER_ID column; and one for the PRODUCT_ID column foreign key.
Constraints can also be created on a table using the ALTER TABLE syntax, as follows:
ALTER TABLE ORDERS
ADD CONSTRAINT PRODUCTS_PRODUCT_ID_FK
FOREIGN KEY(PRODUCT_ID)
REFERENCES PRODUCTS(PRODUCT_ID)
Filed under: Oracle, Programming, SQL, pl/sql | Tagged: constraints, database, Oracle | 1 Comment »
Posted on August 9, 2008 by Bryan
In ColdFusion, it is generally a good idea to prefix your variables.
Why? Performance and Ambiguity.
Performance
If you set a variable and then tried to output it:
<cfset myvariable = “my value”>
<cfoutput>#myvariable#</cfoutput>
ColdFusion will look into each scope until it finds the variable.
ColdFusion searches for variables without a scope the following order:
- Query result (in a <cfoutput> loop)
- Arguments (within a function or CFC)
- variables (local)
- CGI
- URL
- FORM
- COOKIE
- CLIENT
Ambiguity
If you set a variable the following way and output:
<cfset myvariable = “local variables scope”>
<cfcookie name=”myvariable” value=”cookie scope”>
<cfoutput>#myvariable#</cfoutput>
The result would be: “local variables scope”
This is because ColdFusion searched the scopes in order until it reached the local variables scope.
Both variables still exist and are accessible by using the scope prefix.
<cfoutput>
#variables.myvariable#<br />
#cookie.myvariable#
</cfoutput>
The result would be:
local variables scope
cookie scope
Filed under: ColdFusion | Tagged: cf, ColdFusion, scopes, search order, variables | 1 Comment »
Posted on August 6, 2008 by Bryan
In PHP, the operator to concatenate or combine strings together is: . (dot)
Here are some examples of usage when concatenating strings:
<?php
$string1 = ‘tech’;
$string2 = ’saints’;
$string3 = ‘.com’;
$full = $string1.$string2.$string3;
echo $full;
?>
Result: techsaints.com
<?php
$string = ‘techsai’;
$string .= ‘nts’;
$string .= ‘.com’;
echo $str;
?>
Result: techsaints.com
<?php
$num = 123;
$string = $num.’ is a number’;
echo $string;
?>
Result: 123 is a number
When concatenating a number and a string, the string is automatically cast as a string.
Filed under: php | Tagged: combining, concat, concatenate, concatenating, php, strings | Leave a Comment »
Posted on August 5, 2008 by Joey
The Oracle varchar2 data type allows for the storage of up to 4000 bytes of character data. It can be defined for a column as follows:
MyColumn varchar2(100) DEFAULT ‘MyString’ NOT NULL;
Note that the varchar data type is now deprecated in Oracle as it’s usage has become a synonym for varchar2, although this may change in future versions.
Filed under: Oracle, pl/sql | Leave a Comment »
Posted on July 31, 2008 by Joey
The SET DEFINE OFF command in Oracle prevents Oracle from doing any variable substitution.
Use the SET DEFINE OFF command in Oracle to prevent Oracle from interpreting the ampersand for variable substitution.
The SET DEFINE ON command turns variable substitution back on.
Filed under: Database, Oracle, Programming, SQL, Software, Technology, pl/sql | Tagged: blog, blogging, coding, database, Oracle, pl/sql, techsaints | 1 Comment »
Posted on July 30, 2008 by Joey
The Oracle DUAL table is a table created by Oracle and is used for selects statements that do not have a table name to go along with them.
For example, the DUAL table can be used to select pseudo columns, such as getting the currval or nextval from an Oracle sequence.
SELECT myseq.nextval FROM DUAL;
The DUAL table has one column, named “DUMMY,” which is a VARCHAR2 datatype of length 1. The value of the record is “X.”
All Oracle users have access to the DUAL table, although it is owned by “SYS.”
The DUAL table should not be updated, altered, or deleted.
Filed under: Database, Oracle, Programming, SQL, Software, Technology, pl/sql | Tagged: dual, Oracle, tables | Leave a Comment »
Posted on July 28, 2008 by Joey
The following syntax demonstrates how to create an Oracle tablespace:
CREATE TABLESPACE myTablespace
DATAFILE ‘/oracle/ts_mytablespace.dbf’
SIZE 100m
AUTOEXTEND on
NEXT 10m
MAXSIZE 500m;
The size of this tablespace is defined as 100 megabytes. By having the AUTOEXTEND attribute set to “on,” the statement is specifying that if the tablespace size exceeds 100 megabytes, more space will be allocated for the tablespace. The amount of space by which to extend the tablespace is specified by the “NEXT” attribute, which in this example is 10 megabytes. The tablespace will continue to grow until it reaches the maximum allowable size, which is determined by the “MAXSIZE” attribute – 500 megabytes.
Note: In order to create an Oracle tablespace, you must have the CREATE TABLESPACE privilege.
Filed under: Database, Oracle, Programming, SQL, Software, Technology, pl/sql | Tagged: database, Oracle, SQL, tablespaces, techsaints | Leave a Comment »
Posted on July 26, 2008 by Joey
A tablespace is the Oracle way of referring to a database file. A tablespace is a file set up to contain tables and is the primary logical storage structure for Oracle databases. Logical storage means the tablespace itself cannot be seen in the file system of the database server. The tablespace relates database objects to particular data files. Data files are physically located in the file system of the server.
An Oracle tablespace keeps information about various aspects of tables, including:
- Data types of columns
- Maximum length of data that is permissible in columns
Various types of tablespaces can exist in Oracle. A quite limited description of a few of these are:
Permanent tablespaces, such as the SYSTEM tablespace
- This is where Oracle’s data dictionary resides. If this table space gets deleted, your database will be gone.
- The SYSTEM tablespace is created by Oracle at the time the database is created.
- The SYSTEM tablespace stores the various units that comprise Oracle programs, such as packages, procedures, functions, and triggers.
Temporary tablespaces
- These are used primary for sort operations.
- Cannot contain permanent objects.
Undo tablespaces
- Stores records of transaction data before it is committed to the database.
- By creating an Undo tablespace, a DBA can control how long to keep rollback data.
Filed under: Oracle, Programming, SQL, Software, Technology, pl/sql | Tagged: data files, database, Oracle, pl/sql, SQL, system, tablespaces | Leave a Comment »
Posted on July 23, 2008 by Joey
When a large amount of alphanumeric data needs to be stored in one record, the CLOB data type should be used.
CLOB stands for “Character Large Object.” The CLOB data type allows for the storage of up to 4GB of data. It’s basically the same as the VARCHAR2 data type, with the exception that it allows for the storage of much longer strings of data.
Note that the CLOB data type stores only alphanumeric data. If it is necessary to store other types of data, such as sound or video, the BLOB data type should be used.
Filed under: Oracle, Programming, Software, pl/sql | Tagged: clob, data types | 1 Comment »
Posted on July 22, 2008 by Joey
To list all stored procedures in MySQL, simply execute the following command:
SHOW PROCEDURE STATUS;
Filed under: MySQL, Programming, php | Tagged: MySQL, procedures, SQL, status, stored procedures | Leave a Comment »
