Oracle PL/SQL Learning Basics (1)

1, PL/SQL, full name procedure Language/sql, process-language

The program structure of PL/SQL

Declare

--declarations section (including variables, cursors, exception declarations)

Begin

--Statement sequence (DML statement)

exception

--Exception handling statements

End

/

2. Declaration part

(1) Define basic variables, type: Char,varchar2,date,number,boolean,long

(2) How to assign a variable:: =,into keyword

(3) Reference type variable, record type variable

Reference type variable: Emp_ename emp.ename%type;

Record type variable: Emp_rec emp%rowtype;

SELECT * into Emp_rec from EMP where eno=7839;

Dbms_ouput.put_line (emp_rec.ename| | ' ' | | Emp_rec.sal);--equivalent to an EMP alias.

3. Program Body part

(1) Branch statements

If condition Then

Statement sequence 1;

elsif

Statement sequence 2;

Else

Statement sequence 3;

End If;

(2) Circulation body

1) Form 1

 While I<=  loop  i:=I+1; end Loop;

2) Form 2

Loop Exit  when condition; end Loop;

3) Form 3

 for inch 1.. Ten loop   statement sequence; end Loop;

4, cursor/cursor, is actually the result set

Basic format: cursor name [(parameter name data type [, parameter name data type]) ...]

is SELECT statement;

Properties:%found,%notfound--is there a record

%isopen--Determine if the cursor is open

%rowcount--Number of rows affected

Number of cursors limit:

Connecting the SYS user in the Sqlplus

Conn Sys/[email PROTECTED]:1521/ORCL as Sysdba

To view configuration parameters:

Show parameter cursor

To modify the settings for the number of cursors:

Alter system set OPEN_CURSORS=400 Scope=both;

Scope Value: both,memory (changes the current instance only, does not change the parameter file), SPFile (only changes the parameter file, does not change the current instance, and the database needs to be restarted)

The cursor with the parameter, which, when open, passes the argument to the cursor.

5. Fetch fetches a row of records

Fetch cursor name into field 1, field 2 ...; --corresponds to the field at the open cursor

6. Transactions: atomicity, consistency, isolation, persistence

Oracle PL/SQL Learning Basics (1)