Stored Procedure Cases

1. Create a table

1 CREATE TABLE Book 2 (3       bookId number,4        bookname VARCHAR2 (5)        Publishhouse Varchar2 (6 )

2.----------A stored procedure with input parameters (add operation)---------------------------------------------------------------------

1CREATE OR REPLACE PROCEDURE Sp_pro--create a stored procedure named Sp_pro;2(      --parameters that need to be entered3Spbookid in number,--Book ID4Spbookname in VARCHAR2,--Book name5Publishhouse in VARCHAR2--Book publishing house6 )7IS--Connection Keywords8BEGIN--block, beginning with begin, ending with end;9INSERT into book VALUES (Spbookid,spbookname,publishhouse); --execution section, inserting the entered values sequentially into the table named BookTen END; One  ACall Sp_pro (3, ' book3 ', ' China ');

3.----------stored procedures with input and output parameters (query operation)---------------------------------------------------------------

1 CREATE OR REPLACE PROCEDURE Sp_pro22 (3Spno in number,--in indicates the value entered, does not write default to in4Spname out VARCHAR2,--out represents the value of the output5 Spsal out number,6 spjob out VARCHAR27 )8  is9 BEGINTenSELECT ename,sal,job to spname,spsal,spjob from emp WHERE empno = spno; --Query The result and assign the value to the output by entering the value OneEND;

4.------------the stored procedure that returns the result set------------------------------------------------------------------------------------

1--1. Create a package to customize a cursor type2CREATE OR REPLACE Package testpackage as--Create a package3TYPE tesr_cursor is REF cursor; --define a type with a name of Tesr_cursor in the package, which is a cursor4END Testpackage; --Close Package5--2. Create a process6 CREATE OR REPLACE PROCEDURE Sp_pro37 (8 spno in number,9P_cursor out Tesr_cursor--output parameter p_cursor, whose type is tesr_cursor (the type defined in the package, is a cursor)Ten ) One  is A BEGIN -OPEN p_cursor for SELECT * from emp WHERE deptno = spno; --Opens the cursor (open cursor name) receive parameter ( forSQL statements) -END;

5.-----------the stored procedure paging---------------------------------------------------------------------

1--1. Create a package that defines a type in the package that is a cursor2 CREATE OR REPLACE package Testpackage as3 TYPE test_cursor is REF cursor;4 END testpackage;5--2. Create a paging stored procedure6 CREATE OR REPLACE PROCEDURE fenye7 (8PageSize in number,--page shows the number of bars (shows a few data per page)9Pagenow in number,--Current PageTenMyrows out number,--Total Records (total number of data) OneMypagecounts out number,--Total Pages (total number of pages) AP_cursor out Testpackage.test_cursor--back to record set - ) -  is theV_sql VARCHAR2 (1000); --Storing SQL statements -V_begin Number: = (pageNow-1) *pagesize + 1; --Calculate start Position -V_end Number: = PageSize * Pagenow; --Calculate End Position - BEGIN +V_sql: = ' select * FROM (select T1.*,rownum rn from -(SELECT * from ' | | tablename| | ') T1 where rownum<= ' | | v_end| | ') +where rn>= ' | | v_begin| | "; AOPEN test_cursor for V_sql; --open a cursor to hold the result set at--reorganize a SQL to calculate the total number of pages -V_sql:= ' SELECT * from ' | |TableName; ---(execute IMMEDIATE) Execute SQL immediately to assign the result to Myrows - EXECUTE IMMEDIATE v_sql into myrows; ---calculates the total number of pages, if not divisible (modulo mod (divisor, divisor)), the result adds a -IF MOD (myrose,pagesize) =0 Then inMypagecontents = myrows/pageSize; - ELSE toMypagecontents = myrows/pagesize+1; + END IF; ---Close Cursors the CLOSE test_cursor; *END;

6.---------cursor (reference type)------------------------------------------------------------------

1 DECLARE2--Defining cursor Types3 TYPE sp_emp_cursor is REF cursor;4--Defining cursor Variables5 test_cursor sp_emp_cursor;6--Defining Variables7V_ename emp.ename%TYPE;8V_sal emp.sal%TYPE;9 BEGINTenOPEN test_cursor for SELECT ename,sal from emp WHERE deptno = &NO; --open a cursor and associate the cursor with SQL; OneLoop--loops out (equivalent to do-- while(), must be performed once) AFETCH test_cursor into V_ename,v_sal; --Remove the content Assignment (fetch) in the cursor -EXIT when Test_cursor%notfound; --Exits when the cursor is empty (must determine exit, otherwise dead loop) (%NotFound to determine whether it is empty) -Dbms_output.put_line (' Name: ' | | V_ename | | ' wages: ' | |v_sal); the END LOOP;  - CLOSE test_cursor; -END;

Stored Procedure Cases