1 / 67

Stored Procedures & Functions

Stored Procedures & Functions. One or more SQL statements are grouped together to make a program or procedure. With the stored procedure, several capabilities normally associated with programming languages are available Some of these capabilities are:

jihan
Download Presentation

Stored Procedures & Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stored Procedures & Functions

  2. One or more SQL statements are grouped together to make a program or procedure. • With the stored procedure, several capabilities normally associated with programming languages are available • Some of these capabilities are: • Conditional Statements: IF… THEN….ELSE that tests for a certain condition and carry out different operations depending on the condition • Looping: WHILE or FOR loop that causes a set of SQL statements to be executed a number of times • Named Variable: A SQL procedure may store a value that it has calculated, retrieved from the database, or derived in some other way into a program variable and later retrieve the stored value for use in subsequent calculations.

  3. The syntax for using Stored Procedures and Stored Functions differ from one DBMS to another. • Note” Not all DBMS’s offer them! • Examples in this lecture are based on Oracle PL/SQL. • Other DBMS use similar syntax

  4. The basic syntax of a stored procedure is as follows: • CREATE PROCEDURE Sample • AS BEGIN • DBMS_OUTPUT.PUT_LINE('Hello World'); • END; • / • Please type this in to SQLplus Worksheet, but don’t run it just yet!

  5. Creating a Stored Procedure - Create • The Create procedure statement assigns the newly defined procedure a name • CREATE PROCEDURE Sample • The Name (Sample) follows naming standards as already discussed • 30 chars max, starts with a letter, avoid special chars • You ‘EXECUTE’ a stored procedure by specifying the name, so it should be descriptive • Try running your code now (it should work)… • Try again – What happens?

  6. Creating a Stored Procedure - Create • If the procedure exists already, must use • ‘CREATE OR REPLACE PROCEDURE name’ • Warning: This deletes the old procedure! • Add ‘OR REPLACE’ and try running your code again

  7. Creating a Stored Procedure - Create • AS BEGIN • Indicates where code begins • We will cover some of the code that can be here shortly… • DBMS_OUTPUT.PUT_LINE('Hello World'); • An Oracle supplied Stored Procedure that allows you to display output to the screen • Notice how it ends in a ‘;’. • All commands must be properly terminated • Has a ‘quirk’ as we will see…

  8. Creating a Stored Procedure - Create • END; • Indicates where code ends • / • Indicates the end of the stored procedure • When encountered: • Oracle compiles the code • Stores the code in the System Catalog in an internal representation • If not provided the code may not be compiled!

  9. Calling (Running) a Stored Procedure from SQLPlus • We can execute a stored procedure in SQLPlus Worksheet using the EXECUTE command • EXECUTE Sample; • OR • EXEC Sample;

  10. Calling (Running) a Stored Procedure from SQLPlus • We can execute a stored procedure in SQLPlus Worksheet using the EXECUTE command • EXECUTE Sample; • OR • EXEC Sample; • Try running your stored procedure.

  11. Calling (Running) a Stored Procedure from SQLPlus • Do you get any output? • No! • This is the ‘quirk’ I mentioned • By default, any output generated by the server is suppressed • To actually see the output, execute this first: • SET SERVEROUTPUT ON • Try running the stored procedure now – it should work

  12. Creating a Stored Procedure - Parameters • A stored procedure can accept zero or more parameters as arguments. As an example: • CREATE OR REPLACE PROCEDURE Sample ( • iName IN CHAR, • oCOMMENT OUT VARCHAR2, • ioVALUE IN OUT NUMBER • ) • Placed within parenthesis • If there are no parameters, there are no parenthesis • Arguments are separated by comma • Can be any valid DBMS data type • Size cannot be specified - it is determined automatically

  13. Creating a Stored Procedure- Parameters • The arguments are either input parameters or output parameters or both. • ‘IN’ • The procedure is expecting a value to be passed when it is called • If you execute the stored procedure but fail to pass a value for an ‘In’, an error is returned • iName IN CHAR,

  14. Creating a Stored Procedure- Parameters • ‘OUT’ • The procedure will place a value in this parameter before it finishes executing • Once the procedure finishes the value placed here is available to whatever called this stored procedure • oCOMMENT OUT VARCHAR2,

  15. Creating a Stored Procedure- Parameters • ‘IN OUT’ • The stored procedure expects a value to be passed in • If you execute the stored procedure but fail to pass a value for an ‘In Out’, an error is returned • If the variable is modified in the stored procedure, the changed value will be available. If it is not modified, the value passed in is available • ioVALUE IN OUT NUMBER

  16. Creating a Stored Procedure- Parameters • Parameters can be named anything you like • Normally identified with a similar name as attribute in table (if applicable) • Should prefix with an indicator showing how parameter is used • This allows you to distinguish between a parameter and a table attribute • ‘i’ indicates ‘in’ • ‘o’ indicates ‘out’ • ‘io’ indicates both

  17. CREATE OR REPLACE PROCEDURE SampleProc /* Sample PL/SQL Procedure*/ ( iName IN CHAR, oCOMMENT OUT VARCHAR2, ioVALUE IN OUT NUMBER ) … • The name of the stored procedure: • SampleProc • It receives 3 parameters • The ‘…’ refers to the remainder of the procedure

  18. CREATE OR REPLACE PROCEDURE SampleProc /* Sample PL/SQL Procedure*/ … • The name of the stored procedure: • SampleProc • It receives no parameters • Note how there are no parenthesis • The ‘…’ refers to the remainder of the procedure

  19. Try changing your stored procedure to receive and print out your name: CREATE OR REPLACE PROCEDURE Sample ( iName CHAR ) AS BEGIN DBMS_OUTPUT.PUT_LINE('Hello: '); DBMS_OUTPUT.PUT_LINE(iName); END; / Note: Green Italics indicate what you need to add

  20. To execute the stored procedure you need to pass in a value • This is accomplished by using either if these methods: • EXECUTE Sample(‘yourname’); • EXEC Sample(‘yourname’); • Note: • Executing a stored procedure with an ‘out’ or ‘inout’ variable means you need to handle the returned value • While this can be done in SQLPLUS it is somewhat complex • You will be handling returned values by having one stored procedure call another

  21. Creating a Stored Procedure- Code • If you want to see several items on the same line, you can use PUT: • DBMS_OUTPUT.PUT('Hello:'); • DBMS_OUTPUT.PUT(iName); • If you use this approach you need to terminate the line with a newline character • DBMS_OUTPUT.NEW_LINE;

  22. Creating a Stored Procedure- Code • CREATE OR REPLACE PROCEDURE Sample( • iName CHAR • ) • AS • BEGIN • DBMS_OUTPUT.PUT('Hello: '); • DBMS_OUTPUT.PUT(iName); • DBMS_OUTPUT.NEW_LINE; • END; • /

  23. Creating a Stored Procedure- Code • Or you can concatenate the two (or more) items together on a PUT_LINE… • DBMS_OUTPUT.PUT_LINE( • 'Hello: ' || iName);

  24. Creating a Stored Procedure- Code • CREATE OR REPLACE PROCEDURE Sample( • iName CHAR • ) • AS • BEGIN • DBMS_OUTPUT.PUT_LINE('Hello: ' || iName); • END; • /

  25. Creating a Stored Procedure– • Working Variables • Next is the AS keyword • This indicates the beginning of the body of the code • After the AS keyword you may find either local variable declarations or the BEGIN keyword • If BEGIN immediately follows AS then there are no local variable declarations • Following the BEGIN keyword is the stored procedure code

  26. CREATE OR REPLACE PROCEDURE Sample /* Sample PL/SQL Procedure*/ ( iName IN CHAR, oCOMMENT OUT VARCHAR2, ioVALUE IN OUT NUMBER ) AS BEGIN … • The name of the stored procedure: • Sample • It receives 3 parameters • There are no local variable declarations since there is nothing between AS and BEGIN • The ‘…’ refers to the remainder of the procedure

  27. Creating a Stored Procedure– • Working Variables • Working variables are placed between the AS and BEGIN keywords • AS • wTotal NUMBER(10,2); • wName CHAR(20); • BEGIN • Names can be anything you like • Follow the same naming rules as other objects • Normally prefixed with a ‘w’ to indicate they are ‘w’orking variables • Can be any valid DBMS datatype • Sizes of working variables are specified • Each definition terminates with a semicolon

  28. CREATE OR REPLACE PROCEDURE SampleProc /* Sample PL/SQL Procedure*/ AS wTotal Number(10,2); wName CHAR(20); BEGIN … • The name of the stored procedure: • SampleProc • It receives no parameters • It has a two working variables • The ‘…’ refers to the remainder of the procedure

  29. Creating a Stored Procedure- Code • Executable code placed after BEGIN and ends with END; and / • BEGIN • code here; • END; • / • The code can be: • Any SQL statement covered so far • Normally DML (Select, Insert, etc) • Rarely DDL (Create, Alter, Drop, etc) • PL/SQL statements that extend SQL into a fully functional ‘Language’ • Statements are terminated with a colon (;)

  30. Creating a Stored Procedure- Code • When assigning a value to a variable, must use ‘:=‘ • The ‘=‘ is only used to check for equality, not assignment • AS • wTotal Number(10,2); • BEGIN • wTotal := 0; • You can assign a value to any of the following variables: • ‘working’ as above, • ‘OUT’ or ‘IN OUT’ parameters • You can also assign one variable to another variable • You cannot assign to an IN

  31. Creating a Stored Procedure- Code • When executing a select, must select ‘INTO’ a variable name • SELECT Company • INTO wName • FROM Customers • WHERE Custnum = iNum; The name returned is placed in variable wName Could also place in an OUT or INOUT variable

  32. Creating a Stored Procedure- Code • CREATE OR REPLACE PROCEDURE GetCustName • ( • iNum IN NUMBER • ) • AS • wCompany CHAR(50); • BEGIN • SELECT Company • INTO wCompany • FROM Customers • WHERE Custnum = iNum; • DBMS_OUTPUT.PUT_LINE(wCompany); • END; • / The name returned is placed in variable wCompany wCompany is displayed on the screen.

  33. Creating a Stored Procedure- Code • CREATE OR REPLACE PROCEDURE GetCustName • ( • iNum IN NUMBER, • oCompany OUT CHAR • ) • AS • BEGIN • SELECT Company • INTO oCompany • FROM Customers • WHERE Custnum = iNum; • END; • / The name returned is placed in OUT variable oCompany The value stored in oCompany is available to the calling routine

  34. Compiling and Saving a Stored Procedure • The actual procedure is entered into SQLPlus or SQLPlus Worksheet • While both tools can be used, SQLPlus Worksheet is much easier • For this course, please use the Worksheet to enter your stored procedures • Once you have completed entering your procedure in the Worksheet, ‘execute’ it as if you are running an SQL statement

  35. Compiling and Saving a Stored Procedure • The DBMS will compile the code and store it in the database • Note: The code is stored whether the compile is successful or not, so you may want to always use ‘CREATE OR REPLACE’ – unless you never make mistakes that is… • This is not going to ‘run’ your code, it merely compiles it and stores it in the system catalog

  36. Compiling and Saving a Stored Procedure • If the compile fails, you will receive this message: • Warning: Procedure created with compilation errors. • To see the errors produced, execute the command • SHOW ERRORS

  37. Compiling and Saving a Stored Procedure • The errors look like this: • LINE/COL ERROR • -------- ----------------------------------------------------------------- • 3/12 PLS-00103: Encountered the symbol ";" when expecting one of the • following: • := . ) , @ % default character • The symbol ";" was ignored. • The most helpful part is the line and column that indicates exactly where the error occurred • The text of the error message can be somewhat ambiguous, but together with the line and column, they are not usually too hard to figure out… • The error above tells us that there is a semicolon where it should not be…

  38. Calling (Running) a Stored Procedure – Application Programs • An application program may request execution of the stored procedure using the appropriate SQL statement. • The exact syntax of the call is dependant in the language used to write the application program • VB, JAVA, etc

  39. Calling (Running) a Stored Procedure from another Stored Procedure • Another stored procedure may call it to perform a specific function: • CREATE OR REPLACE PROCEDURE CallProc ( • iValue IN Number • ) • AS • wComment Varchar2(50); • wValue Number(10); • BEGIN • SampleProc(iValue, wComment, wValue); • END; • / Call the stored procedure Provide a value for the parameter passed to the called procedure Can be a variable (a parameter or a ‘working; one) or a literal Must provide a variable for the ‘OUT’ and ‘IN OUT’ parameters

  40. Calling (Running) a Stored Procedure from SQLPlus • We can execute a stored procedure in SQLPlus Worksheet using the EXECUTE command • If the stored procedure has no parameters at all: • EXECUTE Sample; • OR • EXEC Sample;

  41. Calling a Stored procedure from SQLPlus • If a stored procedure only has ‘IN’ parameters, the simplest way to call it is to use literals • Call a parameter that has two ‘IN’ parameters, one a NUMBER the other a CHAR • EXECUTE SampleInParms(55467,’Widgets’); • Note: • When you call a procedure, you specify the parameter values on the call in the same order defined in the procedure itself • In the procedure above, the first parameter would be NUMBER, the second CHAR or VARCHAR2

  42. Calling a Stored procedure • If the stored procedure contains ‘OUT’ or ‘IN OUT’ parameters, we must define a variable to receive the value returned from the procedure • While this can be done from within SQLPlus, it is somewhat difficult • For our course, we will limit executing stored procedures in ‘SqlPlus’ to those that have only IN parameters, or no parameters at all • Note: You will be writing Stored Procedures using ‘OUT’ parameters, only they will be called from within another Stored Procedure…

  43. Stored Function • Similar to a stored procedure - The difference is that a stored function returns a value while a stored procedure does not. • Example • Define a stored function that accepts a customer number, and calculates and returns the total current order amount for that customer. • CREATE FUNCTION GetTotOrds( • iCnum IN INTEGER • ) RETURN NUMBER • AS • wTotOrd NUMBER(16,2); • BEGIN • SELECT SUM(Amount) INTO wTotOrd • FROM Orders • WHERE Cust = iCnum; • RETURN wTotOrd; • END; • / Create a Function Specify the datatype to return Use the ‘RETURN’ statement to specify what to return. Must be the datatype identified above (NUMBER)

  44. Within a stored procedure, you can call a stored function and use its return value in calculations or store it in a variable • Example (Use the returned value directly): • SELECT Company, Name • INTO wCompany, wName • FROM Customers, Salesreps • WHERE CustRep = Salesrep • AND Custnum = iCustNum • AND GetTotOrds(CustNum) > 1000.00 Similar to a Correlated Subquery, this function is called for every joined Customers and Salesreps row. The Custnum from the row is passed to the function which will execute and return the calculated value. This returned value is compared to 1000.00

  45. Example (storing the calculate value returned in a variable): • wTotOrder := GetTotOrds(iCustNum); • SELECT Company, Name • INTO wCompany, wName • FROM Customers, Salesreps • WHERE CustRep = Salesrep • AND Custnum = iCustNum • AND wTotOrder > 1000.00; • Either method is acceptable. What you choose may be a matter of coding style (what you are comfortable with) or may be a standard imposed by the organization.

  46. Conditional Execution • The IF… THEN… ELSE structure works similar to other programming languages. • The syntax is: • IF (condition) THEN • SQL Statement 1; • … • SQL Statement n; • ELSE • SQL Statement 1; • … • SQL Statement n; • END IF;

  47. /* Add a customer procedure*/ CREATE PROCEDURE AddCust ( iCname IN VARCHAR2, iCnum IN INTEGER, iCredLim IN NUMBER, iTgtSls IN NUMBER, iCrep IN INTEGER, iCoffc IN VARCHAR2 ) AS BEGIN INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (iCnum, iCname, iCrep, iCredLim); IF (iTgtSls <= 20000.00) THEN UPDATE Salesreps SET Quota = Quota + iTgtSls WHERE Salesrep = iCrep; ELSE UPDATE Salesreps SET Quota = Quota + 20000.00 WHERE Salesrep = iCrep; END IF; UPDATE Offices SET Target = Target + iTgtSls WHERE City = iCoffc; Commit; END; / Example of code using conditional processing

  48. Repeated Execution • If you need to process some group of statements repeatedly you can use a loop. • Loops are either conditional loops (WHILE LOOP) or unconditional loops which loop N times (FOR LOOP).

  49. Repeated Execution • The general syntax of a FOR loop is: • FOR (Index = S TO E) STEP V • …. • …. • END FOR; • Index – Variable defined by you that controls repetition • S – Value to start counting at • E – Value to count to • V – How much to increment ‘Index’ each time through

  50. The Syntax for WHILE LOOP is: • WHILE (Condition) LOOP • …. • … • END LOOP; • This indicates that as long as the condition in the WHILE statement is True, the loop is executed • As soon as the condition evaluates to False the loop terminates • Execution continues with the statement after END LOOP;

More Related