Shulou(Shulou.com)05/31 Report--

How is PLSQL used in Oracle? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Detailed explanation of the use example of Oracle basic PLSQL

PL/SQL block is an application developed on top of SQL language, which can centrally deal with all kinds of complex SQL operations.

Composition:

DECLARE: declaration part BEGIN Writing theme EXCEPTION catches exception END

1. A simple PL/SQL block

DECLARE i number; BEGIN END 30; DBMS_OUTPUT.put_line (the content of'I is:'| | I);

At this point, you can execute the program directly.

After execution, no output was found. Because Oracle defaults to not displaying the output in the system settings, if you want to display, enter the following command:

Set serveroutput on; if you are using Toad, you can select PLSQL in Desktop and open DBMS OUTPUT.

Statement block with exception

DECLARE i number; BEGIN iVuan1Comp0; EXCEPTION when ZERO_DIVIDE then DBMS_OUTPUT.put_line ("There is an Exception"); END

Note: the output string should be in single quotation marks.

The 2.PL/SQL block can also receive input information from the user, for example, now ask the user to enter an employee number, and then query the employee's name based on the input.

The user's input information is completed using "&"

DECLARE eno number; en varchar (20); BEGIN-- the entered information is stored in the eno eno:=&no;-- and then the database is queried according to the value of eno. Select ename into en from emp where empno = eno; DBMS_OUTPUT.put_line ('serial number:' | eno | | 'employee's name is:' | | en); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.put_line ('no such employee'); END

3. Go further in the above inquiry: according to the employee's number, you can find out the name of the employee and the name of his leader and the department he belongs to, and display it.

DECLARE eno emp.empno%TYPE; en emp.ename%TYPE; mn emp.ename%TYPE; dn dept.dname%TYPE; dept dept% rowtype; BEGIN-- the information entered is saved in eno eno: = & no -- then query the database according to the value of eno, SELECT e.enameforce m.enamedirection d.dname INTO en,mn,dn FROM emp eMague dGramMemp m WHERE e.empno=7369 AND e.mgr=m.empno AND e.deptno=d.deptno; DBMS_OUTPUT.put_line ('number is:' | eno | 'employee's name is:' | | en); DBMS_OUTPUT.put_line ('number is:' | eno | | 'employee's superior name is:' | | mn) DBMS_OUTPUT.put_line ('serial number:' | eno | | 'employee's department:' | | dn); DBMS_OUTPUT.put_line (dept.deptno); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.put_line ('no employee'); END

Description:

No_data_found an exception type: no data found

Emp.empno%TYPE;: indicates that the variable is defined by the type of the empno field in the emp table

E.enamerem.enamered.dname INTO en,mn,dn: you can put more than one value at a time

Dept dept rowtype; indicates that dept is a row of data

PL/SQL also contains conditional control statements such as loops, branches, etc.

4.Loop loop (similar to do... While)

Format:

Statement of LOOP loop; EXIT WHEN termination condition; loop condition must be changed; END LOOP

For example: loop output 1 to 10.

DECLARE countNum NUMBER; BEGIN-must have initial values countNum: = 1; LOOP DBMS_OUTPUT.put_line ('countNum =' | | countNum); EXIT WHEN countNum > 10; countNum: = countNum + 1; END LOOP; END

Note: the count keyword can only be used in sql statements

This loop is executed once and then judged, and the execution result ends the cycle at 11.

5.while cycle

Format:

WHILE (determine the condition of the loop) the statement of the LOOP loop; the change of the condition of the loop; END LOOP

Use this statement to modify the above program:

DECLARE countNum NUMBER; BEGIN-must have initial values countNum: = 1; WHILE (countNum10 THEN DBMS_OUTPUT.put_line ('countNum =' | countNum); END IF; END)

Conditional statement, meet the condition, execute once

8.IF... ELSE statement

If the IF is satisfied, execute it, otherwise execute ELSE

Example:

DECLARE countNum NUMBER; BEGIN countNum: = 1; IF countNum > 10 THEN DBMS_OUTPUT.put_line ('countNum =' | countNum); ELSE DBMS_OUTPUT.put_line ('condition is not valid'); END IF; END

The condition does not satisfy the execution of the statement that follows the else

9.if... Elsif... Else statement

Example:

DECLARE countNum NUMBER; BEGIN countNum: = 1; IF countNum > 10 THEN DBMS_OUTPUT.put_line ('countNum =' | countNum); ELSIF countNum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.