oracle--Basic Knowledge

I. Data types in Oracle

1, String type. such as: char, nchar, VARCHAR2, nvarchar2.
2, numeric type. such as: int, number (p,s), Integer, smallint.
3. Type of date. such as: date, interval, timestamp.
4. PL/SQL type. such as: Pls_integer, Binary_integer, binary_double (10g), binary_float (10g), Boolean. Plsql types are not available in SQL environments, such as when building tables.
5, custom type. Use the type definition; a type is like a compound variable: The individual understands that the type is an array, but his return value has only one value. And you need to assign a value

Select To_char (sysdate, ' Yy-mm-dd hh24:mi:ss ') from dual//display: 08-11-07 13:22:42

Select To_date (' 2005-12-25,13:25:59 ', ' Yyyy-mm-dd,hh24:mi:ss ') from dual//display: 2005-12-25 13:25:59

And if the writing: Select To_date (' 2005-12-25,13:25:59 ', ' yyyy-mm-dd,hh:mi:ss ') from the dual, will be an error, because the hour HH is 12 binary, 13 is illegal input, can not match.

Ii. PL/SQL statement block

is a block structure, divided into: the declaration part, the execution part, the exception processing Part three parts. As follows

The Declare declaration section--between Declare and begin--is between Begin and Exception; Exception exception between Begin and End when no Exception is performed　　Section--Located between Exception and end; --Must add a semicolon

1. DECLARE variables:

Variable name [CONSTANT] Variable type [[Not NULL]: =|　　DEFAULT expression]; Unlike SQL Server, the variable name does not need to be @

Note:

• CONSTANT: constant;
• Not NULL: constraint, if any, initialization must be assigned value;
• : = assignment symbol; Directly assigns the default value.

Cases:　

Declare
A varchar2 (20): = ' 12 ';
sqltable varchar (200);
SQLText varchar (1000);

2. Executive Section

For logical operations and queries, etc., commonly used statements: Dbms_output.put_line (' 1 '); -Similar to print in SQL Server.

3. Exception Handling

Three, the basic operation

1.

2. View field type

SELECT * from All_tab_columns where table_name=upper (' table name ');

3, is and as

There is no difference between stored procedures and functions, and only as is not available in the view; There is no difference from its definition.

create [or replace] procedure procedure_name
[(Parameter_name [in | out | on out] type [,........])]
{is | as}
Begin
Procedure_body
End procedure_name;

5, EXECUTE IMMEDIATE. Instead of the previous oracle8i dbms_sql package, it resolves and immediately executes dynamic SQL statements or PL/SQL blocks that are not created at runtime.

Tips for use:
1. Execute immediate will not commit a DML transaction execution and should be explicitly committed

Immediate part of himself. If the DDL command is processed through execute immediate, it submits all previously changed data
2. Queries that return multiple rows are not supported, and the interaction uses temporary tables to store records (see examples below) or ref cursors.
3. When executing the SQL statement, do not use a semicolon when executing the PL-SQL block with a semicolon at its tail;
4. Example: Execute immediate ' select COUNT (1) from Tempa '.

Question: How do I get a procedure to return a dataset?

Reply:

There are two key points: one is to use an out type parameter. The second is that the parameter uses a special data type: The system refers to the cursor type (sys_refcursor).

A procedure returns a dataset that can be received by another block or procedure in a PL/SQL program;

Question: What is the difference between the cursor type and the Sys_refcursor type?

Reply:

The cursor is a cursor type that represents the dataset itself and can only be closed in a PL/SQL program, either through a cursor for loop or by defining the cursor.

Instead, Sys_refcursor represents a reference to the cursor, which is the reference address of the dataset, which can only be done by using the open for statement.

The biggest difference between the two is that the latter can pass this address to other programs.

Problem: It is said that the "REF CURSOR type" can also return a dataset, what does it have to do with sys_refcursor?

Answer: REF CURSOR is the data type used in older versions of Oracle, the disadvantage of which is that it must be defined in the package, and now with the Ys_refcursor type, it can be used directly.

oracle--Basic Knowledge