Oracle Common functions

1, set linesize 100; Set length
2, set pagesize 30; Set the number of displays per page
3. Em A.sql Open Notepad
4, @ A executes the code in file A, you can specify the path of the file @d:a.txt
5, conn user name/password to connect to the database based on user name and password if the connection Super Administrator (SYS) should be added as SYSDBA;
6, show user; Display the currently connected user
7. Select * from tab; Get all the tables under the current user
8, desc temp; View table Structure
9./Continue execution of the previous query statement
Clear SCR; Clear Screen

Character functions
10, select Upper (' Coolszy ') from dual; Convert lowercase letters to uppercase, dual to a virtual table
11, select lower (' KUKA ') from dual; Convert uppercase letters to lowercase
12, select Initcap (' Kuka ') from dual; Capitalize the first letter
13. Select Concat (' Hello ', ' world ') from dual; Connection string, but no | | Useful Select Concat (' Hello ', ' world ') from dual;
14, select substr (' Hello ', 1,3) from dual; Intercept string
15. Select Length (' Hello ') from dual; To find the string length
16. Select replace (' Hello ', ' l ', ' X ') from dual; Replace string
17, select substr (' Hello ', -3,3) from dual; Three post-intercept

numeric functions
18, select Round (789.536) from dual; Rounding, rounding out decimals
19, select Round (789.536,2) from dual; Keep Two decimal places
20, select Round (789.536,-1) from dual; Rounding an integer
21, select Trunc (789.536) from dual; Give away decimals, but not carry
22, select Trunc (789.536,2) from dual;
23, select Trunc (789.536,-2) from dual;
24. Select mod (10,3) from dual; Returns the result of 10%3

Date function
25, select Sysdate from dual; Returns the current date
26, select Months_between (sysdate, ' 1 June-June -08 ') from dual; Number of months between returns
27, select Add_months (sysdate,4) from dual; Add a month to the date
28, select Next_day (sysdate, ' Monday ') from dual; Ask for the next Monday
29, select Last_day (sysdate) from dual; Ask for the last day of the month


Conversion functions
30, select To_char (sysdate, ' yyyy ') Year,to_char (sysdate, ' mm '), To_char (sysdate, ' DD ') from dual;
31, select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
32, select To_char (sysdate, ' Fmyyyy-mm-dd ') from dual; Cancel 0 in front of the day of the month
33, select To_char (' 20394 ', ' 99,999 ') from dual; Split Money 9 means format
34, select To_char (' 2034 ', ' l99,999 ') from dual; plus the coin symbol.
35, select To_number (' 123 ') *to_number (' 2 ') from dual;
36, select To_date (' 1988-07-04 ', ' YYYY-MM-DD ') from dual;

General functions
37, select NVL (null,0) from dual; If NULL, replace with 0
38, select Decode (1, 1, ' content is 1 ', 2, ' content is 2 ', 3, ' content is 3 ') from dual; Similar to Switch...case ...

Transaction processing
39, commit; Commit a transaction
40, rollback; Rolling back a transaction


41, select rownum from table; Show line numbers in front of a column
42. DROP TABLE name Cascade constraint
On delete Casecade the contents of the child table are also deleted when the contents of the parent table are deleted

43. DESC table name shows the structure of the table
44. Create user identified by [password] creating new users
45, Grant rights 1, permissions 2...to user to create user rights
Ex:grant create session to can only connect to the database at this time
Grant Connect,resource to at this time permission to meet the requirements
46. Alter user identified by [password] to modify password
47. Alter user password expired prompt to change password at next logon
48, ALTER user account lock lock users
49. Alter user account unlock unlock lock users
50. Grant Select,delete on Scott.emp to take Scott under the EMP table two permissions to the user
51, revoke SELECT, delete on Scott.emo from reclaim permissions
Oracle database functions (single-line function)

Functions in Oracle are similar to functions in C, with function names, parameter tables, and return value types, one-line functions that have a result for each record. A single-line function can appear behind a select, or it can appear in a WHERE clause.

When you need to deal with realistic results, write the function behind the select and use it in the WHERE clause for conditional filtering.

1, character function (varchar)

