An arrow ( =>) serves as the association operator, which associates the formal parameter to the left of the arrow with the actual parameter to the right of the arrow. The second procedure call uses named notation. And, the compiler associates the second actual parameter, amt, with the second formal parameter, amount. The PL/SQL compiler associates the first actual parameter, acct, with the first formal parameter, acct_no. The first procedure call uses positional notation. You can call the procedure credit_acct in four logically equivalent ways:Ĭredit_acct(acct, amt) - positional notationĬredit_acct(amount => amt, acct_no => acct) - named notationĬredit_acct(acct_no => acct, amount => amt) - named notationĬredit_acct(acct, amount => amt) - mixed notation PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS. That is, you can indicate the association between an actual and formal parameter by position or name. When calling a subprogram, you can write the actual parameters using either positional or named notation. Raise_salary(emp_num, '$2500') - note the dollar signįor more information, see "Datatype Conversion". The following procedure call raises the predefined exception VALUE_ERROR because PL/SQL cannot convert the second actual parameter to a number: Also, the result value must be convertible to the new datatype. For instance, PL/SQL cannot convert between the DATE and REAL datatypes. The actual parameter and its corresponding formal parameter must have compatible datatypes. For example, the following call to raise_salary is valid: If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. When you call procedure raise_salary, the actual parameters are evaluated and the result values are assigned to the corresponding formal parameters.
#Subtype time calc view update
UPDATE emp SET sal = sal + amount WHERE empno = emp_id Ī good programming practice is to use different names for actual and formal parameters. SELECT sal INTO current_salary FROM emp WHERE empno = emp_id PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS For example, the following procedure declares two formal parameters named emp_id and amount: The variables declared in a subprogram spec and referenced in the subprogram body are formal parameters. The next procedure call shows that expressions can be used as actual parameters: For example, the following procedure call lists two actual parameters named emp_num and amount: The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. Subprograms pass information using parameters. For more information about creating and using stored subprograms, see Oracle8i Application Developer's Guide - Fundamentals. IF new_balance = min_sal) AND (salary listġ CREATE PROCEDURE fire_employee (emp_id NUMBER) ASįor readability, when creating subprograms, you can use the keyword AS instead of IS in the spec. PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
The exception-handling part contains exception handlers, which deal with exceptions raised during execution.Ĭonsider the following procedure named debit_account, which debits a bank account:
The executable part contains statements that assign values, control execution, and manipulate Oracle data. These items are local and cease to exist when you exit the subprogram. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part. Generally, you use a procedure to perform an action and a function to compute a value. PL/SQL has two types of subprograms called procedures and functions. Subprograms are named PL/SQL blocks that can take parameters and be invoked. Major Topics What Are Subprograms? Advantages of Subprograms Procedures Functions RETURN Statement Declaring Subprograms Actual versus Formal Parameters Positional and Named Notation Parameter Modes NOCOPY Compiler Hint Parameter Default Values Parameter Aliasing Overloading Calling External Routines Invoker Rights versus Definer Rights Recursion What Are Subprograms? They are like building blocks, which you can use to construct modular, maintainable applications. Subprograms aid application development by isolating operations.
#Subtype time calc view how to
This chapter shows you how to use subprograms, which let you name and encapsulate a sequence of statements. Civilization advances by extending the number of important operations that we can perform without thinking about them.
0 kommentar(er)
