Oracle Advanced Practical Notes

This article contains the following content:
1. Oracle PL/SQL usage
2.5 Constraints in Oracle (non-NULL constraints, UNIQUE constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, check constraints)
3, Oracle single quotation mark, double quotation mark difference (OTL programming not pay attention to often make mistakes)
5. Business
6. System Date & System time
7. Sequence
8. Stored Procedures
9. Functions

1. Oracle PL/SQL usage
   Introduction
   PL/SQL is a programming language within Oracle that is used to write stored procedures, triggers, functions, and so on.
   The PL/SQL language is a complement to that. SQL has no branches, no loops, is the fourth generation programming language, non-process, as long as the results are obtained.

Note

You must perform set serveroutput on before you can see the output.
Performing set serveroutput on can cause Sql*plus to retrieve and display buffer.

"/" means execution, equivalent to the input of run;

For example
Sql> set Serveroutput on
Sql> Declare
2 v_sal number (6,0);
3 begin
4 V_sal: = 6600;
5 if (V_sal < 2500) Then
6 V_sal: = V_sal * 2;
7 Dbms_output.put_line (' Sal < 2500 ' | | v_sal);
8 elsif (V_sal > 2500) Then
9 V_sal: = V_SAL/2;
Ten dbms_output.put_line (' sal > 2500 ' | | v_sal);
One else
Dbms_output.put_line (' sal = 2500 ' | | v_sal);
End If;
The end;
15/
Sal > 2500 3300

PL/SQL procedure successfully completed.

Common variable types:
1) Binary_integer: integer, used primarily for counting instead of representing field types
2) Number: Numeric type
3) Char: fixed length string
4) VARCHAR2: variable length string
5) Date: Day
6) Long: length string, maximum 2GB
7) Boolean: Boolean type, can be evaluated as true, FALSE, and Null (it is recommended that the Boolean type give the initial value at the time of declaration, otherwise it is null)

Rules for variable declarations:
1) variable names cannot be used with reserved words, such as from, select, etc.
2) The first character must be a letter
3) variable names contain a maximum of 30 characters
4) do not have the same name as the database table or column
5) Only one variable can be declared per line

Description
1) constant equivalent to final, constant in Java, const in C + +, constant.
2) "| |" is a string connector.
3) Dbms_output.put_line cannot print values of Boolean type.

Cycle
Sql> Declare
2 I Binary_integer: = 1;
3 begin
4 loop
5 Dbms_output.put_line (i);
6 I: = i + 1;
7 Exit when (I >= 9);
8 End Loop;
9 End;
10/
1
2
3
4
5
6
7
8

PL/SQL procedure successfully completed.

Summary
The PL/SQL Write program is very fixed:
[DECLARE]
– Declarations section, optional. Declaring various variable cursors
BEGIN
– The execution section must be. Starting from here, the program starts to execute
[EXCEPTION]
– Exception Handling section, optional. Equivalent to what is executed when catch to exception
END;
– End with a semicolon after end

–eg: The simplest block of statements

Begin
Dbms_output.put_line (' Hello world! ');
End
/

5 Kinds of constraints in 2.Oracle
Five constraints on a table in Oracle
Non-null (NOT NULL) constraint
Only (unique) constraint
Primary key (primary key) constraint
Foreign key (foreign key) constraint
Check Constraint
2.1 Non-null constraints
CREATE TABLE TT (
ID Number (3) is not NULL,
Name VARCHAR2 (+) constraint tt_name_nn NOT NULL
);

2.2 Uniqueness constraints
1), the unique constraint requires that the combined value of the column or column being constrained is unique and cannot have two of the same value.
2), unique constraints can be defined at the column level can also be defined at the table level. A table-level can define a combination of fields.
3), the UNIQUE constraint allows null values, because null values are not equal to any value.
4), the unique constraint of the combined field, as long as the combination of fields is not exactly the same can be inserted into the table.
CREATE TABLE TT (
ID Number (3),
Name VARCHAR2 (100),
Constraint Tt_id_name_uk Unique (ID, NAME)
);

SQL> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name=‘TT‘;OWNER--------------------------------------------------------------------------------CONSTRAINT_NAME C TABLE_NAME------------------------------ - ------------------------------SYSTEMSYS_C007178 C TTSYSTEMTT_NAME_NN C TT

Example of a unique insertion violation:
sql> drop table TT;
Table dropped.