Characters are case-sensitive
Turn lowercase lower (' ... ')
Turn capital Upper (' ... ')
Initial capital Initcap (' ... ')
The dual table, which is dedicated to function testing and arithmetic, has only one record
String stitching concat (' ... ', ' .... ')
Specify substring substr (' ... ', starting position, number of characters) (starting position can be negative)
Length of the string (' ... ')
You can use "-" to indicate a right-to-left fetch, which can be taken from the left to the friend.
Cases:
Select upper (' Test ') from dual;
Select First_Name from S_emp where upper (first_name) = ' GEORGE ';
Select SUBSTR (first_name,-2,2) sub from S_emp; (Take Two)
Select SUBSTR (first_name,2,2) sub from S_emp; (take top two)

2, numeric function (number)

Round round (data, few after the decimal point)
You can use a negative number to represent the decimal point, 0, the first digit after the decimal point, that is, reserved bits,-1 for bits (reserved to 10 bits).
Example: Select Round (15.36,1) from dual;
Results: 15.4

Intercept number function trunc (data, number of digits reserved (number of digits after the decimal point)) intercept digit after 0
Example: Select Trunc (123.456,-1) from dual; Hold down 10 bits
Results: 120

3, Date function

Date format,
Full date format century information, month day, time seconds.
Default date format, day-month-year DD-MON-RR
The date type can be mathematically calculated, +1, is the next day,-1, is the last day, the database will be implicitly converted to the data.

Modifies the date format of the current session, outputting the date in the specified format
Alter session set nls_date_format= ' yyyy mm DD hh24:mi:ss ';

Returns the current date sysdate
Example: select sysdate+20 from dual;

Date is format-sensitive
Find out how many months are between two dates Months_between (DATE1,DATE2)
Add and subtract the specified number of months add_months (date, number of months), the number of months can be negative, and the negative value is minus the corresponding number of months.
Date starting next week plus day Next_day (date, number of days)

Example: Select Next_day (sysdate,2) from dual;
Returns the date of the month Last_day (date)

Intercept date trunc (date, ' year or month or day or seconds ')
Example: Select Trunc (Add_months (sysdate,1), ' month ') from dual;

4, conversion function between different data types

Convert date to character ToChar (date, ' dates format ')
Date format to be used in valid format, format case sensitive ' yyyy mm DD hh24:mi:ss ', ' Year ' (full spell years), ' mm ' (number of months represented) ' Month ' (full spell month), ' Day ' (full spell of the week), ' ddspth ' (full spell of the day) ' yy MM DD ', ' RR-MM-DD ' (Century judged by year)
Example: Select To_char (sysdate, ' yyyy mm DD hh24:mi:ss ') from dual;

Converts a character to a digital to_number (' ... '), (you can add a second parameter, and the second parameter specifies a numeric binary)

Convert numbers to characters to_char (number, ' FMT ') FMT is a digital format

