First knowledge of Oracle IV (self use) plsql

pl/sql program segment

18. Grammar
    

declare
		--Description part (variable declaration, cursor declaration, exception (exception) description)
	begin
		--Statement sequence( DML Statement)
	exception
		--Exception handling statement
	end;


  Simple output statement
        

begin
    dbms_output.put_line('hello world');
end;

     18.3. Declaration of constants and variables
         The basic types of variables are consistent with those of Oracle when creating tables
         Define syntax:

                varl char(25);
                ass number(9,2);
         Constant definition:
                    cl boolean:=true;

     18.4. Reference the data in the table
        myname emp.ename%type;
         Example:
        

declare
    myname emp.ename%type;
begin
    select t.ename into myname from emp t where t.empno=1001;
end;

   18.5. Record type variables can be understood as objects in java

declare 
    myname emp%rowtype;
begin
    select * into myname from emp t where t.empno=1001;
    dbms_output.put_line(myname. ||' '||myname.  ||' '|| ...)--Note: myname.Post filling emp Column names in tables
end;

   19.if statement
   19.1 syntax:
  1.     

if condition then Statement 1;
Statement 2;... --Note: if Encountered in else elsif or end if It's over
end if;

  2.

if condition then Statement 1;
else Statement 2;
end if;

  3.

if condition then Statement 1;
elsif then Statement 2;
else Statement 3
end if;

20.loop
20.1. Grammar
  1.

WHILE expression LOOP
...
END LOOP;

   2. (more commonly used)

LOOP
EXIT [WHEN condition]
...
END LOOP;


   3. Suitable for continuous digital traversal

FOR I(variable) IN 1..3 LOOP
...
END LOOP;

21. Cursor (equivalent to a collection in java)
     21.1. Definition syntax
         CURSOR tag name [(parameter name, parameter type, parameter name, parameter type,...)] IS SELECT statement
         Example:
             cursor c1 is select * from test is defined in the declare section
    
     21.2. Use steps
         1. Open cursor          open c1; (open cursor for query)
         2. Take the value of a row of cursors      fetch c1 into pno;(pno is a variable)
         3. Close the cursor          close c1; (close cursor to release resources)
         4. Cursor end method          exit when c1%notfound
         Note: the above n must be consistent with the parameter type listed in the table pno emp.eno%type;
         Example: output the employee number and name in emp table in cursor mode

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp;
begin
    open c1;
    loop
        fetch c1 into prec;--Take values from the cursor. After taking values, the cursor will automatically move down one step
        exit when c1%notfound;
    end loop;
    close c1;
end;


        Example: salary increase:

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp;
    addsal number(4);
begin
    open c1;
    loop
        fetch c1 into prec;--Take values from the cursor. After taking values, the cursor will automatically move down one step
        exit when c1%notfound;
        if prec.job='president' then 
            addsal:=800;
        elsif prec.job='manager' then
            addsal:=400;
        else addsal:=100;
        end if;
        update emp 
        set sal = prec.sal+addsal 
        where id=prec.id;
    end loop;
    close c1;
    commit;--Submit after updating data
end;

         Examples with parameters:

declare
    prec emp%rowtype;
    cursor c1(dno emp.id%type) is
    select * from emp where id=dno;
begin
    open c1(4);
    loop
        fetch c1 into prec;
        exit when c1%notfound;
        update emp 
        set sal = prec.sal+100
        where id=prec.id;
    end loop;
    close c1;
    commit;
end;

22. Exceptions (similar to exceptions in java)
     22.1.Oracle's own exceptions:
         1.no_data_found (data not found)
         2.too_many_rows (the query result is more than the value that the variable to be stored can store. For example, the row record statement can only store one row of data, while the select query finds multiple rows of data)
         3.zore_divide (divide by 0)
         4.value_error (arithmetic or conversion error)
         5.timeout_on_resource (timeout when requesting resources)
     22.2. Exception handling:
         In exception
        when value_error then ...;
        when zore_divide then ...;
        when others then ...;
     22.3. Exception statement:
         In declare
         Exception name   exception;
     22.4. Throw exception:
         raise exception name;   

  22.5. Example:

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp where id=70;
    no_emp exception;
begin
    open c1;
    loop
        fetch c1 into prec;
        if c1%notfound then
        raise no_emp;
        update emp 
        set sal = prec.sal+100
        where id=prec.id;
    end loop;
    close c1;
    commit;
exception
    when no_emp then 
        dbms_output.output_line('The employee was not found');
    when others then 
        dbms_output.output_line('Other exceptions');
end;

23. Stored procedure
     23.1 definitions
      

 create or replace procedure Stored procedure name [(parameter in/out Parameter type)]--Input parameters( in)And output parameters( out)
 as 
 begin
     plsql Program body;
 end;

         perhaps

create or replace procedure Stored procedure name [(parameter in/out Parameter type)]--Input parameters( in)And output parameters( out)
is 
begin
    plsql Program body;
end Stored procedure name;

         If database update and other operations are encountered in the stored procedure, generally, the commit operation will be executed only when the stored procedure is called, and the commit operation will not be carried out inside the stored procedure.

     23.2. Call (assuming that the stored procedure has input and output parameters, the first parameter is input and the second bit is output)
        declare 
            
        begin
             Stored procedure name (parameter 1, parameter 2);
        end;

24. Storage function
     24.1. Grammar

create or replace function Function name [(parameter in Parameter type)]
return data type
is 
Result variable data type
begin
    plsql Program body;
return Result variable 
end Function name;

24.2. Difference from stored procedure
         1. The stored procedure has no return value and the stored function has a return value, but the stored procedure can pass out multiple values through out. Therefore, it is generally recommended to use the stored procedure 25.java call
25.java call     

1. Stored procedure

public static void test(){
  String driver="Oracle.jdbc.OracleDriver";
  String conStr="jdbc:Oracle:thin:@127.0.0.1:1521:orcl";
  Connection conn=null;
  CallableStatement cs=null;
  try {
      Class.forName(driver);
      conn= DriverManager.getConnection(conStr,"sys","sys");
      cs=conn.prepareCall("{call countysal(?,?)}");
      cs.setInt(1,7390);
      //Register the type of Oracle output parameter
      cs.registerOutParameter(2,OracleTypes.NUMBER);
      //Execute stored procedure
      cs.execute();
      int y=cs.getInt(2);

  } catch (Exception e) {
      e.printStackTrace();
  }finally {
      try {
          if(cs!= null){
              cs.close();
          }
          if(conn!= null){
              conn.close();
          }
        } catch (SQLException throwables) {
              throwables.printStackTrace();
          }        
    }
}

2. Storage function:

public static void test(){
    String driver="Oracle.jdbc.OracleDriver";
    String conStr="jdbc:Oracle:thin:@127.0.0.1:1521:orcl";
    Connection conn=null;
    CallableStatement cs=null;
    try {
        Class.forName(driver);
        conn= DriverManager.getConnection(conStr,"sys","sys");
        cs=conn.prepareCall("{?=call countysal(?)}");
        cs.setInt(2,7390);
        //Register the type of Oracle output parameter
        cs.registerOutParameter(1,OracleTypes.NUMBER);
        //Execute stored procedure
        cs.execute();
        int y=cs.getInt(2);

    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            if(cs!= null){
                cs.close();
            }
            if(conn!= null){
                conn.close();
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

Source of learning materials: Guo Monogram

Tags: Database Oracle SQL

Posted on Fri, 17 Sep 2021 11:30:18 -0400 by zrocker