SELECT name
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourProcedureName')
LEARN SQL
its a good tutorial of SQL if someone is interested in learning SQL. Hope you people will like it.
Thursday, 14 May 2015
Monday, 11 May 2015
Get SQL Server schema via a SQL query?
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS ;
Tuesday, 14 April 2015
PL/SQL CURSORS
Oracle creates a memory area, known as context area, for processing
an SQL statement, which contains all information needed for processing
the statement, for example, number of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.
The syntax for creating an explicit cursor is :
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:
- Implicit cursors
- Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:
Attribute | Description |
---|---|
%FOUND | Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE. |
%NOTFOUND | The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. |
%ISOPEN | Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. |
%ROWCOUNT | Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. |
Example:
We will be using the CUSTOMERS table we had created and used in the previous chapters.Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:
DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 500; IF sql%notfound THEN dbms_output.put_line('no customers selected'); ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ' customers selected '); END IF; END; /When the above code is executed at SQL prompt, it produces the following result:
6 customers selected PL/SQL procedure successfully completed.If you check the records in customers table, you will find that the rows have been updated:
Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2500.00 | | 2 | Khilan | 25 | Delhi | 2000.00 | | 3 | kaushik | 23 | Kota | 2500.00 | | 4 | Chaitali | 25 | Mumbai | 7000.00 | | 5 | Hardik | 27 | Bhopal | 9000.00 | | 6 | Komal | 22 | MP | 5000.00 | +----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.The syntax for creating an explicit cursor is :
CURSOR cursor_name IS select_statement;Working with an explicit cursor involves four steps:
- Declaring the cursor for initializing in the memory
- Opening the cursor for allocating memory
- Fetching the cursor for retrieving data
- Closing the cursor to release allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:CURSOR c_customers IS SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:CLOSE c_customers;
Example:
Following is a complete example to illustrate the concepts of explicit cursors:DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; /When the above code is executed at SQL prompt, it produces the following result:
1 Ramesh Ahmedabad 2 Khilan Delhi 3 kaushik Kota 4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal MP PL/SQL procedure successfully completed.
Below is tested code in Microsft Sql Server 2014
Declare @id as int; Declare @name as NVARCHAR(50); Declare @age as NVARCHAR(50); Declare @address as NVARCHAR(50); Declare @salary as NVARCHAR(50); Declare @c_customers as cursor set @c_customers =cursor for select id,name,age,[address],salary from customer; open @c_customers; fetch next from @c_customers into @id, @name,@age,@address,@salary; while @@FETCH_STATUS=0 begin Update customer set salary=salary+500 where id=@id print cast(@id as varchar (50))+' '+@name; fetch next from @c_customers into @id,@name,@age,@address,@salary; end close @c_customers; deallocate @c_customers;
--To check the updated Data select * from customer
Sunday, 7 October 2012
10 interesting facts about SQL for beginners
10 interesting facts about SQL for beginners
2. SQL helps to structure and manage information in a database as well as carry out searches for information using structures. Some of these structures might include putting all the information about cars into tables comprising of makes, models, year of manufacture, and colour.
3. SQL makes an enquiry (or Query as the name suggests) to a database by sending a request. This request could come from a website such as Autotrader or a search engine.
4. SQL can perform all of these functions in a fraction of a second.
5. There are different developers of SQL, such as Oracle, Sybase, and Microsoft, however their SQL programs all fundamentally work the same way.
6. MySQL is an opensource version of SQL, meaning it is open to the public to upgrade through the internet and best of all… free. This makes it very popular with developers.
7. SQL falls into the middleware category of software. Middleware typically functions in between two other programs, such as a website (the front end) and a database (the backend) allowing them to communicate.
8. Three good examples of websites that use SQL in some capacity are Ebay, Autotrader, and Amazon.
9. The types of people that may use SQL include Applications Developers, Programmers, and Database Administrators (i.e. the people that look after large databases).
10. One type of commonly known database program that works well with SQL is ‘Microsoft Access’. This works with Microsoft’s own version of SQL (Microsoft SQL) which is typically stored on business servers (i.e. central computers). This type of set up allows thousands of employees to access data through their own PCs from the server. This data could be anything from financial information, customer details or retail goods that their company might sell.
Friday, 5 October 2012
SQL Hosting
SQL Hosting
If you want your web site to be able to store and display data from a database, your web server should have access to a database system that uses the SQL language.
If your web server will be hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans.
The most common SQL hosting databases are MySQL, MS SQL Server, and MS Access.
You can have SQL databases on both Windows and Linux/UNIX operating systems.
Below is an overview of which database system that runs on which OS.
MS SQL Server
Runs only on Windows OS.
MySQL
Runs on Windows, Mac OS X, and Linux/UNIX operating systems.
MS Access (recommended only for small websites)
Runs only on Windows OS.
SQL Summary
This SQL tutorial has taught you the standard computer language for accessing and manipulating database systems.
You have learned how to execute queries, retrieve data, insert new records, delete records and update records in a database with SQL.
You have also learned how to create databases, tables, and indexes with SQL, and how to drop them.
You have learned the most important aggregate functions in SQL.
You now know that SQL is the standard language that works with all the well-known database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and MS Access.
If you want your web site to be able to store and display data from a database, your web server should have access to a database system that uses the SQL language.
If your web server will be hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans.
The most common SQL hosting databases are MySQL, MS SQL Server, and MS Access.
You can have SQL databases on both Windows and Linux/UNIX operating systems.
Below is an overview of which database system that runs on which OS.
MS SQL Server
Runs only on Windows OS.
MySQL
Runs on Windows, Mac OS X, and Linux/UNIX operating systems.
MS Access (recommended only for small websites)
Runs only on Windows OS.
SQL Summary
This SQL tutorial has taught you the standard computer language for accessing and manipulating database systems.
You have learned how to execute queries, retrieve data, insert new records, delete records and update records in a database with SQL.
You have also learned how to create databases, tables, and indexes with SQL, and how to drop them.
You have learned the most important aggregate functions in SQL.
You now know that SQL is the standard language that works with all the well-known database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and MS Access.
The HAVING Clause
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
________________________________________
SQL HAVING Example
We have the following "Orders" table:
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
________________________________________
SQL HAVING Example
We have the following "Orders" table:
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/11/12 | 1000 | Hansen |
2 | 2008/10/23 | 1600 | Nilsen |
3 | 2008/09/02 | 700 | Hansen |
4 | 2008/09/03 | 300 | Hansen |
5 | 2008/08/30 | 2000 | Jensen |
6 | 2008/10/04 | 100 | Nilsen |
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000 br="br">The result-set will look like this:2000>
Customer | SUM(OrderPrice) |
---|---|
Nilsen | 1700 |
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
Customer | SUM(OrderPrice) |
---|---|
Hansen | 2000 |
Jensen | 2000 |
Subscribe to:
Posts (Atom)