Oracle Study Notes

(1) how to read the values in SEQNAME. NEXTVAL
　　
You can directly insert into test values (SEQNAME. NEXTVAL) as follows:
　　
SELECT tmpdata_seq.NEXTVAL
INTO id_temp
From dual; then id_temp can be used
　　
(2) PLS-00103: The symbol ">" in the need of the following:
The Code is as follows:
　　
IF (sum> 0)
THEN
Begin
Insert into emesp. tp_sn_production_log
VALUES (r_serial_number, id_temp );
　　
EXIT;
End;
　　
Sum> 0 is always reported. This is a very depressing problem. Because sum is used for the variable, it cannot be used. Then it is changed to I _sum> 0.
　　
(3) oracle syntax
　　
1. Overview of Oracle Application Editing Methods
A: 1) Pro * C/C ++/...: the method of dealing with databases in C language, which is more common than OCI;
2) ODBC
3) OCI: the method of dealing with databases in C language. It is similar to ProC, which is lower-level and rarely used;
4) SQLJ: a new method to access the Oracle database using Java. There are not many users;
5) JDBC
6) PL/SQL: stores and runs in data. Other methods are used to access the database outside the database;
　　
2. PL/SQL
Answer: 1) PL/SQL (Procedual language/SQL) is a procedural processing language based on standard SQL;
2) The operating language for Oracle client tools to access the Oracle server;
3) Oracle SQL expansion;
　　
4. Advantages and Disadvantages of PL/SQL
Answer: advantages:
1) Structured Modular programming, not object-oriented;
2) Good portability (regardless of the operating system in which Oracle runs );
3) good maintainability (stored in the database after compilation );
4) improve system performance;
　　
　　
Chapter 2
　　
PL/SQL program structure
　　
1. PL/SQL Block
Answer: 1) Statement, DECLARE (indispensable );
2) execution part, BEGIN... END;
3) EXCEPTION handling, EXCEPTION (may not );
　　
2. PL/SQL Development Environment
A: You can use any plain text editor, such as VI. toad is very useful.
　　
3. PL/SQL Character Set
Answer: PL/SQL is case insensitive.
　　
4. identifier naming rules
Answer: 1) It must start with a letter;
2) followed by any non-space characters, numbers, currency symbols, underscores, or #;
3) the maximum length is 30 characters (about 8 characters );
　　
5. Variable Declaration
Answer: syntax
Var_name type [CONSTANT] [not null] [: = value];
Note: 1) the statement can have a default value or not;
2) If [CONSTANT] [not null] exists, the variable must have an initial value;
3) The value assignment statement is ": = ";
4) variables can be considered as a field in the database;
5) it is specified that the variable not initialized is NULL;
　　
Chapter 3
　　
1. Data Type
Answer: 1) scalar type: numeric, numeric, Boolean, and date type;
2) combination: RECORD (commonly used), TABLE (commonly used), and VARRAY (rarely used)
3) reference type: ref cursor (CURSOR) and REF object_type
4) LOB (Large Object)
　　
2.% TYPE
A: The variable has the same type as a field in the database table.
Example: v_FirstName studengts. first_name % TYPE;
　　
3. RECORD type
A: TYPE record_name is record (/* Where TYPE, IS, record is the keyword, record_name IS the variable name */
Field1 type [not null] [: = expr1],/* use commas to separate equivalent members */
Field2 type [not null] [: = expr2],/* If a field is not null, it must have an initial value */
.../* All fields not initialized will be initially NULL
Fieldn type [not null] [: = exprn]);
　　
4.% ROWTYPE
A: A database-defined type is returned.
DECLARE
V_StuRec Student % ROWTYPE;/* Student indicates the table name */
　　
Note: Compared with setting a record in 3, one step is completed, while 3 defines two steps: a. All member variables must be affirmed; B. instantiate variables;
　　
5. TABLE type
A: TYPE tabletype is table of type index by BINARY_INTEGER;
Example: DECLARE
TYPE t_StuTable is table of Student % rowtype index by BINARY_INTERGER;
V_Student t_StuTable;
BEGIN
SELECT * INTO v_Student (100) FROM Student WHERE id = 1001;
END;
Note: 1) the limit on the number of rows is determined by the range of BINARY_INTEGER;
　　
6. Scope and visibility of Variables
Answer: 1) the execution block can be embedded into the execution block;
2) the variable of the execution block in the layer is invisible to the external layer;
3) Modifying block variables at the internal layer will affect the value of block variables at the external layer;
　　
Chapter 4
　　
1. Condition statements
A: IF boolean_expression1 THEN
...
ELSIF boolean_expression2 THEN/* Note that ELSIF is not ELSEIF */
.../* The ELSE statement is not required, but the end if; statement is required */
ELSE
...
End if;
　　
2. Loop statements
Answer: 1) Loop
...
IF boolean_expr THEN /**/
EXIT;/* exit when boolean_expr */
End if ;/**/
End loop;
2) WHILE boolean_expr LOOP
...
End loop;
3) FOR loop_counter IN [REVERSE] low_blound... high_bound LOOP
...
End loop;
Note: a. When REVERSE is added, it indicates a decrease. The step from the end boundary to the start boundary is one;
B. low_blound start boundary; high_bound end boundary;
　　
3. GOTO statement
A: GOTO label_name;
1) only internal blocks can be jumped to external blocks;
2) Set tags: < >
3) Example:
LOOP
...
If d % ROWCOUNT = 50 THEN
GOTO l_close;
End if;
...
End loop;
< >;
...
　　
4. NULL statement
A: add a statement in the statement block to supplement the statement integrity. Example:
IF boolean_expr THEN
...
ELSE
NULL;
End if;
　　
5. SQL in PL/SQL
Answer: 1) only dml SQL can be used directly in PL/SQL;
　　
Chapter 5
　　
1. CURSOR)
Answer: 1) Role: used to extract multi-row datasets;
2) Statement: a. General statement: delcare cursor CURSOR_NAME IS select_statement/* the content of CURSOR must be a query statement */
B. With parameter Declaration: DELCARE CURSOR c_stu (p_id student. ID % TYPE) SELECT * FROM student WHERE ID = p_id;
3) open cursor: OPEN Cursor_name;/* is equivalent to executing the select statement and saving the execution result to CURSOR;
4) number retrieved from the cursor: a. FETCH cursor_name INTO var1, var2,...;/* The number, type, and sequence of the variables must be consistent with the fields in the Table ;*/
B. FETCH cursor_name INTO record_var;
Note: extract the value from the CURSOR and put it into the variable. One record is obtained every FETCH;
5) CLOSE the cursor: CLOSE Cursor_name;
Note: a. The cursor should be closed after use;
B. The closed cursor cannot be FETCH or closed again;
C. Closing the CURSOR is equivalent to clearing the contents of the CURSOR in the memory;
　　
2. cursor attributes
Answer: 1) % FOUND: whether there is a value;
2) % NOTFOUND: whether there is no value;
3) % ISOPEN: whether it is open;
4) % ROWCOUNT: current record number of CURSOR;
　　
3. The FETCH loop of the cursor
Answer: 1) LOOP
FETCH cursor...
Exit when cursor % NOTFOUND;/* EXIT after the cursor is not recorded */
End loop;
2) WHILE cursor % FOUND LOOP
FETCH cursor...
End loop;
3) FOR var IN cursor LOOP
FETCH cursor...
End loop;
　　
Chapter 6
　　
1. Exception
A: DECLARE
...
E_TooManyStudents EXCEPTION;/* declares an EXCEPTION */
...
BEGIN
...
RAISE e_TooManyStudents;/* trigger exception */
...
EXCEPTION
WHEN e_TooManyStudents THEN/* trigger exception */
...
When others then/* handle all other exceptions */
...
END;
　　
Wednesday
　　
PL/SQL database programming (II)
　　
1. Stored PROCEDURE (PROCEDURE)
A: creation process:
CREATE [or replace] PROCEDURE proc_name
[(Arg_name [{IN | OUT | in out}] TYPE,
Arg_name [{IN | OUT | in out}] TYPE)]
{IS |}
Procedure_body
1) IN: indicates that the parameter cannot be assigned a value (only on the right of the equal sign );
2) OUT: indicates that this parameter can only be assigned a value (only on the left of the equal sign );
3) in out: indicates that this type can be assigned values or passed values;
　　
2. Stored Procedure example
A: create or replace procedure ModeTest (
P_InParm in number,
P_OutParm out number,
P_InOutParm in out number)
IS
V_LocalVar NUMBER;/* declaration part */
BEGIN
V_LocalVar: = p_InParm;/* execution part */
P_OutParm: = 7;
P_InOutParm: = 7;
...
EXCEPTION
.../* Exception Handling part */
END ModeTest;
　　
3. Example of calling PROCEDURE
Answer: 1) Anonymous blocks can be called;
2) Other PROCDEURE can be called;
Example:
DECLARE
V_var1 NUMBER;
BEGIN
ModeTest (12, v_var1, 10 );
END;
Note: v_var1 is 7 at this time.
　　
4. Specify the real parameter Mode
Answer: 1) Location Identification Method: All parameters are added during the call, and the real participation parameters are matched in order;
2) name marking method: the name of the form parameter is given during the call and the real parameter is given.
ModeTest (p_InParm => 12, p_OutParm => v_var1, p_Inout => 10 );
Note: a. The two methods can be mixed;
B. When mixing, the first parameter must be specified by location.
　　
5. Differences between functions and Procedure
A: 1) the process call itself is a PL/SQL statement (which can be directly called through the exec statement in the command line );
2) function call is part of an expression;
　　
6. function declaration
A: CREATE [or replace] PROCEDURE proc_name
[(Arg_name [{IN | OUT | in out}] TYPE,
Arg_name [{IN | OUT | in out}] TYPE)]
RETURN TYPE
{IS |}
Procedure_body
Note: 1) a function that does not return a statement will be an error;
　　
7. Deletion process and Function
A: drop procedure proc_name;
Drop function func_name;
　　
Chapter 8
　　
1. Package
Answer: 1) The package is a PL/SQL structure that stores related objects;
2) packages can only be stored in the database, not local;
3) A package is a declaration with a name;
4) it is equivalent to the declaration part of a PL/SQL block;
5) anything that appears in the block declaration part can appear in the package;
6) The package can contain procedures, functions, cursors, and variables;
7) packages can be referenced from other PL/SQL blocks. The packages provide global variables that can be used for PL/SQL.
8) the packet has a packet header and a packet body. If there is no function or process in the packet header, the packet body does not need it.
　　
2. Baotou
A: 1) the header contains information about the content of the package. The header does not contain any process code.
2) Syntax:
CREATE [or replace] PACKAGE pack_name {IS |}
Procedure_specification | function_specification |
　　
Variable_declaration | type_definition | prediction_declaration |
　　
Cursor_declaration
END pack_name;
3) Example:
Create or replace package pak_test
PROCEDURE RemoveStudent (p_StuID IN students. id % TYPE );
TYPE t_StuIDTable is table of students. id % type index by BINARY_INTEGER;
END pak_test;
　　
3. Package subject
Answer: 1) The package subject is optional. If the packet header does not contain any functions or procedures, the package subject is not required.
2) the subject and header of the package are stored in different data dictionaries.
3) if the packet header fails to be compiled, the package subject cannot be correctly compiled.
4) The package body contains the code of all processes and functions declared in the header.
5) Example:
Create or replace package body pak_test
PROCEDURE RemoveStudent (p_StuID IN students. id % TYPE) IS
BEGIN
...
END RemoveStudent;
TYPE t_StuIDTable is table of students. id % type index by BINARY_INTEGER;
END pak_test;
　　
4. Package Scope
Answer: 1) when calling the package outside the package (the package name must be added): pak_test.AddStudent (100010, 'cs ', 101 );
2) the objects and processes declared in the header can be directly used in the package owner (the package name is not required );
　　
5. overload of package neutron programs
Answer: 1) the process and function in the same package can be reloaded;
2) the same process or function name, but different parameters;
　　
6. Package Initialization
Answer: 1) The package is stored in the database;
2) when the package is called for the first time, it is transferred to the memory from the database and initialized;
3) all variables defined in the package are allocated with memory;
4) Each session will have a copy of its own package variables.
　　
Chapter 9
　　
1. Trigger
Answer: 1) similarities between triggers and processes/functions
A. All execution blocks with names;
B. All declarations, execution bodies, and exceptions;
2) differences between triggers and processes/functions
A. The trigger must be stored in the database;
B. The trigger is automatically executed;
　　
2. Create a trigger
Answer: 1) Syntax:
CREATE [or replace] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event ON table_reference
[For each row [WHEN trigger_condition]
Trigger_body;
2) Example:
Create or replace trigger UpdateMajorStats after insert or delete or update on students
DECLARE
CURSOR c_Statistics IS
SELECT * FROM students group by major;
BEGIN
...
END Up;
　　
3. Trigger
Answer: 1) three statements (INSERT/UPDATE/DELETE );
2) two types (before/after );
3) two levels (row-level/statement-level );
So a total of 3X2X2 = 12
　　
4. Trigger restrictions
A: 1) transaction control statements should not be used;
2) No LONG or long raw variables can be declared;
3) tables that can be accessed are limited.
　　
5. Tables accessible to the trigger body
A: 1) You cannot read or modify any change table (the table being modified by the DML Statement );
2) You cannot read or modify the primary keys, unique values, and foreign key columns of a restricted table (tables with constraints.
　　
(4) ORA-01000 using Oracle in Java Development
　　
Many friends in Java Development, when using Oracle database, often encounter a ORA-01000: maximum open cursors exceeded. Error.
In fact, the cause of this error is mainly caused by code problems.
Ora-01000: maximum open cursors exceeded.
Indicates that the maximum number of opened cursors has been reached for a process.
The main cause of this error is that the Java code is executing conn. createStatement () and conn. when prepareStatement () is used, it is equivalent to opening a cursor in the database. In particular, if your createStatement and prepareStatement are in a loop, this issue is very likely to occur. Because the cursor is always open and not closed.
In general, when writing Java code, both createStatement and prepareStatement should be placed outside the loop and closed in time after these Statment are used. It is best to disable Statment immediately after executing executeQuery, executeUpdate, and so on without using the ResultSet data.
For the case of ORA-01000 errors, simply increase open_cursors is not a good way, it is just a temporary cure. In fact, the hidden risks in the Code have not been removed.
In most cases, open_cursors only needs to set a small value, which is enough unless there are special requirements.
　　
　　
　　
(5) execute DDL statements in store procedure
First: execute immediate 'update' | table_chan | 'set' | column_changed | '= ''' | v_trans_name | ''' where empid = ''' | v_empid | '''';
　　
Second, The DBMS_ SQL package can be used to execute DDL statements directly from PL/SQL.
　　
This is an example of creating a table. This process has two parameters: Table Name and field and its type list.
　　
Create or replace procedure ddlproc (tablename varchar2, cols varchar2)
Cursor1 INTEGER;
BEGIN
Cursor1: = dbms_ SQL .open_cursor;
Dbms_ SQL .parse (cursor1, 'create table' | tablename | '(' | cols | ')', dbms_ SQL .v7 );
Dbms_ SQL .close_cursor (cursor1 );
End;
/
　　
2. How do I find the name of the primary key field of the database table?
SQL> SELECT * FROM user_constraints
WHERE CONSTRAINT_TYPE = 'p' and table_name = 'table _ name ';
3. How can I query the number of tables in the database?
SQL> select * from all_tables;
　　
4. Use the SQL unified character
　　
Wildcard Description Example % any string containing zero or more characters. WHERE title LIKE '% computer %' searches for all titles containing the word computer in any location of the title. _ (Underline) any single character. WHERE au_fname LIKE '_ ean' searches for the names of all four letters ending with ean (such as Dean and Sean ). [] Any single character in the specified range ([a-f]) or set ([abcdef. WHERE au_lname LIKE '[C-P] arsen' will look for the author's surname that ends with arsen and starts with any single character between C and P, such as Carsen, Larsen, Karsen, and so on. [^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef. WHERE au_lname LIKE 'de [^ l] %' searches for the last names of all authors whose names start with de and whose subsequent letters are not l.
　　
5. Grant normal users the permission to view v $ Session
　　
GRANT SELECT
ON "SYS". "V _ $ OPEN_CURSOR" TO "SFISM4 ";
GRANT SELECT
ON "SYS". "V _ $ SESSION" TO "SFISM4 ";
　　
Common functions
　　
Distinct
Remove duplicate
Minus Subtraction
In the first table but not in the second table
SELECT * from football minus select * from softball;
Intersect Intersection
INTERSECT returns the rows in the two tables.
SELECT * from footbal;
Union all is the same as UNION, but it does not remove duplicate records.
Aggregate functions
Count
Select count (*) from test;
SUM
SUM returns the SUM of all values in a column just as it intended.
Select sum (SINGLES) TOTAL_SINGLES from test;
SUM can only process numbers. If it is not a number, you will receive the following message:
Input/Output
SQL> SELECT SUM (NAME) FROM TEAMSTATS;
ERROR
ORA-01722 invalid number
No rows selected
This error message is reasonable because the NAME field cannot be summarized.
AVG
AVG returns the average value of a column.
Select avg (SO) AVE_STRIKE_OUTS from teamstats;
MAX
If you want to know the maximum value in a column, use MAX.
Select max (HITS) from teamstats;
MIN
MIN is similar to MAX. It returns the smallest value in a column.
VARIANCE
The VARIANCE is not defined in the standard, but is a crucial value in the field of statistics.
Select variance (HITS) from teamstats;
STDDEV
This is the standard deviation of the value returned by the last statistical function STDDEV.
Select stddev hits from teamstats;
Date and Time Functions
ADD_MONTHS
ADD_MONTHS can also work outside select
The function adds a month to a given date. For some special reasons, the above plan needs to be postponed for two months.
LAST_DAY
LAST_DAY returns the last day of the specified month.
MONTHS_BETWEEN
If you want to know the number of months in the given two dates, you can use MONTHS_BETWEEN.
Select task, startdate, enddate, months between (Startdate, enddate) duration from project;
The returned result may be a negative value.
Negative values can be used to determine whether a certain date is before another date. The following example will show all the competitions that started before January 1, May 19, 1995.
SELECT * FROM PROJECT
WHERE MONTHS_BETWEEN ('19-MAY-95 ', STARTDATE)> 0;
NEW_TIME
If you want to adjust the time to your time zone, you can use NEW_TIME.
SQL> SELECT ENDDATE EDT, NEW_TIME ('enddate', 'edt', 'pdt ') FROM PROJECT;
NEXT_DAY
NEXT_DAY will return the required Sunday in the same week or one week later as the specified date.
If you want to know the number of Friday of the specified date, you can do this.
SQL> SELECT STARTDATE, NEXT_DAY (STARTDATE, 'Friday') FROM PROJECT;
SYSDATE
SYSDATE returns the system date and time.
Select distinct sysdate from project;
Mathematical functions
ABS
The ABS function returns the absolute value of a given number.
CEIL and FLOOR
CEIL returns the smallest integer equal to or equal to the given parameter. FLOOR, which returns
The maximum integer equal to or smaller than the given parameter.
COS COSH SIN SINH TAN TANH
Cos sin tan function can return the default trigonometric function value of a given parameter. The parameter is regarded as radian.
EXP
EXP returns the power of base number e with the given parameter as the exponent.
LN and LOG
These are two logarithm functions. LN returns the natural logarithm of the given parameter.
MOD
It is known in ANSI that the modulo operation notation % is replaced by the function MOD in some interpreters.
POWER
This function returns the power of one number to another. When using the power function, the first parameter is the base number, and the second is the index.
SIGN
If the value of the parameter is negative, SIGN returns-1. If the value of the parameter is positive, SIGN returns 1. If the parameter is zero, SIGN returns zero.
SQRT
This function returns the square root of the parameter. Since a negative number cannot be square, we cannot apply this function to a negative number.
Character Functions
CHR
The characters returned by this function and the given numeric parameters depend on the character set on which the database depends.
CONCAT
And | connects two strings.
INITCAP
This function converts the first letter of the parameter to uppercase, and other letters to lowercase.
LOWER and UPPER
As expected, LOWER converts the parameter to all lowercase letters, while UPPER converts all the parameters to uppercase letters.
LPAD and RPAD
These two functions require at least two parameters and at most three parameters. Each parameter is a string to be processed.
The second parameter is the string extended width. The third parameter indicates the characters used to fill the widening part.
The default value of the third parameter is space, but it can also be a single character or string.
LTRIM and RTRIM
LTRIM and RTRIM must have at least one parameter. A maximum of two parameters can be set:
It is similar to a string. The second parameter is also a character or string. By default, it is a space.
If the number is not a space, the function will cut the specified character as the space.
REPLACE
This function requires three parameters. The first parameter needs to be searched.
The second parameter is the content of the search. The third parameter is the string to be replaced.
If the parameter is omitted or NULL, only the search operation will be executed and NO content will be replaced.
SUBSTR
This function has three parameters that allow you to output a portion of the target string. The first parameter is the target string,
The second string is the starting point of the substring to be output, and the third parameter is the length of the substring to be output.
If the second parameter is a negative number, the system locates forward from the end of the source string to the absolute value of the negative number.
TRANSLATE
This function has three parameters: the target string, the source string, and the target string.
The characters in the string will be replaced with the corresponding characters in the target string.
INSTR
If you need to know where a string meets specific content, you can use INSTR, its first Parameter
The number is the target string, the second parameter is the Matching content, and the third and fourth parameters are numbers used to specify the start search
The start point of the cable and the first few matching conditions will be returned.
LENGTH
LENGTH returns the LENGTH of the specified string.
Conversion functions
There are three conversion functions that allow you to easily convert data from one type to another.
TO_CHAR
The initial function of this function is to convert a number to the numeric type. Different interpreters may use it to convert other
For example, convert a data type to a date type or have more parameters.
TO_NUMBER
This function corresponds to the TO_CHAR function, which converts a string number to a numeric value.
Other functions
GREATEST and LEAST
These two functions return the largest and least of several expressions.
Select greatest ('alpha', 'Bravo ', 'foxtrot', 'delta') from convert;
It seems necessary to use the FROM clause. However, if the FROM clause does not exist, you will receive an error message
A select statement requires the FROM clause.
USER
This function returns the name of the user currently using the database.