1 what is a stored procedure (a set of sql statements compiled in advance and stored in the database)

1.1 Stored Procedure

In a large database system, a set of SQL statements to complete specific functions are stored in the database and called again after the first compilation. There is no need to compile again. The user calls the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

1.2 advantages of stored procedures

(1) Improve sql execution efficiency: 'precompiled' is stored in memory, so there is no need to compile again
(2) Reduce 'network traffic (I/0)': reduce the context exchange between sql and the server
(3) Improve the 'security' of the system: reduce the danger of sql injection and permission control

1.3 disadvantages of stored procedures

(1) Increase the 'load' of the database server

2 grammar

2.1 creating

create or replace procedure schema.Process name(Variable name in|out data type) 
AS/IS -- here is and as equivalent
   Declaration statement segment;
begin
   Execute statement segment;
exception 
   Exception handling statement segment;
end Process name;

--Note:
   --In stored procedure(PROCEDURE)Sum function(FUNCTION)There is no difference in;
   --In view(VIEW)Can only be used in AS out-of-service IS；
   --In cursor(CURSOR)Can only be used in IS out-of-service AS. 
--as: keyword.
--begin: keyword.
--dbms_output.put_line();Output content.
--end;keyword.
--statement declare keyword

Example

-- Example: divide two numbers and output the result
-- Function Description: calculate the division of two numbers
-- Parameter description:	i_a      Divisor
--         	i_b      Divisor
--         	o_result result 
--         	io_flag  Program execution ID

create or replace procedure scott.p_procedure_demo(i_a      in number,
                                                   i_b      in number,
                                                   o_result out number,
                                                   io_flag  in out varchar2) is
  v_message varchar2(30) := 'Stored procedure template';
begin
  io_flag := 'Y';

  -- i_b = 0 When, the error can be verified
  o_result := i_a / i_b;

  -- Format adjustment 1/2 = .5 => 0.5
  o_result := regexp_replace(o_result, '^\.', '0.');
