<Oracle Database 10 gsql> Development Guide notes

1. Functions in Oracle

Functions can be combined, such as: Select name upper (substr (name, 2, 8 ))...
1) Single Row Function
Character functions, numeric functions, conversion functions, date functions, regular expression functions (10 Gb)
A Conversion Function is a function that converts data from one type to another.
2) Aggregate functions
Aggregate functions operate on a group of rows at the same time, and return a row of output results for each group of rows.
AVG/count/max/median/MIN/stddev/sum/variance

 

2. storage and processing of date and time

The time value can be stored by the date type.
Timestamp can be used to store a specific date and time. Compared with date, it can store seconds with decimal places and time zones.
You can use interval to store the length of time.
To_date () and to_char () can be converted between the time value and the string.
To_char (X [, format]) to_date (X [, format]) format is a format Control
Time Value Function:
Addmonth (x, y)
Last_day (X)
Months_between (x, y)
Round (X [, unit])
Sysdate ()
Trunc (X [, unit])
Time zone-related functions:
Current_date ()
Dbtimezone ()
New_time (x, time_zone1, time_zone2)
Sessiontimezone ()
Tz_offset (time_zone)
There are also some functions related to timestamp, such as current_timestamp.
The interval also has several corresponding functions.

 

3. Use of SQL * Plus

Command: a c clbuff del l r/X, etc. Edit the operation buffer command.
Save, search, and run: Save get start @ filename ed spool off
Format column: column clear column format: column clear
Set page size: Set pagesize
Set the row size: Set linesize
Use Variables
Temporary Variable
Use characters & to define temporary variables. When an SQL statement contains a variable, If you execute this statement, you are prompted to enter a value for the variable.
Some control commands for variables:
Set verify off | on
Set define '#'
Variable defined
You can use a defined variable multiple times in an SQL statement until it is explicitly deleted, redefined, or exited SQL * Plus.
Use define definition to view variables. (Undefine delete)
For example, define product_id_var = 7
Accept can also define variables, but you need to wait for user input.
You can use SQL * Plus to create a simple report.
In the script, you can use $1 $2 to reference the parameters passed to the script.
Ttitle btitle can be used to add a header and footer.
Automatically generate SQL statements. By outputting query results in a fixed format, such results are SQL statements that can be directly used.

 

4. Advanced Query

Decode (value, search_value, result, default_value)
Value is compared with search_value. If the two values are equal, result is returned; otherwise, default_value is returned. Decode () allows you to execute if-then-else logic processing in SQL.
The case expression can also be used to implement the if-then-else logic in SQL, which works in a similar way as decode.
Simple case expression:
Case search_expression
When expression1 then result1
When expression2 then result2
...
When expressionn then resultn
Else default_result
End
Search Case expression:
Case
When condition1 then result1
...
When conditionn then resultn
Else default_result
End
Self-reference can be performed in the table, such:
References table_name (name_id)
Table_name indicates that name_id is a column of the table.
In this way, the table is layered. To perform hierarchical queries, use connect by and start
You can use the pseudo column level to display the hierarchy of nodes in the tree.
You can use level and lpad to format hierarchical query results.
Rollup is an extension of the Group by clause and returns subtotal records for each group.
Cube is also an extension of the Group by clause. You can return the subtotal record of each column combination and add a total record at the end.
The grouping () function can accept a column and return 0 or 1. If the column value is empty, grouping returns 1; if the column value is not empty, 0 is returned.
Grouping set can only return subtotal records. Grouping_id () can use the having clause to filter records and remove records that do not contain subtotal or total. Group_id (), which can be used to remove duplicates returned by the Group by clause
Record.
There are many analysis functions in the database, such as rating functions, window functions, and report functions ......
The model clause in Oracle 10g can be used for inter-line computing.

 

5. PL/SQL programming

