1.存储过程
CREATE DATABASE dbtest15;
USE dbtest15;
CREATE TABLE employees
AS
SELECT *
FROM atguigudb. ` employees` ;
CREATE TABLE departments
AS
SELECT * FROM atguigudb. ` departments` ;
SELECT * FROM employees;
SELECT * FROM departments;
DELIMITER $
CREATE PROCEDURE select_all_data( )
BEGIN
SELECT * FROM employees;
END $
DELIMITER ;
CALL select_all_data( ) ;
DELIMITER
CREATE PROCEDURE avg_employee_salary( )
BEGIN
SELECT AVG ( salary) FROM employees;
END
DELIMITER ;
CALL avg_employee_salary( ) ;
DELIMITER
CREATE PROCEDURE show_max_salary( )
BEGIN
SELECT MAX ( salary)
FROM employees;
END
DELIMITER ;
CALL show_max_salary( ) ;
DESC employees;
DELIMITER
CREATE PROCEDURE show_min_salary( OUT ms DOUBLE )
BEGIN
SELECT MIN ( salary) INTO ms
FROM employees;
END
DELIMITER ;
CALL show_min_salary( @ms ) ;
SELECT @ms ;
DELIMITER
CREATE PROCEDURE show_someone_salary( IN empname VARCHAR ( 20 ) )
BEGIN
SELECT salary FROM employees
WHERE last_name = empname;
END
DELIMITER ;
CALL show_someone_salary( 'Abel' ) ;
SET @empname := 'Abel' ;
CALL show_someone_salary( @empname ) ;
SELECT * FROM employees WHERE last_name = 'Abel' ;
DELIMITER
CREATE PROCEDURE show_someone_salary2( IN empname VARCHAR ( 20 ) , OUT empsalary DECIMAL ( 10 ,