exception
  when others then
    io_flag := 'N';
    dbms_output.put_line(sqlcode || ', ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

Parameterless stored procedure

CREATE OR REPLACE PROCEDURE demo AS/IS
	Variable 2 DATE;
	Variable 3 NUMBER;
BEGIN
	--Business logic to process
	EXCEPTION    --Stored procedure exception
END

Stored procedure with parameters

CREATE OR REPLACE PROCEDURE Stored procedure name(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --Business processing.....
END

--Line 1: param1 Are parameters, types, and student surface id The fields are of the same type.
--Line 3: declare variables name，Type is student surface name Type of field (ditto).
--Line 4: declare variables age，Number of types, initialized to 20

Stored procedure with parameters and assignment

CREATE OR REPLACE PROCEDURE Stored procedure name(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('Students of this age are'||total||'people');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('Return value is more than 1 line'); 
END
--Where parameters IN Indicates the input parameter, which is the default mode of the parameter.
--OUT Represents the return value parameter. Any type can be used Oracle Legal type in.
--OUT The parameters defined by the pattern can only be assigned within the procedure body, which means that the parameter can pass a value to the callback procedure
--IN OUT Indicates that the parameter can pass a value to the process or a value

2.2 parameter description (in | out): the default is in and can be omitted

--1. Without parameters: No '()'
   	  scott.p_procedure_demo

--2. With parameters: 'Fixed parameter type' or 'Dynamic parameter type'(Recommended, regardless of the data type of the variable) 
   --(1) Only input parameters in: By default, it can be omitted
       scott.p_procedure_demo(p_ename in varchar2)
       scott.p_procedure_demo(p_ename in scott.emp.ename%type)
       
   --(2) Only output parameters out: Cannot be omitted
       scott.p_procedure_demo(p_ename out scott.emp.ename%type)

  --(3) Existing input parameters in，There are also references out
       -- When multiple parameters
       scott.p_procedure_demo(p_empno in scott.emp.empno%type,
                              p_ename out scott.emp.ename%type)
       -- When a parameter is used, it is both an input parameter and an output parameter
       scott.p_procedure_demo(p_empno in out scott.emp.empno%type)

2.3 calling

Calling stored procedures in pl/sql ("applicable in any case")

   declare--Can be omitted
   begin
      procedure_name; -- If yes, add "()"
   end;

In the command window (please note: only applicable when 'no parameter' or 'only input parameter')

   exec procedure_name;
   execute procedure_name; -- exec yes execute Abbreviation of

   call procedure_name();

   -- exec and call Differences between:
   -- exec Can only be executed in the command window
   -- call It can be executed either in the command window or in the pl/sql Execute in the program window, but there must be parentheses, even if there are no parameters

abnormal

create or replace procedure myDemo03
as
age int;
begin
  age:=1/0;
  dbms_output.put_line(age);
  --abnormal
  exception when others then
    dbms_output.put_line('error');
end;

call myDemo03();

while loop

create or replace procedure myDemo5
as
  n_count number := 0;
begin
  while n_count < 5 loop
    dbms_output.put_line(n_count);
    n_count := n_count + 1;
  end loop;
end;
 
begin
  myDemo5;
end;

for loop

create or replace procedure myDemo06
as
begin
 FOR USE in (select * from T_USER_INFO) loop
     if (USE.id<3) then
        dbms_output.put_line(USE.USER_NAME);
      end if;
  end loop;
end;

CALL myDemo06();

Basic CRUD

create or replace procedure mydemo07(ID in int, user_name in varchar,pssword in varchar, created_date in DATE,created_by IN varchar,UPDATED_date IN DATE,UPDATED_by IN  varchar,is_valid IN varchar)
as
begin
     insert into T_USER_INFO VALUES(ID,user_name,pssword,created_date,created_by,UPDATED_date,UPDATED_by,is_valid);
    --UPDATE  T_USER_INFO t SET t.USER_NAME='sunqi' WHERE t.ID=ID ;
    --DELETE  T_USER_INFO t WHERE t.ID=ID ; 
    commit; --Submit
end;

begin
mydemo07(3,'ex_sunqi','666666',SYSDATE,'ex_sunqi',SYSDATE,'ex_sunqi','Y');
end;

2.4 modification and deletion

4. create or replace... -- Repetitive operations are supported

5. drop procedure scott.p_procedure_demo; -- delete
 Debug
6. Select the process name,'Add debug information'
   -- If added, skip this step (if not, the process will not be entered)
   
7. choice 'test'，get into 'Test window'

8. Debug( Debug)

3. Examples of stored procedures

3.1 operators

3.2 select into state statement

CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2;   --Student name
s_age NUMBER;      --Student age
s_address VARCHAR2; --Student native place
BEGIN
  --Assign a value to a single variable
  SELECT student_address INTO s_address
  FROM student where student_grade=100;
   --Assign values to multiple variables
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
  --Output the student information with a score of 100
  dbms_output.put_line('full name:'||s_name||',Age:'||s_age||',Native place:'||s_address);
END

3.3 selection statement (IF... END IF)

IF s_sex=1 THEN --1-Boys; 0-girl student
  dbms_output.put_line('The student is a boy');
END IF
b.IF..ELSE..END IF

IF s_sex=1 THEN
  dbms_output.put_line('The student is a boy');
ELSE
  dbms_output.put_line('The student is a girl');
END IF

3.4 circular statements

--a.Loop Basic cycle
LOOP
  IF expression THEN
    EXIT;
  END IF
END LOOP;

--b.while loop
WHILE expression LOOP
  dbms_output.put_line('haha');
END LOOP;

--c.for Cycle 1
FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

--c.for Cycle 2
create or replace procedure myDemo06
as
begin
 FOR emp in (select * from employees) loop
     if (emp.EMPLOYEE_ID=104) then
        dbms_output.put_line(emp.LAST_NAME );
      end if;
  end loop;
end;

3.5 cursor

Oracle A storage area, called the context area, is created for processing SQL Statement, which contains the statements to be processed, such as all information, row number processing, and so on.

A cursor is an area that points to this context. PL/SQL By controlling the cursor in the context area. The row held by the cursor(One or more)from SQL Statement returns a row set. The cursor held by the row set is called an active set.

Common cursor properties:

attribute	describe
%FOUND	If data is updated or DQL finds the result after the execution of DML statement, return true. Otherwise, return false.
%NOTFOUND	If the data is updated or the DQL finds the result after the execution of the DML statement, it returns false. Otherwise, it returns true.
%ISOPEN	When the cursor is opened, it returns true; otherwise, it returns false.
%ROWCOUNT	Returns the number of rows affected after DML execution.

Use cursor

--Declaring cursors defines the name of the cursor and the associated SELECT sentence:
CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

--Open the cursor to allocate memory so that it is ready to fetch SQL Statement to the row it returns:
OPEN cur_cdd;

--Grab data in cursor, available LIMIT Keyword to limit the number of entries. If there is no default, grab one entry at a time:
FETCH cur_cdd INTO id, name ;

--Close the cursor to free the allocated memory:
CLOSE cur_cdd;

4. Case practice

There is a table student(s_no, s_name, s_age, s_grade), where s_no - student number, which is also the primary key, is arranged upward from 1 (for example, the first student number is 1, the second is 2, and so on) ; s_name - student name; s_age - student age; s_grade - grade; there are tens of millions or even hundreds of millions of data in this table. At the end of a school year, I want to promote all these students to grade one, that is, add 1 to the s_grade field.

If we run this sql directly, because the amount of data is too large, the database undo table space will burst, resulting in exceptions. Let's write a stored procedure and update it in batches. We submit it every 100000.

CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student;
  WHILE i<=total LOOP
    UPDATE student SET grade=grade+1 WHERE s_no=i;
    i := i + 1;
    IF i >= 100000 THEN
      COMMIT;
    END IF;
  END LOOP;
  dbms_output.put_line('finished!');
END;

5. Advanced stored procedure

   In the above case, how long does it take for our stored procedure to process all the data? In fact, I didn't wait for it to complete, and it didn't complete within the acceptable time range. So is the stored procedure helpless in dealing with this tens of millions of data? The answer is No. let's look at other tricks.

   Let's first analyze the execution process of the execution process: after a stored procedure is compiled, when executing one statement, if it encounters pl/sql I'll take it to you pl/sql Engine execution, if encountered sql The statement is sent to sql The engine executes, and then returns the execution result to the pl/sql Engine. When an update with a large amount of data is encountered, the execution focus (in progress, in state) ACTIVE)Will constantly switch back and forth.

   Pl/SQL And SQL The communication between engines is called context switching. Too much context switching will bring excessive performance load, resulting in reduced efficiency and slow processing speed.

   from Oracle8i start PL/SQL Two new data manipulation statements are introduced: FORALL,BUIK COLLECT，These statements greatly reduce the number of context switches (one switch and multiple executions) and improve DML Performance, so the stored procedures using these statements are almost as fast as flying when processing a large amount of data.

1.BUIK COLLECT

Oracle8i First introduced in Bulk Collect characteristic, Bulk Collect Batch retrieval can be carried out, and the retrieval results will be bound to a collection variable at one time, rather than through a cursor cursor One by one retrieval processing. Can be in SELECT INTO,FETCH INTO,RETURNING INTO Used in statements BULK COLLECT，Next, let's look at how these statements are used BULK COLLECT of

(1).SELECT INTO

Find out a result set and assign it to a set variable.

The syntax structure is:

SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

explain:

   field: The fields to be queried can be one or more (ensure that they correspond to the following set variables).

   var_collect: Collection variables (associative arrays, etc.) are used to store the found results.

   table: Table name, the table to query.

   colStatement: The following filter condition statement. such as s_age < 10;

Example: find out the names of students younger than 10 years old and assign them to the array arr_name variable

SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

(2).FETCH INTO

Grab some data from a set and assign it to a set variable.

The syntax structure is as follows:

FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]
explain:

        cur1: Is a collection of data, such as a cursor.

        var_collect: The meaning is the same as above.

        [LIMIT rows]: Optional, limiting the amount of data captured each time. If it is not written, it defaults to one piece of data at a time.

Example: demote a student under the age of 10.

--Query the student number of students younger than 10 years old and put it in the cursor cur_no in
CURSOR cur_no IS 
		SELECT s_no FROM student WHERE s_age < 10;
 
--Declares a union array type, element type, and cursor cur_no The type of each element is consistent
TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
 
--Declare a variable of this array type no
no ARR_NO;
BEGIN
  FETCH cur_no BULK COLLECT INTO no LIMIT 100;
  FORALL i IN 1..no.count SAVE EXCEPTONS
	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
END;

Note: first find out the student numbers of students younger than 10 years old and put them in the cursor. Then take out 100 student numbers from the cursor each time and update them to lower their grade by one level.

(3).RETURNING

In addition to batch binding with SELECT and FETCH, BULK COLLECT can also be used in combination with INSERT, DELETE and UPDATE statements to return the record contents (some fields) affected by the execution of these DML statements.

Take another look at the fields in the student table: student(s_no, s_name, s_age, s_grade)

The syntax structure is as follows:

DMLStatement
RETURNING field BULK COLLECT INTO var_field;
explain:

    DMLStatement: It's a DML sentence.

    field: It is a field in this table. Of course, it can also be separated by multiple commas( field1，field2, field3). 

    var_field: A collection whose type is the field type is separated by commas, as follows:

    (var_field1, var_field2, var_field3)

Example: get the set of names of students whose grade is reduced to grade one because they are less than 10 years old.

TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
names NAME_COLLECT;
BEGIN
  UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
  RETURNING s_name BULK COLLECT INTO names;
END;

explain:

   NAME_COLLECT: Is a collection type. The type is student Tabular name Type of field.

   names: Defines a NAME_COLLECT Variable of type.

(4) 2. Precautions

a. You cannot use the BULK COLLECT clause on associative arrays that use string types as keys.

b. BULK COLLECT can only be used in server-side programs. If it is used on the client, an error will occur that does not support this feature.

c. The target object of bulk collect into must be a collection type.

d. Composite targets, such as object types, cannot be used in the RETURNING INTO clause.

e. If multiple implicit data type conversions exist, multiple composite targets cannot be used in the BULK COLLECT INTO clause.

f. If there is an implicit data type conversion, the collection of composite targets (such as the collection of object types) cannot be used in the bulk collect into clause.

2.FORALL

(1) . grammar

FORALL index IN bounds [SAVE EXCEPTIONS]
sqlStatement；
explain:

    index Is the subscript;

    bounds Is a boundary, the form is start..end

    [SAVE EXCEPTIONS] It can be written or not. This is introduced below;

    sqlStatement It's a DML Statement, there is and only one sql sentence;

example:

– example 1: remove students in grades 5 to 10

FORALL i IN 5..10
       DELETE FROM student where s_grade=i;

- example: 2. arr is an array containing the names of students who want to be promoted to the first grade

FORALL s IN 1..arr.count SAVE EXCEPTIONS
       UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

(2).SAVE EXCEPTIONS

Usually, we may encounter exceptions when executing DML statements, which may cause a statement or the whole transaction to roll back. If we do not use the SAVE EXCEPTIONS statement when writing FORALL statements, the DML statements will stop halfway through the execution.

   If our FORALL Statement SAVE EXCEPTIONS Statement, if an exception is encountered during execution, the data processing will continue downward, and the exception information will be saved to the SQL%BULK_EXCEPTONS In the cursor property of, the cursor property is a record collection, and each record has two fields, for example: (1, 02300);

   ERROR_INDEX: This field stores the exception FORALL Iteration number of the statement;

   ERROR_CODE: Store the corresponding exception, oracle Error code;

The exception information of SQL% bulk_exceptions always stores the possible exceptions of the most recently executed FORALL statement. The number of exceptions in this exception record set is represented by its COUNT attribute, that is:

   SQL%BULK_EXCEPTONS.COUNT，SQL%BULK_EXCEPTIONS Valid subscript indexes range from 1 to%BULK_EXCEPTIONS.COUNT between.

(3). INDICES OF

There is an important limitation before Oracle Database 10g. The database reads the contents of the set from the first row to the last row IN the IN enclosing clause. If an undefined row is encountered within this range, Oracle database will raise ORA-22160 exception event: ORA-22160: element at index [N] does not exist. To solve this problem, Oracle subsequently provided two new statements: indexes of and VALUES OF.

Next, let's look at the indexes of statement, which is used to deal with sparse arrays or arrays with gaps (for example, some elements of a collection have been deleted).

The syntax structure of this statement is:

FORALL i INDICES OF collection [SAVE EXCEPTIONS]

       sqlStatement;

explain:

i: Subscript to a set (nested table or associative array).

Collection: This is the collection.

[SAVE EXCEPTIONS] and sqlStatement have been explained above.

Example: arr_std is a joint array. Each element contains (name,age,grade). Now you want to insert data into the student table.

FORALL i IN INDICES OF arr_stu
       INSERT INTO student VALUES(
           arr_stu(i).name,
              arr_stu(i).age,
              arr_stu(i).grade
       );

(4). VALUES OF

VALUES OF applies: the bound array can be sparse or not, but I only want to use a subset of the elements in the array. The VALUES OF option can specify that the value of the loop counter in the FORALL statement comes from the value of the elements in the specified set. However, VALUES OF has some limitations when using:

   If VALUES OF If the collection used in clause is an associative array, it must be used PLS_INTEGER and BINARY_INTEGER Index, VALUES OF The element used in the clause must be PLS_INTEGER or BINARY_INTEGER；

   When VALUES OF Clause refers to an empty collection, then FORALL Statement will cause exception;

The syntax structure of this statement is:

FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
sqlStatement;
Note: i and collection have the above meanings

Joint array please see the article (or Baidu): https://blog.csdn.net/leshami/article/details/7372061

3.pl/sql debugging stored procedure

First, the current user must have the permission to debug stored procedures. If not, authorize you as a database administrator:

– userName is the user name you want to get permission to debug stored procedures
GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

4. Case practice

The scenario is the same as the case above, as follows:

There are tables student(s_no, s_name, s_age, s_grade), where s_ No - student number, which is also the primary key, is arranged upward from 1 (for example, the first student number is 1, the second is 2, and so on); s_ Name - student name; s_ Age - student age; s_ Grade grade; There are tens of millions or even hundreds of millions of data in this table. At the end of a school year, I want to promote all these students to the first grade, that is, let s_ Add 1 to the grade field.

This sql is written as follows:

update student set s_grade=s_grade+1

Write stored procedures:

(1) 1. Stored procedure 1

Name: process_student1, s of student table_ The no field type is varchar2(16).

CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT s_no FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;

explain:

    hold student The student number of the record to be updated in the table is taken out and placed in the cursor CUR_STUDENT，Each time 100000 pieces of data are fetched from this cursor and assigned to the array students，The 100000 records are updated each time. Loop until all the data in the cursor is fetched.

    FETCH .. BULK COLLECT INTO .. LIMIT rows Statement: this rows I can test up to 100000 at present.

(2) . stored procedure 2 (ROWID)

   If we this student The table has no primary key and no index. What should I do?

Under analysis:

   ROWNUM It is a pseudo column. After each result is obtained, a column will be generated in the result set, starting from 1, and starting from 1 every time.

    ROWID In each table, for each record ROWID Are unique. In this case, we can use ROWID. But note that, ROWID Is a type, note that it and VARCHAR2 Conversion between. There are two ways: ROWIDTOCHAR()Yes ROWID Type conversion to CHAR Type; CHARTOROWID()Yes CAHR Type conversion to ROWID Type.

Next, we write the stored procedure process_student2, the script is as follows:

CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;

explain:

   We found the record first ROWID And convert it into CHAR Type, stored in cursor CUR_STUDENT Inside,

Every time 100000 pieces of data are fetched and assigned to the array for updating. When updating the WHERE condition of the statement, the ROWID string whose array element is CAHR type is converted to ROWID type.

6 basic usage of rownum in Oracle

In mysql, we think of using limit, but in oracle, there is no limit. rownum can solve this problem.

Basic use

Note: rownum does not support >, > =, =,! =, Between... and... Operators can only use symbols (<, < =)

ROWNUM is a sequence, which is the order in which oracle database reads data from data files or buffers.
If it gets the first record, the rownum value is 1, the second record is 2, and so on.
If you use >, > =, =, between... And these conditions, because the rownum of the first record obtained from the buffer or data file is 1, it will be deleted, and then the next record will be removed, but its rownum is still 1, it will be deleted again, and so on, there will be no data. (except select id,name from table where rownum between 1 and 5)

Example: query the records after 3 entries in the table

First, the column of rownum and table are "spliced" as a sub table to query, so that the column of rownum represents an attribute (the key is to take the alias, but it is still sorted according to the previous sequence without taking the alias)

select *
from (
         select ROWNUM r,employees.*
         from employees
      ) where r > 3

result

        R EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE   JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
         4         103 Alexander            Hunold                    AHUNOLD                   590.423.4567         1990/1/3    IT_PROG       9000.00                       102            60
         5         104 Bruce                Ernst                     BERNST                    590.423.4568         1991/5/21   IT_PROG       6000.00                       103            60
         6         107 Diana                Lorentz                   DLORENTZ                  590.423.5567         1999/2/7    IT_PROG       4200.00                       103            60

Select all records in the first 3 of a value in the list

select *
from employees where ROWNUM < 3 order by employee_id desc

It is suggested to write like this

select *
from (
    select distinct * from employees order by employee_id desc
    ) where rownum <= 3

Select the record of interval [a,b]

The idea here is the same as that in the first example. First, the rownum column and table are "spliced" as a sub table to query, so that the column where rownum is located represents an attribute (the key is to take the alias, but it is still sorted according to the previous sequence) and performs between processing on this value;

Paging query

SELECT * FROM (
	SELECT e.*,ROWNUM r FROM employees e WHERE ROWNUM <= 30(pagenumber*pagesize+pagesize)
	) WHERE r >= 25(pagenumber*pagesize)

7 use of reference cursor

We know that in sql server, select * from can be directly used in stored procedures to generate recordsets, but not in oracle stored procedures. Reference cursors can be used to allow recordsets to be returned in stored procedures, functions and packages.

First define a reference cursor:

TYPE ref_type_name IS REF CURSOR
     [RETURN {cursor_name%ROWTYPE
            |ref_cursor_name%ROWTYPE
            |record_name%TYPE
            |record_type_name
            |table_name%ROWTYPE} ];

For example:

CREATE OR REPLACE PACKAGE Types AS
 TYPE cursor_type IS REF CURSOR;
END Types;
/

Then reference the cursor in the stored procedure:

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
                   p_recordset OUT Types.cursor_type) AS