Convert String to date to_date (' ... ', ' date format ')
Example: Select To_char (To_date (' 2006 ", ' yyyy mm DD '), ' dd-month-yy ')
from dual;

5, Function nesting

Example: Select To_char (to_date (' 2006 ', ' yyyy mm DD '), ' Dd-month-yy ') from dual;

6. Decode function
Example: DECODE (Control_value,value1,result1[,value2,result2 ...] [, Default_result]);

7, distinct
Select a unique result set, that is, to remove duplicate records

8. The syntax for Oracle to generate database triggers is:
create [or replace] trigger trigger name trigger Time trigger Event
On table name
[For each row]
PL/SQL statements
which
Trigger Name: The name of the trigger object. Because the trigger is automatically executed by the database, the name is just a name and has no real purpose.
Trigger time: Indicates when the trigger executes, which is desirable:
Before---indicates that the trigger executes before the database action;
After---indicates that the departure timer executes after the database action.
Trigger event: Indicates which database actions will trigger this trigger:
Insert: Database insert triggers this trigger;
UPDATE: Database modification triggers this trigger;
Delete: Database deletion triggers this trigger.
Table name: The table where the database trigger resides.
For each row: executes once on each row of the table trigger. If you do not have this option, only one time is executed for the entire table.
Example: The following trigger is triggered before updating the table Auths to not allow the table to be modified on weekends:
Create Trigger Auth_secure
Before insert or UPDATE or delete//To update the entire table before it is triggered
On Auths
Begin
if (To_char (sysdate, ' DY ') = ' SUN '
Raise_application_error (-20600, ' cannot modify table auths on weekends ');
End If;
End

9. Cursor Usage:
Defining Cursors First
Cursor cursor name
Is
Select field 1, field 2 from table_name;
Open cursor name;
Loop
Fetch cursor name into My_all, your new variable name;
Exit when cursor name%notfound;

10. PL/SQL
PL/SQL is an extension of Oracle's standard database language, Oracle has consolidated PL/SQL into Oracle servers and other tools, and more developers and DBAs have begun using PL/SQL in recent years, and this article will cover PL/SQL basic syntax, structures and components, And how to design and execute a PL/SQL program.
Benefits of PL/SQL
Since version 6 PL/SQL has been reliably integrated into Oracle, it is hard to imagine that Oracle is missing PL/SQL when it has the benefits of PL/SQL and the convenience of its unique data management. PL/SQL is not a standalone product, it is a technology integrated into Oracle servers and Oracle tools that can treat PL/SQL as an engine within an Oracle server, and a single SQL statement processor to handle pl/ SQL program block. The SQL statement executor in the Oracle server processes the SQL statements in a PL-SQL program block when the PL/SQL program block is processed in the PL/SQL engine.
The advantages of PL/SQL are as follows:
. PL/SQL is a high-performance transactional-based language that can run in any Oracle environment and support all data processing commands. The data definition and data control elements of SQL are processed by using the PL/SQL program unit.
. PL/SQL supports all data types and all SQL functions, while supporting all Oracle object types
. PL/SQL blocks can be named and stored in an Oracle server, and can be invoked by other PL/SQL programs, or by the command of an application that can be accessed by any client/server tool, with good reusability.
. You can use Oracle data tools to manage the security of PL/SQL programs stored on the server. The ability of other users of the database to access PL/SQL programs can be authorized or revoked.
. PL/SQL code can be written using any ASCII text editor, so it is convenient for any operating system that Oracle can run
. For sql,oracle to process each SQL statement at the same time, in a networked environment this means that each individual call must be handled by the Oracle server, which consumes a lot of server time and causes the network to be congested. PL/SQL is sent to the server with the entire statement block, which reduces network congestion.
PL/SQL block structure
PL/SQL is a block-structured language, and the unit that makes up PL/SQL programs is a logical block, and a PL/SQL program contains one or more logical blocks, each of which can be divided into three parts. As with other languages, variables must be declared before they are used, and PL/SQL provides separate parts specifically for handling exceptions, which describe the different parts of the PL/SQL block:
Declaration part (Declaration section)
The Declarations section contains the data types and initial values of variables and constants. This section starts with the keyword declare, and if you don't need to declare variables or constants, you can ignore this part; It is important to note that the declaration of the cursor is also in this section.
Execution part (executable section)
The execution section is the instruction portion of the PL/SQL block, starting with the keyword begin, where all executable statements are placed, and other PL/SQL blocks can be placed in this part.
Exception handling sections (Exception section)
This section is optional, dealing with exceptions or errors in this section, and we'll take a detailed discussion of exception handling.
PL/SQL block syntax
[DECLARE]
---declaration statements
BEGIN
---executable statements
[EXCEPTION]
---exception statements
END

Each statement in a PL/SQL block must end with a semicolon, which can make multiple lines, but the semicolon represents the end of the statement. There can be more than one SQL statement in a row, separated by semicolons. Each PL/SQL block starts with a begin or declare and ends with end. Note by--mark.
The naming and anonymity of PL/SQL blocks
The PL/SQL program block can be a named program block or an anonymous block. Anonymous blocks can be used on the server side as well as on the client.
A named program block can appear in the Declarations section of other PL/SQL program blocks, which is obviously a subroutine that can be referenced in the execution section or in the Exception handling section.
PL/SQL blocks can be independently compiled and stored in a database, and any application connected to the database can access these stored PL/SQL blocks. Oracle provides four types of stored programs:
. Function
. Process
. Package
. Trigger

Function
A function is a named PL/SQL program block that is stored in a database. The function accepts 0 or more input parameters, has a return value, and the data type of the return value is defined when the function is created. The syntax for defining a function is as follows:
FUNCTION name [{parameter[,parameter,...])] RETURN Datatypes is
[Local declarations]
BEGIN
Execute statements
[EXCEPTION
Exception handlers]
END [Name]

Process
A stored procedure is a PL/SQL program block that accepts 0 or more parameters as input or outputs (output), or both as input and output (INOUT), unlike functions, where stored procedures do not have return values and stored procedures cannot be used directly by SQL statements. The syntax for defining stored procedures can be called only through the execut command or the PL/SQL program block:
PROCEDURE name [(Parameter[,parameter,...]) Is
[Local declarations]
BEGIN
Execute statements
[EXCEPTION
Exception handlers]
END [Name]

Packages (Package)
A package is actually a collection of related objects that are grouped together, and when any function or stored procedure in the package is called, the package is loaded into memory, and the subroutine of any function or stored procedure in the package is accessed much faster.
The package consists of two parts: a specification and a package body (body), a specification describing variables, constants, cursors, and subroutines, and a package body that fully defines subroutines and cursors.
Trigger (Trigger)
Triggers are associated with a table or database event, and triggers that are defined on the table are triggered when a trigger event occurs.
Variables and constants
Variables are stored in memory to obtain values that can be referenced by PL/SQL blocks. You can think of variables as a container to store things that can be changed in a container.
declaring variables
Variables are generally declared in the declaration section of the PL/SQL block, and PL/SQL is a strong type language, which means that before referencing a variable you must first declare that you want to use the variable in the execution or exception handling section, and the variable must first be declared in the Declarations section.
The syntax for declaring variables is as follows:
variable_name [CONSTANT] databyte [Not null][:=| DEFAULT expression]

Note: You can impose a NOT NULL constraint on a variable when declaring a variable, at which time the variable must be assigned a value at initialization time.
Assigning values to variables
There are two ways to assign a value to a variable:
. Assigning a value directly to a variable
x:=200;
y=y+ (X*20);
. Assigning a value to a variable via SQL SELECT into or fetch into
SELECT sum (SALARY), sum (salary*0.1)
Into Total_salary,tatal_commission
From EMPLOYEE
WHERE dept=10;
Constant
Constants are similar to variables, but the value of a constant cannot be changed inside the program, and the value of the constant is given when defined, and he is declared in a manner similar to a variable, but must include the keyword constant. Constants and variables can be defined as SQL and user-defined data types.
Zero_value CONSTANT number:=0;

This statement has a constant named Zero_value, whose data type is number and has a value of 0.
Scalar (scalar) data types
Scalar (scalar) data types have no internal components, and they can be roughly divided into the following four classes:
. Number
. Character
. Date/time
. Boolean
Table 1 shows the numeric data types, table 2 shows the character data types, and table 3 shows the date and Boolean data types.
Table 1 Scalar types:numeric
Datatype
Range
subtypes
Description
Binary_integer
- 214748-2147483647
NATURAL
NATURAL
npositive
Positiven
Signtype
is used to store single-byte integers. The
requires storage length below the number value.
Subtype for restriction range (subtype):
NATURAL: For non-negative
POSITIVE: Positive only
Naturaln: only for non-negative and non-null values
Positiven: Only for positive numbers, cannot be used for null values
Signtype: Only values:-1, 0, or 1.
Number
1.0e-130-9.99e125
DEC
DECIMAL
DOUBLE
PRECISION
FLOAT
integeric
INT
NUMERIC
REAL
SMALLINT
Stores numeric values, including integers and floating-point numbers. You can choose the precision and scale method, syntax:
number[([,])]. The
default precision is 38,scale is 0. The
Pls_integer
-2147483647-2147483647
is basically the same as Binary_integer, but Pls_integer provides better performance when using machine operations.

Table 2 Character data types
DataType
Rang
Subtype
Description
CHAR
Maximum length 32767 bytes
CHARACTER
Stores the fixed-length string, if the length is not determined, the default is 1
LONG
Maximum length 2147483647 bytes
Storing variable-length strings
RAW
Maximum length 32767 bytes
Used to store binary data and byte strings, raw data is not converted between character sets when passing between two databases.
Longraw
Maximum length 2147483647
Similar to the Long data type, it is also not possible to convert between character sets.
ROWID
18 bytes
The same as the database ROWID pseudo-column type, can store a row identifier, you can treat the row identifier as a unique key value for each row in the database.
VARCHAR2
Maximum length 32767 bytes
Stringvarchar
Similar to the varchar data type, stores variable-length strings. The Declaration method is the same as varchar

Table 3 Date and Boolean
DataType
Range
Description
BOOLEAN
True/false
Store logical value TRUE or FALSE, no parameters
DATE
01/01/4712 BC
Store fixed-length date and time values that contain time in date values
11.
Dbms_output.put_line (' available number is ' | | v_servnumber ');
12. Cursor Action
For Cur1 in (select Servnumber
from subscriber where status = ' US10 ' and active = ' 1 ')
Loop
V_servnumber: =cur1.servnumber;
Dbms_output.put_line (V_servnumber);
End Loop;
Responded by: Wildwave-2009-09-11 11:53:05
Cursors are fetching data from a database and applying the data.
A simple example is the statistics of the number of men and women. You want to take a gender from a database, and judge the statistics of men and women
A trigger event is a DML (INSERT, UPDATE, DELETE) operation performed on a database table.
/*create OR REPLACE TRIGGER t_sysuserrange_b
Before UPDATE or INSERT or DELETE on Sysuserrange
BEGIN
DELETE * from Sysuserrangeobj
WHERE Sysuserrangeobj. Rangeid: = old. Rangeid;
END t_sysuserrange_b;*/
There are many things you can do with triggers, including:
1. Maintaining complex integrity constraints that cannot be performed at table creation time through declarative constraints
2. Audit the information in the table by recording the changes made and who made the changes
3. When the table is modified, automatically signals to other programs that need to perform the operation.
Trigger restricted constraint:
1. Triggers should not use transaction control statements--commit,rollback,savepoint. Trigger as Trigger statement
Part of the execution is ignited, and it is in the same transaction as the trigger statement. When a trigger statement is committed or a commit is withdrawn,
The work of the trigger is also submitted or withdrawn accordingly.
2. Any procedure or function that is called by a trigger body cannot use a transaction control statement.
3. The trigger body cannot declare any long or long raw variables. Also,: New and: Old cannot point to a long or long raw column in the table that defines the trigger.
4. The table that the trigger principal can access is limited. The table may also vary depending on the type of trigger and the constraint constraints on the table.
Drop Trigger * * Permanently delete trigger
Alter TRIGGER **disable/enable prohibit point as trigger
Used in row-level triggers:: Old and: New (pseudo record)
The INSERT statement did not define: old, and the DELETE statement did not define: new.
: Old and: New is only valid inside a row-level trigger.
The When clause can only be used for row-level triggers.
Using trigger predicates: inserting, updating, deleting
Initcat the first letter of a string into uppercase
INSTR find the location of a string
InStrB find the position and number of bytes of a string
SUBSTR substring of a string
SUBSTRB substring of string (in bytes)
TRANSLATE perform string search and replace
NVL replacing null values with a value

Ceil (value) the smallest integer greater than or equal to value
COSH (value) inverse cosine
Value of EXP (value) E power
Floor (value) is the largest integer less than or equal to value
The natural logarithm of LN (value) value
The base 10 logarithm of log (value) value
MOD (value,divisor) modulo
NVL (Value,substitute) value is empty with substitute instead
Power (value,exponent) value exponent power
ROUND (value,precision) by precision accuracy 4 5 in
sign (value) value returns 1 for positive, 1 for negative, and 0 for 0.
SINH (value)
SQRT (value) The square root of value
TANH (value) anyway cut
TRUNC (value, press precision) to intercept value by precision
Vsize (value) returns the size of value in Oracle's storage space
Lpad (STR,N[,STR2]) Lpad (STR,N[,STR2])
Lpad () and Rpad () will truncate the extra characters when N<str.length is not in the STR2 parameter.
And all the first n characters are taken from left to right.

Oracle Common functions