1. Process-oriented languages

Print hello word

Declare -- Specify part (variable, cursor, or exception) begin -- Program body DBMS_output. put_line("Hello Word"); end; Set serverOutput on / -- execute ------ output ------- Hello World -- check the package structure desc dbMS_outputCopy the code

​

Official syntax document

database-pl-sql-language-reference.pdf

Documentation for the package

database-pl-sql-packages-and-types-reference.pdf

Defining base variables

Type: char, varchar2, date, number, Boolean, long

For example: var1 char(15);

​ married boolean := true;

Psal number (7, 2);

Referential variable

pename emp.ename%type ; The pename variable type changes with the ename field in the EMP table

Similar to Pename Varchar2 (64);

Record type variables

Note: represents a row of records, which have different types

​ emp_rec emp%rowtype;

​ select * into emp_rec from emp where empno= 7839;

The use of the if statement

The first type

​

IF conditional THEN statement 1; Statements 2; END IF;Copy the code

The second type

IF conditional THEN statement 1; ELSE statement 2; END IF;Copy the code

The third type

IF conditional THEN statement; ELSIF statement THEN statement; ELSE statements; END IF;Copy the code

Enter a number from the keyboard

Looping statements

The first type

While total <= 25000 Loop
.....
total := total+salary;

END LOOP;
Copy the code

The second type

Loop EXIT[when condition]; . End loop;Copy the code

The third type

FOR I IN 1... 3 Sequence of LOOp statements; END LOOP;Copy the code

Cursor (equivalent to Java Resultset Resultset)

Cursor properties

%fund %notfund Determines whether the cursor reached a record

%isopen: checks whether the cursor isopen

% rowCount Specifies the number of rows affected

CURSOR name [(Parameter name data type [, parameter name data type]…)] IS SELECT statement;

For example, cursor C1 is select ename from EMp;

Limit the number of cursors

By default, Oracle database allows only 300 cursors to open in a single session

Query parameters

Modify the number of cursors

alter  system set open_cursors=400 scope=both;
Copy the code

Open the cursor:

open c1; Open the cursor to perform the query

Close the cursor:

close c1; Close the cursor to release resources

Take the value of the cursor line:

fetch c1 into pename; Take a row into a variable

Fetch is used for:

1) Return the record to which the current pointer points

2) Point to the next record

Cursor use

1. Define a cursor cname is select……

  1. Define a variable for the cursor pename emp.ename%type ; (Reference type variable)
  2. Open the cursor open cname
  3. Fetch cname into for a record pename
  4. Close cname
Set serverOutput ondeclare-- select ename,sal from emp; Cursor cemp(dno number) is select ename from EMp where deptno = dno; -- Define a relative variable for the cursor pename emp.ename%type; psal emp.sal%type; Begin -- Open the cursor open cemp; Loop -- Fetch a record fectch cemp into pename,psal; exit when cemp%notfound; - print dbms_output. Putline (pename | | 'new water' | | psal); end loop; Close cemp; end; /Copy the code

Note: If you modify data in PL/SQL, always commit;

For Oracle, the default transaction isolation level is Read Commiteed;

abnormal

Throw exceptions

Declare Description section (variable description, cursor declaration, exception description) BEGIN statement sequence Exception Exception processing statement end; / example: exception when no_data_fund then dbMS_output.put_line (" The employee was not found "); When others then dbMS_output.put_line (' other exceptions '); end; /Copy the code

Common abnormal

No_data_fund found no data

Too_many_rows returns multiple rows of data

Zero_divide: 0 cannot be divided

Value_error: An arithmetic or conversion error, such as a string variable being converted to a numeric variable.

Custom exception

When an exception is thrown and the cursor is not closed properly, Oracle automatically starts the Pmon Process (Process Monitor) that collects garbage, much like GC in Java

If there is something wrong, we welcome to discuss it together. Finally, welcome to follow my wechat account “Tao Tao Zhi hai”. Your likes, favorites and forwarding are the biggest encouragement to me.