BEGIN
 OPEN p_recordset FOR
   SELECT ename,
          empno,
          deptno
   FROM   emp
   WHERE  deptno = p_deptno
   ORDER BY ename;
END GetEmpRS;
/

For Oracle 9i and later versions, the definition in the first step can be omitted and sys can be used_ Refcursor instead:

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
                   p_recordset OUT sys_refcursor ) AS
BEGIN
 OPEN p_recordset FOR
   SELECT ename,
          empno,
          deptno
   FROM   emp
   WHERE  deptno = p_deptno
   ORDER BY ename;
END GetEmpRS;
/

How to call in the program?

In ADO:

Dim conn, cmd, rs
Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetEmpRS"
cmd.CommandType = 4 'adCmdStoredProc
Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
 -- Do something
 rs.MoveNext
Loop
rs.Close
conn.Close
Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing

In Java:

import java.sql.*;
import oracle.jdbc.*;
public class TestResultSet  {
 public TestResultSet() {
   try {
     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
     Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
     CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); END;");
     stmt.setInt(1, 30); // DEPTNO
     stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
     stmt.execute();
     ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
     while (rs.next()) {
       System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno"));
     }
     rs.close();
     rs = null;
     stmt.close();
     stmt = null;
     conn.close();
     conn = null;
   }
   catch (SQLException e) {
     System.out.println(e.getLocalizedMessage());
   }
 }
 public static void main (String[] args) {
   new TestResultSet();
 }
}

