Functions in Oracle, use of cursors with ___ static functions

Source: Internet
Author: User

use of functions and cursors in Oracle

--Creates a function and invokes the CREATE function fun1 (v_in_ename varchar2) return number is v_annual_sal number;
  Begin Select (SAL+NVL (comm,0)) *13 into V_annual_sal from EMP where ename=v_in_ename;
return v_annual_sal;
End
/-Direct Call function var v_annual_sal number;
Call Fun1 (' FORD ') into:v_annual_sal;

Print v_annual_sal; --Package--demand: Write a package that has a procedure that can receive a username and a new salary--(used in the future to update the salary by user name) and a function that can receive a username--(how much will be paid for that user in the future) Create package
Mypackage1 is-declares a process procedure Pro1 (v_in_ename varchar2,v_in_newsal number);
function fun1 (v_in_ename varchar2) return number;

End  --Write Package Create package body Mypackage1 is--the implementation process procedure Pro1 (v_in_ename varchar2,v_in_newsal number) is Begin update
EMP set sal=v_in_newsal where Ename=v_in_ename;
End
function fun1 (v_in_ename varchar2) return number is v_annual_sal number;
  Begin Select (SAL+NVL (comm,0)) *13 into V_annual_sal from EMP where ename=v_in_ename;
return v_annual_sal;
End
End /--pl/sql verbose syntax Create or replace procedure Pro1 (v_in_empno number) is--definitionVariable v_tax_rate Number: = 0.03;
V_sal number;
V_tax number;
V_ename VARCHAR2 (32);
Begin select Ename,sal into V_ename,v_sal from EMP where empno=v_in_empno;
V_tax:=v_sal*v_tax_rate; Dbms_output.put_line (v_ename| | ' Salary is = ' | | v_sal| | ' Personal income tax = ' | |
V_tax);

End
Use%type in--pl/sql to increase the flexibility of the column create or replace procedure Pro1 (v_in_empno number) is--Define variable v_tax_rate number: = 0.03;
V_sal Emp.sal%type;
V_tax number;
V_ename Emp.ename%type;
Begin select Ename,sal into V_ename,v_sal from EMP where empno=v_in_empno;
V_tax:=v_sal*v_tax_rate; Dbms_output.put_line (v_ename| | ' Salary is = ' | | v_sal| | ' Personal income tax = ' | |
V_tax);


End --"case" to write a procedure that can receive a user number--and display the user's name, salary, position (note: Using Pl/sql record Implementation) Create or replace procedure Pro1 (V_in_empno in
Is--Defines the record data type type Zy_emp_record is records (V_ename Emp.ename%type, V_sal emp.sal%type, V_job emp.job%type);
--Define a variable, the type is Zy_emp_record V_emp_record Zy_emp_record;
Begin select Ename,sal,job into V_emp_record from EMP where empno=v_in_empno; Dbms_output.put_line (' Username: ' | | V_emp_record.v_ename| | ' Salary = ' | |
V_emp_record.v_sal);

End
--the cursor's use of the Create or replace procedure Pro1 (V_in_deptno number) is-defines a cursor variable type types Zy_emp_cursor is ref CURSOR first;
--Define a cursor variable v_emp_cursor zy_emp_cursor;
--Define 2 variables V_ename emp.ename%type;
V_sal Emp.sal%type;
Begin-Execute Statement open v_emp_cursor for select ename,sal from EMP where Deptno=v_in_deptno; --Take out each row of data that the cursor points to, and fetch v_emp_cursor into v_ename,v_sal loop with loops;
This sentence will cause V_emp_cursor to go down--to determine whether the current cursor reaches the last exit when V_emp_cursor%notfound; --Output dbms_output.put_line (' username = ' | | v_ename| | ' salary = ' | |
V_sal);
End Loop;
--Closes the cursor close v_emp_cursor;

End --The use of cursors, which are added on the basis of wage create or replace procedure Pro1 (V_in_deptno number) is--Define one cursor variable type type zy_emp_cursor is REF CURSOR
;
--Define a cursor variable v_emp_cursor zy_emp_cursor;
--Define 2 variables V_ename emp.ename%type;
V_sal Emp.sal%type;
V_empno Emp.empno%type;
Begin-Execute Statement open v_emp_cursor for select Ename,sal,empno from EMP where Deptno=v_in_deptno; --Take out each row of data that the cursor points to, and fetch v_emp_cursor into v_ename,v_sal,v_empno loop with loops; This sentence will cause V_emp_cursor toGo down--to determine whether the current cursor arrives at the last exit when V_emp_cursor%notfound; --Output dbms_output.put_line (' username = ' | | v_ename| | ' salary = ' | |
V_sal);
If v_sal <2000 then update emp set sal=sal+1000 where Empno=v_empno;
End If;
End Loop;
--Closes the cursor close v_emp_cursor;












 End


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.