Oracle442 application scenarios --------- basic PL/SQL, basic plsql tutorial

Oracle442 application scenarios --------- basic PL/SQL, basic plsql tutorial

Bytes -----------------------------------------------------------------------------------
The Application Scenario 207---231 is skipped after the backup and restoration of the database .....
The database network configuration is omitted, which is not very common in actual applications. Therefore, we will not explain it in detail here. If there is time in the future, we will sort it out.
Application Scenario 32----244 goes beyond ........

Bytes -----------------------------------------------------------------------------------

----------------- PL/SQL language basics ------------------------

Basic knowledge points:

PL/SQL Character Set
PL/SQL example Program
Declare variables and constants

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

Application Scenario 2245: Use a value assignment statement

SET ServerOutput ON;
DECLARE
BookName VARCHAR2 (50 );
BEGIN
BookName: = 'oracle 11g study note ';
Dbms_output.put_line (BookName );
END;
/

Use Case 246: use 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;
/

Use CASE 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 'tues'
WHEN 3 THEN 'weday'
WHEN 4 THEN 'thurs'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'satur'
WHEN 7 THEN 'Listen 7'
ELSE 'data out of boundary'
END;
Dbms_output.put_line (Result );
END;
/

Scenario 248: 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: 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;
/

Application Scenario 250: LOOP statement 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;
/

Application Scenario 251: LOOP statement 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;
/

Use Case 252: Exception Handling

Set serveroutput on;
DECLARE
X NUMBER;
BEGIN
X: = 'abc'; -- assign a string to variable X of the NUMBER type, 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. Orders ments 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 Data Matching Multiple rows ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('unknown error information ');
END;
/

Use Case 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 Functions
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 Functions
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;
/

Use Case 254: balanced 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. orders;

SET ServerOutput ON;
BEGIN
Dbms_output.put_line (UPPER ('abc '));
END;
/

Scenario 255: Date 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 ('2017-2-5 ', 'yyyy-mm-dd '));
END;
/

Months_between function:
SET ServerOutput ON;
DECLARE
Date1 VARCHAR2 (20): = '2017-06-05 ';
Date2 VARCHAR2 (20): = '2017-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;

Use Case 256: statistical functions

Count Function
Select count (Dep_id) from hrman. Orders ments;

MAX Functions
Select max (Dep_id) from hrman. Orders ments;

MIN Function
Select min (Dep_id) from hrman. Orders ments;

 

 

 

 

Copyright Disclaimer: you are welcome to reprint it. I hope you can add the original article address while reprinting it. Thank you for your cooperation.