Create
sql> sql> CREATE TABLE TT (
2 ID Number (3),
3 name VARCHAR2 (100),
4 constraint Tt_id_name_uk unique (ID, NAME)
5);
Table created.

Insert
sql> INSERT INTO TT values (1, ' aaaa ');
1 row created.

Insert
sql> INSERT INTO TT values (1, ' bbbb ');
1 row created.

Repeat the insertion again, error!
sql> INSERT INTO TT values (1, ' aaaa ');
INSERT into TT values (1, ' AAAA ')
*
ERROR at line 1:
Ora-00001:unique constraint (System.tt_id_name_uk) violated

2.3 Primary Key
* * The primary key allows you to find the record that corresponds to the unique row.
1), a table can have only one primary KEY constraint.
2), a single primary key is defined at the column level, and the combined primary key is defined at the table level.
3), primary key does not allow null values, duplicate values are not allowed.
Sql> CREATE TABLE tb_pk_example (ID number, NAME varchar2, DESCRIPTION varchar2), CONSTRAINT tb_pk_example_pk P Rimary KEY (ID));
Table created.

Insert a row of data
sql> INSERT INTO tb_pk_example values (1, ' Laoyang ', ' testing ');
1 row created.

Insert a row of data, the same ID, violate the primary key constraints, will error.
sql> INSERT INTO tb_pk_example values (1, ' Zhang ', ' testing ');
INSERT into tb_pk_example values (1, ' Zhang ', ' testing ')
*
ERROR at line 1:
Ora-00001:unique constraint (SYSTEM. TB_PK_EXAMPLE_PK) violated

2.4 Foreign Key
1) The foreign key of one table must be the primary key or unique key of the other table.
2) A foreign key can be null, and a foreign key value must match a value that exists in the parent table or a null value.
3) Foreign keys can be defined at the table level (single row), column level (a single column or a combination of columns).
4) The foreign key must point to the parent table's PK field or the UK field.

Create a table
Sql> CREATE TABLE Tb_supplier
2 (
3 supplier_id number NOT NULL,
4 supplier_name varchar2 () NOT NULL,
5 contact_name VARCHAR2 (50),
6 CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
7);

Table created.

Create a table
Sql> CREATE TABLE Tb_products
2 (
3 product_id number NOT NULL,
4 product_name varchar2 (100),
5 supplier_id number NOT NULL,
FOREIGN KEY constraints
//
6 constraint Fk_products_supplier foreign key (supplier_id) references Tb_supplier (supplier_id)
7);

Table created.

2.5 CHECK Constraint
A check constraint defines a condition that must be met for each row, similar to a where filter for the data when the data is inserted.
Examples are as follows:
Sql> CREATE TABLE Tb_age (age number (3));
Table created.
Age-limited range "0,125"
sql> ALTER TABLE tb_age ADD CONSTRAINT ck_temp_age CHECK ((age>0) and (age <= 125));
Table altered.
can be inserted within a given range.
sql> INSERT INTO tb_age values (33);
1 row created.

Two test values out of range-1, 126 will be an error.
sql> INSERT into tb_age values (126);
INSERT into Tb_age values (126)
*
ERROR at line 1:
Ora-02290:check constraint (SYSTEM. Ck_temp_age) violated

sql> INSERT into Tb_age values (-1);
INSERT into tb_age values (-1)
*
ERROR at line 1:
Ora-02290:check constraint (SYSTEM. Ck_temp_age) violated

3.Oracle double Quote & single Quote Difference
1. The effect of double quotes:
Keyword, object name, field name, and double quotation marks, the Oracle will be strictly case-sensitive, otherwise ORACL will default to uppercase.

2. The function of single quotation marks:
1) reference A string constant that defines the beginning and end of a string.
2) escape character, escaping the characters (single quotes) that appear immediately after.
3) means that it itself, that is, it appears in a string constant as part of a string, which is inseparable from 2.

eg

SQL> select sysdate from dual;SYSDATE------------------18-APR-16//以下则表示字符串‘sysdate‘ from dual;‘SYSDAT-------sysdate

4.group by & Count use
1), the essence of grouping is one row (or more rows) in a column (or columns) with the same value.

2), the group is non-null, if the grouping succeeds, the group contains at least one member (or row).

3), groups are unique, meaning that when you use GROUP by in a query, you do not have to use the DISTINCT keyword in the select list.

4), when the aggregate function count is used in queries against non-empty tables (including group by), it will never return 0. At least one non-0 value is returned.

