PLSQL_ structure, stored procedures, functions

/**************************************002 plsql structure, stored procedure, function *****************************************/
/**
[DECLARE declarations]
BEGIN
Executable statements
[EXCEPTION handlers]
END;
*/
DECLARE
V_ename Emp.ename%type;
BEGIN
SELECT ename to v_ename from emp WHERE empno = &empno;
Dbms_output.put_line (' Employee Name: ' | | v_ename);
EXCEPTION
When No_data_found
Then Dbms_output.put_line (' check none of this person! ');
END;

/**
Conditional judgment
If Boolean then executable END if;
If Boolean then executable ELSE executable END if;
If Boolean then executable elsif the Boolean then executable ELSE executable END IF;
*/

/**
Loops (Loop while for)
*/
--LOOP
DECLARE
J Number: = 0;
BEGIN
J: = 1;
LOOP
Dbms_output.put_line (j);
EXIT when J >= 7;
J: = j + 1;
END LOOP;
END;

-While
DECLARE
J Number: = 0;
BEGIN
J: = 1;
While J <= 8
LOOP
Dbms_output.put_line (J | | | ‘---‘);
J: = J+1;
END LOOP;
END;

--For
DECLARE
J Number: = 0;
BEGIN
For J in 1..8
LOOP
Dbms_output.put_line (J | | | ‘---‘);
END LOOP;
END;

/**
Stored procedures. PROCEDURE
Three kinds of Parameter form (in, Outm on out)
*/

--001 in Example
CREATE OR REPLACE PROCEDURE PROC1 (i in number)
As
A VARCHAR2 (50);
BEGIN
A: = ";
For J in 1..i
LOOP
A: = a | | ‘*‘;
Dbms_output.put_line (a);
END LOOP;
END;

--Two ways to call stored procedure 1 execute with EXEC 2 via block
--exec PROC1 (4);
BEGIN
PROC1 (7);
END;

--002 Out example

CREATE OR REPLACE PROCEDURE PROC2 (i out number)
As
BEGIN
I: = 100;
Dbms_output.put_line (i);
END;

DECLARE
K number;
BEGIN
PROC2 (K);
Dbms_output.put_line (k);
END;

--003 in out example

CREATE OR REPLACE PROCEDURE PROC3 (P1 in Out number, p2 in out number)
As
V_temp number;
BEGIN
V_temp: = p1;
P1: = p2;
P2: = v_temp;
END;

DECLARE
NUM1 number: = 10;
NUM2 number: = 20;
BEGIN
PROC3 (NUM1, num2);
Dbms_output.put_line (NUM1);
Dbms_output.put_line (NUM2);
END;

/**
Function: (function is a named PL/SQL subroutine that can return a value)
CREATE [OR REPLACE] FUNCTION <function name> [(PARAM ...)]
RETURN <DATATYPE> Is|as
[LOCAL DECLARATIONS]
BEGIN
Executable statements;
RETURN RESULT;
EXCEPTION
EXCEPTION handlers;
END;
*/

--001 Requirements: Create a function, you can accept the user input number, get the student's position, output position.
/**
--Preparation of experiments
CREATE TABLE STUDENT (stu_no number (3), NAME VARCHAR2 (Ten), score number (3));
INSERT into STUDENT VALUES (1, ' Little ', 99);
INSERT into STUDENT VALUES (2, ' small G ', 80);
INSERT into STUDENT VALUES (3, ' Connaught ', 98);
INSERT into STUDENT VALUES (4, ' small North ', 79);
COMMIT;
SELECT * from STUDENT;
*/

CREATE OR REPLACE FUNCTION FUNC1 (SNO int) RETURN int
As
V_score number;
V_MINGCI number;
BEGIN
SELECT score into V_score from STUDENT WHERE stu_no = SNO;
SELECT COUNT (*) into V_MINGCI from STUDENT WHERE score > V_score;
V_MINGCI: = v_mingci + 1;
RETURN V_MINGCI;
END;

SELECT FUNC1 (3) from DUAL;

PLSQL_ structure, stored procedures, functions