Oracle Basic Grammar Collection

Oracle Basic Grammar Collection

1. Table

CREATE TABLE test (names VARCHAR2 (12), Dates date, Num INT, Dou double);

2. View

Create or replace view vi_test as SELECT * from Test;

3. Synonyms

Create or replace synonym AA For DBUSRCARD001.AA;

4. Stored Procedures

Create or replace produce DD (v_id in Employee.empoy_id%type) As Begin End dd

5. Functions

Create or Replace function ee (v_id in Employee%rowtype) return varchar (15) Is Var_test VARCHAR2 (15); Begin return var_test; Exception when others then End

6, three types of trigger definitions

Create or replace trigger FF Alter Delete On test For each row Declare Begin Delete from test; If Sql%rowcount < 0 or sql%rowcount is null then Rais_replaction_err (-20004, "Error") End If End Create or replace trigger GG Alter insert On test For each row Declare Begin If:old.names =: New.names Then Raise_replaction_err (-2003, "Code Duplication"); End If End Create or replace trigger HH For update On test For each row Declare Begin If updating then If:old.names <>: New.names Then Reaise_replaction_err (-2002, "keyword cannot be modified") End If End If End

7. Defining Cursors

Declare Cursor AA is Select Names,num from Test; Begin for BB in AA Loop If bb.names = "ORACLE" Then End If End Loop; End

8, speed optimization, the previous statement does not have a statement after the speed of dozens of times times faster

Select Names,dates From Test,b where test.names = B.names (+) and B.names is null and B.dates > Date (' 2003-01-01 ', ' yyyy-mm-dd ') Select Names,dates From Test Where names not in (select names From b where dates > to_date (' 2003-01-01 ', ' yyyy-mm-dd '))

9. Find Duplicate Records

Select Names,num From Test where ROWID! = (select Max (ROWID) From Test b where b.names = Test.names and B.num = test.num)

Find table TEST time the latest top ten Records

SELECT * FROM (SELECT * from Test ORDER BY dates DESC) where RowNum < 11

generation of serial numbers

Create sequence row_id MinValue 1 MaxValue 9999999999999999999999 Start with 1 Increment by 1 INSERT into test values (Row_id.nextval,....)

1. connect

--- inner connection
select * from dali.test1 a, dali.test2 b where a.a=b.a;

--- Left connection
select * from dali.test1 a,  DALI.TEST2 B WHERE A.A=B.A (+);

--- Right connection
select * from dali.test1 a,  DALI.TEST2 B WHERE A.A (+) =B.A;

--- full connection
select * from dali.test1 a,  DALI.TEST2 B WHERE A.A=B.A (+)
Union
select * from dali.test1 a,  DALI.TEST2 B WHERE A.A (+) =B.A;

--- Cartesian
select * from dali.test1,  Dali.test2;

To determine if it is empty:
In SQL Server for ISNULL (field1,0)
for NVL (field1,0) in Oracle

basic syntax for Oracle stored procedures

1. Basic Structure
CREATE OR REPLACE PROCEDURE stored procedure name
(
Parameter 1 in number,
Parameter 2 in number
) is
Variable 1 INTEGER: =0;
Variable 2 DATE;
BEGIN

ENDStored Procedure name

2.SELECT into STATEMENT
WillSelectThe result of the query is stored in a variable, you can store multiple columns in multiple variables at the same time, there must be a
Record, or throw an exception(If no record is thrownno_data_found)
Example:
BEGIN
SELECT Col1,col2 intovariables1,variables2 from typestruct where xxx;
EXCEPTION
When No_data_found Then
xxxx
END;
...

3.IFJudging
IF V_test=1 Then
BEGIN
Do something
END;
END IF;

4.whileLoops
While V_test=1 LOOP
BEGIN
Xxxx
END;
END LOOP;

5.assigning values to variablesV_test: = 123;

6.with for inUsecursor
...
Is
CURSOR cur is SELECT * from xxx;
BEGIN
For Cur_result in cur LOOP
BEGIN
V_sum: =cur_result.Column Name1+cur_result.Column Name2
END;
END LOOP;
END;

7.with the parametercursor
CURSOR C_user (c_id number) is the SELECT NAME from USER WHERE typeid=c_id;
OPEN C_user (Variable Value);
LOOP
FETCH C_user into V_name;
EXIT FETCH C_user%notfound;
Do something
END LOOP;
CLOSE C_user;

8.withPL/SQL Developer DebugAfter connecting to the database, create aTest WINDOW
In the window input callSPthe Code, F9StartDebug,ctrl+nsingle-Step debugging

Some questions about Oracle stored Procedures Memo

1. in Oracle , data table aliases cannot be added As, for example:

Select A.appname from AppInfo a;--correct
Select A.appname from appinfo as a;--error

Maybe it's a matter of being afraid to conflict with the keyword as in a stored procedure in Oracle .

2. In a stored procedure, when a field isselect , it must be followed by into, if the select entire record, Using cursors is a matter of other things.

 select af.keynode into kn from appfoundation af where  af.appid=aid and af.foundationid=fid;--  has into Span style= "font-family: the song Body;" >, compile the
 select af.keynode from appfoundation af where af.appid= correctly aid and af.foundationid=fid;--  no , compile error, prompt: COMPILATION 
 error: pls-00428:  an into clause is expected in this select statement

3. in the use of select...into ... syntax, you must first make sure that the record is in the database, or you will report "No data Found" exception.

Before this syntax, you can use SELECT COUNT (*) from to see if the record exists in the database, and if so, then use the select...into ...

4. In the stored procedure, the alias cannot be the same as the field name, or although the compilation can pass, but at run stage will be error

Select Keynode to kn from appfoundation where Appid=aid and foundationid=fid;--run correctly
Select Af.keynode to kn from Appfoundation af where af.appid=appid and af.foundationid=foundationid;--run stage error, hint
Ora-01422:exact fetch returns more than requested number of rows

5. in the stored procedure, there is a problem with null

Suppose there is a table a, which is defined as follows:

CREATE TABLE A (
ID VARCHAR2 (primary) key NOT NULL,
Vcount Number (8) is not NULL,
Bid varchar2 () NOT null--foreign key
);

If you are in a stored procedure, use the following statement:

Select SUM (vcount) into Fcount from A where bid= ' xxxxxx ';

If a record of bid= "xxxxxx" does not exist in table A , then fcount=null ( even if fcount A default value is set when defined, such as: fcount Number (8): =0 is still invalid, Fcount or will it become null) , so that later use Fcount There may be a problem, so it's best to judge here first:

If Fcount is null then
fcount:=0;
End If;

That's all OK .

6.Hibernate calling oracle stored Procedures

This.pnumberManager.getHibernateTemplate (). Execute (
New Hibernatecallback () ... {
Public Object Doinhibernate (session session)
Throws Hibernateexception, SQLException ... {
CallableStatement cs = Session
. Connection ()
. Preparecall ("{Call Modifyapppnumber_remain (?)}");
Cs.setstring (1, Foundationid);
Cs.execute ();
return null;
}
});