Oracle442 application scenarios --------- PL/SQL Basics

Oracle442 application scenarios --------- PL/SQL Basics

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; DECLAREBookName VARCHAR2 (50); BEGINBookName: = 'oracle 11g study note'; dbms_output.put_line (BookName); END ;/

Use Case 246: use conditional statements

SET ServerOutput ON; DECLARENum INTEGER: =-11; BEGINIF Num <0 THENdbms_output.put_line ('negative number'); ELSIF Num> 0 values ('positive number'); ELSEdbms_output.put_line ('0 '); end if; END ;/

Use CASE 247: branch statement CASE

SET ServerOutput ON; DECLAREvarDAY INTEGER: = 3; Result VARCHAR2 (20); BEGINResult: = CASE varDAYWHEN 1 THEN 'Monday 'when 2 then' Tuesday 'when 3 then' Wednesday 'when 4 then' THEN 'then 5 then' Friday 'when 6 then' Saturday 'when 7 THEN 'then 7' else' data out of bounds 'end; dbms_output.put_line (Result); END ;/

Scenario 248: LOOP... EXIT... END

SET ServerOutput ON;DECLAREv_Num INTEGER := 1;v_Sum INTEGER := 0;BEGINLOOPv_Sum := v_Sum + v_Num;dbms_output.put_line(v_Num);IF v_Num = 4 THENEXIT;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;DECLAREv_Num INTEGER := 1;v_Sum INTEGER := 0;BEGINLOOPv_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;DECLAREv_Num INTEGER := 1;v_Sum INTEGER := 0;BEGINWHILE v_Num <= 4LOOPv_Sum := v_Sum + v_Num;dbms_output.put_line(v_Num);IF v_Num < 4 THENdbms_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;DECLAREv_Num INTEGER;v_Sum INTEGER := 0;BEGINFOR v_Num IN 1..4LOOPv_Sum := v_Sum + v_Num;dbms_output.put_line(v_Num);IF v_Num < 4 THENdbms_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; DECLAREx NUMBER; BEGINx := 'abc'; -- assign a value to the NUMBER type variable X, resulting in exception predictionwhen VALUE_ERROR THENDBMS_OUTPUT.PUT_LINE ('data type error'); END; /set serveroutput on; DECLAREvar_DepName VARCHAR (40); BEGINSELECT Dep_Name INTO var_DepNameFROM HRMAN. parameters WHERE Dep_id> 1; predictionwhen NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE ('no data'); WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE ('data that matches multiple rows '); when others THENDBMS_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;BEGINdbms_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;BEGINdbms_output.put_line(ASCII('ABC'));END;/COL DEP_NAME FORMAT A20SELECT Dep_name, LENGTH(Dep_name) FROM HRMAN.Departments;SET ServerOutput ON;BEGINdbms_output.put_line(UPPER('abc'));END;/

Scenario 255: Date Functions

Sysdate function: SET ServerOutput ON; BEGINdbms_output.put_line (SYSDATE); END;/TO_CHAR function SET ServerOutput ON; functions (TO_CHAR (SYSDATE); END;/last_day function SET ServerOutput ON; BEGINdbms_output.put_line (LAST_DAY (SYSDATE); END;/TO_DATE function SET ServerOutput ON; BEGINdbms_output.put_line (TO_DATE ('2017-2-5 ', 'yyyy-mm-dd'); END; /months_between function: SET ServerOutput ON; DECLAREdate1 VARCHAR2 (20): = '2017-06-05 '; date2 VARCHAR2 (20): = '2017-10-05 '; BEGINdbms_output.put_line (MONTHS_BETWEEN (TO_DATE (date2, 'yyyy-mm-dd'), TO_DATE (date1, 'yyyy-mm-dd'); END;/round function: SET ServerOutput ON; BEGINdbms_output.put_line (TO_CHAR (ROUND (SYSDATE, 'mm'); END; Use Case 256: statistical function count function select count (Dep_id) from hrman. parameters; MAX function select max (Dep_id) from hrman. parameters; MIN function select min (Dep_id) from hrman. administrative Departments;