PL / SQL (oracle) 6_ stored procedures

2011-07-04  来源:本站原创  分类:Database  人气:80 

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 exception handling part of the end part.
Second, the type of subroutine.
a. stored procedure: usually an action is performed.
b. Functions: usually perform a calculation.
Third, the stored procedure is a subroutine to perform an action, as a database object is stored in the database, can be called repeatedly.
Fourth, create the stored procedure syntax:
Create [Or Replace] Procedure stored procedure name [(parameter list)] Is | As PL / SQL block.
Description: The parameter list of the form parameter name [parameter mode] parameter's data type, if there are multiple parameters, each separated by, division.
Parameter mode: including three, respectively, in, out, in Out, when the parameter mode is omitted, indicates that the parameters in model.
Parameter data type: data type can only write here the type of name, can not write length.
Is | as the back of your variable declarations do not need to declare additional keywords.
End of the part that can end; can also end the stored procedure name; to end.

Create Or Replace Procedure p_1
    Is
      v_deptname Varchar2(20) :='c';
    Begin
      Insert Into dept Values(16,v_deptname,'newyork');
      Commit;
    End p_1;

Fifth, the stored procedure call.
1, in another block calls: direct write stored procedure name, you can pass the appropriate parameters.
2, Sql-plus years in the call: exec stored procedure name (parameter list);
Sixth, how to edit a stored procedure
1, can write a sql-window, finished after press F8, to see if there are compilation errors.
2, through the new-program window-Procedure to create, compile press F8 after the finish, if there is a compilation error in the stored procedure name, right click, edit to modify, recompile.
3, directly through command_window create, compile errors if the Executive show errors command.
Seven, three of the stored procedure parameter mode:
in: the default mode, the equivalent of a constant, the model parameters in a stored procedure can not be modified. In the parameter list can specify a default value.
Out: the parameters of the model is equivalent to a variable, not specified in the argument list defaults.
In Out: The pattern parameter is equivalent to an initialization of variables, not specified in the argument list defaults.
Exercise:
1, create a stored procedure, function of the stored procedure by specifying the number of employees and wages,
Update the number of staff wages, based on the increase in the original and the updated back wages.

Create Or Replace Procedure raise_sal3(p_empno emp.empno%Type,p_sal In Out emp.sal%Type) Is
   Begin
        Update emp Set sal = sal + p_sal Where empno = p_empno;
        Select sal Into p_sal From emp Where empno = p_empno;
    End;

2, write an anonymous block, call the stored procedure.

Declare
      v_sal emp.sal%Type;
   Begin
      v_sal :=200;
      raise_sal3(7788,v_sal);
      dbms_output.put_line(v_sal);
   End;

Eight, pass parameters when calling a stored procedure approach.
1, are passed by position.
2, are passed by name.

Declare
      v_sal emp.sal%Type;
    Begin
      v_sal :=200;
      raise_sal3(p_sal => v_sal,p_empno => 7788);
      dbms_output.put_line(v_sal);
   End;

3, combination passes: The first argument passed by position, and the remaining parameters are passed by name.

Declare
      v_sal emp.sal%Type;
    Begin
      v_sal :=200;
      raise_sal3(7788,p_sal => v_sal);
      dbms_output.put_line(v_sal);
   End;

Exercise:
1, for the department table to add a field maxnum, integer, this field represents the establishment of the department.
Alter Table dept Add maxnum Number;
2, give different values for the field.
Select * From dept For Update
3, create a stored procedure add_emp, the stored procedure to add staff to achieve the function, the parameter for the emp% rowtype type.
When a department employs more than the maximum time of preparation of the department, prompt the user to use exception handling, "the number of establishment of the department is full, can not add employees."

Create Or Replace Procedure add_emp(p_emp emp%Rowtype) Is
         v_maxnum Number;
         v_currnum Number;
         e_too_many Exception;
      Begin
           Select maxnum Into v_maxnum From dept Where deptno = p_emp.deptno;-- Query departmental establishment 

           Select Count(empno) Into v_currnum From emp Where deptno = p_emp.deptno ;-- Query sector number of the current 

           If v_currnum >=v_maxnum Then
              Raise e_too_many;
           Else
              Insert Into emp Values(p_emp.empno,p_emp.ename,p_emp.job,p_emp.mgr,p_emp.hiredate,p_emp.sal,p_emp.comm,p_emp.deptno);
              dbms_output.put_line(' Recording success ');
           End If;
      Exception
            When e_too_many Then
                 dbms_output.put_line(' The departmental establishment is full ');
      End;

4, write a stored procedure call_addemp, verify the correctness of the stored procedure.

