Basic syntax for the creation and invocation of PL/SQL functions and procedures

--pl/sql Basic Knowledge Learning--one, PL/SQL statement block, basic syntax format declare--variable declaration list Info varchar (25); --Variable declaration stu_unm integer: = 15; BEGIN--statement block info: = ' Soulsjie '; --Assignment of variables dbms_output.put (' HELLO world! '); --Output no newline dbms_output.put_line (info | | stu_unm); --Output Line Wrapping | | The connector for the string--exception handling block end;--1. Loop--1.1for loop declarei integer: =1;j integer: = 10; BEGIN for S in I.. J LOOP Dbms_output.put_line (' Serial number is ' | |    S); END LOOP; end;--1.2if statement Declarechengji Integer;  BEGIN Chengji:=&temp; Dbms_output.put_line (' The result you have entered is: ' | |  Chengji); IF Chengji>90 then--attention not required '; '    Number Dbms_output.put_line (' excellent ');      elsif Chengji>=60 Then--Note not ElseIf dbms_output.put_line (' Pass ');        ELSE dbms_output.put_line (' fail '); END IF; End;--1.3while loop Declarechengji Integer: = 10;    BEGIN while chengji>0 LOOP dbms_output.put_line (Chengji);    Chengji: =chengji-1; END LOOP; End;--1.4do Whlie loop declarei INTEGER: = 12;     BEGIN LOOP Dbms_output.put_line (i);     I:=i-1;    EXIT when i=0;  END LOOP;    END; --1.5case Statement Declare i VARchar (20);    BEGIN I: = ' &temp ';    Case I is ' y ' then Dbms_output.put_line (' you selected Yes ');    When the ' n ' then Dbms_output.put_line (' You have selected No ');    ELSE dbms_output.put_line (' Default item ');  END case;     END;               SELECT i.stu_id number, (case i.stu_sex when ' male ' then ' 1 ' when ' women ' then          ' 0 ' ELSE ' unknown ' END ' from Stu_info I;  --1.6 GOTO Statement--Create node, jump to specified node declare BEGIN <<a>> dbms_output.put_line (' a ');  Goto C;  <<b>> dbms_output.put_line (' B ');  <<c>> dbms_output.put_line (' C ');      END; --2.select into usage.  Statistics Student Information table total number of students--select into assigns the result of the query to the variable DECLARE TEMP INTEGER;  Us_name VARCHAR (20); BEGIN Select COUNT (*) into TEMP from (SELECT DISTINCT stu_id from Stu_info);--Query the total number of student information records Dbms_output.put_line (' Total number of records Have ' | | temp| | '   Article '); SELECT i.stu_name into Us_name from Stu_info I WHERE i.stu_id= ' 180301 ';---Query the student name of the specified account Dbms_output.put_line (' name is: ' | |  Us_name);       END;--3.cursor uses the cursor to read data through a recordset--%isopen (whether the cursor is already open)/%found (traversed to data)/%notfound (not traversed to data)/%rowcount (number of records traversed to data) DECLA RE CURSOR Youbiao1 is SELECT * from Stu_info I WHERE i.stu_sex= ' male '; --a. Create a cursor temp youbiao1%rowtype;--b. Creates a variable that records each line of content BEGIN if not Youbiao1%isopen then open youbiao1;--c. If the cursor is not open     Open the cursor dbms_output.put_line (' cursor has been opened ');     END IF;        Loop--d. Using loops to let cursors read data fetch YOUBIAO1 into temp; EXIT when Youbiao1%notfound;--e. Jump out of Loop dbms_output.put_line (temp.) When the record is read.       Stu_name);       END LOOP;     Close youbiao1;--f. Turn off cursor dbms_output.put_line (' cursor closed ');               END;      --4. Exception handling DECLARE AA INTEGER; BEGIN aa:=1/0;--function executes EXCEPTION when OTHERS then--exception handling block Dbms_output.put_line (' An exception appears!       ‘);       END; --second, create and use the function/* basic format Create [OR REPLACE] Function Fun_name [(parameter 1 parameter type, parameter 2 parameter type, parameter n parameter type)] return value type Isbegin function Body retur  N the specific return value; END fun_name;*/--Creating parameterless function Create OR REPLACE function F_wucanshu RETURN INtegeris BEGIN RETURN 3+6; END F_wucanshu; SELECT F_wucanshu call from dual;--function--Create an argument function created OR REPLACE function F_youcanshu (num1 integer,num2 INTEGER) RETURN Integ  Erisbegin RETURN (NUM1*NUM2); END F_youcanshu; SELECT F_youcanshu (& digital 1,& number 2) the product is the from dual;--called the parameter function--Create the function to query the student's name according to the student number entered by the user Create OR REPLACE function f_ Search_name (stu_id varchar) RETURN varchar-note the declaration of the parameter without length, such as: (stu_id varchar) istemp varchar; Stu varchar (20);  BEGIN stu:=stu_id; SELECT DISTINCT stu_name into temp from Stu_info WHERE stu_id=stu;--saves the queried number to the variable temp return temp;--Returns the result to end F_search_n AME; Select F_search_name (' & Please enter the number to be queried ') query results from dual;--III, process creation and use--complete a specific task in the database that can be permanently saved in the database for other programs to use the--3.1 process created by the basic format/* CREATE [OR REPLACE] PROCEDURE pro_name [parameter name in parameter type, ... Type of parameter N in parameter n]isbegin//procedure to execute the code body end;*/create OR REPLACE PROCEDURE insert_stuinfo (stu_id in VARCHAR,--Note that no length is added when declaring the type of the parameter Parameter declaration with function (Canshu VARCHAR) differs stu_name in Varchar,stu_class in Varchar,stu_sex in VARCHAR) Isbegin INSERT into Stu_info VAlues (Stu_id,stu_name,stu_class,stu_sex);--The SQL statement block END to be executed by the process; Call Insert_stuinfo (' Jie ', ' Jie ', ' Jie ', ' Jie ');--the invocation of the stored procedure

Basic syntax for the creation and invocation of PL/SQL functions and procedures