Learning Oracle Database - Stored Procedures

2010-10-12  来源:本站原创  分类:Database  人气:131 

Learning Oracle Database - Stored Procedures

SQL is written in the loop and branch

create or replace procedure basePro is
  num number default 0;
begin

 --loop Cycle
  loop
    Dbms_Output.put_line('loop...end loop' || num);
    exit when(num > 2);
    num := num + 1;
  end loop;

  --while loop Cycle
  while (num < 6) loop
    Dbms_Output.put_line('while...end loop' || num);
    num := num + 1;
  end loop;

  --for loop Cycle
  for i in 1 .. 4 loop
    Dbms_Output.put_line('for...end loop' || i);
  end loop;

  num := 5;
  if num < 10 then
    Dbms_Output.put_line('if num < 10');
  elsif num < 20 then
    Dbms_Output.put_line('if num < 20');
  else
    Dbms_Output.put_line('if num < 30');
  end if;

  case
    when num < 10 then
      Dbms_Output.put_line('case num < 10');
    when num < 20 then
      Dbms_Output.put_line('case num < 10');
    else
      Dbms_Output.put_line('case num < 30');
  end case;

-- He throws exception
raise_application_error(-20000,'bug bug');

  insert into test values (1, '33');
  commit;

exception

  when others then
    Dbms_Output.put_line('insert erroer'||sqlcode||sqlerrm);

end basePro;
/
create or replace procedure bigNumInsert(arg1 in out default 0  number  , arg2 varchar2) is
  newid number default 0;
begin
  dbms_output.put_line(arg1);
  dbms_output.put_line(arg2);
  dbms_output.put_line(newid);

  while (arg1 < 1000) loop
    select nvl(max(id), 0) into newid from test;
    newid := newid + 1;
    insert into test values (newid, arg2 || arg1);

    arg1 := arg1 + 1;
  end loop;

update test t set t.name='4';
   dbms_output.put_line(SQL%rowcount);

  commit;

end;
/
prompt
prompt Creating procedure CURSOR1
prompt ==========================
prompt
create or replace procedure cursor1 is
  num number default 0;
  cursor c1 is
    select * from emp;

  emprow emp%rowtype;
begin

  -- Simple cursor  

  for i in (select * from emp) loop
    dbms_output.put_line(i.EMPNO);
  end loop;
  -- Simple cursor  2
  for i in c1 loop
    dbms_output.put_line(i.ENAME);
  end loop;

-- In general usage
  open c1;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.empno || ' ' || emprow.ENAME);
  end loop;
  close c1;

  -- Implicit cursor  

end cursor1;
/
create or replace procedure cursor2 is
  --num number default 0;
  --type ref_cursor is ref cursor;
  c1     p1.ref_cursor;
  emprow emp%rowtype;
  sqlString varchar2(2000);
begin

-- Dynamic  sql

  sqlString := 'select * from emp t where t.empno=  7369';
  open c1 for sqlString;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.ename);
  end loop;
  close c1;

  sqlString := 'select * from emp t where t.empno=  :empno';
  open c1 for sqlString
    using 7369;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.ename);
  end loop;
  close c1;
