oracle--basic Knowledge (II.)

1. Select query

A single SELECT statement

Retrieving data from a database through select in PL/sql:

Grammar:

<1> must use the INTO clause

<2> query must and can only return one row

<3> can use the full select syntax

Using the Select command, you can remove a single row of data from the database
To modify rows in a database by using the DML command
Controlling transactions with commit or rollback commands
Executing the DDL through execute IMMEDIATE

1. View field type

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

2, 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;

3, EXECUTE IMMEDIATE. Parse and immediately execute a dynamic SQL statement or a PL/SQL block that is not created at run time.

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.
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.

• Stored Procedures

The subroutine can be compiled independently and stored in the Oracle database. A subroutine that is explicitly created by using the CREATE statement is a "storage" sub-program. Once compiled and saved into a data dictionary, it becomes a schema object (schema objects) that can be called by many applications that connect to the database.

The stored subroutine defined in the package is called a bun program (packaged subprogram); A separately defined storage subroutine is called an Independent subroutine (standalone subprogram), while in another subroutine or pl/ A stored subroutine defined within a SQL block is called a local subroutine, and such a subroutine cannot be called by another application for local use only.

oracle--basic Knowledge (II.)