oracle-cursors-Stored procedures-functions-Package

One, stored procedures can not be used directly in the Insert,update,delete, you can have a return but the exit process has three types: do not return a value, you can return multiple values, there are three types of parameters, such as: in: Enter only, do not return the result, the default is in  Out: Returns only the result, not input, to remove the value of the output variable must be taken out of the variable in the PL/SQL block in the out: can be entered, and return the result, to take out the value of the output variable must be removed from the variable of PL/SQL block--syntax create or replace    Procedure name (A1 in Varchar2,a2 out varchar2,a3 in out int) as Begin end;        --Test in the Create or replace procedure GetSex1 (P_customername in varchar2) as V_sex varchar2 (20);            Begin select Sex to V_sex from customer where customername=p_customername;        Dbms_output.put_line (V_sex);     End          --Test out create or replace procedure GetSex2 (P_customername in Varchar2,p_sex out varchar2) as begin        Select Sex into P_sex from the customer where customerName like p_customername;        Exception when others then Raise_application_error ( -20001, ' occur error ');    End --Test in-out create or replace procedure GetSex3 (p_param1 in Out VarchaR2) as begin select sex into p_param1 from customer where customername=p_param1;    End        --Execute DECLARE begin GETSEX1 (' A1 ') in PL/SQL blocks;    End        --Execute DECLARE p_sex VARCHAR2 (20) in PL/SQL block;            Begin GETSEX2 (' A1 ', p_sex);            Dbms_output.put_line (P_sex);            GetSex2 (' A2 ', p_sex);        Dbms_output.put_line (P_sex);                End        DECLARE p_sex VARCHAR2 (20);            Begin p_sex:= ' A1 ';            GetSex3 (P_sex);            Dbms_output.put_line (P_sex);            p_sex:= ' A2 ';            GetSex3 (P_sex);        Dbms_output.put_line (P_sex);    End; function: only return and must return a result, can be used directly in Insert,update,delele,select can have multiple return;     Syntax: Create or Replace function function name (P1 VARCHAR2) return VARCHAR2 as begin exception end;   Example: Return last name by name Create or Replace function Getsex (p_customername varchar2) return VARCHAR2     As V_sex Customer.sex%type;            Begin select Sex to V_sex from customer where customername=p_customername;        return v_sex;        End; Execute function: 1, in SQL (sqlplus) Select Getsex (customerName), sex from customer;            2. Declare v_sex customer.sex%type in PL/SQL block;                Begin V_sex:=getsex (' A1 ');            Dbms_output.put_line (V_sex);                  end; stored procedure: 1. There is no return at the declaration, you can return multiple values, return a return with an output variable (out,in out) for the exit program, and do not return a result.                    2. Can not be used directly in Insert,update,delete,select, only through the EXEC function: 1. The declaration has a return, can only return a value,                  Return represents a value returned. 2. Can be used directly in Insert,update,delete,select. Third, trigger set serveroutput on;create or replace trigger Teacher_trigg Er after insert or update or delete on customer for each row begindbms_output.put_line (' The table data already have been MO   Dified. '); end; Iv. Cursors     DECLARE V_customer Customer%rowtype;                    V_customername Customer.customername%type;              Cursor C1 (V_customername varchar2) is a select * from customer where customerName like v_customername; Begin V_customername:= '&aa  ‘;                    Open C1 (v_customername);                    Fetch C1 into V_customer;      while (C1%found) loop dbms_output.put_line (v_customer.customername| | ' ' | |                        V_customer.sex);                    Fetch C1 into V_customer;              End Loop;       End; Package: There can be more than one method in the package, including the package declaration and the package body, the method name declared in the package declaration, the parameter name, the type, and the number must be exactly the same as the method of the package body. A variable declared in a package declaration is a global variable that can be used--implement package declaration create or replace my_p as function getreverse (v_name varchar2) return Varch    AR2; Procedure Teacher_modify_column (teacher_id number,column_name varchar2,column_value1 number); End;create or replace         Package Body my_pasprocedure teacher_modify_column (teacher_id number,column_name varchar2,column_value1 number) is      V_sql VARCHAR2 (200); Begin v_sql:= ' Update teachers set ' | | column_name| | ' = ' | |   column_value1| | ' where teachers.teacher_id = ' | |        teacher_id;    EXECUTE IMMEDIATE V_sql; End Teacher_modify_column; function Getreverse (V_name varchar2) return varchar2asv_title varchar2, I int:=1;j int:=0;begin j:=length (v_name); while (j>0) loop v_title:= substr (v_name,i,1) | | V_title; i:=i+1; J:=j-1;      End Loop;return V_title;end;end;           Create or Replace package MyPackage as type c_type is REF CURSOR;           function Myreverse (source varchar2) return varchar2;       Procedure SplitPage (P_sql varchar2,page int,pagesize int,result out c_type,pagecount out int);       End --Implement package body create or replace packages body mypackage as function myreverse (source varchar2) return Varcha                 R2 as I int;                 j int;               Result Varchar2 (2000): = ";                 Begin J:=length (source);                 I:=1; while (i<=j) Loop Result:=substr (source,i,1) | |                   Result I:=i+1; end Loop;               return result;             End Query Statement page procedure SplitPage (P_sql Varchar2,pag                 E int,pagesize int,result out c_type,pagecount out int) as V_sql varchar2 (500);                 StartPage int;                 EndPage int;               V_rowcount int; Begin V_sql:= ' SelectCount (*) from (' | |                 p_sql| | ') ';                 Dbms_output.put_line (V_sql);                 Execute immediate v_sql into V_rowcount; PageCount:=ceil (v_rowcount/pagesize); if (page=0)Then raise_application_error (-20001, ' The requested page is too small ');                 End If; if (page>PageCount) then Raise_application_error (-20001, ' The requested page is too large ');                 End If;                 startpage:= (page-1) *pagesize;                 Endpage:=page*pagesize; V_sql:= ' select * FROM (' | | p_sql| | ') where RowNum<= ' | | To_char(EndPage); V_sql:=v_sql| | 'minus '; V_sql:=v_sql| | ' Select* FROM (' | | p_sql| | ') where rownum<= ' | |                  To_char (StartPage); Dbms_output.put_line (V_sql);              Open result for V_sql;    End    End    --Call the method in the package select Mypackage.myreverse (Sex) from customer;      --Call declare PageCount int in the PL/SQL block;      C1 Mypackage.c_type;    V_customer Customer%rowtype;      Begin Mypackage.splitpage (' SELECT * from Customer ', 1,2,c1,pagecount); Dbms_output.put_line (' total pages are ' | |      PageCount);      Fetch C1 into V_customer;  while (C1%found) loop dbms_output.put_line (v_customer.customername| | ' ' | |             V_customer.sex);      Fetch C1 into V_customer;      End Loop;      Close C1;      Mypackage.splitpage (' select * from Customer ', 2,2,c1,pagecount);      Fetch C1 into V_customer;  while (C1%found) loop dbms_output.put_line (v_customer.customername| | ' ' | |             V_customer.sex);      Fetch C1 into V_customer;      End Loop;      Close C1;      Mypackage.splitpage (' select * from Customer ', 3,2,c1,pagecount);      Fetch C1 into V_customer; while (C1%found) loop             Dbms_output.put_line (v_customer.customername| | ' ' | |             V_customer.sex);      Fetch C1 into V_customer;      End Loop;              Close C1;      Mypackage.splitpage (' select * from Customer ', 4,2,c1,pagecount);      Fetch C1 into V_customer;  while (C1%found) loop dbms_output.put_line (v_customer.customername| | ' ' | |             V_customer.sex);      Fetch C1 into V_customer;      End Loop;          Close C1; End

oracle-cursors-Stored procedures-functions-Package