This video we are going to discuss the NUMBER data type. The Number data type is used to store integers, and real numbers. When you create a column as a NUMBER, you can store pretty huge or pretty small numbers in this column. Now there are two things you need to consider when working with numbers, and that is the precision as well as the how big the number is.
For example, we can store the number 9.9. This has two significant digits. We could also store the number 9.9 X 10^4. In this situation, the number is much larger, but the number of significant digits is the same. 9.9 are the significant digits. When we expand this out we just have 99000, and the zeros are just used for size and are not considered "significant" in this situation. In fact, you get a max precision of 38, but a maximum value of 9.99 * 10^125. You can also use this data type to store very small numbers. Check the docs for the specifics on maximums and minimums.
You can provide it with two pieces of information:
Precision - The total number of digits.
Scale - The number of digits to the right of the decimal.
You do it in this format: NUMBER (precision, scale).
The important thing to remember in this is that when you specify a precision, you will be limiting the max size of the numbers.
The secret behind this data type is that it is actually stored in scientific notation. That is we store a number and then we can multiply it by 10 raised to some power. This allows us to store much larger numbers without taking up a ton of space.
The oracle docs actually gives a formula that you can use to see how much storage is going to be required for a specific NUMBER data type.
How much precision can be used? The acceptable range is 1-38.
What about scale? The range is actually -84 to 127. I'll explain the scale in more detail in an upcoming video. That's because there is a lot of confusing things here…What does it mean for the scale to be negative? How can we have a scale that is bigger that the total number of digits available through the precision. That's a topic for another video.
It's important to understand that when we increase our scale, we decrease the max size of the number. For example if we have a precision of 5 and a scale of 3, the highest number we can store is 99.999. This is in contrast to a precision of 5 and a scale of 2 which allows for up to 99.999. Either way you get 5 significant digits, but the numbers of digits to the left and right of the decimal change.
Support me! http://www.patreon.com/calebcurry
Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter
More content: http://CalebCurry.com
Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Caleb, this series has been a great help to me starting a new role and learning a new language. You have a great way of teaching the subject and I've been engaged the whole way through. I hope you are well and that there are more videos to come on the subject.
A trigger is a named PL/SQL unit that is stored in the database and executed ( fired ) in response to a specified event that occurs in the database.
Overview of Triggers.
A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:
A database manipulation (DML) statement ( DELETE , INSERT , or UPDATE )
A database definition (DDL) statement ( CREATE , ALTER , or DROP )
A database operation ( SERVERERROR , LOGON , LOGOFF , STARTUP , or SHUTDOWN )
The trigger is said to be defined on the table, view, schema, or database.
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.
An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement. For more information, see Modifying Complex Views (INSTEAD OF Triggers).
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points :
Before the triggering statement executes.
After the triggering statement executes.
Before each row that the triggering statement affects.
After each row that the triggering statement affects.
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data. For more information, see Compound Triggers.