转换为PROCEDURE pl/sql

问题描述

我想编写一个程序，该程序将首先打印雇员的雇员编号和薪水(即7839).然后，它将按照以下条件增加雇员7839的薪水(这将是表中雇员的雇员编号):

I want to Write a PROCEDURE that will first print the Employee Number and Salary of an employee (i.e. 7839). Then it will increase the salary of an employee 7839 (this will be employee number in the table employee) as per following conditions:

Condition-1: If experience is more than 10 years, increase salary by 20%.
Condition-2: If experience is greater than 5 years, increase salary by 10%.
Condition-3: All others will get an increase of 5% in the salary.

该程序将在增加之前和之后打印员工编号和薪水，我尝试了以下步骤，但不确定其准确性如何.

The program will print the Employee Number and salary before and after the increase i tried the following steps but not sure how accurate is it..

我需要将其转换为PROCEDURE代码.

I need to convert it to a PROCEDURE code.

请告知

 DECLARE
     veno  emp.empno%type:=&veno;
     vsal  emp.sal%type;
     vexp  number;
BEGIN
    select empno,sal,trunc(to_char(months_between(sysdate,hiredate)/12))into veno,vsal,vexp from emp where empno=veno;
DBMS_OUTPUT.PUT_LINE('before update:' ||chr(10)||veno||chr(10)||vsal);
    if vexp>=10 then
        update emp set sal=sal+(sal*.20) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   elsif vexp>=5 then
        update emp set sal=sal+(sal*.10) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
     else
        update emp set sal=sal+(sal*.05) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   end if;
END;
/

推荐答案

您需要更改的只是将DECLARE(指示匿名块的开始)更改为CREATE PROCEDURE，其中包含您当前通过设置的变量替代变量作为形式参数；所以代替:

All you need to change is the DECLARE (indicating the start of an anonymous block) to CREATE PROCEDURE, with the variable you're currently setting via a substitution variable as a formal argument; so instead of:

DECLARE
     veno  emp.empno%type:=&veno;
     vsal  emp.sal%type;
     vexp  number;
BEGIN
...
END;
/

制作:

CREATE OR REPLACE PROCEDURE my_proc (veno IN emp.empno%type)
AS
     vsal  emp.sal%type;
     vexp  number;
BEGIN
...
END;
/

然后，您可以使用execute速记从匿名块，SQL * Plus或SQL Developer中调用它:

You can then call that from an anonymous block, or in SQL*Plus or SQL Developer with the execute shorthand:

set serveroutput on
execute my_proc(&veno);

此示例仍在使用替换变量，因此将提升您使用的值，但您也可以直接传递数字.

This example is still using a substitution variable so you'll be promoted for the value to use, but you can pass a number directly too.

详细了解创建过程，并且参数的类型.

您可以大大简化代码，以减少重复和重新查询；查找大小写表达式和返回子句.但这并不直接相关.

You could simplify the code quite a bit to reduce repetition and requerying; look up case expressions and the returning clause. But that's not directly relevant.

这篇关于转换为PROCEDURE pl/sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！