Oracle stored procedures Detailed (II)

Resources:
Oracle stored procedures and parameters understand the non-parametric stored procedures:

Create or replace procedure SayHello
as
-description part
begin
  Dbms_output.put_line (' Hello World ');
End

two ways to invoke a command window1.

Sql> set serveroutput on;   --The first time you must open
sql> exec sayhello
Hello World
pl/sql procedure successfully completed

2.

Sql> begin
  2  sayhello ();
  3  SayHello ();
  4 End  ;
  5  /
Hello World
Hello World
pl/sql procedure successfully completed

Pl/sql block Debugging

BEGIN
  SayHello ();
--rollback; 
End;

SQL window Invocation (dbms_output content is not output)

exec is a sqlplus command that can only be used in Sqlplus (plsql the Command window for developer).
Call is an SQL command that any tool can use. If you want to return the result, use the pass parameter

Call SayHello ();

A stored procedure with a parameter case: Raise salary for employees

Create or Replace procedure raisesalary (Eno in number)
as-defines a variable used to save a salary before a raise
psal Emp.sal%type;
Begin
 -Before a pay rise
 select Sal into Psal from emp where empno = Eno;

 --Salary
 update emp Set sal = Sal + where empno = Eno;

 Dbms_output. Put_Line (' Before Salary: ' | | psal| | ' After Salary: ' | | (psal+100));
 End

Pl/sql block Debugging

DECLARE
  ENO number;
BEGIN
  ENO: = 7499;

  Raisesalary (
    ENO => ENO
  );
--rollback; 
End;

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 to Pname,psal,pjob from emp where empno = Eno;

Pl/sql block Debugging

DECLARE
  ENO number;
  PName VARCHAR2 ();
  Psal number;
  Pjob VARCHAR2 ();
BEGIN
  ENO: = 7521;

  Queryempinfo (
    ENO => ENO, pname => pname, psal => psal, pjob =>
  );

Dbms_output. Put_Line (' pname = ' | | pname);

  :P Name: = pname;

Dbms_output. Put_Line (' psal = ' | | PSAL);

  :P sal: = psal;

Dbms_output. Put_Line (' pjob = ' | | Pjob);

  :P job: = Pjob;
--rollback; 
End;

a parameter stored function

Case: Check 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;

  Returns the yearly income (must pay attention to the null value here) return
  PSAL*12+NVL (pcomm,0);
End;

–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;

View the properties of a stored procedure as input or output

Sql> DESC dbms_output;
Element   Type      
------------------ 
ENABLE    PROCEDURE 
DISABLE   PROCEDURE 
put       PROCEDURE 
Put_Line  PROCEDURE 
new_line  PROCEDURE 
get_line  PROCEDURE   TYPE      
Get_lines PROCEDURE 

sql> DESC dbms_output.put_line;
Parameter Type     Mode Default? 
----------------------------- 
A         VARCHAR2 in   

Out collection (using the cursor)

--Baotou
Create or replace package mypackage as
 type empcursor is REF CURSOR;--Declares a cursor type
 procedure Queryemplist (d No in number,emplist out empcursor);
End;

--Package
Create or replace package body mypackage as

 procedure queryemplist (DNO in Number,emplist out empcursor) as
  begin
     --Opens the cursor open emplist for the
     select * from emp where deptno=dno;
   End;
End

Command Window (view package structure)

sql> desc mypackage
Element      type      
--------------------- 
empcursor    type      
Queryemplist PROCEDURE 

sql> desc mypackage.queryemplist
Parameter Type       Mode Default? 
------------------------------- 
DNO number in            
emplist   

accessing stored procedures and stored functions in applications Accessing stored Procedures

Gets the connection of the database
slightly
//sql statement
String sql = "{call Queryempinfo (?,?,?,?)}";
Create statment callablestatement Call
= Conn.preparecall (sql) by connection;

The value
call.setint (1,7839) is required for the in parameter;

For out parameters need to first declare
call.registeroutparameter (2,oracletypes.varchar);
Call.registeroutparameter (3,oracletypes.number);
Call.registeroutparameter (4,oracletypes.varchar);

Execute call
call.execute ();

Fetch result
String name = call.getstring (2);
Double sal = call.getdouble (3);
String job = call.getstring (4);

Accessing storage functions

Gets the connection to the database
slightly
//sql statement
String sql = "{=call queryempincome (?)}";
Create statment callablestatement Call
= Conn.preparecall (sql) by connection;

For the output parameter need to declare
call.setint (1,oracletypes.number);

The input parameter needs to be assigned a value
call.registoutparameterr (2,7839);

Execute call
call.execute ();

Take out yearly Income
double income = call.getdouble (1);

Accessing a stored procedure with cursors

Gets the connection of the database
slightly
//sql statement (be sure to write the package name)
String sql = "{call Mypackage.queryemplist (?,?)}";
Create statment callablestatement Call
= Conn.preparecall (sql) by connection;

The value
call.setint (1,10) is required for the in parameter;

For out parameters need to first declare
call.registeroutparameter (2,oracletypes.cursor);

Execute call
call.execute ();

Remove all employee information from the Department (note here)
ResultSet rs = ((oraclecallablestatement) call). GetCursor (2);

while (Rs.next ()) {
    //You can remove all the fields from the query in the SQL statement (here take only a few demos)
    int empno = Rs.getint ("empno");
    String ename = rs.getstring ("ename");
    Double sal = rs.getdouble ("Sal");
}

The most basic two 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 compiles or executes execute
      immediate ' drop table ', regardless of whether the table is in or out TableName;  
   End If;

EXCEPTION when  
  others THEN 

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

Delete a 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));
End Drop_sequence;