Create a table
sql> CREATE table Fruits (name VARCHAR2 (10));
Table created.

Inserting data
sql> INSERT into fruits values (' oranges ');
1 row created.

sql> INSERT into fruits values (' oranges ');
1 row created.

sql> INSERT into fruits values (' oranges ');
1 row created.

sql> INSERT into fruits values (' Apple ');
1 row created.

sql> INSERT into fruits values (' Peach ');
1 row created.

sql> INSERT into fruits values (null);
1 row created.

sql> INSERT into fruits values (null);
1 row created.

sql> INSERT into fruits values (null);
1 row created.

sql> INSERT into fruits values (null);
1 row created.

sql> INSERT into fruits values (null);
1 row created.

Group Queries 1
Sql> select Name, count (name) from fruits Group by name;
Name COUNT (name)

0
Oranges 3
Apple 1
Peach 1

Group Queries 2
Sql> select Name, COUNT (*) from fruits group by name;
NAME COUNT (*)

5
Oranges 3
Apple 1
Peach 1

Group Query 1& Group Query 2 comparison shows:
COUNT (name) ignores the null row for the column, and COUNT (*) does not omit the empty value row.

6.Oracle Character Set

SQL> select userenv(‘language‘) from dual;USERENV(‘LANGUAGE‘)----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

Host queries for Oracle installations
[Email protected] rc.d]# echo $NLS _lang
American_america. Al32utf8

The Nls_lang in the Oracle environment variable is the character set that defines the client.
such as environment variable Nls_lang=american_america. Zhs16gbk
However, the character set of three instances of a database can be:
American_america. Al32utf8,
American_america. WE8ISO8859P1,
American_america. ZHS16GBK.

5. Business

A transaction transforms a database from a consistent state into another consistent state, which is the task of a transaction.

Oracle's transactions provide an acid feature:

Atomicity (atomicity): All actions in a transaction either occur or do not occur.

Consistency (consistency): A transaction transforms a database from a consistent state into the next consistent state.

Isolation (Isolation): The impact of a transaction is not visible to other transactions before the transaction commits.

Persistence (Durability): Once a transaction is committed, the result is permanent.

One of the most important features is its atomicity.

Be sure to use commit or rollback explicitly to terminate your transaction. Commit:commit will end your transaction and make all modifications that have been made permanent (persistent).

6. System date, System time operation

System date

SQL> create table tb_date(x date);Table created.SQL> insert into tb_date values(sysdate);1 row created.SQL> select * from tb_date;X------------------18-APR-16

System time

SQL> create table tb_time(stamp timestamp);Table created.SQL> insert into tb_time values(sysdate);1 row created.SQL> insert into tb_time values(systimestamp);1 row created.SQL> select * from tb_time;STAMP---------------------------------------------------------------------------18-APR-16 02.11.57.000000 PM18-APR-16 02.12.03.242952 PM

Example of interception time (year of interception):

