Database stored procedures, packages, and function syntax

Database stored procedures, packages, function syntax drop table student; create table student (stuNo int primary key, Name varchar2 (10), address varchar2 (30), birthday date ); insert into student values (1, 'mei chaofeng ', 'shandong', to_date ('2017-02-12 12:12:23 ', 'yyyy-MM-DD HH24: MI: ss ')); insert into student values (2, 'lu chengfeng ', 'shanxi', to_date ('2017-2-12 ', 'yyyy-MM-DD'); insert into student values (3, 'feng Mufeng ', 'anhui', '10-March February-1886 '); insert into student values (4, 'qu lingfeng', 'Hunan Changde ', to_date ('2017-02-12 12:12:23', 'yyyy-MM-DD HH24: MI: ss ')); set serveroutput on Rem ======================================== ============================== Rem 1. Create a simple stored procedure, how to execute the Stored Procedure Rem ============================================ ================================ create or replace procedure my_proc6 as begin DBMS_OUTPUT.PUT_LINE ('This is an example of a simple stored procedure! '); End my_proc6;/-- The following is the execution of the stored procedure begin my_proc; end;/-- The following is the execution of the stored procedure execute my_proc in SQLPLUS; rem ============================================== ======================================= Rem 2. Create a stored procedure with parameters Rem = ========================================================== ================================== create or replace procedure sel_StuNameByNO_proc (p_sNo student. stuNo % TYPE: = 1) AS sName student. name % TYPE; begin select name into sName FROM student WHERE stuNo = p_sNo; DBMS_OUTPUT.PUT_LINE ('student ID: '| p_sNo |' name: '| sName ); exception when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('student ID: '| p_sNo |' student does not exist '); END sel_StuNameByNO_proc; /-- execute the Stored Procedure execute sel_StuNameByNO_proc (p_sNo = & gt; 3) in SQLPLUS; -- "= & gt;" assigns the value of execute sel_StuNameByNO_proc (2) to the specified parameter ); -- create or replace procedure (p_sNo in number, p_sname OUT VARCHAR2) as begin select name into p_sname FROM student WHERE stuNo = p_sNo; exception when NO_DATA_FOUND THEN p_sname: = NULL; END sel_StuName_proc;/-- The following is the Stored Procedure DECLARE sName VARCHAR2 (10); sNo PLS_INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE ('enter your student ID '); sNo: = & amp; sNo; sel_StuName_proc (sNo, sName); IF sName is null then DBMS_OUTPUT.PUT_LINE ('student ID: '| sNo |' student does not exist '); ELSE DBMS_OUTPUT.PUT_LINE ('student ID: '| sNo |' name: '| sName); end if; END; -- create or replace procedure swap (p1 in out number, p2 in out number) IS v_temp NUMBER; BEGIN v_temp: = p1; p1: = p2; p2: = v_temp; END;/DECLARE num1 NUMBER: = 100; num2 NUMBER: = 200; BEGIN swap (num1, num2); DBMS_OUTPUT.PUT_LINE ('num1 = '| num1 ); DBMS_OUTPUT.PUT_LINE ('num2 = '| num2); END; rem ============================================== ======================================= Rem 3. Authorize the Stored Procedure Rem ==== ========================================================== ================================== grant execute on sel_StuNameByNO_proc to scott; grant execute on my_proc to public; -- execute accp in SCOTT mode. my_proc; drop procedure my_proc; rem ============================================== ================================ Rem 4, function Rem ========== ========================================================== ============================= the REM function can only contain IN parameters, parameters with in out or OUT parameters IN the REM format cannot only use the database type, do not use pl or SQL-type REM functions. The RETURN type must also be the database type. -- a simple FUNCTION, CREATE OR REPLACE FUNCTION, fun_hello RETURN VARCHAR2 IS BEGIN RETURN, the function is that simple ....... '; END;/-- execute the function select fun_hello from dual; create or replace function verrify_stuNo (sNo PLS_INTEGER) RETURN VARCHAR2 IS max_no PLS_INTEGER; min_no PLS_INTEGER; begin select max (stuNO ), MIN (stuNO) INTO max_no, min_no FROM student; IF sNo & gt; = min_no AND sNo; PROCEDURE stu_proc (sNo NUMBER) IS stu_rec student % ROWTYPE; begin open stu_cur (sNo); -- OPEN the cursor loop fetch stu_cur INTO stu_rec; exit when stu_cur % NOTFOUND; future (stu_rec.stuNo | ''| stu_rec.name |'' | stu_rec.address | ''| stu_rec.birthday); end loop; CLOSE stu_cur; -- CLOSE the cursor END; END pack_stu_cur; /-- EXEC pack_stu_cur.stu_proc (2 ); /Rem ============================================== ================================ REF cursor Rem ==== ========================================================== ================================== Rem can use the REF cursor in the package to return the record set from the Oracle stored procedure, rem Oracle stored procedures cannot RETURN values, but the in out mode Rem CURSOR variable parameter can be used to RETURN the result set create or replace package pack_stu_ref as type stu_cur_type is ref cursor return student % ROWTYPE; PROCEDURE tables (stu_rec in out stu_cur_type); END pack_stu_ref;/create or replace package body pack_stu_ref as procedure tables (stu_rec in out stu_cur_type) is begin open stu_rec for select * FROM student; END stu_ref_proc; END pack_stu_ref;/-- the package calls VARIABLE l_stu REFCURSOR; EXECUTE pack_stu_ref.stu_ref_proc (: l_stu); PRINT l_stu; rem ============================================== ==================================== use the RECORD type Rem ====== In the Rem package ========================================================== ========================== create or replace package pack_test_rec as TYPE l_stu_type is record (-- custom record type sNo student. stuNo % type, sName student. name % type, sAddress student. address % type); CURSOR stu_cur RETURN l_stu_type; PROCEDURE future; END pack_test_rec;/create or replace package body pack_test_rec as cursor stu_cur RETURN l_stu_type is select stuNo, name, address FROM student; PROCEDURE stu_cur_proc IS stu_rec l_stu_type; begin open stu_cur; loop fetch stu_cur INTO stu_rec; exit when stu_cur % NOTFOUND; DBMS_OUTPUT.PUT_LINE (stu_rec.sNo | ''| stu_rec.sName |'' | stu_rec.sAddress); end loop; CLOSE stu_cur; END pack_test_rec;/EXEC success; column line format 999 column text format A70 SELECT line, text FROM USER_SOURCE where name = UPPER ('p2 ');