Results 1 to 7 of 7

Thread: Using stored procedures and stored functions in MySQL

  1. #1
    Join Date
    Sep 2010
    Posts
    12

    Using stored procedures and stored functions in MySQL

    I know that the stored procedures are another characteristic whose absence has long been emphasized by critics of MySQL with version 5.0 has finally remedied this lack. I am also aware that a stored procedure is a set of SQL statements that are stored on the server with a name that identifies them, then this name helps to rerun the set of instructions by simply referring to it. But the issue with me is, I am not able to create stored procedure.!! This may be happening because I don't have enough knowledge about the stored procedures and stored functions that are used in MySQL. I am sure that you guys have got what I want to say.?! Please help me by providing appropriate information. Detailed information about the stored procedures and stored functions in MySQL would be grateful.

  2. #2
    Join Date
    Jan 2009
    Posts
    143

    Re: Using stored procedures and stored functions in MySQL

    Let's see how to create a stored procedure:
    Code:
    CREATE PROCEDURE name ([parameter [,...]]) 
    [SQL SECURITY {DEFINER | INVOKER}] body 
    // Parameters: 
    [IN | OUT | INOUT] type parameterName
    As we have said, each procedure is identified by a name. In addition, the procedure is attributed to a specific database (as of MySQL 5.0.1), just like a table. Consequently, the procedure is assigned to a database at the time of creation, and the names referenced in it will relate to the same database, unless you are qualified with the name of a specific database. In the creation phase, then, if we denote the name of the procedure without specifying the database that will be allocated to the database currently in use. Each procedure can have one or more parameters, each of which consists of a name, data type and an indication in the case of parameter input, output, or both. If no indication, the parameter input is considered.

  3. #3
    Join Date
    Jan 2009
    Posts
    99

    Re: Using stored procedures and stored functions in MySQL

    The SQL SECURITY clause determines whether, at the time, the procedure will use the permissions of the user that is running or the user who created it (the default is DEFINER). Lets see a concrete example of stored procedure, so that you can easily understand it:

    Code:
    CREATE PROCEDURE nameProc (IN param1 INT, OUT param2 INT) 
    SELECT COUNT (*) INTO param2 FROM table 
    WHERE field1 = param1;
    This statement creates a procedure called 'nameProc' in the current database, the procedure uses a parameter input and one output, both integers, and performs the count of rows in the table in which the value of field1 is the first parameter and the result the query is stored in the second parameter through the INTO clause. Here's how to call the procedure and display the result:

    Code:
    CALL nomeProc (7, @ a); 
    SELECT @ a;
    With the CALL statement we provide the procedure call (assuming that the database currently in use is the same that is associated with the procedure), passing the value 5 as an input parameter and the variable @ a as an output parameter, which will stored the result. The next SELECT statement displays the value of that variable after the execution.

  4. #4
    Join Date
    Feb 2009
    Posts
    105

    Re: Using stored procedures and stored functions in MySQL

    The an example provided by the "Astro121", the stored procedure contains a simple SELECT, you can instead create procedures that contain complex syntax containing multiple statements, in practice, the actual script, with the ability to control the flow through the different constructs (IF, CASE, LOOP, WHILE, REPEAT, LEAVE, ITER). You can also use cursors to handle the resultset. Here's an example of a procedure of some complexity:

    Code:
    DELIMITER // CREATE PROCEDURE procedure1 (INT param1, param2 CHAR (3), OUT param3 INT) 
    BEGIN DECLARE done INT default 0; DECLARE a INT; 
    DECLARE b CHAR (50); CUR1 
    DECLARE CURSOR FOR SELECT id, name FROM customer WHERE cat = param2; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 'SET done = 1; 
    OPEN CUR1, pre-set param3 = 0; CUR1 FETCH INTO a, b; 
    loop: WHILE NOT finished DO IF param3 <param1 THEN December param3 = param3 + 1; FETCH CUR1 INTO a, b; ELSE LEAVE cycle; 
    END IF; END WHILE loop; END // DELIMITER;

  5. #5
    Join Date
    Apr 2008
    Posts
    193

    Re: Using stored procedures and stored functions in MySQL

    Let's break the code provided by the StudyBoy. First, we note the DELIMITER command, you need to change the normal mysql client delimiter instruction, which would be a semicolon. In fact, since the stored procedure contains more instructions, inside the semicolon is used several times. Therefore, if we are to succeed in memorizing the procedure, we must communicate to the server, which the delimiter is another, otherwise, the first semicolon would think that our CREATE is finished. We decide that the new delimiter is a double slash. We declare the procedure with the name procedura1 three parameters, the first two input and output of the last. The code to be executed should be enclosed between the BEGIN and END clauses and inside are three statements that declare DECLARE many variables, so the declaration of a cursor, then the declaration of a handler for SQLSTATE 02000, which we shall soon see the utility. The operation is the first real opening of the cursor (OPEN), which executes the query using an associated input parameters. Here, after initializing the output parameter (which would be counter lines read), we perform the first FETCH, that is, reading the first line of the query result, whose values are assigned to variables a and b. Here c ' is a WHILE loop (to which we have assigned the very name 'cycle') that runs until the value of 'finished' is false, that is equal to zero as initialization. This value will change when there are more lines to read, as specified with the handler (in fact the state is 02,000 indicating this situation.)

  6. #6
    Join Date
    Apr 2009
    Posts
    90

    Re: Using stored procedures and stored functions in MySQL

    Inside the loop occurs if the variable 'param3' has already reached the value of 'param1', which was the other input parameters: in this case, the statement LEAVE you leave the cycle itself, otherwise it increase the variable and run a new reading of the cursor, always bringing the results into the variables b (the example is for illustration only, since it does not ever use these values). The cycle ends when they're finished and then the rows of the cursor, or when we read as many as indicated by the first parameter, depending on which of two events occurs first. On completion of the output parameter will contain the number of lines read. The DELIMITER statement is used again to restore the semicolon delimiter, once the procedure is stored.

  7. #7
    Join Date
    Oct 2008
    Posts
    101

    Re: Using stored procedures and stored functions in MySQL

    The following are some elements that you can find in a stored procedure:
    • Variables are those declared with the DECLARE statement, the more the parameters received from the procedure
    • Conditions: MySQL error codes match or SQLSTATE values to which we can give a name and then manage them with a HANDLER
    • Sliders: are used to perform queries that return a resultset when we want to run this procedure within the resultset
    • Handler: instructions to execute upon the occurrence of certain conditions and these conditions may be, in addition to those defined above, directly SQLSTATE, or an error code or other conditions SQLWarning, NOT FOUND, SQLException. The statement must also specify whether the occurrence of the condition, the procedure should continue or terminate (CONTINUE or EXIT)
    All these elements are created with the DECLARE statement, and must be at the beginning of the code (after BEGIN) in the order they have listed.

Similar Threads

  1. Extracting stored procedures
    By Maal-Gaadi in forum Software Development
    Replies: 3
    Last Post: 29-12-2010, 07:29 PM
  2. What is SQL Stored Procedures?
    By technika in forum Software Development
    Replies: 5
    Last Post: 20-02-2010, 05:00 PM
  3. Calling Oracle Stored Procedures with PHP
    By Landan in forum Software Development
    Replies: 5
    Last Post: 12-02-2010, 05:37 PM
  4. How to execute Stored procedures in Hibernate?
    By NegiRaj in forum Software Development
    Replies: 3
    Last Post: 10-08-2009, 11:54 PM
  5. Replies: 3
    Last Post: 03-08-2009, 09:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,722,460.61215 seconds with 17 queries