oracle stored procedure II

Keywords: SQL Stored Procedure Database sqlplus

Parameterless stored procedure:

create or replace procedure sayhello
as
--Explanation part
begin
  dbms_output.put_line('hello world');
end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

Two call methods of command window

  1. 1.
SQL> set serveroutput on;   --The first time I have to drive
SQL> exec sayhello
hello world
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  1. 2.
SQL> begin
  2  sayhello();
  3  sayhello();
  4  end;
  5  /
hello world
hello world
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

pl/sql block debugging

BEGIN
  SAYHELLO();
--rollback; 
END;
  • 1
  • 2
  • 3
  • 4

SQL window call (the contents of DBMS Ou output will not be output)

exec is the command of sqlplus, which can only be used in sqlplus (the same is true of PLSQL Developer's command window).  
call is an sql command, which can be used by any tool. If you want to return a result, use the pass through parameter

call sayhello();
  • 1

Referenced stored procedure

Case: pay rise for employees

create or replace procedure raisesalary(eno in number)
as
--Define a variable to save the salary before salary increase
psal emp.sal%TYPE;
begin
 --Before raising salary
 select sal into psal from emp where empno = eno;

 --Salary increase
 update emp set sal = sal + 100 where empno = eno;

 DBMS_OUTPUT.PUT_LINE('Before raising salary:' ||psal|| 'After salary increase:' || (psal+100));
 end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

PL/SQL block debugging

DECLARE
  ENO NUMBER;
BEGIN
  ENO := 7499;

  RAISESALARY(
    ENO => ENO
  );
--rollback; 
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

Case: query employee information

create or replace procedure queryempinfo
(
  eno in number,
  pname out varchar2,
  psal out number,
  pjob out varchar2
)
 as
 begin
   select ename,sal,job into pname,psal,pjob from emp where empno = eno;
end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

PL/SQL block debugging

DECLARE
  ENO NUMBER;
  PNAME VARCHAR2(200);
  PSAL NUMBER;
  PJOB VARCHAR2(200);
BEGIN
  ENO := 7521;

  QUERYEMPINFO(
    ENO => ENO,
    PNAME => PNAME,
    PSAL => PSAL,
    PJOB => PJOB
  );

DBMS_OUTPUT.PUT_LINE('PNAME = ' || PNAME);

  :PNAME := PNAME;

DBMS_OUTPUT.PUT_LINE('PSAL = ' || PSAL);

  :PSAL := PSAL;

DBMS_OUTPUT.PUT_LINE('PJOB = ' || PJOB);

  :PJOB := PJOB;
--rollback; 
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

Parametrically stored function

Case: query the annual income of employees

create or replace function queryempincome(eno in number)
return number
as
psal emp.sal%TYPE;
pcomm emp.comm%TYPE;
BEGIN
  SELECT SAL,COMM INTO PSAL,PCOMM FROM EMP WHERE EMPNO = ENO;

  --Return to annual income(We must pay attention to null value here)
  return psal*12+nvl(pcomm,0);
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

– PL/SQL block debugging

DECLARE
  ENO NUMBER;
  v_Return NUMBER;
BEGIN
  ENO := 7839;

  v_Return := QUERYEMPINCOME(
    ENO => ENO
  );

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

  :v_Return := v_Return;
--rollback; 
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

View whether the properties of the stored procedure are input or output

SQL> DESC DBMS_OUTPUT;
Element   Type      
--------- --------- 
ENABLE    PROCEDURE 
DISABLE   PROCEDURE 
PUT       PROCEDURE 
PUT_LINE  PROCEDURE 
NEW_LINE  PROCEDURE 
GET_LINE  PROCEDURE 
CHARARR   TYPE      
GET_LINES PROCEDURE 

SQL> DESC DBMS_OUTPUT.put_line;
Parameter Type     Mode Default? 
--------- -------- ---- -------- 
A         VARCHAR2 IN
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

out set (using cursor)

--Baotou
create or replace package mypackage as
 type empcursor is ref cursor; --Declare a cursor type
 procedure queryEmpList(dno in number,empList out empcursor);
end;

--Enclaves
create or replace package body mypackage as

 procedure queryEmpList(dno in number,empList out empcursor) as
   begin
     --open cursor
     open empList for select * from emp where deptno=dno;
   end;
end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

Command window (view the structure of the package)

SQL> desc mypackage
Element      Type      
------------ --------- 
EMPCURSOR    TYPE      
QUERYEMPLIST PROCEDURE 

SQL> desc mypackage.queryEmpList
Parameter Type       Mode Default? 
--------- ---------- ---- -------- 
DNO       NUMBER     IN            
EMPLIST   REF CURSOR OUT
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

Accessing stored procedures and functions in an application

Accessing stored procedures

//Get connection to database
//slightly
//sql statement
String sql = "{call queryEmpinfo(?,?,?,?)}";
//Create a statement through a connection
CallableStatement call = conn.prepareCall(sql);

//aboutINParameter needs to be assigned
call.setInt(1,7839);

//aboutOUTParameters need to be stated first
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(3,OracleTypes.NUMBER);
call.registerOutParameter(4,OracleTypes.VARCHAR);

//Execution call
call.execute();

//Remove results
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

Accessing storage functions

//Get connection to database
//slightly
//sql statement
String sql = "{?=call queryempincome(?)}";
//Create a statement through a connection
CallableStatement call = conn.prepareCall(sql);

//Declaration required for output parameters
call.setInt(1,OracleTypes.NUMBER);

//Assignment required for input parameters
call.registOutParameterr(2,7839);

//Execution call
call.execute();

//Take out annual income
double income = call.getDouble(1);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

Access stored procedure with cursor

//Get connection to database
//slightly
//sql statement (be sure to write the package name)
String sql = "{call mypackage.queryEmpList(?,?)}";
//Create a statement through a connection
CallableStatement call = conn.prepareCall(sql);

//aboutINParameter needs to be assigned
call.setInt(1,10);

//aboutOUTParameters need to be stated first
call.registerOutParameter(2,OracleTypes.CURSOR);

//Execution call
call.execute();

//Retrieve all employee information in the Department (note here)
ResultSet rs = ((OracleCallableStatement)call).getCursor(2);

while(rs.next()){
    //You can take out all the fields of the query in the sql statement (just a few demonstrations here)
    int empno = rs.getInt("empno");
    String ename = rs.getString("ename");
    double sal = rs.getDouble("sal");
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

The two most basic stored procedures used by the company:

Delete table

create or replace procedure drop_table(tablename in VARCHAR2)
is

  vn_ctn number(2);
  Begin

  select count(*) into vn_ctn from user_all_tables a where a.table_name = upper(tablename); 

   if vn_ctn > 0 then 
       --execute immediate Whether or not the table is compiled or executed
      execute immediate 'drop table ' || tablename;  
   end if;

EXCEPTION  
  WHEN OTHERS THEN 

    dbms_output.put_line(SQLCODE || '::'||SUBSTR(SQLERRM, 1, 400));
End drop_table;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

Deletion sequence

create or replace procedure drop_sequence(sequenceName in VARCHAR2)
is

  vn_ctn number(2);
  Begin

  select count(*) into vn_ctn from user_sequences a where a.sequence_name = upper(sequenceName); 

   if vn_ctn > 0 then 
      execute immediate 'drop sequence ' || sequenceName;  
   end if;

EXCEPTION  
  WHEN OTHERS THEN 

    dbms_output.put_line(SQLCODE || '::'||SUBSTR(SQLERRM, 1, 400));
End drop_sequence;

Posted by joenjeru on Sun, 29 Mar 2020 09:34:56 -0700

Hot Keywords

©2024 Programmer Group