Create Or Replace Procedure call_addemp Is
             v_emp emp%Rowtype;
      Begin
           Select * Into v_emp From emp Where empno= 7788;
           v_emp.empno := 7789;
           add_emp(v_emp);
      End;

Nine, the local subroutine
: Statement stored procedure in another stored procedure, use the procedure to declare the stored procedure name means, the effective range of the local routine is limited to their definition of a parent block. And must be written in the declaration of all variables.
Ten, delete stored procedures.

Exercise: write a simple Oracle stored procedure: requires the competent number showing its direct subordinate employees and all lower-level employees (Note: reference to head into the number)

Create Or Replace Procedure show_nextlevel(p_empno emp.empno%Type) Is
   Cursor emp_cursor Is Select Level,ename From emp Where empno <> p_empno Start With empno = p_empno Connect By Prior empno=mgr ;
Begin
   For emp_record In emp_cursor Loop
       If emp_record.Level =2 Then
          dbms_output.put_line(' Staff '||emp_record.ename||' Direct subordinate ');
       Else
          dbms_output.put_line(' Staff '||emp_record.ename||' For indirect subordinates ');
       End If;
   End Loop;
End;
-- Invoke stored procedures
Begin
     show_nextlevel(p_empno => 7839);
End;
相关文章
  • 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

  • PL / SQL paging packages, stored procedures, cursors adjusted to achieve java stored procedure, return set 2010-05-26

    20.PL/SQL page article in Category: Database  describes the preparation of the paging process is any website page (bbs, online shop, blog) are used to the technology, learning pl / sql programming development necessary to master the technology. Figu

  • 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

  • PL / SQL (oracle) 8_ package 2011-07-04

    First, the package concept is similar to a container, can be packaged accordingly Pl / SQL variables, constants, functions, procedures, complex data types and other elements to this container. Used to restrict access to such content. Second, the pack

  • SQL * Plus or PL / SQL Developer View stored procedure compile errors 2011-05-27

    SQL * Plus or PL / SQL Developer View stored procedure compile errors In SQL * Plus or PL / SQL Developer in the Command Windows With show errors procedure procedure_name can see the stored procedure specific error With show errors function function_

  • Oracle and Sql Server AND stored procedures and trigger 2010-06-28

    Experimental content 1, oracle, use the stored procedure the way to achieve log log. 2, SQLserver in a way to use stored procedures to achieve log log. 3, oracle, in the user table, add the last login time field, use the trigger approach, to achieve

  • 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 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

  • PL / SQL (oracle) 1_plsql Profile 2011-07-04

    A, PL / SQL Goal II, Pl / SQL Overview of Oracle Corporation developed specifically for the Oracle database as a programming language. Pl: on behalf of procedural language, procedural language. SQL: Increased cycle control, conditional programming la

  • PL / SQL (oracle) 7_ function 2011-07-04

    First, the concept of functions is a named PL / SQL block. It is stored as a database object in the database, to receive parameters, and can have a return value. It can be invoked as part of an expression. Select round(12.345)+12 From emp; Second, th

  • pl / sql oracle connection is not the solution 2010-02-18

    pl / sql developer oracle connection time and discovered that "ORA-12514: TNS: listener does not recognize the current connection descriptor in the requested service" is wrong, but is no problem with sqlplus, using google search, and find the fo

  • [SQL Server] write stored procedures and optimization experience 2010-07-21

    First, for readers: database development programmers, database data a lot, relates to the SP (stored procedures) the optimization of the project developer, the database has a strong interest in people. 2, Introduction: Zai database development proces

  • In the PL / SQL, Oracle ERP call request (a) - submit a single request 2010-11-04

    Recent development of a PLM system from the work flow through the system to the ORACLE ERP project information into the program, Oracle ERP system, an independent request module, so that we simply call the request to the ERP module can be, as used PL

  • 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 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

  • PL / SQL Oracle installation and connection-free 2011-03-01

    1. Oracle official website to download InstantClient archive, extract it to x: \ oracle. 2 new x: \ oracle \ network \ admin \ tnsnames.ora file, configure the database connection. Example: DB_27 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL

  • 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) 2_ control structure 2011-07-04

    A control structure, branch structure 1, if Note: elsif and else If if( Conditions )then ... ... elsif ... ... end if ; 2, case Note: select case statement can omit the condition, when followed either a specific value, can also be one or more conditi

  • SQL Server system stored procedures commonly used in 2010-08-26

    Commonly used system stored procedure System Stored Procedures Explain sp_databases Services, all listed in the database sp_helpdb Report on the specified database or all databases sp_renamedb Change the name of the database sp_tables Back to the cur

  • 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