Block Structure:
[Declare
Declaration_statements
]
Begin
Executable_statements
[Exception
Prediction_handling_statements
]
End;
Variables and cursors can be declared in the declare section. In addition, such variables can only be accessed within the block.
The exception part is the code executed when an exception occurs.
PL/SQL supports conditional logic, such as if, then, else, And endif. There are also for and while loops.
The cursor declaration method is:
Cursor cursor_name is
Select_statement;
When the cursor is opened, the SELECT statement is executed. Open cursor_name;
Fetch retrieves records from the cursor.
Fetch cursor_name
Into variable [, variable...];
Close cursor_name; close the cursor.
You can use create procedure to create a process. (Stored Procedure)
The creation process can be used by any program that can access the database.
You can use the call statement to call the process.
A function is similar to a process. The only difference is that a function must return a value to the statement that calls it. Stored Procedures and functions are sometimes combined and called storage subroutines.
Create function can be used to create a function.
Call the function:
Select function_name (PARAM)
From dual;
Package: processes and functions can be organized together into a package. packages can divide functions related to each other into a self-contained unit. In this way, PL/SQL code is modularized to build a code library that can be reused by other programmers.
Create package create package specifications.
Create package body: Create a package body.
You can use select from dual or call to call a function or process in the package. The package name is limited before the function or process name.
The package, function, and process information can be obtained from the user_procedures view.

Use drop to delete packages, functions, and processes.
Trigger: when a specific SQL DML statement, such as insert, update, or delete, runs on a specific database table, the database automatically runs the process.
Triggers can be activated before and after SQL statements are run.
Statement-Level Trigger and row-Level Trigger.
Create trigger: Create trigger.
You can obtain the trigger information in the user_triggers view.
Alter trigger can enable or disable a trigger. Drop trigger can delete a trigger.

 

6. database objects

Crate type can create objects
Objects can contain functions and are declared using member functions.
Describe can obtain information about object types.
Objects can also be used in PL/SQL
The object type can be inherited. If not final is specified at the end of create type, it indicates that the object type can be inherited.
If an object is used only as a superclass and is not instantiated, the not instantiable can be used to indicate that the class cannot be instantiated.
You can customize constructors.

 

7. Set

Two new database types are introduced after oracle8, called collection, which allows the storage of element sets.
Set Type:
Variable-length array: A one-dimensional array with a maximum size. It is set at creation. However, you can change the size later. (Create type)
Nested table: A table nested in another table. There is no limit on the size. (Create type)
Join array: (10 Gb new) join array is a set of key-value pairs. Similar to a hash table. (Create procedure to create)
The Set also has some functions, such as Count, delete, exists, extend, first, last, next, Prior, trim, etc.

 

8. large objects

Before oracle8, long or longraw (Binary) must be used to store large objects ).
Long raw and long can store up to 2 GB of data. Raw can only store 4 kb of binary data.
A large object can be used after oracle8. a large object is called lob and can store up to TB of data.
Four types of Lob:
Clob character lob, used to store characters
Blob binary lob, which stores binary data
Bfile stores the file pointer. The file is located in the file system, that is, outside the database.
Lob of nclob national characters
Before inserting data, large objects must be initialized. Empty_blob () empty_clob () can be used for initialization.
For example, insert into mytable (ID, clob_colom) values (1, empty_clob (); update the data.
Before using bfile, you must use create directory in the database to create a directory object.
Usage: insert into bfile_content (ID, bfile_colom) values (1, bfilename ('files _ dirs', 'text.txt '))
Files_dirs is the Created directory object.
Large objects are used in PL/SQL, and many functions can be used, such as open, read, substr, append, close, and so on...

 

9. SQL Optimization

1) Use the WHERE clause to filter rows
2) Use table links instead of multiple queries
3) Use a fully qualified column reference when executing a connection
4) Use Case expressions instead of multiple queries
5) Add a table Index
6) use where instead of having
7) use Union all instead of union
8) use exists instead of in
9) use exists instead of distinct
10) bind a variable