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)