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

-----------------------------------------------------------------------------------
Back up and restore the database skip over and explain the scenario 207---231, over ...
Database network configuration is skipped, it is not very common in the actual application, so, here first not detailed explanation, if later time will be organized.
Application scenario----244 over .....

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

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

Basic Points of knowledge:

PL/SQL Character set
PL/SQL 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;

Copyright NOTICE: Welcome reprint, Hope in your reprint at the same time, add the original address, thank you with

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