Oracle442 application Scenarios---------PL/SQL Basics

-----------------------------------------------------------------------------------
Backup and restore database skipped. In the following illustration the application scenario 207---231, crossed.

。。。。
Database network configuration is skipped, it is not very common in the actual application. So. No specific explanations are given here. Let's say there's time to sort it out later.
The application scenario is crossed----244.

。。。

。

。。

。

-----------------------------------------------------------------------------------

-------------------PL/SQL Language Foundation------------------------

Basic Points of knowledge:

PL/SQL Character set
PL/SQL Demo sample program
declaring variables and constants

----------------------------------------------------------

Scenario 2245: Using Assignment statements

SET serveroutput on;
DECLARE
BookName VARCHAR2 (50);
BEGIN
BookName: = ' Oracle 11g learning note ';
Dbms_output.put_line (BookName);
END;
/

Scenario 246: Using conditional statements

SET serveroutput on;
DECLARE
Num INTEGER: =-11;
BEGIN
IF Num < 0 Then
Dbms_output.put_line (' negative number ');
elsif Num >0 Then
Dbms_output.put_line (' positive number ');
ELSE
Dbms_output.put_line (' 0 ');
END IF;
END;
/

Scenario 247: Branch Statement case

SET serveroutput on;
DECLARE
Varday INTEGER: = 3;
Result VARCHAR2 (20);
BEGIN
Result: = Case Varday
When 1 Then ' Monday '
When 2 Then ' Tuesday '
When 3 Then ' Wednesday '
When 4 Then ' Thursday '
When 5 Then ' Friday '
When 6 then ' Saturday '
When 7 Then ' week Seven '
ELSE ' data out of bounds '
END;
Dbms_output.put_line (Result);
END;
/

Scenario 248: Looping statements loop ... EXIT ... END

SET serveroutput on;
DECLARE
V_num INTEGER: = 1;
V_sum INTEGER: = 0;
BEGIN
LOOP
V_sum: = V_sum + v_num;
Dbms_output.put_line (V_num);
IF V_num = 4 Then
EXIT;
END IF;
Dbms_output.put_line (' + ');
V_num: = V_num + 1;
END LOOP;
Dbms_output.put_line (' = ');
Dbms_output.put_line (v_sum);
END;
/

Scenario 249: Looping statements Loop ... EXIT When: END

SET serveroutput on;
DECLARE
V_num INTEGER: = 1;
V_sum INTEGER: = 0;
BEGIN
LOOP
V_sum: = V_sum + v_num;
Dbms_output.put_line (V_num);
EXIT when v_num = 4;
Dbms_output.put_line (' + ');
V_num: = V_num + 1;
END LOOP;
Dbms_output.put_line (' = ');
Dbms_output.put_line (v_sum);
END;
/

Scenario 250: Looping statements while .... LOOP ... END LOOP

SET serveroutput on;
DECLARE
V_num INTEGER: = 1;
V_sum INTEGER: = 0;
BEGIN
While V_num <= 4
LOOP
V_sum: = V_sum + v_num;
Dbms_output.put_line (V_num);
IF V_num < 4 Then
Dbms_output.put_line (' + ');
END IF;
V_num: = V_num + 1;
END LOOP;
Dbms_output.put_line (' = ');
Dbms_output.put_line (v_sum);
END;
/

Scenario 251: Looping statements for ... In ... LOOP ... END LOOP

SET serveroutput on;
DECLARE
V_num INTEGER;
V_sum INTEGER: = 0;
BEGIN
For V_num in 1..4
LOOP
V_sum: = V_sum + v_num;
Dbms_output.put_line (V_num);
IF V_num < 4 Then
Dbms_output.put_line (' + ');
END IF;
END LOOP;
Dbms_output.put_line (' = ');
Dbms_output.put_line (v_sum);
END;
/

Scenario 252: Exception Handling

SET serveroutput on;
DECLARE
X number;
BEGIN
x:= ' abc ';--Assigning a string to a variable x of type number, resulting in an exception
EXCEPTION
When Value_error Then
Dbms_output. Put_Line (' Data type error ');
END;
/

SET serveroutput on;
DECLARE
Var_depname VARCHAR (40);
BEGIN
SELECT Dep_name into Var_depname
From Hrman. Departments WHERE dep_id > 1;
EXCEPTION
When No_data_found Then
Dbms_output. Put_Line (' no data ');
When Too_many_rows Then
Dbms_output. Put_Line (' Return multiple rows of matching data ');
When OTHERS Then
Dbms_output. Put_Line (' Error condition unknown ');
END;
/

Scenario 253: Numeric functions

1.ABS function
SET serveroutput on;
BEGIN
Dbms_output.put_line (ABS (-4));
END;
/

2.CEIL function
SET serveroutput on;
BEGIN
Dbms_output.put_line (Ceil (116.34));
Dbms_output.put_line (Ceil (-112.74));
Dbms_output.put_line (ceil (0));
END;
/

3.FLOOR function
SET serveroutput on;
BEGIN
Dbms_output.put_line (Floor (116.34));
Dbms_output.put_line (Floor (-112.74));
Dbms_output.put_line (Floor (0));
END;
/

4.POWER function
SET serveroutput on;
BEGIN
Dbms_output.put_line (POWER (15, 4));
END;
/

5.ROUND function
SET serveroutput on;
BEGIN
Dbms_output.put_line (ROUND (123.456, 2));
Dbms_output.put_line (ROUND (123.456, 1));
Dbms_output.put_line (ROUND (123.456, 0));
Dbms_output.put_line (ROUND (123.456,-1));
Dbms_output.put_line (ROUND (123.456,-2));
Dbms_output.put_line (ROUND (123.456,-3));
END;
/

Scenario 254: Character-type functions

SET serveroutput on;
BEGIN
Dbms_output.put_line (ASCII (' ABC '));
END;
/

COL dep_name FORMAT A20
SELECT Dep_name, LENGTH (dep_name) from Hrman. Departments;

SET serveroutput on;
BEGIN
Dbms_output.put_line (UPPER (' abc '));
END;
/

Scenario 255: Date-based functions

Sysdate function:
SET serveroutput on;
BEGIN
Dbms_output.put_line (sysdate);
END;
/

To_char function
SET serveroutput on;
BEGIN
Dbms_output.put_line (To_char (sysdate));
END;
/

Last_day function
SET serveroutput on;
BEGIN
Dbms_output.put_line (Last_day (sysdate));
END;
/

to_date function
SET serveroutput on;
BEGIN
Dbms_output.put_line (to_date (' 2010-2-5 ', ' yyyy-mm-dd '));
END;
/

Months_between function:
SET serveroutput on;
DECLARE
Date1 VARCHAR2 (20): = ' 2010-06-05 ';
Date2 VARCHAR2 (20): = ' 2010-10-05 ';
BEGIN
Dbms_output.put_line (Months_between (To_date (date2, ' yyyy-mm-dd '), To_date (Date1, ' yyyy-mm-

DD ')));
END;
/

Round function:
SET serveroutput on;
BEGIN
Dbms_output.put_line (To_char (ROUND (sysdate, ' MM ')));
END;

Scenario 256: Statistical functions

Count function
SELECT COUNT (dep_id) from Hrman. Departments;

Max function
SELECT MAX (dep_id) from Hrman. Departments;

Min function
SELECT MIN (dep_id) from Hrman. Departments;

Oracle442 application Scenarios---------PL/SQL Basics