有员工关系emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) (1)创建存储过程emp_p,用游标提取emp表中3344雇员的姓名和职务； （2）调用存储过程emp_p。

以下是实现上述存储过程的 SQL 代码：

CREATE PROCEDURE emp_p
AS
BEGIN
    DECLARE @empno INT
    DECLARE @ename VARCHAR(50)
    DECLARE @job VARCHAR(50)
    
    DECLARE emp_cursor CURSOR FOR
        SELECT empno, ename, job
        FROM emp
        WHERE empno = 3344

    OPEN emp_cursor

    FETCH NEXT FROM emp_cursor INTO @empno, @ename, @job

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Employee Name: ' + @ename + ', Job: ' + @job
        FETCH NEXT FROM emp_cursor INTO @empno, @ename, @job
    END

    CLOSE emp_cursor
    DEALLOCATE emp_cursor
END

该存储过程使用游标提取 `emp` 表中雇员编号为 3344 的姓名和职务信息，并通过 `PRINT` 语句输出结果。你可以通过以下代码调用该存储过程：

EXEC emp_p

注意，在调用存储过程时不需要传递任何参数。

相关问题

创建表emp,并插入 EMPNO、ENAME、JOB、MGR、HIREDATE、SAL、COMM 和 DEPTNO这些列

好的，以下是创建表emp并插入数据的SQL语句：

CREATE TABLE emp (
  EMPNO NUMBER(4) PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'YYYY-MM-DD'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1982-12-09', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1983-01-12', 'YYYY-MM-DD'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'YYYY-MM-DD'), 1300, NULL, 10);

这个SQL语句会先创建一个名为emp的表，包含EMPNO、ENAME、JOB、MGR、HIREDATE、SAL、COMM和DEPTNO这些列，并将EMPNO列设为主键。然后，通过INSERT INTO语句向表中插入了一些数据。

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 NULL 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7565 EMILY SALESMAN 7698 20-FEB-81 1250 800 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7655 JONES MANAGER 7839 2-APR-81 2975 NULL 20 7698 BLAKE MANAGER 7839 1-MAY-91 2850 NULL 30 7782 CLARK MANAGER 7839 9-JUN-81 2450 NULL 10 7788 SCOTT ANALYST 7655 21-MAR-87 3000 NULL 20 7844 TURNER SALESMAN 7698 18-SEP-81 1500 NULL 30 7876 ADAMS CLERK 7788 24-APR-87 1100 NULL 20 7900 JAMES CLERK 7698 3-DEC-81 950 NULL 30 7902 FORD ANALYST 7655 3-DEC-81 3000 NULL 20 7934 MILLER CLERK 7782 3-JAN-81 1300 NULL 10 Given This Table Called 'EMP', please define a function that, given a department number, returns the average salary of that department.

Sure, here's the function in SQL:

CREATE FUNCTION avg_salary_dept (dept_no INT) RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE total_salary DECIMAL(10, 2);
    DECLARE num_employees INT;
    SELECT SUM(sal) INTO total_salary, COUNT(*) INTO num_employees FROM EMP WHERE DEPTNO = dept_no;
    RETURN total_salary / num_employees;
END;

This function takes in a department number and returns the average salary of employees in that department. It uses the SQL `SUM` function to add up all the salaries of employees in the department, and the `COUNT` function to get the number of employees in the department. Then it calculates the average salary by dividing the total salary by the number of employees, and returns the result.