8 use of TYPE keyword IS RECORD and IS TABLE OF keyword in Oracle (circular)

declare
--Define a custom type
type aaa is record (
a_field varchar2(10),
b_field varchar2(10)
)

--Define a collection of custom types(Default subscript index by number)
type aaa_array is table of aaa;
begin 
    --Insert custom type data
    index := nvl(aaa_array.last,0)+1;
    aaa_array(index).a_field := 'a_value';
    aaa_array(index).b_field := 'b_value';

    --Traverse custom type collection aaa_array
    for i in aaa_array.first .. aaa_array.last
    loop

        dbms_output.put_line(aaa_array(i).a_field;

    end loop;
end;
/

--Define a self inserting array and traverse it (5000 Is the array length)
declare 
TYPE varray_type IS VARRAY(5000) OF varchar2(10);
v1 varray_type;
begin
    v1 := varray_type('740151715',
    '736045418',
    '737086339',
    '738277723');
    for i in v1.first..v1.last loop
      dbms_output.put_line(v1(i)); 
    end loop;
end;
/

--Define a index by varchar2 of map Type and traverse(2000 Is the array length)
declare

TYPE map_type IS TABLE OF varchar2(2000) INDEX BY varchar2(50);
v_map_set map_type; 
key varchar2(50) := null;

begin
  --insert key-->value 
    map_set(v_distinct) :=v_deal_name||',null, '||v_master_service_type||','||j.category_code;
    
    --ergodic map
    key:=v_map_set.first;
    loop
        dbms_output.put_line(v_map_set(key));
        key:=v_map_set.next(key);
        exit when key is null;
    end loop;
    
end;
/


--Cycle one query
for letter_rec in ( 
    select * from ord_letter_master where status =1 
)
loop
  dbms_output.put_line(letter_rec.ord_letter_code);
end loop;


--Cycle one syscursor
declare
    --definition Oracle cursor
    rec_master_cur                      SYS_REFCURSOR:=null;
    --Cursor used to load corresponding data row
    rec_master                          brx_ord_factor_master%rowtype:=null;
begin
    --Open cursor
    open rec_master_cur for 
                SELECT *
                from brx_ord_factor_master bofm
                where bofm.order_channel = 'RET'
                and bofm.status = 'A'
                and bofm.pps = in_pps; 
    --Traversal cursor
    loop 
      fetch rec_master_cur into rec_master;
      exit when rec_master_cur%NOTFOUND;
      dbms_output.put_line(rec_master.ord_master_code);
    end loop;
end;
/

9 add table comments and add column comments

--Add table comments   
COMMENT ON TABLE b_so IS 'Shipment order';

Copy code add column comments
– add column comments
COMMENT ON column b_so.c_customerup_id IS' superior distributor ';