Shulou(Shulou.com)06/01 Report--

Create a stored procedure for MYAQL

1 create a storage process without parameters

CREATE PROCEDURE procedure name () process body SELECT VERSION ()

2 call the stored procedure

The first CALL procedure name (); (used with parameters)

The second CALL procedure name; (used without parameters)

(3) create a stored procedure with IN type parameters (change the default delimiter of MYSQL to / / before creation, and modify it back after creation)

Step 1 DELIMITER new delimiters such as / /

The second step is CREATE PROCEDURE procedure name such as removeUserByid (IN parameter name such as id data type such as INT character type such as UNSIGNED)

Step 3 BEGIN

Step 4 DELETEFROM table name the parameter id passed by the field id= in the WHERE data table

Step 5 END

Step 6 / /

Step 7 original delimiter of DELIMITER

4 delete program

DELETE PROCEDURE procedure name

Delete stored procedure

DROP PROCEDURE procedure name

(5) create a stored procedure with IN and OUT type parameters (change the default delimiter of MYSQL to / / before creation, and modify it back after creation)

Step 1 DELIMITER new delimiters such as / /

The second step is CREATE PROCEDURE procedure name such as removeUserAndReturnUserNums (IN parameter name such as p _ id data type such as INT character type such as UNSIGNED,OUT parameter name such as userNums data type such as INT character type such as UNSIGNED)

Step 3 BEGIN

Step 4 DELETEFROM table name the parameter p_id passed by the field id= in the WHERE data table

Step 5 SLEECTcount (field such as id) FROM table name INTO parameter name userNums

Step 6 END

Step 7 / /

Step 8 original delimiter of DELIMITER

6 call stored procedure CALL procedure name

For example, removeUserAndReturnUserNums (21 to be deleted and @ nums for the remaining total); query the remaining total for SELECT @ nums

ROW_COUNT (); you can query the insertion of data. Total number of records deleted and updated

7. Create a stored procedure with multiple parameters of OUT type (change the default delimiter of MYSQL to / / before creation, and modify it back after creation)

Step 1 DELIMITER new delimiter such as / / delete the user by age and return information

The second step is CREATE PROVEDURE procedure name such as removeUserByAgeReturnInfos (IN parameter name such as p _ age data type such as SMALLINT character type such as UNSIGNED,OUT parameter name such as deleteUsers data type such as SMALLINT character type such as UNSIGNED,OUT parameter name such as userCounts data type such as SMALLINT character type such as UNSIGNED)

Step 3 BEGIN

Step 4 DELETEFROM table name the parameter p_age passed by the field age= in the WHERE data table

Step 5 SLEECTROW_ COUNT () table name INTO parameter name deleteUsers

Step 6 SLEECTCOUNT (field such as id) FROM table name INTO parameter name userCounts

Step 7 END

Step 8 / /

Step 9 original delimiter of DELIMITER

8 call stored procedure CALL procedure name such as removeUserByAgeReturnInfos (to delete, such as 21, the number you want to delete, such as @ nums, the remaining total, such as @ wang)

Query the quantity you want to delete and the quantity left after deletion

SELECT such as @ nums,@wang

Query the remaining total SELECT @ nums

9 how many fields do you want to query in the query table

SELECT COUNT (id) FROM table name WHERE the fields you want to query such as age= the data you want to query such as 21

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.