SQL> select trunc(sysdate,‘YYYY‘) from dual;TRUNC(SYSDATE,‘YYY------------------01-JAN-16

7. Sequence

The sequence (sequence) is one of the Oracle database objects that automatically increases (or decreases) each time it is used.

Create a sequence

sql> Create sequence SEQ1
2 Increment by 1
3 Start with 1
4 MaxValue 999999
5 MinValue 1
6 cycle
7 NoCache;

Sequence created.

Nextval value of the query sequence

SQL> select seq1.nextval from dual;NEXTVAL----------1

The current currval value of the query sequence

SQL> select seq1.currval from dual;CURRVAL----------1

Nextval value of the query sequence

SQL> select seq1.nextval from dual;NEXTVAL----------2

Sql> CREATE TABLE Tb_stu (ID number,name varchar2 (40));

Table created.

Inserting a sequence as a parameter into a table
sql> INSERT into Tb_stu values (seq1.nextval, ' testtest ');

1 row created.

Query table, you can see the increment of the sequence value.
Sql> select * from Tb_stu;

ID NAME

3 Testtest

Delete data from a table
Sql> Delete from Tb_stu;

1 row deleted.

Modifying the first property type of a table
Sql> ALTER TABLE Tb_stu Modify ID char (1);

Table altered.
Inserting data into a table note: When inserted, the digital and character type Oracle automatically converts, and the sequence is used directly as a component when inserting records.

sql> INSERT into Tb_stu values (seq1.nextval, ' test2test2 ');

1 row created.

Querying data in a table (sequence values are still incremented)
Sql> select * from Tb_stu;

I NAME

4 Test2test2

Summarize:
To create a sequence syntax:

CREATE SEQUENCE Sequence_name
[INCREMENT by N]
[START with N]
[{MAXVALUE n | Nomaxvalue}] 10^27
[{MINVALUE n | Nominvalue}] -10^27
[{CYCLE | Nocycle}]
[{CACHE n | NOCACHE}];

Description
INCREMENT by: Add a few each time
Start with: From several beginnings
MAXVALUE: What is the maximum value, or Nomaxvalue does not set the maximum value
MINVALUE: What is the minimum value, or Nominvalue does not set the minimum value
Cycle: The loop after the maximum value, or the nocycle has been accumulated without cycling
Cache: Sets How many sequences are cached in memory, and if the system is down the memory sequence is lost, causing the sequence to skip the number, or set to NoCache not cached.

8. Stored Procedures

Stored procedures: A program that alters the state of a database object and can contain one or more behaviors, often by processing the records of one table and then putting them into another table.

Note: The function is very similar to the function, but different, the next section 9 will have a detailed comparison of different instructions.

Create a stored procedure

sql> CREATE OR REPLACE PROCEDURE proc_1 (num number, name VARCHAR2)
2 is
3 BEGIN INSERT INTO TB_EMP1 (empno, ename) VALUES (num, name);
4 END;
5
6/

Procedure created.

Execute the stored procedure (the Last "/" represents execution, as described in article 1. Oracle PL/SQL uses partial descriptions).
Sql> BEGIN proc_1 (one, ' laoyang11 ');
2 END;
3/

PL/SQL procedure successfully completed.

View execution Results

SQL> select * from tb_emp1;EMPNO----------ENAME--------------------------------------------------------------------------------JOB--------------------------------------------------------------------------------11zhangengineer11laoyang11EMPNO----------ENAME--------------------------------------------------------------------------------JOB

Summarize:

Syntax format for creating stored procedures:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(Parameter_name [In | Out | In out] type[,....])
{is | As}
[LOCAL declarations]
BEGIN
Executable statements;
[EXCEPTION
exception_statements;
END procedure_name;

9. Functions
The function is to compute a function that is often used to calculate and return a computed result.
Create a function (with parameters).
Returns data of type number.
sql> CREATE OR REPLACE FUNCTION sal_tax
2 (v_sal number)
3 Return number
4 is
5 begin
6 if (V_sal <) Then
7 return 0.10;
8 elsif (V_sal < 2750) Then
9 return 0.15;
Ten Else
return 0.20;
The End If;
The end;
14/

Function created.

Inquire

SQL> select sal_tax(2000) from dual;SAL_TAX(2000)-------------          .15SQL> select sal_tax(5555) from dual;SAL_TAX(5555)-------------           .2

There are no parameters in the function, you can do without ().
The function generally does not use Dbms_output.put_line to print information, because the function is used to calculate and return a result of the calculation, of course, printing is ok (also verified after testing: will not print)
sql> CREATE OR REPLACE FUNCTION Hello RETURN VARCHAR2 is
2 var VARCHAR2 (+): = ' Hello function ';
3 BEGIN
4–dbms_output.put_line (' aaaaaaaaaaa ');
5 return var;
6 END;
7/

Function created.

SQL> select hello from dual;HELLO--------------------------------------------------------------------------------hello function

Summarize:
Basic syntax format for a function:
CREATE [OR REPLACE] FUNCTION function_name
[(Parameter_name [In | Out | In out] type[,....])
RETURN TYPE
{is | As}
BEGIN
FUNCTION BODY
END function_name;

key ———— The difference between a function and a stored procedure:
1) functions and procedures are similar, the difference is that the function must return a value, and the stored procedure is only to perform a series of operations.

2) at the time of invocation, the function can be called as part of an expression and can be used in select. A stored procedure can only be called as a PL/SQL statement and cannot be used in a SELECT statement.

3) The syntax structure of a function is similar to a stored procedure, except that functions are defined using function. An important feature of the outside is that the function has a return clause that specifies the return type of the function.

4) Both functions and stored procedures can use out parameters to return content.

2016-4-17 am0:30
Ming Yi World
Reprint please indicate source, original address: http://blog.csdn.net/laoyang360/article/details/51189292
If you feel this article is helpful, please click on the ' top ' support, your support is I insist on writing the most power, thank you!

Oracle Advanced Practical Notes