Oracle Advanced Learning

Paging

1,rowid

1. rowid, which is generated and unchanged when creating records, is the unique identifier of physical location, pointing directly to the storage location on the hardware.

2. ID is the only logical identifier, so rowid search speed is faster than id, is the fastest way to locate a record at present.

2,rownum

1. rownum is a pseudo-sequence (the default hidden column), which will not be displayed unless specifically specified in the query.

2. rownum is a field used to mark the sequence of results in a result set. It is characterized by sequential marking and continuity. Its main purpose is to control the number of rows returned by a query.

3. The rownum keyword can only be directly related to < or <=, and if it is > or =, it needs to be given an individual name.

3. Paging Template

--Initial subscript = (Current page -1)* pageSize + 1
--End subscription = Current page * pageSize

select * from
(select rowmun rt, table alias.* from Table Name Table Alias where rownum <= End subscription)
where rt >= Initial subscript

------------------------------------------------

--Get the6-10Data record of bar
select * from
(select rownum rt,e.* from scott.emp e where rownum <=10)
where rt >=6

View (virtual table) - Super Account has permission

--Create View Syntax
create view View Name as <select Sentence>

-- Create a view
create view Eiffel_Wu_emp_view
as
select * from scott.emp

-- Create a view(Recommended use)
create or replace view Eiffel_Wu_emp_view
as
select * from scott.emp

--Query the data in the view
select * from Eiffel_Wu_emp_view

--Delete Views
drop view Eiffel_Wu_emp_view

3. Stored Procedures

1. Noun Interpretation

Stored Procedure is a set of SQL statements for specific functions. It is a named statement block written by process control and SQL statements. It is compiled and optimized and stored in the database server. When an application is used, it can only be invoked by specifying the name of the stored procedure and giving parameters (e.g. by specifying the name of the stored procedure). If the stored procedure has parameters) to call.

2. Stored procedure grammar rules in Oracle

--Writing norms
create [or replace] procedure Stored procedure name (parameter name)1 Parameter type1,Parameter name2 Parameter type2)
as
    //Declare variables;
begin 
    sql code block;
end;

--ordinary java Method
public void Eiffel_Wu_emp(int age){
    System.out.println("age=" + age);
}

--Create a stored procedure similar to the above
create or replace procedure Eiffel_Wu_emp(a number)
as
begin
    DBMS_OUTPUT.PUT_LINE('a='||a);
end;

3. Calling stored procedures

--The first way
--exec Stored procedure name()
    
exec Eiffel_Wu_emp(10);

--The second way of calling

declare
begin
    //Stored procedure name();
end;

declare
begin
    Eiffel_Wu_emp(50);
end;

4. Stored Procedures with Return Values

--ordinary java Method
public int Eiffel_Wu_sum(int a, int b){
    int c = a + b;
    return c;
}

--Pass two to the stored procedure number Type of variable, calculates the sum of two variables and returns

create or replace procedure Eiffel_Wu_sum(a in number, b in number, rs out number)
as
    c number;
begin
    c:=a+b;
    rs:=c;
end;

--Call stored procedures with return values
declare
    sumab number;
begin
    Eiffel_Wu_sum(100,200,sumab);
    DBMS_OUTPUT.PUT_LINE('sumab='||sumab);
end;

5. Storage process with if

--ifRule of grammar
if condition then
    //Conditional block;
end if;

--------------------------Pass two number Type parameter, returns maximum value--------------------------------
--ordinary java Method   
public int  Eiffel_Wu_max(int a, int b){
    int temp = a;
    if(a < b){
        temp = b;
    }
    return temp;
}

--stored procedure
create or replace procedure Eiffel_Wu_max(a in number, b in number,rs out number)
as
    temp number;
begin
    temp:=a;
    if a < b then
        temp := b;
    end if;
    rs:=temp;
end;

--Calling stored procedures
declare
    rs number;
begin
    Eiffel_Wu_max(10,20,rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;

6. Storage process with if else

--------------------------Pass two number Type parameter, returns maximum value--------------------------------
--ordinary java Method
public int  Eiffel_Wu_max2(int a, int b){
    if(a > b){
        return b;
    }else{
        return a;
    }
}

--stored procedure
create or replace procedure Eiffel_Wu_max2(a in number, b in number,rs out number)
as
begin
    if a > b then
        rs := a;
    else
        rs := b;
    end if;
end;

--Calling stored procedures
declare
    rs number;
begin
    Eiffel_Wu_max2(30,20,rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;   

7. Storage process with else if

--Pass a stored procedure Number Variables of type, if input1Then print'January'，input2Then print'February'，Other Prints'March'
--stored procedure
create or replace procedure Eiffel_Wu_print(i in number)
as
begin
    if i=1 then
        DBMS_OUTPUT.PUT_LINE('January');
    elsif i =2 then
        DBMS_OUTPUT.PUT_LINE('February');
    else
        DBMS_OUTPUT.PUT_LINE('March');
    end if;
end;

--Stored procedure call
exec Eiffel_Wu_print(1);

8. Storage procedures with while loops

-----------------------------Enter a number Type parameter, traverse this parameter-----------------------------
--ordinary java Method
public void Eiffel_Wu_while(int i){
    int j = 1;
    while(j<=i){
        System.out.println("j=" + j);
        j++;
    }
}

--stored procedure
create or replace procedure Eiffel_Wu_while(i in number)
as
    j number;
begin
    j := 1;
    while j <= i loop
        DBMS_OUTPUT.PUT_LINE('j='||j);
        j := j + 1;
    end loop;
end;

--Stored procedure call
exec Eiffel_Wu_while(100);

----------------Enter a parameter number,such as100，Calculation1-100Between all can be3Sum of divisions-----------------
--ordinary java Method    
public void Eiffel_Wu_sum2(int i){
    int j = 1;
    int sum = 0;
    while(j <= i){
        if(j%3==0){
            sum += j;
        }
        j++;
    }
    return sum;
}

--stored procedure
create or replace procedure Eiffel_Wu_sum2(i in number, rs out number)
as
    j number;
    sm number;
begin
    j := 1;
    sm := 0;
    while j <= i loop
        if j mod 3 = 0 then
            sm := sm + j;
        end if;
        j := j + 1;
    end loop;
    rs := sm;
end;

--Calling stored procedures
declare
    rs number;
begin
    Eiffel_Wu_sum2(100, rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;