Oracle System Variable Function Usage Guide, oracle Variables

Oracle System Variable Function Usage Guide, oracle Variables

Oracle functions are diverse. System variable functions are one of the three most common system variable functions.

Oracle System variable functions:

1) SYSDATE

This function returns the current date and time. The current date and time of the Oracle server are returned.

select sysdate from dual;insert into purchase values(‘Small Widget','SH',sysdate, 10);insert into purchase values(‘Meduem Wodget','SH',sysdate-15, 15);

To view all sales records in the last 30 days, run the following command:

select * from purchase where purchase_date between (sysdate-30) and sysdate;

2) USER

View the user name.

select user from dual;

3) USERENV
View various information about the user environment.

select userenv(‘TERMINAL') FROM dual;

The preceding Oracle System variable functions are introduced.

Oracle function, explains the use and call of variables. I just learned oracle from cainiao and cannot understand the use of functions. The more detailed the better.

CREATE OR REPLACE
FUNCTION get_salary (// FUNCTION
Dept_no NUMBER, // input parameter
Emp_count out number) // output parameters
Return number // RETURN parameters
IS
V_sum NUMBER; // This is the local variable for declaring the Function
BEGIN
Select sum (SALARY), count (*) INTO V_sum, emp_count // The form parameter is used here. emp_count is an output parameter and will return
From employees where DEPARTMENT_ID = dept_no;
RETURN v_sum; // RETURN the value of v_sum.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('the data you need does not exist! ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );
END get_salary;
Call
DECLARE // here is the variable declared during the call
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum: = get_salary (10, v_num); // v_num, v_sum the two variables call the function to obtain the value. v_num is the value obtained by emp_count, and v_sum is the v_sum value of return in the function.
DBMS_OUTPUT.PUT_LINE ('total salary of Department number: 10: '| v_sum |', number of students: '| v_num );
END;

The value assignment of variables in Oracle functions does not work. oracle11g is used.

-- Select count (pid) into rootId from costcenterinfo where ID = rootId;
Select count (pid) into sumnum from costcenterinfo where id = costcenterId;

RETURN sumnum;

Because sumnum is returned, you assign a value to the comments and return NULL to all of the rootId values.