end cursor2;
/
相关文章
  • Learning Oracle Database - Stored Procedures 2010-10-12

    Learning Oracle Database - Stored Procedures SQL is written in the loop and branch create or replace procedure basePro is num number default 0; begin --loop Cycle loop Dbms_Output.put_line('loop...end loop' || num); exit when(num > 2); num := num + 1

  • oracle database stored procedures, with commit, rollback 2010-09-29

    First, the database stored procedures (1) the establishment of package create or replace package t_allpackage is type mycursor is ref cursor; end t_allpackage; (2) stored procedure body create or replace procedure tuser_all(user_no in varchar2,mycur

  • oracle database stored procedures and functions small example 2011-05-10

    ----- Function create or replace function isNumber (strNum in varchar2) return char is chkNum number; begin chkNum: = to_number (strNum); return 'Y'; exception when others then return 'N'; end isNumber; declare ww varchar2 (10): = '17 '; begin dbms_o

  • oracle learning - constraints and data dictionary and database stored procedures 2010-12-02

    Ideas: a. data dictionary tables and user tables User table: the user to create and maintain, contains the user's information Data Dictionary: dictionary (including shows and comments) dba_tab_coumns, dba_constraints, dba_tables, dba_indexes / / obje

  • Take the phonetic code of the database stored procedures (Oracle.Sql Server) 2011-03-15

    Oracle Database: Sql Code CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2) RETURN VARCHAR2 AS v_strlen INT; v_return VARCHAR2 (500); v_ii INT; v_n INT; v_c VARCHAR2 (2); v_chn VARCHAR2 (2); v_rc VARCHAR2 (500); /************************************

  • oracle triggers stored procedures. functions. package 2010-09-09

    1. Oracle trigger to use create or replace trigger tri_userid before insert on t_user for each row declare new_key number; begin select seq_userid.nextval into new_key from dual; :new.id:=new_key; end; 2 Stored Procedures create or replace procedure

  • ORACLE JAVA stored procedures using BLOB export pictures 2010-11-17

    A few days ago a friend of mine to solve the problem: XX received a single system, need to trade statistics from the addition of a system to store and export the images the way to view Figure Film to exist in the database BLOB type directly. Original

  • Data collection platforms in the statements of wisdom how to call oracle database stored procedure 2011-08-21

    Dataset from a stored procedure. sql statement to execute when the first compiled, then executed. Stored procedure is compiled the number of sql statement. Applications need to call directly when you can, so more efficient. Stored procedure with flow

  • Oracle functions. Stored Procedures Summary 2011-03-27

    Comin re-review of an Oracle functions and stored procedures, specifically wrote in the review of the results! Hope you can play a little bit of help! ------------------------------------------------ Function articles --------------------------------

  • PL / SQL (oracle) 6_ stored procedures 2011-07-04

    First, the concept of subroutines. a. is a named pl / SQL block. b. usually able to receive the parameters passed to invoked by others. c. Based on the standard PL / SQL block structure, including the declaration section, executable it part of except

  • Oracle database stored procedure 2011-08-01

    1.Oracle Stored procedure examples CREATE OR REPLACE PROCEDURE proc_page ( p_tblName IN VARCHAR, p_fields IN VARCHAR, p_order IN VARCHAR, p_pageSize IN INT := 10, p_pageIndex IN INT DEFAULT 1, p_rowCount OUT INT, p_returnDesc OUT VARCHAR, p_sel_cur O

  • oracle database stored procedure permissions assigned to another user 2010-11-09

    The oracle database dzjc users dzjc_pack_xzxk_exchange stored procedure permissions assigned to bjsp_tysp users. Need to log into dzjc on, then do what the statement: grant all on dzjc_pack_xzxk_exchange to bjsp_tysp; This can be used in bjsp_tysp dz

  • Oracle functions, stored procedures, packages, views, data link summary 2011-09-06

    1. Stored Procedures 2 Function 3 package was created and its application 4 Views 5 Database chain 1 Create a stored procedure and its application 1.1 stored procedures without parameters -- Stored procedure ( No parameter ) create or replace procedu

  • Summary database stored procedures 10 2010-05-31

     What is the stored procedure stored procedure (procedure) is similar to C language function  used to perform administrative tasks or applications of complex business rules  stored procedure can take parameters and can return results  stored proc

  • oracle database stored procedure, with commit, rollback 2010-09-29

    First, the database stored procedure (1) the establishment of package create or replace package t_allpackage is type mycursor is ref cursor; end t_allpackage; (2) the stored procedure body create or replace procedure tuser_all(user_no in varchar2,myc

  • Oracle Procedure Stored Procedures 2010-09-16

    Oracle / PLSQL: Creating Procedures: http://www.techonthenet.com/oracle/procedures.php Quote The syntax for a procedure is: CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [E

  • oracle stored procedures and functions and how to call oracle java stored procedures and functions 2010-08-05

    A. procedures and functions to process the implementation of the rights granted to other users: GRANT EXECUTE ON find_emp TO MARTIN; b. removal process: DROP PROCEDURE find_emp c. function call: SELECT fun_hello FROM DUAL; d. all of the process see:

  • oracle database stored procedure syntax 2010-12-02

    Process is a subroutine to perform certain procedures, he is the module to perform specific tasks. Process is named PL / SQL block. It can be given parameters, stored in a database, and then have an application or other PL / SQL procedure call. Creat

  • (Original) Ibatis2 call database stored procedures related to sample 2008-09-17

    The first method, passing in a parameter (non-custom java type), return a single value: oracle code: CREATE OR REPLACE PROCEDURE testPro (bidObjectId NUMBER, projectId OUT NUMBER) AS BEGIN SELECT bo.project_id INTO projectId FROM bm_t_bid_object bo W

  • learning oracle database collection 2010-09-07

    Set 1.1 Index Table Index table is the data stored in memory! ! ! 1.1.1 define the index table - Defining a recordset TYPE yang_rec IS RECORD (ename varchar2 (30), eid NUMBER); - Define the type of index table TYPE yang_tab IS TABLE OF yang_rec INDEX